oracle 第15章 PLSQL程序设计ppt课件

1、Oracle 数据库基础教程2009整理ppt1第第15章章 PL/SQL程序设计程序设计Oracle 数据库基础教程2007整理ppt2本章内容本章内容pPL/SQL概述pPL/SQL基础p控制结构p游标p异常处理p存储子程序p包p触发器Oracle 数据库基础教程2007整理ppt3本章要求本章要求p掌握PL/SQL程序设计基础知识p掌握存储过程、函数、包、触发器的应用Oracle 数据库基础教程2007整理ppt415.1 PL/SQL概述概述pPL/SQL特点pPL/SQL功能特性pPL/SQL执行过程与开发工具Oracle 数据库基础教程2007整理ppt515.1.1PL/SQL特

2、点特点p与SQL语言紧密集成。p减小网络流量，提高应用程序的运行性能。p模块化的程序设计功能，提高了系统可靠性。p服务器端程序设计，可移植性好。Oracle 数据库基础教程2007整理ppt615.1.2PL/SQL功能特性功能特性p语句块结构p异常处理p变量和类型p条件语句p循环结构p游标p过程、函数和触发器p包p集合p动态SQLp对象特性Oracle 数据库基础教程2007整理ppt715.1.3PL/SQL执行过程与开发工具执行过程与开发工具PL/SQL块SQL语句客户端应用程序PL/SQL引擎数据库服务器过程化语句执行器SQL执行器块中SQL语句pPL/SQL执行过程 Oracle 数

3、据库基础教程2007整理ppt8pPL/SQL开发工具nSQL *PLUSnProcedure BuildernOracle Form、Oracle ReportsnPL/SQL DeveloperOracle 数据库基础教程2007整理ppt915.2 PL/SQL基础基础pPL/SQL程序结构 p词法单元 p数据类型p变量与常量p编译指示pPL/SQL中的SQL语句Oracle 数据库基础教程2007整理ppt10PL/SQL程序结构程序结构pPL/SQL块的组成pPL/SQL块分类 Oracle 数据库基础教程2007整理ppt11pPL/SQL块的组成nPL/SQL语言以块为单位，块中

4、可以嵌套子块。语言以块为单位，块中可以嵌套子块。 n一个基本的一个基本的PL/SQL块由块由3部分组成：部分组成：p声明（DECLARE），p可执行部分（BEGIN），p异常处理部分EXCEPTION）。n Oracle 数据库基础教程2007整理ppt12p声明部分n声明部分以关键字DECLARE开始，BEGIN结束。主要用于声明变量、常量、数据类型、游标、异常处理名称以及本地（局部）子程序定义等。 p可执行部分n执行部分是PL/SQL块的功能实现部分，以关键字BEGIN开始，EXCEPTION或END结束（如果PL/SQL块中没有异常处理部分，则以END结束）。该部分通过变量赋值、流程控制

5、、数据查询、数据操纵、数据定义、事务控制、游标处理等实现块的功能。p异常处理部分n异常处理部分以关键字异常处理部分以关键字EXCEPTION开始，开始，END结束。该部分用结束。该部分用于处理该块执行过程中产生的异常。于处理该块执行过程中产生的异常。 Oracle 数据库基础教程2007整理ppt13p注意：n执行部分是必需的，而声明部分和异常部分是执行部分是必需的，而声明部分和异常部分是可选的；可选的；n可以在一个块的执行部分或异常处理部分嵌套可以在一个块的执行部分或异常处理部分嵌套其他的其他的PL/SQL块；块；n所有的所有的PL/SQL块都是以块都是以“END;”结束结束 Oracle

6、数据库基础教程2007整理ppt14pPL/SQL块分类n匿名块匿名块n命名块命名块p函数p存储过程p包p触发器Oracle 数据库基础教程2007整理ppt1515.5.215.5.2词法单元词法单元p字符集p标识符p分隔符p常量值p注释Oracle 数据库基础教程2007整理ppt16p字符集n大小写字母：大小写字母：AZAZ，azazn数字：数字：0909n空白：制表符、空格和回车空白：制表符、空格和回车n数字符号：数字符号：+ - + - * * / / = =n标点符号：标点符号： ! # $ % &* （）（）_ | ？ ； ：, . “ Oracle 数据库基础教程200

