在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:\n%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: an INTO clause is expected in this SELECT statement错误的内容).因此，您必须按如下所示重写语句.

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屋！