Home arrow Oracle arrow Page 2 - Oracle PL/SQL Subprogram Conditionals and Variables

Declaring and Setting Variables in Oracle Subprograms - Oracle

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



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 theDECLAREsection of the PL/SQL subprogram or anonymous block, and its syntax looks like this:

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

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

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

There are a few things to note about this example. Variable declarations can refer to other variables already defined. In the previous example, the variableareais initialized to the area of a circle with a radius of 2.5. Note also the datatypeREAL; 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 ofpi, you can add theCONSTANTkeyword 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:

radius := 7.7;
area := pi * radius**2;
('Area of circle with radius: ' || radius || ' is: ' || area);

It’s also possible to set variables from table columns using aSELECT INTOstatement. The syntax is identical to aSELECTstatement you might run in SQL Commands or SQL*Plus but with the addition of theINTO 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 theraise_salaryprocedure created earlier in the chapter:

SELECT last_name INTO emp_name FROM employees WHERE employee_id = emp_id;

>>> 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: