《oracle补充》PPT课件.ppt

《《oracle补充》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《oracle补充》PPT课件.ppt（88页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、PL-SQL编程目标q 掌握如何定义变量并赋值q 掌握如何输出显示数据q 掌握IF、WHILE、CASE逻辑控制语句2PL/SQL块 qPL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。qPL/SQL块的结构如下：块的结构如下：qDECLARE q/*声明部分:在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数*/qBEGINq /*执行部分:过程及SQL 语句 ,即程序的主要部分 */qEXCEPTIONq /*执行异常部分:错误处理 */qEND;q其中 执行部分是必须的。3注释 q 在PL/SQL里，可以使用两种符号来写注释，即：q使用双-(减号)加注释

2、qPL/SQL允许用 来写注释，它的作用范围是只能在一行有效。如：qV_Sal NUMBER(12,2);-工资变量。q使用/*/来加一行或多行注释，如：q/*/q/*文件名：statistcs_sal.sql */q/*/4PL/SQL 变量类型 qPL/SQL支持多种数据类型，本节讨论代码中最常用并且最实用的数据类型。qVarchar2qNumberqDateqBoolean5Varchar2qVarchar2为可变长的字母数字数据类型。在PL/SQL中，varchar2的最大长度为32767字节。q在declare部分，其定义以分号(；)结束，所有varchar2变量的定义类似于下面的定

3、义：qvariable_name varchar2(max_length);q括号中的长度值为本变量的最大长度且必须是正整数，如：qvc_field varchar2(10);q在定义变量时，可以同时对其进行初始化，格式为：qvc_field varchar2(10):=STARTVALUE;6Numberqnumber类型数据可用来表示所有的数值数据，说明格式为：qnum_field number(precision,scale);q其中，precision可以有138个数字位，而scale表示在precision位数字中小数点后的数字位数，下面的定义：qnum_fieldnumber(12

4、,2);q表示num_field是一个整数部分最多10位、小数部分最多2位的变量。7Dateq此数据类型用于保存固定长度的日期值，date变量的说明为：qdate_field date;q在缺省时，Oracle以DD-MON-YY格式显示日期。因此，2004年9月9日显示为09-SEP-04。qPL/SQL中编程处理日期必须使用这种格式。是否可以说用户必须用数据库缺省的日期格式，除非用户用TO_CHAR或TO_DATE是否能提醒用户用自己的TO_DATE/TO_CHAR定义的格式而不依赖于缺省的日期格式。qq格式由初始化参数格式由初始化参数格式由初始化参数格式由初始化参数nls_date_fo

5、rmatnls_date_format设置设置设置设置也可通过也可通过也可通过也可通过setset设置设置设置设置 alter session set alter session set nls_date_formatnls_date_format=yyyy-mm-ddyyyy-mm-dd hh24:mi:ss hh24:mi:ss8Booleanq这种数据类型只有两个值：true或false。在使用boolean变量时，如果测试结果为true，则做某事，否则做另外的事。例如，如果试图考察某公司是否分发了一个10KB的2000年年度预算表，可用boolean变量。若该公司分发了此表，则该变量值

6、为true。q布尔值只有TRUE,FALSE及 NULL 三个值 9类型类型子类子类说说 明明范范 围围ORACLE限制限制CHARCharacterStringRowidNchar定长字符串定长字符串民族语言字符集民族语言字符集032767可选可选,确省确省=12000VARCHAR2Varchar,StringNVARCHAR2可变字符串可变字符串民族语言字符集民族语言字符集03276740004000BINARY_INTEGER带符号整数带符号整数,为整数计算优化性为整数计算优化性能能NUMBER(p,s)DecDouble precisionIntegerIntNumericRealS

7、mall int小数小数,NUMBER 的子类型的子类型高精度实数高精度实数整数整数,NUMBER 的子类型的子类型整数整数,NUMBER 的子类型的子类型与与NUMBER等价等价与与NUMBER等价等价整数整数,比比 integer 小小LONG变长字符串变长字符串0-214748364732,767字节字节DATE日期型日期型公元前公元前4712年年1月月1日至公元后日至公元后4712年年12月月31日日BOOLEAN布尔型布尔型TRUE,FALSE,NULL不使用不使用ROWID存放数据库行号存放数据库行号UROWID通用行标识符，字符类型通用行标识符，字符类型10变量q（1）声明变量q

8、 声明变量的语句格式如下：qVariable_name CONSTANT datatype NOT NULL:=|DEFAULT qexpressionq 注意：可以在声明变量的同时给变量强制性的加上NOT NULL约束条件，此时变量在初始化时必须赋值。q（2）给变量赋值q 给变量赋值有两种方式：q 直接给变量赋值q 通过SQL SELECT INTO 给变量赋值11qv_ename varchar2(10);qv_sal number(6,2);qv_tax_rate constant number(3,2):=5.5qv_hiredate DATE;12qdeclareqx varchar

9、(20);qbeginqx:=This is.;qdbms_output.put_line(x:|x);qend;13q 数据库赋值是通过 SELECT语句来完成的，每次执行 SELECT语句就赋值一次，一般要求被赋值的变量与SELECT中的列名要一一对应。qDECLAREqemp_id emp.empno%TYPE;qemp_name emp.ename%TYPE;qwages emp.sal%TYPE;qBEGINq Emp_id:=7788;qSELECT ename,NVL(sal,0)+NVL(comm,0)INTO emp_name,wages qFROM emp WHERE em

10、pno=emp_id;qDbms_output.put_line(emp_name|-|to_char(wages);qEND;14 可转换的类型赋值 qCHAR 转换为转换为 NUMBER：q使用 TO_NUMBER 函数来完成字符到数字的转换，如：qv_total :=TO_NUMBER(100.0)+sal;qNUMBER 转换为转换为CHAR：q 使用 TO_CHAR函数可以实现数字到字符的转换，如：qv_comm:=TO_CHAR(123.45)|元;q字符转换为日期：字符转换为日期：q使用 TO_DATE函数可以实现 字符到日期的转换，如：qv_date:=TO_DATE(2001

11、.07.03,yyyy.mm.dd);q日期转换为字符日期转换为字符q使用 TO_CHAR函数可以实现日期到字符的转换，如：qv_to_day:=TO_CHAR(SYSDATE,yyyy.mm.dd hh24:mi:ss);15q使用代替变量输入雇员编号，分别保存员工名，工资，显示输出各变量的值qdeclareqv_name varchar2(5);qv_sal number(6,2);qbeginqselect ename,sal into v_name,v_sal from emp where empno=&no;qdbms_output.put_line(name:|v_name);qd

12、bms_output.put_line(sal:|v_sal);qend;q/16练习q使用代替变量输入雇员编号，分别保存员工名，工资，个人所得税，显示输出各变量的值q其中个人所得数计算方式工资的3%17使用使用%TYPEq%TYPE%TYPE：使一个变量的数据类型与另一个变量：使一个变量的数据类型与另一个变量：使一个变量的数据类型与另一个变量：使一个变量的数据类型与另一个变量（尤其是表的列）数据类型一致，（尤其是表的列）数据类型一致，（尤其是表的列）数据类型一致，（尤其是表的列）数据类型一致，oracleoracle提供的提供的提供的提供的一种定义方式一种定义方式一种定义方式一种定义方式18

13、复合变量q ORACLE 在 PL/SQL 中除了提供象前面介绍的各种类型外,还提供一种称为复合类型的类型-记录和表 q定义记录类型语法如下定义记录类型语法如下:qTYPE record_type IS RECORD(q Field1 type1 NOT NULL :=exp1,q Field2 type2 NOT NULL :=exp2,q .q Fieldn typen NOT NULL :=expn );19qDECLARE q TYPE test_rec IS RECORD(q Code VARCHAR2(10),q Name VARCHAR2(30)NOT NULL:=a book)

14、;q V_book test_rec;qBEGINq V_book.code:=123;q V_book.name:=C+Programming;q DBMS_OUTPUT.PUT_LINE(v_book.code|v_book.name);qEND;20%ROWTYPEq%ROWTYPE%ROWTYPE：使一个变量的数据类型与参照表的数：使一个变量的数据类型与参照表的数：使一个变量的数据类型与参照表的数：使一个变量的数据类型与参照表的数据类型一致，据类型一致，据类型一致，据类型一致，oracleoracle提供的一种定义方式提供的一种定义方式提供的一种定义方式提供的一种定义方式21PL/SQ

15、L语法语法qRECORDRECORD：记录类型。类似于：记录类型。类似于：记录类型。类似于：记录类型。类似于c c语言中的语言中的语言中的语言中的structurestructure22插入语句qdeclareqv_deptno dept.deptno%type;qv_dname dept.dname%type;qbeginqv_deptno:=&no;qv_dname:=&name;qinsert into dept(deptno,dname)values(v_deptno,v_dname);qend;q/23使用子查询插入qdeclareqv_deptno dept.deptno%type

16、:=&no;qbeginqinsert into employee select*from emp where detno=v_deptno;qend;q/24更新数据q使用表达式更新列q使用子查询更新列25删除记录q使用变量删除数据q使用子查询删除记录qdeclareqv_ename emp.ename%type:=&name;qbeginqupdate emp set(sal,comm)=q(select sal+100,comm+100 from emp where ename=v_name)qwher job=(select job from emp where ename=v_ena

17、me)qend;q/26PL/SQL结构控制语句结构控制语句qqIf If语句语句语句语句if then;if then;else;else;end ifend ifif if 条件表达式条件表达式条件表达式条件表达式1 then;1 then;elsifelsif 条件表达式条件表达式条件表达式条件表达式2 then;2 then;elsifelsif 条件表达式条件表达式条件表达式条件表达式3 then;3 then;else;else;end ifend ifqq例例例例27SQL get E:SQL get E:jhrdocumentjhrdocument 备课备课备课备课oracle

18、oracle脚本脚本脚本脚本if1.txtif1.txt 1 declare 1 declare 2 2 v_scorev_score integer;integer;3 begin 3 begin 4 select score into 4 select score into v_scorev_score 5 from 5 from courseadmin.courseresultscourseadmin.courseresults 6 where 6 where sidsid=200301 and cid=1;=200301 and cid=1;7 if 7 if v_scorev_sco

19、re=60 then=60 then 8 8 dbms_output.put_linedbms_output.put_line(及格及格及格及格););9 else 9 else 10 10 dbms_output.put_linedbms_output.put_line(不及格不及格不及格不及格););11 end if;11 end if;12*end;12*end;SQL/SQL/及格及格及格及格28PL/SQL结构控制语句结构控制语句qqCaseCase结构结构结构结构qqCase Case 变量或表达式变量或表达式变量或表达式变量或表达式 when when 条件表达式条件表达式条件

20、表达式条件表达式1 then;1 then;when when 条件表达式条件表达式条件表达式条件表达式2 then;2 then;when when 条件表达式条件表达式条件表达式条件表达式3 then;3 then;else;else;end caseend case；qq例例例例29SQL get E:jhrdocument备课oracle脚本case.txt 1 declare 2 v_score integer;3 begin 4 select score into v_score 5 from courseadmin.courseresults 6 where sid=200301

21、 and cid=1;7 case 8 when v_score=90 then dbms_output.put_line(优秀);9 when v_score=80 and v_score=70 and v_score=60 and v_score/中等30课堂练习课堂练习：课堂练习：请根据平均分和下面的评分规则，编写请根据平均分和下面的评分规则，编写PL-SQL语句输入学生学号查询学语句输入学生学号查询学员的成员的成 绩，如上图所示。绩，如上图所示。优优：90分以上分以上 良良：8089分分 中中：7079分分 差差：6069分分 不及格不及格：60分以下分以下如果输入如果输入s25303

22、，则显示，则显示该学生成绩：中该学生成绩：中学员成绩分析学员成绩分析31PL/SQL结构控制语句结构控制语句qqLoopLoopqqLoopLoopexit exit /exit when/exit when 条件表达式条件表达式条件表达式条件表达式循环体循环体循环体循环体end loopend loopqq例子例子例子例子3233PL/SQL结构控制语句结构控制语句qqWhileWhileWhileWhile语句语句语句语句qqWhile While While While 条件表达式条件表达式条件表达式条件表达式 looplooplooploop循环体循环体循环体循环体end loopen

23、d loopend loopend loopqq例子例子例子例子 declare i integer;declare i integer;declare i integer;declare i integer;begin i:=1;begin i:=1;begin i:=1;begin i:=1;while i 100 loop while i 100 loop while i 100 loop while i 100 loop i:=i+1;i:=i+1;i:=i+1;i:=i+1;end loop;end loop;end loop;end loop;dbms_output.put_lin

24、e(idbms_output.put_line(idbms_output.put_line(idbms_output.put_line(i););););end;end;end;end;34PL/SQL结构控制语句结构控制语句qqFor For 语句语句语句语句qqFor For 循环变量循环变量循环变量循环变量 in in 初值表达式初值表达式初值表达式初值表达式.终值表达式终值表达式终值表达式终值表达式 looploop循环体循环体循环体循环体end loopend loopqq例例例例 declare declare vsumvsum integer;integer;begin begi

25、n vsumvsum:=0;:=0;for i in 1.100 loop for i in 1.100 loop vsumvsum:=:=vsum+ivsum+i;end loop;end loop;dbms_output.put_line(vsumdbms_output.put_line(vsum););end;end;35标号和GOTO qPL/SQL中中GOTO语句是无条件跳转到指定的标号语句是无条件跳转到指定的标号去的意思。语法如下：去的意思。语法如下：qGOTO label;q.q/*标号是用标号是用括起来的标识符括起来的标识符*/36qdeclareqx number;qbegi

