Handout 19

Stored Procedures

---

Overview

Stored procedures (groups of SQL and PL/SQL statements) allow you to move code that enforces business rules from your application to your database. For our purposes in getting started, you are asked to create two relatively simple stored procedures using only SQL. If interested, you may look up details on PL/SQL, which includes procedural constructs (repetition, selection, sequence).

Thoughts on developing your stored procedure:

• Where developed?
  In your SQL*Plus window. In this window you issue a CREATE PROCEDURE sql statement.

• Syntax for creating a procedure:

  CREATE [or REPLACE] PROCEDURE  procedure
  [(argument [IN|OUT|IN OUT] datatype
  [, argument [IN|OUT|IN OUT] datatype]  
  {IS|AS} block;
  
  where block is the code to be executed as in:
  BEGIN 
  	code
  END
  

  Consider the following example to add a record to an owner table:

• Owner Table description:

  SQL> describe owner
   Name                            Null?    Type
   ------------------------------- -------- ----
   ID                              NOT NULL CHAR(5)
   FIRST_NAME                      NOT NULL CHAR(20)
   LAST_NAME                       NOT NULL CHAR(20)
   CITY                            NOT NULL CHAR(4)
   PHONE                           NOT NULL CHAR(12)
  

• CREATE PROCEDURE command to add a record:

  CREATE PROCEDURE addowner
     (id IN CHAR,
     fn IN CHAR,
     ln IN CHAR,
     c IN CHAR, 
     p IN CHAR)
  AS
      BEGIN
         INSERT INTO owner
           (id, first_name, last_name, city, phone)
         VALUES
           (id, fn, ln, c, p);
       END;
  

• Code to run the procedure:

  SQL> EXECUTE ADDOWNER('6', 'John', 'Jones', 'SLO', '805-756-1111');
  
  PL/SQL procedure successfully completed.
  

• Suppose you made an error ... consider the following where we forgot to put in the BEGIN statement:

  CREATE PROCEDURE addowner
     (id IN CHAR,
     fn IN CHAR,
     ln IN CHAR,
     c IN CHAR, 
     p IN CHAR)
  AS
      
         INSERT INTO owner
           (id, first_name, last_name, city, phone)
         VALUES
           (id, fn, ln, c, p);
       END;
  
  

• We would get an error message indicating that the procedure compiled incorrectly. To see what is wrong we can issue a SHOW ERRORS command as shown below:

  Warning: Procedure created with compilation errors.
  
  SQL> show errors
  Errors for PROCEDURE ADDOWNER:
  
  LINE/COL ERROR
  -------- -----------------------------------------------------------------
  8/8      PLS-00103: Encountered the symbol "INSERT" when expecting one of
           the following:
           begin function package pragma procedure subtype type use
             etc.
           The symbol "begin" was substituted for "INSERT" to continue.
  
  

  Hopefully the error message is of assistance in debugging your code!