Lab 12.1 Creating Procedures

Team-Fly    

Oracle® PL/SQL® Interactive Workbook, Second Edition
By Benjamin Rosenzweig, Elena Silvestrova
Table of Contents
Chapter 12.  Procedures


Lab Objectives

After this Lab, you will be able to:

  • Create Procedures

  • Query the Data Dictionary for Information on Procedures

Benefits of Modular Code

A PL/SQL module is any complete logical unit of work. There are four types of PL/SQL modules: (1) anonymous blocks that are run with a text script (this is the type you have used until now), (2) procedures, (3) functions, and (4) packages.

There are two main benefits to using modular code: (1) It is more reusable and (2) it is more manageable.

You create a procedure either in SQL*Plus or in one of the many tools for creating and debugging stored PL/SQL code. If you are using SQL*Plus, you will need to write your code in a text editor and then run it at the SQL*Plus prompt.

Block Structure

The block structure is common for all the module types. The block begins with a header (for named blocks only), which consists of (1) the name of the module, and (2) a parameter list (if used).

The Declaration section consists of variable, cursors, and subblocks that will be needed in the next section.

The main part of the module is the Execution section, where all the calculations and processing is performed. This will contain executable code such as IF-THEN-ELSE, LOOPS, calls to other PL/SQL modules, and so on.

The last section of the module is an optional exception handler, which is where the code to handle exceptions is placed.

Anonymous Block

Until this chapter, you have only been writing anonymous blocks. Anonymous blocks are very much the same as modules, which were just introduced (except anonymous blocks do not have headers). There are important distinctions, though. As the name implies, anonymous blocks have no name and thus cannot be called by another block. They are not stored in the database and must be compiled and then run each time the script is loaded.

The PL/SQL block in a subprogram is a named block that can accept parameters and can be invoked from an application that can communicate with the Oracle database server. A subprogram can be compiled and stored in the database. This allows the programmer to reuse the program. It also provides for easier maintenance of code. Subprograms are either procedures or functions.

Procedures

A procedure is a module performing one or more actions; it does not need to return any values. The syntax for creating a procedure is as follows:

 CREATE OR REPLACE PROCEDURE name     [(parameter[, parameter, ...])]  AS     [local declarations]  BEGIN     executable statements  [EXCEPTION     exception handlers]  END [name]; 

A procedure may have 0 to many parameters. This will be covered in the next lab. Every procedure has two parts: (1) the header portion, which comes before AS (sometimes you will see ISthey are interchangeable), keyword (this contains the procedure name and the parameter list), and (2) the body, which is everything after the IS keyword. The word REPLACE is optional. When the word REPLACE is not used in the header of the procedure, in order to change the code in the procedure, the procedure must be dropped first and then re-created. Since it is very common to change the code of the procedure, especially when it is under development, it is strongly recommended to use the OR REPLACE option.

Lab 12.1 Exercises

12.1.1 Create Procedures

In this exercise, you will run a script that creates a procedure. Using a text editor such as Notepad, create a file with the following script.

 -- ch12_01a.sql  CREATE OR REPLACE PROCEDURE Discount  AS    CURSOR c_group_discount    IS      SELECT distinct s.course_no, c.description        FROM section s, enrollment e, course c       WHERE s.section_id = e.section_id         AND c.course_no = s.course_no       GROUP BY s.course_no, c.description,                e.section_id, s.section_id       HAVING COUNT(*) >=8;  BEGIN     FOR r_group_discount IN c_group_discount     LOOP        UPDATE course           SET cost = cost * .95         WHERE course_no = r_group_discount.course_no;        DBMS_OUTPUT.PUT_LINE          ('A 5% discount has been given to'||           r_group_discount.course_no||' '||           r_group_discount.description          );     END LOOP;  END; 

At the SQL*Plus session, run the script.

a)

What did you see on your screen? Explain what happened.

In order to execute in SQL*Plus use the following syntax:

 EXECUTE Procedure_name 

b)

Execute the Discount procedure. How did you accomplish this? What are the results that you see in your SQL*Plus screen?

c)

The script did not contain a COMMIT. Discuss the issues involved with placing a COMMIT in the procedure and indicate where the COMMIT could be placed.

12.1.2 Query the Data Dictionary for Information on Procedures

There are two main views in the data dictionary that provide information on stored code. They are the USER_OBJECTS view, to give information about the objects, and the USER_SOURCE, to give the text of the source code. Remember, the data dictionary also has an ALL_ and DBA_ version of these views.

a)

Write the select statement to get pertinent information from the USER_OBJECTS view about the Discount procedure you just wrote. Run the query and describe the results.

b)

Write the SELECT statement to display the source code from the USER_SOURCE view for the Discount procedure.

Lab 12.1 Exercise Answers

12.1.1 Answers

a)

What did you see on your screen? Explain what happened.

A1:

Answer: Procedure created. The procedure named Discount was compiled into p-code and stored in the database for later execution. Note if you saw an errorthis is due to a typing mistake. Recheck the code against the example in the book and recompile.

b)

Execute the Discount procedure. How did you accomplish this? What are the results that you see in your SQL*Plus screen?

A1:

 SQL> EXECUTE Discount  5% discount has been given to 25  Adv. Word Perfect  .... (through each course with an enrollment over 8)  PL/SQL procedure successfully completed. 

c)

The script did not contain a COMMIT. Discuss the issues involved with placing a COMMIT in the procedure and indicate where the COMMIT could be placed.

A2:

Answer: There is no COMMIT in this procedure, which means the procedure will not update the database. A COMMIT needs to be issued after the procedure is run, if you want the changes to be made. Alternatively, you can enter a COMMIT either before or after the END LOOP. If you put the COMMIT before the END LOOP, then you are committing changes after every loop. If you put the COMMIT after the END LOOP, then the changes will not be committed until after the procedure is near completion. It is wiser to take the second option. This way you are better prepared for handling errors.

graphics/intfig07.gif

If you receive an error, then type the command:

 Show error 

You can also add to the command:

 L start_line_number end_line_number 

to see a portion of the code in order to isolate errors.

12.1.2 Answers

a)

Write the select statement to get pertinent information from the USER_OBJECTS view about the Discount procedure you just wrote. Run the query and describe the results.

A1:

 SELECT object_name, object_type, status    FROM user_objects   WHERE object_name = 'DISCOUNT'; 

The result is:

 OBJECT_NAME          OBJECT_TYPE     STATUS  -------------------- --------------- ------ DISCOUNT             PROCEDURE       VALID 

The status indicates where the procedure was complied successfully. An invalid procedure cannot be executed.

b)

Write the SELECT statement to display the source code from the USER_SOURCE view for the Discount procedure.

A2:

 SQL> column text format a70       SELECT TO_CHAR(line, 99)||'>', text    FROM user_source  WHERE name = 'DISCOUNT' 

graphics/intfig07.gif

A procedure can become invalid if the table it is based on is deleted or changed. You can recompile an invalid procedure with the command

 alter procedure procedure_name compile 


    Team-Fly    
    Top
     



    Oracle PL. SQL Interactive Workbook
    Oracle PL/SQL Interactive Workbook (2nd Edition)
    ISBN: 0130473200
    EAN: 2147483647
    Year: 2002
    Pages: 146

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net