Home arrow Oracle arrow Oracle PL/SQL Subprogram Conditionals and Variables

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).

  1. Oracle PL/SQL Subprogram Conditionals and Variables
  2. Declaring and Setting Variables in Oracle Subprograms
  3. PL/SQL Constructs
By: Apress Publishing
Rating: starstarstarstarstar / 1
March 22, 2011

print this article



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 aSELECTstatement. 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 fromSELECTstatements. 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 asOUT
  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 ofINandOUT. First, create a stored procedure calledRAISE_SALARYthat accepts an employee ID and a salary increase amount and returns the employee name to confirm the salary increase:

create PROCEDURE raise_salary
  (emp_id IN NUMBER, amount IN NUMBER, emp_name OUT VARCHAR2) AS
   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:

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

The results are as follows:

Salary has been updated for: Austin

Statement processed.

>>> More Oracle Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: