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