第07章OraclePLSQL语言基础_控制语句_过程_触发器

1、数据库操作与管理语言数据库操作与管理语言Oracle SQL Oracle PL/SQL语言基础语言基础/过程过程/触发器触发器本章目标本章目标lPL/SQL语言基本结构语言基本结构lPL/SQL变量和常量的声明变量和常量的声明lPL/SQL程序的执行部分程序的执行部分l了解了解PL/SQL常用函数常用函数l存储过程存储过程l触发器触发器l自定义函数自定义函数l游标游标PL/SQL简介简介lPL/SQL(Procedural Language/SQL，过程语言，过程语言/SQL)它是结合它是结合Oracle过程语言和结构化查询语言的一种扩展语言过程语言和结构化查询语言的一种扩展语言PL/SQL

2、支持多种数据类型，可以使用条件语句和循环语句等控制支持多种数据类型，可以使用条件语句和循环语句等控制结构结构PL/SQL可用于创建存储过程、触发器和程序包，也可以用来处理可用于创建存储过程、触发器和程序包，也可以用来处理业务规则、数据库事件或给业务规则、数据库事件或给SQL命令的执行添加程序逻辑命令的执行添加程序逻辑PL/SQL的优点的优点l支持支持SQLl支持面向对象编程支持面向对象编程(OOP)l更好的性能更好的性能l可移植性可移植性l与与SQL集成集成l安全性安全性PL/SQL的基本结构的基本结构-1lPL/SQL语言是程序化程序设计语言。语言是程序化程序设计语言。块块(Block)是是

3、PL/SQL程序中最基本的结构，所有程序中最基本的结构，所有PL/SQL程序都是由块组成。程序都是由块组成。lPL/SQL的块由变量声明、程序代码和异常处理代码的块由变量声明、程序代码和异常处理代码3部分组成：部分组成：DECLARE标记声明部分标记声明部分变量的声明，必须要在变量的声明，必须要在begin前面前面声明一些变量、常量、用户定义的数据类型及游标声明一些变量、常量、用户定义的数据类型及游标name varchar(30); -声明时不设置值声明时不设置值name varchar(30):=Jack;-声明带有默认值声明带有默认值name %type; -直接引

4、用一个表的数据类型直接引用一个表的数据类型BEGIN标记主程序体部分开始标记主程序体部分开始主程序体，在这里可以加入各种合法语句主程序体，在这里可以加入各种合法语句EXCEPTION 标记异常处理部分开始标记异常处理部分开始异常处理程序，当程序中出现错误时执行这一部分异常处理程序，当程序中出现错误时执行这一部分END 标记主程序体结束部分标记主程序体结束部分PL/SQL基本结构基本结构-2declare 说明部分 （变量说明，光标申明，例外说明 begin 语句序列 （DML语句 exception 例外处理语句 End;/PL/SQL字符集字符集lPL/SQL语言有效字符包括以下三类语言有效

5、字符包括以下三类所有大写和小写英文字母所有大写和小写英文字母09的阿拉伯数字的阿拉伯数字操作符，包括操作符，包括(、)、+、-、*、/、!、=、%等等lPL/SQL标识符标识符标识符的最大长度为标识符的最大长度为30个字符，不区分大小写，但建议在标识符个字符，不区分大小写，但建议在标识符中适当使用大小写，以增加程序的可读性。中适当使用大小写，以增加程序的可读性。PL/SQL的运算符的运算符lPL/SQL语言的运算符语言的运算符算术运算符算术运算符加加(+)、减、减(-) 、乘、乘(*) 、除、除(/) 、乘方、乘方(*)和连接和连接(|)关系运算符关系运算符=、(或或!=)、=、=、BETWE

6、EN.AND.、IN、LIKE、IS NULL逻辑运算符逻辑运算符逻辑与逻辑与(AND) 、逻辑或、逻辑或(OR) 、逻辑非、逻辑非(NOT)PL/SQL常量和变量常量和变量l在在PL/SQL程序运行时，需要定义一些变量来存放一些数程序运行时，需要定义一些变量来存放一些数据。常量据。常量和变量在使用前必须声明，可以使用和变量在使用前必须声明，可以使用DECLARE对变量进行声明，对变量进行声明，语法如下：语法如下：DECLARE :=默认值默认值;.在在DECLARE块中可以同时声明多个常量和变量。声明普通常量或块中可以同时声明多个常量和变量。声明普通常量或变量是需要说明以下信息：变量是需要说

