12.pl_sql--cursors cursor

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/1C/wKioL1QPBoXjGN9mAAIiKqKqqL8634.jpg "style=" float: none; "title=" 1.png "alt=" Wkiol1qpboxjgn9maaiikqkqql8634.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/1A/wKiom1QPBnmxCnLGAAHrMnMd9-w561.png "style=" float: none; "title=" 2.PNG "alt=" Wkiom1qpbnmxcnlgaahrmnmd9-w561.png "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/1C/wKioL1QPBobxm7lBAAF2jlvctks921.jpg "style=" float: none; "title=" 3.png "alt=" Wkiol1qpbobxm7lbaaf2jlvctks921.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/1C/wKioL1QPBofT8SBXAAGYvQgmL_c247.png "style=" float: none; "title=" 4.PNG "alt=" Wkiol1qpboft8sbxaagyvqgml_c247.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/1A/wKiom1QPBnujNZROAAGDohcLo-g076.png "style=" float: none; "title=" 5.PNG "alt=" Wkiom1qpbnujnzroaagdohclo-g076.png "/>

Sql> Edit

DECLARE

Cursorc_emp_cursor is

SELECTEMPLOYEE_ID, last_name

Fromemployees

wheredepartment_id = 30;

V_empnoemployees.employee_id%type;

V_lnameemployees.last_name%type;

BEGIN

OPEN c_emp_cursor;

--1. Open Cursor

LOOP

FETCH c_emp_cursor

Intov_empno, V_lname;

--2. Fetch Data

EXIT Whenc_emp_cursor%notfound;

--3. Jump out of the loop

