Oracle cursor Stored Procedure statement

You can use the scott password triger, an existing Oracle account, to log in and use the existing table to perform the test.

1. Create or replace procedure lpmtest2
2. As
3. Para1 varchar2 (10);
4. Cursor youbiao is select ename from test where sal>1300;
5. Begin
6. Open youbiao;
7. Loop
8. Fetch youbiao into para1;
9. Exit when youbiao % notfound;
10. Dbms_output.put_line ('++ :'| Para1 );
11. End loop;
12. Close youbiao;
13. End;

Code

1. Create or replace procedure lpmtest2
2. As
3. Cursor youbiao is select ename, sal, job from test where sal>1300;
4. C_row youbiao % rowtype; -- defines a cursor variable c_row, which is the data type of a row in youbiao.
5. Begin
6. Open youbiao;
7. Loop
8. Fetch youbiao into c_row;
9. Exit when youbiao % notfound;
10. Dbms_output.put_line ('++ :'| C_row.ename |':'| C_row.sal |':'| C_row.job );
11. End loop;
12. Close youbiao;
13. End;

Code

1. Create or replace procedure lpmtest3
2. As
3. Cursor c_dept is select * from dept order by deptno;
4. Cursor c_emp (p_dept varchar2) is select ename, sal from emp where deptno = p_dept order by ename;
5. R_dept c_dept % rowtype;
6. V_ename emp. ename % type;
7. V_sal emp. sal % type;
8. V_totalsal emp. sal % type; -- used to save the total salary of all employees in each department
9. Begin
10. Open c_dept;
11. Loop
12. Fetch c_dept into r_dept;
13. Exit when c_dept % notfound;
14. Dbms_output.put_line (r_dept.deptno |':'| R_dept.dname |'++');
15. V_totalsal: =0;
16. Open c_emp (r_dept.deptno );
17. Loop
18. Fetch c_emp into v_ename, v_sal;
19. Exit when c_emp % notfound;
20. Dbms_output.put_line ('V _ ename :'| V_ename |';'|'V _ sal :'| V_sal );
21. V_totalsal: = v_totalsal + v_sal;
22. End loop;
23. Close c_emp;
24. Dbms_output.put_line ('Depsaltotal :'| V_totalsal );
25. End loop;
26. Close c_dept;
27. End;

Print the result:

1. 10: ACCOUNTING ++
2. V_ename: CLARK; v_sal:2450
3. V_ename: KING; v_sal:5000
4. V_ename: MILLER; v_sal:1300
5. Deptsaltotal:8750
6. 20: RESEARCH ++
7. V_ename: ADAMS; v_sal:1100
8. V_ename: FORD; v_sal:3000
9. V_ename: JONES; v_sal:2975
10. V_ename: SCOTT; v_sal:3000
11. V_ename: SMITH; v_sal:800
12. Deptsaltotal:10875
13. 30: SALES ++
14. V_ename: ALLEN; v_sal:1600
15. V_ename: BLAKE; v_sal:2850
16. V_ename: JAMES; v_sal:950
17. V_ename: MARTIN; v_sal:1250
18. V_ename: TURNER; v_sal:1500
19. V_ename: WARD; v_sal:1250
20. Deptsaltotal:9400
21. 40: OPERATIONS ++
22. Deptsaltotal:0

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12