26、nqx:=0;qqx:=x+1;qdbms_output.put_line(x);qif x 10 THENq GOTO l_ENDofLOOP;q END IF;q END LOOP;q q DBMS_OUTPUT.PUT_LINE(V_counter的当前值为:|V_counter);qEND;38NULL 语句 q在PL/SQL 程序中，可以用 null 语句来说明“不用做任何事情”的意思，相当于一个占位符，可以使某些语句变得有意义，提高程序的可读性。如：qDECLAREq.qBEGINqqIF v_num IS NULL THENqGOTO print1;qEND IF;qqqNULL

27、;-不需要处理任何数据。qEND;39练习q创建一个表temp_table，其中只有一个字段num_col 类型numberq用loop循环插入编号为1-10的记录q用while循环插入编号11-20的记录q用for循环插入编号21-30的记录q用for循环插入编号40-31的记录q用goto语句循环插入41-50的记录40游标41q游标有两种类型：显式游标和隐式游标。q在前述程序中用到的SELECT.INTO.查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。q但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处

28、理。显式游标对应一个返回结果为多行多列的SELECT语句。q游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。什么是游标？42隐式游标q如前所述，DML操作和单行SELECT语句会使用隐式游标，它们是：q插入操作：INSERT。q更新操作：UPDATE。q删除操作：DELETE。q单行查询操作：SELECT.INTO.。q当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。q隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通

