Example of a database stored procedure

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.