oracle 游标 转成数组,Oracel 游标Cursor的使用(转)

http://www.javaeye.com/topic/79108

--[4]// Oracle Cursor and OOP Conception

-------------------------------------------------------------------------------------//

--显示游标---------------------------------------------------------//

--001

DECLARE

CURSOR c1 IS  --声明游标

SELECT name,address FROM student ORDER BY name;

v_name student.name%TYPE;

v_addr student.address%TYPE;

BEGIN

OPEN c1;  --打开游标

FETCH c1 INTO v_name,v_addr;  --第一次定位读取数据，并保存在变量

--循环读取数据

WHILE c1%FOUND LOOP

DBMS_OUTPUT.PUT_LINE(TO_CHAR(c1%ROWCOUNT) ||'  '|| v_name ||' , '|| v_addr);

FETCH c1 INTO v_name,v_addr;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Total rows is : '|| c1%ROWCOUNT);

CLOSE c1;  --关闭游标

END;

/

--002

DECLARE

CURSOR cur_emp IS

SELECT sal FROM emp WHERE deptno =20FOR UPDATE OF sal;

v_sal emp.sal%TYPE;

BEGIN

OPEN cur_emp;

FETCH cur_emp INTO v_sal;

LOOP

EXIT WHEN cur_emp%NOTFOUND;

IF v_sal <2000THEN

UPDATE emp SET sal =2000WHERE current OF cur_emp;  --更新当前数据

END IF;

FETCH cur_emp INTO v_sal;

END LOOP;

DBMS_OUTPUT.PUT_LINE('命令执行完毕');

IF cur_emp%ISOPEN THEN

CLOSE cur_emp;

IF cur_emp%ISOPEN THEN

DBMS_OUTPUT.PUT_LINE('Cursor state : Open');

CLOSE cur_emp;

ELSE

DBMS_OUTPUT.PUT_LINE('Cursor state : Close');

END IF;

END IF;

END;

/

--隐式游标---------------------------------------------------------//

--不能显式的使用OPEN、CLOSE和FETCH语句，他会自动完成

DECLARE

no   emp.empno%TYPE;

name emp.ename%TYPE;

BEGIN

SELECT empno,ename INTO no,name FROM emp WHERE empno ='7788';

IF SQL%ISOPEN THEN

DBMS_OUTPUT.PUT_LINE('Cursor state : Open');

ELSE

DBMS_OUTPUT.PUT_LINE('Cursor state : Close');

END IF;

DBMS_OUTPUT.PUT_LINE(no ||'    '|| name);

DBMS_OUTPUT.PUT_LINE('Return rows : '|| SQL%ROWCOUNT);

EXCEPTION

WHEN CURSOR_ALREADY_OPEN THEN

DBMS_OUTPUT.PUT_LINE('Cursor already open');

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('No data found');

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE('Return many rows');

END;

/

--游标变量(一个游标变量可以在一个PL\SQL块中使用多次)---------------------------//

DECLARE

TYPE refcur IS REF CURSOR;  --[RETURN TYPE]

cur_emp refcur;  --引用游标

dept emp.deptno%TYPE;

name emp.ename%TYPE;

BEGIN

OPEN cur_emp FOR SELECT deptno FROM emp WHERE empno ='7788';

FETCH cur_emp INTO dept;

DBMS_OUTPUT.PUT_LINE('Dept : '|| dept);

CLOSE cur_emp;

OPEN cur_emp FOR SELECT ename FROM emp WHERE empno ='7788';

FETCH cur_emp INTO name;

DBMS_OUTPUT.PUT_LINE('Name : '|| name);

CLOSE cur_emp;

END;

/

--游标在三种循环中的使用-------------------------------------------//

--001--Loop

DECLARE

CURSOR cur_emp IS

SELECT ename FROM emp;

v_name emp.ename%TYPE;

BEGIN

OPEN cur_emp;

FETCH cur_emp INTO v_name;

LOOP

EXIT WHEN cur_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Name is : '|| v_name);

FETCH cur_emp INTO v_name;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Return rows : '|| cur_emp%ROWCOUNT);

CLOSE cur_emp;

END;

/

--002--While

DECLARE

CURSOR cur_emp IS

SELECT ename FROM emp;

v_name emp.ename%TYPE;

BEGIN

OPEN cur_emp;

FETCH cur_emp INTO v_name;

WHILE cur_emp%FOUND LOOP

DBMS_OUTPUT.PUT_LINE('Name is : '|| v_name);

FETCH cur_emp INTO v_name;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Return rows : '|| cur_emp%ROWCOUNT);

CLOSE cur_emp;

END;

/

--003--For

--注：在使用 FOR 循环时，不能显式的使用 open、colse 和 FETCH 语句，他会自动完成

DECLARE

rows number :=0;

CURSOR cur_emp IS

SELECT ename FROM emp;

BEGIN

FOR v_emp in cur_emp LOOP

DBMS_OUTPUT.PUT_LINE('name is : '|| v_emp.ename);

rows := rows +1;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Return rows : '|| rows);

END;

/