7、明以下信息：常量或变量的名称常量或变量的名称常量或变量的数据类型常量或变量的数据类型变量说明变量说明l说明说明l记录变量分量的引用：记录变量分量的引用：emp_rec.ename:=ADAMS;说明变量名、数据类型和长度后用分号结束说明语句。引用型变量，即my_name的类型与emp表中ename列的类型一样记录型变量集声明常量声明常量l声明常量的基本格式如下：声明常量的基本格式如下： constant := ;:= 为赋值语句为赋值语句关键字关键字constant表示声明的是常量。常量一旦定义，在以后的使用中表示声明的是常量。常量一旦定义，在以后的使用中其值不再改变。其值不再改变。一些固定的

8、大小为了防止有人改变，最好定义成常量。一些固定的大小为了防止有人改变，最好定义成常量。例如例如Pass_Score constant INTEGER := 60 ;声明变量声明变量l声明变量的基本格式如下：声明变量的基本格式如下： (宽度宽度) := ;变量声明是没有关键字，但要指定数据类型，宽度和初始值可以定义变量声明是没有关键字，但要指定数据类型，宽度和初始值可以定义也可以不定义。也可以不定义。例如例如Address VARCHAR2(30) := 地址未知地址未知;lPL/SQL对一个未初始化的变量，将被默认赋值为对一个未初始化的变量，将被默认赋值为NULL例如例如Address VAR

9、CHAR2(30);PLSQL案例案例-1：l-打开输出打开输出lset severoutput onl-声明一个变量并输出声明一个变量并输出ldeclarel name varchar(10):=HelloWorld;l beginl dbms_output.put_line(name);l end;案例案例-2：l例如例如 SET SERVEROUTPUT ON; DECLARE Pass_Score constant INTEGER:=60; Address VARCHAR2(30):=北京海淀区北京海淀区; BEGINDBMS_OUTPUT.PUT_LINE(Pass_Score);D

10、BMS_OUTPUT.PUT_LINE(Address); END;l使用使用SET SERVEROUTPUT ON命令设置环境变量命令设置环境变量SERVEROUTPUT为打开状态，从而使为打开状态，从而使PL/SQL程序能够程序能够在在SQL*Plus中输出结果中输出结果l使用函数使用函数DBMS_OUTPUT.PUT_LINE()可以输出参数的值可以输出参数的值PL/SQL程序的执行部分程序的执行部分lPL/SQL程序的执行部分包括程序的执行部分包括赋值语句赋值语句流程控制语句流程控制语句SQL语句语句游标语句游标语句使用赋值语句使用赋值语句l可以在声明变量时或处理变量时设置初始值。也可

11、以在程可以在声明变量时或处理变量时设置初始值。也可以在程序的执行部分对变量进行赋值。序的执行部分对变量进行赋值。SET ServerOutPut ON;DECLARETrainName VARCHAR2(30);BEGINTrainName := Oracle Administration; -赋值一个新值赋值一个新值Dbms_output.put_line(TrainName);END;运行结果如下图所示运行结果如下图所示接收用户的输入赋值：接收用户的输入赋值：在在declare之前，可以通过之前，可以通过accept someVar prompt 提示信息提示信息;要求用户输入要求用户输入

12、在后面的代码中，可以通过地址引用在后面的代码中，可以通过地址引用&someVal接收这值接收这值-接收用户的输入接收用户的输入set serveroutput on-要求用户输入一个串要求用户输入一个串,如果不输入后面也可以直接引用，只是一个提示而已如果不输入后面也可以直接引用，只是一个提示而已accept age prompt plz enter a number:;declarenn number;begin nn := &age; dbms_output.put_line(你输入的信息是你输入的信息是|nn);end;从查询中结果中赋值：从查询中结果中赋值：l用用into

13、关键字可以将查询结果的值，设置给变量：关键字可以将查询结果的值，设置给变量：set serveroutput ondeclare id varchar(30); nm varchar(30);begin select id,name into id,nm from person where id=P001; dbms_output.put_line(编号编号|id|名称名称|nm); -以下是异常处理以下是异常处理 -可选可选 exception when NO_DATA_FOUND then dbms_output.put_line(没有你要查询的数据没有你要查询的数据); when oth

