oracle游标 - 数据库编程

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; 复制代码

例17：使用游标变量（没有RETURN子句）

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; 复制代码

例18：使用游标变量（有RETURN子句）

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; 复制代码

oracle游标(五)