29、常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。游标的属性有四种，如表7-1所示。43隐式游标属性44q使用隐式游标的属性，判断对雇员工资的修改是否成功。SET SERVEROUTPUT ON BEGINUPDATE emp SET sal=sal+100 WHERE empno=1234;IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(成功修改雇员工资！);COMMIT;ELSEDBMS_OUTPUT.PUT_LINE(修改雇员工资失败！);END IF;END;隐式游标例子45显式游标q游标的使用分成以下4个步骤。q1声明游标在DECLARE部分按以下

30、格式声明游标：CURSOR 游标名游标名(参数参数1 数据类型数据类型，参数，参数2 数据类型数据类型.)IS SELECT语句语句;q参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。qSELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。46p2打开游标 在可执行部分，按以下格式打开游标：OPEN 游标名(实际参数1，实际参数2.);p打开游标时，SELECT语句的

31、查询结果就被传送到了游标工作区。q3提取数据q在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。qFETCH 游标名 INTO 变量名1，变量名2.;或 FETCH 游标名 INTO 记录变量;q游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。显式游标47q获取数据格式：q第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。q第二种格式一次将一行数据取到记录变量中，需要使

32、用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。q定义记录变量的方法如下：q其中的表必须存在，游标名也必须先定义。q变量名 表名|游标名%ROWTYPE；q4关闭游标 CLOSE 游标名;q显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。游标48显式游标显式游标q声明游标声明游标声明游标声明游标 cursor cursor cursor cursor 游标名游标名游标名游标名 is selectis selectis selectis select语句语句语句语句q打开游标打开游标打开游标打开游

