我正在尝试使用oracle创建一个函数,它应该可以正常工作,但我不断收到以下错误:
错误(8,1):PLS-00103:遇到符号“ SET”,这是我的功能:
CREATE OR REPLACE FUNCTION CountViewers(nameofPlay plays.play%TYPE) RETURN NUMBER AS NUM NUMBER; BEGIN SELECT SUM(registerd) INTO num1 FROM plays WHERE play=nameofPlay; return num; END; SET SERVEROUT ON; DECLARE inc integer; res NUMBER; invalid_status EXCEPTION; CURSOR clients2 IS SELECT * FROM plays; name_of_play plays.play%ROWTYPE; play_name plays%TYPE; BEGIN inc:=0; name_of_play := '&Play'; OPEN clients2; LOOP FETCH clients2 INTO play_name; IF name_of_play != play_name.play and clients2%rowcount>i THEN inc:=inc+1; ELSIF name_of_play = play_name.play THEN EXIT WHEN clients2%found; ELSE RAISE invalid_status; --like throw error END IF; EXIT WHEN clients2%notfound; END LOOP; res:=CountViewers(name_of_play); DBMS_OUTPUT.PUT_LINE(name_of_play ||' | '|| res); EXCEPTION WHEN invalid_status THEN DBMS_OUTPUT.PUT_LINE('The name of play is not found'); END;
问题是您将该命令放在哪里:SET SERVEROUT ON;导致其在函数声明中出现。
SET SERVEROUT ON;
您应该尝试将其带到函数之外:
SET SERVEROUT ON; \ CREATE OR REPLACE FUNCTION CountViewers(nameofPlay plays.play%TYPE) RETURN NUMBER AS NUM NUMBER; BEGIN SELECT SUM(registerd) INTO num1 FROM plays WHERE play=nameofPlay; return num; END; DECLARE inc integer; res NUMBER; invalid_status EXCEPTION; CURSOR clients2 IS SELECT * FROM plays; name_of_play plays.play%ROWTYPE; play_name plays%TYPE; BEGIN inc:=0; name_of_play := '&Play'; OPEN clients2; LOOP FETCH clients2 INTO play_name; IF name_of_play != play_name.play and clients2%rowcount>i THEN inc:=inc+1; ELSIF name_of_play = play_name.play THEN EXIT WHEN clients2%found; ELSE RAISE invalid_status; --like throw error END IF; EXIT WHEN clients2%notfound; END LOOP; res:=CountViewers(name_of_play); DBMS_OUTPUT.PUT_LINE(name_of_play ||' | '|| res); EXCEPTION WHEN invalid_status THEN DBMS_OUTPUT.PUT_LINE('The name of play is not found'); END;