Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Sunday, 3 April 2016

PL/SQL Variables

Declaration Syntax
          identifier [CONSTANT] datatype [NOT NULL] [:= expr | DEFAULT expr]
          Note: Square brace indicates optional  
Valid variable declarations                                      
DECLARE
v_hiredate DATE;
v_deptno NUMBER(2) NOT NULL := 10;
v_location VARCHAR2(13) := ’Atlanta’;
c_comm CONSTANT NUMBER := 1400;
v_NoOfSeats  NUMBER DEFAULT 45;
v_FirstName VARCHAR2(20) DEFAULT ‘SCOTT’
  
Invalid variable Declarations
          v_deptno number(2) NOT NULL;
          v_name varchar2 DEFAULT ‘Sachin’;

Inheriting data type
          You can declare variables to inherit the data type of a database column or other variable
          v_empno emp.empno%TYPE ;

Constants:using the CONSTANT keyword we can declare a constant
          c_max_size CONSTANT NUMBER := 100;

Bind Variable:a variable declared in a host environment and used in many blocks by referencing it with “:” prefix

Row Type Variable:holds one record/row at a time
          e.g. v_record emp%ROWTYPE;

PL/SQL Record type Variable:A record is a group of related data items stored in fields, each with its own name and data type.

A record containing a field for each item lets you treat the data as a logical unit.

The variable based on a PL/SQL record type is a composite data member having fields as defined in the corresponding record type 
 
Syntax:
          TYPE type_name IS RECORD (field_declaration[,field_declaration]...);
       variable_name type_name;
Example:
                   -- Type declaration
      TYPE DeptRec IS RECORD (                     
                             dept_id dept.deptno%TYPE,
                             dept_name VARCHAR2(14),
                             dept_loc VARCHAR2(13)
                                         );
            -- Record type variable declaration
        vDeptRec DeptRec;     
Guidelines for Declaring PL/SQL Variables 
Ø Follow the naming conventions 
Ø Initialize variables designated as NOT NULL and CONSTANT 
Ø Declare one identifier per line 
Ø Initialize identifiers by using the assignment operator (:=) or the reserved word “DEFAULT”
Share this article :

0 comments:

Post a Comment