SELECT * from emp;
SELECT * from dept;
--过程
CREATE OR REPLACE PROCEDURE update_sal(NAME VARCHAR2, newsal NUMBER)
IS
BEGIN
UPDATE emp SET sal=newsal WHERE lower(ename)=lower(NAME);
END;
--函数
CREATE OR REPLACE FUNCTION annual_income(NAME VARCHAR2)
RETURN NUMBER IS
annual_salary NUMBER(7,2);
BEGIN
SELECT sal * 12 + nvl(comm,0) INTO annual_salary FROM emp WHERE lower(ename)=lower(NAME);
RETURN annual_salary;
END;
--触发器
CREATE OR REPLACE TRIGGER update_cascade
AFTER UPDATE OF deptno ON dept
FOR EACH ROW
BEGIN
UPDATE emp SET deptno =:new.deptno WHERE deptno =:old.deptno;
END;
--包
CREATE OR REPLACE PACKAGE emp_pkg IS
PROCEDURE update_sal(NAME VARCHAR2, newsal NUMBER);
FUNCTION annual_income(NAME VARCHAR2) RETURN NUMBER;
END;
--包体
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
PROCEDURE update_sal(NAME VARCHAR2, newsal NUMBER)
IS
BEGIN
UPDATE emp SET sal=newsal WHERE lower(ename)=lower(NAME);
END;
FUNCTION annual_income(NAME VARCHAR2) RETURN NUMBER
IS
annual_salary NUMBER(7,2);
BEGIN
SELECT sal * 12 + nvl(comm,0) INTO annual_salary FROM emp WHERE lower(ename)=lower(NAME);
RETURN annual_salary;
END;
END;
--复合变量
DECLARE
TYPE emp_record_type IS RECORD(
NAME emp.ename%TYPE,
salary emp.sal%TYPE,
title emp.job%TYPE);
emp_record emp_record_type;
BEGIN
SELECT ename,sal,job INTO emp_record FROM emp WHERE empno=7788;
dbms_output.put_line('雇员名:'||emp_record.name);
END;
--PL/SQL表
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
BEGIN
SELECT ename INTO ename_table(-1) FROM emp WHERE empno=7788;
dbms_output.put_line('雇员名:'||ename_table(-1));
END;
--嵌套表
CREATE OR REPLACE TYPE emp_type AS OBJECT(
NAME VARCHAR2(10),
salary NUMBER(6,2),
hiredate DATE);
CREATE OR REPLACE TYPE emp_array IS TABLE OF emp_type;
CREATE TABLE department(deptno NUMBER(2),dname VARCHAR2(10),employee emp_array)
NESTED TABLE employee STORE AS employee;
SELECT * from department;
SELECT * from employee;
DECLARE
dvalue emp_array;
BEGIN
dvalue :=emp_array();
dvalue.extend(3);
dvalue(1) :=emp_type('张三',100,to_date('2011-2-22','yyyy-mm-dd'));
dvalue(2) :=emp_type('李四',100,to_date('2011-2-22','yyyy-mm-dd'));
dvalue(3) :=emp_type('王五',100,to_date('2011-2-22','yyyy-mm-dd'));
INSERT INTO department VALUES(2,'部门2',dvalue);
END;
--VARRAY
CREATE TYPE article_type AS OBJECT(titile VARCHAR2(20),pubddate DATE);
CREATE TYPE article_array IS VARRAY(20) OF article_type;
CREATE TABLE author(ID NUMBER(6), NAME VARCHAR2(20),article article_array);
SELECT * from author;
DECLARE
dvalue article_array;
BEGIN
dvalue :=article_array();
dvalue.extend(1);
dvalue(1) :=article_type('张三',to_date('2011-2-22','yyyy-mm-dd'));
INSERT INTO author VALUES(2,'部门2',dvalue);
END;
--REF CURSOR
DECLARE
TYPE c1 IS REF CURSOR;
emp_cursor c1;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
OPEN emp_cursor FOR SELECT ename,sal FROM emp WHERE deptno=10;
LOOP
FETCH emp_cursor INTO v_ename,v_sal;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(v_ename);
END LOOP;
CLOSE emp_cursor;
END;
--REF obj_type
CREATE OR REPLACE TYPE home_type AS OBJECT(
street VARCHAR2(50),
city VARCHAR2(20),
state VARCHAR2(20),
zipcode VARCHAR2(6),
owner VARCHAR2(10)
);
CREATE TABLE homes OF home_type;
INSERT INTO homes VALUES('华能路','济南','山东','250100','司纪满');
SELECT * from homes;
CREATE TABLE person(ID NUMBER(6) PRIMARY KEY,NAME VARCHAR2(10), addr REF home_type);
INSERT INTO person SELECT 1,'司纪满',REF(p) FROM homes p WHERE p.owner='司纪满';
SELECT * from person;
--批量插入
INSERT FIRST--ALL
WHEN deptno =10 THEN INTO dept10
WHEN deptno =20 THEN INTO dept20
WHEN deptno =30 THEN INTO dept30
WHEN ename='SCOTT' THEN INTO scott
ELSE INTO OTHER
SELECT * from emp;
--只读事务
SET TRANSACTION READ ONLY;--当设置只读事务时，该语句必须是事务开始的第一条语句
--may not perform insert/delete/update operation inside a READ ONLY transaction
--顺序事务
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--使用rollup
SELECT deptno, job, AVG(sal) FROM emp GROUP BY ROLLUP (deptno,job);
--使用cube
SELECT deptno, job, AVG(sal) FROM emp GROUP BY CUBE (deptno,job);
--使用grouping函数
SELECT deptno, job, AVG(sal),grouping(deptno),grouping(job) FROM emp GROUP BY CUBE (deptno,job);
--使用grouping sets
SELECT deptno,job,AVG(sal) FROM emp GROUP BY GROUPING SETS(deptno,job);
--相等连接
--不等连接
--自连接
--内连接和外连接
SELECT talbe1.column1,table2.column2 FROM table1 [INNER|LEFT|RIGHT|FULL] JOIN table2 ON table1.column1=table2.column2;
--如果主表的主键列和从表的外部键列名称相同，那么可以使用natural join关键字自动执行内连接操作。
--UNION、UNOIN ALL、INTERSECT、MINUS
--层次查询
START WITH condition CONNECT BY condition;
START WITH 用于指定层次查询的根行
CONNECT BY 用于指定父行与子行之间的关系。在condition中必须使用PRIOR引用父行。
--查看历史数据
SELECT ename,sal FROM emp AS OF TIMESTAMP to_timestamp('2011-2-23 18:15:00','yyyy-mm-dd hh24:mi:ss');
--正则表达式
SELECT regexp_replace(ename,'(.)','/1 ') FROM emp;
SELECT regexp_substr('http://www.oracle.com/products','http://([[:alnum:]]+/.?){3,4}/?') FROM dual;
--时间函数
SELECT add_months(SYSDATE,-1) FROM dual;
SELECT current_date FROM dual;
SELECT current_timestamp FROM dual;
SELECT DBTIMEZONE FROM dual;
SELECT extract(YEAR FROM SYSDATE) YEAR FROM dual;
SELECT from_tz(TIMESTAMP '2003-03-28 08:00:00','3:00') FROM dual;
SELECT last_day(SYSDATE) FROM dual;
SELECT localtimestamp FROM dual;
SELECT months_between(SYSDATE,SYSDATE-300) FROM dual;
--转换函数
SELECT asciistr('中国') FROM dual; --将任意字符集的字符串转换成数据库字符的ASCII字符串
SELECT bin_to_num(1,0,1,1,1) FROM dual;
SELECT chartorowid('') FROM dual;
SELECT rowidtochar('') FROM dual;
SELECT compose( 'u' || UNISTR('/0308')) FROM dual; --将输入字符串转变为UNICODE字符串
SELECT decompose('ü') FROM dual;
SELECT hextoraw('AB56FA2C') FROM dual; --将十六进制字符串转变为RAW数据类型
SELECT rawtohex('AB56FA2C') FROM dual;
SELECT rawtonhex('7d') FROM dual;
SELECT ename,scn_to_timestamp(ORA_ROWSCN) FROM emp;
SELECT timestamp_to_scn(hiredate) FROM emp;
SELECT to_char(n'中华人民共和国') FROM dual;
SELECT to_char(SYSDATE,'yyyy-mm-dd') FROM dual;
SELECT to_char(-10000,'L99G999D99MI') FROM dual;
SELECT to_clob(n'中华人民共和国') FROM dual;
SELECT to_lob(-) FROM dual;
SELECT to_multi_byte('abcd') FROM dual;
SELECT to_single_byte('ａｂｃｄ') FROM dual;
SELECT to_nchar(SYSDATE) FROM dual;
SELECT to_nclob('伟大的祖国') FROM dual;
SELECT to_number('1234') FROM dual;
--集合函数
SELECT deptno,cardinality(employee) FROM department; --用于返回嵌套表的实际元素的个数
--单行函数
--BFILENAME('directory','filename') --该函数用于初始化BFILE定位符
CREATE OR REPLACE DIRECTORY USER_DIR AS '/home/oracle';
DECLARE
v_content VARCHAR2(100);
v_bfile BFILE;
amount INT;
offset INT:=1;
BEGIN
v_bfile :=bfilename('USER_DIR','test.txt');
amount :=dbms_lob.getlength(v_bfile);
dbms_lob.fileopen(v_bfile);
dbms_lob.read(v_bfile,amount,offset,v_content);
dbms_lob.fileclose(v_bfile);
dbms_output.put_line(v_content);
END;
--coalesce(expr1[,expr2[,expr3]...)--用于返回表达式列表中第一个NOT NULL表达式的结果
DECLARE
v_expr1 INT;
v_expr2 INT :=100;
v_expr3 INT :=1000;
v_nn INT;
BEGIN
v_nn :=coalesce(v_expr1,v_expr2,v_expr3);
dbms_output.put_line(v_nn);
END;
--decode(expr,search1,result1[,search2,result2..][,defaults];
SELECT DUMP('hello',1016) FROM dual;
--XML
SELECT EXISTSNODE(VALUE(p), '/purchaseorder/user') FROM xmltable p; --判断XML节点是否存在
SELECT extract(VALUE(p), '/purchaseorder/user') FROM xmltable p; --用于返回XML节点路径下的相应内容
SELECT extracevalue(VALUE(p), '/purchaseorder/user') FROM xmltable p; --用于返回XML节点路径下的值
SELECT any_path,path(1),depth(2) FROM resource_view WHERE under_path(res,'/sys/schemas/PUBLIC',1)=1 AND under_path(res,'/sys/schemas/PUBLIC',2)=1;
--depth(n) 用于返回XML方案中UNDER_PATH路径所对应的相对层数
--path(n) 用于返回特定XML资源所对应的相对路径
--字符集
SELECT nls_charset_decl_len(200,nls_charset_id('ZHS16GBKFIXED')) FROM dual; --返回字节数在特定字符集中占用的字符个数
SELECT nls_charset_id('ZHS16GBKFIXED') FROM dual; --返回字符集的ID号
SELECT nls_charset_name(852) FROM dual; --返回特定ID号所对应的字符集名
SELECT nullif(100,100) FROM dual; --二者相等返回NULL不等返回V1
SELECT lpad(' ', 2*LEVEL-1) || sys_connect_by_path(ename,'/') "Path" FROM emp START WITH ename='SCOTT' CONNECT BY PRIOR empno=mgr;
--对象函数
CREATE TYPE cust_address_typ_new AS OBJECT
(street_address VARCHAR2(40),
postal_code VARCHAR2(10),
city VARCHAR2(30),
state_provice VARCHAR2(10),
country_id VARCHAR2(2));
CREATE TABLE address_table OF cust_address_typ_new;
CREATE TABLE customer_address(
add_id NUMBER,
address REF cust_address_typ_new SCOPE IS address_table);
INSERT INTO address_table VALUES('1 First','G45 EU8','Paris','CA','US');
INSERT INTO customer_address SELECT 999,REF(a) FROM address_table a;
SELECT DEREF(address).city FROM customer_address;
SELECT make_ref() FROM dual;
SELECT REF(e) FROM address_table e;
SELECT VALUE(e).city FROM address_table e;
--访问数据库
--检索单行数据
SELECT select_list INTO variable_name[,variable_name]...|record_name FROM table1 WHERE condition;
--异常:(1)NO_DATA_FOUND (2)TOO_MANY_ROWS
--在where条件中使用变量时注意，变量名不能和列名相同，否则会触发TOO_MANY_ROWS错误
--SQL游标四种属性
/*当执行select、update、insert、delete语句时Oracle Server会为这些SQL语句分配相应的上下文区。并且Oracle使用上下文区解析并执行
相应的SQL语句，而游标是指向上下文区的指针。*/
--1.SQL%ISOPEN
--2.SQL%FOUND
--3.SQL%NOTFOUND
--4.SQL%ROWCOUNT
--事务控制语句
--COMMIT,ROLLBACK,SAVEPOINT
--编写控制结构
--条件分支语句
IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE statements]
END IF;
--case语句
CASE selector
WHEN expression1 THEN statements1;
WHEN expression2 THEN statements2;
...
WHEN expressionN THEN statementsN;
[ELSE statementsN+1;]
END CASE;
--循环语句
LOOP
statement1;
...
EXIT [WHEN condition];
END LOOP;
WHILE contition LOOP
statement1;
statemwnt2;
...
END LOOP;
FOR counter IN [REVERSE] lower_bound..upper_bound LOOP
statement1;
statemwnt2;
...
END LOOP;
--嵌套循环与标号
DECLARE
results INT;
BEGIN
<<OUTER>>
FOR i IN 1..100 LOOP
<<inter>>
FOR j IN 1..100 LOOP
results :=i*j;
EXIT OUTER WHEN results =1000;
EXIT WHEN results=500;
END LOOP inter;
dbms_output.put_line('inter' ||results);
END LOOP OUTER;
dbms_output.put_line('outer' || results);
END;
--顺序控制语句
GOTO label_name;
NULL;
--使用复合数据类型
--记录类型(处理单行多列数据)
--定义
TYPE type_name IS RECORD(filed_desc1[,filed_desc2]..); --自定义记录类型
identifier1 table_name%ROWTYPE; --使用%ROWTYPE属性定义记录变量
--使用
SELECT ename,sal,deptno INTO emp_record FROM emp WHERE ...;
INSERT INTO dept VALUES dept_record;
UPDATE dept SET ROW=dept_record WHERE ...;
--集合(处理多行单列)
--1.索引表(不可以做为表列的数据类型)
--定义
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY key_type; --key_type可以为PLS_INTEGER、BINARY_INTEGER和VARCHAR2
--使用
area_table area_table_type;
area_table('北京') :=1;
--2.嵌套表(下标从１开始,元素个数无限制)(可以做为表列的数据类型)
--定义
TYPE type_name IS TABLE OF elment_type;
--使用
ename_table :=ename_table_type('张三');
CREATE TYPE phone_type IS TABLE OF VARCHAR2(20);
CREATE TABLE employee(ID NUMBER,NAME VARCHAR2(20),phone phone_type) NESTED TABLE phone STORE AS phone_table;
INSERT INTO employee VALUES(1,'SCOTT',phone_type('0531-63211616','15964010918'));
phone_table phone_type;
SELECT phone INTO phone_table FROM employee;
FOR i IN 1..phone_table.COUNT LOOP
phone_table(i);
END LOOP;
--3.变长数组(VARRAY)(可以做为表列的数据类型)
--定义(元素的最大个数为size_limit)
TYPE type_name IS VARRAY(size_limit) OF elment_type [NOT NULL];
--使用(与嵌套表一致)
ename_table ename_table_type:=ename_table_type('张三');
CREATE TYPE phone_type IS VARRAY(20) ;
CREATE TABLE employee(ID NUMBER,NAME VARCHAR2(20),phone phone_type);
--4.记录表(处理多行多列数据)
--例
DECLARE
TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY binary_integer;
emp_table emp_table_type;
BEGIN
SELECT * INTO emp_table(1) FROM emp WHERE empno=&NO;
dbms_output.put_line('雇员姓名' || emp_table(1).ename);
END;
--5.多级集合
--示例１。使用多级VARRAY
DECLARE
--定义一维VARRAY
TYPE al_varray_type IS VARRAY(10) OF INT;
--定义二维VARRAY
TYPE nal_varray_type IS VARRAY(10) OF al_varray_type;
--初始化二维VARRAY
nv1 nal_varray_type :=nal_varray_type(al_varray_type(58,100,102),al_varray_type(55,6,73),al_varray_type(2,4));
BEGIN
dbms_output.put_line('显示二维数组所有的元素');
FOR i IN 1..nv1.COUNT LOOP
FOR j IN 1..nv1(i).COUNT LOOP
dbms_output.put_line('nv1('||i||','||j||')='||nv1(i)(j));
END LOOP;
END LOOP;
END;
--示例2.使用多级嵌套表
DECLARE
--定义一维嵌套表
TYPE al_table_type IS TABLE OF INT;
--定义二维嵌套表
TYPE nal_table_type IS TABLE OF al_table_type;
--初始化二维嵌套表
nv1 nal_table_type :=nal_table_type(al_table_type(2,4),al_table_type(5,73));
BEGIN
dbms_output.put_line('显示二维嵌套表中数据');
FOR i IN 1..nv1.COUNT LOOP
FOR j IN 1..nv1(i).COUNT LOOP
dbms_output.put_line('nv1('||i||','||j||')='||nv1(i)(j));
END LOOP;
END LOOP;
END;
--示例3.使用多级索引表
DECLARE
--定义一维table
TYPE al_table_type IS TABLE OF INT INDEX BY BINARY_INTEGER;
--定义二维table
TYPE nal_table_type IS TABLE OF al_table_type INDEX BY BINARY_INTEGER;
--初始化二维table
nv1 nal_table_type;
BEGIN
nv1(1)(1) :=10;
nv1(1)(2) :=5;
nv1(2)(1) :=100;
nv1(2)(2) :=50;
dbms_output.put_line('显示二维table所有元素');
FOR i IN 1..nv1.COUNT LOOP
FOR j IN 1..nv1(i).COUNT LOOP
dbms_output.put_line('nv1('||i||','||j||')='||nv1(i)(j));
END LOOP;
END LOOP;
END;
--6.集合方法
--函数:EXISTS、COUNT、LIMIT、 FIRST、LAST、NEXT、PRIOR（返回的是元素的下标）
--过程:EXTEND(扩展集合元素的尺寸,只适用于嵌套表和VARRAY)、TRIM(只适用于嵌套表和VARRAY)、DELETE(只适用于嵌套表和索引表)
--7.集合赋值
--1.将一个集合的数据赋值给另一个集合
name_array1 :=name_array2;
--2.给集合赋NULL值
name_array :=name_empty;
--3.使用集合操作符给嵌套表赋值
--(1)使用SET操作符 用于取消嵌套表中的重复值
results :=SET(nt_table);
--(2)使用MULTISET UNION操作符
results :=nt1 MULTISET UNION nt2;
--(3)使用MULTISET UNION DISTINCT操作符
results :=nt1 MULTISET UNION DISTINCT nt2;
--(4)使用MULTISET INTERSECT操作符
results :=nt1 MULTISET INTERSECT nt2;
--(5)使用MULTISET EXCEPT操作符
results :=nt1 MULTISET EXCEPT nt2;
--比较集合
--1.检测集合是否为NULL
IS NULL
--2.比较嵌套表是否相同
= 和 !=
--3.在嵌套表上使用集合操作符
cardinality(nt1); --返回嵌套表变量的元素个数
IF n1 submultiset OF n2; --用于确定一个嵌套表是否为另一个嵌套表的子集。
IF v1 MEMBER OF nt1; --检测特定数据是否为嵌套表的元素
IF nt1 IS a SET ; --检测嵌套表中是否包含重复的元素值
--批量绑定
--FORALL语句
FORALL i IN low_bound..upper_bound sql_statment;
FORALL i IN INDICES OF collection [BETWEEN lower_bound AND upper_bound] sql_statment; --用于跳过NULL集合元素
FORALL i IN VALUES OF index_collection sql_statment;
--例
FORALL i IN 1..id_table.COUNT INSERT INTO demo VALUES(id_table(i),name_table(i));
FORALL i IN INDICES OF id_table DELETE FROM demo WHERE ID=id_table(i);
DECLARE
TYPE id_table_type IS TABLE OF demo.id%TYPE;
TYPE name_table_type IS TABLE OF demo.name%TYPE;
id_table id_table_type;
name_table name_table_type;
TYPE index_pointer_type IS TABLE OF PLS_INTEGER;
index_pointer index_pointer_type;
BEGIN
SELECT * BULK COLLECT INTO id_table,name_table FROM demo;
index_pointer :=index_pointer_type(2,3,5);
FORALL i IN VALUES OF index_pointer
INSERT INTO new_demo VALUES(id_table(i),name_table(i));
END;
--使用SQL%BULK_ROWCOUNT(i)属性 用于取得在执行批量绑定操作时第i个元素所作用的行数。
--BULK COLLECT子句
--1在select into语句中使用BULK COLLECT 子句
SELECT * BULK COLLECT INTO emp_table FROM emp;
--2在DML的返回子句中使用BULK COLLECT子句
DELETE FROM emp RETURNING ename BULK COLLECT INTO ename_table;
--显示游标
--1.使用显示游标 DECLARE-->OPEN-->FETCH<-->空?-->CLOSE
--(1)定义游标
CURSOR cursor_name IS select_statement;
--(2)打开游标
OPEN cursor_name;
--(3)提取数据
FETCH cursor_name INTO variable1,variable2...
FETCH cursor_name BULK COLLECT INTO collect1,collect2,..[LIMIT ROWS];
--(4)关闭游标
CLOSE cursor_name;
--2.显示游标属性
--(1)%ISOPEN
--(2)%FOUND
--(3)%NOTFOUND
--(4)%ROWCOUNT
DECLARE
CURSOR emp_cursor IS SELECT ename FROM emp WHERE deptno=10;
TYPE ename_table_type IS TABLE OF VARCHAR2(10);
ename_table ename_table_type;
BEGIN
IF NOT emp_cursor%ISOPEN THEN
OPEN emp_cursor;
END IF;
FETCH emp_cursor BULK COLLECT INTO ename_table;
dbms_output.put_line('提取的总计行数:'||emp_cursor%ROWCOUNT);
FOR i IN 1..ename_table.COUNT LOOP
dbms_output.put_line(ename_table(i));
END LOOP;
CLOSE emp_cursor;
END;
--基于游标定义记录变量 %ROWTYPE
--参数游标
CURSOR cursor_name(parameter_name datatype) IS select_statement;
--使用游标更新或删除数据
--在定义游标时必须要带for update子句。
CURSOR cursor_name(parameter_name datatype) IS select_statement FOR UPDATE [OF column_reference][NOWAIT];
UPDATE table_name SET column1=.. WHERE CURRENT OF cursor_name;
DELETE FROM table_name WHERE CURRENT OF cursor_name;
--在特定表上加行共享锁
CURSOR cursor_name IS SELECT ename,sal,dname,emp.deptno FROM emp,dept WHERE emp.deptno=dept.deptno FOR UPDATE OF emp.deptno;
--游标FOR循环
FOR record_name IN cursor_name LOOP
statement1;
statement2;
...
END LOOP;
--使用游标变量
--1.使用步骤
--(1)定义REF CURSOR类型和游标变量
TYPE ref_type_name IS REF CURSOR [RETURN return_type];
cursor_variable ref_type_name;
--(2)打开游标
OPEN cursor_variable FOR select_statement;
--(3)提取数据
FETCH cursor_variable INTO variable1,variable2...
FETCH cursor_variable BULK COLLECT INTO collect1,collect2...[LIMIT ROWS];
--(4)关闭游标
CLOSE cursor_variable;
--使用CURSOR表达式
SELECT a.dname,CURSOR(SELECT ename,sal FROM emp WHERE deptno=a.deptno) FROM dept a;
DECLARE
TYPE refcursor IS REF CURSOR;
CURSOR dept_cursor(NO NUMBER) IS SELECT a.dname,CURSOR(SELECT ename,sal FROM emp WHERE deptno=a.deptno) FROM dept a WHERE a.deptno=NO;
empcur refcursor;
v_dname dept.dname%TYPE;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
OPEN dept_cursor(&NO);
LOOP
FETCH dept_cursor INTO v_dname,empcur;
EXIT WHEN dept_cursor%NOTFOUND;
dbms_output.put_line('部门名:'||v_dname);
LOOP
FETCH empcur INTO v_ename,v_sal;
EXIT WHEN empcur%NOTFOUND;
dbms_output.put_line('雇员名:'||v_ename||',工资:'||v_sal);
END LOOP;
END LOOP;
CLOSE dept_cursor;
END;
--常用预定义例外
--(1)ACCESS_INTO_NULL
--对应于ORA-06530错误。当开发对象类型应用时，在引用对象属性之前必须初始化对象。如果没有初始化对象直接为对象属性赋值就会引发该例外。
--(2)CASW_NOT_FOUND
--在编写CASE语句时，如果在WHEN子句中没有包含必须的条件分支，并且没有包含ELSE分支，就会引发该例外。
--(3)COLLECTION_IS_NULL
--在集合元素(嵌套表或VARRAY类型)赋值前必须先初始化集合元素，否则就会引发该例外。
--(4)CURSOR_ALREADY_OPEN
--当重新打开已经打开的游标时会引发该例外。
--(5)DUP_VAL_ON_INDEX
--当在惟一索引所对应的列上键入重复值时会引发该例外。
--(6)INVAID_CURSOR
--当试图在不合法的游标上执行操作时会引发该例外。
--(7)INVALID_NUMBER
--当内嵌SQL语句不能有效将字符转换成数字时会引发该例外。
--(8)NO_DATA_FOUND
--当执行SELECT INTO未返回行，或者引用了索引表未初始化的元素时会引发该例外
--(9)TOO_MANY_ROWS
--当执行SELECT INTO返回多行数据时则会引发该例外。
--(10)ZERO_DIVIDE
--如果使用数字除0则会引发该例外。
--(11)SUBSCRIPT_BEYOND_COUNT
--当使用嵌套表或VARRAY元素时，如果元素的下标超出了嵌套表或VARRAY元素的范围则会引发该例外。
--(12)SUBSCRIPT_OUTSIDE_LIMIT
--当使用嵌套表或VARRAY元素时，如果元素的下标为负值时会引发该例外。
--(13)VALUE_ERROR
--如果变量不足以容纳实际数据则会引发该例外。
--其它预定义例外
--LOGIN_DENIED
--连接数据库时如果提供了不正确的用户名或密码时会引发该例外。
--NOT_LOGGED_ON
--如果应用程序没有连接到数据库那么在执行PL/SQL块中访问数据库时会引发该例外。
--PROGRAM_ERROR
--如果出现该错误则表示存在PL/SQL内部问题，用户此时可能需要重新安装数据字典和PL/SQL系统包。
--ROWTYPE_MISMACH
--当执行赋值时如果宿主游标变量和PL/SQL游标变量的返回类型不一兼容会引发该例外。
--SELF_IS_NULL
--当使用对象类型时，在NULL实例上调用成员方法会引发该例外。
--处理非预定义例外
--步骤
--定义例外 关联例外和错误 引用例外
DECLARE
e_integrity EXCEPTION;
PRAGMA Exception_Init(e_integrity, -2291);
BEGIN
UPDATE emp SET deptno=&dno WHERE empno=&eno;
EXCEPTION
WHEN e_integrity THEN
dbms_output.put_line('该部门不存在');
END;
--处理自定义例外
--步骤
--定义例外 显示触发例外 引用例外
DECLARE
e_integrity EXCEPTION;
PRAGMA Exception_Init(e_integrity, -2291);
e_no_employee EXCEPTION;
BEGIN
UPDATE emp SET deptno=&dno WHERE empno=&eno;
IF SQL%NOTFOUND THEN
RAISE e_no_employee;
END IF;
EXCEPTION
WHEN e_integrity THEN
dbms_output.put_line('该部门不存在');
WHEN e_no_employee THEN
dbms_output.put_line('该雇员不存在');
END;
--使用例外函数
--1.SQLCODE和SQLERRM
--2.RAISE_APPLICATION_ERROR
--在PL/SQL应用程序中自定义错误消息。该过程只能在数据库端的子程序（过程、函数、包、触发器）中使用。
raise_application_error(err_number,message[,TRUE|FALSE]);
err_number必须是-20000到-20999之间的负数
CREATE OR REPLACE PROCEDURE raise_comm(eno NUMBER,commission NUMBER)
IS
v_comm emp.comm%TYPE;
BEGIN
SELECT comm INTO v_comm FROM emp WHERE empno=eno;
IF v_comm IS NULL THEN
raise_application_error(-20001,'该雇员不无补助');
END IF;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('该雇员不存在');
END;
--开发过程
CREATE [OR REPLACE] PROCEDURE procedure_name(argument1 [mode1] datatype1,argument2 [mode1] datatype2,...)
IS [AS]
PL/SQL BLOCK;
--开发函数
CREATE [OR REPLACE] FUNCTION function_name(argument1 datatype1,argument2 datatype2,...)
RETURN datatype
IS [AS]
PL/SQL Blocks;
--列出子程序编译错误
show ERRORS PROCEDURE raise_salary;
SELECT * from user_errors WHERE NAME='raise_salary';
--列出对象依赖关系
--无论是直接依赖还是间接依赖，当修改了被引用的对象的结构时，都会使得相关依赖对象转变为INVALID状态。
--(1)使用USER_DEPENDENCIES确定直接依赖关系。
SELECT NAME,TYPE FROM user_dependencies t WHERE t.referenced_name='EMP';
--(2)使用工具视图DEPTREE和IDEPTREE确定直接依赖和间接依赖关系
--首先运行SQL脚本utldtree.sql来建立这两个视图和过程DEPTREE_FILL然后调用过程DEPTREE_FILL填充这两个视图
@%ORACLE_HOME%/rdbms/ADMIN/utldtree
EXEC DEPTREE_FILL('TABLE','SCOTT','EMP');
SELECT nested_level,NAME,TYPE FROM deptree;
SELECT * from ideptree;
--(3)重新编译子程序
ALTER PROCEDURE ** COMPILE;
ALTER VIEW ** COMPILE;
ALTER FUNCTION ** COMPILE;
--开发包
--1.建立包头
CREATE OR REPLACE PACKAGE package_name
IS | AS
PUBLIC TYPE AND item declarations
subprogram specifications
END package_name;
--2.建立包体
CREATE OR REPLACE PACKAGE BODY package_name
IS | AS
PRIVATE TYPE AND item declarations
subprogram bodies
END package_name;
--3.调用包组件
--对于包的私有组件，只能在包内调用，并且可以直接调用，对于包的公用组件，可以在包外调用，须加包名作为前缀。
--4.删除包
DROP PACKAGE package_name;
--5.使用包重载
--重载批多个具有相同名称的子程序。
--6.使用包构造过程
--当在会话内第一次调用包的公用组件时会自动执行其构造过程并且该构造过程在同一会话内只会执行一次
--包的构造过程没有任何名称，它是在包体中实现了包中其它过程之后以BEGIN开始，END结束的部分。
--7.使用纯度级别
--当使用包的公用函数时它既可以作为表达式的一部分使用，也可以在SQL语句中使用。但如果要在SQL语句中引用包中的公用函数，
--那么该公用函数不能包含DML语句，也不能读写远程包的变量。
--定义纯度级别语法：
PRAGMA RESTRICT_REFERENCES (function_name,WNDS[,WNPS][,RNDS][,RNPS]);
--WNDS 不能修改数据库数据(即禁止执行DML操作)
--WNPS 不能修改包变量
--RNDS 不能读取数据库数据
--RNPS 不能读取包变量
--触发器
--1.触发事件
--1.启动和关闭例程
--2.Oralce错误信息
--3.用户登录和断开会话
--4.特定表或视图的DML操作
--5.在任何方案上的DDL语句
--2.触发条件
--使用WHEN子句指定一个BOOLEAN表达式，当表达式返回TRUE时会自动执行触发相应代码。
--3.触发操作
--指包含SQL语句和其它执行代码的PL/SQL块。
--代码的大小不能超过32K
--触发器中只能包含select,insert,update,delete语句，而不能包含DDL语句和事务控制语句
--4.创建触发器
--1.触发时机用于指定触发器的触发时间。BEFORE和AFTER
--2.触发事件
--3.表名
--4.触发类型
--5.触发操作
DECLARE
..
BEGIN
..
EXCEPTION
..
END;
--6.DML触发器触发顺序
--(1)DML触发器在单行数据上的触发顺序
--BEFORE语句触发器 BEFORE行触发器 AFTER行触发器 AFTER语句触发器
--(2)DML触发器在多行数据上的触发顺序
--BEFORE语句触发器 BEFORE行触发器 AFTER行触发器 BEFORE行触发器 AFTER行触发器 ..... AFTER语句触发器
--7.语句触发器
CREATE OR REPLACE TRIGGER trigger_name
timing event1 [OR event2 OR event3]
ON table_name
PL/SQL BLOCK;
--timing 用于指定触发时机（BEFORE、AFTER)
--event 用于指定触发事件(INSERT、UPDATE、DELETE)
--条件谓词 INSERTING UPDATING DELETING
--8.行触发器
CREATE OR REPLACE TRIGGER trigger_name
timing event1 [OR event2 OR event3]
ON table_name
[REFERENCING OLD AS OLD | NEW AS NEW]
FOR EACH ROW
[WHEN condition]
PL/SQL BLOCK;
--9.DML触发器使用注意事项
--当编写DML触发器时，触发器代码不能从触发器所对应的基表中读取数据
--10.建立INSTEAD OF触发器
--当视图符合以下任一条件时不允许直接执行DML操作
--1.具有集合操作符(UNION,UNION ALL,INTERSECT,MINUS)
--2.具有分组函数(MIN,MAX,SUM,AVG,COUNT等)
--3.具有GROUP BY,CONNECT BY或START WITH等子句
--4.具有DISTINCT关键字。
--5.具有连接查询
--为了在具有以上情况的复杂视图上执行DML操作必须要基于视图建立INSTEAD-OF触发器。
--建立INSTEAD OF触发器要注意
--1.INSTEAD OF选项只能适用于视图
--2.不能指定BEFORE,AFTER选项
--3.在建立视图时没有指定WITH CHECK OPTION选项
--4.必须指定FOR EACH ROW选项
CREATE OR REPLACE TRIGGER tr_instead_of_dept_emp
INSTEAD OF INSERT ON dept_emp
FOR EACH ROW
DECLARE
v_temp INT;
BEGIN
SELECT COUNT(*) INTO v_temp FROM dept WHERE deptno =:new.deptno;
IF v_temp = 0 THEN
INSERT INTO dept(deptno,dname) VALUES(:new.deptno,:new.dname);
END IF;
SELECT COUNT(*) INTO v_temp FROM emp WHERE empno =:new.empno;
IF v_temp = 0 THEN
INSERT INTO emp(empno,ename,deptno) VALUES( :new.empno,:new.ename,:new.deptno);
END IF;
END;
--11.建立系统事件触发器
--1.常用事件属性函数
ora_client_ip_address
ora_database_name
ora_des_encrypted_password
ora_dict_obj_name
ora_dict_obj_name_list
ora_dict_obj_owner
ora_dict_obj_owner_list
ora_dict_obj_type
ora_grantee
ora_instance_num
ora_is_alter_column
ora_is_creating_nested_table
ora_is_drop_column
ora_is_servererror
ora_login_user
ora_sysevent
--2.建立例程启动和关闭触发器
CREATE OR REPLACE TRIGGER tr_startup
AFTER startup ON DATABASE
BEGIN
--
END;
CREATE OR REPLACE TRIGGER tr_shutdown
BEFORE SHUTDOWN ON DATABASE
BEGIN
--
END;
--12.管理触发器
--1.显示触发器信息
SELECT trigger_name,status FROM user_triggers;
--2.禁用触发器
ALTER TRIGGER trigger_name DISABLE;
--3.激活触发器
ALTER TRIGGER trigger_name ENABLE;
--4.禁用或激活表的所有触发器
ALTER TABLE emp DISABLE ALL TRIGGERS;
ALTER TABLE emp ENABLE ALL TRIGGERS;
--5.重新编译触发器
ALTER TRIGGER trigger_name COMPILE;
--6.删除触发器
DROP TRIGGER trigger_name;
--动态SQL
--处理非查询语句
EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[,define_variable]..|record1}]
[USING [IN | OUT |IN OUT] bind_argument
[, [IN|OUT|IN OUT] bind_argument]...]
[{RETURNING |RETURN } INTO bind_argument[,bind_argument]...];
--1.使用EXECUTE IMMEDIATE处理DDL操作
CREATE OR REPLACE PROCEDURE drop_table(table_name VARCHAR2)
IS
sql_statement VARCHAR2(100);
BEGIN
sql_statement :='DROP TABLE ' || table_name;
EXECUTE IMMEDIATE sql_statement;
END;
--2.使用EXECUTE IMMEDIATE处理DCL操作
CREATE OR REPLACE PROCEDURE grant_sys_priv(priv VARCHAR2,username VARCHAR2)
IS
sql_stat VARCHAR2(100);
BEGIN
sql_stat :='GRANT ' || priv || ' TO ' ||username;
EXECUTE IMMEDIATE sql_stat;
END;
--3.使用EXECUTE IMMEDIATE处理DML操作
--(1)处理无占位符和RETURNING子句的DML语句
DECLARE
sql_stat VARCHAR2(100);
BEGIN
sql_stat :='UPDATE emp SET sal = sal * 1.1 WHERE deptno = 30';
EXECUTE IMMEDIATE sql_stat;
END;
--(2)处理包含占位符的DML语句
DECLARE
sql_stat VARCHAR2(100);
BEGIN
sql_stat :='UPDATE emp SET sal = sal * (1 + :persent/100) WHERE deptno = :dno';
EXECUTE IMMEDIATE sql_stat USING &1,&2;
END;
--(3)处理包含RETURNING子句的DML语句
DECLARE
salary NUMBER(6,2);
sql_stat VARCHAR2(100);
BEGIN
sql_stat :='UPDATE emp SET sal = sal * (1 + :persent/100) WHERE empno = :eno' ||
' RETURNING sal INTO :salary';
EXECUTE IMMEDIATE sql_stat USING 15,7788 RETURNING INTO salary;
dbms_output.put_line('新工资:'||salary);
END;
--4.使用EXECUTE IMMEDIATE处理单行查询
DECLARE
sql_stat VARCHAR2(100);
emp_record emp%ROWTYPE;
BEGIN
sql_stat :='select * from emp where empno =:eno';
EXECUTE IMMEDIATE sql_stat INTO emp_record USING &1;
dbms_output.put_line('雇员:'||emp_record.ename ||'的工资为' ||emp_record.sal);
END;
--处理多行查询语句
--定义游标变量
TYPE cursortype IS REF CURSOR;
cursor_variable cursortype;
--打开游标变量
OPEN cursor_variable FOR dynamic_string [USING bind_argument[,bind_argument]...];
--循环提取数据
FETCH cursor_variable INTO {var1,var2,record1}
--关闭游标变量
CLOSE cursor_variable
--例
DECLARE
TYPE empcurtyp IS REF CURSOR;
emp_cv empcurtyp;
emp_record emp%ROWTYPE;
sql_stat VARCHAR2(100);
BEGIN
sql_stat :='select * from emp where deptno=:dno';
OPEN emp_cv FOR sql_stat USING &1;
LOOP
FETCH emp_cv INTO emp_record;
EXIT WHEN emp_cv%NOTFOUND;
dbms_output.put_line('雇员:'||emp_record.ename ||'的工资为' ||emp_record.sal);
END LOOP;
CLOSE emp_cv;
END;
--在动态SQL中使用BULK子句
--1.在EXECUTE IMMEDIATE语句中使用动态BULK子句
EXECUTE IMMEDIATE dynamic_string
[BULK COLLECT INTO define_variable[,define_variable...]]
[USING bind_argument[,bind_argument...]]
[ {RETURNING | RETURN}
BULK COLLECT INTO return_variable[,return_variable...]];
--(1)使用BULK子句处理DML语句返回子句
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
TYPE sal_table_type IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
sal_table sal_table_type;
sql_stat VARCHAR2(200);
BEGIN
sql_stat :='UPDATE emp set sal = sal * (1 + :persent/100)' ||
' where deptno=:dno' ||
' RETURNING ename,sal into :name,:salary';
EXECUTE IMMEDIATE sql_stat USING &persent,&dno RETURNING BULK COLLECT INTO ename_table,sal_table;
FOR i IN 1..ename_table.COUNT LOOP
dbms_output.put_line('雇员:'||ename_table(i)||'的工资为'||sal_table(i));
END LOOP;
END;
--(2)使用BULK子句处理多行查询
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
sql_stat VARCHAR2(100);
BEGIN
sql_stat :='select ename from emp where deptno=:dno';
EXECUTE IMMEDIATE sql_stat BULK COLLECT INTO ename_table USING &dno;
FOR i IN 1..ename_table.count LOOP
dbms_output.put_line(ename_table(i));
END LOOP;
END;
--2.在FETCH语句中使用BULK子句
FETCH dynamic_cursor BULK COLLECT INTO define_variable[,define_variable...];
--例
DECLARE
TYPE empcurtyp IS REF CURSOR;
emp_cv empcurtyp;
TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
sql_stat VARCHAR2(100);
BEGIN
sql_stat :='select ename from emp where job=:title';
OPEN emp_cv FOR sql_stat USING '&job';
FETCH emp_cv BULK COLLECT INTO ename_table;
FOR i IN 1..ename_table.count LOOP
dbms_output.put_line(ename_table(i));
END LOOP;
CLOSE emp_cv;
END;
--3.在FORALL语句中使用BULK子句
FORALL index1 IN low_bound..upper_bound
EXECUTE IMMEDIATE dynamic_string
USING bind_argument|bind_argument(index1)[,bind_argument|bind_argument(index1)]...
[{RETURNING | RETURN} BULK COLLECT INTO bind_argument[,bind_argument...]];
--例
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
TYPE sal_table_type IS TABLE OF emp.sal%TYPE;
ename_table ename_table_type;
sal_table sal_table_type;
sql_stat VARCHAR2(100);
BEGIN
ename_table :=ename_table_type('SCOTT','SMITH','CLARK');
sql_stat :='UPDATE emp set sal = sal * 1.1 where ename=:1 returning sal into :2';
FORALL i IN 1..ename_table.count
EXECUTE IMMEDIATE sql_stat USING ename_table(i) RETURNING BULK COLLECT INTO sal_table;
FOR j IN 1..ename_table.count LOOP
dbms_output.put_line('雇员'||ename_table(j)||'的工资为'||sal_table(j));
END LOOP;
END;
--使用对象类型
--1.对象类型:为了描述现实世界对象所抽象出来的具体特征，应该涵盖对象所具有的公共特性。
--2.对象实例:对象类型的具体实现,对应于现实世界的具体对象。
--3.对象类型方法
--(1)构造方法:用于初始化对象并返回对象实例
--(2)MEMBER方法:用于访问对象实例的数据，如果在对象类型中需要访问特定对象实例的数据则需定义该方法，可以使用SELF访问当前对象实例.
object_type_instance.method();
--(3)STATIC方法:用于访问对象类型，它用于在对象类型上执行全局操作，而不需要访问特定对象实例的数据。
object_type.method();
--(4)MAP方法:对于标量数据其数据是可以直接进行比较的，但对象类型是不可以直接进行比较的，
--为了按照特定规则排序对象实例的数据，可以在对象类型上定义MAP方法，它可以将对象实例映射为标量类型数据，然后排序。
--(5)ORDER方法:MAP方法可以在多个对象实例间进行排序，而ORDER方法只能比较两个对象实例的大小。
--4.对象表:包含对象类型的表
--行对象
CREATE TABLE employee OF employee_type;
--列对象
CREATE TABLE department(dno NUMBER,dname VARCHAR2(20),emplyee_type);
--5.对象类型继承
--6.REF数据类型
--REF是指向行对象的逻辑指针，是Oracle的一种内置数据类型。建表时通过使用REF引用行对象，可以使不同表共享相同对象，从而降低内在占用。
CREATE TABLE department(dno NUMBER,dname VARCHAR2(20), emp REF empployee_type);
--建立和使用简单对象类型
CREATE OR REPLACE TYPE type_name AS OBJECT(
attribute1 datatype1[,attribute2 datatype2,...],
[MEMBER | STATIC method1 spec , MEMBER | STATIC method2 spec,...]);
CREATE OR REPLACE TYPE BODY type_name AS
MEMBER | STATIC method1 bodies;
MEMBER | STATIC method2 bodies;
...]
--1.建立和使用不包含任何方法的对象类型
CREATE OR REPLACE TYPE person_typ1 AS OBJECT(NAME VARCHAR2(10),gender VARCHAR2(2),birthday DATE);
--(1)建立行对象
CREATE TABLE person_tab1 OF person_typ1;
--插入数据
INSERT INTO person_tab1 VALUES('张三','男',SYSDATE);
INSERT INTO person_tab1 VALUES(person_typ1('李四','女',SYSDATE));
--检索数据
SELECT VALUE(p) FROM person_tab1 p WHERE p.name='张三';
--更新数据
UPDATE person_tab1 p SET p.birthday= SYSDATE -10 WHERE p.name='张三';
--删除数据
DELETE FROM person_tab1 p WHERE p.name='张三';
--2.建立列对象
CREATE TABLE employee_tab2(eno NUMBER, person person_typ1,sal NUMBER(6,2),job VARCHAR2(10));
--插入数据
INSERT INTO employee_tab2(eno,sal,job,person) VALUES(1,2000,'C++',person_typ1('张三','男',SYSDATE));
--检索对象类型列的数据
DECLARE
employee person_typ1;
salary NUMBER(6,2);
BEGIN
SELECT person,sal INTO employee,salary FROM employee_tab2 WHERE eno=1;
dbms_output.put_line('雇员名:'||employee.name);
dbms_output.put_line('雇员工资:'||salary);
END;
--更新对象列数据
UPDATE employee_tab2 p SET p.person.birthday = SYSDATE -10 WHERE eno=1;
--删除
DELETE FROM employee_tab2 p WHERE p.person.NAME='张三';
--建立和使用包含MEMBER方法的对象类型
CREATE OR REPLACE TYPE person_typ2 AS OBJECT(
NAME VARCHAR2(10),gender VARCHAR2(2),birthday DATE,address VARCHAR2(100),
MEMBER PROCEDURE change_address(new_addr VARCHAR2),
MEMBER FUNCTION get_info RETURN VARCHAR2);
CREATE OR REPLACE TYPE BODY person_typ2 IS
MEMBER PROCEDURE change_address(new_addr VARCHAR2)
IS
BEGIN
address :=new_addr;
END;
MEMBER FUNCTION get_info RETURN VARCHAR2
IS
v_info VARCHAR2(100);
BEGIN
v_info :='姓名:'||NAME||',出生日期'||birthday;
RETURN v_info;
END;
END;
--建表
CREATE TABLE employee_tab3(eno NUMBER(6),person person_typ2,sal NUMBER(6,2),job VARCHAR2(10));
INSERT INTO employee_tab3(eno,sal,job,person) VALUES(1,2000,'C++',person_typ2('张三','男',SYSDATE,'济南'));
DECLARE
v_person person_typ2;
BEGIN
SELECT person INTO v_person FROM employee_tab3 WHERE eno =&&NO;
v_person.change_address('北京');
UPDATE employee_tab3 SET person = v_person WHERE eno =&NO;
dbms_output.put_line(v_person.get_info);
END;
--建立和使用包含STATIC方法的对象类型
CREATE OR REPLACE TYPE person_typ4 AS OBJECT (
NAME VARCHAR2(10),gender VARCHAR2(2),birthday DATE,regdate DATE,
STATIC FUNCTION getdate RETURN DATE,
MEMBER FUNCTION get_info RETURN VARCHAR2);
CREATE OR REPLACE TYPE BODY person_typ4 IS
STATIC FUNCTION getdate RETURN DATE IS
BEGIN
RETURN SYSDATE;
END;
MEMBER FUNCTION get_info RETURN VARCHAR2 IS
BEGIN
RETURN '姓名:'||NAME||',注册日期'||regdate;
END;
END;
--建表
CREATE TABLE employee_tab4(eno NUMBER(6),person person_typ4,sal NUMBER(6,2),job VARCHAR2(10));
INSERT INTO employee_tab4(eno,sal,job,person) VALUES(1,2000,'C++',person_typ4('张三','男',SYSDATE,person_typ4.getdate()));
--建立和使用包含MAP方法的对象类型
CREATE OR REPLACE TYPE person_typ5 AS OBJECT(
NAME VARCHAR2(10),gender VARCHAR2(2),birthday DATE,
MAP MEMBER FUNCTION getage RETURN VARCHAR2);
CREATE OR REPLACE TYPE BODY person_typ5 IS
MAP MEMBER FUNCTION getage RETURN VARCHAR2
IS
BEGIN
RETURN trunc((SYSDATE-birthday)/365);
END;
END;
--建表
CREATE TABLE employee_tab5(eno VARCHAR2(6),person person_typ5,sal NUMBER(6,2),job VARCHAR2(10));
--插入
INSERT INTO employee_tab5(eno,sal,job,person) VALUES(1,1500,'C++',person_typ5('张三','女','11-1月-76'));
INSERT INTO employee_tab5(eno,sal,job,person) VALUES(2,2500,'JAVA',person_typ5('李四','男','11-5月-75'));
INSERT INTO employee_tab5(eno,sal,job,person) VALUES(3,3500,'DBA',person_typ5('王五','男','11-5月-70'));
--
DECLARE
TYPE person_table_type IS TABLE OF person_typ5;
person_table person_table_type;
v_temp VARCHAR2(100);
BEGIN
SELECT person BULK COLLECT INTO person_table FROM employee_tab5;
IF person_table(1).getage()>person_table(2).getage THEN
v_temp :=person_table(1).name ||'比'||person_table(2).name||'大';
ELSE
v_temp :=person_table(1).name ||'比'||person_table(2).name||'小';
END IF;
dbms_output.put_line(v_temp);
END;
--建立和使用包含ORDER方法的对象类型
CREATE OR REPLACE TYPE person_typ6 AS OBJECT (
NAME VARCHAR2(10),gender VARCHAR2(2),birthday DATE,
ORDER MEMBER FUNCTION compare(p person_typ6) RETURN INT);
CREATE OR REPLACE TYPE BODY person_typ6 IS
ORDER MEMBER FUNCTION compare(p person_typ6) RETURN INT
IS
BEGIN
CASE
WHEN birthday > p.birthday THEN RETURN 1;
WHEN birthday = p.birthday THEN RETURN 0;
WHEN birthday < p.birthday THEN RETURN -1;
END CASE;
END;
END;
--建表
CREATE TABLE employee_tab6(eno NUMBER(6),person person_typ6,sal NUMBER(6,2),job VARCHAR2(10));
INSERT INTO employee_tab6(eno,sal,job,person) VALUES(1,1500,'C++',person_typ6('张三','男','11-1月-76'));
INSERT INTO employee_tab6(eno,sal,job,person) VALUES(2,2000,'DBA',person_typ6('李四','女','11-5月-75'));
--
DECLARE
TYPE person_table_type IS TABLE OF person_typ6;
person_table person_table_type;
v_temp VARCHAR2(100);
BEGIN
SELECT person BULK COLLECT INTO person_table FROM employee_tab6;
IF person_table(1).compare(person_table(2)) =1 THEN
v_temp :=person_table(1).name ||'比'||person_table(2).name||'大';
ELSE
v_temp :=person_table(1).name ||'比'||person_table(2).name||'小';
END IF;
dbms_output.put_line(v_temp);
END;
--建立和使用包含自定义构造方法的对象类型
CREATE OR REPLACE TYPE person_typ7 AS OBJECT(
NAME VARCHAR2(10),gender VARCHAR2(2),birthday DATE,
CONSTRUCTOR FUNCTION person_typ7(NAME VARCHAR2) RETURN SELF AS RESULT,
CONSTRUCTOR FUNCTION person_typ7(NAME VARCHAR2,gender VARCHAR2) RETURN SELF AS RESULT,
CONSTRUCTOR FUNCTION person_typ7(NAME VARCHAR2,gender VARCHAR2,birthday DATE) RETURN SELF AS RESULT);
CREATE OR REPLACE TYPE BODY person_typ7 IS
CONSTRUCTOR FUNCTION person_typ7(NAME VARCHAR2) RETURN SELF AS RESULT
IS
BEGIN
self.name :=NAME;
self.gender :='女';
self.birthday :=SYSDATE;
RETURN;
END;
CONSTRUCTOR FUNCTION person_typ7(NAME VARCHAR2,gender VARCHAR2) RETURN SELF AS RESULT
IS
BEGIN
self.name :=NAME;
self.gender :=gender;
self.birthday :=SYSDATE;
RETURN;
END;
CONSTRUCTOR FUNCTION person_typ7(NAME VARCHAR2,gender VARCHAR2, birthday DATE) RETURN SELF AS RESULT
IS
BEGIN
self.name :=NAME;
self.gender :=gender;
self.birthday :=birthday;
RETURN;
END;
END;
--建表
CREATE TABLE employee_tab7(eno VARCHAR2(10),person person_typ7,sal NUMBER(6,2),job VARCHAR2(10));
INSERT INTO employee_tab7(eno,sal,job,person) VALUES(1,1500,'C++',person_typ7('张三'));
INSERT INTO employee_tab7(eno,sal,job,person) VALUES(2,2500,'JAVA',person_typ7('李四','男'));
INSERT INTO employee_tab7(eno,sal,job,person) VALUES(3,3500,'DBA',person_typ7('王五','男','01-1月-70'));
1504
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