14、ers then dbms_output.put_line(其他错误其他错误);end;流程控制语句流程控制语句l流程控制语句是所有过程性程序语言的关键流程控制语句是所有过程性程序语言的关键lPL/SQL的主要控制语句如下：的主要控制语句如下：if.then elsif then end if;判断判断if正确则执行正确则执行then，否则执行，否则执行else(elsif为嵌套判断为嵌套判断)注意注意elsif,里面少一下里面少一下e.Case var when then when then end有逻辑的从数值中做出选择有逻辑的从数值中做出选择Loop exit end loop循环控制，

15、用判断语句执行循环控制，用判断语句执行exitLoop exit when end loop同上，当同上，当when为真时执行为真时执行exitwhile.loop end loop当当while为真时循环为真时循环for.in.loop end loop已知循环次数的循环已知循环次数的循环条件语句条件语句IF-1lIF语句是根据条件表达式的值决定执行相应的程序段。语语句是根据条件表达式的值决定执行相应的程序段。语法结构如下：法结构如下：IF THEN. ELSIF THEN. .ELSEEND IF;l其中其中ELSIF子句是可选项。子句是可选项。l注意是注意是ELSIF而不是而不是ELSE

16、IF条件语句条件语句IF-2l程序中说明一个整型变量程序中说明一个整型变量Number，使用，使用IF语句判断语句判断Number变量是正变量是正数、负数或数、负数或0。SET ServerOutPut ON;DECLARENumber INTEGER := -10;Begin IF Number 0 THEN dbms_output.put_line(正数正数); ELSE dbms_output.put_line(0); END IF;End;l执行效果如右图所示：执行效果如右图所示：分支语句分支语句CASE-1l分支语句是对指定的变量进行判断，从指定的列表中选择分支语句是对指定的变量进行

17、判断，从指定的列表中选择满足条件的行，并把该行的值作为满足条件的行，并把该行的值作为CASE语句的结果返回语句的结果返回。lCASE语句的语法结构如下：语句的语法结构如下：CASE WHEN THEN 值值1WHEN THEN 值值2.WHEN THEN 值值nELSE 值值n+1END;分支语句分支语句CASE-2l声明一个整型变量声明一个整型变量varDAY和一个字符型变量和一个字符型变量Result。使用。使用CASE语句判断语句判断varDAY是星期几。如果变量是星期几。如果变量varDAY在在17之间，则能够显示相应的星期信之间，则能够显示相应的星期信息，否则返回提示信息息，否则返回

18、提示信息“数据越界数据越界”；SET ServerOutPut ON;DECLARE varDAY INTEGER := 3; Result VARCHAR2(20);BEGIN Result :=Case varDAY WHEN 1 THEN 星期一 WHEN 2 THEN 星期二 WHEN 3 THEN 星期三 WHEN 4 THEN 星期四 WHEN 5 THEN 星期五 WHEN 6 THEN 星期六 WHEN 7 THEN 星期日 ELSE 数据越界 END; dbms_output.put_line(Result);END;案例案例-1：l使用简单的使用简单的case when e

19、lse end语句：语句：declare i int:=0;begin i:=(case when 1=1 then 111 else 222 end); dbms_output.put_line(i);end;在查询时使用简单的在查询时使用简单的Case：l在查询中使用在查询中使用case语句：语句：SQL select (case id when 2 then 222 else 33 end) from t6;SQL -上面的示例等于上面的示例等于SQL select (case when id=2 then 2222 else 333 end) from t6;循环语句循环语句LOOP.

20、EXIT.END-1l此语句的功能是重复执行循环体中的程序块，直到执行此语句的功能是重复执行循环体中的程序块，直到执行EXIT语句，则退出循环。语句，则退出循环。lLOOP.EXIT.END语句的语法结构如下语句的语法结构如下LOOP IF THEN EXIT END IF END LOOP;LOOP.EXIT.END LOOP-2l计算计算14累加累加SET ServerOutPut ON;DECLARE varNum INTEGER := 1; varSum INTEGER := 0;BEGIN LOOP varSum := varSum + varNum; dbms_output.put

21、_line(varNum); IF varNum = 4 THEN EXIT; END IF; dbms_output.put_line(+); varNum := varNum + 1; END LOOP; dbms_output.put_line(=); dbms_output.put_line(varSum);END;LOOP.EXIT WHEN.END LOOP-1l此循环语句的功能是重复执行循环体中的程序块，直到满此循环语句的功能是重复执行循环体中的程序块，直到满足足EXIT WHEN后面的判断语句，则退出循环。后面的判断语句，则退出循环。lLOOP.EXIT WHEN.END语句的

22、语法结构如下：语句的语法结构如下：LOOP EXIT WHEN END LOOP;LOOP.EXIT WHEN.END LOOP-2l重新实现重新实现14累加累加 SET ServerOutPut ON; DECLARE varNum INTEGER := 1; varSum INTEGER := 0; BEGIN LOOP varSum := varSum + varNum; dbms_output.put_line(varNum); EXIT WHEN varNum = 4; dbms_output.put_line(+); varNum := varNum + 1; END LOOP;

23、dbms_output.put_line(=); dbms_output.put_line(varSum); END;WHILE.LOOP.END LOOP-1l此语句的功能是当此语句的功能是当WHILE后面的语句条件成立时，重复执后面的语句条件成立时，重复执行循环体中的程序块。行循环体中的程序块。lWHILE.LOOP.END LOOP语句语法结构如下：语句语法结构如下：WHILE LOOP END LOOP;WHILE.LOOP.END LOOP-2l再次实现再次实现14累加累加SET ServerOutPut ON;DECLARE varNum INTEGER := 1; varSum

24、INTEGER := 0;BEGIN WHILE varNum = 4 LOOP varSum := varSum + varNum; dbms_output.put_line(varNum); IF varNum 4 THEN dbms_output.put_line(+); END IF; varNum := varNum + 1; END LOOP; dbms_output.put_line(=); dbms_output.put_line(varSum);END;FOR.IN.LOOP.END LOOP-1l此语句定义一个循环变量，并指定循环变量的初始值和终此语句定义一个循环变量，并指

25、定循环变量的初始值和终止值。每循环一次循环变量自动加止值。每循环一次循环变量自动加1.lFOR.IN.LOOP.END LOOP语句的语法如下语句的语法如下FOR IN . LOOP END LOOP;FOR.IN.LOOP.END LOOP-2l再次实现再次实现14累加累加SET ServerOutPut ON;DECLARE varNum INTEGER := 1; varSum INTEGER := 0;BEGIN FOR varNum IN 1.4 LOOP varSum := varSum + varNum; dbms_output.put_line(varNum); IF varN

26、um 4 THEN dbms_output.put_line(+); END IF; END LOOP; dbms_output.put_line(=); dbms_output.put_line(varSum);END;FOR.IN.LOOP 3:-for in loop使用变量使用变量lset serveroutput onldeclarel i integer:=1;l j integer:=10;l x integer:=0;lbeginl for x in i.j loop /i和和j都是变量都是变量l dbms_output.put_line(x);l end loop;lend;

27、异常处理异常处理lPL/SQL程序在运行过程中，可能会出现错误或异常现象程序在运行过程中，可能会出现错误或异常现象例如：无法建立到例如：无法建立到Oracle的连接或用的连接或用0做除数。好的程序应该对可做除数。好的程序应该对可能发生的异常情况进行处理，异常处理代码在能发生的异常情况进行处理，异常处理代码在EXCEPTION块中实块中实现现可以使用可以使用WHEN语句来定义异常。语句来定义异常。WHEN语句的使用方法如下：语句的使用方法如下：EXCEPTION WHEN THEN WHEN THEN . WHEN OTHERS THEN 预定义异常种类预定义异常种类异常异常说明说明ACCESS

28、_INTO_NULL在未初始化对象时出现在未初始化对象时出现CASE_NOT_FOUNF在在CASE语句中的选项与用户输入的数据不匹配时出现语句中的选项与用户输入的数据不匹配时出现COLLECTION_IS_NULL在给尚未初始化的表或数组赋值时出现在给尚未初始化的表或数组赋值时出现CURSOR_ALREADY_OPEN用户试图重新打开已经打开的游标时出现。在重新打开游标用户试图重新打开已经打开的游标时出现。在重新打开游标前必须先将其关闭前必须先将其关闭DUP_VAL_ON_INDEX用户试图将重复的值存储在使用唯一索引的数据库列中时出用户试图将重复的值存储在使用唯一索引的数据库列中时出现现I

