DECLARE
TYPE emp_record_type IS RECORD( --TYPE 表示定义变量,变量类型是RECORD(变量1,变量2)
f_name employees.first_name%TYPE,
h_date employees.hire_date%TYPE);
v_emp_record EMP_RECORD_TYPE; --用刚定义的变量类型EMP_RECORD_TYPE定义变量v_emp_record
代替写法,基于游标定义记录变量:
DECLARE
CURSOR myCursor IS SELECT......
v_emp_record myCursor%ROWTYPE;
注意:Navicat连接Oracle中,需要删除关键字DECLARE
定义有参数且有返回值的游标。
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 myCursor IS SELECT......
v_emp_record myCursor%ROWTYPE;
案例:
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;