您的位置:知识库 » 数据库

ORACLE PL/SQL编程之把游标说透

作者: huyong  来源: 博客园  发布时间: 2011-06-04 18:55  阅读: 5417 次  推荐: 7   原文链接   [收藏]  
[1] ORACLE PL/SQL编程之把游标说透
[2] ORACLE PL/SQL编程之把游标说透
4.2 游标变量

  与游标一样,游标变量也是一个指向多行查询结果集合中当前数据行的指针。但与游标不同的是,游标变量是动态的,而游标是静态的。游标只能与指定的查询相连,即固定指向一个查询的内存处理区域,而游标变量则可与不同的查询语句相连,它可以指向不同查询语句的内存处理区域(但不能同时指向多个内存处理区域,在某一时刻只能与一个查询语句相连),只要这些查询语句的返回类型兼容即可。

  4.2.1  声明游标变量

  游标变量为一个指针,它属于参照类型,所以在声明游标变量类型之前必须先定义游标变量类型。在PL/SQL中,可以在块、子程序和包的声明区域内定义游标变量类型。

  语法格式为:

TYPE ref_type_name IS REF CURSOR
[ RETURN return_type];
其中:ref_type_name为新定义的游标变量类型名称;

  return_type 为游标变量的返回值类型,它必须为记录变量。

  在定义游标变量类型时,可以采用强类型定义和弱类型定义两种。强类型定义必须指定游标变量的返回值类型,而弱类型定义则不说明返回值类型。

  声明一个游标变量的两个步骤:

  步骤一:定义一个REF CURSOU数据类型,如:

  TYPE ref_cursor_type IS REF CURSOR;

  步骤二:声明一个该数据类型的游标变量,如:

  cv_ref REF_CURSOR_TYPE;

  例:创建两个强类型定义游标变量和一个弱类型游标变量:

DECLARE
TYPE deptrecord
IS RECORD(
Deptno departments.department_id
%TYPE,
Dname departments.department_name
%TYPE,
Loc departments.location_id
%TYPE
);
TYPE deptcurtype
IS REF CURSOR RETURN departments%ROWTYPE;
TYPE deptcurtyp1
IS REF CURSOR RETURN deptrecord;
TYPE curtype
IS REF CURSOR;
Dept_c1 deptcurtype;
Dept_c2 deptcurtyp1;
Cv curtype;

  4.2.2  游标变量操作

  与游标一样,游标变量操作也包括打开、提取和关闭三个步骤。

  1. 打开游标变量

  打开游标变量时使用的是OPEN…FOR 语句。格式为:

OPEN {cursor_variable_name | :host_cursor_variable_name}
FOR select_statement;

  其中:cursor_variable_name为游标变量,host_cursor_variable_name为PL/SQL主机环境(如OCI: ORACLE Call Interface,Pro*c 程序等)中声明的游标变量。

  OPEN…FOR 语句可以在关闭当前的游标变量之前重新打开游标变量,而不会导致CURSOR_ALREAD_OPEN异常错误。新打开游标变量时,前一个查询的内存处理区将被释放。

  2. 提取游标变量数据

  使用FETCH语句提取游标变量结果集合中的数据。格式为:

FETCH {cursor_variable_name | :host_cursor_variable_name}
INTO {variable [, variable]| record_variable};

  其中:cursor_variable_name和host_cursor_variable_name分别为游标变量和宿主游标变量名称;variable和record_variable分别为普通变量和记录变量名称。

  3. 关闭游标变量

  CLOSE语句关闭游标变量,格式为:

CLOSE {cursor_variable_name | :host_cursor_variable_name}

  其中:cursor_variable_name和host_cursor_variable_name分别为游标变量和宿主游标变量名称,如果应用程序试图关闭一个未打开的游标变量,则将导致INVALID_CURSOR异常错误。

  例15:强类型参照游标变量类型

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;

  例16:弱类型参照游标变量类型

PROMPT
PROMPT
'What table would you like to see?'
ACCEPT tab PROMPT
'(D)epartment, or (E)mployees:'

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;

  例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 IN12);
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;

[第1页][第2页]
7
0

数据库热门文章

    数据库最新文章

      最新新闻

        热门新闻