PL/SQL Tables

PL/SQL tables are the temporary memory objects that give you access to the data like array. PL/SQL tables can have only one column and one primary key. The data type of the column can be any scalar type and data type of the primary key column is BINARY_INTEGER. The size of the PL/SQL tables is unconstrained, which means the number of rows in a PL/SQL table can increase dynamically.

Declaring PL/SQL Tables
You can declare a PL/SQL table in the declarative part of the PL/SQL block, subprogram, or package. PL/SQL tables are declared in two steps: first, you need to define the TABLE type and then declare the PL/SQL table of that TABLE type.

The following syntax is used to declare a table type:

TYPE type_name IS TABLE OF
{Column_definition}
INDEX BY BINARY_INTEGER;

Here type_name is the name of the TABLE type and Column_definition contains the declaration of the field name, field type, and constraints.


After defining the TABLE type, you can declare the PL/SQL table. The syntax for declaring a PL/SQL table is as follows:

Table_name Table_typename;

In the above syntax, Table_name is the name of the PL/SQL table and Table_typename is the name of the TABLE type which you have defined. The following example shows how to declare a PL/SQL table:

DECLARE
TYPE Tbl_Emp IS TABLE OF Emp.Ename%TYPE
INDEX BY BINARY_INTEGER;

In the above example, Tbl_Emp is a table data type that stores the name of the employees. After defining the table type Tbl_Emp, you can declare PL/SQL tables of that type in the following way:

Tbl_Emp Emp_Name;


Referring PL/SQL Tables
In PL/SQL, the elements of PL/SQL tables can be referred by specifying an index number. The syntax to refer a PL/SQL table is as follows:

plsql_tablename(index)

In the above syntax, plsql_tablename is the name of the PL/SQL table and the index is an expression that returns the BINARY_INTEGER data type.

The following example will illustrate the use of PL/SQL tables.


  DECLARE
  TYPE Emp_name IS TABLE OF Emp.Ename%TYPE
  INDEX BY BINARY_INTEGER;
  TYPE Emp_Sal IS TABLE OF Emp.Sal%TYPE
  INDEX BY BINARY_INTEGER;
  E_name Emp_name;
  E_Sal Emp_Sal;

  Ctrl_index BINARY_INTEGER := 0;
  BEGIN
    FOR EnameRec IN (SELECT * FROM Emp) LOOP
        Ctrl_index := Ctrl_index + 1;
        E_name(Ctrl_index) := EnameRec.Ename;
        E_Sal(Ctrl_index) := EnameRec.Sal;
    END LOOP;
    FOR i IN 1..Ctrl_index LOOP
        DBMS_OUTPUT.PUT_LINE(‘Name: ’ || E_name(i));
        DBMS_OUTPUT.PUT_LINE(‘Salary: ’ || E_Sal(i));
        DBMS_OUTPUT.PUT_LINE(‘-----------------------------------------’);
    END LOOP;
  END;
/

Triggers for Multiple Operations

The following steps are required to create a trigger that can be used to perform multiple
operations:

1. Enter the following statements in SQL*Plus to create the tables Employee and Employee_Audit.


• CREATE TABLE Employee
  (
  ID VARCHAR2(4) NOT NULL,
  First_Name VARCHAR2(10),
  Last_Name VARCHAR2(10),
  Start_Date DATE,
  End_Date DATE,
  Salary NUMERIC(8,2),
  city VARCHAR2(10),
  Description VARCHAR2(10)
  );


• 
  CREATE TABLE Employee_Audit
  (
  ID VARCHAR2(4) NOT NULL,
  First_Name VARCHAR2(10),
  Last_Name VARCHAR2(10),
  Salary NUMERIC(8,2),
  Description VARCHAR2(10),
  V_User VARCHAR2(20),
  Updated_Date DATE,
  Inserted_Date DATE,
  Deleted_Date DATE
  );
2. Open an editor (Notepad), enter the following code in it, and then save the file at C:\oracle_11g\c08_oracle_11g with the file name Emp_Audit_Trig.txt. Alternatively, enter the following code directly in SQL*Plus:



