带输出的存储过程,如何调用?
老本 2004-07-27 03:50:22 产生存储过程:
create or replace function getTCBL(DWDM IN VARCHAR2, ND OUT NUMBER) return NUMBER AS
TCBL NUMBER;
myYEAR NUMBER;
nowYEAR NUMBER;
CURSOR myCursor IS
SELECT LACKPERCENT, YEAR FROM sys_unitlackpercent
WHERE UNITCODE = DWDM
ORDER BY YEAR DESC;
BEGIN
select substr(Sysdate,1,4)-1 INTO nowYEAR from dual;
OPEN myCursor;
FETCH myCursor INTO TCBL, myYEAR;
CLOSE myCursor;
IF (myYEAR=nowYEAR+1) THEN
ND:=1;
RETURN TCBL;
ELSIF (myYEAR=nowYEAR) THEN
ND:=0;
RETURN TCBL;
END IF;
RETURN -1;
end;
/
访问时出错,语句:
DECLARE
i NUMBER;
BEGIN
SELECT getTCBL('02',i) FROM DUAL;
dbms_output.put_line(I);
END;
/
显示:
SELECT getTCBL('02',i) FROM DUAL;
*
ERROR 位于第 4 行:
ORA-06550: 第 4 行, 第 10 列:
PL/SQL: ORA-06572: 函数 GETTCBL 具有输出参数
ORA-06550: 第 4 行, 第 3 列:
PL/SQL: SQL Statement ignored
采用Exec也一样。(在SQL Plus中测试)