时间戳

首页 > 解决方案 > 是否可以使用 CTE 启动 Oracle SQL 过程？

oracle - 是否可以使用 CTE 启动 Oracle SQL 过程？

问题描述

我有一个我知道有效的 CTE，但我想将它用作一个过程，以便我可以参数化其中的一些查询。这是我尝试运行以创建过程的代码

CREATE OR REPLACE PROCEDURE VATIS_OWNER.getEnvVariables(PF IN VARCHAR, SN IN VARCHAR) AS
BEGIN
--Joins environemt variables from TESTCASES and ENVIRONMENTS tables and transposes result into columns 'ColumnName' and 'ColumnValue' and the ProfileId
--Is used by getAllDynamicData and unioned with results from sp_getProfileDetails
with
 cte as (
SELECT
T.TestcaseID,
T.ProfileID,
T.TrustID,
T.DNIS,
T.TESTID,
T.ACDID,
--Must be 8 digits long, so leading 0's are added
SUBSTR(CONCAT('00000000',T.testcaseid),-8, 8) as TestCaseNo,
T.HostId,
E.TFN
FROM TESTCASES T 
FULL JOIN ENVIRONMENTS E ON T.ENV_NAME = E.ENV_NAME
--Only returns env vars associated with testcase @SN
where T.TestcaseID = SN
),
cte2 as (
Select A.ProfileID
      ,B.*
From  cte A 
--Transpose happens here
Cross Apply (  SELECT 'TestcaseID' AS ColumnName,A.TestcaseID AS ColumnValue FROM DUAL UNION ALL
               SELECT 'ProfileID' AS ColumnName,A.ProfileID AS ColumnValue FROM DUAL UNION ALL
               SELECT 'TrustID' AS ColumnName,A.TrustID AS ColumnValue FROM DUAL UNION ALL
               SELECT 'DNIS' AS ColumnName,SUBSTR(A.DNIS,-7,7) AS ColumnValue FROM DUAL UNION ALL
               SELECT 'TESTID' AS ColumnName,A.TESTID AS ColumnValue FROM DUAL UNION ALL
               SELECT 'ACDID' AS ColumnName,A.ACDID AS ColumnValue FROM DUAL UNION ALL
               SELECT 'TestCaseNo' AS ColumnName,A.TestCaseNo AS ColumnValue FROM DUAL UNION ALL
               SELECT 'HostId' AS ColumnName,A.HostId AS ColumnValue FROM DUAL UNION ALL
               SELECT 'TFN' AS ColumnName,A.TFN AS ColumnValue FROM DUAL UNION ALL
               SELECT 'INVALIDANI' AS ColumnName,SUBSTR(CONCAT(A.TestcaseID ,A.TESTID),-10,10) AS ColumnValue FROM DUAL
             ) B
)
select distinct * from cte2 where profileID = PF;
END getEnvVariables;

如果我用字符串值替换 SN 和 PF 并只运行 CTE 和查询，它就可以工作。等效版本在 SQL Server 中用作存储过程，但是当我尝试在 Oracle 中创建此过程时，出现以下编译错误：

PLS-00428: an INTO clause is expected in this SELECT statement

知道为什么我不能在程序中使用它吗？我比 Oracle 更熟悉 SQL Server，所以如果我忘记了什么，请告诉我。提前致谢。

标签： oraclestored-procedurescommon-table-expression

解决方案

如果要从此过程返回选择结果，则必须使用 SYS_REFCURSOR，如下所示 -

