Tuesday, 22 May 2012

How does a PL/SQL block integrate with data dictionary? | PL SQL

The %ROWTYPE and %TYPE attributes are used to integrate a PL/SQL block with the data dictionary. These attributes provide data independence because if the definition of the database columns changes, the corresponding variable declaration also changes automatically.
The %TYPE attribute is used to declare variables based on the definition of the database columns.
The following code snippet shows the implementation of the %TYPE attribute:
 DECLARE
 var_emp_deptno
 t_employee.emp_deptno%TYPE;
 .....
 BEGIN
 IF var_emp_deptno = 10 THEN_
The %ROWTYPE attribute is used to declare a record type, which represents a row in a table or a view. The following code snippet shows the implementation of the %ROWTYPE attribute:
 DECLARE
 emp_rec t_employee%ROWTYPE;
 .....
 BEGIN
 SELECT * INTO emp_rec FROM t_employee WHERE ....
In the preceding code, the column values, which are returned by the SELECT statement, will be stored in the fields. You can also use the dot notation to  reference any particular field;  for example, you can reference the dept_no field as follows:
 IF emp_rec.dept_no = 20 THEN ....

No comments: