欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracle 开发 - 4 博客分类: 开发 OracleSQL数据结构OOPMicrosoft 

程序员文章站 2024-03-21 15:49:40
...
--[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//