Results 1 to 6 of 6

Thread: Cursors in PL/SQL

  1. #1
    Contributing Member
    Join Date
    May 2006
    Answers
    72

    Cursors in PL/SQL

    What are cursors in PL/SQL? Kindly provide me an example to understand the usage and syntax of cursors.


  2. #2
    Expert Member
    Join Date
    Sep 2006
    Answers
    477

    Re: Cursors in PL/SQL

    When you execute a SQL statement from PL/SQL, the Oracle RDBMS assigns a private work area for that statement. This work area contains information about the SQL statement and the set of data returned or affected by that statement. The PL/SQL cursor is a mechanism by which you can name that work area and manipulate the information within it.
    For further reference,
    http://www.unix.org.ua/orelly/oracle...kt/ch01_09.htm

    http://www.unix.org.ua/orelly/oracle/prog2/ch06_02.htm

    Cheers,
    Kalayama

    [COLOR="Blue"][SIZE="2"]"If you are not living on the edge of your life, you are wasting space"[/SIZE][/COLOR]

    Someone says "Impossible is nothing". The man next him says "Let me see you licking your elbow tip!"

  3. #3
    Expert Member
    Join Date
    Sep 2006
    Answers
    130

    Re: Cursors in PL/SQL

    Oracle uses work areas to execute SQL statements and store processing information.
    These work areas are called Cursors which are used to access its stored information.

    There are two kinds of cursors: Implicit Cursor and Explicit Cursor.
    Implicit Cursors :- PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row.
    Implicit Cursors are opened & closed implicitly & it does not require declaration also.

    Explicit Cursors :- Queries that return more than one row needs explicit declaration to process the rows individually.
    Explicit Cursors needs four steps to be followed, DECLARE, OPEN, FETCH & CLOSE.
    Ex:
    DECLARE
    CURSOR emp_cur IS
    SELECT empno, ename from emp;
    BEGIN
    OPEN emp_cur;
    FETCH emp_cur INTO l_empno, l_ename;
    CLOSE emp_cur;
    END;
    Declare the Cursor - Naming and defining the structure of the query to be performed.
    Open the Cursor - The OPEN statement executes the query.
    Fetch data from Cursor - Fetches the row data into the variables for further processing of the records.
    Close the Cursor - Finally the cursor is closed.

    *** Innila ***

    Last edited by Innila; 02-20-2007 at 08:03 AM.

  4. #4
    Contributing Member
    Join Date
    Dec 2006
    Answers
    77

    Re: Cursors in PL/SQL

    Cursors are replacements for multiple select statements..You can fetch record by record using cursors in oracle.
    The basic steps in cursor operation is
    Declare the cursor
    Open the cursor
    Fetch the cursor and
    Close the cursor.

    The fetch statement will fech a list of records from the DB and stores in the cursor area which can be used for retrieval.

    [B][B][I][COLOR="Blue"][FONT="Times New Roman"]Regards,
    Raju[/FONT][/COLOR][/I][/B][/B]

  5. #5
    Junior Member
    Join Date
    Jul 2008
    Answers
    2

    Re: Cursors in PL/SQL

    hi bro,
    Cursor is a private SQL Work Area.
    There are two types of cursor
    1) Implicit Cursor
    2)Explicit Cursor which is user define

    EXAMple
    Retrive first 10 employees one by one
    DECLARE
    v_no employees.employee_id%TYPE;
    v_name employees.last_name%TYPE;
    CURSOR emp_cursor IS
    SELECT employee_id,last_name
    FROM employees;
    BEGIN
    OPEN emp_cursor
    FOR i IN 1...10 LOOP
    FETCH emp_cursor INTO v_no,v_name;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_no)|| ' '||v_name);
    END LOOP;
    END;


    REGARD
    MIHIR


  6. #6
    Junior Member
    Join Date
    Oct 2009
    Answers
    3

    Re: Cursors in PL/SQL

    we can also use parametrized cursor..........
    try this-


    set serveroutput on
    declare
    v_id employees.employee_id%type;
    v_name employees.last_name%type;
    cursor c(v_dept number) is
    select employee_id,last_name from employees where department_id=v_dept;
    begin
    open c(10);
    loop
    fetch c into v_id,v_name;
    exit when c%notfound;
    dbms_output.put_line(v_id||v_name);
    end loop;
    end;


  7.  Sponsored Ads
    Ad


     

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact