sql - 错误(8,1):PLS-00103:遇到符号“SET”
问题描述
我正在尝试使用 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;
\
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;
推荐阅读
- python - Python中的修剪平均值
- python - 如何将 n 列切换为 ar 行 pandas 数据帧的行(最终数据帧中的 n*r 行)?
- node.js - 如何将项目依赖项与 ts-node 一起使用
- python - 如何在 python 中处理不平衡的数据集和异常值?
- python-3.x - 是否有任何 API 端点来检查 DAG 是否为 PAUSESD / UNPAUSED?
- php - Laravel 5.8 - 如何授予用户读取、创建、更新、删除的权限?
- amazon-web-services - spark 无法从 emr 访问客户的 s3 存储桶
- java - Ubuntu TestContainers 无法连接到 Postgis:9.5
- android - Response.isSuccessfull() 为假
- r - 如何从主热图中提取一组基因兴趣的热图?