Example of a database stored procedure

Create or Replace procedure p_do_staff_qualify (I_get_type in Varchar2,
I_org_no in Varchar2,
I_staff_no in Varchar2,
i_apply_occu_id in Varchar2,
Iapply_occu_name in Varchar2,
i_apply_work_type_id in Varchar2,
I_apply_work_type_name in Varchar2,
i_apply_title_id in Varchar2,
I_apply_title_name in Varchar2,
i_create_user_id in Varchar2,
O_rtnstr out VARCHAR2,
O_rtninfo out VARCHAR2) is
----Shaosong
--i_org_no code for the institution where the person is logged in
--i_get_type Operation Action
--i_staff_no Personnel Code
--i_apply_occu_id applying for a career code
--I_APPLY_WORK_TYPE_ID application for the Proctor code
--I_APPLY_TITLE_ID Application Title Code
--i_create_user_id Creating a person code

v_num int: = 0;
V_ber int: = 0;
V_org_level varchar2 (100);
Begin
O_rtnstr: = ' false ';
O_rtninfo: = ' operation failed ';
If i_create_user_id = "or i_create_user_id is null then
O_rtninfo: = ' illegal user ';
Return
End If;

Select COUNT (*)
Into V_num
From T_f_user t
where t.user_id = i_create_user_id
and t.is_valid = ' 0 ';

If V_num < 1 Then
O_rtninfo: = ' Operation user not present ';
Return
End If;
Select Org_level into V_org_level from t_f_org where org_no = I_org_no;
-------Evaluator Operations
--Application for qualification of assessors
If V_org_level = ' 3 ' Then
If I_get_type = ' staff ' then
Select COUNT (*)
Into V_ber
From T_f_staff_qualify
where staff_no = I_staff_no
and apply_occu_id = i_apply_occu_id
and apply_work_type_id = i_apply_work_type_id
and apply_title_id = i_apply_title_id;
If V_ber >= 1 Then
O_rtninfo: = ' The employee is qualified as the Professional evaluator ';
Elsif V_ber < 1 Then

INSERT INTO T_f_staff_qualify
(Staff_no,
APPLY_OCCU_ID,
Apply_occu_name,
APPLY_WORK_TYPE_ID,
Apply_work_type_name,
APPLY_TITLE_ID,
Apply_title_name,
Verify_state,
Create_time,
CREATE_USER_ID)
Values
(I_staff_no,
I_APPLY_OCCU_ID,
Iapply_occu_name,
I_APPLY_WORK_TYPE_ID,
I_apply_work_type_name,
I_APPLY_TITLE_ID,
I_apply_title_name,
' 00 ',
Sysdate,
I_CREATE_USER_ID);
Commit
O_rtninfo: = ' The employee applied for success! ‘;


End If;
End If;

elsif v_org_level = ' 2 ' Then
--Appraisal Center audit to audit the qualification of examiners
--Through the Accreditation Center audit
If I_get_type = ' Verifyispass ' Then
Update t_f_staff_qualify
Set verify_state = ' 10 ',
verify_opinion_2 = ' through ',
verify_time_2 = Sysdate
where verify_state = ' 00 '
and staff_no = I_staff_no
and apply_occu_id = i_apply_occu_id
and apply_work_type_id = i_apply_work_type_id
and apply_title_id = i_apply_title_id;
Commit
O_rtninfo: = ' Accreditation Center audit passed ';

--not approved by the accreditation Center
elsif i_get_type = ' Verifyisunpass ' Then
Update t_f_staff_qualify
Set verify_state = ' 11 ',
Verify_opinion_2 = ' The subject of the study does not match ',
verify_time_2 = Sysdate
where verify_state = ' 00 '
and staff_no = I_staff_no
and apply_occu_id = i_apply_occu_id
and apply_work_type_id = i_apply_work_type_id
and apply_title_id = i_apply_title_id;
Commit
O_rtninfo: = ' Accreditation center audit failed ';
End If;
elsif v_org_level = ' 1 ' Then
--Verification of the accreditation of the Evaluation Center to review the qualification of examiners
--Through the Accreditation Guidance Center audit
If I_get_type = ' Verifyispass ' Then
Update t_f_staff_qualify
Set verify_state = ' 20 ',
verify_opinion_1 = ' through ',
Verify_time_1 = Sysdate
where verify_state = ' 10 '
and staff_no = I_staff_no
and apply_occu_id = i_apply_occu_id
and apply_work_type_id = i_apply_work_type_id
and apply_title_id = i_apply_title_id;
Commit
O_rtninfo: = ' Accreditation Guidance Center audit passed ';

