PLSQL学习笔记

 

PL/SQL学习笔记

笔记内容说明

PL/SQL

目录

1PL/SQL简介 1

1.1什么是PL/SQL 1

1.2 PL/SQL程序结构 1

1.3 PL/SQL运行过程 1

1.4注释 2

2、 变量与数据类型 3

2.1数据类型 3

2.2标量类型 3

2.3变量声明 3

3、 流程控制语句 5

3.1条件语句 5

3.2循环语句 6

4PL/SQL中的SQL 9

4.1 PL/SQL中的SQL分类 9

4.2 DMLinsertupdatedelete)和TCLcommitrollback 9

4.3 DDL 9

5PL/SQL中的select 11

5.1 select语句的实现 11

5.2 record类型 11

5.3 %rowtype 12

5.4 record变量的引用 12

5.5 cursor的概念 13

5.6 cursor的分类 13

5.7显式cursor的处理 13

5.8显式cursor的属性 14

5.9隐式cursor的属性 14

5.10 cursor的声明 14

5.11 open cursor 14

5.12 fetch cursor 15

5.13结果集提取的控制 15

5.14 close cursor 15

6、 集合 19

6.1什么是collection 19

6.2什么是关联数组 19

6.3 Associative arrays的定义 19

6.4声明Associative arrays类型和变量 19

6.5关联数组的操作 20

6.6 Associative arrays的方法 20

6.7关联数组的遍历 21

6.8 Associative arrays的异常 22

6.9批量绑定 22

7、 异常 25

7.1 Oralce错误 25

7.2 Oralce错误处理机制 25

7.3异常的类型 25

7.4 PL/SQL中的异常 25

7.5异常捕获 25

7.6异常的捕获规则 26

7.7 Oralce预定义错误的捕获 26

7.8Oracle预定义异常 27

7.9用户自定义异常 27

7.10异常处理总结 28

7.11 sqlcodesqlerrm 28

7.12异常的传播 29

8、 子程序 30

8.1子程序 30

8.2有名子程序 30

8.3有名子程序的分类 30

8.4有名子程序的优点 30

9、 过程procedure 31

9.1语法 31

9.2创建存储过程 31

9.3形参和实参 31

9.4形参的种类 32

9.5调用存储过程 32

9.6存储过程中的参数 32

9.7对实际参数的要求 33

9.8形式参数的限制 33

9.9带参数的过程调用 33

9.10使用缺省参数 33

9.11存储过程中的DDL语句 34

9.12变量 35

9.13 PL/SQL中的SQL分类 36

9.14 再一次来看SQL语句的处理过程 37

9.15软分析和硬分析 37

9.16对过程procedure的基本操作 37

9.17案例 38

10、 函数function 39

10.1语法 39

10.2创建函数 39

10.3调用函数 39

10.4对函数function的基本操作 40

10.5过程和函数的比较 40

10.6匿名块中的过程和函数声明 40

10.7案例 40

11、 包package 42

11.1什么是package 42

11.2包的组成 42

11.3包的优点 42

11.4 package声明的语法 42

11.5 package body声明的语法 43

11.6编译包和包体 43

11.7案例 43

12、 触发器trigger 46

12.1面临问题 46

12.2 DML触发器的组成 46

12.3 DML触发器的类型 46

12.4 DML触发器的触发顺序 46

12.5 DML行级触发器 46

12.6 :OLD:NEW 47

12.7触发器的重新编译 48

12.8触发器的状态 48

13、 其他注意事项 50

13.1 PL/SQL的特点 50

13.2PL/SQL的好处 50

13.3命名建议 50

13.4搞清楚如下内容 50

13.5保证所有对象的状态都是valid 50

13.6 declare中都可声明什么 50

13.7数据库对象 50

 

1、PL/SQL简介

1.1什么是PL/SQL

PL/SQLProcedural Language/SQL)是Oracle在标准SQL的基础上增加了过程化处理,把DMLselect语句组织在PL/SQL代码的过程性单元中,通过逻辑判断、循环等操作,实现复杂的功能或者计算的程序语言。

扩展:变量和类型、控制结构、过程与函数。

  • 注意事项:

  • java中是写方法,把复杂的业务逻辑写入方法中,再调用方法。

  • PL/SQL是写过程、函数,把复杂的业务逻辑写入过程、函数中,再调用它们。

1.2 PL/SQL程序结构

PL/SQL块:包含三部分,声明部分:declare;执行部分:begin;异常处理:exception

eg1:语法

declare

v_AccountID number(5):=1001;

v_RealName varchar(20);

begin

select real_name info v_RealName from account where id=v_AccountID;

exception

when on_data_found then

insert info Fee_Log(desrc) values('Account 1001 dece not exit!')

commin;

end;--(不需要理解代码的具体含义!)

  • 注意事项:最简写的方式为begin end;

eg2:打印Hello World

begin

dbms_output.put_line('Hello World');

end;

  • 注意事项:

  • dbms_output是系统提供的包package,包含多个过程、函数。其中的过程put_line实现的是输出功能,只有一个参数,只能为“字符类型”(日期和数值也可,系统自动转换!布尔类型不行!但java中可以),用于接收需输出的字符串。在sql工作表子窗口中可以调用存储过程。

  • 想要在屏幕上输出需要写:set serveroutput on(在begin上面写)。

  • 如何调用过程:begin 包名.过程名();所有过程都是没有返回值的,即java中的void

1.3 PL/SQL运行过程

如下图所示:

 

 

1.4注释

增加可阅读性,使程序更容易理解。编译时将被忽略。

1)单行注释:由两个连字符“--开始,到行尾都是注释。

2)多行注释:由“/*开头,由“*/结尾。

2、变量与数据类型

2.1数据类型

标量类型:数字型、字符型、日期型、布尔型;可以直接用的。

复合类型:recordassociative arraynested tablevarray;需要自己去定义的。

2.2标量类型

  1. 数字类型:①number number的子类型dec(38)float(38)real(18)

③binary_integer(只能在PL/SQL中用),按10进制赋值,但存的时候会换成2进制存。优势是计算快。

2)字符类型:①varchar2varchar(长度:1.~32767)②string(只能在PL/SQL中用,长度:1.~32767)③char(长度:1~32767)④long

3)日期类型:date

4)布尔类型:boolean

用于存储逻辑值true,false,nulljava中只有true,false)。

不能向数据库中插入boolean类型的数据。

不能将列值保存到boolean变量中。

只能对boolean变量执行逻辑操作。

2.3变量声明

1)语法:var_name type [constant][not null][:=value];

  • 注意事项:PL/SQL规定没有初始化的变量为null

2)直接定义类型

declare

v_n1 number :=1;--赋初值要有“冒号”,PL/SQL中赋值和等号是区分开的

v_c1 varchar2(10);

v_d1 date :=sysdate;--sysdate是函数,有返回值

begin

  • 注意事项:

  • PL/SQL中没返回值的叫过程;有返回值的叫函数,且必须有指向(要么打印出,要么赋值给变量)。所以,过程和函数的调用是不一样的。

  • java中的方法一种没有返回值,一种是有返回值;有返回值的如果没返回,则会丢弃。但PL/SQL不行!

3%type方式:变量具有与数据库的表中的某列或其他变量相同的类型。

eg1:三种声明方式

declare v_RealName varchar2(20);

declare v_RealName account.real_name%type;

declare v_TempVar number(7,3) not null :=12.3;

v_AnotherVar v_TempVar%type :=12.3;

 

eg2:定义变量,打印系统时间,并输出它们的值

 

 

set serveroutput on

declare

v_d1 date :=sysdate;

v_c1 varchar2(20);

begin

v_c1 :=to_char(v_d1,'yyyy mm dd hh24:mi:ss');

dbms_output.put_line('Current date is ' || v_c1);

--put_line只有一个参数,所以必须拼接!

end;

4)复合类型

record类型、集合类型。详细内容见后面的第五、六章!

3、流程控制语句

条件语句:ifcase;循环语句:loopwhilefor

3.1条件语句

  1. if语句

方式一:

if then

statement;

end if;//最简写的方式

方式二:

if then

statement;

else

statement1;

end if;

方式三:

if boolean_expr then

statement;

elsif boolean_expr then

statement1;

else

statement2;

end if

  • 注意事项:else ifPL/SQL中的写法是elsif

eg:输出布尔类型的变量的值