• CREATE OR REPLACE TRIGGER Emp_Trig
  BEFORE UPDATE OR DELETE OR INSERT
  ON EMPLOYEE
  FOR EACH ROW
  DECLARE
  V_User VARCHAR2(20);
  N_Date DATE;
  BEGIN
  SELECT USER, SYSDATE INTO V_User, N_Date FROM DUAL;
  IF INSERTING THEN
  IF :NEW.Description = ‘Programmer’ THEN
  IF :NEW.Salary >= 2000 AND :NEW.Salary <= 3000 THEN
  
• --inserting new row into the Employee_Audit table
  
• INSERT INTO EMPLOYEE_Audit(ID, First_Name, Last_Name, Salary, Description, V_User, Inserted_Date) VALUES(:NEW.ID, :NEW.First_Name,:NEW.Last_Name, :NEW.Salary,:NEW.Description, V_User,N_Date);
  
• ELSE
  
• RAISE_APPLICATION_ERROR(-20102, ‘Salary cannot be more than 3000 or less than 2000 for Programmer’);
  
• END IF;
  
• ELSIF :NEW.Description = ‘Tester’ THEN
  
• IF :NEW.Salary >= 3000 AND :NEW.Salary <= 5000 THEN
  
• --inserting new row into the Employee_Audit table
  
• INSERT INTO EMPLOYEE_Audit(ID, First_Name, Last_Name,Salary, Description, V_User, Inserted_Date) VALUES (:NEW.ID, :NEW.First_Name, :NEW.Last_Name,:NEW.Salary,:NEW.Description, V_User,N_Date);
  
• ELSE
  
• RAISE_APPLICATION_ERROR(-20102, ‘Salary cannot be more than 5000 or less than 3000 for Tester’);
  
• END IF;
  
• ELSIF :NEW.Description = ‘Manager’ THEN
  
• IF :NEW.Salary >= 5000 AND :NEW.Salary <= 8000 THEN
  
• --inserting new row into the Employee_Audit table
  
• INSERT INTO EMPLOYEE_Audit(ID, First_Name, Last_Name,Salary, Description, V_User, Inserted_Date) VALUES (:NEW.ID, :NEW.First_Name, :NEW.Last_Name,:NEW.Salary,:NEW.Description, V_User,N_Date);
  
• ELSE
  
• RAISE_APPLICATION_ERROR(-20102, ‘Salary cannot be more than 8000 or less than 5000 for Manager’); END IF;
  
• END IF;
  
• ELSIF UPDATING THEN
  
• --inserting old row into the Employee_Audit table which is going to updated
  
• INSERT INTO EMPLOYEE_Audit (ID, First_Name, Last_Name,Salary, Description, V_User, Updated_Date) VALUES (:OLD.ID, :OLD.First_Name, :OLD.Last_Name,:OLD.Salary,:OLD.Description, V_User,N_Date);
  
• ELSIF DELETING THEN
  
• --inserting deleting row into the Employee_Audit table
  
• INSERT INTO EMPLOYEE_Audit (ID, First_Name, Last_Name,Salary, Description, V_User, Updated_Date) VALUES (:OLD.ID, :OLD.First_Name, :OLD.Last_Name,:OLD.Salary,:OLD.Description, V_User,N_Date);
  
• END IF;
  
• END; /
  

In the given code, the trigger Emp_Trig is created for performing multiple operations such as updating, inserting, or deleting a record from the Employee table. It means the trigger Emp_Trig will be fired when the UPDATE, INSERT, or DELETE statement is issued against the Employee table. This trigger is created such that whenever a user deletes, updates, or inserts data in the Employee table, the information such as employee id, name, salary, description, deleted date, insert date, update date, and user name will be stored in the Employee_Audit table.

In the DECLARE section, V_User and N_Date are declared as the VARCHAR and DATE data types, respectively, to store the user name and the current date. In the BEGIN section, the SELECT statement assigns values to the V_User and N_Date variables from the DUAL table. The statement IF INSERTING THEN evaluates to TRUE, if you issue the INSERT command against the Employee table. Similarly, the ELSIF UPDATING THEN and ELSIF DELETING THEN statements evaluate to TRUE, if you issue the UPDATE and DELETE commands against the Employee table.

As discussed earlier, the old and new column values can be accessed in the row level triggers with the correlation names: OLD and NEW.


3. Now, execute the contents of file to create the trigger Emp_Trig. To execute the contents of the file, enter the following command in SQL *Plus.

