Oracle Stored Procedure syntax

The following is a simple example to apply the usage of the stored procedure mentioned above: Assume that there are two tables, one is studnet, and the field is stdId.

The following is a simple example to apply the usage of the stored procedure mentioned above: Assume that there are two tables, one is studnet, and the field is stdId.

1 create or replace procedure stored PROCEDURE name
2 IS
3 BEGIN
4 NULL;
5 END;
　　
Row 1:
Create or replace procedure is an SQL statement that notifies the Oracle database to CREATE a stored PROCEDURE called skeleton, and overwrites it if it exists;
Row 2:
The IS keyword indicates that a PL/SQL body will be followed.
Row 3:
The BEGIN keyword indicates the start of the PL/SQL body.
Row 4:
The null pl/SQL statement indicates that nothing is done. This statement cannot be deleted because at least one sentence is required in the PL/SQL body;
Row 5:
The END keyword indicates the END 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 (condition) then
Select column name into variable 2 from Table A where column name = param1;
Dbms_output. Put_line ('print info ');
Elsif (Judgment condition) then
Dbms_output. Put_line ('print info ');
Else
Raise Exception name (NO_DATA_FOUND );
End if;
Exception
When others then
Rollback;
End;

Note:
1. stored procedure parameters do not have a value range. in indicates input, and out indicates output.
Type can use the legal type in any Oracle.
2. The variable has a value range followed by a semicolon
3. Use the count (*) function to determine whether the operation record exists before determining the statement.
4. select... Into... Assign values to variables
5. If an exception is thrown in the code, use raise + Exception name.

Create or replace procedure stored PROCEDURE name
(
-- Define parameters
Is_ym in char (6 ),
The_count out number,
)
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); -- start month of the same period
Vs_ym_sn_end CHAR (6); -- end month of the same period

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

CURSOR cur_1 IS
SELECT...
FROM...
WHERE...
Group ...;

BEGIN

-- Assign the initial value to the variable using the input parameter, and use the Oralce
SUBSTR
TO_CHAR
ADD_MONTHS
TO_DATE and other 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, 'yyymm'),-12), 'yyymm ');
Vs_ym_sn_end: = TO_CHAR (ADD_MONTHS (TO_DATE (vs_ym_end, 'yyymm'),-12), 'yyymm ');

-- Delete data with specific conditions in the table first.

Delete from table name WHERE ym = is_ym;

-- Then use the put_line method of the built-in DBMS_OUTPUT object to print the number of affected records. One system variable SQL % rowcount is used.

DBMS_OUTPUT.put_line ('del last month record = '| 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 Record of the current month = '| SQL % rowcount |' bar ');
-- 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 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 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;