Procedures

You can create a procedure that contains a group of SQL and PL/SQL statements. Procedures allow you to centralize your business logic in the database and may be used by any program that accesses the database.

In this section, you ll learn how to:

• Create a procedure

• Call a procedure

• Get information on procedures

• Drop a procedure

• View errors in a procedure

Creating a Procedure

You create a procedure using the CREATE PROCEDURE statement. The simplified syntax for the CREATE PROCEDURE statement is as follows :

 CREATE [OR REPLACE] PROCEDURE  procedure_name  [(  parameter_name  [IN  OUT  IN OUT]  type  [, ...])] {IS  AS} BEGIN  procedure_body  END  procedure_name;  

where

• OR REPLACE specifies the procedure is to replace an existing procedure if present. You can use this option when you want to modify a procedure.

• procedure_name specifies the name of the procedure.

• parameter_name specifies the parameter name. A procedure may be passed multiple parameters.

• IN OUT IN OUT specifies the mode of the parameter. You may pick one of the following modes for each parameter:

  • IN is the default mode for a parameter. This mode is specified for parameters that already have a value when the procedure is run and that value may not be changed in the body.

  • OUT is specified for parameters whose values are only set in the body.

  • IN OUT is specified for parameters that may already have a value when the procedure is called, but their value may also be changed in the body.

• type specifies the type of the parameter.

• procedure_body contains the SQL and PL/SQL statements to perform the procedure s task.

The following statement defines a procedure named update_product_price(). This statement is contained in the store_schema.sql script. The update_product_price() procedure multiplies the price of a product by a factor ”the product ID and the factor are passed as parameters to the procedure. If the specified product doesn t exist, the procedure takes no action, otherwise it updates the product price by the factor.

 CREATE OR REPLACE PROCEDURE update_product_price(   p_product_id IN products.product_id%TYPE,   p_factor IN NUMBER ) AS   v_product_count INTEGER; BEGIN   -- count the number of products with the   -- supplied product_id (should be 1 if the product exists)   SELECT COUNT(*)   INTO v_product_count   FROM products   WHERE product_id = p_product_id;   -- if the product exists (v_product_count = 1) then   -- update that product's price   IF v_product_count = 1 THEN     UPDATE products     SET price = price * p_factor     WHERE product_id = p_product_id;     COMMIT;   END IF; EXCEPTION   WHEN OTHERS THEN     ROLLBACK; END update_product_price; / 

As you can see from this listing, the procedure takes two parameters named p_product_id and p_factor. Notice that both of these parameters use the IN mode, which means their values cannot be changed in the body.

The declaration section contains an INTEGER variable named v_product_count:

 v_product_count INTEGER; 

The body of the procedure follows this declaration section, starting with BEGIN . The first statement in the body uses a SELECT statement that counts the number of products with the specified ID. The count is performed using the COUNT() function:

 -- count the number of products with the -- supplied product_id (should be 1 if the product exists) SELECT COUNT(*) INTO v_product_count FROM products WHERE product_id = p_product_id; 

Note  

COUNT(*) counts the rows and returns the total.

If the product exists in the table, v_product_count will be set to 1. If the product doesn t exist, v_product_count will be 0. If the value in v_product_count is 1, the price column can be multiplied by p_factor using an UPDATE statement, and the change can be committed. The following IF statement is

 -- if the product exists (v_product_count = 1) then -- update that product's price IF v_product_count = 1 THEN   UPDATE products   SET price = price * p_factor   WHERE product_id = p_product_id;   COMMIT; END IF; 

The EXCEPTION block performs a ROLLBACK if an exception is raised:

 EXCEPTION   WHEN OTHERS THEN     ROLLBACK; 

Finally, the END keyword is used to mark the end of the procedure:

 END update_product_price; / 

Note  

The repetition of the procedure name at the end is not required, but it is good programming practice to put it in.

Calling a Procedure

You call a procedure using the CALL statement. The example you ll see in this section will multiply the price of product #1 by 1.5 using the update_product_price() procedure shown in the previous section. First, the following query retrieves the price of product #1 so you can compare with the modified price later:

  SELECT price   FROM products   WHERE product_id = 1;  PRICE ----------      19.95 

The following statement calls update_product_price(), passing the parameter values 1 (the product_id) and 1.5 (the factor to multiply the price of the product by):

 CALL update_product_price(1, 1.5); 

The next query retrieves the details for product #1 again; notice the price has increased:

  SELECT price   FROM products   WHERE product_id = 1;  PRICE ---------- -----29.93 

Getting Information on Procedures

Note  

You can get information on your procedures from the user_procedures view. Table 11-2 describes some of the columns in user_procedures.

Note  

You can get information on all the procedures you have access to using all_procedures.

The following example retrieves the object_name, aggregate, and parallel columns from user_procedures for update_product_price():

  SELECT object_name, aggregate, parallel   FROM user_procedures   WHERE object_name = 'UPDATE_PRODUCT_PRICE';  OBJECT_NAME                    AGG PAR ------------------------------ --- --- UPDATE_PRODUCT_PRICE           NO  NO 

Table 11-2: Some Columns in user_procedures

Column	Type	Description
OBJECT_NAME	VARCHAR2(30)	Name of the object, which may be a procedure, function, or package name.
PROCEDURE_NAME	VARCHAR2(30)	Name of the procedure.
AGGREGATE	VARCHAR2(3)	Whether the procedure is an aggregate function. Set to YES or NO .
IMPLTYPEOWNER	VARCHAR2(30)	Name of the owner of the implementation type (if any).
IMPLTYPENAME	VARCHAR2(30)	Name of the implementation type (if any).
PARALLEL	VARCHAR2(3)	Whether or not the procedure or function is enabled for parallel queries. Set to YES or NO .

Dropping a Procedure

You drop a procedure using DROP PROCEDURE . For example, the following statement drops update_product_price():

 DROP PROCEDURE update_product_price; 

Viewing Errors in a Procedure

If the database reports an error when you create a procedure (or function), you can view the errors by issuing a SHOW ERRORS command. For example, the following CREATE PROCEDURE statement attempts to create a procedure that has a syntax error at line 6 (the parameter should be p_dob, not p_dobs):

 SQL>  CREATE OR REPLACE PROCEDURE update_customer_dob (  2  p_customer_id INTEGER, p_dob DATE  3  ) AS  4  BEGIN  5  UPDATE customers  6  SET dob = p_dobs  7  WHERE customer_id = p_customer_id;  8  END update_customer_dob;  9  /  Warning: Procedure created with compilation errors. 

As you can see, the database reports a compilation error in the procedure. To view the errors, you issue the SHOW ERRORS command, for example:

 SQL>  SHOW ERRORS  Errors for PROCEDURE UPDATE_CUSTOMER_DOB: LINE/COL ERROR -------- --------------------------------------------- 5/3      PL/SQL: SQL Statement ignored 6/13     PL/SQL: ORA-00904: invalid column name 

As you can see, line 5 was ignored because an invalid column name was referenced in line 6 of the CREATE PROCEDURE statement. You can fix the error by issuing an EDIT command, changing p_dobs to p_dob, and rerunning the CREATE PROCEDURE statement by entering /.