7、7整理ppt17p标识符n标识符以字母开头，后边可以跟字母、数字、标识符以字母开头，后边可以跟字母、数字、货币符号、下划线和货币符号、下划线和”#” #” n标识符的最大长度为标识符的最大长度为3030字符，并且所有字符都字符，并且所有字符都是有效的。是有效的。p合法：X v_studentID TempVarp非法：X+y _temp Oracle 数据库基础教程2007整理ppt18p+p-p*p/p=p:=pp=pp!=p=p=p(p)p/*p*/pp%p;p:p.pp“p.pp|p=p*p-p分隔符分隔符Oracle 数据库基础教程2007整理ppt19p常量值n字符型常量字符型常量n

8、数字型常量数字型常量 n布尔型常量：布尔型常量：TURE、FALSE、NULLn日期型常量日期型常量Oracle 数据库基础教程2007整理ppt2015.2.3数据类型数据类型p数字类型p字符类型p日期/区间类型p行标识类型p布尔类型p原始类型pLOB类型p记录类型p集合类型Oracle 数据库基础教程2007整理ppt21PL/SQL中常用的基本数据类型分类数据类型数字类型NUMBER、BINARY_NUMBER PLS_NUMBER字符类型VARCHAR2、CHAR、LONG、NCHAR、NVARCHAR日期/区间类型 DATE、TIMESTAMP、INTERVAL行标识类型ROWID、

9、UROWID布尔类型BOOLEAN（TRUE、FALSE、NULL）原始类型RAW、LONG RAWLOB类型CLOB、BLOB、NCLOB、BFILE记录类型RECORD集合类型TABLE、VARRAYOracle 数据库基础教程2007整理ppt22p记录类型的定义nTYPE record_type IS RECORD(nfield1 datatype1 NOT NULLDEFAULT|:=expr1,nfield2 datatype2 NOT NULL DEFAULT|:=expr2,nnfieldn datatypen NOT NULL DEFAULT|:=exprn); Oracle

10、 数据库基础教程2007整理ppt2315.2.415.2.4变量与常量变量与常量p变量与常量的定义p变量的作用域Oracle 数据库基础教程2007整理ppt24变量声明变量与常量的定义变量与常量的定义p变量定义的一般格式：n CONSTANT NOT NULL DEFAULT|:= ;p说明n每行只能定义一个标识符。n如果加上关键字CONSTANT,则表示所定义的标识符为一个常量，必须为它赋初值。n如果定义的标识符不能为空，则必须加上关键字NOT NULL,并赋初值。n为标识符赋值时，使用赋值符号：=，默认值为空。Oracle 数据库基础教程2007整理ppt25pDECLAREp v1

11、NUMBER(4);p v2 NUMBER(4) NOT NULL :=10;p v3 CONSTANT NUMBER(4) DEFAULT 100;pBEGINp IF v1 IS NULL THEN p DBMS_OUTPUT.PUT_LINE(V1 IS NULL!);p END IF;p DBMS_OUTPUT.PUT_LINE(v2| |v3);pEND; Oracle 数据库基础教程2007整理ppt26p声明一个变量，使它的类型与某个变量或声明一个变量，使它的类型与某个变量或数据库基本表中某个列的数据类型一致，数据库基本表中某个列的数据类型一致，可以使用可以使用%TYPE。p示例

12、示例nv_empno1 emp.empno%TYPE;nv_empno2 v_empno1%TYPE;Oracle 数据库基础教程2007整理ppt27变量的作用域变量的作用域p变量的作用域是指变量的有效作用范围，从变量声明开始，直到块结束。p如果PL/SQL块相互嵌套，则在内部块中声明的变量是局部的，只能在内部块中引用，而在外部块中声明的变量是全局的，既可以在外部块中引用，也可以在内部块中引用。p如果内部块与外部块中定义了同名变量，则在内部块中引用外部块的全局变量时需要使用外部块名进行标识。 Oracle 数据库基础教程2007整理ppt28ppDECLAREp v_ename CHAR(1

13、5);p v_outer NUMBER(5);pBEGINp v_outer :=10;p DECLAREp v_ename CHAR(20); p v_inner DATE;p BEGINp v_inner:=sysdate;p v_ename:=INNER V_ENAME;p OUTER.v_ename:=OUTER V_ENAME;p END;p DBMS_OUTPUT.PUT_LINE(v_ename);pEND; Oracle 数据库基础教程2007整理ppt2915.2.5编译指示编译指示p编译指示是对编译程序发出的特殊指令，也称伪指令。n关键字：关键字：PRAGMApPL/SQL

