PLSQL Cursor is a name given to an area and can handle the memory used by oracle for processing the SQL statements. For instance, it is used to fetch a bunch of rows from the order table or update the year & revenue, and expense information in the DWS accounting tables. PLSQL Cursor is a fundamental and very important construct in PL/SQL. While issuing the select, update, insert or delete commands in SQL code, oracle creates an implicit cursor or an area in the memory to parse and execute the query and hold the results. PLSQL Cursor is classified into two types, such as:

Whenever an SQL statement gets executed, oracle automatically creates the implicit cursors. The developer cannot handle the implicit cursor if there is no explicit cursor associated with it. For example, consider department table consisting of two columns- dept_id and dept_name. And, employee table consists of emp_name, emp_id, emp_location emp_name_id, and emp_sal columns. The emp_dept_id in employee table has been assigned the constraint of foreign key to the dept_id column of the department table. PLSQL Cursor implicit is open for issuing the select, insert or delete statements.

Oracle opens the cursors for fetching rows and automatically closes it. In the programming code, issue select statement is used to get dept_id and dept_name from the department table. And, fetch the I_dept_id and I_dept_name into the syntax of PL/SQL, which is different from the sql statement. Here, it needs the number and type of variables to be declared to fetch the select column in the sql query. For instance, I_dept_id is used to fetch the dept_id and I_dept_name is used to fetch the dept_name from the select statements. If the number and type of variables differ from the select column, oracle gives an error. SQL%FOUND is a built-in implicit cursor attribute and returns true if the select statement has fetched a row like updating or deleting a row. It returns false, if no such statement is found.

SQL@OPEN

 SQL@OPEN consequently closes the SQL cursor after executing the SQL statement and returns false for implicit cursors. SQL%NOTFOUND is opposite of SQL%FOUND. SQL%ROWCOUNT will give the number of rows fetched, updated or deleted.

The below example describes the implicit cursor: [c]SQL> DECLARE var_rows number(5); 2 BEGIN 3 UPDATE employee 4 SET salary =salary+1000; 5 IF SQL%NOTFOUND THEN 6 dbms_output.put_line('None of the salaries where updated'); 7 ELSE SQL%FOUND THEN 8 var_rows:=SQL%ROWCOUNT; 9 dbms_output.put_line('salaries for'||var_rows||'employees are updated'); 10 END IF; 11 END; 12 / PL/SQL Successfully executed.[/c] In the above example, the emp table containing the employee salaries will be updated. If not, it will display a message 'no salary has been updated'.

The cursor is defined in the declaration block of PL/SQL. It is designed on select statement and gives more than one row. For a cursor, one can assign a suitable name.

The basic syntax is the keyword cursor followed by the cursor_name and then the select statement. In the declaration block, two variables I_dept_name and I_dept_id are to be declared to hold the dept id and dept name values. And, then declare a cursor cur_get_department to select the dept_name and dept_id from the department where dept_id is equal to 1. Open a PLSQL Cursor in the execution section using the name. This will assign an area in memory for the cursor, parse and execute the query to get the desired output and then issue the fetch to get the first row from the result set. The number and type of variables should match the select columns. For instance, I_dept_id should match the type of dept_id and the I_dept_name should match the type of dept_name. If there are two columns in the select statement, there should be only two variables to fetch, otherwise oracle gives an error and then close the cursor. One advantage of using this cursor is that even if there is no data found, it won't show up any error. And, oracle gives cursor attributes in order to handle it. For example, fetching a single row from the department table. If we fetch more than one row like fetching the emp_id and emp_sal, it can be manipulated by 0.10. It declares a record based on the cursor column and can be accessed using dot notations. If the cursor is said to be true, it will fetch the statements and returns at least one row. It doesn't return a statement, if it is said to be false. If the cursor is said to be true, it will fetch the statements and doesn't return a row. It returns at least one row, if it said to be false. In the program, if the cursor is already open, it returns true. Otherwise it returns false, if the cursor is not opened in the program.

The below example explains the explicit cursor: [c]SQL> DECLARE 2 emp_rec emp_tbl%rowtype; 3 CURSOR emp_cur IS 4 SELECT * FROM WHERE salary>10; 5 BEGIN 6 OPEN emp_cur; 7 OPEN emp_cur; 8 FETCH emp_cur INTO emp_rec; 9 dbms_output.put_line(emp_rec.first_name||' '|| emp_rec.last_name); 10 CLOSE emp_cur; 11 END; 12 / PL/SQL program successfully executed[/c] In the above example, emp_rec has created the emp_tbl and a record with the cursor by altering the table name with the cursor name. Declare the cursor emp_cur from the select statement. And, open the cursor to execute statements and then fetch the cursor record. After fetching, it will close the record and the cursor.

The cursor FOR loop is a very efficient way of fetching the data from cursors. The cursor For loop will get terminated when every record in the cursor has been fetched.

The below example illustrates the cursor for loop statement. [c]SQL> DECLARE 2 CURSOR cur_get_employee IS 3 SELECT emp_id,emp_sal*0.01 bonus from employee; 4 BEGIN 5 FOR cur_get_employees_varINcur_get_employees LOOP 6 DBMS_OUTPUT.PUT_LINE(cur_get_employees_var.emp_id); 7 DBMS_OUTPUT.PUT_LINE(cur_get_employees_var.bonous); 8 END LOOP; 9 END; 10 / PL/SQL successfully executed.[/c] In the above example, all the employees in the table will get bonus of 0.01% based on their emp_sal and will be added in emp_id's.