PLSQL_8_2_sg (1)

Oracle Academy: Database Programming with PL/SQL

Objectives

• Key objectives of the lesson include:

  • Describe how parameters contribute to a procedure.

  • Define a parameter.

  • Create a procedure using a parameter.

  • Invoke a procedure that has parameters.

  • Differentiate between formal and actual parameters.

Purpose of Parameters

• Procedures need flexibility to handle various purposes and data types.

• Input parameters allow for dynamic data to be passed to the procedure.

• Calculated results can be returned to the caller using parameters.

Understanding Parameters

• Parameters function to pass or communicate data between the caller and the subprogram.

• Think of parameters as special variables that:

  • Are initialized by the calling environment when the subprogram is invoked.

  • Return output values back to the caller when completed.

• Naming convention: Parameters are often prefixed with "p_".

Example of Parameters in Use

• Procedure example for changing a student's grade:

  • Parameters: p_student_id, p_class_id, and p_grade act as local variables.

  • Caller passes values such as Student Id (1023), Class Id (543), and New Grade (B).

Procedure Code Example

• Procedure Definition:

  PROCEDURE change_grade  (p_student_id IN NUMBER, p_class_id IN NUMBER, p_grade IN VARCHAR2) IS 
  BEGIN
      UPDATE grade_table SET grade = p_grade WHERE student_id = p_student_id AND class_id =  p_class_id;
  END;

• Important distinction: Parameter is the name whereas argument is the actual value passed during runtime.

Types of Parameters

• There are two types of parameters:

  • Formal Parameters: Declared in the procedure heading (e.g., p_emp_id).

  • Actual Parameters: The corresponding values in the calling environment (e.g., v_emp_id).

• Compatibility in data types between formal and actual parameters is crucial.

Creating Procedures with Parameters

• Example on how to create a procedure and call it:

  CREATE OR REPLACE PROCEDURE raise_salary (p_id IN my_employees.employee_id%TYPE, p_percent IN NUMBER) IS 
  BEGIN
      UPDATE my_employees SET salary = salary * (1 + p_percent/100) WHERE employee_id = p_id;
  END raise_salary;

• Invocation example:

  BEGIN raise_salary(176, 10); END;

Invoking Procedures with Parameters

• Use anonymous blocks to invoke a procedure:

  • Call the procedure name followed by its argument values in the correct order.

Summary of Terminology

• Parameter: Data communicated between caller and subprogram.

• Argument: The actual value assigned to a parameter.

• Actual Parameter: Can be literals, variables, or expressions.

• Formal Parameter: Declared in a procedure header.

Conclusion

• By the end of this lesson, you should understand:

  • The role of parameters in procedures.

  • How to create and invoke procedures using parameters.

  • Differentiate between formal and actual parameters.