75%(4)3 out of 4 people found this document helpful
This preview shows page 1 - 4 out of 42 pages.
Lemuel TakuITD 134Class #: 15Homework Chapter 4Review Questions1 – D2 – C3 – Here is the symbol -- 4 – A5 – User-defined exception6 – A7 – Here is the symbol /* */8 – B9 – B10 – A11 – PL/SQL createsimplicit cursorand manages automatically means implicit open & close takes place. It used when sql statement return only one row. It has 4 attributes SQL%ROWCOUNT, SQL%FOUND, SQL%NOTFOUND, SQL%ISOPEN.an implicit cursor is implanted directly in a code block:...BEGINSELECT columns INTO variables FROM table where condition;END;...Implicit cursors require anonymous buffer memory.PL/SQL issues an implicit cursor whenever you execute a SQL statement directly in your code, as long as that code does not employ an explicit cursor. It is called an "implicit" cursor because you, thedeveloper, do not explicitly declare a cursor for the SQL statement.
EXPLICIT: It is created & managed by the programmer. It needs every timeexplicitDeclare, open, fetch &close.An explicit cursor is defined as such in a declaration block:DECLARE CURSOR cur IS SELECT columns FROM table WHERE condition;BEGIN...Explicit cursors can be executed again and again by using their name. They are stored in user defined memory space rather than being stored in an anonymous buffer memory and hence can be easily accessed afterwards.With explicit cursors, you have complete control over how to access information in the database. You decide when to OPEN the cursor, when to FETCH records from the cursor (and therefore from the table or tables in the SELECT statement of the cursor) how many records to fetch, and when to CLOSE the cursor. Information about the current state of your cursor is available through examination of the cursor attributes.An explicit cursor is a SELECT statement that is explicitly defined in the declaration section of your code and, in the process, assigned a name. There is no such thing as an explicit cursor for UPDATE, DELETE, and INSERT statements.12 Cursor for loop helps us avoid cursor errors in our programs. It simplifies coding because it handles manyexplicit cursor actions automatically such as creating a record variable, opening a cursor, looping throughone row at a time until the last row is retrieved from the cursor and closing the cursor. A cursor for loop references a record variable created automatically based on the query in the cursor.
Here is an example on how cursor for loop makes cursor processing easierPL/SQL procedure successfully completed.SQL> --Looping through a Cursor by Using a CURSOR FOR LoopSQL>SQL> declare2 cursor c_emp (ci_deptNo NUMBER) is3 select * from employee where id = ci_deptNo;4 begin5 for r_emp in c_emp(10) loop6 update employee7 set salary=salary*1.58 where id = r_emp.id;9 DBMS_OUTPUT.put_line('Emp '|| r_emp.first_Name||' - salary change:'||r_emp.salary||'-->'||r_emp.salary*1.5);10 end loop;11 end;12 /13 – An exception handler is a mechanism for trapping an error that occurs in processing. Its code handles theerror in a user friendly manner and allows the application to continue.