PLSQL讲义.doc

第一章：PL/SQL概述一、什么是PL/SQL ORACLE公司发明PL/SQL为了克服SQL语言自身的一些缺陷并为那些要在ORACLE中运行核心业务提供一种完整的解决方案。PL/SQL具有以下特点l 是一种高度结构化、易读易理解的语言l 是一种标准的、可移植的ORACLE语言，实现了编写一次到处运行l 是一种嵌入式语言，必须在特定的宿主环境内执行，不能编写一个PL/SQL可执行程序l 是一种高性能的、高集成度的数据库语言，如果你使用VB、DELPHI等进行数据库开发，最终你会发现使用PL/SQL将比其他任何语言都跟轻松高效二、改进的执行授权和事物一致性数据库和SQL语言让我们可以紧密地控制对任何个别数据库表的访问和修改，例如可以通过GRANT命令确保只有特定的角色和用户可以对指定的表执行更新操作，另一方面GRANT命令不能确保用户或者应用执行的更新操作能够正确执行，换句话说，数据库不能确保跨越多个表的事物一致性，在业务事务中涉及多个表是很常见的。PL/SQL实现紧密地控制和管理能力是通过执行授权。我们只是授权执行某个过程，后者控制对底层数据结构的访问。从ORACLE8I开始，PL/SQL提供了AUTHID子句，我们可以使用之前描述的定义者权限模型，也可以使用调用者权限模型。三、PL/SQL结构：SQL整合Declare I_book_count int;Begin Select count(*) into i_book_count from books where author like 12; Dbms_output.put_line(123); -改名 Update books set author = 31;End;说明：Declare:声明单元Begin:执行单元-:注释出现错误PL/SQL提供了强大的错误处理机制Procedure check_account(account_id_in in accounts.id%type)IsI_balance_remaining number;I_balance_below_minimum exception;I_account_name account.name%type;Begin Select name into i_account_name from accounts where id = account_id_in; I_balance_remaining := account_balance(account_id_in); If i_balance_remaining 1000 Then Raise i_balance_below_minimum; End if;Exception When no_data_found Then log_error(.); When i_balance_below_minumum Then log_error(.); Raise;End;第二章：语言基础2.1、PL/SQL块结构块：最小的有意义的代码单元，一个块就是一组代码，这个块给出了执行边界，也为变量声明和异常处理提供了作用范围，PL/SQL允许我们创建匿名块和命名块，命名块可以使包、过程、函数、触发器或者对象。一个PL/SQL块最多可以由4个不同的单元组成，只有一个单元是必不可少的HeaderIs Declaration sectionBegin Execution sectionException Exception sectionEnd;块头：只有命名块才会有这个单元，块头单元确定了这个命名块或者程序的调用方法，这个单元是可选的。声明单元：这部分定义变量、游标以及可以被后面的执行单元或者异常处理单元引用的子块，这一单元也是可选的。执行单元：这部分包含PL/SQL引擎运行时要执行的语句，这一单元时必须的。异常处理单元：这部分处理过程中抛出的异常，这一单元也是可选的。Procedure get_happy(ename_in in varchar2)IsI_hiredate date;BeginI_hiredate := sysdate -2;Insert into employee(emp_name,hiredate) values (ename_in,i_hiredate);ExceptionWhen dup_val_in_indexThenDbms_output.put_line(cannot insert.);End;2.2、匿名块如果这个块没有头部单元，直接以DECLARE或者BEGIN开始，这个块就叫做匿名块。匿名块没有一个可用于引用的句柄，因此不能被其他块调用，相反，匿名块作为执行PL/SQL语句的一个容器，经常会调用过程和函数，因为一个匿名块可以由它自己的声明单元和异常处理单元，开发人员经常会在一个大的程序中借助嵌套匿名块的方式以提供一系列标示和异常处理。一个匿名块的语法：declare .声明语句Begin .一个或多个执行语句exception .异常处理语句End;一个最小的匿名块BeginDbms_output.put_line(sysdate);End;一个添加了声明单元的匿名块DeclareI_right_now varchar2(9);BeginI_right_now := sysdate;Dbms_output.put_line(i_right_now);End;一个添加了异常处理单元的匿名块DeclareI_right_now varchar2(9);BeginI_right_now := sysdate;Dbms_output.put_line(i_right_now);ExceptionWhen value_errorThenDbms_output.put_line(123);End;2.3、命名块命名块与匿名块的区别在于头部分：过程的头部分Procedure schema.nameparameter,parameter. authid definer|current_user函数的头部分Function schema.nameparameter,parameter.Return return_datatypeauthid definer|current_userparallel enablepipelined using |aggregate using因为ORACLE允许在SQL语句中调用函数，因此函数的头部会比过程的头部有更多的可选成员。2.4、嵌套块PL/SQL借鉴了ADA和PASCAL语言，因此块可以嵌套到其他块中。Procedure calc_totalsIsYear_total number;BeginYear_total := 0;/*嵌套块开始*/DeclareMonth_total := number;BeginMonth_total := year_total/12;End set_month_total;/*嵌套块结束*/End;被嵌套块也可称为闭包块、子块。外层的PL/SQL块也称为包围块、父块。总的来说、嵌套块的好处就是为我们提供了一个可以在代码中控制作用范围和可见性的方法。DeclareCursor emp_cur is ;BeginDeclareTotal_sales number;BeginDeclareI_hiredate date;BeginEnd;End;End;2.5、作用范围在任何一种编程语言中，作用范围的意思都是指确定一个给定标示符所指代的“具体东西”的方法。如果标示符多次出现，语言本身的作用范围规则将用于定义到底是用的哪一个。在PL/SQL中，变量、异常、模块和一些其他的结构都属于声明他们的块局部的，当这个块执行结束后，我们再也无法引用这些结构了，比如calc_total过程中，我们可以再过程内的任何地方引用外部块的元素，比如year_total变量，然而在内部块声明的元素对于外部块是不可见的。Package scope_demoIsg_global number;procedure set_global (number_in in number);end scope_demo;package body scope_demoisprocedure set_global (number in number)isi_salary number := 10000;i_count pls_integer;beginDeclarei_inner number;beginselect count(*) into i_count from employeeswhere department_id = i_inner and salary i_salary;end local_block;g_global := number_in;end set_global;end scope_demo;任何对scope_demo包具有execute授权的模式的任何代码块都可以使用变量scope_demo.g_global。而i_salary这个变量只能在set_global过程内部使用。I_inner这个变量只能被局部或者嵌套块内部使用。2.6、规范SQL语句中所有对变量和列的引用Package scope_demoIsg_global number;procedure set_global (number_in in number);end scope_demo;package body scope_demoisprocedure set_global (number in number)isi_salary number := 10000;i_count pls_integer;beginDeclarei_inner number;beginselect count(*) into set_global.i_count from employeeswhere department_id = local_block.i_inner and salary set_global.i_salary;end local_block;scope.demo.g_global := set_global.number_in;end set_global;end scope_demo;做了这些改动后，对每一个变量或者列的引用都通过表的别名、报名、过程名或者嵌套块的标签名规范化了。这么做的好处在于：l 改善代码的可读性l 避免由于变量名和列名一样时可能出现的BUGl 充分利用ORACLE11G的细粒度依赖这一特性2.7、可见性DeclareA date;B date;BeginA := sysdate;B := add_months(a,6);End;因为a,b两个变量的声明是在同一个块中，我们可以非常方便仅通过非规范化的标示符来引用他们，这些标示符也被称为可见标示符，一个可见标示符实际上可能属于以下情况：l 一个在当前块声明的标示符l 一个在包含当前块的块中声明的标示符l 属于你所有的一个单独的数据库对象l 一个你能看到是数据库别名l 一个循环体中的索引变量Package pany_pkgIsLast_pany_id number;End pany_pkg;然后可以再包的外面应用这个变量If new_pany_id = pany_pkg.last_pany_id then在默认情况下，给一个包级别的变量所赋的值只会在当前数据库会话的生命周期内有效，不会超过会话生命周期。Procedure calc_totalsIsSalary number;Begin.DeclareSalary number;BeginSalary := calc_totals.salary;End;.End;声明的第一个salary创建了一个作用域时整个过程范围的标示符，在嵌入块中声明了另一个同名的标示符，因此在内部块中引用变量salary时它会优先解释称内部块中声明的变量，因为这个变量是无需任何规范化就是可见的。Procedure remove_order (order_id in number)IsBeginDelete orders where order_id = order_id;End;这段代码的结果是删除order表中一切内容，尽管已经传入可order_id。其原因是：SQL的名称解析规则首先使用的是列名而不是PL/SQL标示符。因此，在编写代码时应该要有预见性，如果不想通过把每个变量都规范化从而保证其唯一性，那就需要某种小心地命名规范以避免这些冲突。2.8、PL/SQL字符集PL/SQL中的特殊符号符号说明;一个声明和语句的结束%属性指示符如%isopen，%rowtype；也用作like通配符_Like中的单字符通配符远程位置指示符:宿主变量标示符*幂运算 != =不等于|连接符标签符=大于等于、小于等于:=赋值=位置表示法使用的关联操作符.范围操作符-单行注释/*/多行注释2.9、标识符标识符就是一个PL/SQL对象名字，包括：l 常量或者变量l 异常l 游标l 程序名l 保留字l 标签标识符的默认属性l 长度最多30个字符l 必须字母开始l 可以带有$ _ #符号l 不能有任何空白字符l 通常不区分大小写，除非带上双引号2.10、直接量所谓直接量就是那些不是通过标识符表现出来的值，它只是一个值。与标识符不同，字符串直接量是大小写敏感的，如：If Steven = steven 结果为false2.11、null在ORACLE数据库中，一个值的缺失是通过关键字NULL来标示的，差不多所有的数据类型的PL/SQL变量都可以由NULL状态，唯一例外是关联数组类型。在ORACLE SQL和PL/SQL中，一个空字符串和零个字符的直接量时没有区别的如 is null 结果为true在pl/sql中给一个varchar2（n）变量赋值一个长度为0 的字符串，导致的结果就是null。但如果在pl/sql中给char(n)变量赋值一个长度为0的字符串，数据库会用空格字符填满这个变量。因而这个变量不为null。尽管如此，这种行为只在PL/SQL中存在，如果在数据库中，当向CHAR类型列插入长度为零的字符串，数据库不会用空格把内容填满，而是null。这说明ORACEL只是部分采纳了ANSI SQL标准92和99的要求。2.12、在一个直接量字符串中嵌入单引号如果想让字符串中带有一个单引号，我们需要使用两个紧挨的单引号theres no body.theres no body123123从10G开始，数据库引入了用户自定义分隔符q (theres no body.) theres no bodyq 123123qq|如例子演示的，我们可以使用简单的分割符如| ！，或者使用成对的如、进行分割。2.13、pragma关键字Pragma通常告诉编译器该采取什么行为如Autonomous_transaction告诉PL/SQL引擎，当前块对数据库所做的提交或修改不影响主事务或者外层事务。Exception_init告诉编译器一个特殊的错误标示DeclareNo_such_sequence exception;Pragma exception_init (no_such_sequence,-2289);Begin.ExceptionWhen no_such_sequenceThenEnd;第三章：PL/SQL程序结构3.1、条件和顺序控制3.1.1、IF语句IF类型特点IF THEN END IF;IF和THEN之间的条件决定THEN和END IF之间的语句是否执行，如果条件部分的求值结果是FALSE或者NULL，这些代码就不会执行IF THEN ELSE END IF;实现了2选1逻辑，根据位于IF和THEN两个关键字之间的条件求值结果，或者是THEN ELSE之间的代码被执行，或者是ELSE END IF之间的代码被执行。总之两部分代码肯定有一个被执行IF THEN ELSIF ELSE END IF;IF语句会从一系列互斥条件中选择一个TRUE的，然后执行条件关联语句。如果在9I以上，应考虑使用CASE语句3.1.2、IF THEN组合对于IF THEN组合，需要理解的是三值逻辑If salary 4000Then.End if;当salary为NULL时，是不会执行THEN END IF之间的代码的。用IS NULL或者IS NOT NULL是应对这种问题的好方法，或者使用NVL函数也可以应对。3.1.3、IF THEN ELSE组合IF conditionThen .trueElse false/null.End if;通常使用布尔变量要方便一些Order_exceed_balance := :customer.order_total max_allowable_order;If Order_exceed_balanceThen.3.1.4、IF THEN ELSIF组合IF condition-1Then Statement-1Elsif condition-nThenStatement-nelseElse statementsEnd if;If salary between 1000 and 2000ThenGive_bonus(employee_id,1500);Elsif salary between 2000 and 4000ThenGive_bonus(employee_id,1000);Elsif salary 4000ThenGive_bonus(employee_id,500);ElseGive_bonus(employee_id,0);End if;IF-ELSIF的条件部分总是按照从前到后的顺序进行求值，如果2个条件的求值结果都是TRUE，只有第一个条件部分会执行，比如之前的例子，2000的薪水会导致1500的津贴，尽管2000的薪水也满足1000的津贴，尽管在ELSIF总条件重叠是允许的，但是在程序设计上还是应该尽量避免这种情况发生3.1.5、嵌套IFIf condition1ThenIf condition2ThenStatement2ElseIf condition3ThenStatement3Elsif condition4ThenStatement4End if;End if;End if;对于复杂的逻辑来说，嵌套IF是必不可少的，不过使用这种嵌套要小心，它非常难于理解和调试，如果发现嵌套层数超过3层就应该仔细审查逻辑，寻求更简单的方法来完成，如果不能，那么可以考虑创建一个或多个本地模块来隐藏最内层的IF语句。嵌套IF结构的一个主要好处就是可以延迟内层条件的求值，只有当外层求值结果为TRUE时，它所包含的内部IF语句条件部分才会被求值。If award_bonus(employee_id) thenIf print_chech(employee_id) thenDbms_output.put_line(123);End if;End if;3.1.6、短路求值PL/SQL使用短路求值，也就是说PL/SQL不需要对一个IF语句中的所有表达式去求值，如If condition1 and condition2ThenElseEnd if;当第一个条件是false或为NULL，pl/sql就会立即停止对表达式的继续求值，立即跳转到else分支上。但是考虑以下语句：My_boolean := condition1 and condition2如果condition1是null，这个表达式不会出现短路，因为最终结果可能是null或者false，这完全依赖于condition2。而对于if来说，null和false没有区别，因此短路时有意义的。与AND类似，如果OR操作符的第一个数十TRUE则PL/SQL会立即执行THEN分支，因此也可以发生短路。因此从优化的角度，如果一个消耗大量CPU和内存资源的条件，此时短路求值非常有用，一定要将这类条件放到整个条件的最后。If low_cpu_condition and high_cpu_conditionThenEnd if;类似的，嵌套IF也可采用短路求值，最外层的条件应为low_cpu_condition。3.2、case语句和表达式简单CASE语句：通过值来关联一个或者多个PL/SQL语句，根据表达式的返回值来选择哪一个被执行。搜索性CASE语句：根据一系列布尔条件来确定要执行的PL/SQL语句系列，那些和第一个求值结果是TRUE的条件相管理的语句会被执行。3.2.1、简单CASE语句Case employee_typeWhen S thenDbms_putput.put_line(S);When H thenDbms_putput.put_line(H);ElseRaise invalid_employee_type;End case;这个CASE语句明确使用了ELSE子句，不过该子句是可选的，如果没有明确使用,PL/SQL隐含使用：ElseRaise case_not_found;这个行为处理和IF是完全不同的，对于CASE来说，如果没有满足WHEN的情形，CASE将导致错误对于发放津贴的例子，使用CASE子句也可以完成，一个开放性的做法如下：Case trueWhen salary =1000 and salary 2000 and salary = 4000Then .Else.End case; 虽然case true方案看起来是聪明的技巧，其实它是搜索型case语句的实现方法3.2.2、搜索型CASECaseWhen expression1 thenStatement1When expression2 thenStatement2Else.End case;与简单case一样，下面规则也同样适用l 一旦某些语句被执行，整个执行也就结束了，即使多个表达式为TRUE，也只有和第一个表达式相关联的语句被执行l ELSE是可选的，如果没有ELSE且没有一个为TRUE，则跑出CASE_NOT_FOUND异常l WHEN表达式从上到下依次求值既然WHEN语句是按照顺序求值，通过把最可能的WHEN放在代码最前面，将提升性能，而且如果WHEN本身消耗极大CPU，也可采用短路求值方法获得性能上的益处。同样，CASE语句中可以使用任何语句，所以可以吧内层CASE语句用IF替换，在CASE语句内也可以嵌套任何语句CaseWhen . ThenCaseWhen thenElseEnd case;When . Then.End case;3.2.3、case表达式Select case when a = 0 then 0When a=1 then 1Else 2End ;From tab_a;与case语句不同的是，即使case表达式中没有when子句被选择也不会跑出异常，相反，如果没有when被满足，case表达式返回NULL。3.2.4、GOTO语句GOTO语句可以无条件跳转到PL/SQL块中其他可执行语句处，与其他语法结构一样，只要适当、仔细使用GOTO，程序就会变得很强大GOTO的常见格式为：GOTO label_name其中LABEL_NAME是代表目标语句的标签名称begin goto secont_output;dbms_output.put_line(1);Dbms_output.put_line(2);End;GOTO语句有些限制：一个标签后面至少跟着一个可执行语句GOTO语句的目标标签必须和GOTO语句在一个作用域内GOTO语句的目标标签必须和GOTO语句在PL/SQL代码块的相同部分3.2.5、NULL有时我们可能不希望PL/SQL做任何事情，此时会用到NULL3.2.5.1、增加程序可读性If :report_mgr.selection = detailThenExec_detail_report;End if;如果用户没有选择detail，那么程序什么也不做，如果你在代码中增加了：If :report_mgr.selection = detailThenExec_detail_report;ElseNull;End if;此时你就明确强调了不用担心，你已经考虑到这种情况。3.2.5.2、在标签之后使用NULL 有时NULL和标签配对使用，如果数据状态表明无需额外的处理则代码快速跳转到程序结尾。Procedure process_date(data_in orders%rowtype,data_action in varchar2)IsStatus integer;BeginIf data_in.ship_data is not nullThenStatus := validate_shipdate(data_in.ship_date);If status != 0 then goto end_of_proc; end if;End if; .Null;End;3.3、循环迭代处理循环结构有以下三种：l 简单循环或者无限循环l For循环l While循环简单循环：仅以LOOP关键字开始，以END LOOP结束，靠循环体内的EXIT和EXIT WHEN或者RETURN来推出循环。LoopExit when n2;Dbms_output.put_line(n);End loop;For循环：分数值循环和游标循环For n in 1.3 loop.End loop;For n in (select employee_id from emp) loopEnd loop;While循环：和简单循环类似，区别在于可能循环一次都不执行While n 2Loop End loop;3.3.1、人为地无限循环有些程序如监控程序，需要永远执行下去，循环将消耗大量CPU，此时需要避免CPU耗尽，解决办法是在循环中加入适当暂停。LoopData_gather_procedure;Dbms_lock.sleep(10);End loop;3.3.2、结束一个人为地无限循环DeclarePipename constant varchar2(12) := singnaler;Result integer;Pipebuf varchar2(64);Begin-创建一个指定名字的私有管道Result := dbms_pipe.create_pipe(pipename);LoopData_gather_procedure;Dbms_lock.sleep(10);-检查管道是有有消息If dbms_pipe.receive_message(pipename,0) = 0Then-解释消息表采取的动作Dbms_pipe.unpack_message(pipebuf);Exit when pipebuf = stop;End if;End loop;End ;构造一个伙伴程序，这个程序只需要在管道中发送一个STOP就可以终止循环DeclarePipename varchar2(12) := signaler;Result integer := dbms_pipe.create_pipe(pipename);BeginDbms_pipe.pack_message(stop);End;3.3.3、出理非平滑增长PL/SQL中没有提供可以指定特殊的步幅，递进的步幅总是以1为单位。For loop_index in 1 . 100LoopIf mod (loop_index,2) = 0ThenCale_values(loop_index);End if;End loop;然而使用简单的方法可以将循环次数减半For even_number in 1 . 50LoopCalc_values(even_number*2);End loop;3.3.4、游标型FOR循环DeclareCursor cur_a is select id,name from emp;Rec_a cur_a%rowtype;BeginOpen cur_a;LoopFetch cur_a into rec_a;Exit when cur_a%notfound;.End loop;Close cur_a;End;以上代码用游标型FOR循环来改写：DeclareCursor cur_a is select id,name from emp;BeginFor rec_a in cur_a loopEnd loop;End;3.3.5、continue语句11G提供了continue语句，用于从本次迭代循环中退出Begin For I in 1 . 10 loopContinue when mod(1,2)=0;Dbms_output.put_line(i);End loop;End;当然使用IF也可以达到CONTINUE子句的效果，但是CONTINUE子句更加优雅，代码量更小。正确使用CONTINUE子句可以使嵌套层数大大减少。3.3.6、迭代处理技巧循环索引使用易于理解的名字一个程序只应有一个入口和一个出口在FOR和WHILE循环中不要使用EXIT,EXIT WHEN。在循环中不要使用RETURN或者GOTO，这样会导致非结构化的循环结束，且随着代码量增大将难于理解和维护。3.3.7、循环计数DeclareI pls_integer := 0;BeginFor i_rec in curLoopI := i_rec%rowcount;End loop;If I 10 then3.3.8、有时100%的SQL有缺点，因为SQL语句通常是全是或无的逻辑。采用游标型FOR循环可以提供更多的灵活性BeginFor i_rec in curLoopBeginInsert DeleteExceptionWhen others thenLog_error(xxx);End;End loop;End;第四章：异常处理术语：系统异常：由ORACLE定义的，在PL/SQL运行时引擎发现的错误后抛出的异常。程序员定义的异常：用EXCEPTION_INIT指定给ORACLE指定的错误名称异常处理单元：PL/SQL块中的可选部分，包含一个或多个异常处理句柄抛出：通过通知运行时引擎出现错误来终止当前块的运行，数据库本身可以抛出异常，也可以通过RAISE和RAISE_APPLICATION来抛出。处理：异常处理单元捕获错误，在处理句柄中编写代码来处理错误，向用户显示消息或者进行错误传播。作用范围：可