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
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

UNION ALL - SQL Further varieties of UNION arise when we replace the key word DISTINCT by ALL in any of the foregoing examples, as in Example. ALL specifies that if row r appe

Declarations in SQL Your program stores values in the variables and constants. As the program executes, the value of the variables can change, but the values constants cannot.

Question: a) Given the following relation: Location(loc_id, bldg_code, room, capacity) The underlined field is a primary key. (i) Write a PL/SQL program using the impl

Case Sensitivity Similar to all the identifiers, the variables, the names of constants, and parameters are not case sensitive. For illustration, PL/SQL considers the following n

OPEN-FOR Statement The OPEN-FOR statements execute the multi-row query related with a cursor variable. It also allocates the resources used by the Oracle to process the query a

I have a Pascal Source file that needs to be compiled into a Service. In addition, there are various functions (Pascal Procedures I guess) that need to be created to Read and Write

Write a cursor to open an employee database and fetch the employee record whose age is greater than 45

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr

Natural Join - SQL In the absence of NATURAL JOIN Example has to be replaced by something rather more longwinded, as shown in Example. Example: Joining IS_CALLED and IS_EN