14、提供以下四种编译指示：nEXCEPTION_INITp告诉编译程序将一个特定的错误号与程序中所声明的异常标识符关联起来。 nRESTRICT_REFERENCESp告诉编译程序打包程序的纯度，即对函数中可以使用的SQL语句和包变量进行限制。Oracle 数据库基础教程2007整理ppt30nSERIALLY_REUSEABLEp告诉PL/SQL运行时引擎，在数据引用之间不要保持包级数据。nAUTONOMOUS_TRANSACTIONp告诉编译程序，该程序块为自治事务，即该事务的提交和回滚是独立进行的。 Oracle 数据库基础教程2007整理ppt3115.2.6PL/SQL中中SQL语句语句

15、p可以在PL/SQL中执行的SQL语句包括nSELECT nDML(UPDATE、DELETE、INSERT)n事务控制语句（事务控制语句（COMMIT、ROLLBACK、SAVEPOINT）p注意DDL语句不可以直接使用Oracle 数据库基础教程2007整理ppt32pSELECTINTOnSELECTINTO语句只能查询一个记录的信语句只能查询一个记录的信息，如果没有查询到任何数据，则会产生息，如果没有查询到任何数据，则会产生NO_DATA_FOUND异常；如果查询到多个异常；如果查询到多个记录，则会产生记录，则会产生TOO_MANY_ROW异常。异常。nINTO句子后的变量用于接收查询

16、的结果，变句子后的变量用于接收查询的结果，变量的个数、顺序应该与查询的目标数据相匹配，量的个数、顺序应该与查询的目标数据相匹配，也可以是记录类型的变量。也可以是记录类型的变量。DML语句语句Oracle 数据库基础教程2007整理ppt33pDECLAREpv_emp emp%ROWTYPE;p v_ename emp.ename%type;p v_sal emp.sal%type;pBEGINpSELECT * INTO v_emp FROM emp WHERE ename=SMITH;p DBMS_OUTPUT.PUT_LINE(v_emp.empno| |v_emp.sal);p sel

17、ect ename,sal INTO v_ename,v_sal FROM emp WHERE empno=7900;p DBMS_OUTPUT.PUT_LINE(v_ename| |v_sal);pEND; Oracle 数据库基础教程2007整理ppt34pDML语句nPL/SQL中中DML语句对标准语句对标准SQL语句中的语句中的DML语句语句进行了扩展，允许使用变量。进行了扩展，允许使用变量。p 示例nDECLAREn v_empno emp.empno%TYPE :=7500;nBEGINn INSERT INTO emp(empno,ename,sal,deptno) VALUES

18、(v_empno,JOAN,2300,20);n UPDATE emp SET sal=sal+100 WHERE empno=v_empno;n DELETE FROM emp WHERE empno=v_empno;nEND; Oracle 数据库基础教程2007整理ppt35pWHEREn标识符的区分标识符的区分p系统首先查看WHERE子句中的标识符是否与表中的列名相同，如果相同，则该标识符被解释为列名；如果没有同名列，系统检查该标识符是不是PL/SQL语句块的变量。n字符串比较字符串比较p填充比较：通过在短字符串后添加空格，使两个字符串达到相同长度，然后根据每个字符的ASCII码进行比

19、较。p非填充比较：根据每个字符的ASCII码进行比较，最先结束的字符串为小。nPL/SQL中规定，对定长的字符串（中规定，对定长的字符串（CHAR类型的字类型的字符串和字符串常量）采用填充比较；如果比较的字符符串和字符串常量）采用填充比较；如果比较的字符串中有一个是变长字符串（串中有一个是变长字符串（VARCHAR2类型的字符类型的字符串），则采用非填充比较。串），则采用非填充比较。 Oracle 数据库基础教程2007整理ppt36pRETURNINGn如果要查询当前如果要查询当前DML语句操作的记录的信息，语句操作的记录的信息，可以在可以在DML语句末尾使用语句末尾使用RETURNING语

20、语句返回该记录的信息。句返回该记录的信息。nRETURNING语句的基本语法：语句的基本语法：pRETURNING select_list_item INTO variable_list|record_variable; Oracle 数据库基础教程2007整理ppt37nDECLAREn v_sal emp.sal%TYPE;nBEGINn UPDATE emp SET sal=sal+100 WHERE empno=7844 RETURNING sal INTO v_sal;n DBMS_OUTPUT.PUT_LINE(v_sal);nEND;Oracle 数据库基础教程2007整理ppt

