我写的存储过程在执行时顺利通过了,调用时报缺失表达式,求大神给看看怎么回事?create or replace procedure get_form_data(p_date varchar2,str1 varchar2,str2 varchar2,str3 varchar2,str4 varchar2,str5 varchar2)ISt_date date;
t_dept varchar2(36);
t_identity varchar2(36);
t_charge varchar2(36);
t_operator varchar2(36);
t_type varchar2(36);
t_time varchar2(36);
t_outp number(6,2);
t_emer number(6,2);
t_daytimeoutp number(6,2);
t_daytimeemer number(6,2);
t_night number(6,2);
t_holidayoutp number(6,2);
t_holidayemer number(6,2);
t_export number(6,2);
t_regist number(6,2);
CUR1 SYS_REFCURSOR;
sql_statement varchar2(3000):='select dim_date,'||str1||','||str2||','||str3||','||str4||','||str5||',sum(t.outp_nums) OUPT_NUMS,sum(emer_nums) EMER_NUMS,
sum(daytime_outp_nums) DAYTIME_OUTP_NUMS,sum(daytime_emer_nums) DAYTIME_EMER_NUMS,
SUM(NIGHT_OUTP_NUMS) NIGHT_OUTP_NUMS,SUM(HOLIDAY_OUTP_NUMS) HOLIDAY_OUTP_NUMS,SUM(HOLIDAY_EMER_NUMS) HOLIDAY_EMER_NUMS,SUM(EXPORT_OUTP_NUMS) EXPORT_OUTP_NUMS,
SUM(REGIST_CHARGES) REGIST_CHARGES
from VDSS_CLINIC_MASTER_REC t where t.DIM_DATE=to_date('||p_date||',"yyyy/MM/dd") and rownum <=2000 group by '||str1||','||str2||','||str3||','||str4||','||str5||';';BEGIN
delete from temp_form_data;
open CUR1 for sql_statement;
loop
fetch cur1 into t_date,t_dept,t_identity,t_charge,t_operator,t_type,t_time,t_outp,t_emer,t_daytimeoutp,t_daytimeemer,t_night,t_holidayoutp,
t_holidayemer,t_export,t_regist;
exit when CUR1%notfound;
insert into temp_form_data values(t_date,t_dept,t_identity,t_charge,t_operator,t_type,t_time,t_outp,t_emer,t_daytimeoutp,t_daytimeemer,t_night,t_holidayoutp,
t_holidayemer,t_export,t_regist);
end loop;
close CUR1;
commit;
END;
t_dept varchar2(36);
t_identity varchar2(36);
t_charge varchar2(36);
t_operator varchar2(36);
t_type varchar2(36);
t_time varchar2(36);
t_outp number(6,2);
t_emer number(6,2);
t_daytimeoutp number(6,2);
t_daytimeemer number(6,2);
t_night number(6,2);
t_holidayoutp number(6,2);
t_holidayemer number(6,2);
t_export number(6,2);
t_regist number(6,2);
CUR1 SYS_REFCURSOR;
sql_statement varchar2(3000):='select dim_date,'||str1||','||str2||','||str3||','||str4||','||str5||',sum(t.outp_nums) OUPT_NUMS,sum(emer_nums) EMER_NUMS,
sum(daytime_outp_nums) DAYTIME_OUTP_NUMS,sum(daytime_emer_nums) DAYTIME_EMER_NUMS,
SUM(NIGHT_OUTP_NUMS) NIGHT_OUTP_NUMS,SUM(HOLIDAY_OUTP_NUMS) HOLIDAY_OUTP_NUMS,SUM(HOLIDAY_EMER_NUMS) HOLIDAY_EMER_NUMS,SUM(EXPORT_OUTP_NUMS) EXPORT_OUTP_NUMS,
SUM(REGIST_CHARGES) REGIST_CHARGES
from VDSS_CLINIC_MASTER_REC t where t.DIM_DATE=to_date('||p_date||',"yyyy/MM/dd") and rownum <=2000 group by '||str1||','||str2||','||str3||','||str4||','||str5||';';BEGIN
delete from temp_form_data;
open CUR1 for sql_statement;
loop
fetch cur1 into t_date,t_dept,t_identity,t_charge,t_operator,t_type,t_time,t_outp,t_emer,t_daytimeoutp,t_daytimeemer,t_night,t_holidayoutp,
t_holidayemer,t_export,t_regist;
exit when CUR1%notfound;
insert into temp_form_data values(t_date,t_dept,t_identity,t_charge,t_operator,t_type,t_time,t_outp,t_emer,t_daytimeoutp,t_daytimeemer,t_night,t_holidayoutp,
t_holidayemer,t_export,t_regist);
end loop;
close CUR1;
commit;
END;
解决方案 »
- ebs不能为更新或删除保留记录
- JDBC连接ORACLE11G RAC的连接串方
- oracle 空间不够 请帮忙
- 求Oracle格式化SQL脚本命令?
- 关于JOB的问题~~~
- 视图基本概念
- 这样的SQL语句在Ora中不好用,看看错在哪里了??
- 初学者疑惑:oracle用户权限和数据库设计的问题?请求各位高手耐心解答!!!绝对给分
- 大型ORACLE数据库应用的讨论,欢迎高手提出见解
- 菜鸟问题,如何自动带出最后三次保存的值
- oracle12c x64, plsql developer 11, 无法导出导入表
- 安装oracle11g的时候显示Oracle Net Configuration Assistant 失败,手动配置netca也失败,急
open cur1 for sql_statement 报缺失表达式
sql没问题的,我是测试完写进存储过程的
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10已选择14行。SQL> select * from t;未选定行SQL> declare v_empno varchar2(50);
2 v_ename varchar2(50);
3 v_sql varchar2(1000);
4 begin
5 v_empno:='empno';
6 v_ename:='ename';
7 v_sql:='insert into t select '||v_empno||','||v_ename||' from emp where sal>2500';
8 execute immediate v_sql;
9 end;
10 /PL/SQL 过程已成功完成。SQL> select * from t; ID NN
---------- --------------------------------------------------
7566 JONES
7698 BLAKE
7788 SCOTT
7839 KING
7902 FORD