| Lab Objectives After this Lab, you will be able to: 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.  | 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' | | 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 |
| 
