PLSQL基础使用

PLSQL基础2

序列

CREATE SEQUENCE clxseq1MINVALUE 1MAXVALUE 999999START WITH 5INCREMENT BY 1CYCLENOCACHE;

块

DECLARE  I NUMBER;BEGIN  SELECT EMPNO   INTO I   FROM EMP   WHERE ENAME='SMITH'   DBMS_OUTPUT.PUT_LINE(I);END

索引—数组

DECLARE  TYPE TBL_NAMES IS TABLE OF EMP.ENAME%TYPE INDEX BY BINARY_INTEGER;  TBL_NAME1 TBL_NAMES;BEGIN  TBL_NAME1(0):='ZHANGSAN';  TBL_NAME1(1):='LISI';  DBMS_OUTPUT.PUT_LINE(TBL_NAME1(0));  DBMS_OUTPUT.PUT_LINE(TBL_NAME1(1));END;

BULK COLLECT集合之间的复制

DECLARE  TYPE TBL_EMP_INFO IS TABLE OF ENP%ROWTYPE INDEX BY BINARY_INTEGER;  EMP_TBL1 TBL_EMP_INFO;BEGIN  SELECT *    BULK COLLECT INTO EMP_TBL1  FROM EMP;  FOR I IN EMP_TBL1.FIRST..EMP_TBL1.LAST LOOP    DBMS_OUTPUT.PUT_LINE(EMP_TBL1(I).ENAME);  END LOOP;END;

动态SQL

执行动态SQL

DECLARE  STR_SQL VARCHAR2(200);BEGIN  STR_SQL:='CREATE TABLE TBL_TEST1 (T_ID NUMBER,T_NAME VARCHAR2(20))';  EXECUTE IMMEDIATE STR_SQL;END;

动态SQL传参

DECLARE  T_EMPNO EMP.EMPNO%TYPE:=7369;  T_ENAME EMP.ENAME%TYPE:'SMITH';  STR_SQL VARCHAR2(200);BEGIN  STR_SQL:='SELECT SAL FROM EMP WHERE EMPNO=:1 AND ENAME=:2';  EXECUTE IMMEDIATE STR_SQL INTO EMPNO,ENAME USING T_EMPNO,T_ENAME;END;

EXCEPTION

隐式触发EXCEPTION

DECLARE  I NUMBER:=0;BEGIN  I:=2/I;EXCEPTION WHEN OTHERS THEN  DBMS_OUTPUT.PUT_LINE(SQLERRM);END;

显示触发EXCEPTION

DECLARE  I NUMBER:=-1;  NUM_IS_NOT_POSITIVE EXCEPTION;--自定义EXCEPTIONBEGIN  IF I<0 THEN    RAISE NUM_IS_NOT_POSITIVE;  END IF;EXCEPTION WHEN NUM_IS_NOT_POSITIVE THEN    DBMS_OUTPUT.PUT_LINE('NUM_IS_NOT_POSITIVE');  WHEN OTHERS THEN    DBMS_OUTPUT.PUT_LINE(SQLERRM);END;

游标

隐式游标

DECLARE     V_NAME SCOTT.EMP.ENAME%TYPE;BEGIN    SELECT ENAME        INTO V_NAME    FROM SCOTT.EMP    WHERE SCOTT.EMP.EMPNO=1111;EXCEPTION WHEN OTHERS THEN    IF SQL%FOUND THEN        DBMS_OUTPUT.PUT_LINE('SUCCESS');    ELSE        DBMS_OUTPUT.PUT_LINE('FAILED');    END IF;    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);END;

显示游标(一般)

DECLARE  CURSOR TEST_CUR IS SELECT * FROM EMP;  T_EMP_INFO EMP%ROWTYPE;BEGIN  OPEN TEST_CUR;  FETCH TEST_CUR INTO T_EMP_INFO;  WHILE TEST_CUR%FOUND LOOP    FETCH TEST_CUR INTO T_EMP_INFO;  END LOOP;  CLOSE TEST_CUR;EXCEPTION WHEN OTHERS THEN  DBMS_OUTPUT.PUT_LINE(SQLERRM);END;

显示游标（使用for）

DECLARE    CURSOR T_CUR IS SELECT * FROM SCOTT.EMP;BEGIN    FOR REC IN CUR LOOP        DBMS_OUTPUT.PUT_LINE(REC.ENAME);    END LOOP;EXCEPTION WHEN OTHERS THEN    DBMS_OUTPUT.PUT_LINE(SQLERRM);END;

带参数的显示游标（使用for）

DECLARE    CURSOR T_CUR(T_EMPNO SCOTT.EMP.EMPNO%TYPE) IS        SELECT *         FROM SCOTT.EMP         WHERE SCOTT.EMP.EMPNO=T_EMPNO;BEGIN    FOR REC IN T_CUR(7436) LOOP        DBMS_OUTPUT.PUT_LINE(REC.ENAME);    END LOOP;EXCEPTION    DBMS_OUTPUT.PUT_LINE(SQLERRM);END;

显示游标(for update)

DECLARE    CURSOR T_CUR IS        SELECT *         FROM SCOTT.EMP        FOR UPDATE;BEGIN    FOR REC IN T_CUR LOOP        UPDATE SCOTT.EMP            SET SCOTT.EMP.ENAME=SCOTT.EMP.ENAME||'1';        WHERE CURRENT OF T_CUR;    END LOOP;    COMMIT;EXCEPTION WHEN OTHERS THEN    DBMS_OUTPUT.PUT_LINE(SQLERRM);END;

REF游标

DECLARE    TYPE T_REF_CUR IS REF CURSOR;    T_REF_CUR1 T_REF_CUR;    T_EMP_INFO SCOTT.EMP%TYPE;    STR_SQL VARCHAR2(200);BEGIN    STR_SQL:='SELECT * FROM SCOTT.EMP';    OPEN  T_REF_CUR1 FOR STR_SQL;    LOOP         FETCH T_REF_CUR1 INTO T_EMP_INFO;        EXIT WHEN T_REF_CUR1%NOTFOUND;            DBMS_OUTPUT.PUT_LINE(T_EMP_INFO.ENAME);    END LOOP;EXCEPTIONEND;