存儲過程(學習筆記)，存儲過程學習筆記

存儲過程(學習筆記)，存儲過程學習筆記

存儲過程指的是在大型數據庫系統中專門定義的一組SQL語句集，它可以定義用戶操作參數，並且存在於數據庫中，當使用時直接調用即可
存儲過程=過程的聲明+PL/SQL塊
定義過程語法：
CREATE [OR REPLACE] PROCEDURE 過程名稱([參數名稱[參數模式] NOCOPY 數據類型 [參數名稱 [參數模式] NOCOPY 數據類型,...]])
[AUTHID [DEFINER | CURRENT_USER]]
AS || IS
[PRAGMA AUTONOMOUS_TRANSACTION;]
聲明部分;
BEGIN
程序部分;
EXCEPTION
導常處理;
END;
參數中定義參數模式表示過程的數據的接收操作，一般分為IN,OUT,IN OUT 3類
CREATE [OR REPLACE]：表示創建或者替換過程，如果過程存在則替換，如果不存在就創建一個新的
AUTHID子句定義了一個過程的所有者權限，DEFINER（默認）表示定義者權限執行，或者用CURRENT_USER覆蓋程序的默認行為，變為使用者權限
PRAGMA AUTONOMOUS_TRANSACTION:表示過程啟動一個自治事務，自治事務可以讓主事掛起，在過程中執行完SQL後，由用戶處理提交或者回滾自治事務，
然後恢復主事務
EXECUTE 過程名 來調用過程
或者EXEC 過程名

在sqlplus中設置過程顯示
SET serveroutput ON

過程授權
GRANT EXECUTE ON 過程名 TO 用戶名 --將執行權授給用戶，但用戶不能再授權給其它用戶
GRANT EXECUTE ON 過程名 TO 用戶名 WITH GRANT OPTION; --將執行權授給用戶，但用戶可以再授權給其它用戶

示例一、定義一個簡單的過程

CREATE OR REPLACE PROCEDURE bdqn_proc
AS
BEGIN
  dbms_output.put_line('學習使用存儲過程!');
END;
執行
EXEC bdqn_proc;

示例二、定義一個簡的過程

CREATE OR REPLACE PROCEDURE FIND_EMP(PNO EMP.EMPNO%TYPE) AS
  V_ENAME EMP.ENAME%TYPE;
  V_JOB   EMP.JOB%TYPE;
  V_COUNT NUMBER;
BEGIN
  SELECT COUNT(EMPNO) INTO V_COUNT FROM EMP; --查詢表中的記錄總數
  IF V_COUNT = 0 THEN
    --判斷是否有記錄0表示沒有
    RETURN; --結束
  END IF;
  SELECT ENAME, JOB INTO V_ENAME, V_JOB FROM EMP WHERE EMPNO = PNO; --查尋姓名和職位，並將值傳給變量
  DBMS_OUTPUT.PUT_LINE('員工編號: ' || PNO || ' 員工姓名: ' || V_ENAME ||
                       ' 員工職位： ' || V_JOB);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('沒有找到數據');
END FIND_EMP;
執行
EXECUTE FIND_EMP(&pnd)

示例三、使用過程增加部門

CREATE OR REPLACE PROCEDURE dpetadd_proc(

v_deptno               dept.deptno%TYPE,
v_dname                dept.dname%TYPE,
v_loc                  dept.loc%TYPE)

AS
 v_count                NUMBER;
BEGIN
  SELECT COUNT(deptno) INTO v_count FROM dept WHERE deptno=v_deptno;          --統計
  IF v_count>0 THEN
    raise_application_error(-20888,'增加失敗,這個部門已經存在');
    ELSE
      INSERT INTO dept(deptno,dname,loc)VALUES(v_deptno,v_dname,v_loc);
      dbms_output.put_line('新部門增加成功');
      COMMIT;
  END IF;
EXCEPTION 
  WHEN OTHERS THEN
    dbms_output.put_line('SQLERRM='||SQLERRM);
    ROLLBACK;
