Constants and variables in pl sql, PL-SQL Programming

Constants and Variables:

 You can declare the constants and variables in the declarative section of any PL/SQL subprogram, block, or package. The Declarations allot the storage space for a value that specify its datatype, and name the storage space location so that you can reference it. The Declarations can also assign an initial value and enforce the NOT NULL constraint.

1070_constant variable.png

Keyword and Parameter Description:

constant_name:

These identify the program constant.

CONSTANT:

These keywords indicate the declaration of a constant. You should initialize a constant in its declaration. The value of a constant cannot be changed if once it is initialized.

record_name.field_name:

These identify the field in a user-defined or %ROWTYPE record formerly declared within the present scope.

scalar_type_name:

These identify a predefined scalar datatype like the BOOLEAN, NUMBER, or VARCHAR2.

db_table_name.column_name:

These identify a database table and column that should be available when the declaration is elaborated.

variable_name:

These identify the program variable.

collection_name:

These identify the nested table, index-by table, or varray earlier declared within the present scope.

cursor_name:

These identify an explicit cursor formerly declared within the present scope.

cursor_variable_name:

These identify a PL/SQL cursor variable formerly declared within the present scope.

object_name:

These identify an object (or instance of the object type) formerly declared within the present scope.

record_name:

These identify a user-defined record formerly declared within the present scope.

db_table_name:

These identify a database table (or view) that should be available when the declaration is elaborated.

%ROWTYPE:

This attribute gives a record type that presents a row in the database table or a row fetched from a formerly declared cursor. The Fields in the record and corresponding columns in the row have similar names and datatypes.

%TYPE:

This attribute gives the datatype of a formerly declared collection, field, cursor variable, object, record, database column, or variable.

NOT NULL:

These constraints prevent the assigning of the nulls to a variable or constant. At run time, trying to assign the null to a variable defined as NOT NULL raises the predefined exception VALUE_ERROR. The constraint NOT NULL should be followed by an initialization clause.

Expression:

This is a randomly complex combination of the variables, literals, constants, operators, and function calls. The easiest expression consists of a single variable. If the declaration is elaborated, the value of the expression is assigned to the constant or variable. The value and the constant or variable should have compatible datatypes.

Posted Date: 10/6/2012 9:10:04 AM | Location : United States







Related Discussions:- Constants and variables in pl sql, Assignment Help, Ask Question on Constants and variables in pl sql, Get Answer, Expert's Help, Constants and variables in pl sql Discussions

Write discussion on Constants and variables in pl sql
Your posts are moderated
Related Questions
Using the BULK COLLECT Clause The keywords BULK COLLECT specify the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these ke

Referencing Records Unlike the elements in a collection, that are accessed using subscripts, the fields in a record are accessed by name. To reference an individual field, you

Tables within a Table - SQL Figure here is an exact copy of the one in the theory book and as before it is just an alternative way of representing some of the information con

Forward Declarations The PL/SQL needs that you declare an identifier before using it. And hence, you should declare a subprogram before calling it. For illustration, the decla

DELETE Statement The DELETE statement eliminates whole rows of data from the specified table or view. Syntax:

Create the four tables and populate them with the given data. Answer the following queries in SQL. 1. Get all part-color/part-city combinations. Note: Here and subsequently, the

Updating Objects: To change the attributes of objects in an object table, you can use the UPDATE statement, as the illustration below shows: BEGIN UPDATE persons p SET p

Iterative Control: LOOP  Statements The LOOP statement executes a series of statements multiple times. There are 3 forms of LOOP statements: LOOP, WHILE-LOOP, & FOR-LOOP. LOOP

Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

Example of Check Constraints Example: Workaround for when subqueries not permitted in CHECK constraints CREATE FUNCTION NO_MORE_THAN_20000_ENROLMENTS ( ) RETURNS BOOLEAN