在 PLSQL SELECT 语句中使用变量 [英] Using variables in PLSQL SELECT statement
问题描述
我有一个查询 ReportStartDate 和 ReportEndDate 的查询,所以我想我会在 PLSQL 中使用变量.不确定我在这里遗漏了什么,但我收到一个错误:
I have a query that queries on ReportStartDate and ReportEndDate so I thought I would use variables in PLSQL. Not sure what I am missing here, but I get an error:
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:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
这发生在 Toad 和 SQL Developer 中.
This happens in Toad as well as in SQL Developer.
在我的 WHERE 子句中使用变量的正确方法是什么?
What is the proper way of using the variables in my WHERE clause?
推荐答案
您不能直接在 PL/SQL 块中使用 SQL 语句(除非您使用 EXECUTE IMMEDIATE
).需要将列提取到变量中(这是 PL/SQL 用 PLS-00428 告诉您的:在此 SELECT 语句
错误中需要 INTO 子句).所以你必须重写你的陈述如下.
You cannot use SQL statements directly in a PL/SQL block ( unless you use EXECUTE IMMEDIATE
). The columns will need to be fetched into variables ( which is what PL/SQL is telling you with PLS-00428: an INTO clause is expected in this SELECT statement
error). So you'll have to rewrite your statements as below.
SELECT
'Value TYPE',
1 AS CountType1,
2 AS CountType2,
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
异常.
Be sure to add Exception Handlers, since PL/SQL expects only 1 row to be returned. If the statement returns no rows, you'll hit a NO_DATA_FOUND
exception - and if the statement fetches too many rows, you'll hit a TOO_MANY_ROWS
exception.
这篇关于在 PLSQL SELECT 语句中使用变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!