Lab 12.2 Passing Parameters In and Out of 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:

  • Use IN and OUT Parameters with Procedures

Parameters

Parameters are the means to pass values to and from the calling environment to the server. These are the values that will be processed or returned via the execution of the procedure. There are three types of parameters: IN, OUT, and IN OUT.

Modes

Modes specify whether the parameter passed is read in or a receptacle for what comes out.

Figure 12.1 illustrates the relationship between the parameters when they are in the procedure header versus when the procedure is executed.

Figure 12.1. Matching Procedure Call to Procedure Header

graphics/12fig01.gif

Formal and Actual Parameters

Formal parameters are the names specified within parentheses as part of the header of a module. Actual parameters are the valuesexpressions specified within parentheses as a parameter listwhen a call is made to the module. The formal parameter and the related actual parameter must be of the same or compatible datatypes. Table 12.1 explains the three types of parameters.

Passing of Constraints (Datatype) with Parameter ValueS

Formal parameters do not require constraints in datatypefor example, instead of specifying a constraint such as VARCHAR2(60), you just say VARCHAR2 against the parameter name in the formal parameter list. The constraint is passed with the value when a call is made.

Matching Actual and Formal Parameters

Two methods can be used to match actual and formal parameters: positional notation and named notation. Positional notation is simply association by position: The order of the parameters used when executing the procedure matches the

Table 12.1. Three Types of Parameters

Mode

Description

Usage

IN

Passes a value into the program

  • Read only value

  • Constants, literals, expressions

  • Cannot be changed within program Default Mode

OUT

Passes a value back from the program

  • Write only value

  • Cannot assign default values

  • Has to be a variable

  • Value assigned only if the program is successful

IN OUT

Passes values in and also sends values back

  • Has to be a variable

  • Values will be read and then written

order in the procedure's header exactly. Named notation is explicit association using the symbol =>.

 Syntax: formal_parameter_name => argument_value 

In named notation, the order does not matter. If you mix notation, list positional notation before named notation.

Default values can be used if a call to the program does not include a value in the parameter list. Note that it makes no difference which style is used; they will both function similarly.

Lab 12.2 Exercises

12.2.1 Use IN and OUT Parameters with Procedures

Create the following text file in a text editor. Run the script at a SQL*Plus session.

 -- ch12_02a.sql  CREATE OR REPLACE PROCEDURE find_sname    (i_student_id IN NUMBER,     o_first_name OUT VARCHAR2,     o_last_name OUT VARCHAR2     )  AS  BEGIN    SELECT first_name, last_name      INTO o_first_name, o_last_name      FROM student     WHERE student_id = i_student_id;  EXCEPTION    WHEN OTHERS    THEN      DBMS_OUTPUT.PUT_LINE('Error in finding student_id:        '||i_student_id);  END find_sname; 

a)

Explain what is happening in the find_sname procedure. What parameters are being passed into and out of the procedure? How would you call the procedure?

Call the find_sname script with the following anonymous block:

 -- ch12_03a.sql  DECLARE    v_local_first_name student.first_name%TYPE;    v_local_last_name student.last_name%TYPE;  BEGIN    find_sname      (145, v_local_first_name, v_local_last_name);    DBMS_OUTPUT.PUT_LINE      ('Student 145 is: '||v_local_first_name||       ' '|| v_local_last_name||'.'      );  END; 

b)

Explain the relationship between the parameters that are in the procedures header definition versus the parameters that are passed IN and OUT of the procedure.

Lab 12.2 Exercise Answers

12.2.1 Answers

a)

Explain what is happening in the find_sname procedure. What parameters are being passed into and out of the procedure? How would you call the procedure?

A1:

Answer: The procedure takes in a student_id via the parameter named i_ student_id. It passes out the parameters o_first_name and o_last_name. The procedure is a simple SELECT statement retrieving the first_name and last_name from the Student table where the student_id matches the value of the i_ student_id, which is the only in parameter that exists in the procedure. To call the procedure, a value must be passed in for the i_student_id parameter.

b)

Explain the relationship between the parameters that are in the procedures header definition versus the parameters that are passed IN and OUT of the procedure.

A1:

Answer: When calling the procedure find_sname, a valid student_id should be passed in for the i_student_id. If it is not a valid student_id, the exception will be raised. Two variables must also be listed when calling the procedure. These variables, v_local_first_name and v_local_last_name, are used to hold the values of the parameters that are being passed out. After the procedure has been executed, the local variables will have value and can then be displayed with a DBMS_OUTPUT.PUT _LINE.

Lab 12.2 Self-Review Questions

In order to test your progress, you should be able to answer the following questions.

Answers appear in Appendix A, Section 12.2.

1)

The benefits of module code are that it (check all that apply)

  1. _____ takes IN and OUT parameters.

  2. _____ can be called by many types of calling environments.

  3. _____ is stored in the database.

  4. _____ is always valid.

2)

All module code contains the following components (check all that apply):

  1. _____ Header

  2. _____ Footer

  3. _____ Declaration

  4. _____ Exception

  5. _____ Execution

3)

If a procedure has an IN parameter, then it must have an OUT parameter.

  1. _____ True

  2. _____ False

4)

Which are valid parameter definitions in the header of a parameter? (check all that apply)

  1. _____ P_LAST_NAME IN OUT VARCHAR2(20)

  2. _____ P_STUDID OUT IN NUMBER

  3. _____ P_ZIPCODE NUMBER

  4. _____ P_COURSE_COST IN NUMBER := 1095

5)

The view USER_SOURCE only contains the code of valid procedures.

  1. _____ True

  2. _____ False


    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