from:
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 = 20 FOR 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 < 2000 THEN
- UPDATE emp SET sal = 2000 WHERE 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
- SELECT 101,REF(a)
- FROM address_table a WHERE id = 1;
- INSERT INTO employee_location
- SELECT 102,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