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

Assignment Help:

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;


Related Discussions:- Variables and constants in pl/sql

Defining ref cursor types, Defining REF CURSOR Types To make cursor va...

Defining REF CURSOR Types To make cursor variables, you take 2 steps. At first, you define a REF CURSOR type, and then declare the cursor variables of that type. You can defin

Count operator in sql, Count Operator in SQL Example: Counting the stu...

Count Operator in SQL Example: Counting the students who have scored more than 50 in some exam (SELECT COUNT (*) FROM (SELECT DISTINCT StudentId FROM EXAM_MARK WHE

Oracle development , Write a stored procedure that accepts the post code in...

Write a stored procedure that accepts the post code in which the customer resides as the input parameter. The procedure should then use an explicit cursor to display comprehensive

Using operator ref - manipulating objects pl sql, Using Operator REF: ...

Using Operator REF: You can retrieve refs by using the operator REF that, like VALUE, takes as its argument a correlation variable. In the illustration below, you retrieve one

Overloading method in pl/sql, Overloading: Similar to packaged subprog...

Overloading: Similar to packaged subprograms, methods of the same type can be overloaded. That is, you can use similar name for various methods if their formal parameters diff

Positional notation, Positional Notation The first procedure call uses...

Positional Notation The first procedure call uses the positional notation. The PL/SQL compiler relates the first actual parameter, account, with the first proper parameter, ac

Biconditional - sql, Biconditional - SQL The biconditional p ↔ q can b...

Biconditional - SQL The biconditional p ↔ q can be expressed in Tutorial D by p = q and the same is true of SQL. The question then arises as to whether, in SQL, p = q is equiv

Keyword and parameter description - if statement, Keyword and Parameter Des...

Keyword and Parameter Description: boolean_expression: This is an expression which results the Boolean value TRUE, FALSE, & NULL. It is related with a series of statement

Data types in sql - xml, Data Types in SQL - XML, Array, Row ...

Data Types in SQL - XML, Array, Row BINARY LARGE OBJECT for arbitrarily large bit strings. XML for XML documents and fragments. ARRAY types for arrays.

Overriding default locking, Overriding Default Locking By default, the...

Overriding Default Locking By default, the Oracle locks the data structures for you automatically. Though, you can request exact data locks on rows or tables when it is to you

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd