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_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;
解决方案 »
- 为什么分区表会入库很慢
- Holding lock: oracle/jdbc/driver/T4CConnection@0x01D826F8[thin lock]
- 求教如何在windows中进入oracle的图形管理界面
- 怎么查看表中有哪些索引,视图,存储过程等信息?
- 请问有没有软件可以自动执行SQL语句,然后填写到指定的EXCEL单元格里面
- 求数据库恢复的方法?
- 用imp导入数据时出现IMP-00013错误,求救!
- 急:一个用户使用另一个用户所创建的表的问题!
- 为何作业提交成功后,在OEM控制台中总是失败状态?(在线等)
- 我以自建的用户登陆SQL PLUS。系统提示“访问PRODUCT_USER_PROFILE时错误:未载入产品用户配置文件信息”。并且用SQL语句查不到表,不知
- 动态日期,按日月统计的问题...
- 这是主键约束错误吗?
但这样传参有问题:where TRUNC(a.mo_start_date)=' || T_IN_DAY ;
where TRUNC(a.mo_start_date)=to_date('||T_IN_DAY||')' ;
我想 你这里也是被转义了 加个||