21、3815.3 15.3 控制结构控制结构 p选择结构p循环结构p跳转结构Oracle 数据库基础教程2007整理ppt3915.3.1选择结构选择结构pIF语句语句nIF condition1 THEN statements1;nELSIF condition2 THEN statements2;nnELSE else_statements;nEND IF; p注意注意n条件是一个布尔型变量或表达式，取值只能是TRUE，FALSE，NULL。Oracle 数据库基础教程2007整理ppt40p例如，输入一个员工号，修改该员工的工资，如果该员工为10号部门，工资增加100；若为20号部门，工资增

22、加150；若为30号部门，工资增加200；否则增加300。 Oracle 数据库基础教程2007整理ppt41pDECLAREpv_deptno emp.deptno%type;p v_increment NUMBER(4);p v_empno emp.empno%type;pBEGINp v_empno:=&x;p SELECT deptno INTO v_deptno FROM emp WHERE empno=v_empno;p IF v_deptno=10 THEN v_increment:=100;p ELSIF v_deptno=20 THEN v_increment:=15

23、0;p ELSIF v_deptno=30 THEN v_increment:=200;p ELSE v_increment:=300;p END IF;p UPDATE emp SET sal=sal+v_increment WHERE empno=v_empno;pEND;Oracle 数据库基础教程2007整理ppt42搜索式搜索式CASE语句语句p基本语法基本语法nCASEn WHEN condition1 THEN statements1;n WHEN condition2 THEN statements2;n n WHEN conditionn THEN statementsn;n

24、 ELSE else_statements;nEND CASE;Oracle 数据库基础教程2007整理ppt43等值比较的等值比较的CASE语句语句p基本语法基本语法nCASE test_valuen WHEN value1 THEN statements1;n WHEN value2 THEN statements2;n n WHEN valuen THEN statementsn;n ELSE else_ statements;nEND CASE; Oracle 数据库基础教程2007整理ppt44pDECLAREp v_deptno emp.deptno%type;p v_increm

25、ent NUMBER(4);p v_empno emp.empno%type;pBEGINp v_empno:=&x;p SELECT deptno INTO v_deptno FROM emp WHERE empno=v_empno;p CASE v_deptno p WHEN 10 THEN v_increment:=100;p WHEN 20 THEN v_increment:=150;p WHEN 30 THEN v_increment:=200;p ELSE v_increment:=300;pEND CASE;pUPDATE emp SET sal=sal+v_increm

26、ent WHERE empno=v_empno; pEND; Oracle 数据库基础教程2007整理ppt45p根据输入的员工号，修改该员工工资。如果该员工工资低于1000，则工资增加200；如果工资在1000-2000之间，则增加150；如果工资在2000-3000之间，则增加100；否则增加50。 Oracle 数据库基础教程2007整理ppt46pDECLAREpv_sal emp.sal%type;pv_increment NUMBER(4);pv_empno emp.empno%type;pBEGINpv_empno:=&x;pSELECT sal INTO v_sal F

27、ROM emp WHERE empno=v_empno;pCASE p WHEN v_sal1000 THEN v_increment:=200;p WHEN v_sal2000 THEN v_increment:=150;p WHEN v_sal 50;p END LOOP;pEND; Oracle 数据库基础教程2007整理ppt50WHILE循环循环p基本语法 WHILE condition LOOP sequence_of_statement; END LOOP; Oracle 数据库基础教程2007整理ppt51p例如，利用WHILE循环向temp_table表中插入50条记录。程序

28、为：pDECLAREp v_counter BINARY_INTEGER :=1;pBEGINp WHILE v_counter = 50 LOOPp INSERT INTO temp_table VALUES (v_counter, Loop index);p v_counter := v_counter + 1;p END LOOP;pEND; Oracle 数据库基础教程2007整理ppt52FOR循环循环p基本语法 FOR loop_counter IN REVERSE low_bound.high_bound LOOP sequence_of_statement; END LOOP;

29、p注意：n循环变量不需要显式定义，系统隐含地将它声明为循环变量不需要显式定义，系统隐含地将它声明为BINARY_INTEGERBINARY_INTEGER变量；变量；n系统默认时，循环变量从下界往上界递增计数，如果系统默认时，循环变量从下界往上界递增计数，如果使用使用REVERSEREVERSE关键字，则表示循环变量从上界向下界递关键字，则表示循环变量从上界向下界递减计数；减计数；n循环变量只能在循环体中使用，不能在循环体外使用。循环变量只能在循环体中使用，不能在循环体外使用。Oracle 数据库基础教程2007整理ppt53p例如，利用FOR循环向temp_table表中插入50条记录。程序

