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
PRIMARY KEY: PRIMARY KEY  indicates that the table is subject to a key constraint, in this case declaring that no two rows in the table assigned to ENROLMENT can ever have the

CHECK Constraints in SQL A CHECK constraint is a table constraint defined using the key word CHECK, as already illustrated in several examples in this chapter. In particular,

We are seeking a freelance consultant that is familiar with Appgen applications. We require exporting all our data into a format appropriate for importing into SAP Business One. Pl

Restriction and AND - SQL Restriction is available via the WHERE operator, and so it is in SQL. However, by Example showing how a certain simple restriction can be expressed u

Selecting Objects: Suppose that you have run the SQL*Plus script below that creates object type Person and object table persons, and that you have settled the table: CREATE

Data Types in SQL SQL's concept does not differ significantly from that defined in the theory book, apart from that business concerning NULL. However, the theory book equates

Literals A literal is an explicit numeric, string, character, or Boolean value not represented by an identifier. Numeric literal 147 and the Boolean literal FALSE are some of

Parameter and Keyword Description: package_name: This construct identifies the package. AUTHID Clause: This determine whether all the packaged subprograms impleme

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illus

Using a join on 2 tables, select all columns and 10 rows from the 2 tables without the use of a Cartesian product. Query: SELECT * FROM EMPLOYEE1 E JOIN STAFF S ON E.EMP_