Page 43 of 95
12. What are the cursor attributes used in PL/SQL ?
%ISOPEN - to check whether cursor is open or not
% ROWCOUNT - number of rows featched/updated/deleted.
% FOUND - to check whether cursor has fetched any row. True if rows
are featched.
% NOT FOUND - to check whether cursor has featched any row. True
if no rows are featched.
These attributes are proceded with SQL for Implict Cursors and with
Cursor name for Explict Cursors.
13. What is a cursor for loop ?
Cursor for loop implicitly declares %ROWTYPE as loop index,opens a
cursor, fetches rows of values from active set into fields in the
record and closes
when all the records have been processed.
eg. FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal;
END LOOP;
14. What will happen after commit statement ?
Cursor C1 is
Select empno,
ename from emp;
Begin
open C1; loop
Fetch C1 into
eno.ename;
Exit When
C1 %notfound;-----
commit;
end loop;
end;
The cursor having query as SELECT .... FOR UPDATE gets closed
after COMMIT/ROLLBACK.
The cursor having query as SELECT.... does not get closed even
after COMMIT/ROLLBACK.
15. Explain the usage of WHERE CURRENT OF clause in cursors ?
WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the
latest row fetched from a cursor.
Database Triggers
16. What is a database trigger ? Name some usages of database trigger ?
Database trigger is stored PL/SQL program unit associated with a
specific database table. Usages are Audit data modificateions,
Log events transparently, Enforce complex business rules Derive
column values automatically, Implement complex security
authorizations. Maintain replicate tables.
|
|