如何使用雪花SQL存储过程中声明的变量作为第一个过程中调用的另一个存储过程的参数？

Question

你好，我正在使用雪花，并正在创建一个SQL存储过程(CDS_EXTRACT)。在CDS_EXTRACT中，我必须执行其他存储过程(CDS_EXTRACT_NEW_LOG_ENTRY)。CDS_EXTRACT_NEW_LOG_ENTRY的参数需要来自我声明为CDS_EXTRACT的变量。有人知道我如何引用CDS_EXTRACT中定义的变量作为参数作为CDS_EXTRACT_NEW_LOG_ENTRY使用吗？

特别是，我想在调用CDS_EXTRACT时使用JOBNAME、SOURCEQUERY、CDCQUERY、CDCVALUE变量作为参数。

在过程A之外运行过程B工作得很好。

希望这是有意义的，请参考我的代码，以便更好地理解。

CREATE OR REPLACE PROCEDURE CDS_EXTRACT (CDSID number)
RETURNS varchar(1677216)
LANGUAGE SQL
AS
    DECLARE 
        JOBNAME varchar(255);
        SOURCEQUERY VARCHAR(16777216);
        CDCQUERY VARCHAR(16777216);
        CDCVALUE VARCHAR(100) DEFAULT NULL;
        STAGE_DATABASE VARCHAR(100);
        STAGE_SCHEMA VARCHAR(100);
        STAGE_TABLE VARCHAR(100);
        FILE_FORMAT VARCHAR(100);
        BLOB_DIRECTORY VARCHAR(100);
        BLOB_FILE VARCHAR(100);
        SINGLE_FILE_FLAG BOOLEAN;
        OVERWRITE_FILE BOOLEAN;
        MAX_FILESIZE int DEFAULT 4900000000;
        START_DATE TIMESTAMP_NTZ(9);
        LAST_RUN_DATE TIMESTAMP_NTZ(9);
        LAST_RUN_CYCLE VARCHAR(30);
        LAST_RUN_COMPLETE BOOLEAN;
        RUNNEXT BOOLEAN;
        ERROR_FLAG BOOLEAN;
    BEGIN
        -- Gathering job info
        JOBNAME           := (SELECT JOBNAME FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        SOURCEQUERY       := (SELECT SOURCEQUERY FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        CDCQUERY          := (SELECT CDCQUERY FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        CDCVALUE          := (SELECT CDCVALUE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        STAGE_DATABASE    := (SELECT STAGE_DATABASE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        STAGE_SCHEMA      := (SELECT STAGE_SCHEMA FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        STAGE_TABLE       := (SELECT STAGE_TABLE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        FILE_FORMAT       := (SELECT FILE_FORMAT FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        BLOB_DIRECTORY    := (SELECT BLOB_DIRECTORY FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        BLOB_FILE         := (SELECT BLOB_FILE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        SINGLE_FILE_FLAG  := (SELECT SINGLE_FILE_FLAG FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        OVERWRITE_FILE    := (SELECT OVERWRITE_FILE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        MAX_FILESIZE      := (SELECT MAX_FILESIZE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        START_DATE        := (SELECT START_DATE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        LAST_RUN_DATE     := (SELECT LAST_RUN_DATE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        LAST_RUN_CYCLE    := (SELECT LAST_RUN_CYCLE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        LAST_RUN_COMPLETE := (SELECT LAST_RUN_COMPLETE FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        RUNNEXT           := (SELECT RUNNEXT FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        ERROR_FLAG        := (SELECT ERROR_FLAG FROM CDS.TALEND_CDS_BLOBEXTRACT WHERE CDSID = :CDSID);
        
        -- Update Start date, and last run date (need to add way to update run cycle as well)
        CALL CDS_EXTRACT_UPDATE_RUNCYCLE_DATE(:CDSID);
        
        -- Insert new log entry row
        CALL CDS_EXTRACT_NEW_LOG_ENTRY(00000, :CDSID, JOBNAME, SOURCEQUERY, CDCQUERY, CDCVALUE);
        
        RETURN 'Procedure completed!';
    END
;

运行将导致以下错误:错误:第47行的“语句_错误”位于第8位置: SQL编译错误:在位置60处的错误第1行(第99行)

Answer 1

在SQL上下文中，每个变量的使用都应加上:。

CALL CDS_EXTRACT_NEW_LOG_ENTRY(00000, :CDSID, :JOBNAME, :SOURCEQUERY, :CDCQUERY, :CDCVALUE);

选择成是分配来自同一个查询的多个变量的更简洁的方法：

SELECT JOBNAME, SOURCEQUERY,  CDCQUERY, CDCVALUE, ...       
INTO :JOBNAME, :SOURCEQUERY, :CDCQUERY, :CDCVALUE, ...                               
FROM CDS.TALEND_CDS_BLOBEXTRACT 
WHERE CDSID = :CDSID;

Answer 2

错误消息与调用CDS_EXTRACT_NEW_LOG_ENTRY过程有关，但您没有共享整个错误消息！？

我试着重复这个问题，我得到的最接近的信息是：

Uncaught exception of type 'STATEMENT_ERROR' on line 47 at position 8 : SQL compilation error: error line 1 at position 60 invalid identifier 'JOBNAME'

我把冒号符号(:)放在变量之前，它起作用了：

CALL CDS_EXTRACT_NEW_LOG_ENTRY(00000, :CDSID, :JOBNAME, :SOURCEQUERY, :CDCQUERY, :CDCVALUE);