END;
執行
EXEC dpetadd_proc(10,'北大青鳥','北京')
EXEC dpetadd_proc(90,'北大青鳥','北京')
SELECT * FROM dept;

示例四、使用過程增加部門

CREATE OR REPLACE PROCEDURE dpetadd2_proc(

v_deptno               dept.deptno%TYPE:=&deptno,
v_dname                dept.dname%TYPE:='&dname',
v_loc                  dept.loc%TYPE:='&loc')

AS
 v_count                NUMBER;
BEGIN
  SELECT COUNT(deptno) INTO v_count FROM dept WHERE deptno=v_deptno;          --統計
  IF v_count>0 THEN
    raise_application_error(-20888,'增加失敗,這個部門已經存在');
    ELSE
      INSERT INTO dept(deptno,dname,loc)VALUES(v_deptno,v_dname,v_loc);
      dbms_output.put_line('新部門增加成功');
      COMMIT;
  END IF;
EXCEPTION 
  WHEN OTHERS THEN
    dbms_output.put_line('SQLERRM='||SQLERRM);
    ROLLBACK;
END;
執行
EXEC dpetadd2_proc(10,'北大青鳥','北京')
EXEC dpetadd2_proc(16,'北大青鳥','北京')

示例五、使用過程查詢1981年入職的員工的工資和公司平均工資比較小於輸出低工資，等於工資還行，高於輸出高工資

CREATE OR REPLACE PROCEDURE SEARCH_PRO AS
  V_EMPNO    EMP.EMPNO%TYPE;
  V_NAME     EMP.ENAME%TYPE;
  V_JOB      EMP.JOB%TYPE;
  V_HIREDATE EMP.HIREDATE%TYPE;
  V_GRADE    SALGRADE.GRADE%TYPE;
  V_AVG      NUMBER;
  V_DNAME    DEPT.DNAME%TYPE;
  V_SAL      EMP.SAL%TYPE;
  CUR_S      SYS_REFCURSOR;
BEGIN
  SELECT AVG(SAL) INTO V_AVG FROM EMP;
  OPEN CUR_S FOR
    SELECT E.EMPNO, E.ENAME, E.JOB, E.HIREDATE, E.SAL, S.GRADE, D.DNAME
      FROM EMP E, SALGRADE S, DEPT D
     WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
       AND E.DEPTNO = D.DEPTNO(+);
  LOOP
    FETCH CUR_S
      INTO V_EMPNO, V_NAME, V_JOB, V_HIREDATE, V_SAL, V_GRADE, V_DNAME;
    EXIT WHEN CUR_S%NOTFOUND;
    IF V_SAL < V_AVG THEN
      DBMS_OUTPUT.PUT_LINE(CUR_S%ROWCOUNT || '員工編號:' || V_EMPNO || ' 姓名:' ||
                           V_NAME || ' 職位:' || V_JOB || ' 入職日期:' ||
                           V_HIREDATE || ' 工資:||v_sal' || ' 工資等級' ||
                           V_GRADE || ' 部門名稱:' || V_DNAME);
      DBMS_OUTPUT.PUT_LINE('工資太低了');
    ELSIF V_SAL = V_AVG THEN
      DBMS_OUTPUT.PUT_LINE(CUR_S%ROWCOUNT || '員工編號:' || V_EMPNO || ' 姓名:' ||
                           V_NAME || ' 職位:' || V_JOB || ' 入職日期:' ||
                           V_HIREDATE || ' 工資:||v_sal' || ' 工資等級' ||
                           V_GRADE || ' 部門名稱:' || V_DNAME);
      DBMS_OUTPUT.PUT_LINE('工資還行');
    ELSE
       DBMS_OUTPUT.PUT_LINE(CUR_S%ROWCOUNT || '員工編號:' ||
                                              V_EMPNO || ' 姓名:' || V_NAME ||
                                              ' 職位:' || V_JOB || ' 入職日期:' ||
                                              V_HIREDATE || ' 工資:||v_sal' ||
                                              ' 工資等級' || V_GRADE ||
                                              ' 部門名稱:' || V_DNAME);
      DBMS_OUTPUT.PUT_LINE('工資高了');
    END IF;
  END LOOP;
  CLOSE CUR_S;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
