引自：http://bbs.chinaunix.net/viewthread.php?tid=643946
在oracle中創(chuàng)建存儲過程和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 /*判斷前句是否有執(zhí)行結果*/
/*程序段*/
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;
附oracle下函數：
NVL(AAA,BBB) 空值判斷函數，AAA是待判斷變量,BBB為為空時要替換值
DECODE(ID,P1,S1,P2,S2,S3) 條件判斷函數，假如ID是P1則返回S1,假如ID是P2則返回S2,否則返回S3。
RPAD(AAA,COUNT,'0') 字符補空函數，在AAA變量后補零，共字符串長COUNT。
SUBSTR(AAA,M,N) 取子串函數，取AAA從第M個字符開始，取N個字符。
LENGTH(STR) 取字符長度。
LENGTHB(STR) 取字符字節(jié)長度。
TO_CHAR()
TO_NUMBER()類型轉換函數
--------------------------------------------------
SYBASE下:
CREATE PROCEDURE PRO_MY
(
@P_YY CHAR(4),
@P_NN CHAR(2)='01',
@P_OUT VARCHAR(255)=NULL OUTPUT
)
AS
BEGIN
DECLARE @V_CPDM NUMERIC(9,0),
@V_CPCB NUMERIC(22,2),
@V_ID INT,
@V_JE FLOAT
DECLARE CUR_TEST CURSOR FOR
SELECT CPCB,JE
FROM TEST
WHERE YY=@P_YY AND NN=@P_NN AND CPDM=@V_CPDM
FOR READ ONLY
SELECT @V_CPCB=CPCB
FROM TEST
WHERE YY=@P_YY AND NN=@P_NN AND CPDM=@V_CPDM
IF @@ROWCOUNT=0
PRINT '未找到'
ELSE
PRINT '找到'
OPEN CUR_TEST
FETCH CUR_TEST INTO @V_CPCB,@V_JE
IF @@SQLSTATUS=2 --返回結果集為空
IF @@SQLSTATUS=1 --游標執(zhí)行出錯
BEGIN
RAISERROR 20000 --返回自定義錯誤號
--RAISERROR 20000,'錯誤信息' --返回自定義錯誤號
ROLLBACK
RETURN 10
END
WHILE @@SQLSTATUS=0 --結果集返回正常結果
BEGIN
/*Exception*/
FETCH CUR_TEST INTO @V_CPCB,@V_JE
END
SELECT @V_ID=@V_ID+1
IF (@V_STR NOT LIKE "[0-9]")
SELECT @@V_ID=1
EXECUTE @V_ID=DELETE_CB @P_YY,@P_NN
UPDATE TEST SET CPCB=100 WHERE YY=@P_YY AND NN=@P_NN
IF @@ERROR!=0
PRINT '更新失敗'
RETURN 0
END
附錄：
CONVERT(INT,@V_JE) --類型轉換函數
LTRIM(@V_STR) --去掉左空格
RTRIM(@V_STR) --去掉右空格
ROUND(@V_JE,2) --數值小數位數設定
SUBSTRING(@V_STR,M,N) --取子字符串
STR(@V_JE,M,N) --設置數值的顯示位數和小數位數
CHAR_LENGTH(@V_STR) --字符串長度
PATINDEX("%[0-9]%",@V_STR) --取前邊字符在后面字符串中的起始位置
patindex("%[kKmMgGpP]%", @v_str)
--case語句
CASE @V_ID WHEN 0 THEN 'NO' WHEN 1 THEN 'YES' ELSE 'OTHER' END
ISNULL(@V_JE,0) --非空值判斷
－－－－－－－－－－－－－－－－－－－－－－－－－
另外：
關于事務處理：
ORACLE下不用顯式打開事務，直接提交即可，而且可以分步提交，多次提交，不用成對出現(xiàn)。
SYBASE必須顯式打開和提交事務，且必須成對出現(xiàn)，平時我們單條更新語句的執(zhí)行被sybase默認為隱式事務來提交。