Cursor variable -- a reference to a cursor object or query in the database.
Cursor variable is a pointer/reference to SQL work area and its value is the address of the that work area.Since it has address of SQL work area, it can accommodate multiple select statements(as shown here) returning different result sets(rows from different table).Syntax and life cycle of cursor variable:-
Cursor variable is of type REF CURSOR/ SYS_REFCURSOR. General syntax of cursor type & variable declaration and life cycle of cursor variable is as is as follows: first create type of cursor then create variable of that type termed as cursor variable followed by FETCH and close.Note:- Once cursor is closed, if try to fetch rows it will throws error ORA-01001: invalid cursor.
/* Create the cursor type.- Return type is optional */
TYPE cur_type IS REF CURSOR [RETURN table%ROWTYPE];
/* Declare a cursor variable of that type. */
cur_var cur_type;
/* Open the cursor variable, associating with it a SQL statement. */
OPEN cur_type FOR <SELECT statement>;
/* Fetch from the cursor variable. */
FETCH cur_var INTO <table_rec_type/use%ROWTYPE>;
/* Close the cursor object associated with variable. */
CLOSE cur_var;
Cursor variable and Cursor object :-
Cursor variable dynamically binds with select statement when OPEN FOR syntax is executedNote:- When a cursor variable is created in PL/SQL program unit, it is just a reference variable. Actual cursor object (the result set identified by the cursor SQL statement) is created when OPEN FOR is executed for select query and assigned to a cursor variable. See the following diagram to understand difference between cursor variable and cursor object:-
Cursor variable and cursor Object creation in PL/SQL- cursor variable is reference to cursor Object |
DECLARE /*Cursor type declaration*/ TYPE v_empcur_type IS REF CURSOR;-- RETURN EMPLOYEES%ROWTYPE; /*cursor variable declaration - no select statement binding here*/ cur_var v_empcur_type; v_deptId NUMBER(5) := 90; v_locId NUMBER(5) := 1800; v_emp_row EMPLOYEES%ROWTYPE; v_dept_row DEPARTMENTS%ROWTYPE; BEGIN /*First binding:- OPEN refcursor for a select statement */ OPEN cur_var FOR select * from employees where DEPARTMENT_ID = v_deptId; dbms_output.put_line('--First binding output--'); dbms_output.put_line('FIRST_NAME' || ' '|| 'EMAIL'); dbms_output.put_line('------------- ---------------'); LOOP FETCH cur_var INTO v_emp_row; EXIT WHEN cur_var%NOTFOUND; dbms_output.put_line(v_emp_row.FIRST_NAME || ' ' || v_emp_row.EMAIL||'@devinline.com'); END LOOP; /*Second binding :- OPEN refcursor for another select statement */ OPEN cur_var FOR select * from DEPARTMENTS where LOCATION_ID = v_locId; dbms_output.put_line('--Second binding output --'); dbms_output.put_line('DEPARTMNET_NAME' || ' '|| 'MANAGER_ID'); dbms_output.put_line('------------- ---------------'); LOOP FETCH cur_var INTO v_dept_row; EXIT WHEN cur_var%NOTFOUND; dbms_output.put_line(v_dept_row.DEPARTMENT_NAME || ' ' || v_dept_row.MANAGER_ID); END LOOP; CLOSE cur_var; END;
--First binding output--
FIRST_NAME EMAIL
------------- ---------------
Steven
SKING@devinline.com
Neena
NKOCHHAR@devinline.com
Lex
LDEHAAN@devinline.com
NIKHIL
NIKSR@devinline.com
--Second binding output --
DEPARTMNET_NAME MANAGER_ID
------------- ---------------
Marketing 201
Purchasing 114
========================================
In above program, cursor type is created followed by a cursor variable and two variable one of type employee row and another of department row type is created. In begin block, cursor is opened and associated it with a select statement. Fetch cursor in Loop and display name and email. Again same cursor is bind with another select statement and again fetch and display rows from it. Above program unit uses same cursor variable to refer two select statement one after another, it is most important and noticeable feature of cursor variable.
Question:- Why error "ORA-01001: invalid cursor " is thrown, if try to fetch rows after cursor has been closed ?
Answer:- Always remember, cursor does nor contain value, it is just a pointer to result set. Once we execute close command, pointer to result set is removed so , cursor is just a variable now and no cursor object is attached to it, so it throws error.
Note:-
- Cursor variable also has same set of attributes as explicit cursor : ISOOPEN, FOUND, NOTFOUND, ROWCOUNT. Refer this table for more detail about cursor attributes.
- Cursor variable can be of two types: Strong type and Weak type.If RETURN clause is added while creating cursor type it is termed as Strong type, however if is missing then that type is called Weak type.Read Difference between strong type and weak type. in more detail.
- We can perform assignment operations with cursor variables and also pass these variables as arguments to procedures and functions. If either of cursor variable participating in assignment operation are weak then compile time check cannot be achieved. And if there is a type mismatch or both cursor variable are not structurally same then error is reported at runtime.
If both cursor variable is strong type, compile time check is done and error is reported if both are structurally not same.--Both cursor variable are strong type,compiler time check for structure compitablity Type emp_cur_type IS REF CURSOR RETURN employees%ROWTYPE; Type dept_cur_type IS REF CURSOR RETURN departments%ROWTYPE; emp_cur_var emp_cur_type; dept_cur_var dept_cur_type; ..... /*compile time error for below assignment: PLS-00382: expression is of wrong type*/ emp_cur_var := dept_cur_var;
- A cursor object(a query/result-set) may be referenced by two cursor variable.
- NULL cannot be assigned to a cursor variable.
- Cursor variables cannot be declared in a package since they do not have a persistent state.
1. Difference between strong cursor type and weak cursor type
2. Difference between SYS_REFCURSOR and REF CURSOR
3. Difference between explicit cursor and cursor variable
4. REF CURSOR, SQL and PL/SQL interaction
Previous: PL/SQL Implicit & Explicit Cursor Next: PL/SQL program unit- Procedure and Function
This blog was making more interesting. Keep adding more information on your page.
ReplyDeleteC C++ Training in Chennai
C Training in Chennai
C++ Training in Chennai
JMeter Training in Chennai
JMeter Training Institute in Chennai
Appium Training in Chennai
javascript training in chennai
core java training in chennai
As I read the blog I felt a tug on the heartstrings. it exhibits how much effort has been put into this.
DeleteFinal Year Project Domains for CSE
Spring Training in Chennai
Project Centers in Chennai for CSE
Spring Framework Corporate TRaining
I would definitely say that this blog is really useful for me and helped me to gain a clear basic knowledge on the topic. Waiting for more updates from this blog admin.
ReplyDeleteIELTS Classes in Mumbai
IELTS Coaching in Mumbai
IELTS Mumbai
IELTS Center in Mumbai
Best IELTS Coaching in Mumbai
Spoken English Classes in Chennai
IELTS Coaching in Chennai
English Speaking Classes in Mumbai
Awesome blog with great piece of information. Very well written blog with crisp and neat content. Keep sharing more such blogs.
ReplyDeleteCloud Computing Training in Chennai
Cloud Training in Chennai
Data Science Course in Chennai
Azure courses in Chennai
VMware course
R Programming Training in Chennai
Cloud Certification in Chennai
The innovative thinking of you in this blog. This blog makes me very useful to learn.
ReplyDeleteSelenium Training in Chennai
Selenium Training Institute in Chennai
best selenium training center in chennai
Selenium Course in Chennai
Selenium Training in Tambaram
Selenium training in Guindy
Python Training in Chennai
Big data training in chennai
SEO training in chennai
JAVA Training in Chennai
I really admired your post, such great and useful information that you have mentioned here.
ReplyDeleteEthical Hacking course in Chennai
Ethical Hacking Training in Chennai
Hacking course in Chennai
ccna course in Chennai
Salesforce Training in Chennai
AngularJS Training in Chennai
PHP Training in Chennai
Ethical Hacking course in Tambaram
Ethical Hacking course in Velachery
Ethical Hacking course in T Nagar
Wonderful blog...! I appreciate your great efforts and keep doing the great work...
ReplyDeletePega Training in Chennai
Pega Developer Training
Advanced Excel Training in Chennai
Linux Training in Chennai
Power BI Training in Chennai
Tableau Training in Chennai
Job Openings in Chennai
Oracle Training in Chennai
Oracle DBA Training in Chennai
Social Media Marketing Courses in Chennai
Pega Training in Adyar
iso 27001 certification services
ReplyDeleteiso 27001 certification in delhi
ISO 9001 Certification in Noida
iso 22000 certification in Delhi
iso certification in noida
ReplyDeleteiso certification in delhi
ce certification in delhi
iso 14001 certification in delhi
iso 22000 certification cost
iso consultants in noida
we have provide the best fridge repair service.
ReplyDeleteWashing Machine Repair In Faridabad
LG Washing Machine Repair In Faridabad
Videocon Washing Machine Service Centre In Faridabad
IFB Washing Machine service centre in faridabad
Samsung Washing Machine Repair In Faridabad
Washing Machine Repair in Noida
godrej washing machine repair in noida
whirlpool Washing Machine Repair in Noida
IFB washing Machine Repair in Noida
LG Washing Machine Repair in Noida
we have provide the best ppc service.
ReplyDeleteppc company in gurgaon
website designing company in Gurgaon
PPC company in Noida
seo company in gurgaon
PPC company in Mumbai
PPC company in Chandigarh
Digital Marketing Company
Rice Bags Manufacturers
ReplyDeletePouch Manufacturers
fertilizer bag manufacturers
Lyrics with music