CREATE OR replace PROCEDURE vatis_owner.Getenvvariables(pf IN VARCHAR, 
                                                        sn IN VARCHAR,
                                                        res OUT SYS_REFCURSOR) AS 
  BEGIN 
    --Joins environemt variables from TESTCASES and ENVIRONMENTS tables and transposes result into columns 'ColumnName' and 'ColumnValue' and the ProfileId
    --Is used by getAllDynamicData and unioned with results from sp_getProfileDetails 
    OPEN res FOR
    WITH cte AS 
    ( 
              SELECT    t.testcaseid, 
                        t.profileid, 
                        t.trustid, 
                        t.dnis, 
                        t.testid, 
                        t.acdid, 
                        --Must be 8 digits long, so leading 0's are added 
                        Substr(Concat('00000000',t.testcaseid),-8, 8) AS testcaseno, 
                        t.hostid, 
                        e.tfn 
              FROM      testcases t 
              full join environments e 
              ON        t.env_name = e.env_name 
                        --Only returns env vars associated with testcase @SN 
              WHERE     t.testcaseid = sn ), cte2 AS 
    ( 
           SELECT a.profileid , 
                  b.* 
           FROM   cte a 
                  --Transpose happens here 
                  cross apply 
                  ( 
                         SELECT 'TestcaseID' AS columnname, 
                                a.testcaseid AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'ProfileID' AS columnname, 
                                a.profileid AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'TrustID' AS columnname, 
                                a.trustid AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'DNIS'              AS columnname, 
                                substr(a.dnis,-7,7) AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'TESTID' AS columnname, 
                                a.testid AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'ACDID' AS columnname, 
                                a.acdid AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'TestCaseNo' AS columnname, 
                                a.testcaseno AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'HostId' AS columnname, 
                                a.hostid AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'TFN' AS columnname, 
                                a.tfn AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'INVALIDANI'                                  AS columnname, 
                                substr(concat(a.testcaseid ,a.testid),-10,10) AS columnvalue 
                         FROM   dual ) b ) 
    SELECT DISTINCT * 
    FROM            cte2 
    WHERE           profileid = pf; 

  END getenvvariables;

然后稍后您可以使用此过程在 ref_cursor 变量中返回结果。

DECALRE
       RES SYS_REFCURSOR;
BEGIN
     vatis_owner.Getenvvariables(pf, 
                                 sn,
                                 RES);
     FOR I IN 1..RES.COUNT LOOP
         DBMS_OUTPUT.PUT_LINE(I.YOUR_DESIRED_COLUMNS);
     END LOOP;
END;

我可以看到您在查询中使用了 CROSS APPLY，这意味着您必须使用 12C 或更高版本。所以你可以使用 DBMS_SQL.RETURN_RESULT 函数 -

CREATE OR replace PROCEDURE vatis_owner.Getenvvariables(pf IN VARCHAR, 
                                                        sn IN VARCHAR) AS 
  RES SYS_REFCURSOR;
  BEGIN 
    --Joins environemt variables from TESTCASES and ENVIRONMENTS tables and transposes result into columns 'ColumnName' and 'ColumnValue' and the ProfileId
    --Is used by getAllDynamicData and unioned with results from sp_getProfileDetails 
    OPEN RES FOR
    WITH cte AS 
    ( 
              SELECT    t.testcaseid, 
                        t.profileid, 
                        t.trustid, 
                        t.dnis, 
                        t.testid, 
                        t.acdid, 
                        --Must be 8 digits long, so leading 0's are added 
                        Substr(Concat('00000000',t.testcaseid),-8, 8) AS testcaseno, 
                        t.hostid, 
                        e.tfn 
              FROM      testcases t 
              full join environments e 
              ON        t.env_name = e.env_name 
                        --Only returns env vars associated with testcase @SN 
              WHERE     t.testcaseid = sn ), cte2 AS 
    ( 
           SELECT a.profileid , 
                  b.* 
           FROM   cte a 
                  --Transpose happens here 
                  cross apply 
                  ( 
                         SELECT 'TestcaseID' AS columnname, 
                                a.testcaseid AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'ProfileID' AS columnname, 
                                a.profileid AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'TrustID' AS columnname, 
                                a.trustid AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'DNIS'              AS columnname, 
                                substr(a.dnis,-7,7) AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'TESTID' AS columnname, 
                                a.testid AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'ACDID' AS columnname, 
                                a.acdid AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'TestCaseNo' AS columnname, 
                                a.testcaseno AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'HostId' AS columnname, 
                                a.hostid AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'TFN' AS columnname, 
                                a.tfn AS columnvalue 
                         FROM   dual 
                         UNION ALL 
                         SELECT 'INVALIDANI'                                  AS columnname, 
                                substr(concat(a.testcaseid ,a.testid),-10,10) AS columnvalue 
                         FROM   dual ) b ) 
    SELECT DISTINCT * 
    FROM            cte2 
    WHERE           profileid = pf;

    DBMS_SQL.RETURN_RESULT(RES); 

  END getenvvariables;

当您运行此过程时，您将在控制台上看到结果。如需更多信息，请阅读。

推荐阅读