Oracle進階（二）存盤程序

目錄

1、名詞釋義

2、創建語法

3、系統例外

4、存盤程序

1、名詞釋義

存盤程序（Stored Procedure）是一組為了完成特定功能的SQL陳述句集，是由流程控制和SQL陳述句書寫的命名陳述句塊，
Oracle存盤程序包含三部分：程序宣告，執行程序部分，存盤程序例外，

2、創建語法

CREATE [OR REPLACE] PROCEDURE 存盤程序名（PARAM1 IN TYPE，PARAM2 OUT TYPE）
AS   --as和is任選一個，在這沒有區別
變數1 型別（值范圍）;
變數2 型別（值范圍）;
BEGIN
    SELECT COUNT(*) INTO 變數1 FROM 表A WHERE列名=PARAM1；

    IF (判斷條件) THEN
       SELECT 列名 INTO 變數2 FROM 表A WHERE列名=PARAM1；
       DBMS_OUTPUT.PUT_LINE(‘列印資訊’);
    ELSIF (判斷條件) THEN
       DBMS_OUTPUT.PUT_LINE(‘列印資訊’);
    ELSE
       RAISE 例外名（NO_DATA_FOUND）;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
       ROLLBACK;
END;

3、系統例外

4、存盤程序

4.1、無參存盤程序

--1)無參存盤程序語法

CREATE OR REPLACE PROCEDURE PRO_1_NOPAR
 AS --宣告
 
 BEGIN --執行
	 --SELECT * FROM D_DEPT D;
DBMS_OUTPUT.PUT_LINE('無參存盤程序');
 EXCEPTION--例外
    WHEN OTHERS THEN
       ROLLBACK;
 END;

--呼叫
BEGIN
  PRO_1_NOPAR;
END;

4.2、帶引數存盤程序

IN表示輸入引數，是引數的默認模式，
OUT表示回傳值引數，型別可以使用任意Oracle中的合法型別，
OUT模式定義的引數只能在程序體內部賦值，表示該引數可以將某個值傳遞回呼，
IN OUT表示該引數可以向該程序中傳遞值，也可以將某個值傳出去，

--2）帶引數存盤程序含賦值方式
CREATE OR REPLACE PROCEDURE PRO_2_PAR   
    (VAL IN NUMBER,   
     SNAME OUT VARCHAR,  
     DT_CODE IN OUT VARCHAR)  
 AS   
    ICOUNT NUMBER;  
 BEGIN  
      SELECT COUNT(*) INTO ICOUNT FROM D_DEPT WHERE DT_ID>VAL;  
      IF ICOUNT=1 THEN  
        SNAME:= 'MLB事業部1' ||VAL;
				DT_CODE:= 'MLB1'||DT_CODE;
      ELSE  
      SNAME:= 'MLB事業部2'||VAL;
				DT_CODE:= 'MLB2'||DT_CODE;
     END IF;  
EXCEPTION  
     WHEN TOO_MANY_ROWS THEN  
     DBMS_OUTPUT.PUT_LINE('回傳值多于1行');  
     WHEN OTHERS THEN  
     DBMS_OUTPUT.PUT_LINE('在PRO_1_PAR程序中出錯！');  
END;  

--呼叫
declare  
     REVAL NUMBER;  
     RENAME varchar(40);  
     RECODE varchar(40);  
begin    --程序呼叫開始  
     REVAL:=20210421;  
     RENAME:='';  
     RECODE:='JUNIOR';  
     --指定值對應變數順序可變  
     PRO_2_PAR(SNAME=>RENAME,VAL=>REVAL,DT_CODE=>RECODE);           
    DBMS_OUTPUT.PUT_LINE(RENAME||'   '||RECODE);  
END;  --程序呼叫結束

4.3、帶if的存盤程序

--3)帶if的存盤程序
CREATE OR REPLACE PROCEDURE PRO_3_IF(A IN NUMBER, B IN NUMBER,RS OUT NUMBER)
AS
    TEMP NUMBER;
BEGIN
    TEMP:=A;
    IF A < B THEN
        TEMP := B;
    END IF;
    RS:=TEMP;
END;

--呼叫
DECLARE
    RS NUMBER;
BEGIN
    PRO_3_IF(10,20,RS);
    DBMS_OUTPUT.PUT_LINE('RS='||RS);
END;

4.4、帶if else的存盤程序

CREATE OR REPLACE PROCEDURE PRO_4_IFELSE(A IN NUMBER, B IN NUMBER,RS OUT NUMBER)
AS
BEGIN
    IF A > B THEN
        RS := A;
    ELSE
        RS := B;
    END IF;
END;

--呼叫
DECLARE
    RS NUMBER;
BEGIN
    PRO_4_IFELSE(30,20,RS);
    DBMS_OUTPUT.PUT_LINE('RS='||RS);
END; 

4.5、帶else if的存盤程序

CREATE OR REPLACE PROCEDURE PRO_5_ELSEIF(Y IN NUMBER)
AS
BEGIN
    IF Y=2020 THEN
        DBMS_OUTPUT.PUT_LINE('2020年');
    ELSIF Y =2021 THEN
        DBMS_OUTPUT.PUT_LINE('2021年');
    ELSE
        DBMS_OUTPUT.PUT_LINE('未知年份');
    END IF;
END;

--存盤程序呼叫
BEGIN
  PRO_5_ELSEIF(Y => 2021);
END;

4.6、帶while回圈的存盤程序

--6)帶while回圈的存盤程序
CREATE OR REPLACE PROCEDURE PRO_6_WHILE(I IN NUMBER)
AS
    J NUMBER;
BEGIN
    J := 1;
    WHILE J <= I LOOP
        DBMS_OUTPUT.PUT_LINE('J='||J);
        J := J + 1;
    END LOOP;
END;

--存盤程序呼叫
BEGIN
  PRO_6_WHILE(I=> 100);
END;

4.7、帶select into的存盤程序

在利用SELECT…INTO…語法時，必須先確保資料庫中有該條記錄，否則會報出"NO_DATA_FOUND"例外，
可先利用SELECT COUNT(*) FROM 查看資料庫中是否存在該記錄，存在則使用SELECT…INTO，
在存盤程序中，別名不能和欄位名稱相同，否則雖然編譯可以通過，但在運行階段會報錯，

--7)帶SELECT INTO的存盤程序
CREATE OR REPLACE PROCEDURE PRO_7_SELINTO(ID IN NUMBER)
AS
M VARCHAR(50);
G VARCHAR(50);
BEGIN
  SELECT MONTH,ORG_OID INTO M,G FROM D_DEPT where DT_ID=ID;
   DBMS_OUTPUT.PUT_LINE('M'||M||'G'||G);
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND例外');
END;


--存盤程序呼叫
BEGIN
  PRO_7_SELINTO(ID=> 21);
END;