posts - 60,comments - 71,trackbacks - 0
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
  • posted on 2008-09-28 21:41 henry1451 阅读(659) 评论(1)  编辑  收藏

    FeedBack:
    # re: Oracel 游标Cursor的使用(转)
    2009-03-02 15:07 | 包装机械
    ok hen hao  回复  更多评论
      

    只有注册用户登录后才能发表评论。


    网站导航: