plsql学习笔记1
plsql简介
- pl/sql编程语言
- pl/sql编程语言是对sql语言的扩展，使得sql语言更具有过程化编程的特性
- pl/sql编程语言比一般的过程化语言更加灵活高效
- pl/sql编程语言主要用来编写存储过程函数等
定义和输出
declare
i number(2) :=15;
begin
dbms_output.put_line(i);
end;
结果：
15
declare
i number(2) :=15;
s varchar2(10) := '小明';
begin
dbms_output.put_line(i);
dbms_output.put_line(s);
end;
结果
15
小明
declare
i number(2) :=15;
s varchar2(10) := '小明';
eb E_BOOK.name%type; --引用型变量
begin
dbms_output.put_line(i);
dbms_output.put_line(s);
select name into eb from E_BOOK where id=1;
dbms_output.put_line(eb);
end;
结果
15
小明
文学类书1
declare
i number(2) :=15;
s varchar2(10) := '小明';
eb E_BOOK.name%type; --引用型遍历
bookrow E_BOOK%rowtype; --记录型变量
begin
dbms_output.put_line(i);
dbms_output.put_line(s);
select name into eb from E_BOOK where id=1;
dbms_output.put_line(eb);
select * into bookrow from E_BOOK where id=1;
dbms_output.put_line('id为：' ||bookrow.id ||' name为：' || bookrow.name);
end;
结果
15
小明
文学类书1
id为：1 name为：文学类书1
if判断
题目：
- 输入小于18的数字，输出未成年
- 输入大于18小于40的数字，输出中年人
- 输入大于40的数字，输出老年人
declare
i number(3) := 19;
begin
if i<18 then
dbms_output.put_line('未成年');
elsif i<40 then
dbms_output.put_line('中年人');
else
dbms_output.put_line('老年人');
end if;
end;
结果
中年人
循环
用三种方式输出1到10
while循环
declare
i number(2) := 1;
begin
while i<11 loop
dbms_output.put_line(i);
i := i+1;
end loop;
end;
结果：
1
2
3
4
5
6
7
8
9
10
exit循环(常用)
declare
i number(2) := 1;
begin
loop
exit when i>10;
dbms_output.put_line(i);
i := i+1;
end loop;
end;
结果：
1
2
3
4
5
6
7
8
9
10
for循环
declare
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
结果：
1
2
3
4
5
6
7
8
9
10
游标
游标：可以存放多个对象，多行记录(类似域java的集合)
输出表中所有行的name
declare
cursor c is select * from e_book;
--定义了一个名为c的游标 并把表中的所有数据存入游标中
bookrow e_book%rowtype;
begin
open c;
loop
fetch c into bookrow;
exit when c%notfound;
dbms_output.put_line(bookrow.name);
end loop;
close c;
end;
结果：
文学类书1
文学类书2
文学类书3
社会科学书1
社会科学书2
社会科学书3
小说书1
小说书2
小说书3
给指定书增加价格
...
存储过程
存储过程：存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库，可以直接被调用。这一段pl/sql一般都是固定步骤的业务
--给指定书的价格加100块钱
create or replace procedure p1(bid e_book.id%type)
is
begin
update e_book set price = price+100 where id=bid;
commit;
end;
--测试
declare
begin
p1(1);
end;
存储函数（理解）
存储过程和储存函数的取别
一般来讲，过程和函数的区别在于函数可以有一个返回值，而过程没有返回值，但过程和函数都可以通过out指定一个或多个输出参数，我们可以利用out参数，在过程和函数中实现返回多个值
--通过储存函数实现计算指定书籍的10本的价格
--存储过程和存储函数的参数都不能带长度
--存储函数的返回值类型不能带长度
create or replace function x(bid e_book.id%type) return number --不能带长度
is
s number(10);
begin
select price*10 into s from e_book where id=bid;
return s;
end;
--测试
--存储函数在调用的时候，返回值需要接收
declare
s number(10);
begin
s := x(1);
dbms_output.put_line(s);
end;
out类型参数如何使用
--out类型参数如何使用
--使用存储过程来算10*价格
create or replace procedure z(bid e_book.id%type, p out number)
is
s number(10);
begin
select price*10 into s from e_book where id=bid;
p := s;
end;
--测试
declare
pp number(10);
begin
z(1,pp);
dbms_output.put_line(pp);
end;
结果
22000
in和out类型参数的取别是什么?
凡是设计到into查询语句复制或者：=赋值操作的参数，那必须使用out来修饰，其他用in
存储过程和存储函数的区别
语法区别：关键字不一样。存储函数比存储过程多了两个return
本质区别：存储函数有返回值，而存储过程没有返回值
如果存储过程想实现有返回值的业务，我们就必须使用out类型的参数
即便是存储过程使用了out类型的参数，其本质也不是真的有了返回值
而是在存储过程内部给out类型参数取值，在执行完毕后，我们直接拿输出类型参数的值
我们可以使用存储函数有返回值的特性，来自定义函数
而存储过程不能用来自定义函数
触发器
触发器：就是制定一个规则，在我们做增删改操作的时候，只要满足该规则，自动触发，无需调用。
- 语句级触发器：不包含for each row的触发器
- 行级触发器：包含for each row的就是行级触发器（加for each row是为了使用：old或者：new对象或者一行记录）
| 触发语句 | ：old | ：new |
|---|---|---|
| insert | 所有字段都是空null | 将要插入的数据 |
| update | 更新以前该行的值 | 更新后的值 |
| delete | 删除以前该行的值 | 所有字段都是空null |
语句级触发器
--插入一条记录，输出 加入一条记录
create or replace trigger t1
after
insert
on t_user
declare
begin
dbms_output.put_line('加入一条记录');
end;
--触发t1
insert into t_user (id,name,age) values(5,'jack',25)
加入一条记录
行级触发器
不能减少年龄
create or replace trigger t2
before
update
on T_USER
for each row
declare
begin
if :old.age>:new.age then
raise_application_error(-20001,'不能降低');
end if;
end;
-- -20001~-20999之间
触发触发器
update T_USER set age = age-10 where id=1
结果：
update T_USER set age = age-10 where id=1
> ORA-20001: 不能降低
ORA-06512: at "ROOT.T2", line 5
ORA-04088: error during execution of trigger 'ROOT.T2'
> 时间: 0.019s
案列
触发器实现主键自增[行级触发器]
分析：在用户插入操作之前，拿到即将插入的数据
给该数据中的主键赋值
create or replace trigger t3
before
insert
on T_USER
for each row
declare
begin
select t_seq.nextval into :new.id from dual;
end;
create sequence t_seq
start with 1
increment by 1
nomaxvalue
nominvalue
nocycle
nocache;
--实现主键自增
insert into T_USER (name,age) values('jack',66)
--下面的自己指定id不起作用,还是会触发，会自增
insert into T_USER (id,name,age) values(1,'rrr',888)
