In my previous articles i have started giving you examples of anonymous block with examples. I also gave information about difference between SQL and tsql in detail. We can check the implicit cursor in PL SQL with real example. There is always question in interview that is difference between implicit cursor and explicit cursor. In this article i would like to give you different Implicit cursor examples in detail.
The article contains :
What is mean by cursor?
Types of Cursor
Implicit cursor examples
What is cursor?
A cursor is nothing but the pointer to private memory area allocated by oracle server. The key use of the cursor is it is used to handle and process the select records in detail.
There are following two types of cursor :
Implicit Cursor :
The implicit cursor is a cursor which has been created and managed by Oracle server internally.
The select statements are always processed internally using the implicit cursor.
When you fire any select statement the internal implicit cursor will be fired in the background.
Explicit Cursor :
The explicit cursor will be defined explicitly by the programmer.
What are different attributes of implicit cursor ?
The cursor attributes are used to test the outcomes of SQL statements.
SQL % FOUND :
The %FOUND attribute is important attribute which is nothing but the boolean value attribute.The boolean return will come or return value as true if at least one statement of SQL will return the value.
SQL % NOT FOUND :
The % NOT FOUND attribute is important attribute which is nothing but the boolean value attribute.The boolean return will come or return value as true if at least NO statement of SQL will return the value.
SQL % ROWCOUNT :
The %ROWCOUNT attribute is one of the SQL attribute which will count the number of rows return by SQL statement. The internal
Real life industry example :
Write a block to retrieve the Salary which will internally execute the SQL statements.
–write a block that retrieve the salary for employee 100 in variable v_sal
–raise the salary 100 in variable v_new_sal
–update the employee 100 with this new salary
–insert new department called test with ID=1
DECLARE
v_sal hr.employees.salary%type;
v_new_sal hr.employees.salary%type;
BEGIN
select salary
into v_sal
from hr.employees
where employee_id=100;
dbms_output.put_line(‘the old salary is ‘||v_sal);
v_new_sal:=v_sal+100;
update hr.employees
set salary=v_new_sal
where employee_id=100;
dbms_output.put_line(‘the new salary is ‘||v_new_sal);
insert into hr.DEPARTMENTS (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)
values (1,’test’,null,null);
commit;
END;
I hope you like this article of implicit cursor with real life examples. If you like this article or if you have any issues with the same kindly comment in comments section.