Example of creating a stored procedure in Oracle

-- Create a stored procedure
Create   Or   Replace   Procedure Xxxxxxxxxxx_p
(
-- The in parameter indicates the input parameter,
-- Out indicates the input parameter. The type can be of any valid Oracle type.
Is_ym In   Char
)
As
-- Define Variables
Vs_msg Varchar2 ( 4000 ); -- Error message variable
Vs_ym_beg Char ( 6 ); -- Start month
Vs_ym_end Char ( 6 ); -- End month
Vs_ym_sn_beg Char ( 6 ); -- Starting month of the same period
Vs_ym_sn_end Char ( 6 ); -- End month of the same period

-- Define a cursor (simply put, it is a result set that can be traversed)
Cursor Cur_1 Is
Select Area_code, cmcode, Sum (RMB _amt) / 10000 RMB _amt_sn, Sum (Usd_amt) / 10000 Usd_amt_sn
From Bgd_area_cm_m_base_t
Where Ym > = Vs_ym_sn_beg
And Ym <= Vs_ym_sn_end
Group   By Area_code, cmcode;

Begin
-- Input parameters are used to assign an initial value to the variable, and common functions such as substr to_char add_months to_date of oralce are used.
Vs_ym_beg: = Substr (is_ym, 1 , 6 );
Vs_ym_end: = Substr (is_ym, 7 , 6 );
Vs_ym_sn_beg: = To_char (add_months (to_date (vs_ym_beg, ' Yyyymm ' ), - 12 ), ' Yyyymm ' );
Vs_ym_sn_end: = To_char (add_months (to_date (vs_ym_end, ' Yyyymm ' ), - 12 ), ' Yyyymm ' );
-- First, delete data with specific conditions in the table.
Delete   From Xxxxxxxxxxx_t Where Ym = Is_ym;
-- Then, the put_line method of the built-in dbms_output object is used to print the number of affected records. A system variable SQL % rowcount is used.
Dbms_output.put_line ( ' Del last month record = ' | SQL % Rowcount | ' Entries ' );

Insert   Into Xxxxxxxxxxx_t (area_code, ym, cmcode, RMB _amt, usd_amt)
Select Area_code, is_ym, cmcode, Sum (RMB _amt) / 10000 , Sum (Usd_amt) / 10000
From Bgd_area_cm_m_base_t
Where Ym > = Vs_ym_beg
And Ym <= Vs_ym_end
Group   By Area_code, cmcode;

Dbms_output.put_line ( ' INS records for the current month = ' | SQL % Rowcount | ' Entries ' );
-- The traversal cursor is processed and updated to the table. You can use the for statement to traverse a cursor in several ways.
For REC In Cur_1 Loop
Update Xxxxxxxxxxx_t
Set RMB _amt_sn = Rec. RMB _amt_sn, usd_amt_sn = Rec. usd_amt_sn
Where Area_code = Rec. area_code
And Cmcode = Rec. cmcode
And Ym = Is_ym;
End Loop;

Commit ;
-- Error Handling Section. Others indicates any errors except declarations. Sqlerrm is a built-in variable that saves detailed information about the current error.
Exception
When Others Then
Vs_msg: =   ' Error in xxxxxxxxxxx_p ( ' | Is_ym | ' ): ' | Substr (sqlerrm, 1 , 500 );
Rollback ;
-- Record the current error to the log table.
Insert   Into Log_info (proc_name, error_info, op_date)
Values ( ' Xxxxxxxxxxx_p ' , Vs_msg, sysdate );
Commit ;
Return ;
End ;

 

From: http://ayaya.javaeye.com/blog/149191