33、标 open open open open 游标名游标名游标名游标名q提取游标提取游标提取游标提取游标 fetch fetch fetch fetch 游标名游标名游标名游标名 into into into into 变量表变量表变量表变量表q关闭游标关闭游标关闭游标关闭游标 close close close close 游标名游标名游标名游标名49显式游标属性虽然可以使用前面的形式获得游标数据，但是在游标定义以后使用它的一些属性来进行结构控制是一种更为灵活的方法。显式游标的属性如表所示。q游标名%属性q要判断游标emp_cursor是否处于打开状态，可以使用属性emp_cursor%ISO

34、PEN。如果游标已经打开，则返回值为“真”，否则为“假”。具体可参照以下的训练。50q【1】用游标提取emp表中7788雇员的名称和职务。SET SERVEROUTPUT ONDECLAREv_ename VARCHAR2(10);v_job VARCHAR2(10);CURSOR emp_cursor IS SELECT ename,job FROM emp WHERE empno=7788;BEGINOPEN emp_cursor;FETCH emp_cursor INTO v_ename,v_job;DBMS_OUTPUT.PUT_LINE(v_ename|,|v_job);CLOSE

35、emp_cursor;END;例子151【训练2】用游标提取emp表中7788雇员的姓名、职务和工资。SET SERVEROUTPUT ONDECLARECURSOR emp_cursor IS SELECT ename,job,sal FROM emp WHERE empno=7788;emp_record emp_cursor%ROWTYPE;BEGINOPEN emp_cursor;FETCH emp_cursor INTO emp_record;DBMS_OUTPUT.PUT_LINE(emp_record.ename|,|emp_record.job|,|emp_record.sal

36、);CLOSE emp_cursor;END;例子252【训练3】显示工资最高的前3名雇员的名称和工资。SET SERVEROUTPUT ONDECLAREV_ename VARCHAR2(10);V_sal NUMBER(5);CURSOR emp_cursor IS SELECT ename,sal FROM emp ORDER BY sal DESC;BEGINOPEN emp_cursor;FOR I IN 1.3 LOOPFETCH emp_cursor INTO v_ename,v_sal;DBMS_OUTPUT.PUT_LINE(v_ename|,|v_sal);END LOOP

