目录
- 16-1、PL/SQL简介
- 23、异常处理
- 19、控制结构
- 20、复合数据类型
- 21、显式游标的使用
- 22、高级显式游标
- 16-2、执行PLSQL语句-使用标准变量
- 16-3、执行PLSQL语句-使用外部变量
- 特殊变量类型:%TYPE与%ROWTYPE
- 17、执行PLSQL语句-嵌套块
- 18、与Oracle服务器之间的交互-PL/SQL中的SQL语句
- 25-3、包-函数-触发器的管理:概述
- 包-函数-触发器的管理:通过视图访问
- 24、存储过程
- 25-1、包-函数-触发器的管理:函数
- 25-4、包-函数-触发器的管理:触发器
- 25-2、包-函数-触发器的管理:包
- trigger相关知识点
- LOB类型
- 参照变量
- 嵌套表类型
- 可变数组
- 集合类型之间的比较
- 系统实用工具包
- 过程调用流程图
目录
- 16-1、PL/SQL简介
- 23、异常处理
- 19、控制结构
- 20、复合数据类型
- 21、显式游标的使用
- 22、高级显式游标
- 16-2、执行PLSQL语句-使用标准变量
- 16-3、执行PLSQL语句-使用外部变量
- 特殊变量类型:%TYPE与%ROWTYPE
- 17、执行PLSQL语句-嵌套块
- 18、与Oracle服务器之间的交互-PL/SQL中的SQL语句
- 25-3、包-函数-触发器的管理:概述
- 包-函数-触发器的管理:通过视图访问
- 24、存储过程
- 25-1、包-函数-触发器的管理:函数
- 25-4、包-函数-触发器的管理:触发器
- 25-2、包-函数-触发器的管理:包
- trigger相关知识点
- LOB类型
- 参照变量
- 嵌套表类型
- 可变数组
- 集合类型之间的比较
- 系统实用工具包
- 过程调用流程图
目录
- 16-1、PL/SQL简介
- 23、异常处理
- 19、控制结构
- 20、复合数据类型
- 21、显式游标的使用
- 22、高级显式游标
- 16-2、执行PLSQL语句-使用标准变量
- 16-3、执行PLSQL语句-使用外部变量
- 特殊变量类型:%TYPE与%ROWTYPE
- 17、执行PLSQL语句-嵌套块
- 18、与Oracle服务器之间的交互-PL/SQL中的SQL语句
- 25-3、包-函数-触发器的管理:概述
- 包-函数-触发器的管理:通过视图访问
- 24、存储过程
- 25-1、包-函数-触发器的管理:函数
- 25-4、包-函数-触发器的管理:触发器
- 25-2、包-函数-触发器的管理:包
- trigger相关知识点
- LOB类型
- 参照变量
- 嵌套表类型
- 可变数组
- 集合类型之间的比较
- 系统实用工具包
- 过程调用流程图
1、PL/SQL简介
什么是PL/SQL:
PL/SQL是 Procedure Language & Structured Query Language 的缩写。ORACLE的SQL是支持ANSI(American national Standards Institute)和ISO92 (International Standards Organization)标准的产品。PL/SQL是对SQL语言存储过程语言的扩展。
PL/SQL优点:
支持所有SQL数据类型和所有SQL函数,同时支持所有ORACLE对象类型。
(提示:在 PL/SQL中只能用 SQL语句中的 DML 部分,不能用 DDL 部分,如果要在PL/SQL中使用DDL(如CREATE table 等)的话,只能以动态的方式来使用。)
可以被命名和存储在ORACLE服务器中,同时也能被其他的PL/SQL程序或SQL命令调用。
有利于客户/服务器环境应用的运行
对于客户/服务器环境来说,真正的瓶颈是网络上。无论网络多快,只要客户端与服务器进行大量的数据交换。应用运行的效率自然就会受到影响。如果使用PL/SQL进行编程,将这种具有大量数据处理的应用放在服务器端来执行。自然就省去了数据在网上的传输时间。
对于SQL,Oracle必须在同一时间处理每一条语句,在网络环境下就意味着
每一个独立的调用都必须被ORACLE服务器处理,这就占用大量的服务器时间,同时导致网络拥挤,而
PL/SQL是以整个语句块发给服务器,这就降低了网络拥挤出现的概率。
PL/SQL块结构
DECLARE 声明部分
BEGIN 执行开始
EXCEPTION 异常处理部分
END 结束
PL/SQL块分类
匿名块:可以用在服务器端和客户端,只能使用一次。
命名块:可被独立编译并存储在数据库中,可重用。
命名块分类
函数:接受0或多个输入参数,有一个返回值,返回值的数据类型在创建函数时定义。
过程:接受零或多个参数作为输入(INPUT)或输出(OUTPUT),或既作输入又作输出(INOUT)。
与函数不同,存储过程没有返回值。
存储过程不能由SQL语句直接使用,只能通过EXECUTE命令或PL/SQL程序块内部调用。
包: 被组合在一起的相关对象的集合,当包中任何函数或存储过程被调用,包就被加载入内存,
包中的任何函数或存储过程的子程序访问速度将大大加快。
包由两个部分组成:包头和包体,在包头中描述变量、常量、游标、和子程序
包体由完整的子程序,游标定义组成。
触发器:与表或数据库事件联系在一起,当一个触发器事件发生时,定义在表上的触发器被触发执行。
2、执行PLSQL语句-使用标准变量
set serverout on
declare
v_ename varchar(10);
v_sal number(6,2);
begin
select ename,sal
into v_ename,v_sal
from emp
where empno=7788;
dbms_output.put_line(v_ename||' Salary is '||v_sal);
end;
/
插入
DECLARE
row_id UROWID;
info VARCHAR2(30);
BEGIN
INSERT INTO dept1 VALUES(90,'SERVICE','BEIJING')
RETURNING rowid,dname||':'||TO_CHAR(deptno)||':'||loc
INTO row_id,info;
DBMS_OUTPUT.PUT_LINE('ROWID: '||row_id);
DBMS_OUTPUT.PUT_LINE(info);
END;
/
row_id UROWID;
info VARCHAR2(30);
BEGIN
INSERT INTO dept1 VALUES(90,'SERVICE','BEIJING')
RETURNING rowid,dname||':'||TO_CHAR(deptno)||':'||loc
INTO row_id,info;
DBMS_OUTPUT.PUT_LINE('ROWID: '||row_id);
DBMS_OUTPUT.PUT_LINE(info);
END;
/
更新
DECLARE
row_id UROWID;
info VARCHAR2(30);
BEGIN
UPDATE dept1 SET dname='IT_DEPT' WHERE deptno=90
RETURNING rowid, TO_CHAR(deptno)||':'||dname
INTO row_id,info;
DBMS_OUTPUT.PUT_LINE('ROWID: '||row_id);
DBMS_OUTPUT.PUT_LINE(info);
END;
/
删除
DECLARE
row_id UROWID;
info VARCHAR2(30);
BEGIN
DELETE FROM dept1 WHERE deptno=90
RETURNING rowid, TO_CHAR(deptno)||':'||dname||': deleted'
INTO row_id,info;
DBMS_OUTPUT.PUT_LINE('ROWID: '||row_id);
DBMS_OUTPUT.PUT_LINE(info);
END;
/
%TYPE使用
declare
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE; --使用%TYPE
begin
select ename,sal
into v_ename,v_sal
from emp
where empno=:empno;
dbms_output.put_line(v_ename||' Salary is '||v_sal);
end;
/
3、执行PLSQL语句-使用外部变量
使用&
替代变量
declare
v_ename varchar(10);
v_sal number(6,2);
begin
select ename,sal
into v_ename,v_sal
from emp
where empno=
&empno;
dbms_output.put_line(v_ename||' Salary is '||v_sal);
end;
/
使用define
预先定义empno变量的值
define empno=7499
使用accept
保存为脚本,第一行添加 accept empno prompt 'EMPNO:',运行脚本。
使用绑定变量 (变量名前加“:”)
var empno number; 定义变量
exec :empno := 7788; 为变量赋值(执行一个
过程)
print empno 显示变量值
declare
v_ename varchar(10);
v_sal number(6,2);
begin
select ename,sal
into v_ename,v_sal
from emp
where empno=
:empno; --在PLSQL中使用绑定变量
dbms_output.put_line(v_ename||' Salary is '||v_sal);
end;
/
替代变量与绑定变量结合使用
var g_empno number
var g_ename varchar2(10)
var g_sal number --定义绑定变量
SQL>/
declare
v_ename emp.ename%TYPE; -- v_ename PL/SQL标量
v_sal emp.sal%TYPE;
begin
:g_empno := &empno; -- 绑定变量的值来自替代变量
select ename,sal
into :g_ename, :g_sal
from emp
where empno = :g_empno; --在PL/SQL中使用绑定变量
dbms_output.put_line(:g_ename||' Salary is '||:g_sal);
end;
/
v_ename emp.ename%TYPE; -- v_ename PL/SQL标量
v_sal emp.sal%TYPE;
begin
:g_empno := &empno; -- 绑定变量的值来自替代变量
select ename,sal
into :g_ename, :g_sal
from emp
where empno = :g_empno; --在PL/SQL中使用绑定变量
dbms_output.put_line(:g_ename||' Salary is '||:g_sal);
end;
/
SQL> /
Enter value for empno: 7499
old 5: :g_empno := &empno;
new 5: :g_empno := 7499;
ALLEN Salary is 1600
使用了绑定变量
优点,可以利用共享池,不需要重新编译可以重用,提高SQL语句的软解析率,降低硬解析率
缺点,SQL优化器得到的信息减少了,可能得不到最优的执行计划。
特殊变量类型:%TYPE与%ROWTYPE
%TYPE使用
declare
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE; --使用%TYPE
begin
select ename,sal
into v_ename,v_sal
from emp
where empno=:empno;
dbms_output.put_line(v_ename||' Salary is '||v_sal);
end;
/
%ROWTYPE使用
如果要声明的记录对应于数据库表或视图中的列的集合(查询整行)
可以使用ROWTYPE属性,记录内字段的名字和数据类型参照与表或视图中的列
简化了使用记录的操作
DECLARE
emp_record emp%ROWTYPE;
v_empno emp.empno%TYPE := &empno;
BEGIN
SELECT * INTO emp_record FROM emp1
WHERE empno=v_empno;
DBMS_OUTPUT.PUT_LINE('ename is :'||emp_record.ename);
DBMS_OUTPUT.PUT_LINE('job is :'||emp_record.job);
DBMS_OUTPUT.PUT_LINE('sal is :'||emp_record.sal);
END;
17、执行PLSQL语句-嵌套块
PL/SQL的变量作用范围特点是:
变量的作用范围是在你所引用的程序单元(块、子程序、包)内。即从声明变量开始到该块的结束。
一个变量(标识)只能在你所引用的块内是可见的。
当一个变量超出了作用范围,PL/SQL引擎就释放用来存放该变量的空间(因为它可能不用了)。
在子块中重新定义该变量后,它的作用仅在该块内。
子块能引用父块定义的变量,父块不能引用子块定义的变量
DECLARE
v_deptno NUMBER(4);
v_dname VARCHAR2(10);
BEGIN
DECLARE --嵌套块
v_ename VARCHAR2(10) := '&ename';
BEGIN
SELECT deptno INTO v_deptno
FROM emp
WHERE ename=UPPER(v_ename);
END; --嵌套块结束
SELECT dname INTO v_dname
FROM dept
WHERE deptno=v_deptno;
DBMS_OUTPUT.PUT_LINE('Department '||v_deptno||' is '||v_dname||'.');
v_deptno NUMBER(4);
v_dname VARCHAR2(10);
BEGIN
DECLARE --嵌套块
v_ename VARCHAR2(10) := '&ename';
BEGIN
SELECT deptno INTO v_deptno
FROM emp
WHERE ename=UPPER(v_ename);
END; --嵌套块结束
SELECT dname INTO v_dname
FROM dept
WHERE deptno=v_deptno;
DBMS_OUTPUT.PUT_LINE('Department '||v_deptno||' is '||v_dname||'.');
--
DBMS_OUTPUT.PUT_LINE('Employee Nmae is '||
v_ename
||'.'); --父块不能引用子块的变量
END;
/
END;
/
18、与Oracle服务器之间的交互-PL/SQL中的SQL语句
SELECT 查询仅返回单行值。 (如果想处理多行返回,使用游标)
无返回值和返回多行值均会产生运行时错误。
NO_DATE_FOUND TOO_MANY_ROWS
组函数在PL/SQL中虽然不适用,但在PL/SQL块中的SQL语句中适用。
DML操作在PL/SQL中可以处理多行
INSERT 可以:
使用SQL函数,例如USER和SYSDATE.
使用数据库序列生成主键值
使用源自PL/SQL块的值
加入列的缺省值
例1:
先建立序列
1 CREATE SEQUENCE emp_empno
2 INCREMENT BY 1
3 START WITH 9000
4 MAXVALUE 10000
5 NOCACHE
6* NOCYCLE
2 INCREMENT BY 1
3 START WITH 9000
4 MAXVALUE 10000
5 NOCACHE
6* NOCYCLE
DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE := '&ename';
v_job emp.job%TYPE := '&job';
v_hiredate emp.hiredate%TYPE
DEFAULT SYSDATE; --使用函数,使用缺省值
v_sal emp.sal%TYPE := &sal;
v_deptno emp.deptno%TYPE := &deptno;
BEGIN
SELECT emp_empno.
nextval INTO v_empno --使用序列
FROM dual;
INSERT INTO emp1 (empno,ename,job,hiredate,sal,deptno)
VALUES (v_empno,v_ename,v_job,v_hiredate,v_sal,v_deptno);
END;
UPDATE
例2:
DECLARE
v_ename emp.ename%TYPE := '&ename';
v_mgrno emp.mgr%TYPE := &mgrno;
BEGIN
UPDATE emp1 SET mgr=v_mgrno WHERE ename=v_ename;
END;
v_ename emp.ename%TYPE := '&ename';
v_mgrno emp.mgr%TYPE := &mgrno;
BEGIN
UPDATE emp1 SET mgr=v_mgrno WHERE ename=v_ename;
END;
DELETE
例3:
DECLARE
v_ename emp.ename%TYPE := '&ename';
BEGIN
DELETE FROM emp1 WHERE ename=v_ename;
END;
隐式游标
当使用一个SQL语句时,ORACLE服务器将分配一大块内存来解析和执行SQL命令,这一工作区就是游标。
当PL/SQL块的执行部分中有SQL语句时,PL/SQL会创建一个隐式游标,并自行管理该游标。
显式游标要由程序员明确的声明并对其命名。
SQL%ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN
例4:
VARIABLE row_count VARCHAR2(20);
DECLARE
v_empno NUMBER := &empno;
BEGIN
DELETE FROM EMP1
WHERE empno=v_empno;
:row_count := (SQL%ROWCOUNT||' rows deleted.');
END;
/
PRINT rows_count
1 rows deleted.
19、控制结构
条件判断
例1:
DECLARE
v_sal emp.sal%TYPE;
v_ename emp.ename%TYPE := '&ename';
BEGIN
SELECT sal INTO v_sal
FROM emp1
WHERE ename=v_ename;
IF v_sal < 1500 THEN
UPDATE emp1 SET sal=v_sal+100
WHERE ename=v_ename;
v_sal emp.sal%TYPE;
v_ename emp.ename%TYPE := '&ename';
BEGIN
SELECT sal INTO v_sal
FROM emp1
WHERE ename=v_ename;
IF v_sal < 1500 THEN
UPDATE emp1 SET sal=v_sal+100
WHERE ename=v_ename;
END IF;
END;
如果给定的员工的工资小于1500,那么给他涨100工资。
IF-THEN-ELSIF-THEN-END IF
例2:
DECLARE
v_sal emp.sal%TYPE;
v_ename emp.ename%TYPE := '&ename';
BEGIN
SELECT sal INTO v_sal
FROM emp1
WHERE ename=v_ename;
IF v_sal < 1000 THEN
UPDATE emp1 SET sal=v_sal+200
WHERE ename=v_ename;
ELSIF v_sal < 1500 THEN
UPDATE emp1 SET sal=v_sal+100
WHERE ename=v_ename;
END IF;
END;
如果给定的员工的工资小于1000,那么给他涨200工资,小于1500涨100元。
循环控制
Basic Loop 无条件循环
FOR Loop 有计数循环
While Loop 有条件循环
EXIT 退出循环语句
Basic Loop
一定要有EXIT语句,否则程序将无限循环下去。
DECLARE
v_sal emp1.sal%TYPE;
v_ename emp1.ename%TYPE :=UPPER('&ename');
BEGIN
SELECT sal INTO v_sal FROM emp1 WHERE ename=v_ename;
IF v_sal < 2500 THEN
LOOP
UPDATE emp1 SET sal=(sal+1) WHERE ename=v_ename;
SELECT sal INTO v_sal FROM emp1 WHERE ename=v_ename;
EXIT WHEN v_sal >=2500;
END LOOP;
END IF;
END;
根据给定的员工名判断,如果员工工资小于最低工资值2500,将工资增加到2500元。
FOR Loop
REVERSSE 关键字将counter(循环计数变量)从最大值每次减1至最小值。
循环范围的上、下界可以是字面值、变量或表达式。
建立测试表
create table item(ordid number(4),itemid number(10));
DECLARE
v_ordid item.ordid%TYPE := 201;
BEGIN
FOR i IN
5..20
LOOP
INSERT INTO item (ordid,itemid) VALUES(v_ordid,
i);
END LOOP;
END;
循环插入10行记录
While Loop
条件为真执行循环,判断条件在循环过程中一定要发生变化,否则会无限循环下去。
以下代码保存到一个SQL脚本,然后执行。
ACCEPT p_new_order PROMPT 'Enter the order number: '
ACCEPT p_items PROMPT 'Enter the number of items in this order: '
DECLARE
v_count NUMBER(3) := 1;
BEGIN
WHILE v_count <= &p_items
ACCEPT p_items PROMPT 'Enter the number of items in this order: '
DECLARE
v_count NUMBER(3) := 1;
BEGIN
WHILE v_count <= &p_items
LOOP
INSERT INTO item (ordid, itemid)
VALUES (&p_new_order, v_count);
v_count := v_count + 1;
END LOOP;
END;
INSERT INTO item (ordid, itemid)
VALUES (&p_new_order, v_count);
v_count := v_count + 1;
END LOOP;
END;
/
添加同一订单号的多个产品项编号
添加同一订单号的多个产品项编号
20、复合数据类型
记录
处理
单行多列的数据
先用RECORD定义记录的类型,然后再声明此类型的记录。
DECLARE
v_empno emp1.empno%TYPE := &empno;
TYPE emp_record_type IS RECORD
(ename emp1.ename%TYPE,
job emp1.job%TYPE,
sal emp1.sal%TYPE);
emp_record emp_record_type;
BEGIN
SELECT ename, job, sal
INTO
emp_record.ename, emp_record.job, emp_record.sal
FROM emp1
WHERE empno=v_empno;
DBMS_OUTPUT.PUT_LINE('ename is :'||emp_record.ename);
DBMS_OUTPUT.PUT_LINE('job is :'||emp_record.job);
DBMS_OUTPUT.PUT_LINE('sal is :'||emp_record.sal);
END;
/
创建记录,将查询结果保存到记录里
ROWTYPE%属性
如果要声明的记录对应于数据库表或视图中的列的集合(查询整行)
可以使用ROWTYPE属性,记录内字段的名字和数据类型参照与表或视图中的列
简化了使用记录的操作
DECLARE
emp_record emp%ROWTYPE;
v_empno emp.empno%TYPE := &empno;
BEGIN
SELECT * INTO
emp_record FROM emp1
WHERE empno=v_empno;
DBMS_OUTPUT.PUT_LINE('ename is :'||emp_record.ename);
DBMS_OUTPUT.PUT_LINE('job is :'||emp_record.job);
DBMS_OUTPUT.PUT_LINE('sal is :'||emp_record.sal);
END;
PL/SQL表(索引表)
什么是PL/SQL表?
首先PL/SQL表和记录(Record)一样,都是复合数据类型。可以看做是一种用户自定义数据类型。
PL/SQL表由
多行单列的标量构成的临时索引表对象。组成类似于一维数组。
区别于上面的记录(Record)的单行多列和物理存储数据的表。
Record + PL/SQL表可以进行数据的多行多列存储。
生命周期在程序块内。
PL/SQL表的组成结构
PL/SQL表只有两列,其中第一列为索引列,为Integer类型索引列,9i后可以支持字符索引。
第二列为用户自定义列,声明用户可用列类型.
通过索引列中的索引值来操作PL/SQL表中对应的用户自定义列。类似于键值对(
数组中的下标)。
确保索引值的唯一性。如果索引值重复的话不会报错,会覆盖前面的值。
DECLARE
TYPE ename_table_type IS TABLE OF emp1.ename%TYPE
INDEX BY BINARY_INTEGER;
TYPE hiredate_table_type IS TABLE OF DATE
INDEX BY BINARY_INTEGER;
ename_table ename_table_type;
hiredate_table hiredate_table_type;
BEGIN
ename_table(1) := 'CAMERON';
hiredate_table(8) := SYSDATE + 7;
IF ename_table.
EXISTS(1) THEN
INSERT INTO emp1(empno,ename,job,sal,hiredate)
VALUES(9000,
ename_table(1),'CLERK',3200,
hiredate_table(8));
END IF;
END;
属性方法:
count --返回pl/sql表的总行数;
delect --删除pl/sql表的所有内容;
delect(行数) --删除pl/sql表的指定的行;
delect(开始行,结束行) --删除pl/sql表的多行;
first --返回表的第一个INDEX;
next(行数) --这个行数的下一条的INDEX;
last --返回表的最后一个INDEX;
Record + PL/SQL表
Record + PL/SQL表可以进行数据的多行多列存储。
这样我们就可使用Record + PL/SQL表在需要时封装一个临时的表对象,进行传递和操作。
通过Record自定义表结构,封装一条记录。
PL/SQL表声明可用列,类型为Record类型(将可用列指向Record类型变量),每个索引对应一个Record类型变量。
使用Record + PL/SQL表进行数据的多行多列存储
①声明Record类型和PL/SQL表,
其中PL/SQL表的索引列为主键约束和唯一约束列或自增Integer。可用列为Record类型或%RowType类型。
②填充PL/SQL表可用列(Record类型):通过索引指向Record,使用Record访问记录成员。
语法:
PL/SQL表名(索引列值).记录成员 := 记录成员类型值;
或
PL/SQL表名(索引列值) := Record类型变量;
--注意其PL/SQL表中声明的可用列要和这里赋值的Record类型变量结构一样
例子:
DECLARE
TYPE emp_record_type IS RECORD
(
r_ename emp1.ename%TYPE,
r_job emp1.job%TYPE,
r_sal emp1.sal%TYPE
);
--定义记录类型
TYPE emp_table_type IS TABLE OF emp_record_type
INDEX BY BINARY_INTEGER;
emp_table emp_table_type;
--定义索引表
n NUMBER :=1;
--定义一个索引初始值
BEGIN
--填充索引表
FOR v_record IN (SELECT * FROM emp1 ORDER BY empno ASC)
--定义循环体变量,每次保存子查询结果的一条记录
LOOP
emp_table(n).r_ename := v_record.ename;
emp_table(n).r_job := v_record.job;
emp_table(n).r_sal := v_record.sal;
n := n+1;
END LOOP;
--访问索引表
FOR v_n IN emp_table.
first..emp_table.
count
LOOP
DBMS_OUTPUT.PUT_LINE(v_n||' '||emp_table(v_n).r_ename||' '||emp_table(v_n).r_job||' '||emp_table(v_n).r_sal);
END LOOP;
END;
21、显式游标的使用
当使用一个SQL语句时,ORACLE服务器将分配一大块内存来解析和执行SQL命令,这一工作区就是游标。
当PL/SQL块的执行部分中有SQL语句时,PL/SQL会创建一个隐式游标,并自行管理该游标。
显式游标要由程序员明确的声明并对其命名。
不能对其显式的执行OPEN、CLOSE、FETCH语句。
隐式游标属性:
SQL%ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN
例:
VARIABLE row_count VARCHAR2(20);
DECLARE
v_empno NUMBER := &empno;
BEGIN
DELETE FROM emp1
WHERE empno=v_empno;
:row_count := (
SQL%ROWCOUNT||' rows deleted.');
END;
/
PRINT rows_count
1 rows deleted.
显式游标:处理返回多条记录的查询语句
由多行查询返回的行集合称之为活动集(ActiveSet)
能够一行接一行的处理查询返回的行
跟踪当前正在处理的哪一行
显式游标可以有参数
游标参数的范围对于游标来讲是局部变量
当游标为OPENED时,游标参数的值可以用于相关的查询
显式游标控制步骤
1、声明游标 使用CURSOR IS语句
2、打开游标 OPEN
3、从游标中提取数据 FETCH INTO
4、关闭游标 CLOSE
--声明游标
DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
CURSOR emp_corsor IS
SELECT empno, ename FROM emp1;
如需要按指定的次序处理行,可在查询中使用ORDER BY子句。
但在声明中不能使用INTO语句。
--打开游标
OPEN emp_cursor;
分配工作区的内存,解析SELECT语句,标识活动集,游标指针指向第一行(
如果无返回行并不会产生异常)
--从游标中提取数据
FETCH emp_cursor INTO v_empno, v_ename;
FETCH一次从活动集提取一行,
每次执行后,游标的指针将自动指向活动集的下一行,
并将当前行的数据读入PL/SQL输出变量中,
如果游标指针到达活动集的末端,则退出游标的循环。
关闭游标
CLOSE emp_cursor;
对于显式声明的游标,需要手动关闭,以释放系统资源。
完整的简单例子:
DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
CURSOR emp_cursor IS
SELECT empno, ename FROM emp1;
-- i NUMBER :=1;
BEGIN
OPEN emp_cursor;
FOR i IN 1..10 LOOP
FETCH emp_cursor INTO v_empno, v_ename;
DBMS_OUTPUT.PUT_LINE('Empno is '||v_empno||' and ename is '||v_ename);
END LOOP;
CLOSE emp_cursor;
END;
显式游标属性:
CURSOR_NAME%ROWCOUNT
CURSOR_NAME%FOUND
CURSOR_NAME%NOTFOUND
CURSOR_NAME%ISOPEN
不能在SQL语句中引用游标的属性
游标属性例子
DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
CURSOR emp_cursor IS
SELECT empno, ename FROM emp1;
BEGIN
OPEN emp_cursor;
IF emp_cursor%ISOPEN THEN --判断游标是否打开
LOOP
FETCH emp_cursor INTO v_empno, v_ename;
END LOOP;
DBMS_OUTPUT.PUT_LINE (emp_cursor%ROWCOUNT); --游标返回的行数
END IF;
CLOSE emp_cursor;
END;
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
CURSOR emp_cursor IS
SELECT empno, ename FROM emp1;
BEGIN
OPEN emp_cursor;
IF emp_cursor%ISOPEN THEN --判断游标是否打开
LOOP
FETCH emp_cursor INTO v_empno, v_ename;
EXIT WHEN
emp_cursor%NOTFOUND; --如果到达记录集的末端,返回TRUE并结束循环
DBMS_OUTPUT.PUT_LINE('Empno is '||v_empno||' and ename is '||v_ename);
END LOOP;
DBMS_OUTPUT.PUT_LINE (emp_cursor%ROWCOUNT); --游标返回的行数
END IF;
CLOSE emp_cursor;
END;
游标可以
赋值给一个
记录
DECLARE
CURSOR emp_cursor IS
SELECT empno, ename FROM emp1;
emp_record emp_cursor%ROWTYPE; --定义一个记录
BEGIN
OPEN emp_cursor;
FOR i IN 1..10 LOOP
FETCH emp_cursor INTO emp_record; --赋值给记录
DBMS_OUTPUT.PUT_LINE('Empno is '||emp_record.empno||' and ename is '||emp_record.ename);
END LOOP;
CLOSE emp_cursor;
END;
CURSOR emp_cursor IS
SELECT empno, ename FROM emp1;
emp_record emp_cursor%ROWTYPE; --定义一个记录
BEGIN
OPEN emp_cursor;
FOR i IN 1..10 LOOP
FETCH emp_cursor INTO emp_record; --赋值给记录
DBMS_OUTPUT.PUT_LINE('Empno is '||emp_record.empno||' and ename is '||emp_record.ename);
END LOOP;
CLOSE emp_cursor;
END;
/
FOR循环游标
游标式FOR循环使用显式游标处理行,
循环索引声明为%ROWTYPE的记录,隐式的打开游标,
并且从活动集反复获取行的值并传送到记录中的字段,
当所有行处理完毕游标自动关闭,循环自动终止。
(不需要声明控制循环的记录,记录的作用域仅限于FOR循环内部)
DECLARE
CURSOR emp_cursor IS
SELECT empno, ename FROM emp1;
BEGIN
FOR
emp_record IN emp_cursor LOOP
--隐式的打开游标并对游标进行提取
DBMS_OUTPUT.PUT_LINE('Empno is '||emp_record.empno||' and ename is '||emp_record.ename);
END LOOP; --隐式的关闭游标
END;
/
使用游标变量
DECLARE
type cl IS REF CURSOR;
emp_cursor cl;
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;
注:cl为 REF CURSOR类型,而emp_cursor 为游标变量,并且再打开游标时指定了其对应的SELECT语句。
22、高级显式游标
参数化的游标
作用:多次打开相同的游标,返回不同的结果集。
游标声明部分的形参必须与OPEN语句中提供的实参相对应(数量与数据类型)。
例1:
DECLARE
v_empno emp1.empno%TYPE;
v_ename emp1.ename%TYPE;
CURSOR emp_cursor
(
p_deptno NUMBER, p_job VARCHAR2) IS
SELECT empno, ename
FROM emp1
WHERE deptno=p_deptno
AND job=p_job;
BEGIN
OPEN emp_cursor(
30,'SALESMAN');
LOOP
FETCH emp_cursor INTO v_empno, v_ename;
EXIT WHEN emp_cursor%NOTFOUND OR emp_cursor%NOTFOUND IS NULL;
DBMS_OUTPUT.PUT_LINE('empno is '||v_empno||' and '||'ename is '||v_ename);
END LOOP;
CLOSE emp_cursor;
END;
记录+FOR+游标参数
例2:
DECLARE
CURSOR emp_c (p_job VARCHAR2)
IS SELECT ename, sal FROM emp
WHERE job=p_job FOR UPDATE NOWAIT;
BEGIN
FOR e_record IN emp_c(UPPER('&jobs'))
LOOP
dbms_output.put_line(e_record.ename||'''s sal:'||e_record.sal);
END LOOP;
END;
/
SELECT FOR UPDATE 游标
通常,SELECT语句不会在被访问的数据行上设置任何锁定,
这允许连接到该数据库的其他会话可以改变被选择的数据。
然而结果集是一致的,因为在OPEN阶段,活动集被确定后,oracle做了个快照,
在此之后发生的变化不会反映到结果集里,除非再一次打开游标。
可以使用
FOR UPDATE子句在活动集的数据行上设置独占行锁定,
直到该事务结束。
例:
DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
CURSOR emp_cursor IS
SELECT empno, ename FROM emp1
FOR UPDATE NOWAIT;
--NOWAIT 如果数据行已经被其他事务锁定,OPEN将会返回一个错误
--
returns an Oracle error if the rows are locked by another session
--也可以写成
FOR UPDATE OF sal NOWAIT, 只锁定sal列。
BEGIN
OPEN emp_cursor;
IF emp_cursor%ISOPEN THEN
LOOP
FETCH emp_cursor INTO v_empno, v_ename;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Empno is '||v_empno||' and ename is '||v_ename);
END LOOP;
DBMS_OUTPUT.PUT_LINE(emp_cursor%ROWCOUNT);
END IF;
CLOSE emp_cursor;
END;
模拟锁定:update emp1 set ename='SMMTH' where empno=7369;
查看系统当前锁
select oracle_username, os_user_name,session_id from v$locked_object;
WHERE CURRENT OF子句
引用显式游标的当前行,(如果游标定义时使用了FOR UPDATE的话)
DECLARE
CURSOR e_sal_cursor IS
SELECT sal FROM emp WHERE deptno=&&DEPTNO
FOR UPDATE NOWAIT;
BEGIN
FOR r IN e_sal_cursor LOOP
UPDATE emp SET sal=sal*1.1
WHERE CURRENT OF e_sal_cursor;
END LOOP;
END;
/
错误的方式:
DECLARE
CURSOR e_sal_cursor IS
SELECT sal FROM emp WHERE deptno=&&DEPTNO
FOR UPDATE NOWAIT;
BEGIN
OPEN e_sal_cursor;
FOR r IN 1..e_sal_cursor%ROWCOUNT LOOP
UPDATE emp SET sal=sal*1.1
WHERE CURRENT OF e_sal_cursor;
END LOOP;
CLOSE e_sal_cursor;
END;
/
CURSOR e_sal_cursor IS
SELECT sal FROM emp WHERE deptno=&&DEPTNO
FOR UPDATE NOWAIT;
BEGIN
OPEN e_sal_cursor;
FOR r IN 1..e_sal_cursor%ROWCOUNT LOOP
UPDATE emp SET sal=sal*1.1
WHERE CURRENT OF e_sal_cursor;
END LOOP;
CLOSE e_sal_cursor;
END;
/
23、异常处理
PL/SQL中的一个警告或错误都被成为异常
错误通常分为两类:
编译时错误--一般是语法错误,由PL/SQL引擎发现并报告给用户,程序本身不能处理,因为还没运行。
运行时错误--程序在运行时引发的错误,例如没返回结果与返回多个结果(只能返回一行结果的情况下)
异常处理通常是指处理运行时错误。
引发异常的一个重要原因是处理数据时发生错误。统计表明,SELECT语句、DML语句以及游标操作语句更容易引发异常。
编写PL/SQL块的主要目的是处理数据,而PL/SQL块在逻辑上与数据是分开的,程序员根本无法预料数据的变化。
例如,要查询部门10的员工,程序员根本不知道这个部门中有没有员工, 有一个还是有多个员工。
所以在编写程序时,程序员应该考虑各种可能出现的异常,在程序中编写这些异常的处理代码,这样的程序才能经受各种错误的考验。
预定义异常:
Oracle把一些常见的错误定义为有名字的异常,这就是预定义的异常。
Oracle有许多预定义的异常,在进行处理时不需要再定义,
只需要编写相应的异常处理程序即可。当PL/SQL块执行发生错误时,
数据库服务器将自动抛出相应的异常,并执行编写的异常处理程序。
预定义说明的 oracle 异常
ORACLE 错误 异常信息 说明
ORA-0001 Dup_val_on_index 试图破坏一个唯一性限制
ORA-0051 Timeout-on-resource 在等待资源时发生超时
ORA-0061 Transaction-backed-out 由于发生死锁事务被撤消.
ORA-1001 Invalid-cursor 试图使用一个无效的游标
ORA-1012 Not-logged-on 没有连接到ORACLE
ORA-1017 Login-denied 无效的用户名/口令
ORA-1403 Not-data-found 没有找到数据
ORA-1422 Too-many-rows select into 返回多行
ORA-1476 Zero-divide 试图被零除
ORA-1722 Invalid-number 转换一个数字失败
ORA-6500 Storage-error 内存不够引发的内部错误
ORA-6501 Program-error 内部错误
ORA-6502 Value-error 转换或截断错误
ORA-6511 Cursor-already-open 试图打开一个已存在的 游标
ORA-6530 Access-into-null 试图为null 对象的属性赋值
非预定义异常:
在PL/SQL中还有一类会经常遇到的错误。每个错误都有相应的错误代码和错误原因,
但是由于Oracle没有为这样的错误定义一个名称,因而不能直接进行异常处理。
在一般情况下,只能在PL/SQL块执行出错时查看其出错信息。
对于这类非预定义的异常,由于它也被自动抛出的,因而只需要定义一个异常,
把这个异常的名称与错误的代码关联起来,然后就可以像处理预定义异常那样处理这样的异常了。
在抛出ORACLE预定义异常之前先抛出自己定义的非预定义异常,可以大大节约异常定位的时间,
比如说很多情况都最终导致同一种预定义异常情况下。
用户自定义异常:
程序员可以把一些特定的状态定义为异常。这样的异常一般由程序员自己决定,在一定的条件下抛出,然后利用PL/SQL的异常机制进行处理。
对于用户自定义的异常,有两种处理方法。
第一种方法是先定义一个异常,并在适当的时候抛出,然后在PL/SQL块的异常处理部分进行处理。
用户自定义的异常一般在一定的条件下抛出,于是这个条件就成为引发这个异常的原因。
第二种方法是向调用者返回一个自定义的错误代码和一条错误信息。
自定义错误代码的范围是-20000到-20999,是oracle为自定义错误保留的
在处理用户自定义的异常时,也可以使用函数SQLCODE和SQLERRM,这两个函数分别用于返回指定的错误代码和错误信息。
系统预定义异常例子:
DECLARE
v_ename emp1.ename%TYPE;
v_sal emp1.sal%TYPE;
BEGIN
SELECT ename, sal
INTO v_ename, v_sal
FROM emp1;
WHERE empno=9100; --不存在的员工编号,会返回 NO_DATA_FOUND异常
DBMS_OUTPUT.PUT_LINE('ename is '||v_ename||'.');
DBMS_OUTPUT.PUT_LINE('salary is '||v_sal||'.');
EXCEPTION
WHEN NO_DATA_FOUND THEN --无返回数据
DBMS_OUTPUT.PUT_LINE('data not found.');
WHEN TOO_MANY_ROWS THEN --返回了多行数据
DBMS_OUTPUT.PUT_LINE('exact fetch returns more than requested number of rows.');
WHEN OTHERS THEN --其他异常
DBMS_OUTPUT.PUT_LINE('others exception.');
END;
v_ename emp1.ename%TYPE;
v_sal emp1.sal%TYPE;
BEGIN
SELECT ename, sal
INTO v_ename, v_sal
FROM emp1;
WHERE empno=9100; --不存在的员工编号,会返回 NO_DATA_FOUND异常
DBMS_OUTPUT.PUT_LINE('ename is '||v_ename||'.');
DBMS_OUTPUT.PUT_LINE('salary is '||v_sal||'.');
EXCEPTION
WHEN NO_DATA_FOUND THEN --无返回数据
DBMS_OUTPUT.PUT_LINE('data not found.');
WHEN TOO_MANY_ROWS THEN --返回了多行数据
DBMS_OUTPUT.PUT_LINE('exact fetch returns more than requested number of rows.');
WHEN OTHERS THEN --其他异常
DBMS_OUTPUT.PUT_LINE('others exception.');
END;
OTHERS异常处理器能截获所有未经处理的异常,所以,OTHERS是最后定义的异常处理器。
非预定义异常例子:
DECLARE
emp_e EXCEPTION;
PRAGMA EXCEPTION_INIT (emp_e, -2292);
--EXCEPTION_INIT将异常名和一个系统错误代码联系起来
v_deptno dept.deptno%TYPE := &p_deptno;
BEGIN
DELETE FROM dept
WHERE deptno = v_deptno;
--COMMIT;
EXCEPTION
WHEN
emp_e THEN
DBMS_OUTPUT.PUT_LINE ('Cannot remove dept ' ||
TO_CHAR(v_deptno) || '. Employees exist. ');
END;
ORA-02292: integrity constraint (string.string) violated - child record found
Cause: attempted to delete a parent key value that had a foreign key dependency.
Action: delete dependencies first then parent or disable constraint.8
使用错误函数(SQLCODE和SQLERRM)
例一、
DECLARE
d_excep EXCEPTION;
PRAGMA EXCEPTION_INIT (d_excep, -2292);
BEGIN
DELETE FROM dept WHERE deptno=30;
EXCEPTION
WHEN d_excep THEN
DBMS_OUTPUT.PUT_LINE( SQLERRM);
END;
/
d_excep EXCEPTION;
PRAGMA EXCEPTION_INIT (d_excep, -2292);
BEGIN
DELETE FROM dept WHERE deptno=30;
EXCEPTION
WHEN d_excep THEN
DBMS_OUTPUT.PUT_LINE( SQLERRM);
END;
/
例二、
先建立一个保存错误信息的表
CREATE TABLE u_err_table(
username VARCHAR2(20),
errdate DATE,
errcode NUMBER,
errm VARCHAR2(100)
);
username VARCHAR2(20),
errdate DATE,
errcode NUMBER,
errm VARCHAR2(100)
);
执行以下语句块,可能引发的异常交给OTHERS处理
DECLARE
v_sqlcode NUMBER;
v_sqlerrm VARCHAR2(100);
v_deptno dept.deptno%TYPE := &deptno;
BEGIN
DELETE FROM dept
WHERE deptno = v_deptno;
EXCEPTION
WHEN OTHERS THEN
v_sqlcode := SQLCODE;
v_sqlerrm := SQLERRM;
INSERT INTO u_err_table
VALUES (USER, SYSDATE, v_sqlcode, v_sqlerrm);
END;
/
查看错误记录表:SELECT * FROM u_err_table;
用户自定义异常例子:
在上面错误函数的例子里,如果输入一个不存在的部门号,程序并不会产生异常,就要用到用户自定义的异常来实现。
DECLARE
e_err_deptno EXCEPTION;
--定义异常
v_deptno dept.deptno%TYPE := &deptno;
BEGIN
DELETE FROM dept
WHERE deptno = v_deptno;
IF SQL%NOTFOUND THEN
RAISE e_err_deptno;
--抛出异常
END IF;
EXCEPTION
WHEN
e_err_deptno THEN
--处理异常
DBMS_OUTPUT.PUT_LINE('invalid deptno.');
END;
可以使用
RAISE_APPLICATION_ERROR返回自定义的错误消息,看起来跟ORACLE的标准错误一样。
DECLARE
v_sqlcode NUMBER;
v_sqlerrm VARCHAR2(100);
v_deptno dept.deptno%TYPE := &deptno;
BEGIN
DELETE FROM dept
WHERE deptno = v_deptno;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20001,'deptno not exists.');
END IF;
EXCEPTION
WHEN OTHERS THEN
v_sqlcode :=
SQLCODE;
v_sqlerrm :=
SQLERRM;
DBMS_OUTPUT.PUT_LINE(v_sqlcode||': '||v_sqlerrm);
END;
自定义异常的例子:
DECLARE
v_deptno dept1.deptno%TYPE := &deptno;
dd_exc EXCEPTION;
BEGIN
DELETE FROM dept1 WHERE deptno=v_deptno;
IF SQL%NOTFOUND THEN
RAISE dd_exc;
ELSE
dbms_output.put_line(SQL%ROWCOUNT||' rows deleted.');
END IF;
EXCEPTION
WHEN dd_exc THEN
dbms_output.put_line('no rows deleted.');
END;
/
v_deptno dept1.deptno%TYPE := &deptno;
dd_exc EXCEPTION;
BEGIN
DELETE FROM dept1 WHERE deptno=v_deptno;
IF SQL%NOTFOUND THEN
RAISE dd_exc;
ELSE
dbms_output.put_line(SQL%ROWCOUNT||' rows deleted.');
END IF;
EXCEPTION
WHEN dd_exc THEN
dbms_output.put_line('no rows deleted.');
END;
/
24、存储过程
一、过程:接受零或多个参数作为输入(INPUT)或输出(OUTPUT),或既作输入又作输出(INOUT)。
与函数不同,存储过程没有返回值。
存储过程不能由SQL语句直接使用,只能通过EXECUTE命令或PL/SQL程序块内部调用。
过程例子1:
CREATE OR REPLACE PROCEDURE insertdept(
p_deptno dept.deptno%TYPE,
p_dname dept.dname%TYPE,
p_loc dept.loc%TYPE) AS
--在过程和函数中没有使用关键词DECLARE,取而代之的是关键字IS或AS。
BEGIN
INSERT INTO dept1(deptno,dname,loc)
VALUES(p_deptno,p_dname,p_loc);
END insertdept;
运行过程:
EXEC insertdept(60,'IT_DEPT','BEIJINIG'); 或
BEGIN
insertdept(60,'HR_DEPT','SHANGHAI');
END;
过程例子2:用户连接登记记录
创建登录记录表
CREATE table logtable (userid VARCHAR2(10), logdate date);
建立过程
CREATE OR REPLACE PROCEDURE logexecution IS
BEGIN
INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE);
END;
执行过程
exec logexecution;
过程调用本身是一个PL/SQL语句,而函数调用是作为表达式的一部分调用的。
二、使用输入参数(IN)
CREATE OR REPLACE PROCEDURE p1
(in_empno
IN emp.empno%TYPE) AS
v_ename emp.ename%TYPE;
BEGIN
SELECT ename INTO v_ename
FROM emp WHERE empno=in_empno;
dbms_output.put_line(v_ename);
END;
/
参数如果没有显式的声明,默认就是
输入参数(IN)
在过程中不能修改输入参数的值。输入参数在过程内部以常量形式存在。
例:
SQL> CREATE OR REPLACE PROCEDURE p2
2 (io_sal IN NUMBER)
3 AS
4 BEGIN
5 io_sal := io_sal*2;
6 dbms_output.put_line(io_sal);
7 END;
8 /
Warning: Procedure created with compilation errors
SQL> show errors
Errors for PROCEDURE SCOTT.P2:
LINE/COL ERROR
-------- ---------------------------------------------------------------------
4/3 PLS-00363: expression 'IO_SAL' cannot be used as an assignment target
4/3 PL/SQL: Statement ignored
三、使用输出参数(OUT)
CREATE OR REPLACE PROCEDURE p3
(o_sal
OUT NUMBER) IS
BEGIN
o_sal := 1400;
o_sal := o_sal*2;
dbms_output.put_line(o_sal);
END;
/
输出参数的值在过程内部可以被修改.
实参具有的任何值将被忽略不计,所以定义过程时,要在过程内部对形参做初始化。
DECLARE
v_sal emp1.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp1
WHERE empno=7369;
p3(v_sal);
UPDATE emp1 SET sal=v_sal WHERE empno=7369;
END;
/
OUT参数应用实例:
CREATE OR REPLACE PROCEDURE p3
(o_sal
OUT NUMBER) IS
v_sal emp1.sal%TYPE;
BEGIN
SELECT sal INTO v_sal FROM emp1
WHERE empno=7902;
o_sal := v_sal;
o_sal := o_sal*2;
dbms_output.put_line(o_sal);
END;
/
四、使用输入输出参数(IN OUT)
将外部变量修改后再输出
CREATE OR REPLACE PROCEDURE p2
(io_sal IN OUT NUMBER)
AS
BEGIN
io_sal := io_sal*2;
END;
/
SQL> var g_sal number;
SQL> exec :g_sal := 3000;
SQL> exec p2(:g_sal);
在PL/SQL中调用过程
DECLARE
v_sal emp.sal%TYPE := &sal;
BEGIN
p2(v_sal);
DBMS_OUTPUT.PUT_LINE(v_sal);
END;
/
按引用和按值传递参数
引用传递简单来说,就是过程内部修改了形参的值,其实也就修改了对应实参的值。
数值传递简单来说,就是形参复制了实参的值,在过程内部更改形参的值,并不影响实参原来的值。
25-1、包-函数-触发器的管理:函数
函数:接受0或多个输入参数,有一个返回值,返回值的数据类型在创建函数时定义。
函数例子1:
CREATE OR REPLACE FUNCTION tax(
p_empno IN NUMBER)
RETURN NUMBER IS
v_sal NUMBER;
v_returnValue NUMBER;
BEGIN
SELECT sal INTO v_sal
FROM emp1 WHERE empno=
p_empno;
v_returnValue := v_sal*0.08;
RETURN v_returnValue;
END tax;
返回员工的所得税
调用函数
DECLARE
v_tax NUMBER;
BEGIN
v_tax :=
tax(&empno);
DBMS_OUTPUT.PUT_LINE('TAX IS '||v_tax);
END;
函数例子2:
创建函数:
CREATE OR REPLACE FUNCTION get_salary(
p_dept_no NUMBER,
p_num
OUT NUMBER)
RETURN NUMBER IS
v_sum NUMBER;
BEGIN
SELECT SUM(sal), count(*) INTO v_sum,
p_num
FROM emp WHERE deptno=p_dept_no;
RETURN v_sum;
END get_salary;
/
在PL/SQL块中调用:
DECLARE
V_num2 NUMBER;
V_sum NUMBER;
BEGIN
V_sum :=get_salary(&&deptno,V_num2);
DBMS_OUTPUT.PUT_LINE(&deptno||' department sum salary is '||V_sum||' employee: '||V_num2);
END;
/
删除函数
我们可以 DROP FUNCTION 命令对不需要的函数进行删除,语法如下:
DROP FUNCTION [user.]FUNCTION_name;
25-2、包-函数-触发器的管理:包
创建包
包头:
CREATE OR REPLACE PACKAGE test_pkg IS
PROCEDURE update_sal(e_name VARCHAR2,newsal NUMBER);
FUNCTION ann_income(e_name VARCHAR2) RETURN NUMBER;
END;
定义了一个函数和一个过程
包体:
CREATE OR REPLACE PACKAGE BODY test_pkg IS
PROCEDURE update_sal(e_name VARCHAR2,newsal NUMBER)
IS
BEGIN
UPDATE emp1 SET sal=newsal WHERE ename=e_name;
END;
FUNCTION ann_income(e_name VARCHAR2)
RETURN NUMBER IS
annsal NUMBER;
BEGIN
SELECT sal*12+NVL(comm,0) INTO annsal FROM emp1
WHERE ename=e_name;
RETURN annsal;
END;
END;
实现上面的函数和过程
对包内共有元素的调用格式为:包名.元素名称:
对包内过程调用
SQL> exec test_pkg.update_sal('SCOTT',1200);
对包内函数调用
DECLARE
v_annsal NUMBER(7,2);
BEGIN
v_annsal:=test_pkg.ann_income('SCOTT');
dbms_output.put_line('年薪为:'||v_annsal);
END;
删除包
我们可以 DROP PACKAGE 命令对不需要的包进行删除,语法如下:
DROP PACKAGE [BODY[user.]package_name;
25-3、包-函数-触发器的管理:概述
PL/SQL块分类
匿名块:可以用在服务器端和客户端,只能使用一次。
命名块:可被独立编译并存储在数据库中,可重用。
命名块分类: 过程、函数、包、触发器
函数有一个返回值,过程没有返回值,但是两者都可以使用
输出参数,改变外部变量的值。
触发器没有返回值,也不能带参数。
包: 被组合在一起的相关对象的集合,当包中任何函数或存储过程被调用,包就被加载入内存,
包中的任何函数或存储过程的子程序访问速度将大大加快。
包由两个部分组成:包头和包体,在包头中描述变量、常量、游标、和子程序
包体由完整的子程序,游标定义组成。
25-4、包-函数-触发器的管理:触发器
触发器:与表或数据库事件联系在一起,当一个触发器事件发生时,定义在表上的触发器被触发执行。
触发器触发次序
1. 执行 BEFORE语句级触发器;
2. 对与受语句影响的每一行:
1. 执行 BEFORE语句级触发器;
2. 对与受语句影响的每一行:
- 执行 BEFORE行级触发器
- 执行 DML语句
- 执行 AFTER行级触发器
DML触发器:
触发类型--行级和表级
行级:触发语句处理每一行时,行级别触发器都激发一次。
通过:old访问原始值、:new访问修改后的值,:old和:new只在触发器内部有效。
触发时间--之前和之后
建立一个触发器, 当职工表 emp 表被删除一条记录时,把被删除记录写到职工表删除日志表中去
建立删除日志表
CREATE TABLE emp_his
AS SELECT * FROM EMP1 WHERE 1=2;
创建触发器
CREATE OR REPLACE TRIGGER del_emp
BEFORE DELETE OR UPDATE ON scott.emp1 FOR EACH
ROW
BEGIN
INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
VALUES( :old.deptno, :old.empno, :old.ename , :old.job,
:old.mgr, :old.sal, :old.comm, :old.hiredate );
END;
在数据字典USER_TRIGGERS中
查看触发器
set long 200
SELECT trigger_name,(DESCRIPTION),trigger_body
FROM user_triggers
/
删除
触发器:
DROP TRIGGER trigger_name;
禁用
启用:
ALTER TIGGER trigger_name [DISABLE | ENABLE ];
INSTEAD-OF触发器:
INSTEAD OF
选项使
ORACLE
激活触发器,而不执行触发事件
instead-of触发器只能定义在视图上,允许修改一个本来无法修改的视图。
可更改视图指:如果对视图的操作都是对基表的修改,并只有一个基表。
不可更改视图反之。
建立测试视图
CREATE OR REPLACE VIEW emp_view AS
SELECT deptno,COUNT(*) total_employee,SUM(sal) total_salary
FROM emp1 GROUP BY deptno;
--SELECT * FROM user_views;
对视图操作报错
DELETE FROM emp_view WHERE deptno=10;
建立替代触发器
CREATE OR REPLACE TRIGGER emp_view_delete
INSTEAD OF DELETE ON emp_view FOR EACH ROW
BEGIN
DELETE FROM emp1 WHERE deptno=:old.deptno;
END;
再次执行DELETE
系统事件触发器:
系统事件触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语言,如CREATE 、ALTER及DROP 等。
而数据库系统事件包括数据库服务器的启动或关闭,用户的登录与退出、数据库服务错误等。
系统事件触发器既可以建立在一个模式上,又可以建立在整个数据库上。当建立在模式(SCHEMA)之上时,
只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器, 默认时为当前用户模式。
当建立在数据库(DATABASE)之上时,该数据库所有用户的DDL操作和他们所导致的错误,
以及数据库的启动和关闭均可激活触发器。要在数据库之上建立触发器时,要求用户具有ADMINISTER DATABASE TRIGGER权限。
使用SYS用户登录,做以下操作:
创建一个记录系统事件的表:
CREATE TABLE eventlog(
Eventname VARCHAR2(20) NOT NULL,
Eventdate date default sysdate,
Inst_num NUMBER NULL,
Db_name VARCHAR2(50) NULL,
Srv_error NUMBER NULL,
Username VARCHAR2(30) NULL,
Obj_type VARCHAR2(20) NULL,
Obj_name VARCHAR2(30) NULL,
Obj_owner VARCHAR2(30) NULL
)
创建触发器
BEGIN
-- 创建DDL触发器trig4_ddl
DBMS_UTILITY.EXEC_DDL_STATEMENT('
--由于在PL/SQL块中不能直接调用DDL语句,所以,利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT过程,由它执行DDL语句创建触发器。
CREATE OR REPLACE TRIGGER trig4_ddl
AFTER CREATE OR ALTER OR DROP
ON DATABASE
DECLARE
Event VARCHAR2(20);
Typ VARCHAR2(20);
Name VARCHAR2(30);
Owner VARCHAR2(30);
BEGIN
-- 读取DDL事件属性
Event := SYSEVENT;
Typ := DICTIONARY_OBJ_TYPE;
Name := DICTIONARY_OBJ_NAME;
Owner := DICTIONARY_OBJ_OWNER;
-- 将事件属性插入到事件日志表中
INSERT INTO sys.eventlog(eventname, obj_type, obj_name, obj_owner)
VALUES(event, typ, name, owner);
END;
');
end;
/
-- 创建LOGON、STARTUP和SERVERERROR 事件触发器
DBMS_UTILITY.EXEC_DDL_STATEMENT('
CREATE OR REPLACE TRIGGER trig4_after
AFTER LOGON OR STARTUP OR SERVERERROR
ON DATABASE
DECLARE
Event VARCHAR2(20);
Instance NUMBER;
Err_num NUMBER;
Dbname VARCHAR2(50);
User VARCHAR2(30);
BEGIN
Event := SYSEVENT;
IF event = ''LOGON'' THEN
User := LOGIN_USER;
INSERT INTO eventlog(eventname, username)
VALUES(event, user);
ELSIF event = ''SERVERERROR'' THEN
Err_num := SERVER_ERROR(1);
INSERT INTO eventlog(eventname, srv_error)
VALUES(event, err_num);
ELSE
Instance := INSTANCE_NUM;
Dbname := DATABASE_NAME;
INSERT INTO eventlog(eventname, inst_num, db_name)
VALUES(event, instance, dbname);
END IF;
END;
');
--创建LOGON事件触发器
DBMS_UTILITY.EXEC_DDL_STATEMENT('
CREATE OR REPLACE TRIGGER LOGIN_HIS
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO LOGTABLE
VALUES(USER,SYSDATE);
END;
');
-- 创建LOGOFF和SHUTDOWN 事件触发器
DBMS_UTILITY.EXEC_DDL_STATEMENT('
CREATE OR REPLACE TRIGGER trig4_before
BEFORE LOGOFF OR SHUTDOWN
ON DATABASE
DECLARE
Event VARCHAR2(20);
Instance NUMBER;
Dbname VARCHAR2(50);
User VARCHAR2(30);
BEGIN
Event := SYSEVENT;
IF event = ''LOGOFF'' THEN
User := LOGIN_USER;
INSERT INTO eventlog(eventname, username)
VALUES(event, user);
ELSE
Instance := INSTANCE_NUM;
Dbname := DATABASE_NAME;
INSERT INTO eventlog(eventname, inst_num, db_name)
VALUES(event, instance, dbname);
END IF;
END;
');
END;
查看eventlog表
SELECT eventname, eventdate, obj_type, obj_name, obj_owner, username, Srv_error
FROM eventlog;
包-函数-触发器的管理:通过视图访问
通过user_objects视图查看已存在的对象
select object_name,object_type,status
from user_objects
where object_type='PROCEDURE';
/
object_type='PROCEDURE' 查看已存在的过程
object_type='FUNCTION' 查看已存在的函数
通过对象名称查看USER_SOURCE视图,查看对象源代码
SELECT text FROM User_Source
WHERE NAME='INSERTDEPT'
/
WHERE NAME='INSERTDEPT'
/
如果对象状态为无效,查看User_Errors视图
SELECT line, position, text
FROM User_Errors
WHERE NAME='INSERTDEPT'
/
FROM User_Errors
WHERE NAME='INSERTDEPT'
/
trigger相关知识点
1. trigger 是自动提交的,不用COMMIT,ROLLBACK
2. trigger最大为32K,如果有复杂的应用可以通过在TRIGGER里调用PROCEDURE或FUNCTION来实现。
LOB类型
1 LOB类型分类
CLOB:字符LOB.用于存储大量的文本信息.采用默认字符集存储
NCLOB:用于存储字符LOB,采用数据库的国家字符集来存储字符.而不是数据库的默认字符集.
BLOB:二进制LOB,存储二进大量的二进制信息.存储时不会进行字符集转换.
CLOB和BLOG在ORACLE 10G中可存储8TB字节.
BFILE:二进制文件LOB,只是一个文件指针.具体的文件存储在操作系统中.
2 LOB类型存储方式
我们把CLOB,NCLOB,BLOB存储在数据库的内部称为内部LOB.这些存储方式都相似,所以可以一起进行讨论.
SQL> create table test_lob (id int primary key,remark clob);
Table created
对于LOB列的创建有非常多的选项.可以查ORACLE文档.
最简单的就是使用dbms_metadata来获得它的完整的脚本.
SQL> select dbms_metadata.get_ddl('TABLE','TEST_LOB') from dual;
得到如下结果
CREATE TABLE "YUAN"."TEST_LOB"
( "ID" NUMBER(*,0),
"REMARK" CLOB,
PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
LOB ("REMARK") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
LOB列的定义可以有以下属性.
存储的表空间,本例为USER.也就是说可以为LOB单独指定表空间.
ENABLE STORAGE IN ROW 默认的一个属性
CHUNK 属性
PCTVERSION 属性
NOCACHE 属性.
一个完整的STORAGE语句.
可见,LOB类型之前介绍的数据类型相比要复杂得多了.
当我们创建了一个带的LOB列的表后,我们可以从USER_SEGMENTS查到,数据库增加了几个段对象.
SQL> select segment_name,segment_type from user_segments;
SEGMENT_NAME SEGMENT_TYPE
--------------------------------- ------------------
BIN$nZwCJWDmQM+ygfB1U8tcIw==$0 TABLE
BIN$0jfW0nNQR/2JEQmbAmfcRQ==$0 TABLE
TEST_TIMESTAMP TABLE
TEST_TIMESTAMP2 TABLE
TEST_TIMESTAMPWZ TABLE
TEST_TIMELTZ TABLE
TEST_INTERVARYM TABLE
TEST_INTERVALYM2 TABLE
TEST_INTERVALDS TABLE
TEST_LOB TABLE
SYS_LOB0000043762C00002$$ LOBSEGMENT
SYS_IL0000043762C00002$$ LOBINDEX
SYS_C004324 INDEX
后面四个段空间对象.新增了四个物理段.普通表只会新增一个或两个段对象.类型为TABLE和INDEX.
而LOB列则额外新增了两个段对象,类型为LOBSEGMENT和LOBINDEX.
SYS_C004324是一个索引段,因为我们有一列为主键.
作为普通字段,数据就存放在表段中.索引就放在索引段中.
而对于LOB数据,数据并不是存在表段中,而是存放在LOBSEGMENT段中.(有些情况下是存放在表test_lob中的.后面会讲)
LOBINDEX用于指向LOB段,找出其中的某一部分.
所以存储在表中的LOB存储的是一个地址,或者说是一个指针,也可以说是一个LOB定位器(LOB locator).
存储在LOBindex中的应该是每一个LOB行的地址.数据是具体存储在LOBSEGMENT中的.
我们先从TEST_LOB的LOB列中找到一个地址,然后在LOBINDEX中来查找这些字节存储在哪里.然后再访问LOBSEGMENT.由此我们可以把lobindex和lobsegment想成是一个主/细表的关系.
实际上lob列中存的是一个地址段.然后在lobindex找到所有的地址段.然后在lobSegment中把所有地址段的值都读取了来
3 LOB类型存储参数介绍
在此,我们已经基本了解了LOB是怎么存储的.我们也从脚本中看到了LOB类型的参数.现在我们就来了解这些参数
1. LOB表空间
LOB ("REMARK") STORE AS (
TABLESPACE "USERS"
在test_lob表中的create语句中包含上面的语句.这里指定的表空间指的是存储lobindex 和lobsegment的表空间.也就是说,存放lob数据与LOB列所在的表是可以在不同的表空间的.
数据表和LOB存放在不同的表空间.
为什么LOB数据会放在不同的表空间呢?这主要还是管理和性能的问题.
LOB数据类型代表了非常巨大的容量.在ORACLE 10G之前,LOB列可以存放4GB字节的数据.在ORACLE 10G 中LOB类型可以存放8TB字节的数据.这是非常庞大的数据.
所以就有必要为LOB数据使用一个单独的表空间,对于备份和恢复以及空间管理.你甚至可以让LOB数据使用另外一个区段大小,而不是普通表数据所用的区段大小.
另外从I/O性能的角度考虑.LOB是不在缓冲区缓存中进行缓存.因此每个LOB的读写,都会产生物理I/O.正因为如此,如果我们很清楚在实际的用户访问中,有些对象会比大部分其它对象需要花费更多的物理I/O,那么就需要把这些对象分离到其它的磁盘.
另外,lobindex 和lobsegment是在同一个表空间中的.不可以把lobindex和lobsegment放在不同的表空间中.在oracle 8i之前版本,允许将lobindex和lobsegment放在不同的表空间中.
2. IN ROW 语句
LOB ("REMARK") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW
我们已经了解了LOB类型的存储结构,但是这种结构会带来额外的磁盘访问.不管是读还是写都会比普通数据类型要慢及带来更多的物理I/O.
针对这种情况,ORALCE作出了个改进就是IN ROW 语句.
使用ENABLE STORAGE IN ROW从字面上理解就是允许行内存储.当LOB的内容小于4000字节时,就把数据存储在数据表中的,即LOB数据与数据表都是同一个表空间中.这里的LOB就相当于VARCHAR2一样,这里LOB列的数据还可以进入缓冲区进行存储.当LOB内容超过了4000字节后,就会把数据移到lobsegment中去.
当定义一个LOB列时,它的大小一般都是小于4000字节的,启用IN ROW 是非常重要的.
如果要禁用IN ROW ,就使用DISALBE STORAGE IN ROW
3. CHUNK 参数
LOB ("REMARK") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
CHUNK 意为大块,块.是指LOB存储的单位.指向LOB数据的索引会指向各个数据块.CHUNK是逻辑上连续的一组数据块.CHUNK是指LOB的最小分配单元.而数据库的最小内存分配单元是数据块(BLOCK).CHUNK大小必须是ORACLE块大小的整数倍.
我们先来了解一下LOB与CHUNK的关系.
1. 每一个LOB实例(即每一行的LOB值)会至少占用一个CHUNK.
用我们本节的数据表test_lob为例,remark列为LOB类型.
假设该表有1000行数据,每一行的remark列的值大小都为7KB.
这样数据库就会分配1000个CHUNK.如果CHUNK的大小设置是64KB,就会分配1000个64KB的CHUNK.如果CHUNK的大小为8KB,就分配1000个8KB的CHUNK.
重要的一点就是一个CHUNK只能由一个LOB对象使用.这有一点像CHAR这种定长类型.如果把CHUNK设为64KB,而实际上我们每一个LOB对象只有7KB的大小,每一列浪费57KB的空间.1000列就浪费了55M的空间.而把CHUNK设为8KB,1000列大约浪费1M的空间.
我们还知道lobindex,且于指向各个块.它会记录每个块的地址.所以当块越多时,索引就越大,索引越大时,读写就会更慢.整体的性能就会降低.
比如每个列的LOB字段实际值大约8M,使用8KB的CHUNK.那么就需要1024个CHUNK.那么在lobindex中就会有1024条记录,用来指向这些CHUNK.
指定CHUNK值,影响到性能和空间.
如果CHUNK过大,就会白白浪费存储空间,如果CHUNK过小,就会降低性能.
所以我们需要在空间和性能上进行取舍和折中.
4. PCTVERSION 语句
LOB ("REMARK") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
PCTVERSION用于控制LOB的读一致性.普通字段都会有UNDO记录的.而lobsegment是没有undo记录的.而是直接在lobsegment本身中维护停息的版本.lobindex会像其它段一样生成undo记录.但是lobsegment不会.
修改一个LOB对象时,oracle会分配一个新的CHUNK,而来的CHUNK会被保留下来.如果事务正常的提交了,lobindex就像指向新的CHUNK.如果事务被回滚了,lobindex就再指回原来的CHUNK.所以undo维护是在LOB段自身中实现的.
这样一来,就会有非常多的无用的CHUNK被开销了.这也是非常大的空间损耗.这些CHUNK指的是数据的旧版本信息.那如何来控制这些旧版本数据占用的空间呢?这就是PCTVERSION的作用.也就是说用多少额外的空间来存储旧版本数据.我们可以看到默认的值是10%.如果你确实经常修改LOB,那么就需要把它设为10%就不够了,需要增加这个值.
5. CACHE参数
LOB ("REMARK") STORE AS (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE
除了NOCACHE外,这个选项还可是CACHE和CACHE READS.这个参数控制lobsegment数据是否存储在缓冲区的缓存中.默认为NOCACHE,也就是每次访问都是从磁盘直接读取写.
CACHE READS允许缓存从磁盘读的LOB数据.但是写入LOB数据是直接写进磁盘的.
CACHE则是允许读和写都能缓存LOB数据.
有些情况下,LOB字段只有几KB大小,进行缓存就非常有用了.如果不缓存,当用户更新LOB字段时,还必须进行等待,从磁盘直接读数据和写数据.
如果要修改缓存设置可以用下面的语句
ALTER TABLE test_lob modify LOB(remark) (CACHE);
ALTER TABLE test_lob modify LOB(remark) (NOCACHE);
ALTER TABLE test_lob modify LOB(remark) (CACHEREADS);
但是对于大数据量的LOB读写,比如超过了20M.是没有理由把它放进缓存的
3 BFILE
BFILE类型只是操作系统上一个文件的指针.用于对存储在操作系统中的文件提供只读访问.
使用BFILE时,还可以使用一个DIRECTORY 对象.DIRECTORY 是将一个操作系统目录映射到数据库的一个串.以便于提供可移值性.
SQL> create table test_bfile(id int primary key, moviefile bfile);
Table created
SQL> create or replace directory movie_directory as 'D:/movie';
Directory created
SQL> insert into test_bfile values(1,bfilename('movie_directory','英雄.dat'));
1 row inserted
对BFILE的操作需要使用DBMS_LOB包来进行.提供了一系统方法和函数
参照变量
参照变量指用于存放数值指针的变量。通过使用参照变量,可以使得应用程序共享相同对象,降低占用空间。在编写Pl/sql程序时,可以使用游标
变量(REF CURSOR)和对象类型变量REF obj_type等两种参照变量类型。
1、REF CURSOR
当使用显式游标时,需要在定义显式时指定相应的SELECT语句,这种显式游标称为静态游标。当使用变量变量时,定义游标变量时不需要指定
SELECT语句,而是在打开游标时指定SELECT语句,从而实现动态的游标操作。
实例:
DECLARE
type cl IS REF CURSOR;
emp_cursor cl;
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;
注:cl为 REF CURSOR类型,而emp_cursor 为游标变量,并且再打开游标时指定了其对应的SELECT语句。
2、REF obj_type
当编写对象类型应用时,为了共享相同对象,可以使用REF引用对象类型,REF实际是指向对象实例的指针。
实例:
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;--利用对象类型创建了对象表homes
CREATE TABLE person(
id NUMBER(6) PRIMARY KEY,
name VARCHAR2(10),addr REF home_type --引用对象
);
嵌套表类型
嵌套表元素的下标从1开始,并且元素个数没有限制。注意:索引表类型不能作为表列的数据类型使用,但嵌套表类型可以作为表列的数据类型使用。语法如下:
TYPE type_name IS TABLE OF element_type;
identifier type_name;
type_name用于指定嵌套表的类型名;element_type用于指定嵌套表元素的数据类型,identifier用于定义嵌套表变量。当使用嵌套表元素时,必须首先使用其构造方法初始化嵌套表。
1.在PL/SQL块中使用嵌套表
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
ename_table ename_table_type;
BEGIN
ename_table:=ename_table_type('xu','jian','ming');--必须初始化嵌套表变量,ename_table_type()是其构造方法。
SELECT ename INTO ename_table(3) FROM emp WHERE empno=&no;
dbms_output.put_line('employee''s name is '||ename_table(3));
END;
/
2.在表列中使用嵌套表
如果在表列中使用嵌套表类型,必须首先使用CREATE TYPE命令建立嵌套表类型。当使用嵌套表类型作为表列的数据类型时,必须要为嵌套表列指定专门的存储表。
SQL> create type phone_type IS table of varchar2(20);
2 /
SQL> create table employee2(id number(4),name varchar2(10),sal number(6,2),phone phone_type) nested table phone store as phone_table;
示例一:在PL/SQL块中为嵌套表列插入数据
当定义嵌套表类型时,oracle自动为该类型生成相应的构造方法,当为嵌套表列插入数据时,需要使用嵌套表的构造方法。
SQL> begin
2 insert into employee2 values(1,'SCOTT',1000,phone_type('0755-88888888','13000000000'));
3 end;
4 /
示例二:在PL/SQL块中检索嵌套表列的数据
当在PL/SQL块中检索嵌套表列的数据时,需要定义嵌套表类型的变量接收其数据。
SQL> declare
2 phone_table phone_type;
3 begin
4 select phone into phone_table from employee2 where id=1;
5 for i in 1..phone_table.count loop
6 dbms_output.put_line('phonenumber is '||phone_table(i));
7 end loop;
8 end;
9 /
示例三:在PL/SQL块中更新嵌套表列的数据
当在PL/SQL块中更新嵌套表列数据时,首先需要定义嵌套表变量,并使用构造方法初始化该变量,然后才可以在执行部分使用UPDATE语句更新其数据。
SQL> declare
2 phone_table phone_type:=phone_type('0755-11111111','13555555555','13666666666');
3 begin
4 update employee2 set phone=phone_table where id=1;
5 end;
6 /
可变数组
可变数组的访问与嵌套表,index-by表访问类似.
定义语法
TYPE type_name IS {VARRAY | VARYING ARRAY} (maximum_size) OF element_type[NOT NULL];
可变数组类似与嵌套表和indec-by表,但是可变数组在大小方面有一个固定的上限,可变数组元素的插入以索引1开始,可变数组的极限大小是 2G字节。元素子内存中是连续存储。
集合类型之间的比较
集合类型之间的比较
1、可变数组与嵌套表
相同点:1.两种类型都使用PL/SQL中的下标符号来允许对单个元素的访问。
2.两种类型都可以存储在数据库表中(当在PL/SQL语句块外声明时)。
3.集合方法可以应用于这两种类型。
不同点:1.可变数组有大小上限,而嵌套表没有一个明确的大小上限。
2.当存储到数据库中时,可变数组保持了元素的排序和下标的值,而嵌套表却不同。
2、嵌套表与index-by表
相同点:1.两种表的数据类型具有相同的结构。
2.两种表的单个元素都是使用下标符号进行访问的。
3.嵌套表可用的方法包括index-by表的所有表属性。
不同点:1.嵌套表可以使用 SQL进行操作,而且可以存储到数据库中,而inedx-by表则不能。
2.嵌套表合法的下标范围为1...2147483647,而index-by 表的范围为 -2147483647...+2147483647
3.index-by表可以有负数下标,而嵌套表则不能。
4.嵌套表可以自动为null(用is null 操作符检验)
5.如果要添加元素,必须初始化和扩展嵌套表。
6.嵌套表有可用的其它方法,如extend
系统实用工具包
dbms_metadata.get_ddl
查看对象的定义语句.要做的准备:
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF
SET LONG 999999
查看表的DDL 语句
SQL> select dbms_metadata.get_ddl('TABLE','EMP') from dual;
查看表空间的DDL 语句
SQL> select dbms_metadata.get_ddl('TABLESPACE', 'USERS') FROM DUAL;
查看 用户的定义语句。
SQL> select dbms_metadata.get_ddl('USER', 'SCOTT') FROM DUAL;
查看 索引的定义语句:
SQL> select dbms_metadata.get_ddl('INDEX','E_ENO_IND') FROM DUAL;
过程调用流程图
-- The flow of procedure calls will typically look like this:
--
-- -----------
-- | open_cursor |
-- -----------
-- |
-- |
-- v
-- -----
-- ------------>| parse |
-- | -----
-- | |
-- | |---------
-- | v |
-- | -------------- |
-- |-------->| bind_variable | |
-- | ^ ------------- |
-- | | | |
-- | -----------| |
-- | |<--------
-- | v
-- | query?---------- yes ---------
-- | | |
-- | no |
-- | | |
-- | v v
-- | ------- -------------
-- |----------->| execute | ->| define_column |
-- | ------- | -------------
-- | |------------ | |
-- | | | ----------|
-- | v | v
-- | -------------- | -------
-- | ->| variable_value | | ------>| execute |
-- | | -------------- | | -------
-- | | | | | |
-- | ----------| | | |
-- | | | | v
-- | | | | ----------
-- | |<----------- |----->| fetch_rows |
-- | | | ----------
-- | | | |
-- | | | v
-- | | | --------------------
-- | | | | column_value |
-- | | | | variable_value |
-- | | | ---------------------
-- | | | |
-- | |<--------------------------
-- | |
-- -----------------|
-- |
-- v
-- ------------
-- | close_cursor |
-- ------------
--
---------------
--
-- -----------
-- | open_cursor |
-- -----------
-- |
-- |
-- v
-- -----
-- ------------>| parse |
-- | -----
-- | |
-- | |---------
-- | v |
-- | -------------- |
-- |-------->| bind_variable | |
-- | ^ ------------- |
-- | | | |
-- | -----------| |
-- | |<--------
-- | v
-- | query?---------- yes ---------
-- | | |
-- | no |
-- | | |
-- | v v
-- | ------- -------------
-- |----------->| execute | ->| define_column |
-- | ------- | -------------
-- | |------------ | |
-- | | | ----------|
-- | v | v
-- | -------------- | -------
-- | ->| variable_value | | ------>| execute |
-- | | -------------- | | -------
-- | | | | | |
-- | ----------| | | |
-- | | | | v
-- | | | | ----------
-- | |<----------- |----->| fetch_rows |
-- | | | ----------
-- | | | |
-- | | | v
-- | | | --------------------
-- | | | | column_value |
-- | | | | variable_value |
-- | | | ---------------------
-- | | | |
-- | |<--------------------------
-- | |
-- -----------------|
-- |
-- v
-- ------------
-- | close_cursor |
-- ------------
--
---------------