本文详细介绍了PL/SQL编程技术，包括变量、赋值、表达式、程序块、游标、异常处理等核心概念，同时展示了如何利用PL/SQL进行数据库操作，如查询、插入、更新和删除数据，并通过实例分析了触发器、函数和包的使用方法。
day01
1. PL/SQL简介
数据库：oracle、db2、sybase
PL：Procedure Language（编程语言）
PL/SQL 在不同数据库中语法不同
SQL语句和PL/SQL编程：基础。
架构*/备份和恢复/调优（DBA）
2. PL/SQL的程序结构
变量 / 赋值/ 表达式 / 分支/ 循环
selectcount(*) from user_tables;(数据字典：用户的所有表)
PL/SQL程序块的组成：
[declare] 变量声明
begin 语句；（执行部分）
[exception]异常（定义异常处理）
end；
打开屏幕的输出/关闭：set serveroutput on/off
eg.打印HelloWorld到屏幕
begin
dbms_output.put_line(‘HelloWorld!’);
end;
eg.定义变量，查询赋值后输出
v_title varchar2(20);
begin
select title
into v_title
from s_emp
where last_name=’Biri’;
dbws_output.put_line(v_title);
end;
eg.异常处理
v_titlevarchar2(20);--变量
begin
/*
取出SCOTT员工的职位并显示
*/
select job
into v_title
from emp
where deptno = 10;
dbms_output.put_line(v_title);
exception
when no_data_foundthen
dbms_output.put_line('foundnothing!!!');
when too_many_rowsthen
dbms_output.put_line('too many rows!!!');
end;
--标识符长度不超过30个字符.
--强调编程风格.
--注释
匿名块:不保存,相当于一条SQL语句.
3.变量
1).简单变量
varchar2(max_lenth) 4000字节
char(max_length) 2000字节
number(p,s)
date 7个字节
默认显示格式:DD-Mon-RR
boolean:true/false/null(pl/sql用)
binary_integer:整数(pl/sql用)
--变量在定义时可以赋初值,使用:=
eg.变量定义赋初值
declare
v_gender char(1);
v_count binary_integer :=0;
v_total_sal number(7,2):=0;
v_date date:=sysdate+7;
c_piconstant number(3,2):=3.14;
v_valid boolean not null:=true;
begin
--执行体…
end;
2）复合变量（组合变量）
变量中包含多个组件，每个组件可以单独存放数据
a．表类型（列table）
类似于数组：由下标和数据两部分组成。
没有长度限制，可动态增长。
eg.
declare//声明表类型
TYPE ename_table_type
IS TABLE OF varchar2(20)
INDEX BY binary_integer;
v_ename_table ename_table_type;//定义了一个具体表
begin
select ename
into v_ename_table(1)
from emp
where empno = 7788;
dbms_output.put_line(v_ename_table(1));
b．记录类型（record）
包括多个组件，数据类型可以不同，每个组件叫做field（域，字段）
eg.
TYPE emp_record_type
IS RECORD(
namevarchar2(20),
job varchar2(10)
);
emp_record emp_record_type;
dept_record emp_record_type;//可定义两个或以上变量
begin
selectename,job into emp_record.name,emp_record.job
fromemp where empno = 7788;
dbms_output.put_line(emp_record.name||’,’||emp_record.job);
end;
3)数据类型
%type:变量跟着某一个类型走
%rowtype:记录类型，跟着表走
eg.定义变量，类型是：%type
declare
v_id number(2);
v_id emp.empno%type；
v_job emp.job%type;
begin
select empno,job
into v_id,v_job
from emp where ename=’scott’;
end;
eg.测试记录类型
declare
emp_record s_emp%rowtype;
begin
select title,last_name
into emp_record.title,emp_record.last_name
from s_emp where last_name=’Biri’;
dbms_output.put_line(emp_record.title);
end;
eg.
declare
dept_record dept%rowtype;
begin
select deptno,dname,loc
into dept_record.deptno,
dept_record.dname,
dept_record.loc
from dept
where deptno = 10;
end;
4）赋值语句（：=）
declare
v_count number(2):=0;
v_rows number(2);
v_married boolean;
v_name char(20);
emp_record emp%rowtype;
begin
一些操作
--直接赋值
v_rows :=10;
v_married :=true;
v_name:=’smith’;
emp_record.ename:=’zhaomin’;
--表达式赋值
v_married:=(v_count=v_rows);
v_count:=v_count+1;
select comm into v_comm
from emp where empno=7788;
v_married :=(v_comm is null);
end;
注：boolean类型的数据不能直接打印
dbms_output.put_line('married');
else
dbms_output.put_line('not married');
end if;
end;
l 在赋值时使用函数
emp_records_emp%rowtype;
v_count number;
v_now char(30);
v_user char(20);
begin
select title into emp_record.title
from s_emp wherelast_name=&no;--替代变量
v_count:=length(emp_record.title);
v_now:=to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’);
v_user:=user;
end;
eg.用替代变量，查找特别方便有效
…
/
Enter value for no:XXX
/
Enter value for no:YYY
4.在PL/SQL中执行SQL语句.
select
DML: insert / update /delete
DDL: create / drop / alter /truncate
TCL: commit / rollback
(Transaction ControlLanguage)
1).select查询语句
必须有into子句，有且仅有一行结果
如果多余一行：too_many_rows
如果没有记录：no_data_found
select …into…（两个…的内容必须完全匹配）
from t1
left outer join t2 on t1.c1=t2.c2
where 条件
group by 字段
having 条件
order by 字段
2)DML语句
DML语句：insert / update / delete
事务处理: commit / rollback
l 复制表,不复制约束条件.
create table mytab
as
(select * from s_emp);(子查询)
begin
insert into emp_yuhualee
values(&no,’&title’,’&last_name’);
commit;
end;
l 更新操作
begin
update dept_ning
set loc = '&loc' where deptno = &no;
commit;
end;
l 上下文区域(context area):游标(cursor)
SQL%ROWCOUNT是游标属性,表示刚刚执行的sql语句影响的记录条数.
eg.
v_rows number:=0;
begin
delete from dept_ning
where deptno>40; ---contextarea:cursor,游标
v_rows:=SQL%ROWCOUNT;影响的记录条数
commit;
dbms_output.put_line(v_rows||’deleted’);
end;
rollback:回滚，取消刚才的DML操作（到没有commit之前，都可以恢复）
不能取消DDL操作。
--临时表temp_ning
--if存在,清空数据(trucate),drop
--if不存在,创建(create).
v_count number(2) := 0;
begin
select count(*) intov_count
from user_tables
where table_name ='TEMP_NING';//数据库里的表名要大写
if v_count = 1 then
execute immediate
'drop table temp_ning';//DDL 语句的删除方式DDL语即执行，
end if; //不需要commit
else
execute immediate
'create tabletemp_ning
(id numberprimary key,
name char(20))';
end if;
--其他逻辑,比如向临时表中插入记录
end;
5.分支语句
1）if-else分支
eg. ANALYST:20%; MANAGER:10%; SALESMAN: 5%
declare
v_rate number(3,2);
v_job emp.job%type;
v_sal emp.sal%type;
v_empno emp.empno%type := &no;
begin
select job, sal into v_job, v_sal
from emp_ning where empno = v_empno;
if v_job = 'ANALYST' then
v_rate := 1.2;
elsif v_job = 'MANAGER' then
v_rate := 1.1;
elsif v_job = 'SALESMAN' then
v_rate := 1.05;
else
v_rate := 1;
end if;
update emp_ning set sal = sal * v_rate
where empno = v_empno;
commit;
dbms_output.put_line(v_job ||' old:'||v_sal||','||'new:'||v_sal *v_rate);
end;
2）case 分支
eg.
declare
v_rate number(3,2);
v_job emp.job%type;
v_sal emp.sal%type;
v_empno emp.empno%type := &no;
begin
select job, sal into v_job, v_sal
from emp_ning where empno = v_empno;
case v_job
when 'ANALYST' then v_rate := 1.2;
when 'MANAGER' then v_rate := 1.1;
when 'SALESMAN'then v_rate := 1.05;
else v_rate := 1;
end case;
update emp_ning set sal = sal * v_rate
where empno = v_empno;
commit;
dbms_output.put_line(v_job||' old:'||v_sal||','||'new:'||v_sal * v_rate);
end;
6.循环语句
1）loop循环
*相当于do-while
eg.declare
v_i number := 1;
begin
loop
insert into temp_ning(id) --loop:循环10 次（b-a+1）
values(v_i);
exit when v_i = 10;
v_i := v_i + 1;
end loop;
commit;
end;
2）for循环
declare
v_i number := 10;
begin
for v_i in 100..110 loop -----循环次数：（b-a+1）
insert into temp_ning(id)
values(v_i);
end loop;
commit;
end;
3）while循环
declare
v_i number := 1000;
begin
while v_i < 1010 loop---循环次数：（b-a）
insert into temp_ning(id)
values(v_i);
v_i := v_i + 1;
end loop;
commit;
end;
7.小结
pl/sql结构 / 变量(简单变量/复合变量) / 赋值语句 / 表达式 / 程序块中使用SQL语句 / 分支 / 循环.
游标 / 异常
今天的单词:
declare：定义
Transaction：事务
validate: 验证
day02
homework-day01
List<HashMap>list
= new ArrayList<HashMap>();
l 使用table复合数据类型,提取员工表emp中的员工姓名ename和员工职位job
declare
emp_record emp%rowtype; --记录类型
type emp_table_type
is table of emp%rowtype
index by binary_integer;--table类型
mytable emp_table_type;
begin
select ename, job
into mytable(1).ename, mytable(1).job
fromemp
where empno = &no;
select * into mytable(100)
from emp
where empno = &no;
dbms_output.put_line(
mytable(100).ename);
end;
l 使用record类型封装部门表dept中的部门编号deptno和部门名称dname, 并打印出来
declare
dept_record dept%rowtype;
begin
select * into dept_record
from dept where deptno = &no;
dbms_output.put_line (dept_record.deptno);
dbms_output.put_line (dept_record.dname);
end;
l 在plsql中插入一条记录到员工信息表emp中，指定参数：员工编号、员工姓名、部门编号并提交。
--使用替代变量
begin
insert into emp(empno, ename, deptno)
values(&no, '&name', &dno);
commit;
end;
l 在plsql中根据员工编号参数删除员工表emp中的记录
begin
delete from emp where empno = &no;
commit;
dbms_output.put_line(
SQL%ROWCOUNT||' rows deleted.');
end;
l 事务处理语句
begin
insert into mytemp(id) values(1);
savepoint a;
insert into mytemp(id) values(2);
savepoint b;
insert into mytemp(id) values(3);
savepoint c;
insert into mytemp(id) values(4);
rollback to b;
commit;
end;
--telnet192.168.0.26
--sqlplusopenlab/open123
begin
for i in reverse 1..10 loop
dbms_output.put_line(i);
end loop;
end;
l 使用标签(label)定义多层循环
eg.
begin
<<outer>>
for i in 1..3 loop
dbms_output.put_line('i is '||i);
<<inner>>
for j in 1..3 loop
dbms_output.put_line('j is '||j);
if j = 2 then
exit outer;
end if;
end loop inner;
end loop outer;
dbms_output.put_line('GAME OVER!');
end;
l 求100-200之间的质数(素数)(今天的作业)
<<outer>>
for i in 100..200 loop
<<inner>>
forj in 2..i/2 loop
ifi mod j = 0 then
exit inner;
end if;
endloop;
end loop;
l pl/sql块的嵌套 /变量的作用域
外层看不见里层，里层可以看见外层的变量，相当于黑盒。
eg.
declare
x number := 100;
y char(1) := 'Y';
begin
declare
y char(1) := 'A';
z number := 1;
begin
-- 100 A 1
dbms_output.put_line(x || y || z);
end;
-- 100 Y
dbms_output.put_line(x || y);
end;
l savepoint：保存点
SQL>savepoint a;
SQL>rollback to a;--a点以后点保存点都被取消
一.游标 cursor
DML/Select 执行时的内存空间（context area）
cursor：是指这个内存空间的指针。
隐式游标/ 显示游标(自定义游标名称)*
SQL%ROWCOUNT /mycur%ROWCOUNT
游标的属性：%ROWCOUNT ，%FOUND,%NOTFOUND, %ISOPEN
1）显式游标：
使用步骤：
1. 定义游标
2. 打开游标
3. 操作游标（从游标中取数据）
4. 关闭游标
eg.定义cursor：是一条select语句
v_nameemp_yu.name%type;
v_salemp_yu.sal%type;
--1.定义游标，把名字和（查询）语句绑定。
-- 相当于 String sal = “select * from emp_yu”;
CURSOR emp_cursor IS
selectname,sal from emp_yu;
begin
--2.打开游标：执行对应的语句，取出的记录保存在游标的内存区域，游标指针指向第一条记录。
--相当于 rs =stmt.executeQuery(sql);
openemp_cursor;
--3.遍历取出游标中的记录，每次取一条并使指针下移一条。
loop
fetch emp_cursor into v_name,v_sal;
dbms_output.put_line(v_name||’ ’’s salaryis:’||v_sal);--’’是转义：’
exit whenemp_cursor%notfound;
end loop;
dbms_output.put_line(’total:’||emp_cursor%rowcount);
end;
eg.
CURSOR emp_cursor(v_no number)IS
select name,sal from emp_yu
where empno = v_no;
emp_record emp_yu%rowtype;
begin
open emp_cursor(20);--打开的时候传参
loop
fetch emp_cursor
into emp_record.name,emp_record.sal;
exit when emp_cursor%notfound;
dbms_output.put_line(emp_record.name||’,’emp_record.sal);
endloop;
closeemp_cursor;
end;
l 游标变量— 强类型
eg.
--1.定义游标变量类型, 强类型
TYPE my_cursor_type
IS REF CURSOR RETURN dept%rowtype;
mycursor my_cursor_type;--2.定义游标变量
myrecord dept%rowtype;
begin
--3.游标打开时才定义对应的sql语句
open mycursor forselect * from dept;
loop
fetch mycursor intomyrecord;
exit when mycursor%notfound;
dbms_output.put_line(myrecord.dname||','||myrecord.loc);
end loop;
close mycursor;
end;
l 游标变量---弱类型
eg.
inputCharchar(1) := upper('&input');
--定义记录类型和记录类型变量
typerecord_type is record（
id number,
descriptionvarchar2(100));
myrecordrecord_type;
--定义游标类型和游标变量
typecursor_type IS REF CURSOR;--弱类型（即后面没有类型）
mycursorcursor_type;
begin
if inputChar ='E' then
--ifmycursor%isopen = false then
open mycursor for select empno, ename from emp;
--end if;
elsifinputChar = 'D' then
open mycursor for select deptno, dname fromdept;
end if;
fetch mycursor into myrecord;--只有fetch，游标才会下移
whilemycursor%found loop
dbms_output.put_line(myrecord.id||','
||myrecord.description);
fetchmycursor into myrecord;
end loop;
dbms_output.put_line('GAME OVER');
closemycursor;
end;
2）小结-显式游标
游标的处理过程:
声明 / 打开 / 获取数据 / 关闭
l 针对游标的for循环（简化方式）
适用条件：游标声明时已绑定sql语句
eg.
CURSOR mycursor(v_no number) IS
select name,salfrom emp_yu
whereempno=v_no;
begin
for myrecord inmycursor(10) loop
dbms_output.put_line(myrecord.name||’,’||myrecord.sal);
end loop;
end;
l 事务的起点是DML操作，select语句不启动事务
select...for update启动事务.
eg.
SQL>select ename, job, sal from emp forupdate;--启动事务
SQL>update emp set sal = sal * 1.2
where job = 'ANALYST';
SQL>commit;
l 更新或删除游标行
CURSORmycursor IS
select *from emp_ning for update;
begin
for myrecordin mycursor loop
ifmyrecord.job = 'ANALYST' then
updateemp_ning set sal = sal * 1.2
where current of mycursor;
dbms_output.put_line(myrecord.sal
||'-'||myrecord.sal * 1.2);
else
dbms_output.put_line(myrecord.sal ||' no change.');
end if;
end loop;
commit;
end;
二.异常exception
系统定义 的异常：由系统抛出, 预定义/ 非预定义
用户自定义 的异常：自行抛出
l 预定义异常:有编码,有信息,有名字
no_data_found（名字）
too_many_rows
zero_divide
rowtype_mismatch
...
l 非预定义异常:有编码,有信息,没有名字
eg. 预定义异常
v_salemp.sal%type;
begin
select salinto v_sal from emp
where ename ='&name';
dbms_output.put_line('GAME OVER!');
exception
when no_data_foundthen---如果name=‘liy’表里没记录，才是 ：no_data_found
dbms_output.put_line('no data');
whentoo_many_rows then
dbms_output.put_line('too many rows');
when others then
dbms_output.put_line('others');
end;
l 非预定义异常
准备数据环境:emp表的deptno字段参照dept表的主键列.
1.复制表emp和dept:--约束条件不能复制
create table emp_sd1103
asselect * from ninglj.emp;
create table dept_sd1103
asselect * from ninglj.dept;
2.创建主键约束
alter table dept_sd1103
addconstraint d_pk primary key(deptno);
3.创建外键约束
alter table emp_sd1103
addconstraint e_fk foreign key (deptno)
references dept_sd1103(deptno);
eg.
e_child_recordexception;--定义异常名字
PRAGMAexception_init(
e_child_record, -2291);--关联异常编码 ？
begin
--delete deptwhere deptno = 10;
insert intoemp(empno,ename, deptno)
values(7654,'limochou',99);
exception
whene_child_record then
dbms_output.put_line('constraint');
when othersthen
dbms_output.put_line('others');
end;
l 用户自定义异常
declare
v_empno emp.empno%type := &no;
v_comm emp.comm%type;
e_null exception;
--自定义异常名字和编码的关联
pragmaexception_init(e_null,-20101);//一般定义成20000以上的数字
begin
select comm into v_comm
from emp where empno = v_empno;
ifv_comm is null then
raise e_null;--主动抛出异常，然后下面的exception会捕捉到
else
update emp set comm = comm + 100
where empno = v_empno;
endif;
exception
when e_null then
dbms_output.put_line('comm is null');
when others then
dbms_output.put_line('others');
end;
今天的单词:
reverse： for循环里1..10 反向—>10..1
fetch： 取来
constraint：约束
validate： 验证
day03
homework
游标/异常
关于异常的两个常用函数
SQLCODE（错误码）/SQLERRM（错误编号）
eg.
declare
v_empno emp.empno%type := &no;
v_comm emp.comm%type;
e_null exception;
pragma exception_init(e_null,-20101);
begin
select comm into v_comm
from emp where empno = v_empno;
if v_comm is null then
raise e_null;--主动抛出异常
end if;
exception
when others then
--打印错误编码和错误信息
dbms_output.put_line
(sqlcode||'---'||sqlerrm);
end;
emp_stats(empno,ratio, stats)
emp_errors(code,message)-sqlcode/sqlerrm
PL/SQL块到程序单元：
匿名块
过程/ 函数/ 包/ 触发器
存储过程（stored Procedure）=SP ，完成某种功能，可以没有返回值。
函数：完成某种计算，一定有返回值。
包：把过程和函数组合到一起的机制。
触发器：（trigger）相当于监听器。
一.过程
完成特定的操作或任务，相当于一个脚本。
过程是一个封闭的整体，过程内部不要用替代变量。
1）定义过程
eg.定义过程 （只有输入参数的过程）--输入参数是常量
create or replace procedure change_salary_yu
(p_noin number, p_sal number)
is
v_count number;（中间变量）
begin
selectcount(*) into v_count
fromemp_yu where empno=p_no;
ifv_count>0 then
update emp set sal = p_sal
where empno=p_no;
commit;
endif;
exception
when others then
dbms_output.put_line(sqlcode||’---’||sqlerrm);
end;
2）调用过程:
方法一：execute
SQL>exec change_salary_yu(7788,5000);
方法二：在匿名程序块中调用过程,直接使用
SQL>declare
v_no number :=&no;
v_sal number:= &sal;
begin
change_salary(v_no, v_sal);
end;
l 在数据字典中查看刚刚建立的过程.
表: user_tables;
约束:user_constraints
视图:user_views
过程:user_procedures
对象:user_objects,是所有对象的数据字典视图.(user_objects> user_procedure)
注意:过程名字,表名默认大写.
eg.数据字典中查询过程
--在user_objects中查询:
selectobject_name, object_type ---procedure
fromuser_objects
whereobject_name = 'CHANGE_SALARY';
--在user_procedures中查询:
select * fromuser_procedures
whereobject_name = 'CHANGE_SALARY';
l 方案:Schema:全部对象的集合
user_objects:方案下所有对象的描述
eg.
--查看方案下有多少种对象:
select distinct object_name, object_type
from user_objects;
--查看方案下有多少个表:
select count(*) from user_objects
where object_type = 'TABLE';
l 查看刚刚建立的过程内容:
--先格式化输出,
--数字用99(几个9就显示几个数字长度),
--字符用aN(N表示长度)
eg.
columntext format a50
selectline, text from user_source
wherename = 'CHANGE_SALARY';
l 有输入和输出参数的过程
eg. 过程emp_sum,输入deptno,输出人数和薪水总和
定义过程
create or replace procedure proc_emp_sum(
p_deptno in emp.deptno%type default 10,
p_count out number,
p_sumsal out number)--三个形参
is
begin
select count(*), sum(sal)
into p_count, p_sumsal
from emp
where deptno = p_deptno;
exception
when others then
dbms_output.put_line(sqlcode || '---' ||sqlerrm);
end;
执行过程
方法一：按位置传参
eg.
SQL>declare
v_countnumber;
v_sumsalnumber;
begin
--过程的in参数是常量,out参数是变量
proc_emp_sum(20,v_count, v_sumsal);
dbms_output.put_line(v_count);
dbms_output.put_line(v_sumsal);
方法二：使用"=>"传参,形参=>实参
eg.
p_count => v_count,
p_sumsal => v_sumsal);
dbms_output.put_line(v_count);
dbms_output.put_line(v_sumsal);
end;
方法三：使用绑定变量
注意：绑定变量使用时前面加":"号
eg.
SQL>var v_count number;对于要输出的变量才在执行过程时定义
SQL>var v_sumsal number;
SQL>exec proc_emp_sum(20, :v_count, :v_sumsal);
SQL>print :v_count
SQL>print :v_sumsal
l 创建无参的过程
eg.创建过程
create table mytemp_ning(id number);
createor replace procedure proc_temp
is
v_count number;
begin
for v_i in 1..10 loop
insert into mytemp_ning values(v_i);
end loop;
commit;
end;
执行过程
SQL>exec proc_temp
SQL>select * from mytemp_ning;
l 练习:
username char(10),
passwordchar(10));
insert into admin_ning
values('1001','1234');
commit;
/*要求:
匹配: 返回1;
用户名对密码错: 返回-1;
用户名错: 返回-2;
*/
创建过程
create or replace procedure check_user(
p_username in char,
p_password in char,
flag out number)
is
v_password char(10); 注意：v_password和表里的password
begin 类型要一致。（不能一个是char，另一个是varchar）
selectpassword into v_password
fromadmin_ning
where username= p_username;
if v_password= p_password then
flag := 1;
else
flag :=-1;
end if;
exception
when othersthen
flag := -2;
end;
--测试过程
declare
v_flag number;
begin
check_user('&username','&password',v_flag);
dbms_output.put_line(v_flag);
end;
Java程序：
public void check_user(String username, Stringpassword){
//callcheck_user
}
小结:
过程:
1)定义过程的方式.
2)在数据库中调试过程的方式
showerrors
execproc_name(p1,p2...)
用匿名块测试有输出参数的过程.
3)jdbc调用过程,传入参数,取回结果.
今天的单词:
procedure
store
compile
day04
复习
l 过程的定义
create or replace procedure proc_name(
参数 in|out 数据类型
)
is|as
变量声明
begin
业务逻辑
exception
when others then
处理异常
end;
l 访问数据库的方法：
1)应用程序访问
java ->JDBC:CallableStatement -> 过程
in|out
2)数据库访问
sqlplus -> exec proc_name
oracle 9i/10g: plsql developer / toad(第三方工具，图形化工具)
oracle 11g : sql developer(oracle自己的)
一.函数
实现功能(一般是计算),返回计算结果.
和过程的差别: 返回值 / 是否独立存在
length / upper /round / mod / to_char / to_date / nvl / decode / coalesce（返回第一个非空表达式）
eg.函数使用语句
selectlength(ename) from emp;
select * from emp
where upper(ename)= 'SCOTT';
select round(sal *1.2345,2) from emp;
selectto_char(sysdate, 'yyyy-mm-dd')
from dual;
insert intoemp(empno, ename, hiredate)
values(1001,'liucs',
to_date('2010-01-01','yyyy-mm-dd'));
select ename, sal+ nvl(comm,0) from emp;
selectcoalesce(comm, sal, 100) from emp;
declare
v_count number;
begin
v_count := length('helloworld');
end;
1）函数声明
eg.
计算薪水对应的税率
create or replace function tax_ning
(v_value in number)
return number(类型)
is
v_rate number;
begin
if v_value < 3000 then
v_rate := 0;
elsif v_value < 5000 then
v_rate := 0.05;
elsif v_value < 8000 then
v_rate := 0.1;
else
v_rate := 0.2;
end if;
return v_rate;
end;
2）测试函数
测试的方式有三种:
1. 虚表测试
selecttax_ning(10000) from dual;
2. 表测试：
select ename, sal, sal * tax_ning(sal)
from emp;
3. 匿名块测试
declare
v_tax number;
v_sal number := &sal;
begin
v_tax := tax_ning(v_sal);
dbms_output.put_line(v_tax);
end;
eg.查询函数名字
select object_name from user_procedures
where object_name=’TAX_YU’;
eg.查询员工人数 / 薪水总和
createor replace function get_emp(p_deptno in number,p_sumsal out number)
return number
is
v_countnumber;
begin
selectcount(*), sum(sal)
into v_count,p_sumsal
from emp
where deptno =p_deptno;
returnv_count;
exception
when othersthen
dbms_output.put_line(sqlcode || sqlerrm);
end get_emp;
--测试:按位置传参或使用:形参=>实参方式
declare
v_sumsal number;
v_count number;
begin
v_count := get_emp(10,v_sumsal);
dbms_output.put_line(v_count||','||v_sumsal);
end;
3）过程vs函数
过程 函数
---------------------------------------
特定任务 复杂计算
独立运行 不独立, 必须作为表达式一部分
PROCEDURE FUNCTION
IN|OUT IN|OUT(不建议用out)
不需要返回类型 头必须定义返回类型return
函数体中必须return数据
user_procedures /user_source
二.包 package
package(包头) / package body(包体)
定义:常量,变量 实现
过程声明, 函数声明
不实现.
1）创建包头
eg.
创建包头
create or replacepackage pack_emp_ning
is
emp_sumsal number;
emp_record emp%rowtype;
TYPE mycursor_type IS REF CURSOR;
--findAll
procedure findAll(p_no in number,
p_cursor out mycursor_type);
--insert
procedure add_emp(
p_empno in emp.empno%type,
p_ename in emp.ename%type,
p_deptno in emp.deptno%type,
p_flag out number);
--delete
procedure remove_emp(
p_empno emp.empno%type);
--select
procedure query_emp(
p_empno emp.empno%type);
function get_sumsal(
p_deptno emp.deptno%type)
return number;
end pack_emp_ning;
SQL>程序包已创建（调试错误：show err）
2）创建包体
创建包体
create or replacepackage body pack_emp_ning
is
--p_no=0,返回全部,p_no=7788,返回一条
procedure findAll(p_no in number,
p_cursor out mycursor_type)
is
sqlstr varchar2(500);
begin
if p_no = 0 then
open p_cursor for
select empno, ename from emp;
else
sqlstr := 'select empno,ename from emp where empno = :w_no';
open p_cursor for sqlstr using p_no;
end if;
end findALL;
--insert
procedure add_emp(
p_empno in emp.empno%type,
p_ename in emp.ename%type,
p_deptno in emp.deptno%type,
p_flag out number)
is
begin
insert into emp(empno, ename, deptno)values(p_empno, p_ename, p_deptno);
p_flag := SQL%ROWCOUNT;
commit;
end add_emp;
--delete
procedure remove_emp(
p_empno emp.empno%type)
is
begin
delete from emp where empno = p_empno;
commit;
end remove_emp;
--select
procedure query_emp(
p_empno emp.empno%type)
is
begin
select * into emp_record
from emp where empno = p_empno;
end query_emp;
function get_sumsal(
p_deptno emp.deptno%type)
return number
is
v_sumsal number;
begin
select sum(sal) into v_sumsal
from emp
where deptno = p_deptno;
return v_sumsal;
end get_sumsal;
end pack_emp_ning;
SQL>程序包体已创建（包和包体要分开创建）
3）测试函数or过程
l 测试函数
selectpack_emp_ning.get_sumsal(20) –包名.函数名（过程名）
from dual;
l 测试过程新增记录
declare
v_flag number;
begin
pack_emp_ning.add_emp(9999,'peter',20,v_flag);
dbms_output.put_line(v_flag);
end;
l 测试过程删除记录
execpack_emp_ning.remove_emp(9999);
l 测试过程,返回全部记录
declare
CURSOR mycursor IS
select empno, ename from emp;
myrecord mycursor%rowtype;
mycur pack_emp_ning.mycursor_type;
begin
pack_emp_ning.findAll(1111, mycur);--？为什么参数1，输入0时返回所有名字，
loop 输入部门号7，返回为空？
fetch mycur into myrecord;
exit when mycur%notfound;
dbms_output.put_line(myrecord.ename);
end loop;
end;
l 在数据字典中查询包:
selectobject_name, object_type
from user_objects
where object_namelike '%PACK_EMP_NING%';
4）包的基本规则:
1)包头中:公开声明,外界可见.
2)包体中定义的过程和函数,外界不可见.
3)前向声明(先声明，后使用)
4)包头是必须的,包体不是必须的.
5)包头必须在包体前建立.
package body....
--thoerproc必须在myrpoc之前完成.
procedure otherproc(i number)
....
procddure myproc
is
begin
otherproc(1);
end;
....
l 一组常量/变量/过程/函数的组合,好处:
1)便于管理
2)一次调用整个包装载到内存,提高效率.
3)将私有的过程/函数隐藏在包体内.安全
4)包头中的变量在一个会话中能够一直保持,起到全局变量的作用.
l 建立一个只有包头没有包体的结构
eg.
create or replace package global_consts
is
piconstant number(3,2) := 3.14;
e constant number(3,2) := 2.71;
mile2kilo constant number := 1.6;
kilo2mile constant number := 0.62;
end global_consts;
exec dbms_output.put_line(20*global_consts.kilo2mile)
l 过程补充:
create or replace procedure findAllEmp(
p_cursor out sys_refcursor)
is
begin
open p_cursor for select * from emp;
end findAllEmp;
declare
mycur sys_refcursor;
myrecord emp%rowtype;
begin
findAllEmp(mycur);
loop
fetch mycur into myrecord;
exit when mycur%notfound;
dbms_output.put_line(myrecord.ename);
endloop;
end;
--小结:
1)函数
2)包
interface PersongetName()
class Student getName() / getSchool()
Person p = newStudent();
p.getSchool();
day05
程序结构:
过程 / 函数 / 包 / 触发器
一.触发器
类似于listener, 隐式触发(fire).
不接受参数.不做事务处理.
安全 / 审计 / 规则性校验 / 同步 / 日志
三个大类:
1)DML触发器
DML: insert / update / delete /
merge(oracle独有)
2)Instead of触发器(替代触发器)
视图
3)系统触发器(DBA的工具)
DDL操作: create/ drop / truncate / alter
登入登出
数据库启动关闭.
1）DML触发器
--定义触发器,在非工作时间不允许对emp表插入记录.
createor replace trigger secure_emp_ning
BEFORE insert ONemp
begin
if to_char(sysdate, 'DY')
in ('FRI','SAT','SUN')
OR
to_char(sysdate, 'hh24:mi')
not between '08:00' and '18:00'
then
raise_application_error(-20100,'u can''t insert data out ofworktime!');-- ’要转义
end if;
end;
--raise_application_error:系统的存储过程
--errorcode:20000-20999之间
--查询数据字典（user_triggers）
select object_name
from user_objects
where object_name = 'SECURE_EMP_NING';
selecttrigger_name, status, table_name
from user_triggers
where trigger_name= 'SECURE_EMP_NING';
l 修改触发器,增加对update /delete的处理
eg.
createor replace trigger secure_emp_ning
BEFORE insert or update or delete
ON emp
begin
if to_char(sysdate, 'DY')
in ('SAT','SUN')
OR
to_char(sysdate, 'hh24:mi')
not between '08:00' and '18:00'
then
ifinserting then
raise_application_error(
-20100,'cann''t insert!');
elsifupdating then
raise_application_error(
-20101,'cann''t update!');
elsifdeleting then
raise_application_error(
-20102,'cann''t delete!');
else
raise_application_error(
-20103,'go back home!');
end if;
end if;
end;
l 语句级触发器和行级触发器
--当新增或修改记录,当职位不是manager或president时,并且薪水>2w,拒绝操作.
1001, peter,sales, 10000 --ok
1002, rose, clerk,8000 --ok
1003, king,manager, 250000 --ok
1004, jones,programmer, 21000 –error(即会触发错误)
update emp setsalary = salary * 1.2
where job ='PROGRAMMER';
10个. 3个人*1.2 > 2w. 7个人*1.2 <= 2w
:new 表示新值, :old 表示旧值
eg.
create or replacetrigger restrict_sal_ning
before insert orupdate on emp
for each row
begin --ENABLE
if NOT(:new.job in ('MANAGER', 'PRESIDENT'))
and
:new.sal > 20000
then
raise_application_error(-20106,'commonworker cann''t earn so many salary!');
end if;
end;
l 使用触发器对约束条件做补充
eg.薪水不允许降低
create or replacetrigger check_sal 在头定义中new/old不加冒号.
before update ofsal on emp
for each row
when (new.sal < old.sal)
begin
raise_application_error(-20507, 'cann''t cutdown salary!');
end;
--测试
update emp set sal= sal - 1;
l 语句级触发器和行级触发器的差别
差别：语句级触发器只触发一次，行级触发器每操作一行触发一次。
create tablemytemp(
myvaluevarchar2(20));
--测试for each row有或没有的情况.
create or replacetrigger trig_emp
before update onemp
--for each row
begin
insertinto mytemp
values('update a record');
end;
update emp set sal= sal + 100;
select * frommytemp;
l 审计功能的触发器
--在DML操作之后保存数据的变更
eg.表audit_emp_ning保存变更的数据.
id numberprimary key,--审计表的主键列
usernamevarchar2(20),
occurtime date,
empnonumber(4),
old_namevarchar2(20),
new_namevarchar2(20),
old_salnumber(7,2),
new_salnumber(7,2)
);
create sequence audit_seq_ning;--建序列
创建触发器：
create or replacetrigger tri_audit_emp_ning
afterdelete or insert or update onemp
for each row
begin
insert into audit_emp_ningvalues(audit_seq_ning.nextval, user, sysdate, :old.empno, :old.ename,:new.ename, :old.sal, :new.sal );
end;
l 数据同步
emp -- department
empno, ename,deptno （员工表）| deptno, dname, num（部门表）
select deptno, count(*)
from emp
group by deptno;
--建立部门表,其中包含人数字段
as
selecte.deptno, d.dname, count(*) num
fromemp_yu e join dept_yu d
one.deptno = d.deptno
groupby e.deptno, d.dname;
l 创建触发器,当emp表人员变更,同步department表.
eg.
create or replacetrigger tri_emp_dept
after delete orinsert or update on emp
for each row
begin
if inserting then
update department set num = num + 1 where deptno = :new.deptno;
elsif deleting then
update department set num = num - 1
where deptno = :old.deptno;
elsif updating then
update department set num = num + 1 where deptno = :new.deptno;
update department set num = num - 1
where deptno = :old.deptno;
end if;
end;
[作业: 数据表emp_sum结构:deptno,sum(sal)
emp:新增/更新/删除 -->emp_sum薪水总和改变
]
2）替代触发器
视图: 简单视图 / 复杂视图 / 关联视图
1)简单视图,允许通过视图修改基表数据
create or replaceview v_emp
as
select empno,ename, sal from emp
where deptno = 10
with read only;
2)复杂视图,不允许通过视图修改基表数据
create or replaceview emp_sum_sal
as
select deptno,sum(sal) sumsal
from emp group bydeptno;
3)关联视图,不允许通过视图修改基表数据
create or replaceview v_emp_dept
as
selecte.empno,e.ename, d.dname, d.loc
from emp e join dept d
on e.deptno =d.deptno
and e.deptno = 20;
l 创建替代触发器,当对复杂视图做删除操作,转换为删除基表数据的操作.
eg.
create or replacetrigger emp_view_del
instead ofdelete on emp_sum_sal
for each row
begin
delete from emp
where deptno = :old.deptno;
end;
--在创建触发器之前之后测试:
delete fromemp_view_del
where deptno = 10;
--查过程和函数体的数据字典:user_source
select text fromuser_source
where name ='MYPROC';
--查看user_triggers的结构
descuser_triggers;
--查触发器的执行体:user_triggers
selecttrigger_body from user_triggers
where trigger_name= 'EMP_VIEW_DEL';
3）触发器的执行顺序
--执行对表的操作,表上所有触发器的触发顺序:
1)before statement触发器
2)行级触发器
a.before row 触发器
b.after row 触发器
3)DML语句,检查完整性约束条件
4)after statement 触发器
--测试:
create or replacetrigger cascade_update
after update ofdeptno on dept
for each row
begin
update emp
set emp.deptno = :new.deptno
where emp.deptno = :old.deptno;
end;
4）系统触发器(DBA工具)
1)DDL触发器:create/drop/alter/truncate
2)系统事件触发器: 登录登出 / 系统启动关闭
create [orreplace] trigger tri_name
timing
ddl_event1 [ or ddl_event2]
on database|schema
trigger_body;
--建立系统触发器,记录用户的DDL操作.
--DBA角色的账户才可以操作.
create tableevent_log(
eventid number primary key,
eventname varchar2(30),
obj_name varchar2(30),
obj_type varchar2(30),
obj_owner varchar2(30),
occurtime date default sysdate
);
create sequenceevent_log_seq;
create or replacetrigger tri4ddl
after create oralter or drop on database
begin
insert into event_log
(eventid,
eventname,
obj_name,
obj_type,
obj_owner)
values(event_log_seq.nextval,
sysevent,
dictionary_obj_name,
dictionary_obj_type,
dictionary_obj_owner);
end;
l 系统登录登出触发器
eg.
创建监听表
create table event(
eventuservarchar2(30),
actionvarchar2(20),
logtime datedefault sysdate
);
登入触发器
create or replacetrigger tri4logon
after logon on database
begin
insert into event(eventuser, action)
values(user, 'lai le!');
end;
登出触发器
create or replacetrigger tri4logoff
before logoff on database
begin
insert into event(eventuser, action)
values(user, 'zou la!');
end;
5）触发器小结
被Oracle Server维护,只有监听的事件发生,隐式触发(fire).
不能被调用,没有输入输出参数,没有事务处理语句.
行级(row)触发器 / 语句级(statement)触发器
适当建立触发器,不宜建过多.
DML触发器 / 替代触发器 / 系统触发器
--PL/SQL小结:
基本语法:数据类型/变量/赋值/表达式/分支/循
环
程序结构:(匿名块) / 过程 / 函数 / 包 / 触发器
今天的单词:
Instead of
trigger
available
万能的PLSQL示例分析：
Eg.游标循环示例
set serveroutput on;
declare
CURSOR mycur IS (select o.callphone phonefrom mp_order o group by o.callphone);
itelecom tb_simcard_area.itype%type:=0;
i integer:=0;(或者 iinteger:=&no(替代变量))
begin
for cur in mycur loop
begin
dbms_output.put_line(1);
dbms_output.put_line(cur.phone);
select a.itype into itelecom fromtb_simcard_area a where a.cphone= cur.phone;--如果没有记录into（放入到）变量，就会报no_data_found错误
dbms_output.put_line(itelecom);
exception—异常放在begin:end段，当抛出异常，还能继续执行loop
--when no_data_found then
--dbms_output.put_line('no_data_found');
when others then—可以只用others来捕获异常（但不能没有处理异常的语句，就算加上一句打印语句都行（如下））
i:=i+1;
dbms_output.put_line('error');
end;
end loop;
end;
Eg.DML触发器示例（触发器是DML触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了）
create or replace trigger imsi_itelecom_trigger2--记录s8_temp_ordersubmit表里imsi
after insert on s8_temp_ordersubmit--对应的运营商
for each row
declare
temp_imsi varchar2(20) := :new.imsi;
itelecom number(10);
count1 number(10);
begin
--dbms_output.put_line('----------temp_imsi:'|| temp_imsi);
select count(*)
into count1
from jfb_imsi_itelecom i
where i.imsi = temp_imsi;
if count1 = 1 or temp_imsi is null then
return;--可以用return，触发语句（insertinto）还是可以自动提交；
else
if length(temp_imsi) = 11 then
select p.itelecom
into itelecom
from tb_simcard_prefix p
where p.cnumber = substr(temp_imsi, 1,3);
else
temp_imsi := substr(temp_imsi, 1, 5);
--dbms_output.put_line('----------temp_imsi:' || temp_imsi);
if (temp_imsi = '46000' or temp_imsi ='46002' or temp_imsi = '46007') then
itelecom := 0;
elsif (temp_imsi = '46001' or temp_imsi ='46006') then
itelecom := 1;
elsif (temp_imsi = '46003' or temp_imsi ='46005') then
itelecom := 4;
else
itelecom := 5;
end if;
end if;
end if;
--dbms_output.put_line('----------itelecom:'|| itelecom);
insert into jfb_imsi_itelecom
(imsi, itelecom2)
values
(:new.imsi, itelecom);
endimsi_itelecom_trigger2;
PS：触发器小结：
1)触发器是DML触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了;
2)触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT语句;
扫一扫
7万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
