Chapter 4 Assignment - Lemuel Taku ITD 134 Class 15 Homework Chapter 4 Review Questions 1D 2C 3 Here is the symbol-4A 5 User-defined exception 6A 7 Here

Chapter 4 Assignment - Lemuel Taku ITD 134 Class 15...

This preview shows page 1 - 4 out of 42 pages.

Lemuel Taku ITD 134 Class #: 15 Homework Chapter 4 Review Questions 1 – D 2 – C 3 – Here is the symbol -- 4 – A 5 – User-defined exception 6 – A 7 – Here is the symbol /* */ 8 – B 9 – B 10 – A 11 – PL/SQL creates implicit cursor and 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: ... BEGIN SELECT 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, the developer, do not explicitly declare a cursor for the SQL statement.
Image of page 1
EXPLICIT : It is created & managed by the programmer. It needs every time explicit Declare, 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 many explicit cursor actions automatically such as creating a record variable, opening a cursor, looping through one 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.
Image of page 2
Here is an example on how cursor for loop makes cursor processing easier PL/SQL procedure successfully completed. SQL> --Looping through a Cursor by Using a CURSOR FOR Loop SQL> SQL> declare 2 cursor c_emp (ci_deptNo NUMBER) is 3 select * from employee where id = ci_deptNo; 4 begin 5 for r_emp in c_emp(10) loop 6 update employee 7 set salary=salary*1.5 8 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 the error in a user friendly manner and allows the application to continue.
Image of page 3
Image of page 4

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture