How to Create a stored procedure in Oracle

The following is an example of the stored procedure I wrote in my project. For more information, see!

/* The method for calling the stored procedure in SQLPLUS is as follows :*/

Set serveroutput on;
Var C varchar2 (50 );
Exec aheoms. GetSEQ ('20170', '0',: C );
Print C

-------------------------------------------------------------

/*
Function: Get the title number
Parameter 1: Condition ID, which can be input according to the actual situation (AREA_ID, PLAN_ID, etc)
Parameter 2: Purchase type:
0: Get the primary key PLAN_ID of TBL_Y_PLAN
1: obtain the foreign key PLAN_ID of TBL_Y_PLAN_ITEM
2: Get the primary key PLAN_ID of TBL_M_PLAN
3: obtain the maximum value of ITEM_ID in TBL_M_PLAN_ITEM.
4: Obtain the call number NOTE_ID in TBL_ONDUTY_NOTE.
Parameter 3:
*/
Create or replace procedure aheoms. GetSEQ (
CONDITION_ID IN VARCHAR2,
SEQ_TYPE IN VARCHAR2,
SEQ_ID OUT VARCHAR2
)
IS
StrCondition VARCHAR2 (300 );
StrTempID VARCHAR2 (50 );
AREA_ID VARCHAR2 (10 );
BEGIN
IF SEQ_TYPE = '0' THEN
AREA_ID: = CONDITION_ID;
StrCondition: = CONCAT (AREA_ID, SUBSTR (TO_CHAR (SYSDATE, 'yyyy'), 3, 4), '% ');
Select max (PLAN_ID) INTO strTempID FROM TBL_Y_PLAN WHERE PLAN_ID LIKE strCondition;
If length (strTempID) = 9 THEN
SEQ_ID: = CONCAT (SUBSTR (strTempID,), LPAD (TO_CHAR (TO_NUMBER (SUBSTR (strTempID,) + 1), 3, '0 '));
ELSE
SEQ_ID: = CONCAT (AREA_ID, SUBSTR (TO_CHAR (SYSDATE, 'yyyy'), 3,4), '001 ');
End if;
-- Dbms_output.put_line (CONCAT ('new TBL_Y_PLAN.PLAN_ID: ', SEQ_ID ));
End if;
-- Parameter 2 is: 1. obtain the maximum number of ITEM_ID in TBL_Y_PLAN_ITEM through the input PLAN_ID and Add 1
IF SEQ_TYPE = '1' THEN
Select max (ITEM_ID) INTO strTempID FROM TBL_Y_PLAN_ITEM WHERE PLAN_ID = CONDITION_ID;
If length (strTempID)> 0 THEN
SEQ_ID: = LPAD (TO_CHAR (strTempID + 1), 4, '0 ');
ELSE
SEQ_ID: = '20140901 ';
End if;
End if;
-- The input AREA_ID of parameter 2 is as follows: 2. The monthly plan number is automatically calculated. The format is 05510508001.
IF SEQ_TYPE = '2' THEN
AREA_ID: = CONDITION_ID;
StrCondition: = CONCAT (CONDITION_ID, SUBSTR (TO_CHAR (SYSDATE, 'yyymmm '),),' % ');
Select max (PLAN_ID) INTO strTempID FROM TBL_M_PLAN WHERE PLAN_ID LIKE strCondition;
If length (strTempID) = 11 THEN
SEQ_ID: = CONCAT (SUBSTR (strTempID, 1, 8), LPAD (TO_CHAR (TO_NUMBER (SUBSTR (strTempID, 9, 11) + 1), 3, '0 '));
ELSE
SEQ_ID: = CONCAT (AREA_ID, SUBSTR (TO_CHAR (SYSDATE, 'yyymmm '),), '001 ');
End if;
End if;
-- Parameter 2 is: 3. obtain the maximum number of ITEM_ID in TBL_M_PLAN_ITEM through the input PLAN_ID and Add 1
IF SEQ_TYPE = '3' THEN
Select max (ITEM_ID) INTO strTempID FROM TBL_M_PLAN_ITEM WHERE PLAN_ID = CONDITION_ID;
If length (strTempID)> 0 THEN
SEQ_ID: = LPAD (TO_CHAR (strTempID + 1), 4, '0 ');
ELSE
SEQ_ID: = '20140901 ';
End if;
End if;
-- Parameter 2: 4: obtain the maximum NOTE_ID value in TBL_ONDUTY_NOTE and Add 1
IF SEQ_TYPE = '4' THEN
Select max (NOTE_ID) INTO strTempID FROM TBL_ONDUTY_NOTE WHERE LOG_ID = CONDITION_ID;
If length (strTempID)> 0 THEN
SEQ_ID: = strTempID + 1;
ELSE
SEQ_ID: = 1;
End if;
End if;
END GetSEQ;

In this way, you can create a stored procedure in sqlplus.

Use SEQ_TYPE as the marker to differentiate different functions.