29、NVALID_CURSOR在执行非法游标运算在执行非法游标运算(如如fetch一个尚未打开的游标一个尚未打开的游标)时出现时出现INVALID_NUMBER将字符串转换为数字时出现将字符串转换为数字时出现LOGIN_DENIED输入的用户名或密码无效时出现输入的用户名或密码无效时出现NO_DATA_FOUND在表中不存在请求的行时出现在表中不存在请求的行时出现SOTRAGE_ERROR在内存损坏或在内存损坏或PL/SQL耗尽内存时出现耗尽内存时出现TOO_MANY_ROWS在执行在执行SELECT INTO语句后返回多行时出现语句后返回多行时出现VALUE_ERROR在产生大小限制错误时出现。

30、如，变量中的列值超出变量的在产生大小限制错误时出现。如，变量中的列值超出变量的大小大小ZERO_DIVIDE以零做除数时出现以零做除数时出现示例示例1：l向一个向一个NUMBER类型的变量赋值字符串时，导致异常的发类型的变量赋值字符串时，导致异常的发生生SET ServerOutPut ON;DECLARE varNum NUMBER;BEGIN varNum := abc;EXCEPTION WHEN VALUE_ERROR THEN dbms_output.put_line(VALUE_ERROR);END;示例示例2：l使用使用SQLCODE , SQLERRM输出错误信息：输出错误信息

31、：lset serveroutput on;ldeclare l j integer:=0;lbeginl j:=Jack;l -如果出错，直接去异常处执行，以下行不会输出如果出错，直接去异常处执行，以下行不会输出l dbms_output.put_line(Value is setted);l exceptionl when others thenl -在异常中默认使用在异常中默认使用sqlerrm输出信息输出信息l dbms_output.put_line(SQLCODE|Other errors |sqlerrm);lend;用户自定义异常：用户自定义异常：l当与一个异常错误相关的错误出

32、现时，就会隐含触发该异当与一个异常错误相关的错误出现时，就会隐含触发该异常错误。用户定义的异常错误是通过显式使用常错误。用户定义的异常错误是通过显式使用 RAISE 语语句来触发。当引发一个异常错误时，控制就转向到句来触发。当引发一个异常错误时，控制就转向到 EXCEPTION块异常错误部分，执行错误处理代码。块异常错误部分，执行错误处理代码。 l对于这类异常情况的处理，步骤如下：对于这类异常情况的处理，步骤如下： 在在PL/SQL 块的定义部分定义异常情况：块的定义部分定义异常情况： exception;RAISE ； 在在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。块的异常

33、情况处理部分对异常情况做出相应的处理。 自定义异常示例：自定义异常示例：lset serveroutput on;ldeclarel myException EXCEPTION;-定义异常定义异常lbeginl update person set id=900 where id=901;l if SQL%NOTFOUND thenl RAISE myException;l end if;l EXCEPTIONl when myException thenl dbms_output.put_line(SQLCODE| Error |SQLERRM); lend;RAISE_APPLICATION

34、_ERROR过程过程 :lRAISE_APPLICATION_ERROR过程可以重新定义异常过程可以重新定义异常错误消息，它为应用程序提供了一种与错误消息，它为应用程序提供了一种与ORACLE交互的方交互的方法。法。 l语法如下：语法如下：RAISE_APPLICATION_ERROR(error_number,error_message,keep_errors ); 这里的这里的error_number 是从是从 20,000 到到 20,999 之间的参数。之间的参数。error_message 是相应的提示信息是相应的提示信息( 2048 字节字节).keep_errors 为可选，如果

35、为可选，如果keep_errors =TRUE ,则新错误将被添则新错误将被添加到已经引发的错误列表中。如果加到已经引发的错误列表中。如果keep_errors=FALSE(缺省缺省),则则新错误将替换当前的错误列表。新错误将替换当前的错误列表。 RAISE_APPLICATION_ERRROR示例：示例：lRAISE_APPLICATION_ERRROR示例：示例：set serveroutput on;declare myException EXCEPTION;begin update person set id=900 where id=901; if SQL%NOTFOUND then

