第13章PLSQL程序设计基础.ppt

《第13章PLSQL程序设计基础.ppt》由会员分享，可在线阅读，更多相关《第13章PLSQL程序设计基础.ppt（101页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、2022年年5月月30日星期一日星期一1第第1313章章 PL/SQLPL/SQL程序设计程序设计基础基础20222022年年5 5月月3030日星期一日星期一2 2PL/SQL语言基础语言基础一、例子DECLARE N CHAR(8); SAL NUMBER(5); I NUMBER(2);BEGIN SELECT SALARY INTO SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=201; IF SAL 10000 THEN DBMS_OUTPUT.PUT_LINE(TO_CHAR(SAL)| 太少); ELSE DBMS_OUTPUT.PUT_LINE(TO

2、_CHAR(SAL)| 还可以); END IF;END;20222022年年5 5月月3030日星期一日星期一3 3二、PL/SQL简述 1. PL/SQL块及程序 用PL/SQL定义块可将多个SQL编制成程序。程序是由若干块组成。 2. PL/SQL块结构与用途 PL/SQL块由三部分组成：定义部分：定义所处理的变量、常量、游标等。可执行部分：SQL语句及控制结构的PL/SQL语句异常处理部分：对执行过程中的错误进行处理。块可以嵌套。20222022年年5 5月月3030日星期一日星期一4 4块结构：DECLARE 说明部分 BEGIN 语句(SQL或PL/SQL语句） EXCEPTION

3、 错误处理程序 END 3. PL/SQL程序分类 由基本PL/SQL块组成PL/SQL程序。程序有： 无名块：它嵌入在某个应用中的PL/SQL块。 存储过程或函数：命名了的PL/SQL块，可以带20222022年年5 5月月3030日星期一日星期一5 5参数，并重复调用，是数据库对象。 包：是命名PL/SQL块，由一组相关的过程、函数和标识符组成。是数据库对象。 触发器:与表相关联的存储过程。一表最多12个。4. PL/SQL程序运行环境 可以有多个：SQL Plus Worksheet,SQL PLUS等。 在SQL PLUS环境中，第一句是DECLARE或BEGIN就识别为是PL/SQL

4、程序。它只能以“/”表示程序结束。20222022年年5 5月月3030日星期一日星期一6 6三、PL/SQL基础1.标识符 标识符是用户定义的符号串，用来命名变量、常量、过程等。 标识符以字母开头，后跟数字(0-9)或特殊字符$、#和_。长度不超过30，不能是PL/SQL的保留字，不能有空格。 例1：合法标识符：A34、BB#，D_123DF 不合法标识符：5A$、#ABC、END。2. 变量及变量说明 变量是表示要处理数据项的名称。变量名用标识符来表示。 变量在使用之前必须用DECLARE进行说明。20222022年年5 5月月3030日星期一日星期一7 7变量说明： 变量名 CONSTA

5、NT 类型 NOT NULL:=值DEFAULT SQL表达式；例1: DECLARE aa char(5) not null:=TEST; bb number(3) default 5; cc char(4):=THIS; dd constant char(4) :=SWJ; -不能赋值beginDBMS_OUTPUT.PUT_LINE(aa| |to_char(bb)| |cc| |DD);end;说明：非空时要有初始值,NOT NULL在前，缺省值在后；每个变量说明占一行，且以分号“；”结束。变量说明要在DECLARE以后，BEGIN之前。20222022年年5 5月月3030日星期一日

6、星期一8 83.变量或常量类型数值型：NUMBER(p,s),INTEGER,FLOAT,DEC字符型：CHAR(n),VARCHAR2(n),RAW(n)日期型：DATE布尔型：BOOLEAN(TRUE或FALSE)大数据类型：BFILE,BLOB,CLOB,NCLOB4. 数据类型转换显示转换: TO_CHAR(),TO_NUMBER(), RAWTOHEX(),HEXTORAW(),ROWIDTOCHAR(),TO_DATE()20222022年年5 5月月3030日星期一日星期一9 95. 函数20222022年年5 5月月3030日星期一日星期一10106. 表达式表达式是由变量、常

7、量、列名、函数和运算符结合的有意义式子。数值表达式：数值运算符、变量、常量、函数等数值运算符：+、-、/、*、()例2: DECLARE A INT :=4; B FLOAT:=4.324; X FLOAT; BEGIN X:= SIN(3)*10+2*8-A*10*(10-B*2); DBMS_OUTPUT.PUT_LINE(TO_CHAR(X); END;20222022年年5 5月月3030日星期一日星期一1111字符表达式：字符运算符: |(合并)关系表达式:关系表达式的结果是TRUE和FALSE关系运算符: != = = LIKE IN BETWEENAND 例3: ABC 123

8、123345 THIS LIKE T% X BETWEEN 10 AND 20 A IN (SS,DD,AD) FALSE SIN(X)+10 A*2+B20222022年年5 5月月3030日星期一日星期一1212逻辑表达式逻辑运算符：NOT AND OR 例4: NOT (A 10 AND B100 AND CCC LIKE C%例5: DECLARE A INT :=4; B FLOAT:=4.324; X FLOAT; C BOOLEAN; BEGIN X:= SIN(3)*10+2*8-A*10*(10-B*2); C:=A IN (SS,DD,AD); IF C OR A10 TH

9、EN N2:=N1+10; DBMS_OUTPUT.PUT_LINE(TO_CHAR(N2); END IF; END;20222022年年5 5月月3030日星期一日星期一2020例2：嵌套选择结构DECLARE N1 NUMBER:=11; N2 NUMBER DEFAULT 10; BEGIN IF N110 THEN IF N210 THEN N2:=N2+10; ELSE N2:=N1*N1; END IF; DBMS_OUTPUT.PUT_LINE(TO_CHAR(n2);END;20222022年年5 5月月3030日星期一日星期一2222例4: IFTHENELSIFEND I

10、F结构DECLARE N1 NUMBER:=10; N2 NUMBER:=20; CC CHAR(20); C2 DATE:=TO_DATE(1999-1-1,YYYY-MM-DD);BEGIN DBMS_OUTPUT.PUT_LINE(TO_CHAR(C2); IF N110 THEN N2:=N1+10; ELSIF N1 BETWEEN 7 AND 12 THEN N2:=SQRT(N1)*2; ELSIF C2=DATE1998-1-1 THEN CC:=TO_CHAR(SYSDATE); ELSE CC:=NO RESULT; END IF; DBMS_OUTPUT.PUT_LINE

11、(CC| |TO_CHAR(N2);END;20222022年年5 5月月3030日星期一日星期一23233. CASE选择结构CASE 变量WHEN 表达式1 THEN 语句序列1;WHEN 表达式2 THEN 语句序列2;WHEN 表达式N THEN 语句序列N;ELSE 语句序列N+1;END CASE说明:1. 2. 20222022年年5 5月月3030日星期一日星期一2424例1：DECLARE C1 CHAR(1):=F; N1 NUMBER:=2; N2 NUMBER:=35; NC NUMBER;BEGIN NC:=ASCII(C1); CASE NC WHEN N2*N2

12、THEN N1:=N2*N2; DBMS_OUTPUT.PUT_LINE(N1=|TO_CHAR(N1); WHEN N2+N2 THEN N1:=N2+N2; DBMS_OUTPUT.PUT_LINE(N1=|TO_CHAR(N1); ELSE DBMS_OUTPUT.PUT_LINE(N1=|TO_CHAR(N1); DBMS_OUTPUT.PUT_LINE(NC=|TO_CHAR(NC); END CASE; END;20222022年年5 5月月3030日星期一日星期一25254. 循环结构循环结构是指按照指定的逻辑条件循环执行一组命令。有三种循环：LOOP-EXIT-END;LOOP

13、-EXIT-WHEN-END;WHILE-LOOP-END和FOR-IN-LOOP-END。条件条件语句序列语句序列当条件为真时，执行语句序列，直到条件为假。条件是任何合法的逻辑表达式或关系表达式。20222022年年5 5月月3030日星期一日星期一26264.1 LOOP-EXIT-END循环 LOOP 语句序列 EXIT END LOOP说明：执行EXIT时从循环中退出，在条件语句中执行EXIT。该循环中，必须有EXIT,否则就会成为“死循环”。EXIT只能在循环。例1：计算1+2+3+100DECLARE I INT :=1; S INT:=0;BEGIN LOOP S:=S+I; I

14、F I=100 THEN EXIT; END IF; I:=I+1; END LOOP;DBMS_OUTPUT.PUT_LINE(TO_CHAR(S); END; 20222022年年5 5月月3030日星期一日星期一27274.2 LOOP-EXIT WHEN-END循环 LOOP 语句序列 EXIT WITH 条件 -等价IF 条件 THEN EXIT，可用在其它循环 END LOOP例2：计算10！DECLARE I INT :=1; S INT:=1;BEGIN LOOP S:=S*I; EXIT WHEN I=10; I:=I+1; END LOOP; DBMS_OUTPUT.PUT

15、_LINE(TO_CHAR(S); END; 20222022年年5 5月月3030日星期一日星期一28284. 3 WHILE-LOOP-END循环WHILE 条件 LOOP 每次循环前计算条 语句序列； 件，为TRUE，执 END LOOP； 行语句，否则不。例3：计算s=1*2+2*3+N*(N+1),当N=50的值。DECLARE I INT :=1; S INT:=0;BEGIN WHILE I=50 LOOP S:=S+I*(I+1); I:=I+1; END LOOP;DBMS_OUTPUT.PUT_LINE(TO_CHAR(S); END; 20222022年年5 5月月303

16、0日星期一日星期一2929例4：在WHILE循环中使用EXIT或EXIT WHEN计算S=1*2*3+2*3*4+N*(N+1)*(N+2);当N=40DECLARE I INT :=1; S INT:=0;BEGIN WHILE TRUE LOOP S:=S+I*(I+1)*(I+2); EXIT WHEN I=40; I:=I+1; END LOOP;DBMS_OUTPUT.PUT_LINE(TO_CHAR(S); END; 说明：EXIT和EXIT WHEN可用在任何循环内，且只能在循环内。20222022年年5 5月月3030日星期一日星期一30304.4 FOR-IN-LOOP-EN

17、D循环FOR 循环变量 IN REVERSE下界.上界 LOOP 语句序列；END LOOP；说明： 循环变量被隐式说明为BINARY-INTEGER，也可显式说明。 步长1或-1(REVERSE)，循环次数:上界-下界+1; EXIT或EXIT WHEN可用在FOR循环中； 已知循环次数时，可用FOR循环，也可用其它 末知循环次数不能用FOR循环 上界或下界可以为表达式。20222022年年5 5月月3030日星期一日星期一3131例5：显示20到50的平方根的值及它们的和。DECLARE S FLOAT :=0;BEGIN FOR I IN 20.50 LOOP DBMS_OUTPUT.P

18、UT_LINE(TO_CHAR(SQRT(I),9.9999); S:=S+SQRT(I); END LOOP; DBMS_OUTPUT.PUT_LINE(S= |TO_CHAR(S);END;说明：循环变量I不用说明。 20222022年年5 5月月3030日星期一日星期一3232例6: 步长-1。加REVERSE。说明：加REVERSE，DECLARE S FLOAT :=0; BEGIN FOR I IN REVERSE 20.50 LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQRT(I),9.9999); S:=S+SQRT(I); END LOOP; DBM

19、S_OUTPUT.PUT_LINE(S= |TO_CHAR(S); END;上界仍要大于下界。20222022年年5 5月月3030日星期一日星期一3333例7:上下界为表达式的FOR循环DECLARE S FLOAT :=0; N INT :=10;BEGIN FOR I IN REVERSE 2*N-1.N*N LOOP DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQRT(I),9.9999); S:=S+SQRT(I); END LOOP; DBMS_OUTPUT.PUT_LINE(S= |TO_CHAR(S);END;20222022年年5 5月月3030日星期一日星期

20、一3434例8: 生成字符串SWJ001-SWJ120declare n number(1); sn varchar2(8); sn1 char(3); begin for n in 1.120 loop if n10 then sn1:=ltrim(to_char(n,9); sn:=rtrim(swj00|sn1); else if n100 then sn1:=ltrim(to_char(n,99); sn:=rtrim(swj0|sn1); else sn1:=ltrim(to_char(n,999); sn:=rtrim(swj|sn1); end if ; end if; DBMS

21、_OUTPUT.PUT_LINE(SN); end loop; End;20222022年年5 5月月3030日星期一日星期一35354. 5 循环嵌套上面的任何循环都可以互相嵌套，且可以多重嵌套，但必须是完全嵌套。如：LOOP FOR LOOP -可有其它循环(多重循环) END LOOP; WHILE LOOP -可有其它循环(多重循环) END LOOP;END LOOP;20222022年年5 5月月3030日星期一日星期一3636例9: 两重循环，计算S=1!+2!+10!DECLARE S FLOAT :=0; N INT :=10; K INT ;BEGIN FOR I IN 1

22、.N LOOP - 计算阶乘和 K:=1; FOR J IN 1.I LOOP -计算K! K:=K*J; END LOOP; S:=S+K; DBMS_OUTPUT.PUT_LINE(TO_CHAR(K); END LOOP; DBMS_OUTPUT.PUT_LINE(S= |TO_CHAR(S);END;20222022年年5 5月月3030日星期一日星期一37374.6 循环标签循环标签是以用来标示循环,放在循环前。其格式： DECLARE S FLOAT :=0; N INT :=10; K INT ;BEGIN FOR I IN 1.N LOOP - 计算阶乘和 K:=1; FOR

23、J IN 1.I LOOP -计算K! K:=K*J; END LOOP FOR_LOOP2; S:=S+K; DBMS_OUTPUT.PUT_LINE(TO_CHAR(K); END LOOP FOR_LOOP1; DBMS_OUTPUT.PUT_LINE(S= |TO_CHAR(S);END;20222022年年5 5月月3030日星期一日星期一3838循环标签一是提高程序的可读性，二是可以从内循环跳到外层。LOOP. LOOP . EXIT outer WHEN . 退出两重循环。 END LOOP;.END LOOP outer;注意：只能从内循环跳到外循环，反之不行。20222022

24、年年5 5月月3030日星期一日星期一3939五、%TYPE类型PL/SQL变量用来存储表中的数据，但二者要有相同类型。要保证变量与列名类型总是一致，就要用%TYPE类型。 变量名 表名.列名%TYPE;例1：DECLARE J_T JOBS.JOB_TITLE%TYPE; BEGIN SELECT JOB_TITLE INTO J_T FROM JOBS WHERE JOB_ID=AD_VP; DBMS_OUTPUT.PUT_LINE(J_T); END;20222022年年5 5月月3030日星期一日星期一4040六、%ROWTYPE类型用%ROWTYPE说明变量为一个记录类型。 变量名

25、表名%ROWTYPE;引用变量时：变量名.列名例1：DECLARE EMP EMPLOYEES %ROWTYPE;BEGIN SELECT * INTO EMP FROM EMPLOYEES WHERE EMPLOYEE_ID=201; DBMS_OUTPUT.PUT_LINE (EMP.LAST_NAME| |EMP.FIRST_NAME);END;20222022年年5 5月月3030日星期一日星期一4141七、游标(CURSOR)游标是将从表中选择的一组记录，放置在内存的临时表中。游标是数据类型，要先说明后使用。1.说明游标 CURSOR 游标名 IS SELECT语句；例1: DECL

26、ARE MIN_S JOBS.MIN_SALARY%TYPE; CURSOR MS IS SELECT * FROM JOBS WHERE MIN_SALARY=MIN_S; BEGIN NULL; -空语句 END;20222022年年5 5月月3030日星期一日星期一42422. 打开游标 OPEN 游标名；注意：打开之前，游标中的变量必须有值；打开游标后,查询记录放在内存,指针指向第一个记录。例2：DECLARE MIN_S JOBS.MIN_SALARY%TYPE; CURSOR MS IS SELECT * FROM JOBS WHERE MIN_SALARY=MIN_S; BEGI

27、N MIN_S:=1000; -先给变量赋值 OPEN MS; - 才能打开游标 END;20222022年年5 5月月3030日星期一日星期一43433. 提取游标数据 FETCH 游标名 INTO 变量1,变量2,;注意: 变量要与说明游标时SELECT后的列名在类型和个数上完成一样。不能只提取部分。 FETCH MS INTO J1，J2，M1，M2；4. 关闭游标 CLOSE 游标； CLOSE MS； 关闭后的游标不能再使用。使用游标的过程：说明游标、变量赋值、打开游标、处理、关闭游标。20222022年年5 5月月3030日星期一日星期一4444例3：提出数据DECLARE J1

28、JOBS.JOB_ID%TYPE; -变量名与列名类型保持一致 J2 JOBS.JOB_TITLE%TYPE; M1 JOBS.MIN_SALARY%TYPE; M2 JOBS.MAX_SALARY%TYPE; MIN_S JOBS.MIN_SALARY%TYPE; CURSOR MS IS SELECT * FROM JOBS -说明游标 WHERE MIN_SALARY=MIN_S; BEGIN MIN_S:=1000; -先给变量赋值 OPEN MS; - 才能打开游标 FETCH MS INTO J1,J2,M1,M2; -第一条记录 DBMS_OUTPUT.PUT_LINE(J1|

29、|J2| |TO_CHAR(m1+m2); FETCH MS INTO J1,J2,M1,M2; -第二条记录 DBMS_OUTPUT.PUT_LINE(J1| |J2| |TO_CHAR(m1+m2); FETCH MS INTO J1,J2,M1,M2; -第三条记录 DBMS_OUTPUT.PUT_LINE(J1| |J2| |TO_CHAR(m1+m2);END;20222022年年5 5月月3030日星期一日星期一4545例4: 复杂游标（分组）DECLARE DEP EMPLOYEES.DEPARTMENT_ID%TYPE; COU INT; CURSOR DEPT IS SELE

30、CT DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES GROUP BY DEPARTMENT_ID;BEGIN OPEN DEPT; - 打开游标 FETCH DEPT INTO DEP,COU; -第一条记录 DBMS_OUTPUT.PUT_LINE(DEP|的人数是： |TO_CHAR(COU); FETCH DEPT INTO DEP,COU; -第二条记录 DBMS_OUTPUT.PUT_LINE(DEP|的人数是： |TO_CHAR(COU); CLOSE DEPT; -关闭游标END;20222022年年5 5月月3030日星期一日星期一46465. 游

31、标属性 通过游标属性来知道游标的状态。A: %FOUND 如果前一个FETCH返回一行，%FOUND返回TRUE；否则返回FALSE。在未打开游标之前设置%FOUND，返回错误ORA_1001。用法： 游标名%FOUNDB.%NOTFOUND与%FOUND相反，常用于退出循环提出。C. %ISOPEN判定游标是否打开。打开为TRUE,否则为FALSE.20222022年年5 5月月3030日星期一日星期一4747D.%ROWCOUNT返回从游标中已提取的行数。%FOUND%NOTFOUND%ISOPEN%ROWCOUNT打开之前 出错 出错 FALSE 出错打开之后 NULL NULL TRU

32、E 01FETCH前 NULL NULL TRUE 01FETCH后 TRUE FALSE TRUE 1下FETCH前 TRUE FALSE TRUE 1下FETCH后 TRUE FALSE TRUE 以数据个数最FETCH前 TRUE FALSE TRUE 以数据个数最FETCH后 FALSE TRUE TRUE 以数据个数CLOSE后 出错 出错 FALSE 出错20222022年年5 5月月3030日星期一日星期一4848例5：declare emp employees %rowtype; cursor sal is select * into emp from employees wh

33、ere salary=10000; -说明游标begin open sal; - 打开游标 loop fetch sal into emp; if sal%found then - 提出成功 dbms_output.put_line (emp.last_name| 工资:|to_char(emp.salary); else - 不成功 exit; end if; end loop;end; 20222022年年5 5月月3030日星期一日星期一4949例6：测试游标属性declare emp employees %rowtype; cursor sal is select * into emp

34、 from employees where department_id=100;begin if not sal%isopen then -游标未打开 open sal; end if; dbms_output.put_line(提取行数：|to_char(sal%rowcount); loop fetch sal into emp; dbms_output.put_line(emp.last_name|行:|to_char(sal%rowcount); exit when sal%notfound or sal%rowcount=5; end loop;end;20222022年年5 5月月

35、3030日星期一日星期一5050例7: 有表DATA_FIEL(n1,n2,n3,exnum)和表TEMPDECLARE num1 data_table.n1%TYPE; num2 data_table.n2%TYPE; num3 data_table.n3%TYPE; result temp.col1%TYPE; CURSOR c1 IS SELECT n1, n2, n3 FROM data_table WHERE exnum = 1;BEGIN OPEN c1; LOOP FETCH c1 INTO num1, num2, num3; EXIT WHEN c1%NOTFOUND; res

36、ult := num2/(num1 + num3); INSERT INTO temp VALUES (result, NULL, NULL); END LOOP; CLOSE c1; COMMIT;END;20222022年年5 5月月3030日星期一日星期一5151八、参数化游标和隐含游标1.参数化游标 CRUSOR 游标名(变量1 类型1,变量2 类型2,) IS SELECT语句说明：SELECT语句的条件中使用变量,变量类型可以为%TYPE。打开参数化游标： OPEN 游标名(实参1,实参2,);说明：在打开时所有实参要有具体值，且在数量和类型上与游标说明时一致。 实参可以是表达式。

37、20222022年年5 5月月3030日星期一日星期一5252例1：带参数游标DECLARE EMP EMPLOYEES%ROWTYPE;CURSOR c1(S1 EMPLOYEES.SALARY%TYPE, DID INT) IS SELECT * FROM EMPLOYEES WHERE SALARY=S1 and DEPARTMENT_ID=did;BEGIN OPEN c1(10000,90); LOOP FETCH c1 INTO EMP; DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT); EXIT WHEN c1%NOTFOUND; END LO

38、OP; CLOSE c1;END;20222022年年5 5月月3030日星期一日星期一5353例2: 带参数游标declare cursor c1(s1 employees.salary%type) is select max(salary),min(salary) from employees where salary=s1; m1 employees.salary%type:=16000; m2 employees.salary%type:=6000;begin open c1(m1+m2)/2); -实参为表达式 loop fetch c1 into m1,m2; if c1%foun

39、d then insert into jobs values (new_j,new job,m1,m2); end if; dbms_output.put_line(to_char(c1%rowcount); exit when c1%notfound; end loop; close c1; commit; -提交游标end;20222022年年5 5月月3030日星期一日星期一54542. 隐含游标（SQL游标)每次处理一个SQL语句，ORACLE自动打开一个游标，该游标不能使用OPEN,FETCH或CLOSE进行操作。但可用游标特性来获得执行SQL的信息。隐含游标的名字是：SQLSQL游

40、标总是反映最后一个SQL语句执行情况。%ISOPEN 永远是FALSE(执行完SQL后自动关闭）%FOUND 至少处理一行,为TRUE;否则为FALSE%NOTFOUND 与%FOUND相反%ROWCOUNT 处理影响的行数。20222022年年5 5月月3030日星期一日星期一5555例3: 隐含游标的属性使用begin delete from jobs where job_id like ddad%; if sql%found then dbms_output.put_line(删除行数: |to_char(sql%rowcount); else dbms_output.put_line(

41、没有删除任何行); end if; update jobs set max_salary=(max_salary+min_salary)/2, min_salary=(max_salary-min_salary)/2 where job_id like ad%; if sql%found then dbms_output.put_line(改行数:|to_char(sql%rowcount); else dbms_output.put_line(没有修改任何行); end if;end;20222022年年5 5月月3030日星期一日星期一5656上机题：1.计算1！+2！+3！+10!的值。

42、2.计算 x/1!+x2/2!+.+xn/n! (其中X2,n=10) 3.编程序求满足不等式 1+32+52+N22000的最小N值。4.计算下面级数当末项小于0.001时的部分和。 1/(1*2)+1/(2*3)+1/(3*4)+1/(n*(n+1)+ 5.计算并输出1900-2000之间的素数的个数N6.定义游标：从雇员表中显示工资大于10000的记录，只要姓名、部门编号和工资。编程显示其中的奇数记录。7.定义游标：列出每个员工的姓名、部门名称。编程显示第10个到第20个记录。20222022年年5 5月月3030日星期一日星期一57578. 在JOBS表中删除工作编号为AD_NEW的记

43、录，如果无，插入(AD_NEW,POLICE,1999,20000)9. 将雇员表中的所有工资小于10000增加1000，统计出增加工资的人数及增加的工资数量。10. 将雇员表中的部门编号为100的所有员工删除，统计删除的人数及删除人的平均工资。11. 编程输出9*9乘法表。12. 从雇员表中显示工资最高的前五个人的姓名，部门和工资。20222022年年5 5月月3030日星期一日星期一5858第五章第五章 过程、函数、触发器过程、函数、触发器无名块只能以文件形式执行、不存放在数据库中，每次执行要编译，不能在其它PL/SQL块中调用。过程、函数和触发器都是有名块，是数据库对象一、过程设计1.创

44、建过程(系统权限CREATE PROCEDURE)过程结构如下：CREATE OR REPLACE PROCEDURE 过程名(参数列表) AS 说明部分 -内部变量BEGIN 执行部分EXCEPTION 异常处理部分END 过程名20222022年年5 5月月3030日星期一日星期一5959说明：过程必须有名字;过程可以有参数或无参数创建过程经编译无错后存放在数据库中(不马上执行)；调用过程时执行块内语句。过程的使用：先创建后调用。例1. Create or replace procedure test1 as sal employees.salary%type; begin select

45、salary into sal from employees where last_name=Abel; dbms_output.put_line(to_char(sal); end;注意:即使程序编译有错，过程也将写入数据库。20222022年年5 5月月3030日星期一日星期一60602.查询过程USER_SOURCEUSER_SOURCE: NAME、TEXT、TYPE、LINESELECT TEXT FROM USER_SOURCE WHERE NAME=TTT ORDER BY LINE;3. 修改过程存储过程以文件形式存在。在SQL PLUS中：CCC.SQLEDIT4. 调用过程

46、(EXECUTE ANY PROCEDURE) 在块中或过程中调用存储过程，不能在表达式。20222022年年5 5月月3030日星期一日星期一6161调用方式：过程名(实参1,实参N)例2: 用户SWJ001： BEGIN TEST1; END;例3:SWJ001:GRANT EXECUTE ON TEST1 TO HR;HR用户：BEGIN SWJ001.TEST1; END; 5. 删除过程DROP PROCEDURE 过程名例4: DROP PROCEDURE TEST1;20222022年年5 5月月3030日星期一日星期一62626. 带参数的过程CREATE OR REPLACE

47、 PROCEDURE 过程名 (参数1 IN|OUT|IN OUT 类型1,) AS 内部变量说明BEGIN 执行部分EXCEPTION 异常处理END；说明：IN 向过程送参数，读入参数OUT: 从过程获得参数，输出参数IN OUT 即可以读入参数，可也获得参数20222022年年5 5月月3030日星期一日星期一6363例5: create or replace procedure test2( v1 in employees.job_id%type, -参数说明 v2 out employees.salary%type) as vv employees.salary%type;-内部变量

48、说明 begin select max(salary) into vv from employees where job_id like v1; v2:=vv+10000; -OUT类型参数必须赋值 end;调用：declare v employees.salary%type; begin test2(AD%,v); dbms_output.put_line(to_char(v); end;20222022年年5 5月月3030日星期一日星期一6464例6：求阶乘 create or replace procedure jc(v1 in int, v2 out int) as s int; b

49、egin s:=1; for i in 1.v1 loop s:=s*i; end loop; v2:=s; dbms_output.put_line(过程内V2值:|to_char(s); end;调用： declare k int:=10; begin jc(k,k); dbms_output.Put_line(过程外K:|to_char(k); end;20222022年年5 5月月3030日星期一日星期一6565注意： 过程调用参数类型与个数必须完全一致； OUT或IN OUT参数在调用时不能对应表达式；例：JC(K+1,K);-正确 但JC(K+1,K*2);非法 参数表示对应表列时

50、，最好%TYPE类型 过程调用只能出现在块中，不能与表达式计算 可以多个OUT参数来返回多个值。 IN参数在过程中不能赋值 如果要给参数赋值，必须指定为OUT或IN OUT 不指明IN、OUT或IN OUT时，缺省为IN 出错信息表USER_ERRORS(列：LINE,TEXT,.)20222022年年5 5月月3030日星期一日星期一6666例7:在过程中用游标(返回第N个不等于的记录)create or replace procedure jobk( j in jobs.job_id%type, jk out jobs%rowtype, n in int) as cursor j_c is