Getting started with Oracle CURSOR

Source: Internet
Author: User
Tags oracle cursor

A cursor with parameters is similar to a stored procedure and a function. You can pass parameters to the cursor and use them in the query. It is usually used to open a cursor under certain conditions.

Unlike stored procedures, a cursor can only accept passed values, but cannot return values. The parameter only defines the data type and has no size.

Example:

DECLARE
CURSOR c_dept is select * from dept order by deptno;
CURSOR c_emp (p_dept VARCHAR2)
Is select ename, sal from emp where deptno = p_dept order by ename;

R_dept DEPT % ROWTYPE;
V_ename EMP. ENAME % TYPE;
V_salary EMP. SAL % TYPE;
V_total_salary v_salary % TYPE;
BEGIN
OPEN c_dept;
LOOP
FETCH c_dept INTO r_dept;
Exit when c_dept % NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('department: '| r_dept.DEPTNO |'-'| r_dept.DNAME );
V_total_salary: = 0;
OPEN c_emp (r_dept.DEPTNO );
LOOP
FETCH c_emp INTO v_ename, v_salary;
Exit when c_emp % NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('name: '| v_ename | 'salary:' | v_salary );
V_total_salary: = v_total_salary + v_salary;
End loop;
CLOSE c_emp;
DBMS_OUTPUT.PUT_LINE ('total salary of depart: '| v_total_salary );
End loop;
CLOSE c_dept;
END;

Url: http://greatverve.cnblogs.com/archive/2011/07/12/oracle-cursor.html

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.