Oracle中动态拼接参数怎么传,急oracle 动态游标传日期参数是如何传的呢，请赐教，多谢...

当前位置:我的异常网» Oracle开发 » 急oracle 动态游标传日期参数是如何传的呢，请赐教

急oracle 动态游标传日期参数是如何传的呢，请赐教，多谢

www.myexceptions.net  网友分享于：2013-08-08  浏览：49次

急！急！急！oracle 动态游标传日期参数是怎么传的呢，请赐教，谢谢！

create or replace procedure P_T_LCD_MAJOR_REPORT

(

T_MONTH IN VARCHAR2 ---定义传入年月参数

)

AS

T_DAYS INT;-- 天数

T_CNT INT;

T_IN_MONTH varchar2(20);

T_IN_DAY DATE;

T_DEFAULT_LINE VARCHAR2(8);--线别

T_MO_START_DATE DATE;--日期

T_INPUT_QTY INT;--投入数

T_ErrorPinQty INT;--不良数

T_PPM INT;--PPM值

type refCur is ref cursor;--定义动态游标

cur refCur ;

sqlstr varchar2(10000);

BEGIN

T_IN_DAY:=to_date(T_MONTH ||'26','yyyy-MM-dd' );

select add_months(trunc(to_date(T_MONTH||'01','yyyy-MM-dd'),'mm'),1) - trunc(to_date(T_MONTH||'01','yyyy-MM-dd'),'mm') tt into T_DAYS from dual;-- 求传入月份的天数

--DBMS_OUTPUT.put_line(T_DAYS);

open cur for 'select a.default_line,a.mo_start_date,a.input_qty,decode(b.ErrorPinQty,null,0,b.ErrorPinQty)ErrorPinQty,

round(decode(b.ErrorPinQty,null,0,b.ErrorPinQty)*1000000/decode(a.input_qty,null,1,a.input_qty),0) PPM

from (select default_line,TRUNC(mo_start_date) mo_start_date,sum(input_qty) input_qty

from t_pm_mo_base

where mo_start_date is not null

group by default_line,TRUNC(mo_start_date)

order by mo_start_date,default_line) a

left join(select TEST_LINE,TEST_TIME,count(serial_number) ErrorPinQty

from

(select distinct t.serial_number,t.TEST_LINE, trunc(t.TEST_TIME) TEST_TIME,t.SUPPLIER,c.ERROR_TYPE1

from t_re_repair t,t_co_error_code c

where t.test_code=c.error_code and c.error_type1=0

order by t.TEST_LINE,TEST_TIME)

group by TEST_LINE,TEST_TIME

order by TEST_LINE,TEST_TIME)b

on a.default_line=b.TEST_LINE and a.mo_start_date=b.TEST_TIME

where TRUNC(a.mo_start_date)=' || T_IN_DAY ;

loop

fetch cur into T_DEFAULT_LINE,T_MO_START_DATE,T_INPUT_QTY,T_ErrorPinQty,T_PPM;

dbms_output.put_line(T_DEFAULT_LINE);

exit when cur %notfound;

end loop;

END;

------解决方案--------------------

试一下把T_IN_DAY改为字符型再组合

where TRUNC(a.mo_start_date)=to_date('||T_IN_DAY||')' ;

文章评论