SQL 在 PLSQL SELECT 语句中使用变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4305061/
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
Using variables in PLSQL SELECT statement
提问by Raj More
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:
我有一个查询 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;
/
The Error is:
错误是：
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:
This happens in Toad as well as in SQL Developer.
这发生在 Toad 以及 SQL Developer 中。
What is the proper way of using the variables in my WHERE clause?
在我的 WHERE 子句中使用变量的正确方法是什么？
回答by Sathyajith Bhat
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 statementerror). So you'll have to rewrite your statements as below.
您不能直接在 PL/SQL 块中使用 SQL 语句（除非您使用EXECUTE IMMEDIATE）。需要将列提取到变量中（这是 PL/SQL 告诉您的PLS-00428: an INTO clause is expected in this SELECT statement错误）。所以你必须重写你的陈述如下。
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
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_FOUNDexception - and if the statement fetches too many rows, you'll hit a TOO_MANY_ROWSexception.
一定要添加异常处理程序，因为 PL/SQL 只期望返回 1 行。如果该语句不返回任何行，您将遇到NO_DATA_FOUND异常 - 如果该语句获取太多行，您将遇到TOO_MANY_ROWS异常。
回答by Dan
The question you have to answer is what do you want to dowith the data that has been selected?
您必须回答的问题是您想对已选择的数据做什么？
Sathya gave you one approach - declare variables in your PL/SQL block and select the columns INTOthose variables. Note that this requires that the SELECT statement returns exactly one row - any more or less rows will throw an error. Another way is to declare collection types using the BULK COLLECT option: http://oracletoday.blogspot.com/2005/11/bulk-collect_15.html
Sathya 为您提供了一种方法 - 在您的 PL/SQL 块中声明变量并选择INTO这些变量的列。请注意，这要求 SELECT 语句只返回一行 - 任何更多或更少的行都会引发错误。另一种方法是使用 BULK COLLECT 选项声明集合类型：http: //oracletoday.blogspot.com/2005/11/bulk-collect_15.html
Yet another option is to have the procedure return a cursor. This is useful in the case where the calling code expects to be able to fetch the data that the procedure has selected:
另一种选择是让过程返回一个游标。这在调用代码希望能够获取过程选择的数据的情况下很有用：
PROCEDURE GET_MY_REPORT( varReportStartDate in date, varReportEndDate in date, cur out sys_refcursor) is
begin
OPEN cur FOR SELECT *
FROM CDR.MSRS_E_INADVCH
WHERE 1=1
AND ReportStartDate = varReportStartDate
AND ReportEndDate = varReportEndDate;
END GET_MY_REPORT;
