1.
create or replace procedure PROC_CHECK(Insert_by VARCHAR) IS v_OID VARCHAR2( 36 ); begin declare -- 類型定義 cursor c_check is select dep_oid,oms_user_oid from dcjetframework.v_oms_user; --定義一個游標變量v_cinfo c_emp% ROWTYPE ,該類型為游標c_emp中的一行數據類型 c_row c_check % rowtype; begin for c_row in c_check loop -- dbms_output.put_line(c_row.dep_oid|| ' - ' || c_row.oms_user_oid); v_OID: = sys_guid(); INSERT INTO T_OMS_DEVICE_CHECK(OID,DEPT_OID,User_Oid,INSERT_BY) VALUES(v_OID,c_row.dep_oid,c_row.oms_user_oid,Insert_by); INSERT INTO t_oms_device_check_detail(check_oid,device_oid,insert_by) SELECT v_OID,OID,Insert_by FROM v_oms_check WHERE Dep_Oid =c_row.dep_oid AND APPLICANT= c_row.oms_user_oid ; end loop; COMMIT; end;
2.
create or replace procedure proc_approve(P_OID IN Varchar2, P_Insert_By IN Varchar2,P_operation_type VARCHAR2) is v_stg Varchar2( 50 ); BEGIN IF P_operation_type = ' 11 ' THEN BEGIN SELECT t.WAREHOUSE_OID INTO v_stg FROM T_OMS_DEVICE_REGIST_LOG t WHERE t.DEVICE_OID =P_OID AND t.operation_type= ' 13 ' AND ROWNUM= 1 ORDER BY t.INSERT_time DESC; UPDATE T_OMS_DEVICE_REGIST t SET t.scrapping_status = ' 2 ' ,t.warehouse_oid=v_stg,t.status= ' 9 ' ,t.SCRAPED_TIME=SYSDATE WHERE t.OID= P_OID; INSERT INTO T_OMS_DEVICE_REGIST_LOG VALUES( sys_guid(),P_OID,P_operation_type, ' 報廢審批通過 ' ,v_stg,Sysdate, '' ,P_Insert_By,Sysdate); COMMIT; END; ELSIF P_operation_type = ' 12 ' THEN BEGIN UPDATE T_OMS_DEVICE_REGIST t SET t.scrapping_status = ' 3 ' WHERE t.OID= P_OID; INSERT INTO T_OMS_DEVICE_REGIST_LOG VALUES( sys_guid(),P_OID,P_operation_type, ' 報廢審批不通過 ' , '' ,Sysdate, '' ,P_Insert_By,Sysdate); COMMIT; END; END IF; end proc_approve;
3.
create or replace procedure proc_check_confirm(check_oid VARCHAR2) is begin UPDATE t_oms_device_check SET confirm_satus = ' 1 ' ,CONFIRM_TIME=SYSDATE WHERE OID= check_oid; end proc_check_confirm;
4.
create or replace procedure PROC_DESTROY_DELETE(p_headOid VARCHAR2,p_result OUT VARCHAR2) IS BEGIN SELECT status INTO p_result FROM t_oms_device_destroy WHERE OID =p_headOid AND rownum= 1 ; IF p_result = ' 2 ' THEN BEGIN RETURN; END; END IF; p_result: = ' 1 ' ; DELETE FROM t_oms_device_destroy WHERE OID = p_headOid; UPDATE t_oms_device_regist SET STATUS = ' 9 ' , DESTROY_OID= '' WHERE destroy_oid= p_headOid; DELETE FROM t_oms_device_regist_attachment WHERE device_oid = p_headOid; COMMIT; end PROC_DESTROY_DELETE;
5.
create or replace function get_Destroy_no return varchar2 is Result varchar2( 50 ); begin SELECT max(destroy_no) INTO RESULT FROM t_oms_device_destroy WHERE substr(destroy_no, 0 , 8 )= to_char( SYSDATE , ' yyyymmdd ' ); IF (RESULT = '' OR RESULT IS NULL) THEN RESULT: =(to_char( SYSDATE , ' yyyymmdd ' )|| ' 001 ' ); else RESULT: =to_number(RESULT)+ 1 ; END IF; RETURN( Result); end get_Destroy_no;
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
