oracle 错误 PLS-00103：遇到符号“DECLARE”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27658469/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ERROR PLS-00103: Encountered the symbol “DECLARE”?
提问by emilly
I have below simple function where i am trying to have simple function with dynamic query i.e sqlstatement (though in this example i have mentioned simple query)
我有下面的简单函数，我试图在其中使用动态查询的简单函数，即 sqlstatement（尽管在这个例子中我提到了简单的查询）
create or replace
FUNCTION test_simple_func ( testVal IN VARCHAR2)
RETURN number IS
total number(2) := 0;
DECLARE @sqlstatement VARCHAR(MAX);
BEGIN
SET @sqlstatement = 'Select * from employee';
select count(*) INTO total from employee;
RETURN total;
END;
i am not getting whats wrong here?
我没有发现这里有什么问题？
回答by Avijit
Here it is in Oracle Version:
这是在 Oracle 版本中：
CREATE OR REPLACE FUNCTION test_simple_func(
testVal IN VARCHAR2)
RETURN NUMBER
AS
total NUMBER;
sqlstatement VARCHAR2(4000);
BEGIN
sqlstatement := 'Select * from employee';
SELECT COUNT(*)
INTO total
FROM employee;
RETURN total;
END;
And if you want to use as a dynamic query then you need to use EXECUTE immediatelike EXECUTE immediate sqlstatement
如果你想用作动态查询，那么你需要使用EXECUTE immediate像EXECUTE immediate sqlstatement
回答by Charmi
Unlike SQL where the varchar limitation is 4000, the limitation in PL/SQL is around 32K
与 varchar 限制为 4000 的 SQL 不同，PL/SQL 中的限制约为 32K
Otherwise, no declare in PL/SQL function. DECLARE is used for anonymous blocs in Oracle.
否则，在 PL/SQL 函数中没有声明。DECLARE 用于 Oracle 中的匿名块。
The correct synthax is
正确的合成器是
CREATE OR REPLACE
FUNCTION test_simple_func ( testVal IN VARCHAR2)
RETURN number IS
total number(2) := 0;
sqlstatement VARCHAR(32000);
BEGIN
sqlstatement := 'Select * from employee';
select count(*) INTO total from employee;
RETURN total;
END;
回答by Hrithu
This is a proper way to execute Dynamic Query
这是执行动态查询的正确方法
CREATE OR REPLACE FUNCTION test_simple_func(
testVal IN VARCHAR2)
RETURN NUMBER
IS
total NUMBER(10) := 0;
sqlstatement CLOB;
BEGIN
sqlstatement := 'Select count(*) from employee';
--select count(*) INTO total from employee;
EXECUTE immediate sqlstatement INTO total;
RETURN total;
END;
回答by Mureinik
The declareblock should start before allthe declarations:
该declare块应该在所有声明之前开始：
create or replace
FUNCTION test_simple_func ( testVal IN VARCHAR2)
RETURN number IS
DECLARE -- right place for declare
total number(2) := 0;
sqlstatement VARCHAR(4000);
BEGIN
SET sqlstatement = 'Select * from employee';
select count(*) INTO total from employee;
RETURN total;
END;
回答by Sandeep
As per my memory VARCHAR(MAX) is not supported by Oracle
根据我的记忆，Oracle 不支持 VARCHAR(MAX)