--OOP Conception---------------------------------------------------//

--001

--创建对象类型(相当于C中的结构体，可实现代码重用机制)---

--**注意：OR REPLACE表示将覆盖此用户下的同名对象类型，在不熟悉数据库结构的时候不要滥用

CREATE OR REPLACE TYPE t_score AS OBJECT

(

java number(5,2),

net  number(5,2)

)

/

--可指定类型名称直接创建对象表

CREATE TABLE score OF t_score;

--或应用于表中

CREATE TABLE student

(

id    VARCHAR2(4),

name  VARCHAR2(20),

score t_score

);

--为上表插入数据(利用构造函数)

INSERT INTO student VALUES('s101','张三',t_score(85,76));

--或

INSERT INTO student(id,name,score) VALUES('s102','李四',t_score(80,89));

--查询指定

SELECT s.score.java FROM student s;  --必须赋予别名

--002

--或者可以创建更为复杂的对象类型，即类型嵌套

CREATE OR REPLACE TYPE t_stu AS OBJECT

(

id    VARCHAR2(4),

name  VARCHAR2(20),

score t_score

)

/

--应用于表中

CREATE TABLE student

(

stu_base t_stu,

teacher  varchar2(20)

);

--查看表结构

SET DESC DEPTH ALL;  --指定查看层次，否则只能看到第一层

DESC student;

--插入数据

INSERT INTO student

VALUES(t_stu('s101','zhao',t_score(76,81)),'zhang');

--查询指定

SELECT s.stu_base.score.java FROM student s WHERE s.stu_base.id ='s101';

--修改对象类型(注：修改对象类型需9i以上版本)------------------------//

--其中INVALIDATE选项使得所有依赖于t_stu类型的对象和表标记为invalid

--增加属性address，注：修改后可能引起一些未知的错误，所以请不要随意修改

ALTER TYPE t_stu

ADD ATTRIBUTE address VARCHAR2(50) INVALIDATE;

--删除类型(注：需按嵌套逐级删除)

DROP TYPE t_stu;

DROP TYPE t_score;

--继承-------------------------------------------------------------//

--创建一个不可被继承的类型

CREATE OR REPLACE TYPE super_TYPE AS OBJECT

(

n NUMBER,

FINAL MEMBER PROCEDURE cannot_override

)

NOT FINAL

/

--继承时将出错，可用【SHOW ERROR】语句查看错误信息

CREATE OR REPLACE TYPE sub_TYPE UNDER super_TYPE

(

OVERRIDING MEMBER PROCEDURE cannot_override

)

/

--创建一个不可被实例化、不可被继承的类型

CREATE OR REPLACE TYPE shape AS OBJECT

(

n NUMBER,

NOT INSTANTIABLE MEMBER FUNCTION calculate_area RETURN NUMBER

)

NOT INSTANTIABLE NOT FINAL

/

--实例化改类型将出错

DECLARE

l_shape shape;

BEGIN

l_shape := shape(2);

END;

/

--嵌套表(表中之表)-------------------------------------------------//

--创建类型，(以下实例将创建一组动物饲养员嵌套表)

CREATE TYPE animal_ty AS OBJECT

(

breed VARCHAR2(25),

name  VARCHAR2(25),

birthdate DATE

);

/

--此类型将用作一个嵌套表的基础类型

CREATE TYPE animal_nt AS TABLE OF animal_ty;

/

--创建嵌套表

CREATE TABLE breeder

(

breedername VARCHAR2(25),

animals     animal_nt

)

NESTED TABLE animals STORE AS animals_nt_tab;  --animals_nt_tab代表别名

--插入数据

INSERT INTO breeder VALUES('Mary',

animal_nt

(

animal_ty('dog','butch',to_date('2004-3-31','yyyy-mm-dd')),

animal_ty('dog','rover',to_date('2005-8-20','yyyy-mm-dd')),

animal_ty('dog','julio',sysdate)

)

);

INSERT INTO breeder VALUES('Jane',

animal_nt

(

animal_ty('cat','an',to_date('2005-10-12','yyyy-mm-dd')),

animal_ty('cat','jame',to_date('2002-1-23','yyyy-mm-dd')),

animal_ty('cat','killer',to_date('2004-6-2','yyyy-mm-dd'))

)

);

--查询表中姓名为Jane所养的动物

SELECT breed,name,birthdate

FROM TABLE(SELECT animals FROM breeder WHERE breedername='Jane');

--可变数组(类似于嵌套表，概念上讲它是限定了行集合的嵌套表)----------//

--创建类型(以下实例将创建一组联系人嵌套表)

CREATE TYPE comm_info AS OBJECT

(

no        NUMBER(3),    --通讯类型号

comm_TYPE VARCHAR2(20), --通讯类型

comm_no   VARCHAR2(30)  --号码

)

/

--创建可变数组

CREATE TYPE comm_info_list AS VARRAY(50) OF comm_info;

/

--创建表

CREATE TABLE user_info

(

user_id   NUMBER(6),     --用户ID

user_name VARCHAR2(20),  --用户名

user_comm comm_info_list --与用户联系的通讯方式

);