37、;CLOSE emp_cursor;END;例子353练习q1、编写PL/SQL块，定义游标，显示高收入的员工编号、姓名、工作类型及工资q2、查询前10名员工的信息。q3、给工资低于1200 的员工增加工资50。q4、使用多种方式，输出工资最高的前n个员工的姓名和工资。54SET SERVEROUTPUT ONDECLAREV_empno NUMBER(5);V_ename VARCHAR2(10);CURSOR emp_cursor(p_deptno NUMBER,p_job VARCHAR2)IS SELECTempno,ename FROM emp WHEREdeptno=p_deptn

38、o AND job=p_job;BEGINOPEN emp_cursor(10,CLERK);LOOPFETCH emp_cursor INTO v_empno,v_ename;EXIT WHEN emp_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_empno|,|v_ename);END LOOP;END;游标参数的传递55带参数的显式游标带参数的显式游标q声明显式游标时可以带参数以提高灵活性声明显式游标时可以带参数以提高灵活性q声明带参数的显式游标的语法如下：声明带参数的显式游标的语法如下：CURSOR CURSOR()IS IS select_state

39、mentselect_statement;SQL SET SERVEROUTPUT ONSQL SET SERVEROUTPUT ONSQL DECLARESQL DECLAREdesig VARCHAR2(20);desig VARCHAR2(20);emp_code VARCHAR2(5);emp_code VARCHAR2(5);empnm VARCHAR2(20);empnm VARCHAR2(20);CURSOR emp_cur(desigparam VARCHAR2)ISCURSOR emp_cur(desigparam VARCHAR2)IS SELECT empno,ename

