Oracle Stored Procedure 1

Basic Structure
CREATE OR REPLACEPROCEDURE
(
Parameter 1 in number,
Parameter 2 IN NUMBER
) IS
Variable 1 INTEGER: = 0;
Variable 2 DATE;
BEGIN
END stored procedure name
 
--- Query data and return a list
Drop procedure mrr. get_risk_assessment_by_status;
Create or replace procedure mrr. get_risk_assessment_by_status (
Out_ref_cursor OUT SYS_REFCURSOR,
Stas IN VARCHAR2
) IS
BEGIN
OPEN out_ref_cursor
Select ra. riskassessment_id as RA_RISKASSESSMENT_ID, RA. riskconfig_id as RA_RISKCONFIG_ID, RA. assessment_status as RA_ASSESSMENT_STATUS, RA. changed_numeric_rating_id as RA_CHANGED_NUMERIC_RATING_ID, RA. changed_alpha_rating_id as RA_CHANGED_ALPHA_RATING_ID, RA. due_date as RA_DUE_DATE, RA. initial_assessment_complete as RA_INITIAL_ASSESSMENT_COMPLETE, RA. initial_numeric_rating_id as RA_INITIAL_NUMERIC_RATING_ID, RA. initial_alpha_rating_id as RA_INITIAL_ALPHA_RATING_ID, RA. notes as RA_NOTES, RA. prior_riskassessment_id as RA_PRIOR_RISKASSESSMENT_ID, RA. risk_mgmt_approval_date as RA_RISK_MGMT_APPROVAL_DATE, RA. risk_mgmt_approval_emp_id as RA_RISK_MGMT_APPROVAL_EMP_ID, RA. last_updated_by as RA_LAST_UPDATED_BY, RA. last_updated as RA_LAST_UPDATED,
RC. riskconfig_id as RC_RISKCONFIG_ID, RC. riskcategorygroup_id as RC_RISKCATEGORYGROUP_ID, RC. contractentity_id as RC_CONTRACTENTITY_ID, RC. accountgroup_id as RC_ACCOUNTGROUP_ID, RC. account_id as RC_ACCOUNT_ID, RC. contract_id as RC_CONTRACT_ID, RC. next_assessment_due_date as RC_NEXT_ASSESSMENT_DUE_DATE, RC. ninety_day_assessment_complete as RC_NINETY_DAY_ASSESS_COMPLE, RC. inactive_date as RC_INACTIVE_DATE, RC. last_updated_by as RC_LAST_UPDATED_BY, RC. last_updated as RC_LAST_UPDATED
From mrr. riskassessment RA, MRR. riskconfig RC
Where ra. riskconfig_id = RC. riskconfig_id
And ra. assessment_status = stas;
END;
/
Grant execute on mrr. get_risk_assessment_by_status TO DMM_KCMRLRSK_ROLE;
/
 

This article is from the "Fallen Angel" blog