oracle数据库课件chapter2PLSQLfor计.ppt

PL/SQL语言基础,一、例1 DECLARE vxh varchar2(6):= 101302 vxm varchar2(8):= 张琼; vzxf number(2):=45; /*定义变量类型*/ BEGIN UPDATE XSB SET XM=vxm, ZXF=vzxf WHERE XH=vxh; /*更新学生表*/ IF SQL%NOTFOUND THEN /*检查记录是否存在，如果不存在就插入记录*/ INSERT INTO XSB(XH, XM, XB, CSSJ, ZY, ZXF) VALUES(vxh,vxm, 女, 16-5月-90, 软件工程, vzxf); END IF; END; / (郑P137),二、PL/SQL简述 1. PL/SQL块及程序 PL/SQL是一种过程化编程语言。用PL/SQL定 义块可将多个SQL编制成程序（程序过程、函数 触发器等）。程序是由若干PL/SQL块组成。 2. PL/SQL块结构与用途 PL/SQL块由三部分组成： 定义部分：定义所处理的变量、常量、游标等。 可执行部分：SQL语句及控制结构的PL/SQL语句 异常处理部分：对执行过程中的错误进行处理。 块可以嵌套。,PL/SQL块块结构： DECLARE 说明部分 BEGIN 语句(SQL或PL/SQL语句） EXCEPTION 错误处理程序 END,3. PL/SQL程序分类 由基本PL/SQL块组成PL/SQL程序。程序有： 无名块：它嵌入在某个应用中的PL/SQL块。 存储过程或函数：命名了的PL/SQL块，可以带参数，并重复调用，是数据库对象。 包：是命名PL/SQL块，由一组相关的过程、函 数和标识符组成。是数据库对象。 触发器:与表相关联的存储过程。一表最多12个。,4. PL/SQL程序运行环境 可以有多个：SQL Plus Worksheet,SQL PLUS 、isqlplus等。 在SQL PLUS环境中，第一句是DECLARE或BEGIN就识别为是PL/SQL程序。 它只能以“/”表示程序结束。,三、PL/SQL基础 标识符 标识符是用户定义的符号串，用来命名变量、常 量、过程等。 标识符以字母开头，后跟数字(0-9)或特殊字符$、#和_且长度不超过30，不能是PL/SQL的保留字，不能有空格。 例1：合法标识符：A34、BB#，D_123DF 不合法标识符：5A$、#ABC、END。,2. 变量及变量说明 变量是表示要处理数据项的名称。变量名用标识 符来表示。 变量在使用之前必须用DECLARE进行说明。 变量说明： 变量名 CONSTANT 类型 NOT NULL:=值DEFAULT SQL表达式；,变量或常量数据类型 数值型：NUMBER(p,s),INTEGER,FLOAT,DEC 字符型：CHAR(n),VARCHAR2(n),RAW(n) 日期型：DATE 布尔型：BOOLEAN(TRUE或FALSE) 大数据类型：BFILE,BLOB,CLOB,NCLOB 4. 数据类型转换 显示转换: TO_CHAR(),TO_NUMBER(), RAWTOHEX(), ,HEXTORAW(),ROWIDTOCHAR(),TO_DATE(),5. 函数,6. 表达式 表达式是由变量、常量、列名、函数和运算符 结合的有意义式子。 （1）数值表达式：数值运算符、变量、常量、函数等 数值运算符：+、-、/、*、() 例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;,（2）字符表达式：字符运算符: |(合并) （3）关系表达式:关系表达式的结果是TRUE和FALSE 关系运算符: != = = LIKE IN BETWEENAND 例3: ABC 123 123345 THIS LIKE T% X BETWEEN 10 AND 20 A IN (SS,DD,AD) FALSE SIN(X)+10 A*2+B,（4）逻辑表达式 逻辑运算符：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 AB THEN DBMS_OUTPUT.PUT_LINE(TO_CHAR(X); END IF; END;,7. 变量赋值 变量名：=与变量同类型的表达式； 例6: DECLARE C1 CHAR(34) ; -赋给C1的字符串不能大于34 N1 NUMBER(3); N2 NUMBER(2); B1 BOOLEAN; BEGIN N1:=10; N2:=SIN(N1)*2+20.30; C1:=TO_CHAR(N2)| TEST; B1:= C1 LIKE %T; END;,8. 注释行 单行注释：在一行的任何位置以“-字符串” 多行注释：/* 注释内容 */ 例7:DECLARE C1 CHAR(34); -字符变量，长度不超过34 N1 NUMBER(3); -数字型变量 BEGIN /* 赋值语句范例： 变量类型要与表达式类型一致 */ N1:=10; C1:=TO_CHAR(N1)| TEST; END;,9. PL/SQL中的语句 PL/SQL块中每一行一条语句，并且必须以分号 “；”结束。 变量或常量说明语句、 变量赋值语句、CASE语句和IF语句、 各种循环语句、数据处理语句SIDU、事务处理语句、游标语句DBMS_OUTPUT.PUT_LINE(字符串) 等。 COMMIT、ROLLBACK EXECUTE IMMEDIATE 动态串 SELECTINTO 变量名表|记录名 FROM,每个块由若干语句组成。若干块组成程序。,四、PL/SQL控制结构 三种程序结构：顺序、选择和循环 顺序结构 顺序结构是指执行过程按所写程序的顺序执行。 例8： DECLARE VV CHAR(20); SAL NUMBER(4); BEGIN SELECT LAST_NAME INTO VV FROM EMPLOYEES WHERE EMPLOYEE_ID=201; DBMS_OUTPUT.PUT_LINE(VV); SAL:=10; END;,例9：DECLARE JID VARCHAR2(10):=AD_VP; JTIT CHAR(30); BEGIN SELECT JOB_TITLE INTO JTIT FROM JOBS WHERE JOB_ID=JID; DBMS_OUTPUT.PUT_LINE(JTIT); END;,说明：JID的类型与JOB_ID的类型要完全一致，且长度要一样。改为JID CHAR(10)将出错。 可改为：JID JOBS.JOB_ID%TYPE:=AD_VP;,2. IF选择结构,条件,语句1,语句2,IF 条件 THEN 语句序列； END IF; “条件”为逻辑表达式或关系表达式，“条件”为TRUE时执行“语句序列”，为FALSE时执行END IF后面的语句。,IF 条件 THEN 语句序列1 ELSE 语句序列2 END IF;,ENDIF,IF 条件1 THEN 语句序列1 ELSIF 条件2 THEN 语句序列2 ELSE 语句序列3 END IF; 例10: DECLARE N1 NUMBER:=11; N2 NUMBER; BEGIN IF N110 THEN N2:=N1+10; DBMS_OUTPUT.PUT_LINE(TO_CHAR(N2); END IF; END;,例11：嵌套选择结构 DECLARE N1 NUMBER:=11; N2 NUMBER DEFAULT 10; BEGIN IF N110 THEN IF N2N1 THEN N2:=N1+10; DBMS_OUTPUT.PUT_LINE(TO_CHAR(N2); END IF; END IF; END; 说明：嵌套必须是完全嵌套,可以是任何选择结构 IF_END IF、IF_ELSE_END IF 等。,例12：IFTHENELSE结构 DECLARE N1 NUMBER:=9; N2 NUMBER:=19; BEGIN IF N110 THEN N2:=N2+10; ELSE N2:=N1*N1; END IF; DBMS_OUTPUT.PUT_LINE(TO_CHAR(N2); END;,例13: IFTHENELSIFEND IF结构 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(CC| |TO_CHAR(N2); END;,3. CASE选择结构 CASE 变量 WHEN 表达式1 THEN 语句序列1; WHEN 表达式2 THEN 语句序列2; WHEN 表达式N THEN 语句序列N; ELSE 语句序列N+1; END CASE,说明: 1. 当“变量”的值与某个表达式值相同时，将执行相应的语句序列 2. 当变量与所有表达式不等时执行序列N+1 3. 语句序列1到语句序列N+1中只能执行一个语句序列。,例14：DECLARE C1 CHAR(1):=F; N1 NUMBER:=2; N2 NUMBER:=35; NC NUMBER; BEGIN NC:=ASCII(C1); CASE NC WHEN N2*N2 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;,4. 循环结构 循环结构是指按照指定的逻辑条件循环执行一组 命令。有三种循环：LOOP-EXIT-END;LOOP- EXIT-WHEN-END;WHILE-LOOP-END和FOR- IN-LOOP-END。,当条件为真时，执行语句序列，直到条件为假。 条件是任何合法的逻辑表达式或关系表达式。,4.1 LOOP-EXIT-END循环 LOOP 语句序列 EXIT END LOOP 说明：执行EXIT时从循环 中退出，在条件语句中执 行EXIT。该循环中，必须 有EXIT,否则就会成为“死 循环”。EXIT只能在循环体内。,例15： 计算1+2+3+100 DECLARE I INT :=1; S INT:=0; BEGIN LOOP S:=S+I; IF I=100 THEN EXIT; END IF; I:=I+1; END LOOP; DBMS_OUTPUT. PUT_LINE(TO_CHAR(S); END;,4.2 LOOP-EXIT WHEN-END循环 LOOP 语句序列 EXIT WHEN 条件 -等价IF 条件 THEN EXIT，可用在其它循环 END LOOP,例15：计算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_LINE(TO_CHAR(S); END;,4. 3 WHILE-LOOP-END循环 WHILE 条件 LOOP 每次循环前计算条 语句序列； 件，为TRUE，执 END LOOP； 行语句，否则不。,例16：计算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;,例17：在WHILE循环中使用EXIT或EXIT WHEN 计算S=1*2*3+2*3*4+N*(N+1)*(N+2);当N=40 DECLARE 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可用在任何循环内， 且只能在循环内。,4.4 FOR-IN-LOOP-END循环 FOR 循环变量 IN REVERSE下界上界 LOOP 语句序列； END LOOP；,说明： 循环变量被隐式说明为BINARY-INTEGER，也可显式说明。 步长1或-1(REVERSE)，循环次数:上界-下界+1; EXIT或EXIT WHEN可用在FOR循环中； 已知循环次数时，可用FOR循环，也可用其它 末知循环次数不能用FOR循环 上界或下界可以为表达式。,例18：显示20到50的平方根的值及它们的和。 DECLARE S FLOAT :=0; BEGIN FOR I IN 2050 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; 说明：循环变量I不用说明。,例19: 步长-1。加REVERSE。 /*说明：加REVERSE，上界仍要大于下界* / DECLARE S FLOAT :=0; BEGIN FOR I IN REVERSE 2050 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;,例20:上下界为表达式的FOR循环 DECLARE S FLOAT :=0; N INT :=10; BEGIN FOR I IN REVERSE 2*N-1N*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;,例21: 生成字符串SWJ001-SWJ120 declare n number(1); sn varchar2(8); sn1 char(3); begin for n in 1120 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_OUTPUT.PUT_LINE(SN); end loop; End;,4. 5 循环嵌套 上面的任何循环都可以互相嵌套，且可以多重嵌 套，但必须是完全嵌套。如： LOOP FOR LOOP -可有其它循环(多重循环) END LOOP; WHILE LOOP -可有其它循环(多重循环) END LOOP; END LOOP;,例22: 两重循环，计算S=1!+2!+10! DECLARE S FLOAT :=0; N INT :=10; K INT ; BEGIN FOR I IN 1N LOOP - 计算阶乘和 K:=1; FOR J IN 1I 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;,4.6 循环标签 循环标签是以用来标示循环,放在循环前。其格式： 例23 DECLARE S FLOAT :=0; N INT :=10; K INT ; BEGIN FOR I IN 1N LOOP - 计算阶乘和 K:=1; FOR J IN 1I 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;,循环标签一是提高程序的可读性，二是可以 从内循环跳到外层。 LOOP . LOOP . EXIT outer WHEN . 退出两重循环。 END LOOP; . END LOOP outer; 注意：只能从内循环跳到外循环，反之不行。,五、%TYPE类型 PL/SQL变量用来存储表中的数据，但二者要有相 同类型。要保证变量与列名类型总是一致，就要 用%TYPE类型。 变量名 表名.列名%TYPE; 例24：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;,六、%ROWTYPE类型 用%ROWTYPE说明变量为一个记录类型。 变量名 表名%ROWTYPE; 引用变量时：变量名.列名 例25：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;,七、游标(CURSOR) 游标是将从表中选择的一组记录，放置在内存的 临时表中。游标是数据类型，要先说明后使用。 说明游标 CURSOR 游标名 IS SELECT语句； 例26: DECLARE MIN_S JOBS.MIN_SALARY%TYPE; CURSOR MS IS SELECT * FROM JOBS WHERE MIN_SALARY=MIN_S; BEGIN NULL; -空语句 END;,2. 打开游标 OPEN 游标名； 注意：打开之前，游标中的变量必须有值；打开 游标后,查询记录放在内存,指针指向第一个记录。 例27：DECLARE MIN_S JOBS.MIN_SALARY%TYPE; CURSOR MS IS SELECT * FROM JOBS WHERE MIN_SALARY=MIN_S; BEGIN MIN_S:=1000; -先给变量赋值 OPEN MS; - 才能打开游标 END;,3. 提取游标数据 FETCH 游标名 INTO 变量1,变量2,; 注意: 变量要与说明游标时SELECT后的列名在类 型和个数上完成一样。不能只提取部分。 FETCH MS INTO J1，J2，M1，M2； 4. 关闭游标 CLOSE 游标； CLOSE MS； 关闭后的游标不能再使用。 使用游标的过程：说明游标、变量赋值、打开游 标、处理、关闭游标。,例28：提出数据 DECLARE J1 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| |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;,例29: 复杂游标（分组） DECLARE DEP EMPLOYEES.DEPARTMENT_ID%TYPE; COU INT; CURSOR DEPT IS SELECT 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;,5. 游标属性 通过游标属性来知道游标的状态。 A: %FOUND 如果前一个FETCH返回一行，%FOUND返回 TRUE；否则返回FALSE。在未打开游标之前设 置%FOUND，返回错误ORA_1001。用法： 游标名%FOUND %NOTFOUND 与%FOUND相反，常用于退出循环提出。 C. %ISOPEN 判定游标是否打开。打开为TRUE,否则为FALSE.,%ROWCOUNT 返回从游标中已提取的行数。,例30：declare emp employees %rowtype; cursor sal is select * into emp from employees where 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; CLOSE sal end;,例31：测试游标属性 declare emp employees %rowtype; cursor sal is select * into emp 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;,例32: 有表DATA_FIEL(n1,n2,n3,exnum)和表TEMP DECLARE 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; result := num2/(num1 + num3); INSERT INTO temp VALUES (result, NULL, NULL); END LOOP; CLOSE c1; COMMIT; END;,八、参数化游标和隐含游标 参数化游标 CRUSOR 游标名(变量1 类型1,变量2 类型2,) IS SELECT语句 说明：SELECT语句的条件中使用变量,变量类型 可以为%TYPE。 打开参数化游标： OPEN 游标名(实参1,实参2,); 说明：在打开时所有实参要有具体值，且在数量 和类型上与游标说明时一致。 实参可以是表达式。,例33：带参数游标 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 LOOP; CLOSE c1; END;,例34: 带参数游标 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%found 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;,2. 隐含游标（SQL游标) 每次处理一个SQL语句，ORACLE自动打开一个 游标，该游标不能使用OPEN,FETCH或CLOSE进 行操作。但可用游标特性来获得执行SQL的信息。 隐含游标的名字是：SQL SQL游标总是反映最后一个SQL语句执行情况。 %ISOPEN 永远是FALSE(执行完SQL后自动关闭） %FOUND 至少处理一行,为TRUE;否则为FALSE %NOTFOUND 与%FOUND相反 %ROWCOUNT 处理影响的行数。,例35: 隐含游标的属性使用 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(没有删除任何行); 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;,BEGIN UPDATE emp SET salary=salary+100; if SQL%FOUND THEN DBMS_OUTPUT.put_line(被修改的行数：|sql%rowcount); end if; delete from dept; if sql%found then dbms_output.put_line(被删除的行数:|SQL%ROWCOUNT); end if; INSERT INTO DEPT VALUES (01,NETWORK); INSERT INTO DEPT VALUES (02,SOFTWARE); if sql%found then dbms_output.put_line(最近插入的行数:|SQL%ROWCOUNT); end if; rollback;,SQL / 被修改的行数：107 被删除的行数:28 最近插入的行数:1 PL/SQL procedure successfully completed,上机题： 计算1！+2！+3！+10!的值。 计算 x/1!+x2/2!+.+xn/n! (其中X2,n=10) 编程序求满足不等式 1+32+52+N22000的最小N值。 计算下面级数当末项小于0.001时的部分和。 1/(1*2)+1/(2*3)+1/(3*4)+1/(n*(n+1)+ 计算并输出1900-2000之间的素数的个数N 定义游标：从雇员表中显示工资大于10000的记录，只要姓名、部门编号和工资。编程显示其中的奇数记录。 定义游标：列出每个员工的姓名、部门名称。编程显示第10个到第20个记录。,8. 在JOBS表中删除工作编号为AD_NEW的记录，如果无，插入(AD_NEW,POLICE,1999,20000) 9. 将雇员表中的所有工资小于10000增加1000，统计出增加工资的人数及增加的工资数量。 10. 将雇员表中的部门编号为100的所有员工删除，统计删除的人数及删除人的平均工资。 11. 编程输出9*9乘法表。 12. 从雇员表中显示工资最高的前五个人的姓名，部门和工资。,14.5过程、函数、触发器,无名块只能以文件形式执行、不存放在数据库中， 每次执行要编译，不能在其它PL/SQL块中调用。 过程、函数和触发器都是有名块，是数据库对象,一、过程设计 创建过程(系统权限CREATE PROCEDURE) 过程结构如下： CREATE OR REPLACE PROCEDURE 过程名(参数列表) AS 说明部分 -内部变量 BEGIN 执行部分 EXCEPTION 异常处理部分 END 过程名,说明： 过程必须有名字; 过程可以有参数或无参数 创建过程经编译无错后存放在数据库中(不马上执行)； 调用过程时执行块内语句。 过程的使用：先创建后调用。,例1. Create or replace procedure test1 as sal employees.salary%type; begin select salary into sal from employees where last_name=Abel; dbms_output.put_line(to_char(sal); end; 注意:即使程序编译有错，过程也将写入数据库。,查询过程USER_SOURCE USER_SOURCE: NAME、TEXT、TYPE、LINE SELECT TEXT FROM USER_SOURCE WHERE NAME=TTT ORDER BY LINE; 3. 修改过程 在OEM中，在指定用户模式下，选中过程名，通过查看/编辑详细资料，可修改。 EDIT 4. 调用过程(EXECUTE ANY PROCEDURE) 在块中或过程中调用存储过程，不能在表达式中调用。,调用方式：过程名(实参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;,6. 带参数的过程 CREATE OR REPLACE PROCEDURE 过程名 (参数1 IN|OUT|IN OUT 类型1,) AS 内部变量说明 BEGIN 执行部分 EXCEPTION 异常处理 END； 说明：IN 向过程送参数，读入参数 OUT: 从过程获得参数，输出参数 IN OUT 即可以读入参数，可也获得参数,例5: create or replace procedure test2( v1 in employees.job_id%type, -参数说明 v2 out employees.salary%type) as vv employees.salary%type;-内部变量说明 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;,例6：求阶乘 create or replace procedure jc(v1 in int, v2 out int) as s int; begin s:=1; for i in 1v1 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;,注意： 过程调用参数类型与个数必须完全一致；,OUT或IN OUT参数在调用时不能对应表达式；例：JC(K+1,K);-正确 但JC(K+1,K*2);非法,参数表示对应表列时，最好%TYPE类型,过程调用只能出现在块中，不能与表达式计算,可以多个OUT参数来返回多个值。,IN参数在过程中不能赋值,如果要给参数赋值，必须指定为OUT或IN OUT,不指明IN、OUT或IN OUT时，缺省为IN 出错信息表USER_ERRORS(列：LINE,TEXT,),例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 select * from jobs where job_idj; k int; begin k:=1; if not j_c%isopen then open j_c; end if; loop fetch j_c into jk; exit when n=k; k:=k+1; end loop; close j_c; end;,调用： declare jj jobs%rowtype; begin jobk(ST_MAN,jj,3); dbms_output.put_line(jj.job_id| |jj.job_title); jobk(FI_MGR,jj,5); dbms_output.put_line(jj.job_id| |jj.job_title); end;,例8: 显示表EMPLOYEES的第N条记录的内容 create or replace procedure n_record(n int, emp out employees%rowtype) as k int; cursor e_c is select * from employees; begin k:=1; if not e_c %isopen then open e_c; end if; loop fetch e_c into emp; exit when e_c%notfound or k=n; k:=k+1; end loop; if kn then dbms_output.put_line(NO FOUND); end if; end;,例9: 显示MN条记录的姓名和工资 create or replace procedure mn_record(m int , n int) as jj employees %rowtype; k int; begin k:=m; loop n_record(k,jj); -在mn_record过程中调用过程 dbms_output.put_line(jj.last_name| |to_char(jj.salary); k:=k+1; exit when kn; end loop; end; 调用： BEGIN mn_record(10,20); END;,二、函数设计 函数是命名块，可以有参数或无参数，但必须有 返回值，函数是用于表达式中而不能单独作为一 个命令行。其它与过程相似。 建立函数 CREATE OR REPLACE FUNCTION 函数名 (参数1IN|OUT|IN OUT,) RETURN 类型 AS 内部参数说明 BEGIN 执行部分或有异常处理 END;,例1:判定整数N是否为素数 create or replace function pri(n int) return boolean as k int; flag boolean; - ii int; begin k:=round(sqrt(n); flag:=false; for I in 2k loop ii:=i; exit when mod(n,i)=0; end loop; if iik then flag:=false; -return false; elsif mod(n,ii)=0 then flag:=false; -return false; else flag:=true; -return true; end if; return flag; - end;,例2：显示2到200之间的所有素数 begin for i in 2200 loop if pri(i) then dbms_output.put_line(to_char(i); end if; end loop; end;,例3：函数中的游标 create or replace function dep return int as cursor de is select department_id,count(*) from employees group by department_id; S int:=0; d1 employees.department_id%type; i1 int; Begin open de; loop fetch de into d1,i1; s:=s+i1; exit when de%notfound; end loop; return s; End; - 无参数函数，调用时用DEP()或DEP 调用：select dep()+10 from dual;,2. 删除函数和修改函数 修改函数是以文件存在,在SQL PLUS:get filename 删除函数：DROP FUNCTION 函数名； 3. 查询函数(USER_SOURCE) NAME,TYPE,TEXT,LINE 三、子程序 内置子程序(Stored Subprogram：过程与函数) 用Create or replace 建立的子程序是内置子程序， 它们存放在数据库中，它是数据库对象。任何位 置都可调用。 USER_OBJECTS： 用户所有对象信息 USER_SOURCE,USER_ERROR(编译错误信息),2. 本地子程序 如果子程序是在PL/SQL块中说明部分定义，叫本 地子程序。它只能在本块中调用。本地子程序的