oracle包如何写,oracle如何写包

一：如何使用FOR循环

二：如何使用拼接语句 EXECUTE IMMEDIATE v_sqlINTO 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;

/