執行
EXEC SEARCH_PRO

參數模式 

IN模式

示例一、定義過程使用IN默認可以不寫

CREATE OR REPLACE PROCEDURE in_proc(
     p_a IN VARCHAR2,                     --明確定義IN參數模式
     p_b IN VARCHAR2                      --默認的參數模式為in
) 
AS
BEGIN
  dbms_output.put_line('執行in_proc()過程: p_a='||p_a);
  dbms_output.put_line('執行in_proc()過程: p_b='||p_b);
END;

執行
DECLARE
    v_a VARCHAR2(50):='Java開發實戰經典';
    v_b VARCHAR2(50):='Oracle開發實戰經典';
BEGIN
    in_proc(v_a,v_b);
END;
結果:
執行in_proc()過程: p_a=Java開發實戰經典
執行in_proc()過程: p_b=Oracle開發實戰經典

示例二、定義過程使用default定義參數默認值

CREATE OR REPLACE PROCEDURE in_proc(
     p_a IN VARCHAR2 DEFAULT '好好學習JAVA',                   --明確定義IN參數模式
     p_b IN VARCHAR2 DEFAULT '努力看Oracle'                   --默認的參數模式為in
) 
AS
BEGIN
  dbms_output.put_line('執行in_proc()過程: p_a='||p_a);
  dbms_output.put_line('執行in_proc()過程: p_b='||p_b);
END;

DECLARE
    v_a VARCHAR2(50):='Java開發實戰經典';
   
BEGIN
    in_proc(v_a);
END;
結果:
執行in_proc()過程: p_a=Java開發實戰經典
執行in_proc()過程: p_b=努力看Oracle
使用了第二個參數沒有寫，使用了默認值，如果有傳遞參數則使用傳遞的參數

OUT模式

示例一、定義過程使用OUT

CREATE OR REPLACE PROCEDURE out_proc(
     p_a OUT VARCHAR2,                     --明確定義out參數模式
     p_b OUT VARCHAR2)                       --明確定義out參數模式

AS
BEGIN
  dbms_output.put_line('執行out_proc()過程: p_a='||p_a);
  dbms_output.put_line('執行out_proc()過程: p_b='||p_b);
   p_a :='Java開發實戰經典';               --將此值返回給實參
   p_b :='Oracle開發實戰經典';
END;

執行
DECLARE
    v_a VARCHAR2(50):='好好學習';
    v_b VARCHAR2(50):='天天向上';
BEGIN
    out_proc(v_a,v_b);
    dbms_output.put_line('調用out_proc()過程: v_a='||v_a);
  dbms_output.put_line('調用out_proc()過程: v_b='||v_b);
    
END;

結果:
執行out_proc()過程: p_a=
執行out_proc()過程: p_b=
調用out_proc()過程: v_a=Java開發實戰經典
調用out_proc()過程: v_b=Oracle開發實戰經典

OUT模式時，傳入的參數數是無用的，傳入的內容不會傳遞 到過程中去

inout模式

示例一、定義過程使用INOUT

CREATE OR REPLACE PROCEDURE inout_proc(
     p_a IN OUT VARCHAR2,                     --明確定義out參數模式
     p_b IN OUT VARCHAR2)                       --明確定義out參數模式

AS
BEGIN
  dbms_output.put_line('執行inout_proc()過程: p_a='||p_a);
  dbms_output.put_line('執行inout_proc()過程: p_b='||p_b);
   p_a :='Java開發實戰經典';               --將此值返回給實參
   p_b :='Oracle開發實戰經典';
END;

執行
DECLARE
    v_a VARCHAR2(50):='好好學習';
    v_b VARCHAR2(50):='天天向上';
BEGIN
    inout_proc(v_a,v_b);
    dbms_output.put_line('調用inout_proc()過程: v_a='||v_a);
  dbms_output.put_line('調用inout_proc()過程: v_b='||v_b);
    
END;
結果:
執行inout_proc()過程: p_a=好好學習
執行inout_proc()過程: p_b=天天向上
調用inout_proc()過程: v_a=Java開發實戰經典
調用inout_proc()過程: v_b=Oracle開發實戰經典

調用inout_proc過程時，將2個變量v_a,v_b傳入到了過程中，由於是INOUT模式，所過程可以接收到傳遞的變量內容，同時過程對變量做了修改也可以運回給實參

示例二、利用過程增加部門

CREATE OR REPLACE PROCEDURE DEPTINSER_PROC(
p_DNO    DEPT.DEPTNO%TYPE,
                                           p_DNAME  DEPT.DNAME%TYPE,
                                           p_LOC    DEPT.LOC%TYPE,
                                           P_RESULT OUT NUMBER --此為標記變量
                                           ) AS
  V_COUNT NUMBER; --保存count函數的結果
BEGIN
  SELECT COUNT(DEPTNO) INTO V_COUNT FROM DEPT WHERE DEPTNO = p_DNO;
  IF V_COUNT > 0 THEN
    P_RESULT := -1;
  ELSE
    INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (p_DNO, p_DNAME, p_LOC);
    P_RESULT := 0;
    COMMIT;
  END IF;
END;
調用
DECLARE
  V_RESULT NUMBER; --定義變量接收結果
BEGIN
  DEPTINSER_PROC(66, 'test', 'China', V_RESULT); --調用過程
  IF V_RESULT = 0 THEN
    DBMS_OUTPUT.PUT_LINE('新部門增加成功');
  ELSE
    DBMS_OUTPUT.PUT_LINE('新部門增加失敗');
  END IF;

END;

 示例三、利用OUT傳遞游標使用過程是查詢員工ID，姓名，職位，工資

CREATE OR REPLACE PROCEDURE search_pro(
       p_emp OUT SYS_REFCURSOR
)
AS
BEGIN
     OPEN p_emp FOR SELECT e.empno,e.ename,e.job,e.sal
                     FROM emp e;
END;

調用
DECLARE
  V_ID    EMP.EMPNO%TYPE;
  V_NAME  EMP.ENAME%TYPE;
  V_JOB   EMP.JOB%TYPE;
  V_SAL   EMP.SAL%TYPE;
  CUR_EMP SYS_REFCURSOR; --定義弱類型游標
BEGIN
  SEARCH_PRO(CUR_EMP);
  LOOP
    FETCH CUR_EMP
      INTO V_ID, V_NAME, V_JOB, V_SAL;
    EXIT WHEN CUR_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT || ' 員工編號:' || V_ID || ' 姓名:' ||
                         V_NAME || ' 職位:' || V_JOB || ' 工資:' || V_SAL);
  END LOOP;
  CLOSE CUR_EMP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

 自治事務

 使用下列語句聲明
PRAGMA AUTONOMOUS_TRANSACTION;

示例一、

CREATE OR REPLACE PROCEDURE dept_insert_proc
AS
       PRAGMA AUTONOMOUS_TRANSACTION;            --自治事務
BEGIN
      INSERT INTO dept(deptno,dname,loc)VALUES(80,'JAVA','北京');
      COMMIT;                                           --提交自治事務
END;
調用
DECLARE
BEGIN
   INSERT INTO dept(deptno,dname,loc)VALUES(60,'Oracl','深圳');
   dept_insert_proc();
   ROLLBACK;                                          --主事務回滾
END;

SELECT * FROM dept;

首先會向部門表中添加一條60部門的信息，此時調用過程，主程序會被掛起，到過程執行完

結果看出80部門已經添加成功，並沒有受到ROLLBACK的影響