Oracle創建存儲過程是我們經常需要用到的功能,下面就為您介紹Oracle創建存儲過程和其他數據庫的區別,如果您感興趣的話,不妨一看。
在Oracle創建存儲過程和Sybase及SQL Server下的語法有些不一致之處。
下面就此用不同的數據庫下存儲過程的例子來演示之。
---------------------------
Oracle創建存儲過程:
- CREATE OR REPLACE FUNCTION MY_FUNC
- (
- P1 IN MY_TABLE.YY%TYPE,
- P2 IN MY_TABLE.NN%TYPE,
- P3 VARCHAR(100)
- )
- RETURN VARCHAR2 AS
/*定義有參數的游標和無參數的游標*/
- CURSOR MY_CURSOR1 IS
- SELECT YY,NN,DECODE(FYYSDM,0,'合計',1,'加工費','其他費用要素')
- FROM MY_TABLE
- WHERE YY=P1
- GROUP BY YY,NN
- ORDER BY YY,NN;
/*定義游標變量,存儲游標數據集中的記錄*/
- V_CURSOR1 MY_CURSOR1%ROWTYPE;
- CURSOR MY_CURSOR2(V_ZYDM MY_TABLE.ZYDM%TYPE,V_FYYSDM NUMBER) AS
- SELECT YY,NN,ZYDM,NVL(ZYCB,0) /*NVL函數轉換空值為指定值*/
- FROM MY_TABLE
- WHERE YY=P1 AND NN=P2 AND ZYDM=V_ZYDM AND FYYSDM=V_FYYSDM
- GROUP BY YY,NN;
/*定義游標變量,存儲游標數據集中的記錄*/
- V_CURSOR2 MY_CURSOR2%ROWTYPE;
- V_CPDM MY_TABLE.CPDM%TYPE;
- V_COUNT NUMBER;
- V_BZ VARCHAR2(2);
- V_CPCB NUMBER(22,2);
- BEGIN
- V_BZ:=1;
- SELECT CPDM INTO V_CPDM FROM MY_TABLE;
- SELECT CPCB INTO V_CPCB FROM MY_TABLE WHERE ROWNUM=1;
- IF MY_CURSOR1%ISOPEN THEN /*判斷游標是否已經打開*/
- CLOSE MY_CURSOR1;
- END IF;
- OPEN MY_CURSOR1;
- FETCH MY_CURSOR1 INTO V_CURSOR1;
- IF MY_CURSOR1%NOTFOUND THEN /*游標返回結果為空*/
- CLOSE MY_CURSOR1;
- RETURN(V_BZ);
- END IF;
- WHILE MY_CURSOR1%FOUND LOOP /*游標返回結果不為空*/
- V_CPDM:=V_CURSOR1.CPDM;
- V_CPCB:=V_CURSOR1.CPCB;
- V_COUNT:=100;
- IF V_COUNT=100 THEN
- V_COUNT:=99;
- END IF;
- FETCH MY_CURSOR1 INTO V_CURSOR1;
- END LOOP;
- CLOSE MY_CURSOR1;
/*顯式打開帶參游標*/
- SELECT CPDM INTO V_CPDM FROM MY_TABLE;
- OPEN MY_CURSOR2;
- FETCH MY_CURSOR2 INTO V_CURSOR2;
- WHILE MY_CURSOR2%FOUND LOOP /*游標返回結果不為空*/
- V_CPDM:=V_CURSOR2.CPDM;
- V_CPCB:=V_CURSOR2.CPCB;
- V_COUNT:=100;
- IF V_COUNT=100 THEN
- V_COUNT:=99;
- ELSE
- V_COUNT:=88;
- END IF;
- FETCH MY_CURSOR2 INTO V_CURSOR2;
- END LOOP;
- CLOSE MYCURSOR2;
/*隱式打開游標*/
- FOR V_CURSOR2 IN MY_CURSOR2(V_CPDM,V_CURSOR1.FYYSDM) LOOP
- IF V_CURSOR2.CPCB IS NULL THEN
- PRINT '非法!';
- ROLLBACK;
- END IF;
- UPDATE MY_TABLE
- SET CPCB=V_CPCB
- WHERE YY=P1 AND NN=P2 AND CPDM=V_CURSOR2.CPDM;
- IF SQL%NOTFOUND THEN /*判斷前句是否有執行結果*/
- /*程序段*/
- END IF;
- END LOOP;
- V_BZ:=MY_DELETE_CB(P_YY,P_NN);
- IF V_BZ<>0 THEN
- PRINT '失敗!';
- END IF;
- FOR I INT 1..V_COUNT LOOP
- /**/
- END LOOP;
- COMMIT;/*提交事務*/
- RETURN(0);/*要有返回值*/
- END MY_FUNC;
- CREATE OR REPLACE PROCEDURE SP_MY
- (
- P_YY IN MY_TABLE.YY%TYPE;
- P_NN NUMBER;
- )
- IS
- CURSOR MY_CURSOR IS
- SELECT CPCB
- FROM MY_TABLE
- WHERE YY=P_YY AND NN=P_NN;
- V_ZYCB NUMBER(22,2);
- BEGIN
- /**/
- /*無返回值*/
- END;