问动态Oracle过程-结构问题
EN

Stack Overflow用户

提问于 2016-05-26 05:24:26

回答 1查看 30关注 0票数 0

在创建存储过程方面，我似乎遇到了一些问题，我根本看不出问题的所在。我对甲骨文比较陌生。

我有一张长度未知的桌子。我所做的是创建一个存储过程，该存储过程将创建一个表，该表的宽度与从该表获取值的表的行数相同。也就是说，如果表有10个值，代码将创建一个宽10列的新表，等等。

我已经设法使初始代码正常工作，但现在我试图添加一些附加逻辑，但没有成功。

我想要实现的逻辑是这样的：

如果表不存在-创建IT

如果表EXSITS -简单地插入到IT中

我还没有为INSERT部分创建代码，因为我无法让第一部分工作。在我把计数和IF语句加进去之前，一切都很顺利。

    CREATE OR REPLACE PROCEDURE "MDWPROD"."WORKFLOW_VAR_PIVOT" IS
    v_sql varchar2(32767);

    -- for the first run ofthe procedure, we need to create the table
    DECLARE var_count INT;

    SELECT 
        COUNT(*) 
    INTO 
        var_count
    FROM 
        all_tables 
    WHERE 
        OWNER = 'MDWPROD'
        AND TABLE_NAME = 'RBI_PROCESSVARIABLE_WK';


    -- if var_count = 0 then the table does not exists, create it, otherwise proceed with other logic
    IF var_count = 0 THEN

        -- cursor to find out the maximum number of projected columns required
        CURSOR cur_proj_test IS
            SELECT DISTINCT 
                ID, 
                VARIABLE_REPORT_LBL
            FROM   
                MDWPROD.RBI_VARIABLETYPE_DM
            ORDER BY 
                ID;

         -- We now loop through the cursor, and build of the SQL string to CREATE and POPULATE the table 
        BEGIN
            v_sql := 'CREATE TABLE MDWPROD.RBI_PROCESSVARIABLE_WK AS SELECT VAR.PROCESS_ID';
            FOR i IN cur_proj_test
            LOOP

                -- dynamically add to the projection for the query
                v_sql := v_sql || ',MAX(CASE VT.VARIABLE_REPORT_LBL WHEN ''' || i.VARIABLE_REPORT_LBL || ''' THEN VAR.VALUE ELSE '''' END) AS "' || i.VARIABLE_REPORT_LBL || '"';

            END LOOP;

            v_sql := v_sql || ' FROM MDWPROD.RBI_VARIABLE_DM VAR INNER JOIN MDWPROD.RBI_VARIABLETYPE_DM VT ON VAR.VARIABLE_TYPE_ID = VT.ID WHERE VAR.CURRENT_IND = ''Y'' GROUP BY VAR.PROCESS_ID order by VAR.PROCESS_ID';

             -- Create table and populate it with all the relevant variable values
            EXECUTE IMMEDIATE v_sql;
        END;


    END IF;
END;

如能提供任何协助，将不胜感激。

原工作程序：

CREATE OR REPLACE PROCEDURE WORKFLOW_VAR_PIVOT IS
    v_sql varchar2(32767);


    -- cursor to find out the maximum number of projected columns required
    CURSOR cur_proj_test IS
        SELECT DISTINCT 
            ID, 
            VARIABLE_REPORT_LBL
        FROM   
            MDWPROD.RBI_VARIABLETYPE_DM
        ORDER BY 
            ID;

     -- We now loop through the cursor, and build of the SQL string to CREATE and POPULATE the table 
    BEGIN
        v_sql := 'CREATE TABLE MDWPROD.RBI_PROCESSVARIABLE AS SELECT VAR.PROCESS_ID';
        FOR i IN cur_proj_test
        LOOP

            -- dynamically add to the projection for the query
            v_sql := v_sql || ',MAX(CASE VT.VARIABLE_REPORT_LBL WHEN ''' || i.VARIABLE_REPORT_LBL || ''' THEN VAR.VALUE ELSE '''' END) AS "' || i.VARIABLE_REPORT_LBL || '"';

        END LOOP;

        v_sql := v_sql || ' FROM MDWPROD.RBI_VARIABLE_DM VAR INNER JOIN MDWPROD.RBI_VARIABLETYPE_DM VT ON VAR.VARIABLE_TYPE_ID = VT.ID WHERE VAR.CURRENT_IND = ''Y'' GROUP BY VAR.PROCESS_ID order by VAR.PROCESS_ID';

        -- un comment this line to print out the entire SQL statement
        -- dbms_output.put_line('Dynamic SQL Statement:-' || chr(10) || v_sql || chr(10) || chr(10));

        -- DROP TABLE before recreating it
        EXECUTE IMMEDIATE 'DROP TABLE MDWPROD.RBI_PROCESSVARIABLE';

        -- Create table and populate it with all the relevant variable values
        EXECUTE IMMEDIATE v_sql;
    END;

sql

oracle

回答 1

Stack Overflow用户

回答已采纳

发布于 2016-05-26 05:41:32

可能还有其他问题(您从来没有说过您的确切错误)，但是我立即看到begin关键字在错误的位置。您的声明应该位于begin关键字之前的顶部，执行语句应该放在后面。

我把东西搬来搬去。这应该能让你更接近你的目标：

CREATE OR REPLACE PROCEDURE "MDWPROD"."WORKFLOW_VAR_PIVOT" IS
    v_sql varchar2(32767);

    -- for the first run ofthe procedure, we need to create the table
    DECLARE var_count INT;

    -- cursor to find out the maximum number of projected columns required
    CURSOR cur_proj_test IS
        SELECT DISTINCT 
            ID, 
            VARIABLE_REPORT_LBL
        FROM   
            MDWPROD.RBI_VARIABLETYPE_DM
        ORDER BY 
            ID;

BEGIN

    SELECT 
        COUNT(*) 
    INTO 
        var_count
    FROM 
        all_tables 
    WHERE 
        OWNER = 'MDWPROD'
        AND TABLE_NAME = 'RBI_PROCESSVARIABLE_WK';


    -- if var_count = 0 then the table does not exists, create it, otherwise proceed with other logic
    IF var_count = 0 THEN


         -- We now loop through the cursor, and build of the SQL string to CREATE and POPULATE the table 
            v_sql := 'CREATE TABLE MDWPROD.RBI_PROCESSVARIABLE_WK AS SELECT VAR.PROCESS_ID';
            FOR i IN cur_proj_test
            LOOP

                -- dynamically add to the projection for the query
                v_sql := v_sql || ',MAX(CASE VT.VARIABLE_REPORT_LBL WHEN ''' || i.VARIABLE_REPORT_LBL || ''' THEN VAR.VALUE ELSE '''' END) AS "' || i.VARIABLE_REPORT_LBL || '"';

            END LOOP;

            v_sql := v_sql || ' FROM MDWPROD.RBI_VARIABLE_DM VAR INNER JOIN MDWPROD.RBI_VARIABLETYPE_DM VT ON VAR.VARIABLE_TYPE_ID = VT.ID WHERE VAR.CURRENT_IND = ''Y'' GROUP BY VAR.PROCESS_ID order by VAR.PROCESS_ID';

             -- Create table and populate it with all the relevant variable values
            EXECUTE IMMEDIATE v_sql;
    END IF;
END;