📄 第一章 pl-sql一览 - pl-sql用户指南与参考 - whatiswhat.htm
字号:
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> <STRONG>CURSOR</STRONG> c1 <STRONG>IS</STRONG><BR> <STRONG>SELECT</STRONG> ename, sal, hiredate, job<BR> <STRONG>FROM</STRONG> emp;<BR><BR> emp_rec c1%<STRONG>ROWTYPE</STRONG>; <EM>-- declare record variable that represents</EM><BR><EM> -- a row fetched from the emp table</EM>
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>当我们执行语句 </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>FETCH</STRONG> c1 <STRONG>INTO</STRONG> emp_rec;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>表emp中ename字段的值就会赋给emp_rec的ename域，sal字段值赋给sal域，依此类推。
</P><IMG alt=""
src="第一章 PL-SQL一览 - PL-SQL用户指南与参考 - whatiswhat.files/o_1-3.gif"
name="">
<P class=title2>7、控制结构</P>
<P>流程控制是PL/SQL对SQL的最重要的扩展。PL/SQL不仅能让我们操作Oracle数据，还能让我们使用条件、循环和顺序控制语句来处理数据，如IF-THEN-ELSE，CASE，FOR-LOOP，WHILE-LOOP，EXIT-WHEN和GOTO等。
</P>
<UL>
<LI>条件控制 </LI></UL>
<P>我们经常需要根据环境来采取可选择的行动。IF-THEN-ELSE语句能让我们按照条件来执行一系列语句。IF用于检查条件；THEN决定在条件值为true的情况下执行；ELSE在条件值为false或null的情况才执行。
</P>
<P>看一下下面的例子，这个程序用于处理银行事务。在我们从账户3取走$500元之前，它会先确认是否有足够的资金供我们支配。如果余额足够，程序就会更新账户(accounts)表的信息，否则的话，程序会向审核(audit)表插入一条余额不足的提示信息。
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> acct_balance <STRONG>NUMBER</STRONG>(11, 2);<BR> acct <STRONG>CONSTANT</STRONG> <STRONG>NUMBER</STRONG>(4) := 3;<BR> debit_amt <STRONG>CONSTANT</STRONG> <STRONG>NUMBER</STRONG>(5, 2) := 500.00;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>SELECT</STRONG> bal<BR> <STRONG>INTO</STRONG> acct_balance<BR> <STRONG>FROM</STRONG> accounts<BR> <STRONG>WHERE</STRONG> account_id = acct<BR> <STRONG>FOR</STRONG> <STRONG>UPDATE</STRONG> <STRONG>OF</STRONG> bal;<BR><BR> <STRONG>IF</STRONG> acct_balance >= debit_amt <STRONG>THEN</STRONG><BR> <STRONG>UPDATE</STRONG> accounts<BR> <STRONG>SET</STRONG> bal = bal - debit_amt<BR> <STRONG>WHERE</STRONG> account_id = acct;<BR> <STRONG>ELSE</STRONG><BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> temp<BR> <STRONG>VALUES</STRONG> (acct, acct_balance, <EM>'Insufficient funds'</EM>);<BR> <EM>-- insert account, current balance, and message</EM><BR> <STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR><BR> <STRONG>COMMIT</STRONG>;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>要从多个条件值中选出对应的操作时，我们可以使用CASE结构。CASE表达式会计算条件值，然后相应的操作(这个操作有可能是一个完整的PL/SQL块)。
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CASE</STRONG><BR> <STRONG>WHEN</STRONG> shape = <EM>'square'</EM> <STRONG>THEN</STRONG><BR> area := side * side;<BR> <STRONG>WHEN</STRONG> shape = <EM>'circle'</EM> <STRONG>THEN</STRONG><BR> <STRONG>BEGIN</STRONG><BR> area := pi *(radius * radius);<BR> DBMS_OUTPUT.put_line(<EM>'Value is not exact because pi is irrational.'</EM>);<BR> <STRONG>END</STRONG>;<BR> <STRONG>WHEN</STRONG> shape = <EM>'rectangle'</EM> <STRONG>THEN</STRONG><BR> area := LENGTH * width;<BR> <STRONG>ELSE</STRONG><BR> <STRONG>BEGIN</STRONG><BR> DBMS_OUTPUT.put_line(<EM>'No formula to calculate area of a'</EM> || shape);<BR> <STRONG>RAISE</STRONG> PROGRAM_ERROR;<BR> <STRONG>END</STRONG>;<BR><STRONG>END</STRONG> <STRONG>CASE</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<UL>
<LI>循环控制 </LI></UL>
<P>LOOP语句能让我们多次执行一系列语句。LOOP循环以关键字LOOP开头，END
LOOP结尾i。下面语句就是最简单的LOOP循环： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>LOOP</STRONG><BR> <EM>-- sequence of statements</EM><BR><STRONG>END</STRONG> <STRONG>LOOP</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>FOR-LOOP语句可以指定整数的范围，然后范围内每一个数字执行一次。例如，下面的循环将向数据库表插入500个数字和它们的平方根：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>FOR</STRONG> num <STRONG>IN</STRONG> 1 .. 500 <STRONG>LOOP</STRONG><BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> roots<BR> <STRONG>VALUES</STRONG> (num, SQRT(num));<BR><STRONG>END</STRONG> <STRONG>LOOP</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>WHILE-LOOP语句会按照某个条件值执行。每次循环条件值都会被计算一次，如果条件值为true，循环内的语句就会被执行，然后再次回到循环顶部，重新计算条件值。如果条件值为false或是null的话，循环就会停止，控制权被移交给下一语句。
</P>
<P>下面的例子中，我们要找出第一个工资高于$2500的且行政级别高于雇员编号7499雇员的员工： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>DECLARE</STRONG><BR> salary emp.sal%<STRONG>TYPE</STRONG> := 0;<BR> mgr_num emp.mgr%<STRONG>TYPE</STRONG>;<BR> last_name emp.ename%<STRONG>TYPE</STRONG>;<BR> starting_empno emp.empno%<STRONG>TYPE</STRONG> := 7499;<BR><STRONG>BEGIN</STRONG><BR> <STRONG>SELECT</STRONG> mgr<BR> <STRONG>INTO</STRONG> mgr_num<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> empno = starting_empno;<BR><BR> <STRONG>WHILE</STRONG> salary <= 2500 <STRONG>LOOP</STRONG><BR> <STRONG>SELECT</STRONG> sal, mgr, ename<BR> <STRONG>INTO</STRONG> salary, mgr_num, last_name<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> empno = mgr_num;<BR> <STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR><BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> temp<BR> <STRONG>VALUES</STRONG> (<STRONG>NULL</STRONG>, salary, last_name);<BR><BR> <STRONG>COMMIT</STRONG>;<BR><STRONG>EXCEPTION</STRONG><BR> <STRONG>WHEN</STRONG> NO_DATA_FOUND <STRONG>THEN</STRONG><BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> temp<BR> <STRONG>VALUES</STRONG> (<STRONG>NULL</STRONG>, <STRONG>NULL</STRONG>, <EM>'Not found'</EM>);<BR><BR> <STRONG>COMMIT</STRONG>;<BR><STRONG>END</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>EXIT-WHEN语句可以在需要继续执行循环的情况下退出循环。当遇到EXIT语句时，WHEN子句中的条件值就会被计算。如果条件为true，循环就会结束，控制权交给下一条语句。下面的例子中，在total的值超过2500时，循环就会结束：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>LOOP</STRONG><BR> ...<BR> total := total + salary;<BR> <STRONG>EXIT</STRONG> <STRONG>WHEN</STRONG> total > 25000; <EM>-- exit loop if condition is true</EM><BR><STRONG>END</STRONG> <STRONG>LOOP</STRONG>;<BR><EM>-- control resumes here</EM>
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<UL>
<LI>顺序控制 </LI></UL>
<P>GOTO语句能让我们无条件地跳转到一个标签。标签就是一个用双尖括号夹起来的未声明标示符，它必须在一个可执行语句或是PL/SQL块之前。执行时，GOTO语句将控制权交给用标签作了标记的语句或块，如下例所示：
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>IF</STRONG> rating > 90 <STRONG>THEN</STRONG><BR> <STRONG>GOTO</STRONG> calc_raise; <EM>-- branch to label</EM><BR><STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR><BR><<calc_raise>><BR><STRONG>IF</STRONG> job_title = <EM>'SALESMAN'</EM> <STRONG>THEN</STRONG> <EM>-- control resumes here</EM><BR> amount := commission * 0.25;<BR><STRONG>ELSE</STRONG><BR> amount := salary * 0.10;<BR><STRONG>END</STRONG> <STRONG>IF</STRONG>;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P class=title2>8、模块化</P>
<P>模块化可以让我们把程序分成多个部分，这样可以把复杂的问题划分开来，更好的解决问题。PL/SQL为我们提供了块、子程序和包三个程序单元来用于模块化处理。</P>
<UL>
<LI>子程序 </LI></UL>
<P>子程序有两种，分别是函数和过程。子程序很像一段小型的程序，由可选的声明部分，执行控制部分和可选的异常控制部分组成。
</P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>PROCEDURE</STRONG> award_bonus(emp_id <STRONG>NUMBER</STRONG>) <STRONG>IS</STRONG><BR> bonus <STRONG>REAL</STRONG>;<BR> comm_missing <STRONG>EXCEPTION</STRONG>;<BR><STRONG>BEGIN</STRONG> <EM>-- executable part starts here</EM><BR> <STRONG>SELECT</STRONG> comm * 0.15<BR> <STRONG>INTO</STRONG> bonus<BR> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> empno = emp_id;<BR><BR> <STRONG>IF</STRONG> bonus <STRONG>IS</STRONG> <STRONG>NULL</STRONG> <STRONG>THEN</STRONG><BR> <STRONG>RAISE</STRONG> comm_missing;<BR> <STRONG>ELSE</STRONG><BR> <STRONG>UPDATE</STRONG> payroll<BR> <STRONG>SET</STRONG> pay = pay + bonus<BR> <STRONG>WHERE</STRONG> empno = emp_id;<BR> <STRONG>END</STRONG> <STRONG>IF</STRONG>;<BR><STRONG>EXCEPTION</STRONG> <EM>-- exception-handling part starts here</EM><BR> <STRONG>WHEN</STRONG> comm_missing <STRONG>THEN</STRONG><BR> ...<BR><STRONG>END</STRONG> award_bonus;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>调用时，这个过程接受一个雇员编号。它使用编号来查询雇员的commission，同时计算出15%的奖金。然后，它检查bonus总值。如果奖金为空，就会抛出异常，否则雇员的工资记录就会被更新。
</P>
<UL>
<LI>包 </LI></UL>
<P>PL/SQL可以让我们把逻辑相关的类型、变量、游标和子程序放在一个包内，这样更加清楚易理解。包通常有两部分组成：包说明部分和包体部分。包说明部分是应用程序的接口，它"声明"了类型、常量、变量、异常、游标和可以使用的子程序。包体用于实现包说明部分声明的子程序和游标。
</P>
<P>下面的例子是把两个雇用相关的过程进行打包： </P>
<BLOCKQUOTE>
<TABLE>
<TBODY>
<TR>
<TD
noWrap><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> emp_actions <STRONG>AS</STRONG> <EM>-- package specification</EM><BR> <STRONG>PROCEDURE</STRONG> hire_employee(empno <STRONG>NUMBER</STRONG>, ename <STRONG>CHAR</STRONG>, ...);<BR><BR> <STRONG>PROCEDURE</STRONG> fire_employee(emp_id <STRONG>NUMBER</STRONG>);<BR><STRONG>END</STRONG> emp_actions;<BR><BR><STRONG>CREATE</STRONG> <STRONG>PACKAGE</STRONG> <STRONG>BODY</STRONG> emp_actions <STRONG>AS</STRONG> <EM>-- package body</EM><BR> <STRONG>PROCEDURE</STRONG> hire_employee(empno <STRONG>NUMBER</STRONG>, ename <STRONG>CHAR</STRONG>, ...); <STRONG>IS</STRONG><BR> <STRONG>BEGIN</STRONG><BR> <STRONG>INSERT</STRONG> <STRONG>INTO</STRONG> emp<BR> <STRONG>VALUES</STRONG> (empno, ename, ...);<BR> <STRONG>END</STRONG> hire_employee;<BR><BR> <STRONG>PROCEDURE</STRONG> fire_employee(emp_id <STRONG>NUMBER</STRONG>) <STRONG>IS</STRONG><BR> <STRONG>BEGIN</STRONG><BR> <STRONG>DELETE</STRONG> <STRONG>FROM</STRONG> emp<BR> <STRONG>WHERE</STRONG> empno = emp_id;<BR> <STRONG>END</STRONG> fire_employee;<BR><STRONG>END</STRONG> emp_actions;
</TD></TR></TBODY></TABLE></BLOCKQUOTE>
<P>只有在包说明中声明的内容对应用程序是可见的，而包体中的内容是不可见，无法访问的。包被编译后会存放到数据库中，当我们首次调用包中的子程序时，整个包就会被加载到内存当中，所以，后续的调用不再涉及到磁盘的读取问题。因此，包可以提高效率改善性能。
</P>
<P class=title2>9、数据抽象</P>
<P>数据抽象可以让我们把必要的属性提取出来，忽略那些非必须的细节问题，有助于我们更好地解决问题。一旦我们设计好一个数据结构，就可以不再考虑细节内容，而专注于操作这个数据结构的算法问题的研究。
</P>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -