首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
社区首页 >问答首页 >动态Oracle过程-结构问题

动态Oracle过程-结构问题
EN

Stack Overflow用户
提问于 2016-05-26 05:24:26
回答 1查看 30关注 0票数 0

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

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

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

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

如果表不存在-创建IT

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

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
    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;

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

原工作程序:

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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;
EN

回答 1

Stack Overflow用户

回答已采纳

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

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

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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;
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/37462169

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
查看详情【社区公告】 技术创作特训营有奖征文