30、为：pBEGINp FOR v_counter IN 1.50 LOOPp INSERT INTO temp_table VALUES (v_counter, Loop Index);p END LOOP;pEND;Oracle 数据库基础教程2007整理ppt5415.3.3跳转结构跳转结构n语法格式：标号标号GOTO 标号；标号；n说明：p块内可以跳转，内层块可以跳到外层块，但外层块不块内可以跳转，内层块可以跳到外层块，但外层块不能跳到内层。能跳到内层。pIF语句不能跳入。不能从循环体外跳入循环体内。语句不能跳入。不能从循环体外跳入循环体内。不能从子程序外部跳到子程序中。不能从子程序外部跳

31、到子程序中。p由于由于goto语句的缺点，建议尽量少用甚至不用语句的缺点，建议尽量少用甚至不用goto语句。语句。 Oracle 数据库基础教程2007整理ppt5515.4 15.4 游标游标p游标的概念及类型 p显式游标 p隐式游标Oracle 数据库基础教程2007整理ppt56pDECLAREp v_counter BINARY_INTEGER :=1;pBEGINp p INSERT INTO temp_table p VALUES (v_counter, Loop index);p v_counter := v_Counter + 1;p IF v_counter6000 THEN

32、 p RAISE e_highlimit;p END IF;pEXCEPTIONp WHEN e_highlimit THENp DBMS_OUTPUT.PUT_LINE(The salary is too large!);p ROLLBACK;pEND; Oracle 数据库基础教程2007整理ppt101OTHERS异常处理器异常处理器pOTHERS异常处理器是一个特殊的异常处理器，可以捕获所有的异常。p通常，OTHERS异常处理器总是作为异常处理部分的最后一个异常处理器，负责处理那些没有被其他异常处理器捕获的异常。 Oracle 数据库基础教程2007整理ppt102pDECLAREp

33、v_sal emp.sal%TYPE;p e_highlimit EXCEPTION;pBEGINp SELECT sal INTO v_sal FROM emp WHERE ename=JOAN;p UPDATE emp SET sal=sal+100 WHERE empno=7900;p IF v_sal6000 THEN p RAISE e_highlimit;p END IF;pEXCEPTIONp WHEN e_highlimit THENp DBMS_OUTPUT.PUT_LINE(The salary is too large!);p ROLLBACK;p WHEN OTHERS

34、 THENp DBMS_OUTPUT.PUT_LINE(There is some wrong in selecting!);pEND; Oracle 数据库基础教程2007整理ppt103p可以通过两个函数来获取错误相关信息。nSQLCODE：返回当前错误代码。：返回当前错误代码。p如果是用户定义错误返回值为1；p如果是ORA-1403：NO DATA FOUND错误，返回值为100；p其他Oracle内部错误返回相应的错误号。 nSQLERRM：返回当前错误的消息文本。：返回当前错误的消息文本。p如果是Oracle内部错误，返回系统内部的错误描述；p如果是用户定义错误，则返回信息文本为“U

35、ser-defined Exception”。 Oracle 数据库基础教程2007整理ppt104pDECLAREp v_sal emp.sal%TYPE;p e_highlimit EXCEPTION;p v_code NUMBER(6);p v_text VARCHAR2(200);pBEGINp SELECT sal INTO v_sal FROM emp WHERE ename=JOAN;p UPDATE emp SET sal=sal+100 WHERE empno=7900;p IF v_sal6000 THEN p RAISE e_highlimit;p END IF;pEXC

36、EPTIONp WHEN e_highlimit THENp DBMS_OUTPUT.PUT_LINE(The salary is too large!);p ROLLBACK;p WHEN OTHERS THENp v_code:=SQLCODE;p v_text:=SQLERRM; p DBMS_OUTPUT.PUT_LINE(v_code| |v_text);pEND; Oracle 数据库基础教程2007整理ppt10515.5.3异常的传播异常的传播p可执行部分异常的传播n如果当前语句块有该异常的处理器，则执行如果当前语句块有该异常的处理器，则执行之，并且成功完成该语句块。然后，控制

