CREATE PROCEDURE statement

Use the CREATE PROCEDURE statement to create a user-defined procedure. (To create a procedure from text of source code that is in a separate file, use the CREATE PROCEDURE FROM statement.)

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

>>-CREATE--+--------------+--+---------+--PROCEDURE--+---------------+------------>
           |    (1)       |  |  (2)    |             '-IF NOT EXISTS-'   
           '-OR REPLACE---'  '-DBA-----'                          
  
                                           

>--+-procedure------+------------------------------------------->
   |  (3)           |   
   '-------function-'   

>--(--+--------------------------------+--)--------------------->
      |                            (4) |      
      '-| Routine Parameter List |-----'      

>--+-----------------------------------------------------------------+-->
   |                        (5)                                      |   
   '-| REFERENCING Clause |------FOR -+--------------+--table_object-'   
                                      '-'--owner--'.-'                   

>--+-----------------------------+------------------------------>
   |  (2)                    (6) |   
   '-------| Return Clause |-----'   

>--+---------------------------------+-------------------------->
   |                             (7) |   
   '-SPECIFIC--| Specific Name |-----'   

>--+------------------------------------------------+--+---+---->
   |                .-,------------------------.    |  '-;-'   
   |  (3)           V                      (8) |    |          
   '-------WITH--(----| Routine Modifier |-----+--)-'          

      (3)                      (9)                 
>--+-------| Statement Block |-----------------+---------------->
   |  (10)                                 (11) |   
   '-------| External Routine Reference |------'   

>--END PROCEDURE--+--------------------------------------+------>
                  |           .-,----------------------. |   
                  |           V                   (12) | |   
                  '-DOCUMENT----| Quoted String |------+-'   

>--+-----------------------------+-----------------------------><
   '-WITH LISTING IN--'pathname'-'
Note:
  1. See OR REPLACE Clause
  2. See DBA Keyword and Privileges on the Procedure
  3. Stored Procedure Language only
  4. See Routine Parameter List
  5. See The REFERENCING and FOR Clauses
  6. See Return Clause
  7. See Specific Name
  8. See Routine modifier
  9. See Statement Block
  10. External routines only
  11. See External Routine Reference
  12. See Quoted String
Element Description Restrictions Syntax
function, procedure Name declared here for a new SPL routine See Procedure names in HCL OneDB. Identifier
owner Owner of table_object Must own table_object Owner name
pathname File to store compile-time warnings Must exist on the computer where the database resides Operating system specific
table_object Name or synonym of a table or view whose triggers can call this UDR Must exist in the local database Identifier

Usage

In , you can use CREATE PROCEDURE only as text within a PREPARE statement. If you want to create a procedure for which the text is known at compile time, you must use a CREATE PROCEDURE FROM statement.

If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if a procedure of the specified name is already registered in the current database. (Because the identifier of a procedure can be overloaded, it might be unnecessary to include these keywords, if the database server can resolve the argument list of the new procedure as different from that of any other procedure of the same name in the current database.)

Routines use the collating order that was in effect when they were created. See SET COLLATION statement statement of HCL OneDB™ for information about using non-default collation.

Example

For this example, assume that you have two overloaded procedures that are defined as follows: 
CREATE PROCEDURE raise_prices ( per_cent INT)
	UPDATE stock SET unit_price = unit_price + (unit_price * (per_cent/100) ); 
END PROCEDURE

CREATE PROCEDURE raise_prices ( per_cent INT, selected_unit CHAR ) 
	UPDATE stock SET unit_price = unit_price + (unit_price * (per_cent/100) )
	where unit=selected_unit; 
END PROCEDURE
In order to refer to the above procedures, you would need to provide the procedure name followed by the parameter list, as in the following examples: 
DROP PROCEDURE raise_prices(INT);
DROP PROCEDURE raise_prices(INT, CHAR);
A more convenient way is to use the specific name to identify each of them. The following example will create the procedure using the specific name: 
CREATE PROCEDURE raise_prices ( per_cent INT ) SPECIFIC 
  raise_prices_all
	UPDATE stock SET unit_price = unit_price + (unit_price * (per_cent/100) ); 
END PROCEDURE

DROP SPECIFIC PROCEDURE raise_prices_all;

CREATE PROCEDURE raise_prices ( per_cent INT, selected_unit CHAR ) 
  SPECIFIC raise_prices_by_unit
	UPDATE stock SET unit_price = unit_price + (unit_price * (per_cent/100) )
	where unit=selected_unit; 
END PROCEDURE
We can simply drop them using their specific names: 
DROP SPECIFIC PROCEDURE raise_prices_by_all;
DROP SPECIFIC PROCEDURE raise_prices_by_unit;