--插入数据

INSERT INTO user_info VALUES(101,'Mary',

comm_info_list(comm_info(1,'手机','13652369888'),

comm_info(2,'座机','02125689366')));

INSERT INTO user_info VALUES(102,'Tom',

comm_info_list(comm_info(1,'手机','13765235898'),

comm_info(2,'座机','021-65234789')));

--查询用户ID为101的手机号码

SELECT comm_type,comm_no

FROM TABLE(SELECT user_comm FROM user_info WHERE user_id =101)

WHERE no =1;

--对象表-----------------------------------------------------------//

--创建对象

CREATE OR REPLACE TYPE address AS OBJECT

(

id     NUMBER(4),

street VARCHAR2(50),

state  VARCHAR2(2),

zip    VARCHAR2(11)

)

/

--创建对象表

CREATE TABLE address_table OF address;

--插入数据

INSERT INTO address_table

VALUES(1,'Oracle way','US','90001');

--或使用构造函数

INSERT INTO address_table

VALUES(address(2,'Microsoft way','US','80863'));

--查询数据

SELECT * FROM address_table;

--VALUE关键字：以对象表别名做参数，返回对象实例

SELECT VALUE(a) FROM address_table a;

--REF数据类型：在关系表中关联对象

CREATE TABLE employee_location

(

empno   NUMBER,

loc_ref REF address SCOPE IS address_table  --此列引用了类型address

);

--查看结构

SET DESC DEPTH ALL;

DESC employee_location;

--REF()函数：将引用对象表中的数据插入

INSERT INTO employee_location

SELECT101,REF(a)

FROM address_table a WHERE id =1;

INSERT INTO employee_location

SELECT102,ref(a)

FROM address_table a WHERE id =2;

--查询

--注：用此语句查询的结果是未解析过的REF数据

SELECT * FROM employee_location

--DEREF()：解析REF数据，返回真正指向的实例

SELECT empno,DEREF(loc_ref)

FROM employee_location;

--悬空REF：REF指向的对象实例被删除了，此时成为REF悬空(dangling)，说明REF指向不存在的实例

DELETE FROM address_table WHERE id =2;

--查询

--悬空的REF会返回NULL，使用 IS DANGLING 确定那些REF悬空

SELECT empno FROM employee_location

WHERE loc_ref IS DANGLING;

--清除悬空的REF，将REF更新未NULL

UPDATE employee_location

SET loc_ref = NULL

WHERE loc_ref IS DANGLING;

--再查看：已经将悬空的REF清除

SELECT * FROM employee_location;

--对象视图---------------------------------------------------------//

--创建表--关系表

CREATE TABLE item

(

item_code VARCHAR2(10),

item_hand NUMBER(10),

item_sode NUMBER(10)

);

--创建对象--使用相同列

CREATE OR REPLACE TYPE item_type AS OBJECT

(

item_code VARCHAR2(10),

item_hand NUMBER(10),

item_sode NUMBER(10)

)

/

--建立对象视图

CREATE VIEW item_view OF item_type  --OF item_type 说明基于对象

WITH OBJECT OID(item_code)  --WITH OBJECT OID(item_code)明确生成OID

AS

SELECT * FROM item

/

--我们现在可以通过视图来操作数据

INSERT INTO item_view VALUES(item_type('i101',15,50));

--MAKE_REF()

--关系主表

CREATE TABLE itemfile

(

itemcode   VARCHAR2(5) PRIMARY KEY,

itemdesc   VARCHAR2(20),

p_category VARCHAR2(20),

qty_hand   NUMBER(5),

re_level   NUMBER(5),

max_level  NUMBER(5),

itemrate   NUMBER(9,2)

);

--关系从表

CREATE TABLE order_detail

(

orderno  VARCHAR2(5),

itemcode VARCHAR2(5),

qty_ord  NUMBER(5),

qty_deld NUMBER(5)

);

--PL/SQL表和记录---------------------------------------------------//

SET SERVEROUTPUT ON;

DECLARE

TYPE rec_emp IS RECORD  --定义记录

(

no   emp.empno%TYPE,

name emp.ename%TYPE

);

TYPE tab_emp IS TABLE OF rec_emp  --定义 PL/SQL 表

INDEX BY binary_integer;

i NUMBER :=1;

temp_emp tab_emp;  --定义 PL/SQL 表的变量

CURSOR cur_emp IS

SELECT empno,ename FROM emp;

BEGIN

OPEN cur_emp;

FETCH cur_emp INTO temp_emp(i);

LOOP

EXIT WHEN cur_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(temp_emp(i).no ||'  '|| temp_emp(i).name);

i := i +1;

FETCH cur_emp INTO temp_emp(i);

END LOOP;

DBMS_OUTPUT.PUT_LINE('总计打印了 '|| temp_emp.count ||' 条记录');

CLOSE cur_emp;

END;

/

----------------------------------------------------------------------------------End

posted on 2008-09-28 21:41 henry1451 阅读(595) 评论(1)  编辑  收藏