转载 游标示例
DECLARE
CURSOR c_cursor
IS SELECT first_name || last_name, Salary
FROM EMPLOYEES
WHERE rownum<11;
v_ename EMPLOYEES.first_name%TYPE;
v_sal EMPLOYEES.Salary%TYPE;
BEGIN
OPEN c_cursor;
FETCH c_cursor INTO v_ename, v_sal;
WHILE c_cursor%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(v_ename||'---'||to_char(v_sal) );
FETCH c_cursor INTO v_ename, v_sal;
END LOOP;
CLOSE c_cursor;
END;
--------------------------------------------------------------------------------------------------------------------------------------
DECLARE
DeptRec DEPARTMENTS%ROWTYPE;
Dept_name DEPARTMENTS.DEPARTMENT_NAME%TYPE;
Dept_loc DEPARTMENTS.LOCATION_ID%TYPE;
CURSOR c1 IS
SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= 30;
CURSOR c2(dept_no NUMBER DEFAULT 10) IS
SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS
WHERE DEPARTMENT_ID <= dept_no;
CURSOR c3(dept_no NUMBER DEFAULT 10) IS
SELECT * FROM DEPARTMENTS
WHERE DEPARTMENTS.DEPARTMENT_ID <=dept_no;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO dept_name, dept_loc;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc);
END LOOP;
CLOSE c1;
OPEN c2;
LOOP
FETCH c2 INTO dept_name, dept_loc;
EXIT WHEN c2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(dept_name||'---'||dept_loc);
END LOOP;
CLOSE c2;
OPEN c3(dept_no =>20);
LOOP
FETCH c3 INTO deptrec;
EXIT WHEN c3%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(deptrec.DEPARTMENT_ID||'---'||deptrec.DEPARTMENT_NAME||'---'||deptrec.LOCATION_ID);
END LOOP;
CLOSE c3;
END;
----------------------------------------------------------------------------------------------------------------------------------------
DECLARE
v_empno EMPLOYEES.EMPLOYEE_ID%TYPE;
v_sal EMPLOYEES.Salary%TYPE;
CURSOR c_cursor IS SELECT EMPLOYEE_ID, Salary FROM EMPLOYEES;
BEGIN
OPEN c_cursor;
LOOP
FETCH c_cursor INTO v_empno, v_sal;
EXIT WHEN c_cursor%NOTFOUND;
IF v_sal<=1200 THEN
UPDATE EMPLOYEES SET Salary=Salary+50 WHERE EMPLOYEE_ID=v_empno;
DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'工资已更新!');
END IF;
DBMS_OUTPUT.PUT_LINE('记录数:'|| c_cursor %ROWCOUNT);
END LOOP;
CLOSE c_cursor;
END;
----------------------------------------------------------------------------------------------------------------------------------------
DECLARE
v_f_name employees.first_name%TYPE;
v_j_id employees.job_id%TYPE;
CURSOR c1 --声明游标,没有参数没有返回值
IS
SELECT first_name, job_id FROM employees
WHERE department_id = 20;
BEGIN
OPEN c1; --打开游标
LOOP
FETCH c1 INTO v_f_name, v_j_id; --提取游标
IF c1%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_f_name||'的岗位是'||v_j_id);
ELSE
DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
EXIT;
END IF;
END LOOP;
CLOSE c1; --关闭游标
END;
----------------------------------------------------------------------------------------------------------------------------------------
DECLARE
v_f_name employees.first_name%TYPE;
v_h_date employees.hire_date%TYPE;
CURSOR c2(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数没有返回值
IS
SELECT first_name, hire_date FROM employees
WHERE department_id = dept_id AND job_id = j_id;
BEGIN
OPEN c2(90, 'AD_VP'); --打开游标,传递参数值
LOOP
FETCH c2 INTO v_f_name, v_h_date; --提取游标
IF c2%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_f_name||'的雇佣日期是'||v_h_date);
ELSE
DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
EXIT;
END IF;
END LOOP;
CLOSE c2; --关闭游标
END;
----------------------------------------------------------------------------------------------------------------------------------------
DECLARE
TYPE emp_record_type IS RECORD(
f_name employees.first_name%TYPE,
h_date employees.hire_date%TYPE);
v_emp_record EMP_RECORD_TYPE;
CURSOR c3(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数有返回值
RETURN EMP_RECORD_TYPE
IS
SELECT first_name, hire_date FROM employees
WHERE department_id = dept_id AND job_id = j_id;
BEGIN
OPEN c3(j_id => 'AD_VP', dept_id => 90); --打开游标,传递参数值
LOOP
FETCH c3 INTO v_emp_record; --提取游标
IF c3%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是'
||v_emp_record.h_date);
ELSE
DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
EXIT;
END IF;
END LOOP;
CLOSE c3; --关闭游标
END;
----------------------------------------------------------------------------------------------------------------------------------------
DECLARE
CURSOR c4(dept_id NUMBER, j_id VARCHAR2) --声明游标,有参数没有返回值
IS
SELECT first_name f_name, hire_date FROM employees
WHERE department_id = dept_id AND job_id = j_id;
--基于游标定义记录变量，比声明记录类型变量要方便，不容易出错
v_emp_record c4%ROWTYPE;
BEGIN
OPEN c4(90, 'AD_VP'); --打开游标,传递参数值
LOOP
FETCH c4 INTO v_emp_record; --提取游标
IF c4%FOUND THEN
DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的雇佣日期是'
||v_emp_record.hire_date);
ELSE
DBMS_OUTPUT.PUT_LINE('已经处理完结果集了');
EXIT;
END IF;
END LOOP;
CLOSE c4; --关闭游标
END;
----------------------------------------------------------------------------------------------------------------------------------------
DECLARE
CURSOR c_sal IS SELECT employee_id, first_name || last_name ename, salary
FROM employees ;
BEGIN
--隐含打开游标
FOR v_sal IN c_sal LOOP
--隐含执行一个FETCH语句
DBMS_OUTPUT.PUT_LINE(to_char(v_sal.employee_id)||'---'|| v_sal.ename||'---'||to_char(v_sal.salary)) ;
--隐含监测c_sal%NOTFOUND
END LOOP;
--隐含关闭游标
END;
----------------------------------------------------------------------------------------------------------------------------------------
DECLARE
CURSOR c_cursor(dept_no NUMBER DEFAULT 10)
IS
SELECT department_name, location_id FROM departments WHERE department_id <= dept_no;
BEGIN
DBMS_OUTPUT.PUT_LINE('当dept_no参数值为30：');
FOR c1_rec IN c_cursor(30) LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
END LOOP;
DBMS_OUTPUT.PUT_LINE(CHR(10)||'使用默认的dept_no参数值10：');
FOR c1_rec IN c_cursor LOOP DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);
END LOOP;
END;
----------------------------------------------------------------------------------------------------------------------------------------
DECLARE
V_deptno department_id%TYPE :=&p_deptno;
BEGIN
DELETE FROM employees WHERE department_id=v_deptno;
IF SQL%NOTFOUND THEN
DELETE FROM departments WHERE department_id=v_deptno;
END IF;
END;
----------------------------------------------------------------------------------------------------------------------------------------
DECLARE
v_rows NUMBER;
BEGIN
--更新数据
UPDATE employees SET salary = 30000
WHERE department_id = 90 AND job_id = 'AD_VP';
--获取默认游标的属性值
v_rows := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个雇员的工资');
--回退更新，以便使数据库的数据保持原样
ROLLBACK;
END;
----------------------------------------------------------------------------------------------------------------------------------------
DECLARE
V_deptno employees.department_id%TYPE :=&p_deptno;
CURSOR emp_cursor
IS
SELECT employees.employee_id, employees.salary
FROM employees WHERE employees.department_id=v_deptno
FOR UPDATE NOWAIT;
BEGIN
FOR emp_record IN emp_cursor LOOP
IF emp_record.salary < 1500 THEN
UPDATE employees SET salary=1500
WHERE CURRENT OF emp_cursor;
END IF;
END LOOP;
-- COMMIT;
END;
----------------------------------------------------------------------------------------------------------------------------------------
DECLARE
v_emp_record employees%ROWTYPE;
CURSOR c1
IS
SELECT * FROM employees FOR UPDATE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_emp_record;
EXIT WHEN c1%NOTFOUND;
IF v_emp_record.department_id = 90 AND
v_emp_record.job_id = 'AD_VP'
THEN
UPDATE employees SET salary = 20000
WHERE CURRENT OF c1; --更新当前游标行对应的数据行
END IF;
END LOOP;
COMMIT; --提交已经修改的数据
CLOSE c1;
END;
----------------------------------------------------------------------------------------------------------------------------------------
DECLARE
TYPE emp_job_rec IS RECORD(
Employee_id employees.employee_id%TYPE,
Employee_name employees.first_name%TYPE,
Job_title employees.job_id%TYPE
);
TYPE emp_job_refcur_type IS REF CURSOR RETURN emp_job_rec;
Emp_refcur emp_job_refcur_type ;
Emp_job emp_job_rec;
BEGIN
OPEN emp_refcur FOR
SELECT employees.employee_id, employees.first_name||employees.last_name, employees.job_id
FROM employees
ORDER BY employees.department_id;
FETCH emp_refcur INTO emp_job;
WHILE emp_refcur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(emp_job.employee_id||': '||emp_job.employee_name||' is a '||emp_job.job_title);
FETCH emp_refcur INTO emp_job;
END LOOP;
END;
----------------------------------------------------------------------------------------------------------------------------------------
DECLARE
Type refcur_t IS REF CURSOR;
Refcur refcur_t;
TYPE sample_rec_type IS RECORD (
Id number,
Description VARCHAR2 (30)
);
sample sample_rec_type;
selection varchar2(1) := UPPER (SUBSTR ('&tab', 1, 1));
BEGIN
IF selection='D' THEN
OPEN refcur FOR
SELECT departments.department_id, departments.department_name FROM departments;
DBMS_OUTPUT.PUT_LINE('Department data');
ELSIF selection='E' THEN
OPEN refcur FOR
SELECT employees.employee_id, employees.first_name||' is a '||employees.job_id FROM employees;
DBMS_OUTPUT.PUT_LINE('Employee data');
ELSE
DBMS_OUTPUT.PUT_LINE('Please enter ''D'' or ''E''');
RETURN;
END IF;
DBMS_OUTPUT.PUT_LINE('----------------------');
FETCH refcur INTO sample;
WHILE refcur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(sample.id||': '||sample.description);
FETCH refcur INTO sample;
END LOOP;
CLOSE refcur;
END;
----------------------------------------------------------------------------------------------------------------------------------------
DECLARE
--定义一个游标数据类型
TYPE emp_cursor_type IS REF CURSOR;
--声明一个游标变量
c1 EMP_CURSOR_TYPE;
--声明两个记录变量
v_emp_record employees%ROWTYPE;
v_reg_record regions%ROWTYPE;
BEGIN
OPEN c1 FOR SELECT * FROM employees WHERE department_id = 20;
LOOP
FETCH c1 INTO v_emp_record;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name||'的雇佣日期是'
||v_emp_record.hire_date);
END LOOP;
--将同一个游标变量对应到另一个SELECT语句
OPEN c1 FOR SELECT * FROM regions WHERE region_id IN（1，2）;
LOOP
FETCH c1 INTO v_reg_record;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_reg_record.region_id||'表示'
||v_reg_record.region_name);
END LOOP;
CLOSE c1;
END;
----------------------------------------------------------------------------------------------------------------------------------------
DECLARE
--定义一个与employees表中的这几个列相同的记录数据类型
TYPE emp_record_type IS RECORD(
f_name employees.first_name%TYPE,
h_date employees.hire_date%TYPE,
j_id employees.job_id%TYPE);
--声明一个该记录数据类型的记录变量
v_emp_record EMP_RECORD_TYPE;
--定义一个游标数据类型
TYPE emp_cursor_type IS REF CURSOR
RETURN EMP_RECORD_TYPE;
--声明一个游标变量
c1 EMP_CURSOR_TYPE;
BEGIN
OPEN c1 FOR SELECT first_name, hire_date, job_id
FROM employees WHERE department_id = 20;
LOOP
FETCH c1 INTO v_emp_record;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('雇员名称：'||v_emp_record.f_name
||' 雇佣日期：'||v_emp_record.h_date
||' 岗位：'||v_emp_record.j_id);
END LOOP;
CLOSE c1;
END;
