=========================================== CREATE OR REPLACE PACKAGE BODY STU.TST1 IS RETFORPROC INT; Y INT; PROCEDURE A (RET OUT INT) IS BEGIN DBMS_OUTPUT.PUT_LINE('A'); RET :=1; SELECT A INTO Y FROM (SELECT 1 AS A FROM DUAL UNION SELECT 2 AS A FROM DUAL ); EXCEPTION WHEN OTHERS THEN BEGIN DBMS_OUTPUT.PUT_LINE('AAA' || SQLCODE || SQLERRM || 'START'); IFTEST; DBMS_OUTPUT.PUT_LINE('AAA' || SQLCODE || SQLERRM || 'END'); BEGIN SELECT 100/10 INTO Y FROM DUAL; SELECT A INTO Y FROM (SELECT 1 AS A FROM DUAL UNION SELECT 2 AS A FROM DUAL ) T WHERE T.A = 3; EXCEPTION WHEN OTHERS THEN BEGIN DBMS_OUTPUT.PUT_LINE('AAA' || SQLCODE || SQLERRM || 'END3'); END; END; DBMS_OUTPUT.PUT_LINE('AAA' || SQLCODE || SQLERRM || 'END4'); B(RETFORPROC); DBMS_OUTPUT.PUT_LINE(RETFORPROC); END; DBMS_OUTPUT.PUT_LINE('AAA' || SQLCODE || SQLERRM || 'END5'); END A; PROCEDURE B (RET OUT INT) IS BEGIN DBMS_OUTPUT.PUT_LINE('B'); RET :=2; END B; PROCEDURE IFTEST IS V_ERRCODE NUMBER; X NUMBER; BEGIN IF '2' IN ('0','1') THEN DBMS_OUTPUT.PUT_LINE('IN!'); ELSE DBMS_OUTPUT.PUT_LINE('NOT IN!'); END IF; V_ERRCODE := SQLCODE; SELECT 100/0 INTO X FROM DUAL; EXCEPTION WHEN OTHERS THEN BEGIN DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM); END ; END; END; / 我自己写了一段测了测. 发现这个东西好像是用堆栈保存的,并且每次SQLCODE 变化时都会保存之前的状态,不知道对不对!
select @新值=@舊值
============================
oracle 的SQLCODE 不是SQlSERVER 的 谢谢!
一般是在每次sql语句后面有判断。
CREATE OR REPLACE PACKAGE BODY STU.TST1
IS
RETFORPROC INT;
Y INT;
PROCEDURE A (RET OUT INT) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('A');
RET :=1;
SELECT A INTO Y FROM (SELECT 1 AS A FROM DUAL UNION SELECT 2 AS A FROM DUAL );
EXCEPTION
WHEN OTHERS THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('AAA' || SQLCODE || SQLERRM || 'START');
IFTEST;
DBMS_OUTPUT.PUT_LINE('AAA' || SQLCODE || SQLERRM || 'END');
BEGIN
SELECT 100/10 INTO Y FROM DUAL;
SELECT A INTO Y FROM (SELECT 1 AS A FROM DUAL UNION SELECT 2 AS A FROM DUAL ) T WHERE T.A = 3;
EXCEPTION
WHEN OTHERS THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('AAA' || SQLCODE || SQLERRM || 'END3');
END;
END;
DBMS_OUTPUT.PUT_LINE('AAA' || SQLCODE || SQLERRM || 'END4');
B(RETFORPROC);
DBMS_OUTPUT.PUT_LINE(RETFORPROC);
END;
DBMS_OUTPUT.PUT_LINE('AAA' || SQLCODE || SQLERRM || 'END5');
END A; PROCEDURE B (RET OUT INT) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('B');
RET :=2;
END B; PROCEDURE IFTEST
IS
V_ERRCODE NUMBER;
X NUMBER;
BEGIN
IF '2' IN ('0','1') THEN
DBMS_OUTPUT.PUT_LINE('IN!');
ELSE
DBMS_OUTPUT.PUT_LINE('NOT IN!');
END IF; V_ERRCODE := SQLCODE; SELECT 100/0 INTO X FROM DUAL; EXCEPTION
WHEN OTHERS THEN
BEGIN
DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
END ;
END;
END;
/
我自己写了一段测了测.
发现这个东西好像是用堆栈保存的,并且每次SQLCODE 变化时都会保存之前的状态,不知道对不对!
不是pga 就是sga 具体不清楚了,应该是全局变量,因为不用定义的