produces example

create or replace procedure sp_OUTCONTA_INPUT_DOCK( pv_conta_no varchar2, --ÏäºÅ pv_truck_no varchar2, --ÍϳµºÅ pv_truck_time varchar2, --³öբʱ¼ä pv_result out varchar2 --½á¹û£¬1Ϊ³É¹¦£¬0²»³É¹¦ ) as /* ³ö¿ÚÏä×ßÏû·ÀͨµÀÂëÍ·²¹Â¼³öÕ¢ÐÅÏ¢ XIAOQI 070313 ÐÂÔöÖмä¿âCICÓû§µÄ±íciq_outto_dock_info info_typeΪ8µÄ¸üÐÂΪ12 */ lv_IO_STATUS ciq_outto_dock_info.IO_STATUS%type; lv_CUSTOM_SHIP_VOY ciq_outto_dock_info.CUSTOM_SHIP_VOY%type; lv_SHIP_VOY ciq_outto_dock_info.SHIP_VOY%type; lv_CUSTOM_S_D_NO ciq_outto_dock_info.CUSTOM_S_D_NO%type; lv_CONTA_NUM ciq_outto_dock_info.CONTA_NUM%type; lv_CONTA_INFO ciq_outto_dock_info.CONTA_INFO%type; lv_DOCK_CODE ciq_outto_dock_info.DOCK_CODE%type; lv_BGH_CODE ciq_outto_dock_info.BGH_CODE%type; lv_BGH_NAME ciq_outto_dock_info.BGH_NAME%type; lv_BGD_CODE ciq_outto_dock_info.BGD_CODE%type; l_truck_time date; l_info_date date := sysdate; ln_count number; ln_infoType number; begin --Èç¹û´æÔÚINFO_TYPE = 12 µÄ¼Ç¼£¬Ôò²»×ö²Ù×÷ select count(*) into ln_count from cic_ciq_outto_dock_info@ora2 where info_type = 12 and CONTA_NO = pv_conta_no ; if ln_count !=0 then pv_result := '0'; return; end if; select distinct IO_STATUS,CUSTOM_SHIP_VOY,SHIP_VOY,CUSTOM_S_D_NO,CONTA_NUM, CONTA_INFO,DOCK_CODE,BGH_CODE,BGH_NAME,BGD_CODE into lv_IO_STATUS,lv_CUSTOM_SHIP_VOY,lv_SHIP_VOY,lv_CUSTOM_S_D_NO,lv_CONTA_NUM, lv_CONTA_INFO,lv_DOCK_CODE,lv_BGH_CODE,lv_BGH_NAME,lv_BGD_CODE from ciq_outto_dock_info where info_type = 8 and CONTA_NO = pv_conta_no ; --°Ñ¹ý³Ì״̬ln_infoType = 12£¬ ln_infoType := 12; l_truck_time := to_date(pv_TRUCK_TIME,'yyyy-mm-dd hh24:mi'); --·ÖÂëÍ·°ÑÊý¾Ý²åÈëÂëÍ·Êý¾Ý¿â¼°CICÊý¾Ý¿â if lv_DOCK_CODE='G0' THEN BEGIN lv_DOCK_CODE:='CCT'; END; END IF; if lv_DOCK_CODE='C0' THEN BEGIN lv_DOCK_CODE:='SCT'; END; END IF; if lv_DOCK_CODE='B1' THEN BEGIN lv_DOCK_CODE:='SKP'; END; END IF; if lv_DOCK_CODE='H0' THEN BEGIN lv_DOCK_CODE:='MW'; END; END IF; if lv_DOCK_CODE='Z0' THEN BEGIN lv_DOCK_CODE:='CIC'; END; END IF; if lv_DOCK_CODE='CCT' THEN BEGIN insert into cct_ciq_outto_dock_info@ora2 (INFO_TYPE,IO_STATUS,CUSTOM_SHIP_VOY,SHIP_VOY,CUSTOM_S_D_NO,CONTA_NO,CONTA_NUM,TRUCK_NO,TRUCK_TIME, info_date,CONTA_INFO,READ_FLAG,BGH_CODE,BGH_NAME,BGD_CODE) values (ln_infoType,lv_IO_STATUS,lv_CUSTOM_SHIP_VOY,lv_SHIP_VOY,lv_CUSTOM_S_D_NO,pv_conta_no,lv_CONTA_NUM,pv_TRUCK_NO,l_truck_time, l_info_date,lv_CONTA_INFO,0,lv_BGH_CODE,lv_BGH_NAME,lv_BGD_CODE); END; END IF; if lv_DOCK_CODE='SCT' THEN BEGIN insert into sct_ciq_outto_dock_info@ora2 (INFO_TYPE,IO_STATUS,CUSTOM_SHIP_VOY,SHIP_VOY,CUSTOM_S_D_NO,CONTA_NO,CONTA_NUM,TRUCK_NO,TRUCK_TIME, info_date,CONTA_INFO,READ_FLAG,BGH_CODE,BGH_NAME,BGD_CODE) values (ln_infoType,lv_IO_STATUS,lv_CUSTOM_SHIP_VOY,lv_SHIP_VOY,lv_CUSTOM_S_D_NO,pv_conta_no,lv_CONTA_NUM,pv_TRUCK_NO,l_truck_time, l_info_date,lv_CONTA_INFO,0,lv_BGH_CODE,lv_BGH_NAME,lv_BGD_CODE); END; END IF; if lv_DOCK_CODE='MW' THEN BEGIN insert into mw_ciq_outto_dock_info@ora2 (INFO_TYPE,IO_STATUS,CUSTOM_SHIP_VOY,SHIP_VOY,CUSTOM_S_D_NO,CONTA_NO,CONTA_NUM,TRUCK_NO,TRUCK_TIME, info_date,CONTA_INFO,READ_FLAG,BGH_CODE,BGH_NAME,BGD_CODE) values (ln_infoType,lv_IO_STATUS,lv_CUSTOM_SHIP_VOY,lv_SHIP_VOY,lv_CUSTOM_S_D_NO,pv_conta_no,lv_CONTA_NUM,pv_TRUCK_NO,l_truck_time, l_info_date,lv_CONTA_INFO,0,lv_BGH_CODE,lv_BGH_NAME,lv_BGD_CODE); END; END IF; if lv_DOCK_CODE='SKP' THEN BEGIN insert into SK_C_ciq_outto_dock_info@ora2 (INFO_TYPE,IO_STATUS,CUSTOM_SHIP_VOY,SHIP_VOY,CUSTOM_S_D_NO,CONTA_NO,CONTA_NUM,TRUCK_NO,TRUCK_TIME, info_date,CONTA_INFO,READ_FLAG,BGH_CODE,BGH_NAME,BGD_CODE) values (ln_infoType,lv_IO_STATUS,lv_CUSTOM_SHIP_VOY,lv_SHIP_VOY,lv_CUSTOM_S_D_NO,pv_conta_no,lv_CONTA_NUM,pv_TRUCK_NO,l_truck_time, l_info_date,lv_CONTA_INFO,0,lv_BGH_CODE,lv_BGH_NAME,lv_BGD_CODE); END; END IF; insert into cic_ciq_outto_dock_info@ora2 (INFO_TYPE,IO_STATUS,CUSTOM_SHIP_VOY,SHIP_VOY,CUSTOM_S_D_NO,CONTA_NO,CONTA_NUM,TRUCK_NO,TRUCK_TIME, info_date,CONTA_INFO,READ_FLAG,DOCK_CODE,BGH_CODE,BGH_NAME,BGD_CODE) values (ln_infoType,lv_IO_STATUS,lv_CUSTOM_SHIP_VOY,lv_SHIP_VOY,lv_CUSTOM_S_D_NO,pv_conta_no,lv_CONTA_NUM,pv_TRUCK_NO,l_truck_time, l_info_date,lv_CONTA_INFO,0,lv_DOCK_CODE,lv_BGH_CODE,lv_BGH_NAME,lv_BGD_CODE); pv_result := '1'; commit; exception when others then pv_result := '0'; end;[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/711001/viewspace-905388/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/711001/viewspace-905388/