PL/SQL学习八

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:JAMES
sal:950

PL/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_14
DECLARE
  --定义变量
  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_15
DECLARE
  --定义变量
  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:SMITH
name2:SMITH
修改了 3 行数据

PL/SQL 过程已成功完成。      

23.6 PL/SQL中使用事务控制语句

-- ex:pl/sql_17
BEGIN
  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_18
SQL> 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  /
C

PL/SQL 过程已成功完成。

多重分支结构CASE语句.

-- ex:pl/sql_19
declare
  v_deptno dept.deptno%type;

begin
  v_deptno := &no;
  case v_deptno
    when 30 then
      dbms_output.put_line('部门号为30!');
    when 20 then
      dbms_output.put_line('部门号为20!');
    when 10 then
      dbms_output.put_line('部门号为10!');
    else
      dbms_output.put_line('不存在此部门号');
    
  end case;

exception
  when others then
    dbms_output.put_line('CASE语句出错');
end;

输入no: 10
部门号为20!

循环结构(LOOP、WHILE、FOR).

-- ex:pl/sql_20
DECLARE
  V_NUM INTEGER := 1;
  V_SUM INTEGER := 0;
BEGIN
  LOOP
    V_SUM := V_NUM + V_SUM;
    V_NUM := V_NUM + 1;
    EXIT WHEN V_NUM = 101;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('1到100的和为:' || V_SUM);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('LOOP语句出错');
END;

输出:
1到100的和为:5050

-- ex:pl/sql_21
DECLARE
  V_NUM INTEGER := 1;
  V_SUM INTEGER := 0;
BEGIN
  WHILE V_NUM != 101 LOOP 
    V_SUM := V_NUM + V_SUM;
    V_NUM := V_NUM + 1;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('1到100的和为:' || V_SUM);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('WHILE语句出错');
END;

输出:
1到100的和为:5050

-- ex:pl/sql_22
DECLARE
  V_SUM INTEGER := 0;
BEGIN
  FOR V_NUM IN 1..100 LOOP 
    V_SUM := V_NUM + V_SUM;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('1到100的和为:' || V_SUM);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('FOR语句出错');
END;

输出:
1到100的和为:5050