from:
http://www.javaeye.com/topic/79108
--[4]
-------------------------------------------------------------------------------------
--顯示游標---------------------------------------------------------
--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; --第一次定位讀取數(shù)據(jù)，并保存在變量
--循環(huán)讀取數(shù)據(jù)
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; --關(guān)閉游標
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; --更新當前數(shù)據(jù)
END IF;
FETCH cur_emp INTO v_sal;
END LOOP;
DBMS_OUTPUT.PUT_LINE('命令執(zhí)行完畢');
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;
/
--游標在三種循環(huán)中的使用-------------------------------------------
--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 循環(huán)時，不能顯式的使用 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
--創(chuàng)建對象類型（相當于C中的結(jié)構(gòu)體，可實現(xiàn)代碼重用機制）---
--**注意：OR REPLACE表示將覆蓋此用戶下的同名對象類型，在不熟悉數(shù)據(jù)庫結(jié)構(gòu)的時候不要濫用
CREATE OR REPLACE TYPE t_score AS OBJECT
(
java number(5,2),
net number(5,2)
)
/
--可指定類型名稱直接創(chuàng)建對象表
CREATE TABLE score OF t_score;
--或應(yīng)用于表中
CREATE TABLE student
(
id VARCHAR2(4),
name VARCHAR2(20),
score t_score
);
--為上表插入數(shù)據(jù)（利用構(gòu)造函數(shù)）
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
--或者可以創(chuàng)建更為復雜的對象類型，即類型嵌套
CREATE OR REPLACE TYPE t_stu AS OBJECT
(
id VARCHAR2(4),
name VARCHAR2(20),
score t_score
)
/
--應(yīng)用于表中
CREATE TABLE student
(
stu_base t_stu,
teacher varchar2(20)
);
--查看表結(jié)構(gòu)
SET DESC DEPTH ALL; --指定查看層次，否則只能看到第一層
DESC student;
--插入數(shù)據(jù)
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;
--繼承-------------------------------------------------------------
--創(chuàng)建一個不可被繼承的類型
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
)
/
--創(chuàng)建一個不可被實例化、不可被繼承的類型
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;
/
--嵌套表（表中之表）-------------------------------------------------
--創(chuàng)建類型，（以下實例將創(chuàng)建一組動物飼養(yǎng)員嵌套表）
CREATE TYPE animal_ty AS OBJECT
(
breed VARCHAR2(25),
name VARCHAR2(25),
birthdate DATE
);
/
--此類型將用作一個嵌套表的基礎(chǔ)類型
CREATE TYPE animal_nt AS TABLE OF animal_ty;
/
--創(chuàng)建嵌套表
CREATE TABLE breeder
(
breedername VARCHAR2(25),
animals animal_nt
)
NESTED TABLE animals STORE AS animals_nt_tab; --animals_nt_tab代表別名
--插入數(shù)據(jù)
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所養(yǎng)的動物
SELECT breed,name,birthdate
FROM TABLE(SELECT animals FROM breeder WHERE breedername='Jane');
--可變數(shù)組（類似于嵌套表，概念上講它是限定了行集合的嵌套表）----------
--創(chuàng)建類型（以下實例將創(chuàng)建一組聯(lián)系人嵌套表）
CREATE TYPE comm_info AS OBJECT
(
no NUMBER(3), --通訊類型號
comm_TYPE VARCHAR2(20), --通訊類型
comm_no VARCHAR2(30) --號碼
)
/
--創(chuàng)建可變數(shù)組
CREATE TYPE comm_info_list AS VARRAY(50) OF comm_info;
/
--創(chuàng)建表
CREATE TABLE user_info
(
user_id NUMBER(6), --用戶ID
user_name VARCHAR2(20), --用戶名
user_comm comm_info_list --與用戶聯(lián)系的通訊方式
);
--插入數(shù)據(jù)
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;
--對象表-----------------------------------------------------------
--創(chuàng)建對象
CREATE OR REPLACE TYPE address AS OBJECT
(
id NUMBER(4),
street VARCHAR2(50),
state VARCHAR2(2),
zip VARCHAR2(11)
)
/
--創(chuàng)建對象表
CREATE TABLE address_table OF address;
--插入數(shù)據(jù)
INSERT INTO address_table
VALUES(1,'Oracle way','US','90001');
--或使用構(gòu)造函數(shù)
INSERT INTO address_table
VALUES(address(2,'Microsoft way','US','80863'));
--查詢數(shù)據(jù)
SELECT * FROM address_table;
--VALUE關(guān)鍵字：以對象表別名做參數(shù)，返回對象實例
SELECT VALUE(a) FROM address_table a;
--REF數(shù)據(jù)類型：在關(guān)系表中關(guān)聯(lián)對象
CREATE TABLE employee_location
(
empno NUMBER,
loc_ref REF address SCOPE IS address_table --此列引用了類型address
);
--查看結(jié)構(gòu)
SET DESC DEPTH ALL;
DESC employee_location;
--REF()函數(shù)：將引用對象表中的數(shù)據(jù)插入
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;
--查詢
--注：用此語句查詢的結(jié)果是未解析過的REF數(shù)據(jù)
SELECT * FROM employee_location
--DEREF()：解析REF數(shù)據(jù)，返回真正指向的實例
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;
--再查看：已經(jīng)將懸空的REF清除
SELECT * FROM employee_location;
--對象視圖---------------------------------------------------------
--創(chuàng)建表--關(guān)系表
CREATE TABLE item
(
item_code VARCHAR2(10),
item_hand NUMBER(10),
item_sode NUMBER(10)
);
--創(chuàng)建對象--使用相同列
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
/
--我們現(xiàn)在可以通過視圖來操作數(shù)據(jù)
INSERT INTO item_view VALUES(item_type('i101',15,50));
--MAKE_REF()
--關(guān)系主表
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)
);
--關(guān)系從表
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 閱讀(674)
評論(1) 編輯 收藏