Page 42 of 95
Some composite data types such as RECORD & TABLE.
6. What are % TYPE and % ROWTYPE ? What are the advantages of using
these over datatypes?
% TYPE provides the data type of a variable or a database column to
that variable.
% ROWTYPE provides the record type that represents a entire row of a
table or view or columns selected in the cursor.
The advantages are : I. Need not know about variable's data type
ii. If the database definition of a column in a table changes, the
data type of a variable changes accordingly.
7. What is difference between % ROWTYPE and TYPE RECORD ?
% ROWTYPE is to be used whenever query returns a entire row of a
table or view.
TYPE rec RECORD is to be used whenever query returns columns of
different
table or views and variables.
E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename
%type
);
e_rec emp% ROWTYPE
cursor c1 is select empno,deptno from emp;
e_rec c1 %ROWTYPE.
8. What is PL/SQL table ?
Objects of type TABLE are called "PL/SQL tables", which are
modelled as (but not the same as) database tables, PL/SQL tables use
a primary PL/SQL tables can have one column and a primary key.
Cursors
9. What is a cursor ? Why Cursor is required ?
Cursor is a named private SQL area from where information can be
accessed. Cursors are required to process rows individually for
queries returning multiple rows.
10. Explain the two type of Cursors ?
There are two types of cursors, Implict Cursor and Explicit Cursor.
PL/SQL uses Implict Cursors for queries.
User defined cursors are called Explicit Cursors. They can be declared
and used.
11. What are the PL/SQL Statements used in cursor processing ?
DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name
INTO or Record types, CLOSE cursor name.
|
|