在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
解决方案 »
- oracle11g
- 各位大虾,求教!关于oracle的decode
- Oracle安装字符集乱码
- oracle与mysql的区别
- 删除用户没反应,强行结束后,再删除也不行.加 cascade也不行.其他用户也是一样.环境:AIX5.2,ORACLE 9i ,ERP TIPTOP
- Oracle中查询前10条记录
- 帮我把下面的Sql Server触发器转换为oracle的,表名一样的!
- 如何释放一张表所占有的空间(已经DROP)了,急急!!
- 外键问题!高分
- kettle怎么链接数据库 求步骤 新手不懂 详细点
- 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就不成了,你可以测试下.