在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;
附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) 取字符字节长度。
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 --游标执行出错
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下不用显式打开事务，直接提交即可，而且可以分步提交，多次提交，不用成对出现。
SYBASE必须显式打开和提交事务，且必须成对出现，平时我们单条更新语句的执行被sybase默认为隐式事务来提交。
下面就此用不同的数据库下存储过程的例子来演示之。
－－－－－－－－－－－－－－－－－－－－－－－－－－－
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;
附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) 取字符字节长度。
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 --游标执行出错
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下不用显式打开事务，直接提交即可，而且可以分步提交，多次提交，不用成对出现。
SYBASE必须显式打开和提交事务，且必须成对出现，平时我们单条更新语句的执行被sybase默认为隐式事务来提交。