40、 FROM employee SELECT empno,ename FROM employee WHERE designation=desigparam;WHERE designation=desigparam;BEGIN BEGINdesig:=test;desig:=test;OPEN emp_cur(desig);OPEN emp_cur(desig);LOOPLOOPFETCH emp_cur INTO emp_code,empnm;FETCH emp_cur INTO emp_code,empnm;EXIT WHEN emp_cur%NOTFOUND;EXIT WHEN emp_cu

41、r%NOTFOUND;DBMS_OUTPUT.PUT_LINE(emp_code|empnm);DBMS_OUTPUT.PUT_LINE(emp_code|empnm);END LOOP;END LOOP;CLOSE emp_cur;CLOSE emp_cur;END;END;56练习q编写PL/SQL，定义参数游标（参数：岗位）,使用替代变量输入岗位，并显示该岗位所有员工的姓名和工资57q【训练1】使用特殊的FOR循环形式显示全部雇员的编号和名称。SET SERVEROUTPUT ONDECLARECURSOR emp_cursor IS SELECT empno,ename FROM em

42、p;BEGINFOR Emp_record IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(Emp_record.empno|Emp_record.ename);END LOOP;END;q说明：可以看到该循环形式非常简单，隐含了记录变量的定义、游标的打开、提取和关闭过程。Emp_record为隐含定义的记录变量，循环的执行次数与游标取得的数据的行数相一致。游标循环58q【训练2】另一种形式的游标循环。SET SERVEROUTPUT ON BEGINFOR re IN(SELECT ename FROM EMP)LOOP DBMS_OUTPUT.PUT_LIN

43、E(re.ename)END LOOP;END;q说明：该种形式更为简单，省略了游标的定义，游标的SELECT查询语句在循环中直接出现。游标59游标游标FOR循环循环60存储过程创建存储过程qCREATE OR REPLACE PROCEDURE procedure_name(parameter1 model datatype1,parameter2 model datatype2.)IS ASBEGIN PL/SQL Block;END procedure_name;q其中：其中：procedure_name是存是存储过储过程的名字，程的名字，parameter用用于指定参数，于指定参数，m

44、odel用于指定参数模式，用于指定参数模式，datatype用于指用于指定参数定参数类类型，型，IS AS用于开始用于开始PL/SQL代代码块码块。q注：当定注：当定义义存存储过储过程的参数程的参数时时，只能指定数据，只能指定数据类类型，不能型，不能指定数据指定数据长长度度62存储过程是如何进行定义和维护的？CREATE OR REPLACE PROCEDURE USP_OutTimeISBEGINDBMS_OUTPUT.PUT_LINE(SYSDATE);END USP_OutTime;63存储过程是如何进行定义和维护的？CREATE OR REPLACE PROCEDURE USP_Lea

