Variables and constants in pl/sql, PL-SQL Programming

Variables and Constants in PL/SQL

The PL/SQL permits you to declare constants and variables, and then use them in SQL and procedural statements anywhere in the expression. Though, forward references are not permitted. So, you should declare a constant or variable before referencing it in another statement, involving other declarative statements.

Declaring Variables

The Variables can have any SQL datatype, like CHAR, DATE, or NUMBER, or any PL/SQL datatype, like BOOLEAN or BINARY_INTEGER. For e.g., suppose that you want to declare a variable name part_no         to hold 4-digit numbers and a variable name in_stock    to hold the Boolean value TRUE or FALSE. You can declare these variables as shown:

part_noNUMBER(4);

in_stock BOOLEAN;

You can also declare nested tables, records using the TABLE, VARRAY, variable-size arrays (varrays for short), and RECORD composite datatypes.

Assigning Values to a Variable

You can assign values to a variable in 2 ways. The first way uses the assignment operator (:=), a colon followed by an equal sign. You put the variable to the left of the operator and an expression to the right. Some examples are as follow:

tax := price * tax_rate;

bonus := current_salary * 0.10;

amount := TO_NUMBER(SUBSTR('750 dollars', 1, 3));

valid := FALSE;

The second way to assign values to a variable is to fetch or select database values into it. In the example below, you have Oracle calculate a 10% bonus when you select the salary of an employee:

SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;

After that, you can use the variable bonus in another calculation or insert its value into a database table.

Declaring Constants

Declaring a constant is such as declaring a variable except that you must add the keyword CONSTANT and right away assign a value to the constant. Subsequently, no more assignments to the constant are allowed. In the example below, you declare a constant named credit_limit:

credit_limit CONSTANT REAL := 5000.00;

Posted Date: 10/2/2012 1:13:13 AM | Location : United States







Related Discussions:- Variables and constants in pl/sql, Assignment Help, Ask Question on Variables and constants in pl/sql, Get Answer, Expert's Help, Variables and constants in pl/sql Discussions

Write discussion on Variables and constants in pl/sql
Your posts are moderated
Related Questions
%FOUND Until the SQL data manipulation statement is executed, the %FOUND yields NULL. Afterward, the %FOUND yields TRUE, when an INSERT, UPDATE, or DELETE statement affected o

Declaring a Cursor The Forward references are not allowed in the PL/SQL. Therefore, you must declare a cursor before referencing it in other statements. Whenever you declare a

Parameter and Keyword Description:   table_reference: This keyword identifies the table or view that should be accessible when you execute the UPDATE statement, and for wh

Using FIRST and LAST FIRST and LAST return the first and last (minimum and maximum) index numbers in a collection. When the collection is empty, the FIRST and LAST return NULL

TYPES OF EVOLUTION - Sequential evolution                  :                    Minor changes in the gene pool of a population from one generation to the next, with the resul

Parameter Modes   To define the behavior of formal parameters you use the parameter modes. The 3 parameter modes, IN, OUT, & IN OUT, can be used with any subprogram. Though, a

This is a Customer Management project. Customer data is presented in a text file. The program will load this text data into its DB columns. The data mapping is user definable. User

Pl/SQL Expressions The Expressions are constructed by using the operands and operators. An operand is a constant, literal, variable, or function call which contributes a value

Providing Results of Queries Expressing queries in SQL is the (big) subject. Here I present just a simple example to give you the flavour of things to come in those chapters.

THEO R Y OF SPECIAL CREATION - Life originated on the earth due to natural events by the super natural power. The biblical story of creation of world within six days was p