日期:2014-05-17  浏览次数:20940 次

創建內建存儲過程失敗
小弟今天在ORACLE 11G中創建了一個內建存儲過程,代碼如下:
1 DECLARE
2 MY_COUNT NUMBER;
3 MY_SUM NUMBER;
4 PROCEDURE MY_PROC (
5 V_NUM SCOTT.EMP.DEPTNO%TYPE,
6 V_SUM OUT NUMBER,
7 V_COUNT OUT NUMBER)
8 AS
9 BEGIN
10 SELECT SUM(SAL),COUNT(*) INTO V_SUM,V_COUNT FROM SCOTT.EMP WHERE DEPTNO=V_NUM;
11 EXCEPTION
12 WHEN NO_DATA_FOUND THEN
13 DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
14 WHEN OTHERS THEN
15 DBMS_OUTPUT.PUT_LINE(SQLCODE||'===='||SQLERRM);
16 END MY_PROC;
/
但進行編譯的時候出現了錯誤,錯誤如下:
ORA-06550:第16行,第12列:
PLS-00103:出現符號"end-of-file"在需要下列之一時:
begin
function progma procedure

------解决方案--------------------
请参考:
SQL code

DECLARE 
    MY_COUNT NUMBER;
    MY_SUM NUMBER;
    PROCEDURE MY_PROC (V_NUM SCOTT.EMP.DEPTNO%TYPE, 
                                            V_SUM OUT NUMBER,
                                            V_COUNT OUT NUMBER)
    AS
    BEGIN
        SELECT SUM(SAL),COUNT(*) INTO V_SUM,V_COUNT FROM SCOTT.EMP WHERE DEPTNO=V_NUM;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(SQLCODE||'===='||SQLERRM);
    END MY_PROC;
BEGIN
    ....其它代码
END;