oracle存储过程函数

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;