37、权之，并且成功完成该语句块。然后，控制权传递到外层语句块。传递到外层语句块。 n如果当前语句块没有该异常的处理器，则通如果当前语句块没有该异常的处理器，则通过在外层语句块中产生该异常来传播该异常。过在外层语句块中产生该异常来传播该异常。然后，执行对外层语句块执行步骤然后，执行对外层语句块执行步骤1。如果。如果没有外层语句块，则该异常将传播到调用环没有外层语句块，则该异常将传播到调用环境。境。 Oracle 数据库基础教程2007整理ppt106pDECLAREp v_sal emp.sal%TYPE;pBEGINp BEGIN p SELECT sal INTO v_sal FROM emp

38、WHERE ename=JOAN;p EXCEPTIONp WHEN NO_DATA_FOUND THEN p DBMS_OUTPUT.PUT_LINE(There is not such an employee!);p END;p DBMS_OUTPUT.PUT_LINE(Now this is outputted by outer block!);pEND;p/pThere is not such an employee!pNow this is outputted by outer block! Oracle 数据库基础教程2007整理ppt107pDECLAREp v_sal emp.

39、sal%TYPE;pBEGINp BEGIN p SELECT sal INTO v_sal FROM emp WHERE deptno=10;p EXCEPTIONp WHEN NO_DATA_FOUND THEN p DBMS_OUTPUT.PUT_LINE(There is not such an employee!);p END;p DBMS_OUTPUT.PUT_LINE(Now this is outputted by outer block!);pEXCEPTIONp WHEN TOO_MANY_ROWS THENp DBMS_OUTPUT.PUT_LINE(There are

40、more than one employee!);pEND;p/pThere are more than one employee!Oracle 数据库基础教程2007整理ppt108p声明部分异常的传播n声明部分的异常立刻传播到外层语句块，即声明部分的异常立刻传播到外层语句块，即使当前语句块有异常处理器。使当前语句块有异常处理器。 p异常处理部分的异常的传播n异常处理器中产生的异常，可以有异常处理器中产生的异常，可以有RAISE语句显式产生，也可以通过运行时错误而隐语句显式产生，也可以通过运行时错误而隐含产生。异常立即被传播到外层语句块。含产生。异常立即被传播到外层语句块。 Oracle 数

41、据库基础教程2007整理ppt109pBEGINp DECLAREpv_number NUMBER(6) :=ABC;pBEGINp v_number:=10;p EXCEPTIONp WHEN OTHERS THENp DBMS_OUTPUT.PUT_LINE(This is outputted by inner block!);p END;pEXCEPTIONpWHEN OTHERS THENp DBMS_OUTPUT.PUT_LINE(This is outputted by outer block!);pEND;p/pThis is outputted by outer block!

42、Oracle 数据库基础教程2007整理ppt11015.6 存储子程序存储子程序p存储过程p函数p局部子程序Oracle 数据库基础教程2007整理ppt111p存储子程序是指被命名的PL/SQL块，以编译的形式存储在数据库服务器中，可以在应用程序中进行调用，是PL/SQL程序模块化的一种体现。p存储子程序是以独立对象的形式存储在数据库服务器中，因此是一种全局结构，与之对应的是局部子程序，即嵌套在PL/SQL块中的局部过程和函数，其存储位置取决于其所在的父块的位置。 Oracle 数据库基础教程2007整理ppt112存储过程存储过程p存储过程的创建p存储过程的调用p存储过程的管理Oracl

43、e 数据库基础教程2007整理ppt113存储过程的创建存储过程的创建pCREATE OR REPLACE PROCEDURE procedure_namep(parameter1_name mode datatype DEFAULT|:=valuep, parameter2_name mode datatype DEFAULT|:=value,)pAS|ISp /*Declarative section is here */pBEGINp /*Executable section is here*/ pEXCEPTIONp /*Exception section is here*/ pEND

44、procedure_name; PROCEDURE BODYOracle 数据库基础教程2007整理ppt114p参数模式nIN 当过程被调用时，实参值被传递给过程。在过程内，该参数起常数 作用，可读不可写。调用结束，实参值不变。（默认参数类型）nOUT 当过程被调用时，实参值被忽略。在过程内，该参数起未初始化的变量作用，值为NULL。过程内，该参数可读可写。 调用结束，形参赋给实参。nIN OUT 当过程被调用时，实参值被传递给过程。在过程内，该参数起已初始化变量作用，过程内，该参数可读可写。调用结束，形参赋给实参。Oracle 数据库基础教程2007整理ppt115p参数限制n声明形参时不

