23. PL/SQL块
注意PL/SQL块中只能直接嵌入SELECT、DML语句以及事务控制语句，而不能直接嵌入DDL语句和DCL语句(GRANT、REVOKE)。
23.1 PL/SQL块嵌入SELECT语句
注意接收变量INTO与SELECT的列的数量及数据类型要一致。
-- ex:pl/sql_12--SQL*PLUS下开启输出显示SQL> set serveroutput on-- 定义匿名PL/SQL块SQL> DECLARE 2 V_ENAME EMP.ENAME%TYPE; 3 V_SAL EMP.SAL%TYPE; 4 5 BEGIN 6 SELECT ENAME, SAL 7 INTO V_ENAME, V_SAL 8 FROM EMP WHERE EMPNO = &a;--手动输入empno的值 9 10 DBMS_OUTPUT.PUT_LINE('name:' || V_ENAME); 11 DBMS_OUTPUT.PUT_LINE('sal:' || V_SAL); 12 --异常处理 13 EXCEPTION 14 WHEN OTHERS THEN 15 DBMS_OUTPUT.PUT_LINE('error'); 16 END; 17 /输入 a 的值: 7900原值 8: FROM EMP WHERE EMPNO = &a;新值 8: FROM EMP WHERE EMPNO = 7900;name:JAMESsal:950PL/SQL 过程已成功完成。
23.2 PL/SQL块中嵌入INSERT语句
注意插入数据时，必须为表的主键列和NOT NULL列提供插入数据.
-- ex:pl/sql_13 DECLARE --定义记录类型变量 TYPE DEPT_RECORD_TYPE IS RECORD( V_DNAME DEPT.DNAME%TYPE, V_DEPTNO DEPT.DEPTNO%TYPE); DEPT_RECORD DEPT_RECORD_TYPE;BEGIN --给记录的各变量赋值 DEPT_RECORD.V_DEPTNO := &NO; DEPT_RECORD.V_DNAME := '&name';--插入dept表 INSERT INTO DEPT(deptno,dname) VALUES (DEPT_RECORD.V_DEPTNO,DEPT_RECORD.V_DNAME);EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Insert error!');END;--SQL*PLUS下测试SQL> /输入 no 的值: 60原值 9: DEPT_RECORD.V_DEPTNO := &NO;新值 9: DEPT_RECORD.V_DEPTNO := 60;输入 name 的值: Test原值 10: DEPT_RECORD.V_DNAME := '&name';新值 10: DEPT_RECORD.V_DNAME := 'Test';PL/SQL 过程已成功完成。
23.3 PL/SQL中使用UPDATE语句
注意更新列值时要满足该列的各种约束条件.
-- ex:pl/sql_14DECLARE --定义变量 V_ENAME EMP.ENAME%TYPE;BEGIN --给变量赋值 V_ENAME := '&name'; --使用子查询更新额emp表 UPDATE EMP SET (SAL, COMM) = (SELECT SAL, COMM FROM EMP WHERE ENAME = V_ENAME) WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = V_ENAME);EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Update error!');END;--SQL*PLUS测试SQL> /输入 name 的值: ALLEN原值 7: V_ENAME := '&name';新值 7: V_ENAME := 'ALLEN';PL/SQL 过程已成功完成。
23.4 PL/SQL中使用DELETE语句
注意删除的时候，有主外键约束的话，那么删除主表(没有外键约束的那个表)的时候，会出错。
-- ex:pl/sql_15DECLARE --定义变量 V_ENAME EMP.ENAME%TYPE;BEGIN --给变量赋值 V_ENAME := '&name'; --使用子查询删除emp表相关数据 DELETE FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = V_ENAME);EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Delete error!');END;--SQL*PLUS下测试SQL> /输入 name 的值: ALLEN原值 7: V_ENAME := '&name';新值 7: V_ENAME := 'ALLEN';PL/SQL 过程已成功完成。
23.5 PL/SQL中使用游标(CURSOR)
游标的几种属性SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT,SQL%ISOPEN。
-- ex:pl/sql_16 DECLARE --定义行变量 V_EMP EMP%ROWTYPE;BEGIN SELECT * INTO V_EMP FROM EMP WHERE ROWNUM = 1; --隐式游标属性SQL%ISOPEN,隐式游标自动打开关闭,SQL%ISOPEN永为FALSE. IF SQL%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('Cursor is open'); ELSE DBMS_OUTPUT.PUT_LINE('Cursor is close'); END IF ; --隐式游标属性SQL%FOUND IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('name1:' || V_EMP.ENAME); ELSE DBMS_OUTPUT.PUT_LINE('no data Found11'); END IF; --隐式游标属性SQL%NOTFOUND IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('no data Found22'); ELSE DBMS_OUTPUT.PUT_LINE('name2:' || V_EMP.ENAME); END IF; --隐式游标属性SQL%COUNT UPDATE EMP SET SAL = SAL * 1.3 WHERE DEPTNO = V_EMP.DEPTNO; DBMS_OUTPUT.PUT_LINE('修改了 ' || SQL%ROWCOUNT || ' 行数据');EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('error!');END;--SQL*PLUS下测试Cursor is close name1:SMITHname2:SMITH修改了 3 行数据PL/SQL 过程已成功完成。
23.6 PL/SQL中使用事务控制语句
-- ex:pl/sql_17BEGIN UPDATE EMP SET SAL = sal+SAL * 1.1; SAVEPOINT UP1; INSERT INTO DEPT (DEPTNO, DNAME) VALUES (50, 'Hello'); SAVEPOINT IN2; DELETE FROM EMP WHERE DEPTNO = 10; SAVEPOINT DE3; DBMS_OUTPUT.PUT_LINE('DML全部操作完成.'); ROLLBACK TO IN2; DBMS_OUTPUT.PUT_LINE('回滚到插入操作后状态.'); COMMIT; DBMS_OUTPUT.PUT_LINE('提交完成');EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('DML操作出错!'); END;--SQL*PLUS下测试DML全部操作完成.回滚到插入操作后状态.提交完成PL/SQL 过程已成功完成。
24. 控制结构
分为条件分支结构、循环结构和顺序结构.
24.1 条件分支结构
三种语句:IF-THEN,IF-THEN-ELSE,IF-THEN-ELSIF(注意没有E,不是ELSEIF)
-- ex:pl/sql_18SQL> DECLARE 2 V_num NUMBER:=77; 3 BEGIN 4 IF v_num>=90 THEN 5 dbms_output.put_line('A'); 6 ELSIF v_num>=80 AND v_num<90 THEN 7 dbms_output.put_line('B'); 8 ELSIF v_num>=60 AND v_num<80 THEN 9 dbms_output.put_line('C'); 10 ELSE 11 dbms_output.put_line('D'); 12 END IF; 13 14 EXCEPTION 15 WHEN OTHERS THEN 16 dbms_output.put_line('Error!'); 17 END; 18 /CPL/SQL 过程已成功完成。