一：如何使用FOR循环
二：如何使用拼接语句 EXECUTE IMMEDIATE v_sql INTO v_WORK_ORDERID;
三：如何定义记录类型做为变量，用于存储及查询
CREATE OR REPLACE PACKAGE pkg_GenerateReport_biz IS
/*===============================================
* PROGRAM NAME: pkg_CSS_CUSTOMER_DELETE_SYNC_biz
*
* DESCRIPTION: ??????????????????
*
* HISTORY:
* 1.00 2016-07-28 chenli Creation
* ==============================================*/
PROCEDURE main(P_INV_ORGID decimal);
END pkg_GenerateReport_biz;
/
CREATE OR REPLACE PACKAGE BODY pkg_GenerateReport_biz IS
/* =============================================
* FUNCTION / PROCEDURE
* NAME :main
* DESCRIPTION:
??????????????
* ARGUMENT:
* RETURN:
*
* HISTORY:
* 1.00 2016-07-28 chenli Creation
* =============================================*/
PROCEDURE main(P_INV_ORGID decimal) IS
v_SQL VARCHAR2(4000); --获取相关工单
v_SQL_UPDATE_OFFLINE VARCHAR2(4000); --更新下线完数
v_SYS_ROLES_CONFIG_row SYS_ROLES_CONFIG%ROWTYPE; --系统配置项
v_OQC_INSP_SAMPLE_RPT_row OQC_INSP_SAMPLE_RPT%ROWTYPE; --抽检报告
v_OQC_INSP_SAMPLE_RPT_SN_row OQC_INSP_SAMPLE_RPT_SN%rowtype;
v_WORK_ORDERID VARCHAR2(5000); --相关工单
str_production_date_from varchar2(50); --生产开始时间
str_production_date_to varchar2(50); --生产结束时间
begin
for v_rlt in (select *
from (select *
from oqc_inspection_sample s
where s.state = 'A'
AND S.status = '已检验'
and s.is_v2 = '1'
AND s.org_id = P_INV_ORGID
order by s.inspection_time desc)
where rownum <= 10) loop
str_production_date_from := to_char(v_rlt.production_start_time,
'yyyy-mm-dd hh24:mi:ss');
str_production_date_to := to_char(v_rlt.production_end_time,
'yyyy-mm-dd hh24:mi:ss');
select *
INTO v_SYS_ROLES_CONFIG_row
FROM SYS_ROLES_CONFIG c
WHERE c.org_id = v_rlt.org_id
AND c.state = 'A';
v_WORK_ORDERID := '';
v_SQL := '';
v_SQL := v_SQL ||
'SELECT wm_concat( DISTINCT(O.MO_NAME)) FROM OQC_MES_INF_UL_QMS_OFFLINE O WHERE O.ORG_ID = ' ||
P_INV_ORGID ||
' AND O.DATETIME_OFFLINE>= to_date(''' ||
str_production_date_from ||
''',''yyyy-mm-dd hh24:mi:ss'') AND O.DATETIME_OFFLINE<=to_date(''' ||
str_production_date_to ||
''',''yyyy-mm-dd hh24:mi:ss'')';
IF v_SYS_ROLES_CONFIG_row.Sys_Parms1 = '同生产工单' THEN
v_SQL := v_SQL || ' and O.MO_NAME=''' || v_rlt.WORK_ORDER_ID || '''';
elsif v_SYS_ROLES_CONFIG_row.Sys_Parms2 = '同物料编码' THEN
v_SQL := v_SQL || ' and O.PRODUCT_CODE=''' || v_rlt.PROD_ID || '''';
elsif v_SYS_ROLES_CONFIG_row.Sys_Parms3 = '同产品型号' THEN
v_SQL := v_SQL || ' and O.PRODUCT_TYPE=''' || v_rlt.PROD_MODEL_ID || '''';
elsif v_SYS_ROLES_CONFIG_row.Sys_Parms4 = '同产线' THEN
v_SQL := v_SQL || ' and O.LINE_DESC=''' || v_rlt.PRODUCTION_LINE || '''';
END IF;
EXECUTE IMMEDIATE v_sql
INTO v_WORK_ORDERID;
v_OQC_INSP_SAMPLE_RPT_row.User_Created := v_rlt.user_created;
v_OQC_INSP_SAMPLE_RPT_row.Datetime_Created := sysdate;
v_OQC_INSP_SAMPLE_RPT_row.Inspection_No := v_rlt.inspection_no;
v_OQC_INSP_SAMPLE_RPT_row.Org_Id := v_rlt.org_id;
v_OQC_INSP_SAMPLE_RPT_row.Inspection_Result := v_rlt.inspection_result;
v_OQC_INSP_SAMPLE_RPT_row.Workorderno := v_WORK_ORDERID;
v_OQC_INSP_SAMPLE_RPT_row.Ref_Sample_Id := v_rlt.id;
v_OQC_INSP_SAMPLE_RPT_row.Inspection_Type := '抽检';
v_OQC_INSP_SAMPLE_RPT_row.Id := SYS_GUID();
v_OQC_INSP_SAMPLE_RPT_row.State := 'A';
if v_rlt.inspection_result = '合格' then
v_OQC_INSP_SAMPLE_RPT_row.IS_UPDATE_MES := 'Y';
elsif v_rlt.inspection_result = '不合格' then
v_OQC_INSP_SAMPLE_RPT_row.IS_UPDATE_MES := 'N';
END IF;
--1、增加抽检报告表
INSERT INTO OQC_INSP_SAMPLE_RPT VALUES v_OQC_INSP_SAMPLE_RPT_row; --插入抽检报告
--2、插入抽检的条码
for v_title in (select *
from oqc_base_standrad_title t
where t.baseamine_id = v_rlt.Id
and t.is_insepection_result = '1'
and t.state = 'A') loop
v_OQC_INSP_SAMPLE_RPT_SN_row.Id := sys_guid();
v_OQC_INSP_SAMPLE_RPT_SN_row.Sn := v_title.barcode;
v_OQC_INSP_SAMPLE_RPT_SN_row.State := 'A';
v_OQC_INSP_SAMPLE_RPT_SN_row.Datetime_Created := SYSDATE;
v_OQC_INSP_SAMPLE_RPT_SN_row.Rpt_Id := v_OQC_INSP_SAMPLE_RPT_row.Id;
v_OQC_INSP_SAMPLE_RPT_SN_row.User_Created := v_rlt.User_Created;
INSERT INTO OQC_INSP_SAMPLE_RPT_SN
VALUES v_OQC_INSP_SAMPLE_RPT_SN_row; --插入抽检报告
end loop;
--3、更新下线完工表
v_SQL_UPDATE_OFFLINE := '';
if v_rlt.inspection_result = '合格' then
v_SQL_UPDATE_OFFLINE := v_SQL_UPDATE_OFFLINE ||
'update OQC_MES_INF_UL_QMS_OFFLINE O set
INSPECTION_RESULT=''' ||
v_rlt.INSPECTION_RESULT ||
''',O.BATCH_INSPECTION_RESULT=''' ||
v_rlt.INSPECTION_RESULT ||
''',O.BATCH_NO=''' || v_rlt.INSPECTION_NO ||
''',O.BATCH_DATETIME_CREATED=sysdate where ORG_ID=' ||
v_rlt.ORG_ID;
elsif v_rlt.inspection_result = '不合格' then
v_SQL_UPDATE_OFFLINE := v_SQL_UPDATE_OFFLINE ||
'update OQC_MES_INF_UL_QMS_OFFLINE O set
O.BATCH_DATETIME_CREATED=sysdate where O.ORG_ID=' ||
v_rlt.ORG_ID;
end if;
v_SQL_UPDATE_OFFLINE := v_SQL_UPDATE_OFFLINE ||
' AND O.DATETIME_OFFLINE>= to_date(''' ||
str_production_date_from ||
''',''yyyy-mm-dd hh24:mi:ss'') AND O.DATETIME_OFFLINE<=to_date(''' ||
str_production_date_to ||
''',''yyyy-mm-dd hh24:mi:ss'')';
IF v_SYS_ROLES_CONFIG_row.Sys_Parms1 = '同生产工单' THEN
v_SQL_UPDATE_OFFLINE := v_SQL_UPDATE_OFFLINE || ' and O.MO_NAME=''' ||
v_rlt.WORK_ORDER_ID || '''';
elsif v_SYS_ROLES_CONFIG_row.Sys_Parms2 = '同物料编码' THEN
v_SQL_UPDATE_OFFLINE := v_SQL_UPDATE_OFFLINE ||
' and O.PRODUCT_CODE=''' || v_rlt.PROD_ID || '''';
elsif v_SYS_ROLES_CONFIG_row.Sys_Parms3 = '同产品型号' THEN
v_SQL_UPDATE_OFFLINE := v_SQL_UPDATE_OFFLINE ||
' and O.PRODUCT_TYPE=''' ||
v_rlt.PROD_MODEL_ID || '''';
elsif v_SYS_ROLES_CONFIG_row.Sys_Parms4 = '同产线' THEN
v_SQL_UPDATE_OFFLINE := v_SQL_UPDATE_OFFLINE ||
' and O.LINE_DESC=''' ||
v_rlt.PRODUCTION_LINE || '''';
end if;
EXECUTE IMMEDIATE v_SQL_UPDATE_OFFLINE;
--4、更新抽检表
update oqc_inspection_sample s
set s.is_inspector = '1',
s.status = '已生成报告',
s.datetime_report_generated = sysdate,
s.batch_no = v_rlt.inspection_no
where s.id = v_rlt.id
and s.org_id = v_rlt.org_id;
commit; --最后提交
end loop;
end;
END pkg_GenerateReport_biz;
/