InterviewSolution
Saved Bookmarks
InterviewSolution
| 1. |
How to create triggers in PL/SQL? |
|
Answer» ACCESS one row at a time when a cursor is fetched in PL/SQL. The following is when you FETCH a cursor which is already opened. Here, d_deptbudget is the cursor name: FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;Let us first see how to create a cursor, open and fetch the cursor. After that we will close it. This is all part of Explicit cursors since they are custom defined i.e. programmer defined cursors. The following is the syntax to create explicit cursor: CURSOR name_of_cursor IS select_statement;Here, As you can see above, an explicit cursor is created on a SELECT statement. Let us now see how to work with Explicit Cursors, Let us declare a cursor and set a cursor name with the SELECT statement: CURSOR d_deptbudget IS SELECT deptid, DEPTNAME, deptloc, budget FROM deptbudget;Above, “d_deptbudget” is our cursor. The memory gets allocated for the cursor when it is opened in PL/SQL. Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it OPEN d_deptbudget;Access one row at a time when a cursor is fetched in PL/SQL. The following is when you fetch a cursor which is already opened: FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;When a cursor is closed, then the allocate memory is released. To close the above created cursor: CLOSE d_deptbudget;Let us now see an example: DECLARE d_deptid deptbudget.deptid%type; d_deptname deptbudget.deptname%type; CURSOR d_deptbudget IS SELECT deptid, deptname FROM deptbudget; BEGIN OPEN d_deptbudget; LOOP FETCH d_deptbudget into d_deptid, d_deptname; EXIT WHEN d_deptbudget%notfound; dbms_output.put_line(d_deptid || ' ' || d_deptname); END LOOP; CLOSE d_deptbudget; END; /The output display department id and name: 1 Finance 2 Marketing 3 Operations 4 Technical 5 ACCOUNTING |
|