存储过程如下:
CREATE OR REPLACE PROCEDURE tr_TEST1 IS
V_SQL VARCHAR2(20000);
CURSOR CURSOR_1 IS SELECT DISTINCT T.预约日期 FROM tr_tmp T ORDER BY 预约日期;
BEGIN
V_SQL := 'SELECT 区域,中心,工单量'; FOR V_预约日期 IN CURSOR_1
LOOP
V_SQL := V_SQL || ',' || 'SUM(DECODE(预约日期,''' || v_预约日期.预约日期 ||
''',工单量,0)) AS ' || V_预约日期.预约日期;
END LOOP; V_SQL := V_SQL || ' FROM tr_test1 GROUP BY 区域,中心,工单量 ORDER BY 区域,中心,工单量';
V_SQL := 'CREATE OR REPLACE VIEW RESULT AS '|| V_SQL;
EXECUTE IMMEDIATE V_SQL;
COMMIT;
END tr_TEST1;编译成功,但是执行时提示找不到FROM关键字,哪里出了问题啊?
CREATE OR REPLACE PROCEDURE tr_TEST1 IS
V_SQL VARCHAR2(20000);
CURSOR CURSOR_1 IS SELECT DISTINCT T.预约日期 FROM tr_tmp T ORDER BY 预约日期;
BEGIN
V_SQL := 'SELECT 区域,中心,工单量'; FOR V_预约日期 IN CURSOR_1
LOOP
V_SQL := V_SQL || ',' || 'SUM(DECODE(预约日期,''' || v_预约日期.预约日期 ||
''',工单量,0)) AS ' || V_预约日期.预约日期;
END LOOP; V_SQL := V_SQL || ' FROM tr_test1 GROUP BY 区域,中心,工单量 ORDER BY 区域,中心,工单量';
V_SQL := 'CREATE OR REPLACE VIEW RESULT AS '|| V_SQL;
EXECUTE IMMEDIATE V_SQL;
COMMIT;
END tr_TEST1;编译成功,但是执行时提示找不到FROM关键字,哪里出了问题啊?
-- 加一个逗号 V_SQL := 'SELECT 区域,中心,工单量,';
具体什么错,你得说啊
tr_test1;
end;
提示缺失表达式
应该是列别名问题
--select hiredate as 20161201 from emp a; --不行
--select hiredate as '20161201' from emp a; --不行
select hiredate as "20161201" from emp a; --可以
-- ''',工单量,0)) AS ' || V_预约日期.预约日期;
--加上双引号 修改成如下试试
''',工单量,0)) AS "' || V_预约日期.预约日期||'"';
CREATE OR REPLACE PROCEDURE tr_TEST1 IS
V_SQL VARCHAR2(20000);
CURSOR CURSOR_1 IS SELECT DISTINCT T.预约日期 FROM tr_tmp T ORDER BY 预约日期;
BEGIN
V_SQL := 'SELECT 区域,中心'; FOR V_预约日期 IN CURSOR_1
LOOP
V_SQL := V_SQL || ',' || 'SUM(DECODE(预约日期,''' || v_预约日期.预约日期 ||
''',工单量,0)) AS "' || V_预约日期.预约日期 ||'"';
END LOOP; V_SQL := V_SQL || ' FROM tr_test1 GROUP BY 区域,中心 ORDER BY 区域,中心';
V_SQL := 'CREATE OR REPLACE VIEW RESULT AS '|| V_SQL;
EXECUTE IMMEDIATE V_SQL;
END tr_test1;编译成功,但是执行提示
ORA-04044:此处不允许过程,函数,程序包或类型
ORA-06512:在line15
ora-06512::在 line2
--EXECUTE IMMEDIATE V_SQL;
dbms_output.put_line(v_sql);
--EXECUTE IMMEDIATE V_SQL;
dbms_output.put_line(v_sql);
提示字符串缓冲区太小
--EXECUTE IMMEDIATE V_SQL;
dbms_output.put_line(v_sql);
提示字符串缓冲区太小游标少取点数据,再试下
CURSOR CURSOR_1 IS SELECT DISTINCT T.预约日期 FROM tr_tmp T
where rownum <=2
ORDER BY 预约日期;