我有一个查询ReportStartDate和ReportEndDate的查询,所以我想我会在PLsql中使用变量.不知道我在这里缺少什么,但我收到一个错误：
CLEAR;
DECLARE
varReportStartDate Date := to_date('05/01/2010','mm/dd/yyyy');
varReportEndDate Date := to_date('05/31/2010','mm/dd/yyyy');
BEGIN
SELECT
'Value TYPE',1 AS CountType1,2 AS CountType2,3 AS CountType3
FROM DUAL;
SELECT COUNT (*)
FROM CDR.MSRS_E_INADVCH
WHERE 1=1
AND ReportStartDate = varReportStartDate
AND ReportEndDate = varReportEndDate
;
END;
/
错误是：
Error starting at line 2 in command: Error report: ORA-06550: line 6,column 5: PLS-00428: an INTO clause is expected in this SELECT statement ORA-06550: line 8,column 5: PLS-00428: an INTO clause is expected in this SELECT statement 06550. 00000 - "line %s,column %s:\n%s" *Cause: Usually a PL/sql compilation error. *Action:
这种情况发生在Toad以及sql Developer中.
在WHERE子句中使用变量的正确方法是什么？
解决方法
您不能直接在PL / sql块中使用sql语句(除非您使用EXECUTE IMMEDIATE).这些列需要被提取到变量中(这是PL / sql告诉你的PLS-00428：在这个SELECT语句错误中需要一个INTO子句).所以你必须重写你的陈述,如下所示.
SELECT
'Value TYPE',3 AS CountType3
INTO
V_VALUE_TYPE,V_CountType1,V_CountType2,V_CountType3
FROM DUAL;
SELECT COUNT(*)
INTO V_COUNT
FROM CDR.MSRS_E_INADVCH
WHERE 1=1
AND ReportStartDate = varReportStartDate
AND ReportEndDate = varReportEndDate
一定要添加异常处理程序,因为PL / sql只需要返回1行.如果语句没有返回任何行,则会遇到NO_DATA_FOUND异常 – 如果语句获取太多行,则会遇到TOO_MANY_ROWS异常.
