1.函數

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;

?

2.存儲過程

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;
COMMIT;
end PROC_DESTROY_DELETE;

?

3.游標存儲過程

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;

end PROC_CHECK;

oracle存儲過程函數