45、能定义形参的长度或精度、刻度声明形参时不能定义形参的长度或精度、刻度 p参数传递nIN参数为引用传递，即实参的指针被传递给形参数为引用传递，即实参的指针被传递给形参；参；nOUT、IN OUT参数为值传递，即实参的值被参数为值传递，即实参的值被复制给形参。复制给形参。 Oracle 数据库基础教程2007整理ppt116p创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。pCREATE OR REPLACE PROCEDURE show_emp(pp_deptno emp.deptno%TYPE)pASp v_sal emp.sal%TYPE;

46、pBEGINpSELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno;pDBMS_OUTPUT.PUT_LINE(p_deptno| |average salary is:|v_sal);pFOR v_emp IN (SELECT * FROM emp WHERE deptno=p_deptno AND salv_sal) LOOPp DBMS_OUTPUT.PUT_LINE(v_emp.empno| |v_emp.ename);pEND LOOP;pEXCEPTIONp WHEN NO_DATA_FOUND THENp DBMS_O

47、UTPUT.PUT_LINE(The department doesnt exists!);pEND show_emp;Oracle 数据库基础教程2007整理ppt117p通常，存储过程不需要返回值，如果需要返回一个值可以通过函数调用实现。但是，如果希望返回多个值，可以使用OUT或IN OUT模式参数来实现。Oracle 数据库基础教程2007整理ppt118p创建一个存储过程，以部门号为参数，返回该部门的人数和最高工资。pCREATE OR REPLACE PROCEDURE return_deptinfo(pp_deptno emp.deptno%TYPE,pp_avgsal OUT e

48、mp.sal%TYPE,pp_count OUT emp.sal%TYPE)pASpBEGINp SELECT avg(sal),count(*) INTO p_avgsal,p_count p FROM emp p WHERE deptno=p_deptno;pEXCEPTIONp WHEN NO_DATA_FOUND THENp DBMS_OUTPUT.PUT_LINE(The department dont exists!);pEND return_deptinfo; Oracle 数据库基础教程2007整理ppt119存储过程的调用存储过程的调用p在SQL*PLUS中调用nEXEC p

49、rocedure_name(parameter_list)nEXECUTE show_emp(10)p在PL/SQL块中调用nBEGIN procedure_name(parameter_list);nEND；Oracle 数据库基础教程2007整理ppt120pDECLAREp v_avgsal emp.sal%TYPE;p v_count NUMBER;pBEGINp show_emp(20);p return_deptinfo(10,v_avgsal,v_count);p DBMS_OUTPUT.PUT_LINE(v_avgsal| |v_count);pEND; Oracle 数据库基

50、础教程2007整理ppt121存储过程的管理存储过程的管理p修改存储过程nCREATE OR REPLACE PROCEDURECREATE OR REPLACE PROCEDUREp重新编译存储过程nALTER PROCEDURE procedure_name COMPILEALTER PROCEDURE procedure_name COMPILE；p删除存储过程nDROP PROCEDURE procedure_nameDROP PROCEDURE procedure_name名；名；p查看过程源代码nselect text from user_source where name=pro

51、cedure_nameprocedure_name ;Oracle 数据库基础教程2007整理ppt12215.6.2函数函数p函数概述p函数的创建p函数的调用p函数的管理Oracle 数据库基础教程2007整理ppt123函数概述函数概述p函数用于返回特定数据，可以返回一个或多个值。p在一个函数中必须包含一个或多个RETURN 语句p函数调用是PL/SQL表达式的一部分，而过程调用可以是一个独立的PL/SQL语句 Oracle 数据库基础教程2007整理ppt124函数的创建函数的创建CREATE OR REPLACE FUNCTION function_name (parameter1_n

52、ame mode datatype DEFAULT|:=value, parameter2_name mode datatype DEFAULT|:=value,)RETURN return_datatype AS |IS /*Declarative section is here */BEGIN /*Executable section is here*/ EXCEPTION /*Exception section is here*/ END function_name;FUNCTION BODYOracle 数据库基础教程2007整理ppt125p创建一个以部门号为参数，返回该部门最高工资