36、 -调用系统的异常处理，抛出异常调用系统的异常处理，抛出异常 RAISE_APPLICATION_ERROR(-20001,can not found any row!); end if; EXCEPTION when myException then dbms_output.put_line(user errors); when others then dbms_output.put_line(Others |SQLCODE| |SQLERRM);end;用异常在用异常在PLSQL块中管理事务块中管理事务lset serveroutput on;lbegin l insert into pe

37、rson values(P007,Tom1);l insert into person values(P006,Tom4);l commit;-提交提交l exception l when others thenl rollback;-回滚回滚 l dbms_output.put_line(SQLCODE|SQLERRM);lend;小结小结lPL/SQL语言基本结构语言基本结构l变量和常量的声明变量和常量的声明l流程控制语句流程控制语句存储过程函数触发器游标存储过程存储过程存储过程l指存储在数据库中供所有用户程序调用的子程序叫存储过指存储在数据库中供所有用户程序调用的子程序叫存储过程。程。l

38、创建存储过程创建存储过程用用CREATE PROCEDURE命令建立存储过程和存储函数命令建立存储过程和存储函数语法：语法：create or replace PROCEDURE 过程名过程名(参数列表参数列表) AS PLSQL子程序体子程序体存储过程中可以接收的参数类型：存储过程中可以接收的参数类型：in类型为输入类型的参数类型为输入类型的参数 out类型为输出类型的参数。类型为输出类型的参数。存储过程调用存储过程调用set serveroutput onbegin raisesalary(7369);end;/set serveroutput onexec raisesalary(736

39、9);l 方法一：l 方法二：不接收参数的过程不接收参数的过程lcreate or replace procedure P1lislbegin dbms_output.put_line(Current date is:|to_char(sysdate,yyyy-mm-dd);lend;l-调用方法调用方法1lset serveroutput on;lexec P1();l-调用方法调用方法2lset serveroutput on;lbeginl p1();lend;接收输入类型的参数：接收输入类型的参数：l-声明接收参数的只声明类型，不声明大小声明接收参数的只声明类型，不声明大小lcreat

40、e or replace procedure pro2(p_id in varchar2,p_name in varchar2)laslbeginl insert into person values(p_id,p_name);lend;l-调用调用lset serveroutput on;lexec pro2(P100,Marray);接收输出类型的参数：接收输出类型的参数：l-select * from all_objects where owner=HR;lcreate or replace procedure pro4(pid in varchar,pnm in varchar,rco

41、unt out number)lasl rc number:=0;lbeginl -先写入数据先写入数据l insert into person values(pid,pnm);l commit;l select count(1) into rc from person;l rcount:=rc;lend;l-测试调用测试调用lset serveroutput on;ldeclarel rc number:=0;lbeginl -第三个参数为返回值的参数第三个参数为返回值的参数l pro4(P210,Jack,rc);l dbms_output.put_line(rc);lend;在过程中使用

42、游标在过程中使用游标create or replace procedure pro5lasl cursor c1 is select * from person;l v_p person%rowType;lbeginl open c1;l loopl fetch c1 into v_p;l exit when c1%notfound;l dbms_output.put_line(v_p.id|v_);l end loop;l if c1%isopen thenl close c1;l end if;lend;l-调用调用lset serveroutput on;lexec pro5

43、;函数函数存储函数存储函数l函数（函数（Function）为一命名的存储程序，可带参数，并返回一计算值）为一命名的存储程序，可带参数，并返回一计算值。函数和过程的结构类似，但必须有一个。函数和过程的结构类似，但必须有一个RETURN子句，用于返回函子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。数值。函数说明要指定函数名、结果值的类型，以及参数类型等。l建立存储函数的语法：建立存储函数的语法：CREATE OR REPLACE FUNCTION 函数名函数名(参数列表参数列表) RETURN 函数值类型函数值类型ASPLSQL子程序体；子程序体；函数示例：函数示例：l

44、create or replace function f1lreturn varchar2laslbeginl return hello;lend;l-调用调用lselect f1() from dual;函数的调用函数的调用declarev_sal number;beginv_sal:=queryEmpSalary(7934);dbms_output.put_line(salary is: | v_sal);end;或是直接使用select调用 begin dbms_output.put_line(salary is: | queryEmpSalary(7934); end;过程和函数中的过

45、程和函数中的in和和outl一般来讲，过程和函数的区别在于函数可以有一个返回值一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。；而过程没有返回值。lIn类型的参数，只可以接收值，不能再给类型的参数，只可以接收值，不能再给in类型的参数设类型的参数设置新的值。置新的值。l但过程和函数都可以通过但过程和函数都可以通过out指定一个或多个输出参数。指定一个或多个输出参数。我们可以利用我们可以利用out参数，在过程和函数中实现返回多个。参数，在过程和函数中实现返回多个。什么时候用存储过程什么时候用存储过程/存储函数？存储函数？l原则：原则：如果只有一个返回值，用存储函数；否则，就

46、用存储过程。如果只有一个返回值，用存储函数；否则，就用存储过程。函数示例函数示例2：l用函数去掉字段中多个重复的空格：用函数去掉字段中多个重复的空格：lcreate or replace function mtrim(str varchar2)lreturn varchar2lasl v_str varchar2(500):=;lbeginl select regexp_replace(str,(s)1,) into v_str from dual;l return v_str;lend;l-测试测试lselect mtrim(H A) from dual;触发器触发器/Trigger触发器触

47、发器l数据库触发器是一个与表相关联的、存储的数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个程序。每当一个特定的数据操作语句特定的数据操作语句(Insert,update,delete)在指定的表上发出时，在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。自动地执行触发器中定义的语句序列。l触发器的类型触发器的类型语句级触发器语句级触发器在指定的操作语句操作之前或之后执行一次，不管这条语句在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行影响了多少行 。行级触发器（行级触发器（FOR EACH ROW）触发语句作用的每一条记录都被触发。在行级触发器

48、中使用触发语句作用的每一条记录都被触发。在行级触发器中使用old和和new伪记录变量伪记录变量, 识别值的状态。识别值的状态。创建触发器创建触发器触发语句与伪记录变量的值触发语句与伪记录变量的值触发语句:old:newInsert将要插入的数据将要插入的数据Update更新以前该行的值更新以前该行的值更新后的值更新后的值delete删除以前该行的值删除以前该行的值示例：限制写入以示例：限制写入以J开始的串：开始的串：l-书写一个最基本的书写一个最基本的before triggerlcreate or replace trigger t1lbefore insert on person for

49、each rowlbeginl dbms_output.put_line(Hello|:NEW.id|:NEW.name);l if :NEW.name like J% thenl RAISE_APPLICATION_ERROR(-20000,限制写入以限制写入以J开始的串开始的串);l -在触发器中，不能写在触发器中，不能写commit或是或是rollbackl end if;lend;lset serveroutput on;linsert into person values(X790,Mack);For each row的含义：的含义：l添加添加for each row后，触发器，将变

50、为行级触发器：后，触发器，将变为行级触发器：l如：如：create or replace trigger trigger1 before update on person for each row declare begin dbms_output.put_line(Hello); end;如果如果person表中有四行，则会显示表中有四行，则会显示4个个hello.如果没有如果没有for each row 则只会显示一个则只会显示一个hello.只有在行级的触发器中，才可以使用只有在行级的触发器中，才可以使用:new,:old。示例示例2：限制修改的年龄大于以前的年龄限制修改的年龄大于以前的

51、年龄create or replace trigger t2before update on t6 for each rowbegin if :NEW.age:OLD.age then RAISE_APPLICATION_ERROR(-20000,年龄不能越来越小); end if;end;-写入小值就是出错update t6 set age=143;select * from t6;示例：示例：l同时使用同时使用insert,update,delete类型的触发器：类型的触发器：-在同一个表上，使用在同一个表上，使用insert,update,delete触发器触发器create or re

52、place trigger trigger1 before insert or update or delete on person declare begin dbms_output.put_line(hello); end;-以下都会引发上面的触发器以下都会引发上面的触发器set serveroutput on;insert into person values(1,Jack);set serveroutput on;update person set name=Tom;set serveroutput on;delete from person;示例：示例：l判断是何种类型的操作：判断是

53、何种类型的操作：l-同时声明同时声明insert,update,delete判断是何种操作判断是何种操作lcreate or replace trigger trigger1l before insert or update or deletel on person for each rowl declarel beginl if INSERTING thenl dbms_output.put_line(这是插入数据这是插入数据);l elsif DELETING thenl dbms_output.put_line(这是删除数据这是删除数据);l elsel dbms_output.put_

54、line(这是修改数据这是修改数据);l end if;l end;示例：示例：l触发器触发器_根据条件决定是否执行根据条件决定是否执行plsql块块create or replace trigger trigger1 before insert on person for each row when (NEW.id=8)-这儿是这样引用这儿是这样引用new declare begin -这儿前面必须要添加这儿前面必须要添加:new才可以引用才可以引用 dbms_output.put_line(这真是的是等于这真是的是等于|:new.id|:); end;示例：示例：l作用在某

55、个列上的作用在某个列上的create or replace trigger trigger1 -声明将这个触发器作用到某个列上声明将这个触发器作用到某个列上before update of name on personfor each rowDeclareBegin dbms_output.put_line(之前的值之前的值|:|现在的值现在的值:|:);end;示例：示例：l在触发器调用过程：在触发器调用过程：create or replace trigger tg1 before insert on person for each row declare v

56、_age number:=0; begin dbms_output.put_line(Trigger.); P1(v_age);-直接调用过程即可直接调用过程即可 dbms_output.put_line(age is:|v_age); end;触发器小总结触发器小总结l触发器可用于触发器可用于数据确认数据确认 实施复杂的安全性检查实施复杂的安全性检查做审计，跟踪表上所做的数据操作等做审计，跟踪表上所做的数据操作等数据的备份和同步数据的备份和同步l查询触发器、过程及函数查询触发器、过程及函数Select Select * * from from user_triggersuser_trigge

57、rs; ;Select Select * * from from user_sourceuser_source; ;游标游标游标：游标：l游标是游标是SQL的一个内存工作区，由系统或用户以变量的形的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的式定义。游标的作用就是用于临时存储从数据库中提取的数据块。数据块。 l游标分为三种类型：游标分为三种类型：隐式隐式Cursor显式显式CursorRef Cursor（动态（动态Cursor）。）。 隐式游标：隐式游标：l对于对于Select INTO语句，一次只能从数据库中获取到语句，一次只能从数据库中获取到一

58、条数据，对于这种类型的一条数据，对于这种类型的DML Sql语句，就是隐式语句，就是隐式Cursor。例如：。例如：Select /Update / Insert/Delete操作操作 。l作用：可以通过隐式作用：可以通过隐式Cusor的属性来了解操作的状态和结的属性来了解操作的状态和结果，从而达到流程的控制。果，从而达到流程的控制。l隐式隐式Cursor是系统自动打开和关闭是系统自动打开和关闭Cursor 。lCursor的属性包含：的属性包含： SQL%ROWCOUNT 整型整型 代表代表DML语句成功执行的数据行数。语句成功执行的数据行数。SQL%FOUND 布尔型布尔型 值为值为TRU

59、E代表插入、删除、更新或单行查代表插入、删除、更新或单行查询操作成功询操作成功 。SQL%NOTFOUND 布尔型布尔型 与与SQL%FOUND属性返回值相反。属性返回值相反。SQL%ISOPEN 布尔型布尔型 DML执行过程中为真，结束后为假执行过程中为真，结束后为假 。隐式游标示例隐式游标示例1：l用隐式的游标判断是否修改数据成功：用隐式的游标判断是否修改数据成功：lset serveroutput on;lbeginl update person set id=P003 where id=P010;l if SQL%FOUND thenl dbms_output.put_line(upd

60、ate successfull);l commit;l elsel dbms_output.put_line(update faulied);l end if;lend;隐式游标示例隐式游标示例2：l判断影响的行数：判断影响的行数：set serveroutput on;declare rowCount integer;begin update person set name=Jack; rowCount :=SQL%ROWCOUNT; -输出本次共影响几行输出本次共影响几行 dbms_output.put_line(Effected rows is:|rowCount);end;显式显式Cursor： l对