Cursor Query
--set SERVEROUTPUT ON
-------------------------------FETCHING RECORDS IN SCALAR VARIABLE------------------------------------
DECLARE
v_name VARCHAR2(100);
BEGIN
SELECT first_name
INTO v_name
FROM employees
WHERE employee_id=100;
dbms_output.put_line('v_name '||v_name);
END;
/
--------------------------FETCHING RECORDS INTO COLLECTION OBJECT----------------------------------
DECLARE
TYPE lv_name_list_type IS TABLE OF VARCHAR2(100);
lv_name_list lv_name_list_type := lv_name_list_type();
BEGIN
SELECT first_name
BULK COLLECT INTO lv_name_list
FROM EMPLOYEES;
FOR I IN lv_name_list.first..lv_name_list.last
LOOP
dbms_output.put_line(lv_name_list(I));
END LOOP;
END;
/
--------------------EXPLICT CURSOR WITH LOOP STATEMENT-------------------------------
DECLARE
CURSOR emp_name_list IS
SELECT first_name
FROM EMPLOYEES;
v_name VARCHAR2(100);
BEGIN
OPEN emp_name_list;
LOOP
FETCH emp_name_list INTO v_name;
EXIT WHEN emp_name_list%NOTFOUND;
dbms_output.put_line(v_name);
END LOOP;
CLOSE emp_name_list;
END;
/
-------------EXPLICT CURSOR WITHOUT LOOP STATEMENT-------------------------------
DECLARE
CURSOR emp_name_list IS
SELECT first_name
FROM employees;
v_name VARCHAR2(100);
BEGIN
OPEN emp_name_list;
FETCH emp_name_list into v_name;
dbms_output.put_line(v_name);
FETCH emp_name_list into v_name;
dbms_output.put_line(v_name);
CLOSE emp_name_list;
END;
/
-----------EXPLICT CURSOR WITH LOOP STATEMENT-------------------------------
DECLARE
CURSOR emp_name_list IS
SELECT first_name
FROM employees;
v_name VARCHAR2(100);
BEGIN
OPEN emp_name_list;
LOOP
FETCH emp_name_list into v_name;
EXIT WHEN emp_name_list%NOTFOUND;
dbms_output.put_line(v_name);
END LOOP;
CLOSE emp_name_list;
END;
/
------------------------------------
SELECT * FROM v$open_cursor;
SELECT * FROM v$parameter;
select * from v$parameter WHERE NAME LIKE '%open_cursor%';
select * from v$open_cursor where user_name='SCOTT';
Comments
Post a Comment