在procedure中,如何输出某个SQL语句块的内容,如下面红色字体部门。
主要是想检查变量是否正确传值?create or replace procedure test_procedure is v_lottype varchar2(40);begin
v_lottype:='(''P'',''R'',''M'',''E1'',''E2'',''E3'',''E9'')';
--v_lottype:='(' ||'P'||','||'R'||','||'M'||','||'E1'||','||'E2'||','||'E3'||','||'E9'||')';
v_cuthour:='00';--dbms_output.put_line('v_rwprod : ' || v_rwprod); --v_eoh
select nvl(sum(componentqty),0) into v_eoh from mfg_tbl_info_wip_hourly_ii
where lottype in v_lottype
and productname not like 'RW%'
and stage not in (select distinct stage from probe_stage where type = 'ALL')
and adstatus != 'Bank'
and histdate=to_char(sysdate,'yyyymmdd')
and hour= v_cuthour ;
--dbms_output.put_line('SQLERRM : ' || SQLERRM);
end;Best Regards
Linda Liu
主要是想检查变量是否正确传值?create or replace procedure test_procedure is v_lottype varchar2(40);begin
v_lottype:='(''P'',''R'',''M'',''E1'',''E2'',''E3'',''E9'')';
--v_lottype:='(' ||'P'||','||'R'||','||'M'||','||'E1'||','||'E2'||','||'E3'||','||'E9'||')';
v_cuthour:='00';--dbms_output.put_line('v_rwprod : ' || v_rwprod); --v_eoh
select nvl(sum(componentqty),0) into v_eoh from mfg_tbl_info_wip_hourly_ii
where lottype in v_lottype
and productname not like 'RW%'
and stage not in (select distinct stage from probe_stage where type = 'ALL')
and adstatus != 'Bank'
and histdate=to_char(sysdate,'yyyymmdd')
and hour= v_cuthour ;
--dbms_output.put_line('SQLERRM : ' || SQLERRM);
end;Best Regards
Linda Liu
解决方案 »
- 求分析函数lead与lag用法例子
- 求一sql,有点难度,高手帮忙!
- 触发器具体怎么用啊?
- 组合外健的定义.急求帮助
- MYSQL与ORACLE数据同步
- exp的query参数?
- 帮忙优化一个查询语句
- 如何把表A的字段A_ID替换成001,002,003,004,005,....有序的自动增加1的数据?(初学))
- 怎样创建一个可自动增一的列作为表的主键,就像Access一样,再现等待,急
- HELP:为何我在vc++ 6下用ado连oracle时不能添加新纪录?
- c# 连接运行存储过程报这个错 ORA-01036: 非法的变量名/编号
- oracle 创建临时表,用过一次如何删除,不然ORA-00955: 名称已由现有对象使用
v_sql := '
select nvl(sum(componentqty),0) from mfg_tbl_info_wip_hourly_ii
where lottype in v_lottype
and productname not like ''RW%''
and stage not in (select distinct stage from probe_stage where type = ''ALL'')
and adstatus != ''Bank''
and histdate=to_char(sysdate,''yyyymmdd'')
and hour= v_cuthour';
dbms_output.put_line(v_sql);--执行
execute immediate v_sql into v_eoh;...
用的是ORACLE 9i
貌似也是用的varchar,超过4000就不成了,你可以测试下.