SQL> @ ‘C:\Oracle\Emp_Audit_Trig.txt’

The trigger will be created and stored in the database, and can now be executed. Once you create the trigger, it gets associated with the table on which it is created. This trigger will be fired when the UPDATE, INSERT, or DELETE statement is issued on the table, on which the trigger is created.


4. Now, insert a new row into the Employee table on which the trigger Emp_Trig is created. Enter the INSERT statement in SQL*Plus.
   

After inserting the data row in the Employee table, check the data of the Employee_Audit table. You will find that the same row is inserted into the Employee_Audit table.

Again, insert the data row into the Employee table with salary greater than 3000 and description as Programmer. While executing the INSERT statement, Oracle will throw an error with the message Salary cannot be more than 3000 or less than 2000 for Programmer. This is because, as defined in the trigger, the salary of an employee with description as programmer should not be greater than 3000 and not less than 2000.

In the same way, using triggers you can delete and update some records from the Employee table and then check their status in the Employee_Audit table. The records that are deleted as well as the rows that are updated in the Employee table will be stored in the Employee_Audit table.

Triggers

A trigger is a special class of stored procedure and is defined on tables or views. Triggers are executed automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or a View. Triggers are powerful tools that are used to enforce the database rules automatically while you modify the data.

A trigger is an SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the Oracle database, and are used to maintain the referential integrity of data by changing the data systematically. A trigger cannot be called or executed. Triggers are fired automatically by Oracle.

The database triggers are used for the following purpose:

1. To generate the resulting data automatically.
2. To enforce complex integrity constraints, security authorizations, and business rules.
3. To avoid invalid transactions.
4. To enforce referential integrity across nodes in a distributed database.
5. To provide transparent event logging and advanced auditing data modifications.
6. To maintain synchronous table replicates and check the status of the table access.

Example: Write a PL/SQL block that prompts a message to enter an employee id and also prompts a message that the employee is present.

The following steps are required to illustrate the use of attribute %TYPE with the implicit
cursor:

1. Open an editor (Notepad), enter the following code in it, and then save the file at C:\oracle_11g\c08_oracle_11g with the file name employee_Cur_Type.txt. Alternatively, enterthe following code directly in SQL * Plus.
   

DECLARE
Name Emp.Ename%TYPE;
Salary Emp.Sal%TYPE;
EmpId Emp.Empno%TYPE;
BEGIN
EmpId := &EmpId;
SELECT Ename, Sal INTO Name, Salary
FROM Emp
WHERE Empno = EmpId;
DBMS_OUTPUT.PUT_LINE (Name || ‘ having Employee id ’ || EmpId ||
‘ and salary ’ || Salary || ‘ is present’);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE (‘The employee is not in the company
or does not exist’);
END;
/

In the above PL/SQL block, Name is declared as the %TYPE attribute against the column Ename of the Emp table and Salary is declared as the %TYPE attribute against the column Sal of the Emp table. In the BEGIN section, the statement EmpId := &Empid; will prompt a message Enter value for empid and the value entered by you will be stored in the variable EmpId. The PL/SQL will execute the SELECT statement and retrieve a message that the employee having the employee number you entered is present. If the employee id you entered does not exist, Oracle will raise an exception NO_DATA_FOUND and prompt the message Employee does not exist.

1. Now, execute the contents of the file employee_Cur_Type.txt by entering the followingcommand in SQL *Plus:
   

SQL>@‘C:\oracle_11g\c08_oracle_11g\employee_ImpCur.txt’

3. When you execute the above PL/SQL block, the message Enter value for empid will be
prompted. Enter a value which exists in the Emp table such as 7900; a message will be
printed as given below:

Enter value for empid: 7900
old 6: EmpId := &EmpId;
new 6: EmpId := 7900;

JAMES having Employee id 7900 and salary 950 is present

PL/SQL procedure successfully completed.

4. Again, execute the same PL/SQL block by entering the forward slash (/) at the SQL
prompt. The message Enter value for empid will be prompted. This time enter the value of employee id that does not exist in the Emp table such as 234; the exception
NO_DATA_FOUND will be raised and a message will be printed as follows:
SQL> /
Enter value for empid: 234
old 5: EmpId := ‘&EmpId’;
new 5: EmpId := ‘234’;
The employee is not in the company or does not exist
PL/SQL procedure successfully completed.