Dbms_output. Put_Line (V_empno | | "| | V_lname);

END LOOP;

CLOSE c_emp_cursor;

--4. Close Cursors

END;

/

Sql> @notes/s52.sql

raphaely

Khoo

Baida

117 Tobias

118 Himuro

119 Colmenares

PL/SQL procedure successfully completed.

===================example1--records=====================

Sql> Edit

DECLARE

CURSOR E is

SELECT * fromemployees;

Emprec E%rowtype;

BEGIN

OPEN e;

LOOP

FETCH e Intoemprec;

EXIT Whene%notfound;

Dbms_output. Put_Line (' First Name ==> ' | | emprec.first_name);

END LOOP;

CLOSE e;

END;

/

Sql> @notes/s53.sql

First Name ==> Donald

First Name ==> Douglas

First Name ==> Jennifer

First Name ==> Michael

First Name ==> Pat

...

First Name ==> Vance

First Name ==> Alana

First Name ==> Kevin

PL/SQL procedure successfully completed.

Sql> Edit

DECLARE

Cursorc_emp_cursor is

SELECTEMPLOYEE_ID, last_name

From Employees

wheredepartment_id = 30;

V_emp_recordc_emp_cursor%rowtype;

BEGIN

OPEN C_emp_cursor;

LOOP

Fetchc_emp_cursor

into V_emp_record;

EXIT Whenc_emp_cursor%notfound;

Dbms_output. Put_Line (v_emp_record.employee_id | | "| | V_emp_record.last_name);

END LOOP;

Closec_emp_cursor;

END;

/

Sql> @notes/s54.sql

raphaely

Khoo

Baida

117 Tobias

118 Himuro

119 Colmenares

PL/SQL proceduresuccessfully completed

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/1A/wKiom1QPBrqgPcpcAAEyeHjf0Tc435.jpg "title=" 6.png " alt= "Wkiom1qpbrqgpcpcaaeyehjf0tc435.jpg"/>

Sql> Edit

DECLARE

Cursorc_emp_cursor is

SELECTEMPLOYEE_ID, last_name

From Employees

wheredepartment_id = 30;

BEGIN

For Emp_record Inc_emp_cursor

LOOP

Dbms_output. Put_Line (emp_record.employee_id| | "| | Emp_record.last_name);

Dbms_output. Put_Line (' Rowcount ==> ' | | c_emp_cursor%rowcount);

END LOOP;

END;

/

Sql> @notes/s55.sql

raphaely

Rowcount ==>1

Khoo

Rowcount ==>2

Baida

Rowcount ==>3

117 Tobias

Rowcount ==>4

118 Himuro

Rowcount ==>5

119 Colmenares

Rowcount ==>6

PL/SQL procedure successfully completed.

Cursor for LOOPs Using subqueries--no need to declare the cursor

Sql> Edit

BEGIN

For I in

(SELECTEMPLOYEE_ID, last_name

From Employees

wheredepartment_id = 30)

LOOP

Dbms_output. Put_Line (i.employee_id | | '--' | | I.last_name);

END LOOP;

END;

/

Sql> @notes/s56.sql

---raphaely

---Khoo

---Baida

117-Tobias

118-Himuro

119-Colmenares

PL/SQL proceduresuccessfully completed

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/1C/wKioL1QPBwPyyNULAAKB5wQJAyc843.jpg "style=" float: none; "title=" 7.png "alt=" Wkiol1qpbwpyynulaakb5wqjayc843.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/1C/wKioL1QPBwSjDHBLAAFYURBFKLs891.jpg "style=" float: none; "title=" 8.png "alt=" Wkiol1qpbwsjdhblaafyurbfkls891.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/1B/wKiom1QPBvjQ_8ekAAIIUP1KYzo306.png "style=" float: none; "title=" 9.PNG "alt=" Wkiom1qpbvjq_8ekaaiiup1kyzo306.png "/>

Sql> Edit

DECLARE

Cursorc_emp_cursor (deptno number) is

SELECTEMPLOYEE_ID, last_name

From Employees

wheredepartment_id = Deptno;

V_emp_recordc_emp_cursor%rowtype;

BEGIN

OPEN C_emp_cursor (10);

LOOP

Fetchc_emp_cursor

Intov_emp_record;

EXIT Whenc_emp_cursor%notfound;

Dbms_output. Put_Line (v_emp_record.employee_id | | "| | V_emp_record.last_name);

END LOOP;

Closec_emp_cursor;

END;

/

Sql> @notes/s58.sql

Whalen

PL/SQL proceduresuccessfully completed

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/49/1B/wKiom1QPB1iD-4mSAAGa8boJQ1w059.png "style=" float: none; "title=" 10.PNG "alt=" Wkiom1qpb1id-4msaaga8bojq1w059.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/1C/wKioL1QPB2WwGctaAAEplv1dLFE627.png "style=" float: none; "title=" 11.PNG "alt=" Wkiol1qpb2wwgctaaaeplv1dlfe627.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/1B/wKiom1QPB1nRfSGAAAGg9V5itJg628.png "style=" float: none; "title=" 12.PNG "alt=" Wkiom1qpb1nrfsgaaagg9v5itjg628.png "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/49/1C/wKioL1QPB2byEmrzAAI_TjrTzbo648.png "style=" float: none; "title=" 13.PNG "alt=" Wkiol1qpb2byemrzaai_tjrtzbo648.png "/>

================ Example 1 =====================

Sql> Edit

DECLARE

TYPE Emp_type istable of Employees%rowtype

INDEX Bypls_integer;

L_emp Emp_type;

L_row Pls_integer;

BEGIN

SELECT * Bulkcollect

Into L_emp

From employees;

Dbms_output. Put_Line (' The Count is: ' | | l_emp. COUNT);

L_row: =l_emp. First;

while (L_row ISNOT NULL)

LOOP

Dbms_output. Put_Line (L_row | | ': ' | | L_emp (l_row). employee_id | | '--' | | L_emp (L_row). first_name);

L_row: =l_emp. NEXT (L_row);

END LOOP;

END;

/

Sql> @notes/s60.sql

The Count is:108

1:198 and Donald

2:199-Douglas

3:200-Jennifer

4:201--Michael

5:202 to Pat

6:203--Susan

================ Example 2 =====================

Sql> Edit

DECLARE

CURSOR e is select* from employees;

TYPE Emp_type istable of E%rowtype

INDEX Bypls_integer;

L_emp Emp_type;

L_row Pls_integer;

BEGIN

OPEN e;

FETCH e bulkcollect into l_emp;

CLOSE e;

Dbms_output. Put_Line (' The Count is: ' | | l_emp. COUNT);

L_row: =l_emp. First;

while (L_row ISNOT NULL)

LOOP

Dbms_output. Put_Line (l_row| | ': ' | | L_emp (l_row). employee_id | | '--' | | L_emp (L_row). first_name);

L_row: =l_emp. NEXT (L_row);

END LOOP;

END;

/

Sql> @notes/s61.sql

The Count is:108

1:198 and Donald

2:199-Douglas

3:200-Jennifer

4:201--Michael

5:202 to Pat

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/49/1B/wKiom1QPB5yjF7hDAAIrIDzsZkM579.png "title=" 14.PNG "alt=" Wkiom1qpb5yjf7hdaairidzszkm579.png "/>

This article is from the "Big sword without front of the great Qiao Not Work" blog, please make sure to keep this source http://wuyelan.blog.51cto.com/6118147/1550348

12.pl_sql--cursors cursor