declare

v_b1 boolean :=false;

begin

if v_b1 then

dbms_output.put_line('true');

elsif v_b1 is null then

dbms_output.put_line('null');

else

dbms_output.put_line('false');

end if;

end;--注意:put_line中的参数只能为字符类型,但日期和数值会隐式转换,布尔类型不行!

2case语句

case when then

when then

else

end;

3.2循环语句

1loop循环(无条件进入)

语法:

loop

statement1;

statement2;

exit when<condition>

end loop;

  • 注意事项:

  • exit when<condition>子句是必须的,否则循环将无法停止。

  • when后面直接写条件,没有尖括号或圆括号。

eg1:循环输出数字110

declare v_index binary_integer :=1;

begin

loop

dbms_output.put_line(v_index);

v_index := v_index+1;

exit when v_index>10;

end loop;

end;

eg2:循环插入10条记录(静态)

declare v_index binary_integer :=1;

begin

loop

insert into test_chang values(v_index);

v_index := v_index+1;

exit when v_index>10;

end loop;

end;

eg3:循环输出语句insert into test_chang values('i')i为字符。

declare v_index binary_integer :=1;

begin

loop

dbms_output.put_line('insert into test_chang values('''||v_index||''')');

v_index := v_index+1;

exit when v_index>10;

end loop;

end;

2while循环

语法:

while<boolean expr>loop

statement1; statement2;

end loop;

循环语句执行的顺序是先判断<boolean expr>的真假,如果为true则循环执行,否则退出循环。

while循环语句中仍然可以使用exitexit when子句。

  • 注意事项:while后面直接写条件,没有尖括号或圆括号。

eg1:循环输出数字110

declare v_index binary_integer :=1;

begin

while v_index <=10 loop

dbms_output.put_line(v_index);

v_index := v_index+1;

end loop;

end;

eg2:循环插入10条记录(动态)

declare v_index binary_integer :=1;

begin

while v_index <=10 loop

execute immediate 'insert into test_chang values('||v_index||')';

v_index := v_index+1;

end loop;

end;

3for循环

语法:

for 循环计数器in 下限.. 上限loop

statement1;

statement2;

end loop;

循环计数器是一个变量,这个变量不需要声明(和java不一样)。它的作用域仅是在循环中。

每循环一次,循环变量自动加1;使用关键字reverse,循环变量自动减1

可以使用exit或者exit when子句退出循环。

  • 注意事项:

  • 跟在inin reverse后面的数字必须是从小到大的顺序,但不一定是整数,可以是能够转换成整数的变量或表达式。

  • 不能用null作上限或下限下标!

eg1:循环输出数字110

begin

for i in 1..10 loop

dbms_output.put_line(i);

end loop;

end;

eg2:使用in reverse循环输出数字110

begin

for i in reverse 1..10 loop

dbms_output.put_line(i);

end loop;

end;

eg3:循环插入10条记录(静态)

begin

for i in 1..10 loop

insert into test_chang values(i);

end loop;

end;

4、PL/SQL中的SQL

4.1 PL/SQL中的SQL分类

1)静态SQL

PL/SQL块中使用的SQL语句在编译时是明确的,执行的是确定对象,即SQL语句是在PL/SQL编译阶段编译的。效率高。能用静态就不用动态!

2)动态SQL

PL/SQL块编译时SQL语句是不确定的,如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进行处理,只是在程序运行时动态的创建语句、对语句进行语法分析并执行该语句。效率低。

4.2 DMLinsertupdatedelete)和TCLcommitrollback

1)它们可以直接在PL/SQL中使用标准的SQL语句。

2)语法:

begin

insert into host(id) values('10.0.0.11');

commit;

end;

4.3 DDL

1)不能原封不动的像DMLTCL那样直接写。

2)所有的DDL语句要在PL/SQL中操作,必须用如下的语法形式(转成字符串),即本地动态SQL是使用execute immediate语句来实现的。

eg1:可执行

begin

execute immediate 'create table test_chang(c1 number)';

end;--本地动态SQL执行DDL语句

eg2:不可执行

begin

execute immediate 'create table test_chang(c1 number)';

insert into test values(1);

commit;

end;--报错!是编译错误,说test_chang不存在!说明表还没有创建,只是在编译期编译。

eg3:可执行

begin

execute immediate 'create table test_chang(c1 number)';

execute immediate 'insert into test_chang values(1)';

commit;

end;--编译,执行都通过!使用动态SQL

  • 注意事项:在一段PL/SQL程序中,先createinsert,必须用execute immediate(静态sql和动态sql

 

 

eg4:用循环向test_chang表中插入10条记录。(从110

begin

execute immediate 'create table test_chang(c1 number)';

for i in 1..10 loop

execute immediate 'insert into test_chang values('||i||')';

end loop;

commit;

end;

5、PL/SQL中的select

5.1 select语句的实现

根据select语句返回的记录数,将select语句的实现分为两类:

1)当且仅当只返回“一条”记录:用selectinto语句实现(将结果放入到变量中)。

2)返回“0条或多条”记录:用cursor实现。

  • 注意事项:select into确认select只返回一条记录,才不报错,否则加异常处理(没有返回记录或返回记录太多)。

eg:提供客户ID,打印客户姓名和年龄

declare

v_realname account.real_name%type;

v_age number(3);

begin

select real_name,round((sysdate-birthdate)/365)//减出的是天数

into v_realname,v_age

from account

where id=1005;--换成where 1=2报错:异常,无数据;换成where 1=1报错:返回记录太多

dbms_output.put_line('姓名:' | | v_realname||' 年龄:' | |v_age);

end;

3select...into后的查询结果集种类

若查询结果是单行单列,into子句后用标量类型(标准类型),与select子句后的目标数据类型一致即可。

若查询结果是单行多列,into子句后的变量个数、顺序、每个变量的数据类型,应该与select子句后的目标数据相匹配;也可以用记录类型的变量。

5.2 record类型

记录类型,处理单行多列数据。

eg1:语法

declare

type t_cost_rec id record(base_cost cost.base_cost%type,--t_cost_rec是记录类型

base_duration cost.base_duration%type,--base_cost是成员

unix_cost cost.unit_cost%type);

v_cost t_cost_rec;--v_cost是记录类型的变量

v_cost_1 t_cost_rec;

eg2:打印每个客户的名字,年龄,身份证

declare

type t_account_rec is record(real_name account.real_name%type,

age number(3), idcard_no char(18));

v_account t_account_rec;

begin

select real_name,round((sysdate-birthdate)/365),idcard_no into v_account

from account

where id=1005;

dbms_output.put_line('姓名:'||v_account.real_name);

dbms_output.put_line('年龄:'||v_account.age);

dbms_output.put_line('身份证:'||v_account.idcard_no);

end;--t_account_rec是记录类型,real_name是成员,v_account是记录类型的变量。

5.3 %rowtype

用表结构或视图结构定义变量

当使用%rowtype定义记录变量时,record成员的名称和类型与表或视图的列名称和类型完全相同。

eg:语法

declare v_cost cost%rowtype;

5.4 record变量的引用

1)记录类型变量的属性引用方法是“.引用,即变量名.属性名。

eg1:语法

declare

v_cost.base_cost :=5.9;

v_cost.base_duration :=20;

v_cost.unit_cost :=0.4;

v_cost_1 :=v_cost;

select base_cost,base_duration,unit_cost into v_cost_1 from cost where id =2;

eg2:语法

declare

v_cost cost%rowtype;

begin

select * into v_cost from cost where id=1;

dbms_output.put_line(v_cost.name);

dbms_output.put_line(v_cost.base_duration);

dbms_output.put_line(v_cost.descr);

end;

  • 注意事项:打印记录类型,一定是打印它的成员!

2)若用PL/SQL程序对表进行DML操作,则该表必须事先存在,用SQL建表。

eg:语法

create table cost_t1 as select base_cost,base_duration,unix_host from cost where id=3;

3)在insert语句和update语句中可以使用记录类型变量。

eg:语法

begin

insert into cost_t1 values v_cost;

update cost_t1 set row = v_cost_1;

commit;

end;

  1. 案例

 

eg:提供客户ID,打印客户名称和身份证号,若该客户不存在,打印客户不存在

declare

type t_account_rec is record(real_name account.real_name%type,idcard_no char(18));

v_account t_account_rec;

v_cnt number;

begin

select count(*) into v_cnt from account where id =1005;

if v_cnt=1 then

select real_name,idcard_no into v_account from account where id=1005;

dbms_output.put_line('姓名:'||v_account.real_name);

dbms_output.put_line('身份证:'||v_account.idcard_no);

else

dbms_output.put_line('用户不存在');

end if;

end;

5.5 cursor的概念

1)根据select语句返回的记录数,若返回记录数是0条或多条用cursor实现。

2Oralce使用专有SQL工作区(private SQL workarea)来执行SQL语句,存储处理信息。这个工作区称为“cursor

3Oracle所执行的每一个SQL语句都有唯一的cursor与之相对应。

4)程序员用PL/SQLcursor定义所需执行的select语句。

5.6 cursor的分类

隐式:selectinto语句、DML语句

显式:返回多条记录的select语句用显式cursor实现。

5.7显式cursor的处理

如下图所示:

 

 

 

5.8显式cursor的属性

属性

类型

描述

cursor_name%isopen

布尔

如果cursoropen的,其值为true

cursor_name%notfound

布尔

如果前一个fetch语句没有返回一行记录,其值为true

cursor_name%found

布尔

如果前一个fetch语句返回记录,其值为true

cursor_name%rowcount

数值

到目前为止,cursor已提取的总行数

目的:获取有关显式cursor的状态信息。

  • 注意事项:

  • 布尔值(truefalsenull),当不fetch时,open后直接%found,则返回null

  • %notfound%found缺省为null,由fetch去改变。

  • sqlplus命令:exec 过程名,即可直接调用过程。

5.9隐式cursor的属性

目的:获取有关隐式cursor的状态信息。

属性

类型

描述

sql%isopen

布尔

DML执行中为true,结束后为false

sql%notfound

布尔

SQL%found属性返回值相反

sql%found

布尔

值为true表示DML操作成功

sql%rowcount

数值

表示DML语句成功执行的数据行数

  • 注意事项:sql是指最近的sql语句。

eg:相关操作

begin

insert into test_chang values(100);

dbms_output.put_line(sql%rowcount);

update test_chang set c1=10 where c1=100;

dbms_output.put_line(sql%rowcount);

end;

5.10 cursor的声明

1)在游标声明中使用标准的select语句。

2)如果需要按指定的次序处理行,可在查询中使用order by子句。

3)可以在查询中引用变量,但必须在cursor语句之前声明这些变量。

eg:语法

declare

cursor c_service_id(p_cost_id number)is select id from service where cost_id=p_cost_id;

begin

5.11 open cursor

1)通过open cursor来执行select语句并标识结果集。

2select语句如果没有返回记录,不会出现异常。

3)语法:open c_service_id(5);

5.12 fetch cursor

1)检索当前行,把值赋给变量。

2)变量可以是record类型或简单变量。

3)如果是简单变量:①包含相同数量的变量。②把每一个变量和相应的列进行位置匹配。

4)通过循环检测cursor中是否包含数据行。

5)语法:

fetch cursor_name into [var1,var2,|record_name];

fetch c_service_id into v_service_id

  • 注意事项:

  • fetch into 变量:变量一定是记录类型,与select后的列的个数、类型相同。

  • cursor不会因为select子句的结果集为0或多值报错。

5.13结果集提取的控制

1)使用循环处理显式cursor结果集中的多行数据。

2)每次fetch一行,反复进行。

3)使用%notfound属性检测一次不成功的提取操作。

4)使用显式cursor的属性检测每一次提取是否成功,避免出现无限循环。

5.14 close cursor

1)处理完结果集中的数据后,应该关闭cursor

2)如果需要,可以再次打开该cursor

3cursor一旦关闭,所有和该cursor相关的资源都会被释放,不可再从关闭的cursor中提取数据。

4)任何对关闭的cursor的操作都会引发invalid_cursor错误。

5)每个session能打开的cursor数量由open_cursor参数决定。

6)语法:close c_service_id;

eg1:打印每个客户的名字和身份证号码(使用loop循环处理cursor)。

declare

cursor c_account is select real_name from account where 1=1;

v_realname varchar2(20);

begin

open c_account;--open则结果集已经在内存中了

loop

fetch c_account into v_realname;

exit when c_account%notfound;

dbms_output.put_line(v_realname);--若输出在判断前,则最后的记录多打印一次

end loop;

close c_account;

end;

eg2:打印每个客户的名字和身份证号码,若查询没有记录返回,打印客户不存在(使用loop循环处理cursor)。where 1=1输出所有用户姓名,where 1=2输出no account

declare

cursor c_account is select real_name from account where 1=1;

v_realname varchar2(20);

begin

open c_account;

fetch c_account into v_realname;

if c_account%notfound then

dbms_output.put_line('no account');

else

loop

dbms_output.put_line(v_realname);

fetch c_account into v_realname;

exit when c_account%notfound;

end loop;

end if;

close c_account;

end;

eg3:打印每个客户的名字和身份证号码,若查询没有记录返回,打印客户不存在(使用loop循环处理cursor)。v_realname也可用rowtype定义为cursor类型,但要注意变量的引用。

declare

cursor c_account is select real_name from account where 1=1;

v_realname c_accountrowtype;

begin

open c_account;

fetch c_account into v_realname.real_name;

if c_account%notfound then

dbms_output.put_line('no account');

else

loop

dbms_output.put_line(v_realname.real_name);

fetch c_account into v_realname.real_name;

exit when c_account%notfound;

end loop;

end if;

close c_account;

end;

eg4:打印每个客户的名字和身份证号码,若查询没有记录返回,打印客户不存在(使用while循环处理cursor)。

declare

cursor c_account is select real_name,idcard_no from account where 1=1;

v_account c_account%rowtype;

begin

open c_account;

fetch c_account into v_account;

if c_account%notfound then

dbms_output.put_line('no account');

else

while c_account%found loop

dbms_output.put_line('姓名:'||v_account.real_name||' 身份证:'||v_account.idcard_no);

fetch c_account into v_account;

end loop;

end if;

close c_account;

end;

  • 注意事项:用while循环和loop循环时,注意fetchput_line()的顺序;否则while会少打记录,loop会多打记录。

eg5:打印每个客户的名字和身份证号码(使用for循环处理cursor

方式一:for的最简单写法

begin

for i in(select real_name,idcard_no from account)

loop

dbms_output.put_line('姓名:'||i.real_name||' 身份证:'||i.idcard_no);

--dbms_output.put_line(c_account%rowcount);无法打印,因为拿不到cursor的名字

end loop;

end;

  • 注意事项:for循环把cursor的操作集成了(openfetchclose),自动打开关闭,自动把值fetchi,所以i的类型是记录类型。

方式二:定义一个cursor

declare

cursor c_account is select real_name,idcard_no from account;

begin

for i in c_account

loop

dbms_output.put_line('姓名:'||i.real_name||' 身份证:'||i.idcard_no);

dbms_output.put_line(c_account%rowcount);

end loop;

end;

eg6:打印每个客户的名字和身份证号码,若查询没有记录返回,打印客户不存在(使用for循环处理cursor

方式一:

declare

cursor c_account is select real_name,idcard_no from account where 1=1;

v_account c_account%rowtype;

begin

open c_account;

fetch c_account into v_account;

if c_account%notfound then

dbms_output.put_line('no account');

else

close c_account;

for i in c_account

loop

dbms_output.put_line('姓名:'||i.real_name||' 身份证:'||i.idcard_no);

end loop;

end if;

close c_account;

end;

方式二:

declare

cursor c_account is select real_name,idcard_no from account where 1=1;

v_cnt binary_integer := 0;

begin

for i in c_account loop

dbms_output.out_line(rpad(i.real_name,12,' ')||' '||i.idcard_no);

v_cnt := 1;

end loop;

if v_cnt = 0 then

dbms_output.put_line('account not exists!');

end if;

end;

6、集合

6.1什么是collection

collection是按某种顺序排列的一组元素,所有的元素有相同的数据类型,每个元素有唯一一个下标标识其在这一组元素中的位置。

分类:

1Associative arrays(关联数组)又称index-by table,使用键值访问。

2Nested table嵌套表(本课程不讲解)。

3Varray数据,变长数组,定义时需要指定数组大小(本课程不讲解)。

6.2什么是关联数组

1)关联数组就是键值对的集合,其中键是唯一的,用于确定数组中对应的值。

2)键可以是整数或字符串。第一次使用键来指派一个对应的值就是添加元素,而后续这样的操作就是更新元素。

3)关联数组能帮我们存放任意大小的数据集合,快速查找数组中的元素。它像一个简单的SQL表,可以按主键来检索数据。

6.3 Associative arrays的定义

1)同种类型的一维、无边界的稀疏集合,只能用于PL/SQL

2type_name是关联数组的类型名,element_type是关联数组中存储的元素类型。

3index by后面的数据类型是下标的数据类型。

  • 注意事项:

  • 关联数组和下标不要求连续,不要求从0、从1开始。

  • 集合对下标是自动排序的,按元素排序要自己写算法。

  • 两个下标一样,则是覆盖操作!

eg1:语法

type type_name is table of element_type [not null]

index by [binary_integer|pls_integer|varchar2(size_limit)];

eg2:集合下标自动排序

dbms_output.put_line('index:'||v_account.first);--输出排序后的第一个下标

dbms_output.put_line('index:'||v_account.last); --输出排序后的最后一个下标

6.4声明Associative arrays类型和变量

eg:声明类型

type associative_array_type is table of number index by binary_integer;

eg:声明变量

V1 associative_array_type;

V2 v1%type;

  • 注意事项:

v_account account%rowtype;

type is table of v_account index by binary_integer;

-报错v_account是变量,不是数据类型,改为account%rowtype才正确

6.5关联数组的操作

eg:语法

declare

type t_indtab is table of number

index by binary_integer;

v_indtab t_indtab;

begin

v_indtab(1) :=1;

v_indtab(5) :=5;

v_indtab(6) :=6;

v_indtab(10) :=10;

dbms_output.put_line(v_indtab(10));

dbms_output.put_line(v_indtab(7));

end;

6.6 Associative arrays的方法

1exists方法:

①exists(1),判断第1个元素是否存在。

在使用count方法前,建议先用exists来判断一下,这样可以避免抛出异常。

eg:语法

if courses.exists(i) then

courses(i) := new_course;

end if;

2count方法:

返回联合数组的元素个数,不包括被删除的元素。

对于空的联合数组,返回值也是0

eg:语法

if projects.count = 25 then ……

3firstlast:返回最小和最大下标号,如果collection为空,则返回null

4prior(n)next(n):返回第n个元素的前一个和后一个,如果不存在,则返回null

5trim(n):从最后一个元素删除n个元素。

6delete方法:

①delete:删除所有元素

②delete(n):删除第n个元素

③delete(m,n):从第m个元素删除到第n个元素

eg:相关操作

declare

type t_account_idxtab is table of varchar2(20)--元素的数据类型

index by binary_integer;--下标的数据类型

cursor c_account is select id,real_name from account;--id作下标

v_account t_account_idxtab;

begin

for i in c_account loop

v_account(i.id) :=i.real_name;

dbms_output.put_line('index:'||i.id);

dbms_output.put_line('element:'||v_account(i.id));

end loop;

dbms_output.put_line('element:'||v_account(1005));

dbms_output.put_line('index:'||v_account.first);

dbms_output.put_line('index:'||v_account.last);

end;

6.7关联数组的遍历

eg1:语法

declare ……

begin ……

for i in v_indtab.first .. v_indtab.last loop

dbms_output.put_line(v_indtab(i));

end loop;

end;

eg2:关联数组的遍历(用while循环)

declare

type t_account_idxtab is table of varchar2(20)--元素的数据类型

index by binary_integer;--下标的数据类型

cursor c_account is select id,real_name from account;--id作下标

v_account t_account_idxtab;

v_index binary_integer;

begin

for i in c_account loop

v_account(i.id) :=i.real_name;

end loop;

v_index :=v_account.first;

while v_index <= v_account.last loop

dbms_output.put_line(v_account(v_index));

v_index := v_account.next(v_index);--1005的下一个下标

end loop;

end;

eg3:关联数组的遍历(用for循环)

declare

type t_account_idxtab is table of varchar2(20)--元素的数据类型

index by binary_integer;--下标的数据类型

cursor c_account is select id,real_name from account;--id作下标

v_account t_account_idxtab;

begin

for i in c_account loop

v_account(i.id) :=i.real_name;

end loop;

for i in v_account.first .. v_account.last loop

if v_account.exists(i) then

dbms_output.put_line(v_account(i));

end if;

end loop;

end;

  • 注意事项:

  • 对于不连续的下标,用for循环会空转,效率低。

  • 下标是连续的用for循环好。

  • 下标不连续的用while循环好(匹配集合中的next()方法)。

6.8 Associative arrays的异常

现象:

declare

type numlist is table of number index by binary_integer;

nums numlist;--自动初始化为null

begin

nums(null) :=3;--触发value_error,没下标,下标不能为空

if nums(1) =1 then --触发no_data_found,下标有,但没元素

6.9批量绑定

1)通过bulk collect减少loop处理的开销。

2)采用bulk collect可以将查询结果一次性的加载到collections中。

3)可以在select intofetch intoreturning into语句中使用bulk collect

  • 注意事项:选择操作的几种方式:selectselect intocursor fetch intoselect bulk collect intofetch bulk collect intofetch bulk collect into limit 10

4)注意在使用bulk collect时,所有的into变量都必须是collections

  • 注意事项:

  • 当返回多条记录时可用bulk collectcursor,但cursor是一条一条处理,bulk collect是批量处理。

  • 集成度越高,可控的内容越少,如下标不可控了。

  • 使用bulk collect时,下标自动生成从1开始。

  • 使用问题:如果内存空间不够,则将无法执行下去,所以要限制limit一次取出的数量。

eg1fetch bulk collect into

declare

cursor is v_account 集合变量

begin

open c_account;

fetch bulk collect into v_account limit 3;

close c_account;

 

eg2:打印每个客户的名字、年龄、以及累计年龄

 

方式一:

declare

type t_account_rec is record (real_name varchar2(20),age binary_integer);

type t_account_idxtab is table of t_account_rec index by binary_integer;

v_account t_account_idxtab;

v_sum binary_integer :=0;

begin

select real_name,round((sysdate-birthdate)/365) bulk collect into v_account from account where 1=1;

dbms_output.put_line('RealName'||' '||'Age'||' '||'SumAge');

if v_account.count<>0 then

for i in v_account.first .. v_account.last loop

v_sum :=v_sum + v_account(i).age;

dbms_output.put_line(rpad(v_account(i).real_name,12,' ')||' '||rpad(v_account(i).age,4,' ')||' '||v_sum);

end loop;

else

dbms_output.put_line('no account');

end if;

end;

方式二:

declare

cursor c_account is select real_name,round((sysdate-birthdate)/365) age--想引用必须起别名

from account;

type t_account_indtab is table of c_account%rowtype index by binary_integer;

v_account t_account_indtab;

v_culage binary_integer :=0;

begin

select real_name,round((sysdate-birthdate)/365)age bulk collect into v_account

from account

where 1=1;--bulk collect也可处理多条记录,下标自动生成从1开始

if v_account.first is not null then

for i in v_account.first .. v_account.last loop

v_culage :=v_culage + v_account(i).age;

dbms_output.put_line(rpad(v_account(i).real_name,12,' ')||' '||rpad(v_account(i).age,4,' ')||' '||v_culage);

end loop;

else

dbms_output.put_line('no account');

end if;

end;

7、异常

7.1 Oralce错误

1PL/SQL错误:编译时错误、运行时错误。

2)运行时的错误:Oracle错误(ORA-XXXXX)、PL/SQL运行错误、用户定义的错误。

7.2 Oralce错误处理机制

1)在程序运行期间的错误对应一个异常(exception)。

2)一个错误对应一个异常,当错误产生时抛出相应的异常,并被异常处理器捕获,程序控制权传递给异常处理器,由异常处理器来处理运行时错误。

7.3异常的类型

1)隐式触发:Oracle预定义异常、非Oracle预定义异常。

2)显式触发:用户自定义异常。

7.4 PL/SQL中的异常

如下图所示:

7.5异常捕获

1)异常的捕获通过异常处理器实现。它是程序的一个独立部分,把错误与程序的其他部分分离开来,使程序逻辑更加易于理解。

2)当异常产生时,控制权立即转移到异常处理器。一旦执行控制权被转移到异常处理器,就无法再回到本语句块的可执行部分。如没有异常部分,则被传播到外层语句块(类似于java中的异常一直向上抛)。

3)异常处理器语法:

exception

when exception_name1 then

sequence_of_statements1;

when exception_name2 then

sequence_of_statements2;

[when others then

sequence_of_statements3;]

end;

7.6异常的捕获规则

1exception关键字,标识异常处理的开始区域。

2)一个异常处理器可以捕获多个异常,只需要在when子句中用or连接即可。

3)允许有多个异常处理器。

4)一个异常只能被一个异常处理器捕获,并进行处理。

5others异常处理器总是作为异常处理部分的最后一个异常处理器,负责处理那些没有被其他异常处理器捕获的异常。

7.7 Oralce预定义错误的捕获

1no_date_found(ORA-1403):如没有找到数据。

2too_many_rows(ORA-1422):如返回的行数超出请求行数。

3invalid_cursor(ORA-1001):如cursor没有open就使用。

4zero_divide(ORA-1476):如0做了除数。

5dup_val_on_index(ORA-0001):如多个重复索引(违反了唯一键约束)。

6value_error(ORA-6502):如集合(数组)中下标为null(或着说元素为空)。

7)不需要定义,不需要触发,只要捕获到即可。

8Oralce预定义错误的捕获

eg1:语法

begin

select …… commit;

exception

when no_date_found then

statement1;

when too_many_rows then

statement2;

when others then

statement3;

end;

eg2:相关操作

declare

v_realname varchar2(20);

begin

insert into test_chang values(1);

insert into test_chang values(1);

commit;--保证没有把锁带进异常

select real_name into v_realname from account where 1=1;

dbms_output.put_line('a');--异常时,此句不执行

exception--执行一个异常后

when no_data_found then--若不接异常,则报错ORA1403no date found

dbms_output.put_line('no data found');

when too_many_rows then--若不接异常,则报错ORA1422exact fetch returns

more than requested number of rows

dbms_output.put_line('too many rows');

when dup_val_on_index then

rollback;--要做都做,要不做都不做,回滚保证没有锁

end;

7.8Oracle预定义异常

将一个经过命名的异常和一个Oracle错误相关联。

1)在语句块的声明部分声明一个异常名称:e_integrity exception;

2)通过pragma exceptoin_init将异常与一个Oracle错误号相关联:

pragma exception_init(e_integrity,-2291)

3)在异常处理部分捕获并处理异常:when e_integrity then ……

eg:非预定义异常

declare

e_noparent exception;

pragma exception_init(e_noparent,-2291);

e_childexist exception;

pragma exception_init(e_childexist,-2292);

begin

delete from parent_chang where c1=1;

insert into child_chang values(1,2);

exception

when e_noparent then

dbms_output.put_line('no parent');

when e_childexist then

dbms_output.put_line('e_childexist');

end;

7.9用户自定义异常

1)用户自定义异常必须在声明部分进行声明。

2)当异常发生时,系统不能自动触发,需要用户使用raise语句(类似java中的throw)。

3)在异常处理部分捕获并处理异常。

eg1:自定义大于100为异常

declare

v_n1 number :=101;

e_more100 exception;

begin

if v_n1 > 100 then

raise e_more100;

end if;

exception

when e_more100 then

dbms_output.put_line('>100');

end;

eg2others接受所有未捕获的异常,sqlcodesqlerrm

declare

v_n1 number :=101;

e_more100 exception;

begin

insert into test values('abc');--异常

if v_n1 > 100 then

raise e_more100;

end if;

exception

when e_more100 then

dbms_output.put_line('>100');

when others then

dbms_output.put_line('a'||sqlcode);--获取错误的编号

dbms_output.put_line('a'||sqlerrm);--获取错误的信息

end;

7.10异常处理总结

1)在声明部分为错误定义异常时,包括非Oracle预定义异常和用户定义异常

①e_exception exception;

②pragma exception_init(e_exception,-####);

2)在执行过程中当错误产生时抛出与错误对应的异常:raise excepname

3)在异常处理部分通过异常处理器捕获异常,并进行异常处理。

7.11 sqlcodesqlerrm

通过以下两个函数获取错误相关信息。

1sqlcode:返回当前错误代码

如果是用户定义错误返回值为1

如果是ORA-1403no data found错误,返回值为100

其他Oracle内部错误返回相应的错误号。

eg:语法

exception--执行一个异常后

when no_data_found then--若不接异常,则报错ORA1403no date found

dbms_output.put_line('no data found');

dbms_output.put_line('a'||sqlcode);--100

when too_many_rows then--若不接异常,则报错ORA1422exact fetch returns more than requested number of rows

dbms_output.put_line('too many rows');

dbms_output.put_line('a'||sqlcode);--1422

end;

2sqlerrm:返回当前错误的消息文本

如果是Oracle内部错误,返回系统内部的错误描述

如果是用户定义的错误,则返回信息文本为“user-defined exception

 

eg:语法

declare

v_n1 number :=101;

e_more100 exception;

begin

insert into test values('abc');--异常

if v_n1 > 100 then

raise e_more100;

end if;

exception

when e_more100 then

dbms_output.put_line('>100');

when others then

d bms_output.put_line('a'||sqlcode);--a-1722

dbms_output.put_line('a'||sqlerrm);--aORA-01722: 无效数字

end;

  • 注意事项:insert into log_tab values(sqlcode,sqlerrm,sysdate);sqlerrm不能直接插入表中,要赋给一个变量!v_errm :=sqlerrm;

insert into log_tab values(sqlcode,v_errm,sysdate);--才能执行

7.12异常的传播

1)可执行部分异常的传播:

如果当前语句块有该异常的处理器,则执行之,并且成功完成该语句块。然后,控制权传递到外层语句块。

如果当前语句块没有该异常的处理器,则通过在外层语句块中产生该异常来传播该异常。然后,对外层语句块执行步骤1。如果没有外层语句块,则该异常将传播到调用环境。

2)声明部分异常的传播:

声明部分的异常立刻传播到外层语句块,即使当前语句块有异常处理器。

3)异常处理部分的异常的传播:

异常处理器中产生的异常,可以有raise语句显式产生,也可以通过运行时错误而隐含产生。异常立即被传播到外层语句块。

8、子程序

8.1子程序

1)匿名子程序,即匿名块declare begin exception end;

匿名块不存在数据库中②每次使用时都会进行编译③不能在其他块中相互调用

2)有名子程序

8.2有名子程序

1)命名的PL/SQL块,编译并存储在数据库中,可以在任何需要的地方调用。

2)子程序的组成部分:子程序头、声明部分、可执行部分、异常处理部分(可选)。

  • 注意事项:有名子程序编译和运行是分开的。

8.3有名子程序的分类

procedure:过程,put_line() function:函数

package:包,dbms_output trigger:触发器

8.4有名子程序的优点

1)模块化:将程序分解为逻辑模块。 2)可重用性:可以被任意数目的程序调用。

3)可维护性:简化维护操作。 4)安全性:通过设置权限,是数据更安全。

9、过程procedure

9.1语法

create [or replace] procedure proc_name [(arg_name[{in|out|in out}]) type, ]

isas

<local variable declaration>--存储过程要用到的局部变量

begin

<executable statements>

exception

<exception handlers>

end;

9.2创建存储过程

就是编译存储过程的代码,目的是为了调用它。

eg:语法

create or replace procedure p_accountno_chang--只要是create出来的都是数据库对象,类型是procedure

is

v_cnt number;--存储过程要用到的局部变量

begin

select count(id) into v_cnt from account;

dbms_output.put_line(v_cnt);

end;

  • 注意事项:

  • select status from user_objects where object_name='P_ACCOUNTNO_CHANG';--查看状态,名字必须大写,存的时候是按大写存的

  • select type from user_source where name='P_ACCOUNTNO_CHANG';--查看是哪种有名子程序

  • --create or replace view(procedure)编译不成动,但数据库对象已创建,只不过状态是invalid

9.3形参和实参

1)在创建过程语句中的参数为形参

create or replace procedure procchang(p_n1 number)--p_n1是形参

is

v_n1 number;--v_n1是过程中的局部变量

begin

v_n1:=p_n1;

end;

2)在调用过程时括号内的参数为实参

declare

v_n1 number :=1;

begin

procchang(v_n1);--v_n1是实参

procchang(2);--2是实参

end;

  • 注意事项:

  • 形参相当于实参的“占位符”。

  • 形参的名字和列名相同,系统则默认按列名。

9.4形参的种类

1in:参数的缺省模式!在调用过程的时候,实际参数的值被传递给该过程;在过程内部,形参是只可读的。

2out:在调用过程时,任何的实参将被忽略;在过程内部,形参是只可写的。

3in out:是inout的组合。在调用过程的时候,实参的值可以被传递给该过程;在过程内部,形参可以被读出也可以被写入;过程结束时,控制会返回给控制环境,而形式参数的内容将赋给调用时的实际参数。

  • 注意事项:

  • in 相当于java中八大基本类型+string的类似效果。

  • in out 相当于java中的引用类型的类似效果。

  • out 无对应。

9.5调用存储过程

1)用匿名块调用

begin

procedure_name();

end;

2)在SQL动作表中直接调用

exec procedure_name();

9.6存储过程中的参数

eg1:语法

create or replace procedure procchang(

p_c1 in varchar2,--只有此时varchar才不用写长度,长度有传入的实参决定

p_c2 out varchar2,p_c3 in out varchar2)

is

v_c1 varchar2(10);--存储过程要用到的局部变量

begin

v_c1 :=p_c1;

--p_c1 :=p_c1||'d';--报错,不能作赋值目标。in在调用过程中,形参只可“读取”实参的值,即不能被写(不能在赋值语句左边)。

p_c2 :=p_c2||'d';--out在调用过程中,任何实参都被忽略,形参只可“写入”实参中(在过程内部可读,但无值,即p_c2 :=p_c2||'d';不报错)。

p_c3 :=p_c3||'d';--只有变量才能作赋值目标

end;

 

 

eg2:用匿名块调用过程

declare

v_c2 varchar2(20) :='abc';--对于传出参数没必要定义初值,即也可v_c2 varchar2(20);

v_c3 varchar2(20) :='abc';

begin

procchang('abc',v_c2,v_c3);--过程的调用可直接单独写出,但函数不行,不要有变量接收返回值

dbms_output.put_line(v_c2);--存储过程依靠out返回,d

dbms_output.put_line(v_c3);--abcd

end;

9.7对实际参数的要求

1)模式为in的形参对应的实际参数可以是“常量或变量”。

2)模式为in outout的形参对应的实际参数必须是“变量”,用于存储返回的值,所以不能是常量或表达式。

9.8形式参数的限制

1)在调用过程当中,实际参数在将值传递给过程时,也传递了对变量的限制。

2)形式参数不能声明长度,但可使用%type来进行限制。

9.9带参数的过程调用

1)位置表示法:调用时添入所有参数,实参与形参按顺序一一对应。

2)名字表示法:调用时给出形参名字,并给出实参:

procname(12,p_outparm => v_var1,p_inout => 10);

前为形参,后为实参;理解为形参对应的实参

3)两种方法可以混用:混用时,第一个参数必须通过位置来指定,名字表示对于参数很多时,可提高程序的可读性。

9.10使用缺省参数

1)形参可以指明缺省值:parm_name [mode] type {:= | default} init_value

2)位置标示法时,所有的缺省值都放在最后面。

3)使用名字标示法则无所谓。

4)声明时,如果有缺省值,尽量将缺省值放在参数表的末尾。

eg:使用缺省参数default

create or replace procedure pro_chang1(p_c1 number,p_c2 number,p_c3 number,

p_c4 number default 1,p_c5 number default 1)

is

begin

dbms_output.put_line(p_c1); dbms_output.put_line(p_c2);

dbms_output.put_line(p_c3); dbms_output.put_line(p_c4);

dbms_output.put_line(p_c5);

end;

exec pro_chang1(1,2,3);--1,2,3,1,1 把缺省值放最后

exec pro_chang1(1,2,3,4,5);--1,2,3,4,5

9.11存储过程中的DDL语句

eg:语法

create or replace procedure proc1

is

begin

execute immediate create table test(c1 number);

end;

exec proc1

ORA-01031:权限不足

  • 注意事项:为何会出现创建table权限不足问题?明明创建table已经好几天了?!

问题分析:

1DBA创建用户:

create user jsd1304;--创建的用户初始什么都做不了,只能慢慢授权各种权限

create role connect--角色connect,是缺省创建的

create role resource--另一种角色resource

2)把角色授权给用户:

grant connect,resource to jsd1304;--授权两种角色给用户1304

user role:enabledisable--用户角色分为:可用和不可用

3)举例说明:

create role role1--创建角色role1

grant create table to role1;--把创建table的权限授予角色role1

grant create index to role1;--把创建index的权限授予角色role1

grant create view to role1;--把创建view的权限授予角色role1

grant role1 to jsd1304;--把角色role1所拥有的权限授予用户jsd1304

4)用户能创建table的原因:用户被授予了一个角色,而角色是可用的

connect role enable priviledge 可用

5)而在处理有名块时,把用户的所有角色disablepriviledge不可用了,所以会出现权限不足问题

6)解决方法:grant create table to jsd1304;--直接授予的权限不会被disable

7)因此,可总结如下:

在过程中执行DDL操作,所需的权限必须通过直接授予的方式,不能通过角色授予。

调用过程时,所有角色都是disable的(系统做的),即角色中包含的所有权限都不能生效。

调用其他用户的过程,必须由过程的属主授予执行权限:grant execute on procname to username;

权限-->角色-->用户<--权限

9.12变量

1)局部变量:在匿名块或存储过程中定义的变量为局部变量,即作用域在整个匿名块或存储过程中。程序运行结束,该变量也就不存在了。

2)绑定变量(宿主变量):在PL/SQLSQL中可以直接使用绑定变量:bv_name,即也可不需要定义。标志是以“冒号”开头。

eg1:绑定变量的定义

--variable i number--绑定变量的定义,也可不声明;且不能定义宽度;且最后一定要有赋值

begin

for i in 1..10 loop

execute immediate 'insert into test_chang values (:i)' using i;

--系统使用using自动给绑定变量赋值,用绑定变量是占位置的,若不声明则系统认为存在!

end loop;

dbms_output.put_line(:i);

commit;

end;

eg2:绑定变量的赋值

variable i number

begin

:i :=1;

end;

print i;--1,print是环境里的命令,不是PL/SQL命令

exec :i :=10000--绑定变量的赋值

print i;

3)各类变量总结

①variable i:绑定变量,不退出环境,变量始终存在,跨session的,生命周期最长。

②declare .. begin之间和is .. begin之间定义的变量:叫局部变量,匿名块退出,变量不存在。

③for ii变量不需要声明,for循环结束,变量不存在。

调用一个函数、过程时,需要提供实参,绑定变量和局部变量可作实参。作用域匿名块里。

定义一个函数、过程时,用形参,形参作用域在函数、过程里。

在包package声明中的变量v1,在包中的过程、函数里都能直接调用,不用写包名;如果出了包:匿名块、过程、函数、其他包里可以用包名调,pkg.v1。也称为session里的全局变量。

9.13 PL/SQL中的SQL分类

1)静态SQL:在PL/SQL块中使用的SQL语句在编译时是明确的,执行的是确定对象,即SQL语句是在PL/SQL编译阶段编译的。

  • 注意事项:

  • SQL书写是有规范的,目的是减少硬分析(硬分析对cpu、内存的耗费代价最高)。

  • SQL中使用绑定变量(非常重要)!目的是减少硬分析。

  • SQL出现在各种开发工具中,两种形式:静态SQL,动态SQL。比如:jdbc只能提供动态SQL方式,PL/SQL可以使用静态方式(运行时sql已经编译过了,oracle对其做额外的优化,如cursor cache,减少软分析的次数)

  • 一个session中可以同时执行sql的数量是有限的(每打开一个cursor就会占内存,内存限制)。

  • 一个session可以打开的cursor是有限的(参数限制open_cursor)。

2)动态SQL:在PL/SQL块编译时SQL语句是不确定的,如根据用户输入的参数的不同而执行不同的操作。编译程序对动态语句部分不进处理,只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。

  • 注意事项:在编译期execute immediate相当于SQL语句,后面的就是单纯的字符串,不看内容。只有在执行时才看、报错。

3PL/SQL中的静态SQL

①Oracle在解析SQL时会吧PL/SQL中定义的变量转化为绑定变量,insert into test_chang values(i);减少了硬分析次数。

②server process将执行完的SQL cache起来,不关闭!当再次执行SQL时,不需要软分析。

静态高效的原因是系统自动使用了绑定变量,同时动态SQL也可用绑定变量方式。

静态SQL1次软分析是由于cursor用完没有被关闭,而是下次继续使用。

过程中的参数会自动转化为绑定变量。

eg1:静态SQL

create or replace procedure proc1

is

begin

for i in 1..1000 loop

insert into test_chang values(i);

end loop;

commit;

end;--编译存储过程时编译SQL语句

begin proc1 end--1次硬分析,1次软分析,1000次执行,静态SQL1次软分析是由于cursor用完没有被关闭,而是下次继续使用

eg2:动态SQL(未使用绑定变量)

create or replace procedure proc1

is

begin

for i in 1..1000 loop

execute immediate insert into test_chang values(||i||);

end loop;

commit;

end;--编译存储过程时不编译SQL语句

begin proc1 end--1000次硬分析,1000次软分析,1000次执行

eg3:动态SQL(使用绑定变量)

create or replace procedure proc1

is

begin

for i in 1..1000 loop

execute immediate insert into test_chang values(:i) using i;

end loop; commit;

end;--编译存储过程时不编译SQL语句

begin proc1 end--1次硬分析,1000次软分析,1000次执行

9.14 再一次来看SQL语句的处理过程

1)语法检查(syntax check):检查此SQL的拼写是否符合语法。

2)语义检查(semantic check):诸如检查SQL语句中的访问对象是否存在及该用户是否具备相应的权限。

3)对SQL语句进行解析(pares):利用内部算法对SQL进行解析,生成解析树(pares tree)及执行计划(execute plan)。

4)执行SQL,返回结果(execute and return)。

9.15软分析和硬分析

Oracle利用内部的hash算法来取得该SQLhash值,然后在library cache里查找是否存在该hash值。

1)假设存在,则将此SQLcache中的进行比较;假设“相同”,就将利用已有的解析树与执行计划,而省略了优化器的相关工作。这也就是软解释的过程。

2)如果上面两个假设中有任意一个不成立,那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫做硬解析。

  • 注意事项:hash算法对大小写是敏感的

9.16对过程procedure的基本操作

1)创建并编译过程:create or replace procedure

2)编译过程:alter procedure procname compile;

3)调用过程:用匿名子程序调用,直接写过程名;用有名子程序调用,直接写过程名。

4)删除过程:drop procedure;

9.17案例

eg1:提供客户ID,返回客户名称、年龄,若该客户不存在则返回no account

create or replace procedure pro_chang(p_id number,name out varchar2,age out number)

is

begin

select real_name,round((sysdate-birthdate)/365)into name,age from account

where id=p_id;

exception

when no_data_found then

name:='no account';

age:=0;

end;

 

declare

v_realname varchar2(20);

v_age number(3);

begin

pro_chang(105,v_realname,v_age);

dbms_output.put_line(v_realname||' '||v_age);

end;

eg2:使用绑定变量

variable b_realname varchar2;

variable b_age number;

begin

pro_chang(1005,:b_realname,:b_age);

end;

 

print b_realname;

print b_age;

  • 注意事项:也可使用exec pro_chang(1005,:b_realname,:b_age);直接调用

10、函数function

10.1语法

create [or replace] function func_name[(arg_name [{in|out|in out}]) type,)]

return type

{is|as}

<local variable declaration>--函数中要用到的局部变量

begin

<executable statements>

return value;

exception

<exception handlers>

end;

10.2创建函数

create or replace function fun1(p_in number,p_out out number)return number

is

begin

p_out :=2;

return p_in;

end;

10.3调用函数

方式一:

declare

v_out number(3);

being

dbms_output.put_line(fun1(10,v_out)||' '||v_out);

end;

方式二:

declare

v_out number(3);

v_fun1 number;--java中函数可以单独使用,但PL/SQL中不可以,有传出要定义变量接收。

being

v_fun1 :=fun1(10,v_out);--函数不能单独放,函数的调用方式为表达式右边

dbms_output.put_line(v_fun1||' '||v_out);

end;

方式三:

variable b_out number

exec dbms_output.put_line(fun1(10,:b_out));

print b_out

10.4对函数function的基本操作

1)创建并编译函数:create or replace function

2)编译函数:alter function funcname compile;

3)调用函数:用匿名子程序调用,允许写值的位置就可以调用函数;用有名子程序调用,允许写值的位置就可以调用函数;在DML语句和select语句中调用。

4)删除函数:drop function;

10.5过程和函数的比较

过程

函数

作为PL/SQL语句执行

作为表达式的一部分调用

在过程头中不包含return

必须在函数头中包含return子句

不返回任何值

必须返回值

可以包含return语句,但不能

有返回值,表示过程终止运行

必须包含至少一条return语句,多条时,

只有一条被执行

  • 注意事项:过程的那一套东西也都适合函数。

10.6匿名块中的过程和函数声明

declare

v_n1 number :=1;

function fun1(p_in number) return number

is

begin

return p_in;

end;

procedure proc1

is

begin

dbms_output.put_line(fun1(v_n1));

end;

begin

proc1;

end;

  • 注意事项:过程和函数只能在本匿名块中调用

10.7案例

eg1:创建一个函数并调用

create or replace function fun1(p_c1 varchar2,p_c2 out varchar2)

return varchar2

is

begin

p_c2 :=p_c1||'d';

return p_c1;

end;

declare

v_c2 varchar2(20);

begin

dbms_output.put_line(fun1('abc',v_c2));--abc

dbms_output.put_line(v_c2);--abcd

end;

eg2:提供客户ID,返回客户名称、年龄,若该客户不存在则null

create or replace function getaccount(p_id number,p_age out number)return varchar2

is

T ype t_account_rec is record(real_name account.real_name%type,age number(3));

v_account t_account_rec;

begin

select real_name,round((sysdate-birthdate)/365) into v_account

from account

where id = p_id;

p_age := v_account.age ;

return v_account.real_name;

exception

when no_data_found then

v_account.real_name :='null';

return v_account.real_name;

end;

 

declare

v_age number(3);

begin

dbms_output.put_line(getaccount(10111,v_age)|| v_age);

end;

11、包package

11.1什么是package

1package(包)是一个可以将相关对象存储在一起的PL/SQL结构。package包含了两个分离的组成部分:specificationpackage的声明,即包声明)和body(声明中的程序实现,即包体)。每个部分都单独被存储在数据字典中。包声明是一个操作接口,对应用来说是可见的。

2)包体是黑盒,对应用来说隐藏了实现细节。

11.2包的组成

将相关的若干程序单元组织到一块,用一个包来标识这个集合,包中可以包含以下的程序单元:

程序单元

描述

过程(procedure

带有参数的程序

函数(function

带有参数的程序,该程序有返回值

变量(variable

用于存储变化值的存储单元

游标(cursor

定义一条SQL语句

类型(type

定义复合类型(recordcollection

常量(constant

定义常量

异常(exception

标识异常

11.3包的优点

1)方便对存储过程和函数的组织:

将相关的过程和函数组织在一起。②在一个用户环境中解决命名的冲突问题。

2)方便对存储过程和函数的管理:

在不改变包的声明定义是可以改变包体的实现的。②限制过程、函数的依赖性。

在包体未实现时,其他程序中可以调用包中的对象,对自己的程序进行编译,可以并行地对程序开发。

3)方便对存储过程和函数的安全性管理:

整个包的访问权限只需一次性授权。②区分公用过程和私有过程。

4)改善性能:

在包被首次调用时作为一个整体全部调入内存。②减少多次调用时磁盘I/O次数。

11.4 package声明的语法

create or replace package pkg_name

{is|as}

公共变量(variable)的定义|公共类型(type)的定义|

公共异常(Exception)的定义|公共游标(cursor)的定义|

函数说明|过程说明

end;

 

 

11.5 package body声明的语法

create or replace package body pkg_name

{is|as}

函数实现;--调用一次执行一次

过程实现;--调用一次执行一次

begin

--初始化代码;--首次调用包中的任意对象则执行一次(只执行1次)

end;

11.6编译包和包体

alter package pkg1 compile;

alter package pkg1 compile body;

11.7案例

eg1:创建一个包并调用

create or replace package pkg_chang--创建package

is

type t_rec is record(m1 number,m2 varchar2(10));

v_rec t_rec;

procedure proc1;

function fun1(p_in number)return number;

end;

--创建package body

create or replace package body pkg_chang

is

procedure proc1--存储在数据库中的package包里

is

begin

dbms_output.put_line(v_rec.m1);

end;

function fun1(p_in number)return number

is

begin

return p_in;

end;

--begin

--v_rec.m1 :=100;

end;

--调用package

begin

pkg_chang.v_rec.m1 :=pkg_chang.fun1(10);

pkg_chang.proc1;

end;

eg2:创建一个包并调用

--创建package

create or replace package pkg_chang1

is

type t_rec is record(realname varchar2(30),age number);

v_account t_rec;

procedure p_account;

function f_account(p_id number)return t_rec;

end;

--创建package body

create or replace package body pkg_chang1

is

procedure p_account

is

begin

dbms_output.put_line('姓名:'||v_account.realname||' 年龄:'||v_account.age);

end;

 

function f_account(p_id number)return t_rec

is

--v_account t_rec;

begin

select real_name,round((sysdate-birthdate)/365) into v_account from account

where id=p_id;

return v_account;

end;

end;

--调用package

begin

pkg_chang1.v_account :=pkg_chang1.f_account(1005);

pkg_chang1.p_account;

end;

  • 注意事项:

  • to_charnvl等常用函数都在系统提供的standard package包中,只不过可以省略包名,其他包都要写包名;包内部可不写包名,出了包要写。

  • 包里的过程和函数也可以重载。

  • 包声明变了,包体一定要变。

  • 可以有包声明,而没有包体,但不能只有包体而没有包声明。

  • 删除包,则包体也被删除了。

  • package没有更新,只更新了package body,调用包的程序代码不需要修改,也不需要重新编译。

12、触发器trigger

12.1面临问题

1)在作DML操作时,不需要提供主键值,系统自动生成。

2)如何实现级联更新。

12.2 DML触发器的组成

组成部分

描述

可能值

触发时间

触发事件的时间次序

beforeafter

触发事件

DML语句是触发事件

insertupdatedelete

触发器类型

触发器体被执行的次数

statement(语句级)、row(行级)

触发器体

该触发器将要执行的动作

完整的PL/SQL

12.3 DML触发器的类型

1)语句级触发器。

2)行级触发器。

3)行级触发器与语句级触发器的区别:触发的次数不同,如果DML语句只影响一行,则语句级与行级触发器效果一样;如果影响多行,则行级触发器触发的次数比语句级触发器触发的次数多。

12.4 DML触发器的触发顺序

根据触发的时间、类型不同,可以组合为四种DML触发器。

触发时间

级别

描述

before

statement

SQL语句执行之前执行一次

before

row

SQL语句影响的每条记录被updatedeleteinsert之前执行一次

after

row

SQL语句影响的每条记录被updatedeleteinsert之后执行一次

after

statement

SQL语句执行之后执行一次

  • 注意事项:如在update 表时分为5个执行区间:做update语句之前;修改每一条符合条件的记录之前;修改记录;修改每一条符合条件的记录之后;做完update语句之后;

12.5 DML行级触发器

for each row子句创建一个行级触发器,使其在受到触发事件影响的每一行上都被触发。

eg:语法

create [or replace] trigger trig_name {before|after} insert [or update]

on tab_name

for each row [when restricting_condition]

PL/SQL block;

12.6 :OLD:NEW

在行级触发器中,在列名前加上:old标识符表示该列变化前的值,加上:new标识符表示变化后的值(绑定变量)。

 

触发事件

old.列名

new.列名

insert

所有的字段是null

insert语句中要插入的值

update

update之前该列的原始值

update语句中要更新的新值

delete

delete行之前的该列的原始值

所有字段都是null

eg1:自动产生主键值

--创建表

create table test_chang(c1 number primary key,c2 number);

--产生序列号

create sequence s1_chang;

--创建触发器,以序列号作为c1列的值

create or replace trigger gen_pk_chang

before insert on test_chang

for each row

begin

select s1_chang.nextval into :new.c1

from dual;

end;

--只插入c2列的值,发现不报错了。原因:系统自动将序列号插入c1

insert into test_chang(c2) values(1);

--c1列的值也插入,发现数字10被序列号替换了,before insert for each row覆盖了:new的值

insert into test_chang values(10,5);

若不想被替换,则匿名块修改为:

if :new.c1 is null then

select s1_chang.nextval into :new.c1

from dual;

end if;

  • 注意事项::new和:old是记录类型。

  • new和:old只能用于行级触发器,不能用于语句级触发器。

  • new tabname%rowtype,绑定变量new和定义触发器的table的行记录结构相同。

  • old tabname%rowtype,绑定变量old和定义触发器的table的行记录结构相同。

  • after行级触发器不可以修改:new的值,但可以查看:new的值。

  • insert into test_chang(c2) values(1); :new.c1=null :new.c2=1 --若没有触发器,则报错。原因:将null插入了主键列。

eg2:实现级联更新

--创建父子表

create table parent_chang(c1 number primary key,c2 number);

create table child_chang(c1 number primary key,c2 constraint child_c2_fk references parent_chang(c1));

--向父子表中插入数据

insert into parent_chang values(11,1);

insert into parent_chang values(12,2);

insert into child_chang values(21,11);

insert into child_chang values(22,12);

--若无触发器,更新c1列的值则报错。

update parent_chang set c1=1 where c1=11;

--若定义触发器

create or replace trigger cascade_update_chang

after update on parent_chang

for each row

begin

update child_chang set c2=:new.c1

where c2=:old.c1;

end;

--再次执行更新语句,则不报错,两表数据同时被更新

update parent_chang set c1=1 where c1=11;

  • 注意事项:

  • 在触发器里,缺省状态下,不能写提交和回滚的!

  • 另一状态:Oracle自治事务,可写。

  • 约束检查发生在触发器完成之后。

12.7触发器的重新编译

1)如果触发器内调用其他函数或过程,当他们被删除或修改后,触发器的状态被表示为无效。当DML语句激活一个无效触发器时,Oracle将重新编译触发器代码,如果编译时发现错误,这将导致DML语句执行失败。

2)调用alter trigger语句重新编译已创建的触发器:

alter trigger [schema.] trig_name compile;

  • 注意事项:查看触发器列表

desc user_triggers; select * from user_triggers;

12.8触发器的状态

1)有效状态(enable):当触发事件发生时,处于有效状态的trigger将被触发。

2)无效状态(disable):当触发事件发生时,处于无效状态的trigger将不会被触发。

3trigger的两种状态可以相互转换,格式为:

alter trigger trig_name [disable|enable];

4alter trigger语句一次只能改变一个触发器的状态,而alter table语句则一次能改变与指定表相关的所有触发器的使用状态。

alter table [schema.] tab_name {enable|disable} all triggers;

  • 注意事项:drop table后:

  • 表中的all constraints drop、表中的all index drop、表中的all trigger drop

  • 无效的有:viewsynonymprocedurefunctionpackage

13、其他注意事项

13.1 PL/SQL的特点

1)结构化模块编程。2)良好的可移植性。3)良好的可维护性。4)提升系统性能

5)不便于向异构数据库移植应用程序。

13.2PL/SQL的好处

client端可以不用写sql,人员要求低,安全,权限控制。

相对于C/S结构,代码存在数据库中,维护的成本低(JDBCjava程序相当于C/S结构)。

B/S结构:sql代码在as上(application server)。

能写静态sql,可以预先编译的,效率高。

JDBC只能写动态sql,程序在运行时,sql语句才编译。

13.3命名建议

b_绑定变量 v_普通变量 p_形参

13.4搞清楚如下内容

  1. 过程参数inoutin out 2)过程,角色,权限的关系。

3)静态sql、动态sql、绑定变量。

13.5保证所有对象的状态都是valid

alter procedure procname compile;

alter package pkg1 compile;

alter package pkg1 compile body;

13.6 declare中都可声明什么

变量、类型、cursorexecption、过程、函数

13.7数据库对象

tableviewindexsequencesynonymprocedurefunctionpackagepackage body

 

 

 

 

 

文章仅在黑客派和本网站刊登,其他为盗载。