Follow us: Connect on YouTube 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 :

2 comments:

  1. Hello There,


    Seems like I won the lottery here….This is a treasure box of blogs and your folks are like leprechauns! Phenomenal read on PL/SQL Variables

    I need to setup auditing (or logging) for RESULTS of queries that are run by certain users against certain tables.

    Fine-grained auditing will result in one audit record with the text of the select statement. But how can I audit the actual result of a select query? It seems to me that it should be "select for each row" trigger for this that does not exist.
    For example, when they run some select statement against "patients" table I need to log all PatientID that were selected from this table. So if there were 100 records selected, there should be 100 "audit" records with patientID logged. Is this possible?


    Awesome! Thanks for putting this all in one place. Very useful!


    Thank you,
    Samson

    ReplyDelete
  2. Hello There,

    I genuinely do look forward for the time where you post some new write ups. Your blog make me feel so educated! Continue soaring and writing please.

    I am trying to count the number of rows each view has in an oracle database.

    There are over 100 views.

    I want it to display the view name and the number of rows.

    I tried

    SELECT VIEW_NAME, COUNT(*) FROM ALL_VIEWS

    But this doesn't work.

    I tried a similar query using a group by of view_name and max(rownum) but this too didn't work.

    The SQL I'm writing is generating the count of view_names or max(rownum)'s assigned to view_names. I need the count of rows for each individual view_name.

    Is there a simple way to do this?

    Once again thanks for your tutorial.

    Kind Regards,
    Preethi.

    ReplyDelete