PL/SQL入门

第九章 PL/SQL
PL/SQL : Procudure Language/SQL
PL/sql中可以写增删改.不能出现ddl语句
PL/SQL组成:定义部分,执行部分,异常处理部分.
定义部分:定义自已使作的变量,常量,异常,游标.
执行部分:begin .. end;
异常部分:错误处理程序.
Declare ...
begin...
exception..
end;
-----
SET SERVROUTPUT ON; --不能写在脚本里.
BEGIN
DBMS_OUTPUT.PUT_LINE("HELLO,WORLD!");
END;

DBMS_OUTPUT.PUT();
DBMS_OUTPUT.PUT_LINE();
每一个语句都要以分号结束.
PL/SQL中的SELECT:
SELECT XX,XX INTO XX,XX ..
DECLARE
EMP_NAME VARCHAR(50);
BEGIN
//保证单行查询,多行用游标处理.
//否则会报错:TOO MANY ROWS
  SELECT ENAME INTO EMP_NAME FROM EMP;
END;

DECLARE
  EMP_NAME VARCHAR2(50);
BEGIN
  // 会报NO DATA ERROR
  SELECT ENAME INTO EMP_NAME FROM EMP WHERE EMPNO=9000;
END;

PL/SQL中使用INSERT,UPDATE,DELETE基本一样.
注意使用COMMIT,ROLLBACK;
------------
定义部分:
支持的数据类型
BINARY_INTEGER,NUMBER,CHAR,VARCHAR2,LONG,RAW,BOOLEAN,DATE,RECORD,TABLE
定义变量:EMP_ID VARCHAR(10) := '111'
B_SEX BOOLEAN;
C_ZERO CONSTANT CHAR(1) := '0';
D_TODAY DATE NOT NULL := SYSDATE;
AGE NUMBER(3) NOT NULL := 25;
在此处申明的变量不能与列名相同.

使用%TYPE -> EMP.EMPNO%TYPE;

DECLARE
TYPE LOCATION_RECORD_TYPE IS RECORD(
  EMP_NAME EMP.ENAME%TYPE,
  EMP_NO EMP.EMPNO%TYPE
);

使用%ROWTYPE
DECLARE
  EMP_VALUE EMP%ROWTYPE;
  EMPLOYEE EMP_VALUE;
BEGIN
SELECT * INTO EMPLOYEE FROM EMP WHERE...
...
END;

类似全局变量
VARIABLE NAME VARCHAR2(30)定义了一个变量
PRINT NAME; 查看变量
// 全局变量
EXEC :NAME := 'ABC'; 最简单的赋值语句

BEGIN
  :NAME := :NAME || 'HELLO';
END;

PRINT NAME;

BEGIN
  INSERT INTO EMP(EMPNO,ENAME) VALUES(&ENO,&ENAME);
END;

TABLE数据类型存一维数组,可动态增长.

DECLARE
TYPE TYPE_TABLE IS TABLE OF VARCHAR2(2) INDEX BY BINARY_INTEGER;
V_T TYPE_TABLE;
BEGIN
V_T(1) := 'XX';
V_T(2) := 'XX';
V_T(3) := 'XX';
END;
=====================
可执行部分
运算符和函数
+ - * /
IS NULL, LIKE, /BETWEEN .. AND.., IN(...);

DECLARE
A NUMBER(5,0);
BEGIN
A := ROUND(1.34);
END;

条件控制:
IF .. THEN .. END IF;
IF .. THEN .. ELSE .. END IF;
IF .. THEN ..ELSIF .. THEN .. ELSE ...END IF;

选择结构
CASE ..
WHEN .. THEN...
WHEN .. THEN ..
ELSE ..
END CASE;

CASE
WHEN .. THEN ..
WHEN .. THEN..
ELSE ..
END CASE;

循环结构
LOOP
....
EXIT WHEN ...
END LOOP;

FOR XX IN 1..100 LOOP
....
END LOOP;
循环控制变量不需要特别声明,可直接使用.

WHILT循环
WHILE .. LOOP
....
END LOOP;

顺序控制语句
GOTO, NULL
NULL语句格式是:NULL;
===============
游标
显示与隐示之分
隐式游标属性:SQL%ROWCOUNT,SQL%FOUND,SQL%NOTFOUND,SQL%ISOPEN;

显示游标(最重要)
定义游标
打开游标
提取数据
关闭游标
DECLARE
CURSOR XX IS SELECT * FROM EMP;
BEGIN
OPEN XX;
CLOSE XX;
END;

DECLARE
   CURSOR XX IS SELECT * FROM EMP;
   EMPLOYEE EMP%ROWTYPE;
BEGIN
   OPEN XX;
           FETCH XX INTO EMPLOYEE;
           DBMS_OUTPUT.PUT_LINE(EMPLOYEE.ENAME);
   CLOSE XX;
END;

游标属性:%ISOPEN,%FOUND,%NOTFOUND,%ROWCOUNT
DECLARE
    CURSOR XX IS SELECT * FROM EMP;
    EMPLOYEE EMP%ROWTYPE;
BEGIN
    OPEN XX;
    LOOP
            EXIT WHEN XX%NOTFOUND;
            FETCH XX INTO EMPLOYEE;
            DBMS_OUTPUT.PUT_LINE(EMPLOYEE.ENAME);
    END LOOP;
    CLOSE XX;

对于WHILE循环一定要先取一条,然后再用FOUND来判断
DECLARE
   CURSOR XX IS SELECT * FROM EMP;
   EMPLOYEE EMP%ROWTYPE;
BEGIN
   OPEN XX;
   FETCH XX INTO EMPLOYEE;
   WHILE XX%FOUND LOOP
           DBMS_OUTPUT.PUT_LINE(EMPLOYEE.ENAME);
           FETCH XX INTO EMPLOYEE;
   END LOOP;
   CLOSE XX;
END;

游标循环
FOR EMP IN EMP_CURSOR LOOP
XXX
END LOOP;

DECLARE
    CURSOR XX IS SELECT * FROM EMP;
    EMPLOYEE EMP%ROWTYPE;
BEGIN
    FOR E IN XX LOOP
            DBMS_OUTPUT.PUT_LINE(E.ENAME);
    END LOOP;
END;
游标循环遍历最简化版:
BEGIN
    FOR E IN (SELECT * FROM EMP) LOOP
            DBMS_OUTPUT.PUT_LINE(E.ENAME);
    END LOOP;
END;

游标参数传递
DECLARE
  CURSOR XX(ENA EMP.ENAME%TYPE) IS SELECT * FROM EMP WHERE ENAME LIKE
   '%'||ENA||'%';
BEGIN
  FOR E IN XX('&EN') LOOP
   DBMS_OUTPUT.PUT_LINE(E.ENAME);
  END LOOP;
END;

========================
异常处理:TO_MAMANY_ROWS,DUP_VAL_ON_INDEX,
EXCEPTION
WHEN ERROR1 THEN...
WHEN ERROR2 THEN...
WHEN OTHERS ...
END;

DECLARE
  EMP_NAME EMP.ENAME%TYPE;
BEGIN
  SELECT ENAME INTO EMP_NAME FROM EMP;
EXCEPTION
WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('EXCEPTION');
END;
如果是自已的异常要ROLLBACK,如果是系统的异常,系统会自动ROLLBACK.

引发系统异常
RAISE_APPLICATION_ERROR(-20004,'出错了');