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
Definition of FROM - SQL Recall that the operand of FROM is denoted by a commalist, each element of that commalist being a table expression optionally accompanied by a range v

Transaction Control The Oracle is transaction oriented; that is, Oracle uses the transactions to make sure the data integrity. The transaction is a sequence of SQL data manip

DBMS: The answer to this question is of course given in of the theory book. This book is concerned with SQL DBMSs and SQL databases in particular. Soon we will be looking a

Write an anonymous block that contains a PL/SQL procedure. The procedure takes two input parameter: oldZip and newZip, and it updates the zipcodes table by replacing all oldZip wit

SQL Operators The PL/SQL uses all the SQL set, comparison, and row operators in the SQL statements. This part briefly describes some of these operators.  1. Comparison Opera

Positional and Named Notation You can write the actual parameters when calling a subprogram, using either positional or named notation. That is, you can point to the relationsh

Relational Operators The relational operators permit you to compare randomly complex expressions. The list below provides the meaning of each operator:

Identifiers You use identifiers to name the PL/SQL program items and units that include constants, variables, cursors, exceptions, cursor variables, subprograms, and packages.

Some Varray Examples In SQL Plus, assume that you define an object type Project, as described below: SQL> CREATE TYPE Project AS OBJECT ( 2 project_no NUMBER(2), 3 title VARCHA

Example of WRAP Operator - SQL The effect of Example can be obtained in SQL but note that one needs to write down not only the names of the columns being wrapped but also the