Oracle Stored Procedure functions

1. Functions

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. Stored Procedures

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. Cursor Stored Procedures

Create or Replace procedure Proc_check (insert_by VARCHAR) is
V_oid VARCHAR2 (36);

Begin
Declare
--type definition
Cursor C_check
Is
Select Dep_oid,oms_user_oid
From Dcjetframework.v_oms_user;
--Define a cursor variable v_cinfo c_emp%rowtype, which is a row of data type in the cursor 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 Stored Procedure functions