53、的函数。pCREATE OR REPLACE FUNCTION return_maxsalp(p_deptno emp.deptno%TYPE)pRETURN emp.sal%TYPEpASp v_maxsal emp.sal%TYPE;pBEGINp SELECT max(sal) INTO v_maxsal FROM emp p WHERE deptno=p_deptno;p RETURN v_maxsal;pEXCEPTION p WHEN NO_DATA_FOUND THENp DBMS_OUTPUT.PUT_LINE(The deptno is invalid!);pEND retu

54、rn_maxsal;Oracle 数据库基础教程2007整理ppt126函数的调用函数的调用p在SQL语句中调用函数p在PL/SQL中调用函数Oracle 数据库基础教程2007整理ppt127pDECLAREp v_sal emp.sal%TYPE;pBEGINp FOR v_dept IN (SELECT DISTINCT deptno FROM emp) p LOOPp v_sal:=return_maxsal(v_dept.deptno);p DBMS_OUTPUT.PUT_LINE(v_dept.deptno| |v_sal);p END LOOP;pEND; Oracle 数据库基

55、础教程2007整理ppt128函数的管理函数的管理p修改函数nCREATE OR REPLACE FUNCTION function_nameCREATE OR REPLACE FUNCTION function_namep重新编译存储过程nALTER FUNCTION function_name COMPILEALTER FUNCTION function_name COMPILE；p删除存储过程nDROP FUNCTION function_name DROP FUNCTION function_name ；p查看过程源代码nselect text from user_source whe

56、re name= function_namefunction_name;Oracle 数据库基础教程2007整理ppt12915.6.3局部子程序局部子程序p局部子程序n嵌套在其他嵌套在其他PL/SQL块中的子程序。块中的子程序。n只能在其定义的块内部被调用，而不能在其父块外被调用。只能在其定义的块内部被调用，而不能在其父块外被调用。p使用局部子程序时需要注意：n局部子程序只在当前语句块内有效；局部子程序只在当前语句块内有效；n局部子程序必须在局部子程序必须在PL/SQL块声明部分的最后进块声明部分的最后进行定义；行定义；n局部子程序必须在使用之前声明，如果是子程序局部子程序必须在使用之前声明

57、，如果是子程序间相互引用，则需要采用预先声明；间相互引用，则需要采用预先声明；n局部子程序可以重载。局部子程序可以重载。Oracle 数据库基础教程2007整理ppt130p在一个块内部定义一个函数和一个过程。函数以部门号为参数返回该部门的平均工资；过程以部门号为参数，输出该部门中工资低于部门平均工资的员工的员工号、员工名。 Oracle 数据库基础教程2007整理ppt131pDECLAREp v_deptno emp.deptno%TYPE;p v_avgsal emp.sal%TYPE;p FUNCTION return_avgsal(p_deptno emp.deptno%TYPE)p

58、 RETURN emp.sal%TYPEp ASp v_sal emp.sal%TYPE; p BEGINp SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno;p RETURN v_sal;p END return_avgsal; p PROCEDURE show_emp(p_deptno emp.deptno%TYPE)p ASp CURSOR c_emp IS p SELECT * FROM emp WHERE salreturn_avgsal(p_deptno);p BEGINp FOR v_emp IN c_emp LO

59、OPp DBMS_OUTPUT.PUT_LINE(v_emp.empno| |v_emp.ename);p END LOOP;p END show_emp;pBEGINp v_deptno:=&x;p v_avgsal:=return_avgsal(v_deptno);p show_emp(v_deptno);pEND;Oracle 数据库基础教程2007整理ppt132p存储子程序与局部子程序区别在于：n存储子程序己经编译好放在数据库服务器端，存储子程序己经编译好放在数据库服务器端，可以直接调用，而局部子程序存在于定义它的可以直接调用，而局部子程序存在于定义它的语句块中，在运行时先进

60、行编译；语句块中，在运行时先进行编译；n存储子程序不能重载，而局部子程序可以进行存储子程序不能重载，而局部子程序可以进行重载；重载；n存储子程序可以被任意的存储子程序可以被任意的PL/SQL块调用，而块调用，而局部子程序只能在定义它的块中被调用。局部子程序只能在定义它的块中被调用。Oracle 数据库基础教程2007整理ppt133p在一个PL/SQL块中重载两个过程，一个以员工号为参数，输出该员工信息；另一个以员工名为参数，输出员工信息。利用这两个过程分别查询员工号为7902，7934，以及员工名为SMITH，FORD的员工信息 。Oracle 数据库基础教程2007整理ppt134pDECLAREp PROCEDURE sh