--not approved by the Accreditation Guidance Center
elsif i_get_type = ' Verifyisunpass ' Then
Update t_f_staff_qualify
Set verify_state = ' 21 ',
Verify_opinion_1 = ' The subject of the study does not match ',
Verify_time_1 = Sysdate
where verify_state = ' 10 '
and staff_no = I_staff_no
and apply_occu_id = i_apply_occu_id
and apply_work_type_id = i_apply_work_type_id
and apply_title_id = i_apply_title_id;
Commit
O_rtninfo: = ' Certification Guidance Center audit failed ';

End If;
Else
O_rtninfo: = ' users and other permissions are too low to operate! ‘;
End If;
Commit

---------Network Management operations
--Application of network administrator qualification

If V_org_level = ' 3 ' Then
If I_get_type = ' webmaster ' Then
Select COUNT (*)
Into V_ber
From T_f_staff_qualify
where staff_no = I_staff_no
and apply_title_id = ' 004 ';
If V_ber >= 1 Then
O_rtninfo: = ' The employee is already a network administrator! ‘;
Elsif V_ber < 1 Then

INSERT INTO T_f_staff_qualify
(Staff_no,
APPLY_TITLE_ID,
Apply_title_name,
Verify_state,
Create_time,
CREATE_USER_ID)
Values
(I_staff_no,
I_APPLY_TITLE_ID,
I_apply_title_name,
' 0 ',
Sysdate,
I_CREATE_USER_ID);
Commit
O_rtninfo: = ' The employee applied for success! ‘;


End If;
End If;

elsif v_org_level = ' 2 ' Then
--Appraisal Center to audit the qualification of network administrator
--Through the Accreditation Center audit
If I_get_type = ' Webmasterispass ' Then
Update t_f_staff_qualify
Set verify_state = ' 1 ',
verify_opinion_2 = ' through ',
verify_time_2 = Sysdate
where verify_state = ' 0 '
and staff_no = I_staff_no;
Commit
O_rtninfo: = ' Accreditation Center audit passed ';

--not approved by the accreditation Center
elsif i_get_type = ' Webmasterisunpass ' Then
Update t_f_staff_qualify
Set verify_state = ' 2 ',
Verify_opinion_2 = ' literacy is too low ',
verify_time_2 = Sysdate
where verify_state = ' 0 '
and staff_no = I_staff_no;
Commit
O_rtninfo: = ' Accreditation center audit failed ';
End If;
elsif v_org_level = ' 1 ' Then
--Appraisal Guidance Center to audit the network administrator qualification
--Through the Accreditation Guidance Center audit
If I_get_type = ' Webmasterispass ' Then
Update t_f_staff_qualify
Set verify_state = ' 3 ',
verify_opinion_1 = ' through ',
Verify_time_1 = Sysdate
where verify_state = ' 1 '
and staff_no = I_staff_no;
Commit
O_rtninfo: = ' Accreditation Guidance Center audit passed ';

--not approved by the Accreditation Guidance Center
elsif i_get_type = ' Webmasterisunpass ' Then
Update t_f_staff_qualify
Set verify_state = ' 4 ',
Verify_opinion_1 = ' literacy is too low ',
Verify_time_1 = Sysdate
where verify_state = ' 1 '
and staff_no = I_staff_no;
Commit
O_rtninfo: = ' Certification Guidance Center audit failed ';

End If;
Else
O_rtninfo: = ' users and other permissions are too low to operate! ‘;
End If;
Commit
End P_do_staff_qualify;

Example of a database stored procedure