Oracle PL/SQL Subprogram Conditionals and Variables

This article is excerpted from chapter 36 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).


Stored procedures can both accept input parameters and return parameters back to the caller. However, for each parameter, you need to declare the name and the datatype and whether it will be used to pass information into the procedure, pass information back out of the procedure, or perform both duties.


Note  Although stored functions can accept both input and output parameters in the parameter list, they only support input parameters and must return one and only one value if referenced from a SELECT statement. Therefore, when declaring input parameters for stored functions, be sure to include just the name and type if you are only going to reference the stored functions from SELECT statements. Oracle best practices discourages the use of function parameters returning values to the calling program; if you must return more than one value from a subprogram, a stored procedure is more suitable.


Perhaps not surprisingly, the datatypes supported as parameters or return values for stored procedures correspond to those supported by Oracle, plus a few specific to PL/SQL. Therefore, you’re free to declare a parameter to be of any datatype you might use when creating a table.

To declare a parameter’s purpose, use one of the following three keywords:

  1. IN : These parameters are intended solely to pass information into a procedure. You cannot modify these values within the procedure. 
     
  2. OUT : These parameters are intended solely to pass information back out of a procedure. You cannot pass a constant for a parameter defined as OUT
     
  3. IN OUT : These parameters can pass information into a procedure, have its value changed, and then be referenced again from outside of the procedure.

Consider the following example to demonstrate the use of IN and OUT . First, create a stored procedure called RAISE_SALARY that accepts an employee ID and a salary increase amount and returns the employee name to confirm the salary increase:

create PROCEDURE raise_salar y
  (emp_id IN NUMBER, amount IN NUMBER, emp_name OUT VARCHAR2) AS
BEGIN
   UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id;
   SELECT last_name INTO emp_name FROM employees WHERE employee_id = emp_id;
END raise_salary;

Next, use an anonymous PL/SQL block to increase the salary of employee number 105 by $200 per month:

DECLARE
  emp_num      NUMBER(6) := 105;
  sal_inc      NUMBER(6) := 200;
  emp_last     VARCHAR2(25);
BEGIN
  raise_salary(emp_num, sal_inc, emp_last);
  DBMS_OUTPUT.PUT_LINE(‘Salary has been updated for: ‘ || emp_last);
END;

The results are as follows:

——————————————–
Salary has been updated for: Austin

Statement processed.
——————————————–

{mospagebreak title=Declaring and Setting Variables in Oracle Subprograms}

Local variables are often required to serve as temporary placeholders when carrying out tasks within a subprogram. This section shows you how to both declare variables and assign values to variables. 

Declaring Variables

Unlike PHP, you must declare local variables within a subprogram before using them, specifying their type by using one of Oracle’s supported datatypes. Variable declaration is achieved with the DECLARE section of the PL/SQL subprogram or anonymous block, and its syntax looks like this:

DECLARE variable_name1type [:= value]; 
        variable_name2type
[:= value];
        . . .

Here is a declaration section for a procedure that initializes some values for the area of a circle:

DECLARE
   pi       REAL := 3.141592654;
   radius   REAL := 2.5;
   area     REAL := pi * radius**2;
BEGIN
   . . .

There are a few things to note about this example. Variable declarations can refer to other variables already defined. In the previous example, the variable area is initialized to the area of a circle with a radius of 2.5. Note also the datatype REAL ; it is one of PL/SQL’s internal datatypes not available for Oracle table columns but is provided as a floating-point datatype within PL/SQL to improve the performance of PL/SQL subprograms that require many high-precision floating point calculations.

Also note that by default any declared variable can be changed within the procedure. If you don’t want the application to change the value of pi , you can add the CONSTANT keyword as follows:

pi     CONSTANT REAL := 3.141592654;

Setting Variables

You use the := operator to set the value of a declared subprogram variable. Its syntax looks like this:

variable_name := value;

Here are a couple of examples of assigning values in the body of the subprogram:

BEGIN
  
radius := 7.7;
  
area := pi * radius**2;
  
dbms_output.put_line
     
(‘Area of circle with radius: ‘ || radius || ‘ is: ‘ || area);

It’s also possible to set variables from table columns using a SELECT INTO statement. The syntax is identical to a SELECT statement you might run in SQL Commands or SQL*Plus but with the addition of the INTO variable_name  clause to specify which PL/SQL variable will contain the table column’s value. We use this construct to retrieve the employee’s last name in the raise_salary procedure created earlier in the chapter:

SELECT last_name INTO emp_name FROM employees WHERE employee_id = emp_id;

{mospagebreak title=PL/SQL Constructs}

Single-statement subprograms are quite useful, but the real power lies in a subprogram’s ability to encapsulate and execute several statements, including conditional logic and iteration. In the following sections, we touch on the most important constructs.

Conditionals

Basing task execution on run-time information (e.g., from user input) is key for wielding tight control over the results of the task execution. Subprogram syntax offers two well-known constructs for performing conditional evaluation: the
IF-THEN-[ELSIF][-ELSE]-END IF statement and the CASE statement. Both are introduced in this section.

IF-THEN-[ELSIF][-ELSE]-END IF

The IF-THEN-[ELSIF][-ELSE]-END IF statement is one of the most common means for evaluating conditional statements. In fact, even if you’re a novice programmer, you’ve likely already used it on numerous occasions. Therefore, this introduction should be quite familiar. The prototype looks like this:

IF condition THEN statement_list
  
[ELSIF condition THEN statement_list] . . .
  
[ELSE statement_list]
END IF


Caution  The keyword for specifying alternate condition testing in an IF . . . END IF statement is ELSIF . In many other programming languages it might be ELSE IF or ELSEIF , but in PL/SQL it’s one word: ELSIF .


For example, let’s say you want to adjust employee’s bonuses in proportion to their sales. Your conditional logic would look somewhat like the following:

IF sales > 50000 THEN
   bonus := 1500;
ELSIF sales > 35000 THEN
   bonus := 500;
ELSE
  
bonus := 100;
END IF;
UPDATE employees SET salary = salary + bonus WHERE employee_id = emp_id;

For employees who are not in the sales department ( sales = 0 ) or whose sales are $35,000 or less, the conditional logic assigns a bonus of $100.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan