oracle数据库笔记a11.plsql开发

1、PL/SQL 开发目的：掌握 PL/SQL 编程技术及其基本应用内容：PL/SQL 语言基础、异常处理机制、器过程、函数、程序包、触发重点：过程、函数、触发器难点：异常处理一、PL/SQL 入门Procedure Language & Structured Query Language，PL/SQL 是 Oracle 对 SQL 语句的扩展，增加了编程语言的特点。数据操作和查询语句被包含在 PL/SQL 代码的过程性单元中，经过逻辑判断、循环等操作完成复杂的功能或者计算。PL/SQL 的优点：（1）使一组语句功能形成模块化程序开发（2）使用过程性语言控制程序结构（3）可以对程序中的错误进行处理

2、（4）具有较好的可移植性（5）集成在数据库中（6）有助于提高程序性能PL/SQL 运行示意1.1、PL/SQL 基本结构PL/SQL 程序代码采取模块式结构，通常由三部分组成。、执行主体和异常处理PL/SQL 程序块基本结构：PL/SQL 程序块举例：-创建表exception_log 来保存异常处理信息create table exception_log(aris_time datedefault sysdate,msgvarchar2(200);-PL/SQL 程序块举例：declare-一个名为 dn 的变量，其数据类型与Scott 方案中的 emp 表deptno字段相同（number

3、(2)）dn scott.emp.deptno%type;begin-查询 emp 表中dn 之中为 7566 的员工所属的部门，并把之保存到变量select deptnoo dn from emp where empno=7566;-更新该部门的全体员工工资，每人加薪 33 元update emp set sal=sal+33 where deptno=dn;commit; -提交事务-异常处理部分exceptionwhen no_data_found then -出现 no_data_found 异常inserto exception_log(msg) values(未查找到指定员工);c

4、ommit;then -出现其他异常when othersrollback;inserto exception_log(msg) values(修改工资操作出现异常);commit;end;执行完程序块后，与 7566 员工相同部门即 20 号部门的员工工资全部加了 33 元。注意：如果修改 empno=8000：select deptnoo dn from emp where empno=8000;这时候会出现no_data_found 异常，查找表 exception_log 可以见到异常信息：PL/SQL 注释：PL/SQL 注释分为单行和多行注释两种：单行注释以-开头，注释到本行行尾；

5、多行注释以/*开头，以*/结束，中间可以换行，行数不限。1.2、字符集与数据类型PL/SQL 程序由纯文本格式的字符序列组成，其所允许使用的字符集内容如下：数字：09大写及小写英文字母：AZ，az非显示字符：制表符、空格和回车符数学符号：+，-，*，/，*，=分隔符：()，?，!，;，:，#，%，$，&，等PL/SQL 语言中字符拼写大小写不敏感（字符串常量内容除外）。PL/SQL 数据类型划分：基本数据类型数据只能保持单一的值。复合数据类型数据可以封装多个不同类型属性，类似于面象编程语言中的类。常用基本数据类型：1.3、变量与常量语法：其中：Identifer：的变量或常量名；Constan

6、t：标记常量；Daype：变量或常量的数据类型；Not null：变量或常量取值不能为空值；:=或 default：为当前例子：的变量或常量指定初始值。set serveroutput on;-开启 DBMS 系统输出功能，在命令窗口中执行输入结果为：在 PL/SQL 中变量或常量时，还允许通过方式指定其数据类型。%TYPE 用于指定当前所的变量或常量的数据类型与指定方案的指定表的类型相同，其中方案名部分不是必须的，默认为当前用户的方案名。例子：输出结果如下：、PL/SQL 中执行 SQL 指令、执行 SELECT 指令PL/SQL 中可以使用 select 指令从数据库中检索数据，但必须添加

7、 子句将查询结果保存到指定的变量中，除此之外可使用完整的 SELECT法。语法格式如下：O语例子：执行结果：注意：PL/SQL 指令必须且只能返回一行查询结果，否则会导致相应的程序运行异常：TOO_MANY_ROWS（查询返回多行结果）和 NO_DATA_FOUND（查询返回零行结果）。如下：1.4.2、执行 DML 及事务控制指令在 PL/SQL 程序中执行 DML 和事务控制指令格式与 SQL 中完全相同，其细微差别在于前者还可以使用其程序的变量或常量。例子：输入结果：执行事务控制指令：指令格式与 SQL 中完全相同，且使用其程序的变量或常量。例如：执行结果如下，输入 select * f

8、rom test2 可以查看到事务先返回到保存点 b，以 1005 这条然后再提交没有被提交：1.4.3、执行 DDL 及 DCL 指令在 PL/SQL 代码中不允许直接执行 DDL 和 DCLEXECUTE IMMEDIATE 语句对其进行调用。例子：指令，但可以通过二、PL/SQL 程序流程控制2.1、分支结构根据约定条件的成立与否有选择地执行或跳过相应的程序代码，PL/SQL 使用常规的 IF 语句实现分支结构。语法格式：其中，可选部分 elseif.then 子句可以出现 0 至多次，else 子句可以出现0-1 次。使用if 子句可以实现单路分支、双路分支和多路分支结构。例子：输出结

9、果：注意：PL/SQL 中处理空值 NULL 的语则与 SQL 相同-任何包含空值的数学表达式，其值为 NULL；在字符表达式中出现的空值，会被作为空串（长度为零的字符串处理）；使用IS NULL 操作符判断空值。例如：、循环结构用于在约定条件下重复执行特定的代码，PL/SQL 支持三种循环类型：简单循环、FOR 循环、WHILE 循环。、简单循环语法：其中，关键字 LOOP.END LOOP 之间的内容为循环体；EXIT 指令用于退出循环；WHEN用于终止循环的条件，如果默认 WHEN 字句，则应将EXIT 指令置于 IF 分支语句中。举例：上面的程序块等价于：2.2.2、FOR 循环语法：

10、Index 是循环计数器变量，相当于其他高级语言中的整形循环迭代变量；PL/SQL 中 for 循环的计数器不需要例子：计算 1 到 100 的和，但只能在循环体内。for(i=0;i=100;i+) N=i;Total=total+i;输出结果为：2.2.3、WHILE 循环语法：condition 为循环条件，当条件成立的时候执行循环体中的代码。例子：用 while 循环计算 1 到 100 的和While(i100) i=i+i; total=total+i;输出结果为：2.3.4、使用控制循环PL/SQL 支持循环嵌套，且可使用例子：进行循环控制。输出结果：2.3、跳转语句PL/SQL

11、 支持使用 GOTO 语句实现跳转操作。语法：举例：注意：goto 语句降低了程序代码的可读性和可使用了。性，现在已经不提倡三、异常处理程序代码运行中所出现错误称为异常。常见异常类型：数据类型转换错误、算数除法的除零错误、向表中时出现主键字重复。PL/SQL 异常处理机制：针对各种常见的异常状况，预先定义的多种异常类型，指令执行过程中如果出现异常，则系统会侦测到并自动生成相应类型的异常信息，然后查找并执行相应的处理代码，如找不到则终止程序运行并显示必要的提示信息。3.1、系统预定义异常PL/SQL 中使用 EXCEPTINO 子句处理异常。例子：输出结果：其中，SQLCODE 和 SQLERR

12、M 为 PL/SQL 系统函数，用于获取异常代码及异常描述信息。在之前的表中可以看到有异常的列和异常名称。对于未命名的系统预定义异常，可以在 when others then 字句中根据异常代码进行区别处理；当然对于已命名异常也可以用异常代码处理，但是一般并不这样用。例子：上面代码分别运行：inserto dept values(500,Account,Beijng);delete dept where deptno=20;-删除子表 emp 中子表 dept 中的主前不能删除主inserto dept values(null,Account,Beijing);-主键字段不能使用空值会出现下面

13、三个输出：3.2、用户自定义异常除使用 Oracle 系统预定义异常之外，用户也可以根据具体的业务逻辑规则定义自己的异常名称，当业务逻辑规则时即可人为触发该自定义异常，进而转入异常处理流程、执行预先准备好的处理代码。用户自定义异常名称必须在 PL/SQL区定义：人为触发异常使用 raise 指令或 raise_application_error()函数：例子 1：输出结果：可以用 raise_application_error 人为触发用户自定义异常，但这种方式只能指定异常及其描述信息，而无法指定异常名称，例如：四、过程之前接触的 PL/SQL 程序块均为块，因为没有名称标识，只能使用一次且不

14、会被在数据库中。如果希望实现代码重用，则应对 PL/SQL 程序块进行命名，并将之编译后保存在数据库服务器端，将来在客户端程序中调用时只需指定其名称即可，且因每次调用时不需要重新编译而可以显著提高运行效率。命名后的PL/SQL 程序块，相当于其他高级编程语言中的函数或子过程，具体又可分为过程、函数、包和触发器四种。4.1、创建过程过程（PROCEDURE）用于在数据库中完成特定的操作或者任务，PL/SQL 中使用 DDL 指令 create procedure 创建或覆盖定义创建语法：过程。Procedure_name：过程名称，第一行相当于其他语言的函数头定义；IS 和 AS 之后的内容相当

15、一函数体；“OR REPLACE”可以省略，省略后只是创建新的过程而无法覆盖现有的过程，过程的形式参数格式如下：例子 1:上述是一个名为 change_salary 的过程，类似于一个函数，调用时和工资数额，然后修改 emp 的相应记通过参数接收从外界传来的雇员录。注意：定义无参的过程时，其过程的小括号必须省略；如果在过程中系统提示出现编译错误，可使用 show errors;指令查看详细的错误提示信息。执行的语句，可以在 PL/SQL 左下角看到创建好的过程：4.2、调用过程过程创建之后即可对其进行调用，可以使用 execute过程。语法如下：指令直接调用举例：在命令窗口下执行如下语句:也可

16、以在 PL/SQL 语句块中调用过程，语法格式为：例子：在一个过程中调用另一个过程create or replace procedure test_procedure(a in number,b in number) isBEGINchange_salary(7369, 2500);END;4.3、参数类型及默认值与高级编程语言中函数的形参类似，PL/SQ过程借助于过程参数实现其与调用环境之间的数据传递，并可以实现灵活的数据传输功能。二者的差别在于，高级编程语言中的函数的形参都是单向传递数据的（函数仅用于接收从外界调用环境传入的数据，再通过函数返回值将处理结果返回/输出到调用环境），而 PL/

17、SQL过程的参数却分为 in、out 和 in out 共三种模式参数以实现灵活的数据传输功能。PL/SQL 参数类型：IN 模式、OUT 模式、IN OUT 模式。4.3.1、IN 参数IN 模式用于在过程调用时接收外界传进来的数据值，相当于传统函数的形参，使用最为广泛，也是过程的默认参数模式（定义时 IN 关键字可以省略）。过程定义中形参数据类型不允许指定其字段长度及数值精度。IN 模式参数被做为已赋值的常量对待，因而不可以在过程体中再对其进行赋值操作。例子：-首先创建样本表mytable1drop table mytable1;createtable mytable1(name varc

18、har2(10),age number(3);inserto mytable1 values(1,30);inserto mytable1 values(2,25);-创建过程mp1,有两个参数newAge 和ncreateor replace procedure mp1(newAgein number,n invarchar)isbeginupdate mytable1 set age=newAgewheretrim(name)=n;commit;end;/此时mytable1 中的数据是：调用过程：现在 mytable1 中的数据是：4.3.2、OUT 模式OUT 模式参数的作用与 IN

19、模式参数相反，即用于将过程处理结果返回给调用环境。OUT 参数相当于未赋值的变量，因要在过程执行结束时将其值返回给调用者，故必须在过程中为其赋值。调用过程时与 OUT 模式参数相对应的实参必须是变量，否则，使用常量或表达式的话，将因无法保存返回值而出错。例子：-创建一个过程mp3，其中v_name 是 in 参数，v_age 是 out 参数create or replaceproceduremp3(v_nameinvarchar,v_ageoutnumber)asbeginv_age:=777;end;/-测试declarev1 number;beginmp3(Tom,v1);-调用过程mp

20、3inserto mytable1values(Tom,v1+100);end;/上面的例子中，在调用过程mp3(Tom,v1);的时候，实参Tom的值被传递给mp3 过程的IN 模式参数v_name，在过程执行完毕时，其out 模式参数v_age的值（777）被反向传递给相应的调用环境（块）的实参v1。执行完后，表mytable1 的数据如下所示：4.3.3、IN OUT 模式IN OUT 模式参数是 IN 与 OUT 两者的结合在过程调用之初接收调用环境传递进来的参数值，在过程执行期间该参数可作为普通局部变量使用（其值可以被修改），在过程执行完毕之后再将其返回给调用环境。例子：-创建使用i

21、n out 模式参数的过程create or replace proceduremp5(v_nameinvarchar,v_ageinoutnumber)asbeginv_age:=v_age+888;end;-调用过程declarev1 number:=0;beginmp5(zhangsan,v1);inserto mytable1 values(zhangsan,v1);end;在过程 mp5 中，v_age 被设置为 in out 参数，那么调用mp5(zhangsan,v1);之初，实参zhangsan被传递给 mp5 的 IN 模式 v_age，在执行完了之后，其out 模式参数 v

22、_age 被反向传递给相应的调用环境。这样 v_age 就相当于起到了一个 IN 和OUT 的作用。所有调用过程完后，查询表mytable1：4.3.4、参数默认值对于 IN 模式参数，可以在例子：时为其指定参数默认值。先创建一个过程：v_new_sal 被设置了默认值 8888create or replace procedure mp6(v_empno number,v_new_salnumber:=8888)isv_nameemp.ename%TYPE;beginselectenameo v_name from emp where empno=v_empno;updateemp set

23、sal=v_new_sal where empno=v_empno;dbms_output.put_line(v_name|工资修改为|v_new_sal);commit;end;然后调用过程：execute mp6(7369,2500):因为这时候传入的是两个参数，那么第二个参数v_new_sal 为 2500如果调用过程：execute mp6(7369):此时因为第二个参数没有值，所有默认v_new_sal 为 8888所以，调用过程中如果给出的实参数目，运行环境将按照从左到右的顺序依次进行参数值的传递，多余出来的形参（必须为 IN 模式，否则运行出错）则使用其默认值，因而在过程参数时即

24、应考虑好其排列顺序，建议排列顺序如下：无默认值的 IN 参数、OUT 默认参数、IN OUT 模式参数，最后才是有默认值的 IN 模式参数。4.4、删除语法格式：过程例如：drop procedure change_salary;五、函数5.1、创建函数函数用来执行复杂的计算，并返回计算的结果。 与其他高级编程语言中函数的不同之处在于：PL/SQL 函数必须有返回值。函数与过程的区别在于：PL/SQL 函数只能使用 IN 模式参数在调用时接收外界传进来的数据；而过程可以使用 IN、OUT 及 IN OUT 3 种模式实现双向的数据传递；PL/SQL 函数必须有返回值，且只能有一个，以返回计算结

25、果给调用环境；而过程不允许有返回值，但可以使用 OUT 或 IN OUT 模式参数返回多个数值给调用环境；（3）函数不允许像过程一样被独立调用，而只能作为表达式的一部分来使用（其执行后的返回值将被作为一个运算数进行表达式计算）；（4）PL/SQL 函数返回值类型必须是 Oracle 数据库支持的数据类型，即不允许使用 PL/SQL 特有的数据类型；因为函数已经定义，不仅可以在PL/SQL 代码被调用，还允许使用标准 SQL 指令进行调用，而过程则与之相反；PL/SQL 函数中，必须包括一个带有数据类型的 return 子句，以指定其返回数据的类型，且在其函数体（PL/SQL 程序块）中至少应包

26、括一个有效的 return 语句。PL/SQL 函数语法格式如下：例子：-创建函数,函数名为tax,形参是v_value，类型是number，返回类型是number 类型create or replace function tax(v_value in number) return numberisbeginif v_value1000 thenreturn(v_value*0.1);elsereturn(v_value*0.5);end if;end tax; -结束函数创建执行上述函数创建后，可以见到Function 中多了 TAX 函数：5.2、调用函数-在SQL 指令中直接调用函数se

27、lect empno,ename,tax(sal)应纳税额 from emp;-在函数中调用函数create or replace function tax2(v_value in number)returnnumberisbeginreturn tax(v_value)+100;end;-在过程中调用函数CREATE OR REPLACEPROCEDURE mp6(v_empno NUMBER)ISv_sal emp.sal%TYPE;BEGINSELECT salO v_sal FROM emp WHERE empno =v_empno;UPDATE emp SET sal = sal -

28、 tax(v_sal) WHERE empno = v_empno;COMMIT;END;5.3、删除函数使用 DDL 指令drop function 删除函数，其语法格式：例如：DROP FUNCTION tax;六、包PL/SQL 程序包（Package ）用于将多个功能或用途相近的程序单元组合到一起，组成一个逻辑上的集合。如果需要，还可以在包内若干个供包中的程序单元共用的全局变量。当包中的任何程序单元被调用时，运行环境都将加载整个程序包，以对包中其他程序单元的速度。6.1、创建包PL/SQL 程序包分为包头和包体两部分：包头（Package Specification）也称包描述、包规范

29、，用于对包中所有组成元素进行简单。包体（Package Body）与包头相对应，包括相关过程及函数的具体定义信息。创建包头的语法格式如下：-创建包头create or replace package p1istax_rate1number:=0.1;tax_rate2number:=0.5;procedurechange_salary(v_empno in number,v_new_sal in number);function tax(v_value in number) return number;end p1;上述代码定义了一个名为 p1 的包头部，其中包含两个全局变量(tax_rate

30、1、tax_rate2)的定义，以及一个明头部信息。过程（change_salary）和一个函数(tax)的声创建好了可以在 PL/SQL Developer 的左下角 Packages 看到创建好的信息：包体部分与包头相对应，包括相关过程及函数的具体定义信息，创建包体语法格式如下：举例：-创建包体create or replace package body p1is-过程的具体定义信息procedure change_salary(v_empno in number,v_new_sal in number)isv_nameemp.ename%TYPE;beginselectenameo v_

31、name from emp where empno=v_empno;updateemp set sal=v_new_sal where empno=v_empno;dbms_output.put_line(v_name|工资修改完毕);commit;end change_salary;-函数的具体定义信息function tax(v_value in number) return numberisbeginif v_value1000 thenreturn (v_value*tax_rate1);elsereturn (v_value*tax_rate2);end if;end tax;end

32、 p1;执行上面创建包体部分代码，创建成功后查看到 Package bodies 增加了 P1：注意：在包体定义过程或函数时，不允许使用 createor replace关键字。为增加代码的可读性，可以在过程、函数或包体定义结束的 END 后加上相应的程序单元名称，例如“END change_salary;”一分清代码层次。6.2、调用包在包外部调用其中所定义的过程及函数时需要在其前面加上.以做标记，除此之外与调用普通过程和函数方法相同。-调用包 p1 中的change_salary 过程execute p1.change_salary(7369,5000);-调用包 p1 中的 tax 函数

33、select p1.tax(2000) from dual;注意：一直在使用的“dbms_output.put_line()”语句其实就是调用系统预定义程序包dbms_output 中的 put_line 函数。6.3、删除包语法格式：举例：DROP PACKAGE p1;七、触发器触发器（Triger）是与过程和函数类似的另一种保存在数据库服务器端的 PL/SQL 程序单元，但其运行机制上与函数存在明显差别触发器不能由用户或应用程序显示调用，而是与特定的数据表、视图操作或数据库事件联系到一起，当进行特定操作或发生特定事件时系统将自动触发并执行相关触发器程序代码，这就类似于高级编程语言中的事件

34、处理器。触发器的作用：安全性保护产生对数据值修改的审计提供更灵活的完整性校验规则（4）提供表数据的同步以及事件日志触发器的事件可以是对数据库表的DML 操作（insert、update 或delete）或视图操作，也可以是某种系统事件或用户事件，例如数据库的启动和管理或 DDL 操作。按照其触发事件的性质进行分类：DML 触发器系统事件触发器用户事件触发器DML 触发器又可以细分为语句级触发器、行级触发器、instead of 触发器三种。7.1、语句级触发器DML 触发器的操作对象为表或视图，由 DML 语句触发，按照 DML 操作类型又可分为insert、update 和delete 三种

35、，按照具体触发时机可分为before（DML 语句执行之前触发），afte（r代 DML 语句的执行)3 种。语句级触发器在被一条 DMLDML 语句执行之后触发）和 instead of(替语句触发后只执行一次，而无论该 DML操作会涉及多少行。语法如下：Tigger_name：触发器名；Timing：触发的时机；Event：具体触发的时间（DML 操作）类型；When 子句：确定触发后是否执行触发器的执行部分。例子 1：(注意：创建的语句最好在 SQL PLus 上面执行，因为出现一些编译错误，SQLPlus 中是不提示的，而在测试时候可能会发生触发器无效且未通过重新验证。如果在 SQL

36、PLus 中运行提示有编译错误，可以执行 show error;查看错误的位置)-创建语句级触发器create or replace trigger secure_deptbefore insert on dept -每当在 dept 表上执行insert 操作之前触发触发器secure_deptbegin-如果时间是周末或工作日的下班时间，则抛出异常if(to_char(sysdate,day) in (六,日) or(to_char(sysdate,hh24:mi) not betn 08:00 and 18:00)then-自定义的一个异常raise_application_error(

37、-20001,你只能在工作时间对dept 表执行操作);dbms_output.put_line(呵呵);end if;end;创建好触发器后，可以看到PL/SQL Developer 左下角的 Triggers：上述触发器创建之后，如果试图在工作以外时间对 dept 表执行 insert 操作则会出错：inserto dept values(60,Account,);例子 2：create or replace trigger secure_dept-每当在 dept 表上执行insert,update 或delete 操作之前触发触发器secure_deptbefore insert or

38、 update or delete on deptdeclareuser_name varchar2(20);begin-如果时间是周末或工作日的下班时间，则抛出异常if(to_char(sysdate,day) in (五,六,日) or(to_char(sysdate,hh24:mi) not betn 08:00 and 18:00)thenif deleting then -如果对表 dept 执行删除操作raise_application_error(-20002,你只能在工作时间对 dept 表执行数据删除操作);elsif inserting then -如果对表 dept 执行

39、操作raise_application_error(-20003,你只能在工作时间对 dept 表执行数据操作);elsif updating(dname) then -如果对表 dept 中的 dname 字段执行更新操作select usero user_name from dual; -从 dual 表中找出当前用户赋值给user_nameif(lower(user_name)scott) then -如果当前用户不是 scottraise_application_error(-20004,你无权在下班时间内修改部门名称信息);end if;elseraise_application_e

40、rror(-20005,只允许在工作时间执行数据修改操作);end if;end if;end;/如下测试：1、以 sys 登录 orcl，然后hr 操作可以在任何方案中更新表：2、在 scott 方案下更新表 dept，因为当前登录用户是 sys，所以会报一下异常：3、如果以scott 用户登录，那么就会修改成功：7.2、行级触发器针对某一 DML 语句操作所影响到的多行数据的每一行都单独执行一次。对于触发时机为 BEFORE 的行级触发器，还可以在触发器执行代码中相关的行字。语法格式：For each row 的意义是：在一次操作表的语句中，每操作成功一行就会触发一次；不写的话，表示是表级

41、触发器，则无论操作多少行，都只触发一次；:NEW 和:OLD 使用方法和意义，new 只出现在 insert 和 update 时，old只出现在update 和 delete 时。在 insert 时 new 表示新的行数据，update时 new 表示要替换的新数据、old 表示要被更改的原来的数据行，delete 时old 表示要被删除的数据。例子：-创建行级触发器create or replace trigger restrict_sal-每当在 emp 表上进行新或涉及 sal 字段更新操作时，会调用触发器restrict_salbefore insert or update of

42、sal on empfor each rowbegin-如果雇员的职位不是经理或者分析员，并且工资超过 5000，那么出错，抛出异常if not(:new.job in(MANAGER,YST) and :new.sal3000thenraise_application_error(-20202,员工不能赚到这么多薪水);end if;end;/测试：7.3、INSTEAD OF 触发器ead of 触发器主要用于视图操作，其作用是替代触发它的 DML 语句主要用于视图操作。在表或视图中定义 instead of 触发器之后，针对该表或视图的 DML 操作将不会被执行而是转向执行instead of 触发器中的代码。复杂视图中的数据通常来自多个基表或符合表达式，故无法对其进行插入、更新及删除等 DML 操作，但