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:


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
Number Types The Number types permit you to store the numeric data (real numbers, integers, and floating-point numbers), show quantities, and do computations. BINARY_INTEG

Indeterminacy in SQL Some SQL expressions are actually not function invocations at all in the mathematical sense, being indeterminate-invocations operating on identical input

PL SQL Code Review HEADER ELEMENTS File Name Clear, meaningful and descriptive about main objective of the file. Multiple words are joined using underscores which adh

Procedures The procedure is a subprogram which can take parameters and be invoked. Normally, you can use a procedure to perform an action. The procedure has 2 sections: the spe

Example of GROUP BY and COLLECT Operator Example: Using GROUP BY and COLLECT to obtain C_ER2 SELECT CourseId, CAST ( COLLECT (ROW (StudentId, Mark)) AS ROW (Studen

Declaring and Initializing Objects: An object type is once defined and installed in the schema; you can use it to declare the objects in any PL/SQL, subprogram, block or packa

%ISOPEN The Oracle closes the SQL cursor automatically after executing its related SQL statement. As a result, the %ISOPEN forever yields FALSE.

SQL Functions The PL/SQL uses all the SQL functions involving the following aggregate functions that summarize the whole columns of the Oracle data: GROUPING, AVG, COUNT, STDDE

Delimiters A delimiter is a simple or compound symbol which has a special meaning to PL/SQL. For example, you use delimiters to symbolize an arithmetic operation like additio

Inner Join We have learned how to retrieve data from one table by using SELECT statement. But, as we have learned, normalized relational databases mean the data is spread betw