在创建存储过程方面,我似乎遇到了一些问题,我根本看不出问题的所在。我对甲骨文比较陌生。
我有一张长度未知的桌子。我所做的是创建一个存储过程,该存储过程将创建一个表,该表的宽度与从该表获取值的表的行数相同。也就是说,如果表有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;
发布于 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;
https://stackoverflow.com/questions/37462169
复制相似问题