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)