45、ring(p_para1varchar2:=参数一参数一,p_para2nvarchar2 default 参数二参数二,p_para3 outvarchar2,p_para4 in out varchar2)ISBEGINDECLAREv_para5varchar2(20);BEGINv_para5:=输入输出：输入输出：|p_para4;p_para3:=输出：输出：|p_para1|p_para2;p_para4:=v_para5;END;END USP_Learing;64删除存储过程q删除存储过程qDROP PROCEDURE procedure_name;q编译存储过程qALTER

46、 PROCEDURE procedure_name COMPILE;65与存储过程相关的几个查询q-查看无效的存储过程qSELECT object_name qFROM USER_OBJECTS qWHERE STATUS=INVALID qAND OBJECT_TYPE=PROCEDURE q-查看存储过程的代码qSELECT TEXT qFROM USER_SOURCE qWHERE NAME=procedure_nameq其中：其中：procedure_name是存是存储过储过程的名字程的名字66如何调用存储过程执行(或调用)存储过程的人是过程的创建者或是拥有EXECUTE ANY PR

47、OCEDURE系统权限的人或是被拥有者授予EXECUTE权限的人。执行的方法如下：方法1：EXECUTE 模式名.存储过程名(参数.);方法2：BEGIN模式名.存储过程名(参数.);END;67传递的参数必须与定义的参数类型、个数和顺序一致(如果参数定义了默认值，则调用时可以省略参数)。参数可以是变量、常量或表达式，用法参见下一节。如果是调用本账户下的存储过程，则模式名可以省略。要调用其他账户编写的存储过程，则模式名必须要添加。以下是一个生成和调用简单存储过程的训练。注意要事先授予创建存储过程的权限。68？【训练1】创建一个显示雇员总人数的存储过程。步骤1：登录SCOTT账户(或学生个人账户

48、)。步骤2：在SQL*Plus输入区中，输入以下存储过程：CREATE OR REPLACE PROCEDURE EMP_COUNTASV_TOTAL NUMBER(10);BEGIN SELECT COUNT(*)INTO V_TOTAL FROM EMP;DBMS_OUTPUT.PUT_LINE(雇 员 总 人 数 为：|V_TOTAL);END;69步骤3：如果存在错误，就会显示:警告:创建的过程带有编译错误。如果存在错误，对脚本进行修改，直到没有错误产生。如果编译结果正确，将显示：过程已创建。步骤4：调用存储过程，在输入区中输入以下语句并执行：EXECUTEEMP_COUNT;显示结果

49、为：雇员总人数为：14PL/SQL过程已成功完成。70【训练2】在PL/SQL程序中调用存储过程。步骤1：登录SCOTT账户。步 骤 2：授 权 STUDENT账 户 使 用 该 存 储 过 程，即 在SQL*Plus输入区中，输入以下的命令：GRANT EXECUTE ON EMP_COUNT TO STUDENT授权成功。步骤3：登录STUDENT账户，在SQL*Plus输入区中输入以下程序：SET SERVEROUTPUT ONBEGINSCOTT.EMP_COUNT;END;71【训练3】编写显示雇员信息的存储过程EMP_LIST，并引用EMP_COUNT存储过程。步骤1：在SQL*P

50、lus输入区中输入并编译以下存储过程：CREATE OR REPLACE PROCEDURE EMP_LISTAS CURSOR emp_cursor IS SELECT empno,ename FROM emp;BEGIN FOR Emp_record IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(Emp_record.empno|Emp_record.ename);END LOOP;EMP_COUNT;END;72步骤2：调用存储过程，在输入区中输入以下语句并执行：EXECUTE EMP_LIST显示结果为：7369SMITH7499ALLEN7521WA