12.PL_SQL——游标CURSOR
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. 打开游标
LOOP
FETCH c_emp_cursor
INTOv_empno, v_lname;
-- 2. 取数据
EXIT WHENc_emp_cursor%NOTFOUND;
-- 3. 跳出循环
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_lname);
END LOOP;
CLOSE c_emp_cursor;
--4. 关闭游标
END;
/
SQL> @notes/s52.sql
114 Raphaely
115 Khoo
116 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
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
PL/SQL proceduresuccessfully completed
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
114 Raphaely
Rowcount ==>1
115 Khoo
Rowcount ==>2
116 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
114 --> Raphaely
115 --> Khoo
116 --> Baida
117 --> Tobias
118 --> Himuro
119 --> Colmenares
PL/SQL proceduresuccessfully completed
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
200 Whalen
PL/SQL proceduresuccessfully completed
================ 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 --> Donald
2: 199 --> Douglas
3: 200 --> Jennifer
4: 201 --> Michael
5: 202 --> 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 --> Donald
2: 199 --> Douglas
3: 200 --> Jennifer
4: 201 --> Michael
5: 202 --> Pat
页:
[1]