Oracle Database Stored Procedure Learning

Stored Procedures

1 CREATE OR REPLACE PROCEDURE Stored procedure name

2 is

3 BEGIN

4 NULL;

5 end;

Line 1:

The 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 the following will follow a pl/sql body.

Line 3:

The BEGIN keyword indicates the beginning of the Pl/sql body.

Line 4:

A NULL pl/sql statement indicates that nothing is done, and this cannot be deleted, because at least one sentence is needed in the pl/sql body;

Line 5:

End keyword indicates that the pl/sql body's ending 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 info ');

elsif (judging condition) then

Dbms_output. Put_Line (' Print info ');

Else

Raise anomaly name (No_data_found);

End If;

Exception

When others then

Rollback;

End;

Precautions:

1, the stored procedure parameter does not take the value range, in represents the incoming, out represents the output

Types can use legitimate types in any Oracle.

2, the variable takes a value range, followed by a semicolon

3, it is best to use the count (*) function to determine whether an action record exists before judging the statement

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

5, throw exception 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); --Starting month
Vs_ym_end CHAR (6); --End of month
Vs_ym_sn_beg CHAR (6); --The beginning of the same month
Vs_ym_sn_end CHAR (6); --Termination month of the same period

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

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

BEGIN

--Using input parameters to assign the initial value to the variable, using the Oralce substr to_char add_months

To_date, such as the most common functions.
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 ');

--Deletes the data for a specific condition in the table first.

DELETE from table name WHERE ym = Is_ym;

-then print out the number of rows of records affected using the Put_Line method of the built-in Dbms_output object, which uses a system variable Sql%rowcount

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

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&NBSP
  and ym <= vs_ym_end 
GROUP&NBSP;BY&NBSP;AREA_CODE,CMCODE;&NBSP

Dbms_output.put_line (' ins month record = ' | | sql%rowcount| | ' , &NBSP,
--updates to the table after traversing the cursor processing. 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 any error except the declaration. SQLERRM is the 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 in 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 Syntax

1. Judgment statement:

If comparison type then begin end; End If;

Create or Replace procedure test (x in number) is

Begin

If x >0&n