blog,blog,blog
blog,blog,blogPLSQL学习笔记
PL/SQL学习笔记
笔记内容说明
PL/SQL;
目录
1、PL/SQL简介
1.1什么是PL/SQL
PL/SQL(Procedural Language/SQL)是Oracle在标准SQL的基础上增加了过程化处理,把DML和select语句组织在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数据类型
标量类型:数字型、字符型、日期型、布尔型;可以直接用的。
复合类型:record、associative array、nested table、varray;需要自己去定义的。
2.2标量类型
-
数字类型:①number ②number的子类型dec(38)、float(38)、real(18)…
③binary_integer(只能在PL/SQL中用),按10进制赋值,但存的时候会换成2进制存。优势是计算快。
2)字符类型:①varchar2、varchar(长度:1.~32767)②string(只能在PL/SQL中用,长度:1.~32767)③char(长度:1~32767)④long
3)日期类型:date
4)布尔类型:boolean
①用于存储逻辑值true,false,null(java中只有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、流程控制语句
条件语句:if、case;循环语句:loop、while、for
3.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 if在PL/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中的参数只能为字符类型,但日期和数值会隐式转换,布尔类型不行!
2)case语句
case when then
when then
else
end;
3.2循环语句
1)loop循环(无条件进入)
语法:
loop
statement1;
statement2;
exit when<condition>
end loop;
-
注意事项:
-
exit when<condition>子句是必须的,否则循环将无法停止。
-
when后面直接写条件,没有尖括号或圆括号。
eg1:循环输出数字1~10
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;
2)while循环
语法:
while<boolean expr>loop
statement1; statement2;
end loop;
①循环语句执行的顺序是先判断<boolean expr>的真假,如果为true则循环执行,否则退出循环。
②在while循环语句中仍然可以使用exit或exit when子句。
-
注意事项:while后面直接写条件,没有尖括号或圆括号。
eg1:循环输出数字1~10
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;
3)for循环
语法:
for 循环计数器in 下限.. 上限loop
statement1;
statement2;
end loop;
①循环计数器是一个变量,这个变量不需要声明(和java不一样)。它的作用域仅是在循环中。
②每循环一次,循环变量自动加1;使用关键字reverse,循环变量自动减1。
③可以使用exit或者exit when子句退出循环。
-
注意事项:
-
跟在in、in reverse后面的数字必须是从小到大的顺序,但不一定是整数,可以是能够转换成整数的变量或表达式。
-
不能用null作上限或下限下标!
eg1:循环输出数字1~10
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
eg2:使用in reverse循环输出数字1~10
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 DML(insert,update,delete)和TCL(commit,rollback)
1)它们可以直接在PL/SQL中使用标准的SQL语句。
2)语法:
begin
insert into host(id) values('10.0.0.11');
commit;
end;
4.3 DDL
1)不能原封不动的像DML和TCL那样直接写。
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程序中,先create再insert,必须用execute immediate(静态sql和动态sql)
eg4:用循环向test_chang表中插入10条记录。(从1到10)
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)当且仅当只返回“一条”记录:用select…into…语句实现(将结果放入到变量中)。
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;
3)select...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;
-
案例
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实现。
2)Oralce使用专有SQL工作区(private SQL workarea)来执行SQL语句,存储处理信息。这个工作区称为“cursor”。
3)Oracle所执行的每一个SQL语句都有唯一的cursor与之相对应。
4)程序员用PL/SQL的cursor定义所需执行的select语句。
5.6 cursor的分类
隐式:select…into语句、DML语句
显式:返回多条记录的select语句用显式cursor实现。
5.7显式cursor的处理
如下图所示:
5.8显式cursor的属性
属性 |
类型 |
描述 |
cursor_name%isopen |
布尔 |
如果cursor是open的,其值为true |
cursor_name%notfound |
布尔 |
如果前一个fetch语句没有返回一行记录,其值为true |
cursor_name%found |
布尔 |
如果前一个fetch语句返回记录,其值为true |
cursor_name%rowcount |
数值 |
到目前为止,cursor已提取的总行数 |
目的:获取有关显式cursor的状态信息。
-
注意事项:
-
布尔值(true、false、null),当不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语句并标识结果集。
2)select语句如果没有返回记录,不会出现异常。
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。
3)cursor一旦关闭,所有和该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_account%rowtype;
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循环时,注意fetch和put_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的操作集成了(open、fetch、close),自动打开关闭,自动把值fetch给i,所以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是按某种顺序排列的一组元素,所有的元素有相同的数据类型,每个元素有唯一一个下标标识其在这一组元素中的位置。
分类:
1)Associative arrays(关联数组)又称index-by table,使用键值访问。
2)Nested table嵌套表(本课程不讲解)。
3)Varray数据,变长数组,定义时需要指定数组大小(本课程不讲解)。
6.2什么是关联数组
1)关联数组就是键值对的集合,其中键是唯一的,用于确定数组中对应的值。
2)键可以是整数或字符串。第一次使用键来指派一个对应的值就是添加元素,而后续这样的操作就是更新元素。
3)关联数组能帮我们存放任意大小的数据集合,快速查找数组中的元素。它像一个简单的SQL表,可以按主键来检索数据。
6.3 Associative arrays的定义
1)同种类型的一维、无边界的稀疏集合,只能用于PL/SQL。
2)type_name是关联数组的类型名,element_type是关联数组中存储的元素类型。
3)index 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的方法
1)exists方法:
①exists(1),判断第1个元素是否存在。
②在使用count方法前,建议先用exists来判断一下,这样可以避免抛出异常。
eg:语法
if courses.exists(i) then
courses(i) := new_course;
end if;
2)count方法:
①返回联合数组的元素个数,不包括被删除的元素。
②对于空的联合数组,返回值也是0;
eg:语法
if projects.count = 25 then ……
3)first和last:返回最小和最大下标号,如果collection为空,则返回null。
4)prior(n)和next(n):返回第n个元素的前一个和后一个,如果不存在,则返回null。
5)trim(n):从最后一个元素删除n个元素。
6)delete方法:
①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 into、fetch into、returning into语句中使用bulk collect。
-
注意事项:选择操作的几种方式:select、select into、cursor fetch into、select bulk collect into、fetch bulk collect into、fetch bulk collect into limit 10
4)注意在使用bulk collect时,所有的into变量都必须是collections。
-
注意事项:
-
当返回多条记录时可用bulk collect和cursor,但cursor是一条一条处理,bulk collect是批量处理。
-
集成度越高,可控的内容越少,如下标不可控了。
-
使用bulk collect时,下标自动生成从1开始。
-
使用问题:如果内存空间不够,则将无法执行下去,所以要限制limit一次取出的数量。
eg1:fetch 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错误
1)PL/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异常的捕获规则
1)exception关键字,标识异常处理的开始区域。
2)一个异常处理器可以捕获多个异常,只需要在when子句中用or连接即可。
3)允许有多个异常处理器。
4)一个异常只能被一个异常处理器捕获,并进行处理。
5)others异常处理器总是作为异常处理部分的最后一个异常处理器,负责处理那些没有被其他异常处理器捕获的异常。
7.7 Oralce预定义错误的捕获
1)no_date_found(ORA-1403):如没有找到数据。
2)too_many_rows(ORA-1422):如返回的行数超出请求行数。
3)invalid_cursor(ORA-1001):如cursor没有open就使用。
4)zero_divide(ORA-1476):如0做了除数。
5)dup_val_on_index(ORA-0001):如多个重复索引(违反了唯一键约束)。
6)value_error(ORA-6502):如集合(数组)中下标为null(或着说元素为空)。
7)不需要定义,不需要触发,只要捕获到即可。
8)Oralce预定义错误的捕获
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--若不接异常,则报错ORA—1403:no date found
dbms_output.put_line('no data found');
when too_many_rows then--若不接异常,则报错ORA—1422:exact fetch returns
more than requested number of rows
dbms_output.put_line('too many rows');
when dup_val_on_index then
rollback;--要做都做,要不做都不做,回滚保证没有锁
end;
7.8非Oracle预定义异常
将一个经过命名的异常和一个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;
eg2:others接受所有未捕获的异常,sqlcode、sqlerrm
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 sqlcode和sqlerrm
通过以下两个函数获取错误相关信息。
1)sqlcode:返回当前错误代码
①如果是用户定义错误返回值为1;
②如果是ORA-1403:no data found错误,返回值为100
③其他Oracle内部错误返回相应的错误号。
eg:语法
exception--执行一个异常后
when no_data_found then--若不接异常,则报错ORA—1403:no date found
dbms_output.put_line('no data found');
dbms_output.put_line('a'||sqlcode);--100
when too_many_rows then--若不接异常,则报错ORA—1422:exact fetch returns more than requested number of rows
dbms_output.put_line('too many rows');
dbms_output.put_line('a'||sqlcode);--1422
end;
2)sqlerrm:返回当前错误的消息文本
①如果是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,… ]
{is|as}
<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形参的种类
1)in:参数的缺省模式!在调用过程的时候,实际参数的值被传递给该过程;在过程内部,形参是只可读的。
2)out:在调用过程时,任何的实参将被忽略;在过程内部,形参是只可写的。
3)in out:是in与out的组合。在调用过程的时候,实参的值可以被传递给该过程;在过程内部,形参可以被读出也可以被写入;过程结束时,控制会返回给控制环境,而形式参数的内容将赋给调用时的实际参数。
-
注意事项:
-
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 out或out的形参对应的实际参数必须是“变量”,用于存储返回的值,所以不能是常量或表达式。
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已经好几天了?!
问题分析:
1)DBA创建用户:
create user jsd1304;--创建的用户初始什么都做不了,只能慢慢授权各种权限
create role connect--角色connect,是缺省创建的
create role resource--另一种角色resource
2)把角色授权给用户:
grant connect,resource to jsd1304;--授权两种角色给用户1304
user role:enable、disable--用户角色分为:可用和不可用
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)而在处理有名块时,把用户的所有角色disable,priviledge不可用了,所以会出现权限不足问题
6)解决方法:grant create table to jsd1304;--直接授予的权限不会被disable
7)因此,可总结如下:
①在过程中执行DDL操作,所需的权限必须通过直接授予的方式,不能通过角色授予。
②调用过程时,所有角色都是disable的(系统做的),即角色中包含的所有权限都不能生效。
③调用其他用户的过程,必须由过程的属主授予执行权限:grant execute on procname to username;
④权限-->角色-->用户<--权限
9.12变量
1)局部变量:在匿名块或存储过程中定义的变量为局部变量,即作用域在整个匿名块或存储过程中。程序运行结束,该变量也就不存在了。
2)绑定变量(宿主变量):在PL/SQL的SQL中可以直接使用绑定变量: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 i:i变量不需要声明,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语句,后面的就是单纯的字符串,不看内容。只有在执行时才看、报错。
3)PL/SQL中的静态SQL:
①Oracle在解析SQL时会吧PL/SQL中定义的变量转化为绑定变量,insert into test_chang values(i);减少了硬分析次数。
②server process将执行完的SQL cache起来,不关闭!当再次执行SQL时,不需要软分析。
③静态高效的原因是系统自动使用了绑定变量,同时动态SQL也可用绑定变量方式。
④静态SQL的1次软分析是由于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次执行,静态SQL的1次软分析是由于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算法来取得该SQL的hash值,然后在library cache里查找是否存在该hash值。
1)假设存在,则将此SQL与cache中的进行比较;假设“相同”,就将利用已有的解析树与执行计划,而省略了优化器的相关工作。这也就是软解释的过程。
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
1)package(包)是一个可以将相关对象存储在一起的PL/SQL结构。package包含了两个分离的组成部分:specification(package的声明,即包声明)和body(声明中的程序实现,即包体)。每个部分都单独被存储在数据字典中。包声明是一个操作接口,对应用来说是可见的。
2)包体是黑盒,对应用来说隐藏了实现细节。
11.2包的组成
将相关的若干程序单元组织到一块,用一个包来标识这个集合,包中可以包含以下的程序单元:
程序单元 |
描述 |
过程(procedure) |
带有参数的程序 |
函数(function) |
带有参数的程序,该程序有返回值 |
变量(variable) |
用于存储变化值的存储单元 |
游标(cursor) |
定义一条SQL语句 |
类型(type) |
定义复合类型(record、collection) |
常量(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_char、nvl等常用函数都在系统提供的standard package包中,只不过可以省略包名,其他包都要写包名;包内部可不写包名,出了包要写。
-
包里的过程和函数也可以重载。
-
包声明变了,包体一定要变。
-
可以有包声明,而没有包体,但不能只有包体而没有包声明。
-
删除包,则包体也被删除了。
-
若package没有更新,只更新了package body,调用包的程序代码不需要修改,也不需要重新编译。
12、触发器trigger
12.1面临问题
1)在作DML操作时,不需要提供主键值,系统自动生成。
2)如何实现级联更新。
12.2 DML触发器的组成
组成部分 |
描述 |
可能值 |
触发时间 |
触发事件的时间次序 |
before、after |
触发事件 |
DML语句是触发事件 |
insert、update、delete |
触发器类型 |
触发器体被执行的次数 |
statement(语句级)、row(行级) |
触发器体 |
该触发器将要执行的动作 |
完整的PL/SQL块 |
12.3 DML触发器的类型
1)语句级触发器。
2)行级触发器。
3)行级触发器与语句级触发器的区别:触发的次数不同,如果DML语句只影响一行,则语句级与行级触发器效果一样;如果影响多行,则行级触发器触发的次数比语句级触发器触发的次数多。
12.4 DML触发器的触发顺序
根据触发的时间、类型不同,可以组合为四种DML触发器。
触发时间 |
级别 |
描述 |
before |
statement |
在SQL语句执行之前执行一次 |
before |
row |
SQL语句影响的每条记录被update、delete或insert之前执行一次 |
after |
row |
SQL语句影响的每条记录被update、delete或insert之后执行一次 |
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将不会被触发。
3)trigger的两种状态可以相互转换,格式为:
alter trigger trig_name [disable|enable];
4)alter trigger语句一次只能改变一个触发器的状态,而alter table语句则一次能改变与指定表相关的所有触发器的使用状态。
alter table [schema.] tab_name {enable|disable} all triggers;
-
注意事项:drop table后:
-
表中的all constraints drop、表中的all index drop、表中的all trigger drop
-
无效的有:view、synonym、procedure、function、package
13、其他注意事项
13.1 PL/SQL的特点
1)结构化模块编程。2)良好的可移植性。3)良好的可维护性。4)提升系统性能
5)不便于向异构数据库移植应用程序。
13.2写PL/SQL的好处
client端可以不用写sql,人员要求低,安全,权限控制。
相对于C/S结构,代码存在数据库中,维护的成本低(JDBC的java程序相当于C/S结构)。
B/S结构:sql代码在as上(application server)。
能写静态sql,可以预先编译的,效率高。
JDBC只能写动态sql,程序在运行时,sql语句才编译。
13.3命名建议
b_绑定变量 v_普通变量 p_形参
13.4搞清楚如下内容
-
过程参数in、out、in out。 2)过程,角色,权限的关系。
3)静态sql、动态sql、绑定变量。
13.5保证所有对象的状态都是valid
alter procedure procname compile;
alter package pkg1 compile;
alter package pkg1 compile body;
13.6 declare中都可声明什么
变量、类型、cursor、execption、过程、函数
13.7数据库对象
table、view、index、sequence、synonym、procedure、function、package、package body