Oracle stored procedure Basic syntax

Line 1:

Create OR REPLACE PROCEDURE is an SQL statement that notifies the Oracle database to create a stored procedure called skeleton, overwriting it if it exists;

Line 2:

The IS keyword indicates that a PL/SQL body is followed.

Line 3:

The BEGIN keyword indicates the beginning of the PL/SQL body.

Line 4:

The NULL PL/SQL statement indicates that nothing is done, and this sentence cannot be deleted because there is at least one sentence in the PL/SQL body;

Line 5:

End keyword indicates the termination of the PL/SQL body

Stored Procedure creation Syntax:

Create or Replace procedure stored procedure name (param1 in type,param2 out type)

As

Variable 1 type (value range); --VS_MSG VARCHAR2 (4000);

Variable 2 type (value range);

Begin

Select COUNT (*) into variable 1 from table A where column name =param1;

If (judging condition) then

Select column name into Variable 2 from table a where column name =param1;

Dbms_output. put_line (' Print information ');

elsif (judging condition) then

Dbms_output. put_line (' Print information ');

Else

Raise exception name (No_data_found);

End if;

Exception

When others then

Rollback;

End;

Precautions:

1, the stored procedure parameter does not take a range of values, in represents the incoming, out represents the output

Type can use a legitimate type in any Oracle.

2, variable with value range, followed by semicolon

3, it is best to use the count (*) function to determine if there is a record of the operation before judging the statement.

4, with Select ... Into ... Assigning values to variables

5, throwing exceptions in code with raise+ exception name

CREATE OR REPLACE Procedure Stored procedure name
(

--Defining parameters
Is_ym in CHAR (6),

The_count out number,
)
As
--Defining variables
VS_MSG VARCHAR2 (4000); -- error message variable
Vs_ym_beg CHAR (6); -- Start month
Vs_ym_end CHAR (6); -- end of month
Vs_ym_sn_beg CHAR (6); --month of the same period
Vs_ym_sn_end CHAR (6); -- month ending in the same period

--Define a cursor ( simply a result set that can be traversed )

CURSOR cur_1 is
SELECT ...
From ...
WHERE ...
GROUP by ...;

BEGIN

--Using the input parameters to assign the initial value to the variable, using the oralce SUBSTR to_char add_months

To_date and other functions that are commonly 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 the data for a specific condition in the table.

DELETE from table name WHERE ym = Is_ym;

--Then use the Put_Line method of the built-in dbms_output object to print out the number of recorded rows affected, which uses a system variable sql%rowcount

Dbms_output.put_line (' del record last month = ' | | sql%rowcount| | ' bar ');  

Insert into table name (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 month record = ' | | sql%rowcount| | ' bar ');  
--Iterate over cursor processing and update to table. There are several ways to traverse a cursor, and a for statement is one of the more intuitive.

For rec in cur_1 LOOP
UPDATE Table Name
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 represents an arbitrary error except for declarations. SQLERRM is a system built-in variable that holds the details of the current error.

EXCEPTION

When OTHERS Then
Vs_msg: = ' ERROR in xxxxxxxxxxx_p (' | | | is_ym| | '): ' | | SUBSTR (sqlerrm,1,500);

ROLLBACK;

--Record the current error into the log table.

INSERT into Log_info (proc_name,error_info,op_date)
VALUES (' xxxxxxxxxxx_p ', vs_msg,sysdate);
COMMIT;
RETURN;

END;

Oracle stored procedure Basic syntax