记录--PLSQL知识点整理

–基本语句

declare
begin
exception
	'
	 zero_divide:除零异常
	 value_error:类型转换异常
	 too_many_rows:查询出多行数据，赋值给一行变量类型rowtype
	 no_data_found:没找到数据
	 sqlerrm:查看异常信息
	 抛出系统异常：raise_application_error(-20001,'异常信息');
	'
	when 异常1 then
	...
	when 异常2 then
	...
	when others then
	...
end;

–将查询出的单个结果赋值给变量

declare
	变量名 表名.列名%type;--%type是取表中某列的数据类型
begin
	select sal into 变量名 from 表名 where 条件;
end;

–将查询出的一条记录赋值给变量

declare
	vrow 表名%rowtype;--%rowtype是取表中一条记录的数据类型
begin
	select * into vrow from 表名 where 条件;
	dbms_output.put_line('姓名' || vrow.name);--||连接字符串,姓名字符必须用单引号括起来
end;

–输出语句到控制台Output

dbms_output.put_line();

–条件判断

if then ...
elseif then ...
else ...
end if;

–while循环

while 条件 loop
	i:=i+1;--条件中变量需要在declare中声明
end loop;

–for循环

for 变量名 in [reverse] 开始值..结束值 loop --reverse,开始值和结束值倒序
	循环体--变量名不需要声明，for循环自动声明
end loop;

–loop循环

loop 
	exit when 条件;--条件中变量需要在declare中声明
end loop;

–游标

--游标的作用是先查询出数据，保存到游标中，当数据量较大时，可以减少查询数据库，提高查询效率；游标中可以存储多行数据，查询语句的结果存入变量只能存储一行数据
declare
	cursor 游标名(形式参数 参数类型) is 查询语句;--声明游标,查询语句中可以使用形式参数作为值赋给数据库表列
	变量名 类型;--声明变量
begin
	open 游标名(实际参数);
	loop 
		fetch 游标名 into 变量名;--从游标中获取一行数据
		exit when 变量名%notfound;--当游标获取数据时找不到数据就退出，能找到是变量名%found
	end loop;
	close 游标名;
end;

–系统引用游标

declare
	游标名 sys_refcursor;--声明系统游标
begin
	open 游标名 for 查询语句;
	fetch 游标名 into 变量名;
	close 游标名;
end;

–for循环使用游标

declare
	cursor 游标名 is 查询语句;--游标可以存放多条数据，
begin
	for vrow in 游标名 loop  --for循环中会自动声明变量，且自动打开和关闭游标
		dbms_output.put_line();
	end loop;
end;

–自定义异常

declare
	异常名 exception;--声明异常
begin
	raise 异常名;--抛出异常
exception
	when 异常名 then --捕获异常
		...
end;

–创建存储过程

--存储过程是封装在服务器的一段代码，可以反复使用，存储过程没有返回值，存储函数有返回值
create or replace procedure 存储过程名(参数1 in|out 参数类型1,参数2 in|out 参数类型2...)
is|as
 --声明变量
begin
 --业务逻辑
end;

–调用存储过程

--第一种方式，直接调用
call 存储过程名();
--第二种方式，代码块中调用
declare
begin
	存储过程名();
end;

–创建存储函数

--存储函数是封装在oracle服务器的一段代码，可以反复使用，一般来说，存储函数是用来被存储过程调用的,存储函数可以在查询语句中当作列名被调用
create or replace function 存储函数名(参数1 in|out 参数类型1,参数2 in|out 参数类型2...) return 参数类型
is|as
 --声明变量
begin
 --业务逻辑
 return 变量名;--需要返回值出来
end;

–调用存储函数

--1.在代码块中调用存储函数，并赋值给变量
declare
begin
	变量名 := 存储函数名();
end;
--2.在查询语句中调用存储函数
select 列名1，存储函数名() from 表名;

–触发器

--可以在某表进行增删改操作之前或之后执行一段代码逻辑，如果执行逻辑后不想执行增删改操作，可以在代码块中抛出系统异常（raise_application_error(-20001,'异常信息');）
create [or replace] triger 触发器名 
begin | after
insert | update | delete
on 表名
--:old.sal表示行级触发器更新前的值，:new.name表示行级触发器更新后的值
[for each row]  --添加这行表示行级触发器，会在增删改操作的每一行都执行逻辑，不添加表示语句级触发器，无论增删改操作了多少行，都执行一次逻辑
declare
begin
end;

–触发器模拟自动增长

create sequence seq;
create triger tri_person 
before 
insert
on person
for each row
declare
begin
	select seq.nextval into :new.pid from dual;
end;
insert into person valuse(null,'姓名');