【Oracle】PL/SQL语法、存储过程，触发器

一、Oracle数据类型

	Orcle数据类型	说明	类比MySQL数据类型
字符型	CHAR	固定长度的字符类型	CHAR
字符型	VARCHAR2	可变长度的字符类型	VARCHAR
字符型	LONG	大文本类型，最大2G
数值型	NUMBER	数值类型，整数小数都可以，number(5)表示长度5的整数，number(5,2)表示共5位，含2位小数	INT存整数，FLOAT、DOUBLE存小数
日期型	DATE	日期时间型，精确到秒
日期型	TIMESTAMP	精确到秒的小数点后9位
二进制型	CLOB	存储字符，最大4G（比LONG更多）	LONGTEXT
二进制型	BLOB	存储图像、声音、视频等数据，最大 4G	LONGBLOB

二、PL/SQL

PL/SQL（Procedure Language/SQL）是Oracle对SQL语言的过程化扩展，指在SQL命令语言中增加了过程处理语句（分支、循环等）。
基本语法结构

[declare
  -- 声明变量
]
begin
  --代码逻辑
[exception
  --异常处理
]
end;

2.1 变量

变量声明语法：
变量名 类型(长度)
变量赋值语法：
变量名:=变量值
select into赋值语法（结果必须是一条记录，多条记录和没有记录都会报错）：
select 列名 into 变量名 from 表名 where 条件

2.2 属性类型

引用型语法，某个变量的类型不预先指定，而是与查询结果相同
表名.列名%type
举例：

declare 
  v_price number(10,2);
  v_usenum2 number(10,2);
  v_usenum t_account.usenum%type;--变量的类型与t_account.num0相同
  v_num0 t_account.num1%type;
begin
  v_price:=2.45;
  select usenum,num0 into v_usenum,v_num0 from t_account -- 把usenum赋给v_usenum,num0赋给v_num0
  where year='2012' and month='01' and owneruuid=1;
  v_usenum2:=round(v_usenum/1000,2);
end;

记录型语法，某个变量表示一行
表名%rowtype
举例：

declare 
  v_price number(10,2);
  v_usenum2 number(10,2);
  v_account t_account%rowtype;--表示一行记录
begin
  v_price:=2.45;
  select * into v_account from t_account 
  where year='2012' and month='01' and owneruuid=1;--把查出来的一行给v_account
  v_usenum2:=round(v_account.usenum/1000,2);--v_account是一行记录，用.列名使用对应列的值
end;

2.3 异常

oracle中有如下两个异常：
NO_DATA_FOUND：执行select into，未返回行
TOO_MANY_ROWS：执行select into，结果集超过一行
举例：

declare 
  v_price number(10,2);
  v_usenum2 number(10,2);
  v_account t_account%rowtype;--表示一行记录
begin
  v_price:=2.45;
  select * into v_account from t_account 
  where year='2012' and month='01' and owneruuid=1;--把查出来的一行给v_account
  v_usenum2:=round(v_account.usenum/1000,2);--v_account是一行记录，用.列名使用对应列的值
exception
  when NO_DATA_FOUND then 
  	DBMS_OUTPUT.putline('select into 未返回数据'); -- 这句话是打印输出，类似python中print()
  when TOO_MANY_ROWS then
  	DBMS_OUTPUT.putline('select into 返回多行数据');
end;

2.4 条件判断

基本语法1：
if 条件 then 业务逻辑 end if;
基本语法2：
if 条件 then 业务逻辑 else 业务逻辑 end if;
基本语法3：
if 条件 then 业务逻辑 elsif 条件 then 业务逻辑 else 业务逻辑 end if;

2.5 循环

无条件循环语法（重点）：
loop 循环语句 end loop;
举例：

declare
  v_num number;
begin
  v_num:=1;
  loop
   v_num:=v_num+1;
   exit when v_num>100;--loop循环中使用exit退出循环
   --也可以写成：if v_num>100 then exit;end if;
  end loop;
end;

有条件循环语法：
while 条件 loop 循环语句 end loop;
举例：

declare
  v_num number;
begin
  v_num:=1;
  while v_num<=100
  loop
    v_num:=v_num+1;
  end loop;
end;

for循环语法（重点）：
for 变量 in 起始值..终止值 loop 循环语句 end loop;
举例：

begin
  for v_num in 1..100  --for循环v_num自动声明，不用声明，是局部变量，只能在loop和end loop中间使用
  loop
    DBMS_OUTPUT.putline(v_num);
  end loop;
end;

2.6 游标

游标存放SQL语句的执行结果，可以理解成结果集，可以对其进行逐行处理。

声明游标语法：
cursor 游标名称 is SQL语句;
使用游标语法：

open 游标名称
loop
  fetch 游标名称 into 变量
  exit when 游标名称%notfound
end loop;
close 游标名称

普通游标使用举例：

declare
  cursor cur_pricetable is select * from t_pricetable where owertypeid = 100;--声明游标
  v_pricetable t_pricetable%rowtype;
begin
  open cur_pricetable;--打开游标
  	loop
  		fetch cur_pricetable into v_pricetable;--提取游标
  		exit when cur_pricetable%notfound;--退出循环游标
  		DBMS_OUTPUT.putline(v_pricetable.price);--打印每一条记录的price
  	end loop;
  close cur_pricetable;--关闭游标
end;

带参数游标使用举例：

declare
  cursor cur_pricetable(v_ownertype number) is select * from t_pricetable where owertypeid = v_ownertype;--声明带参数的游标
  v_pricetable t_pricetable%rowtype;
begin
  open cur_pricetable(100);--打开游标，传入参数100
  	loop
  		fetch cur_pricetable into v_pricetable;--提取游标
  		exit when cur_pricetable%notfound;--退出循环游标
  		DBMS_OUTPUT.putline(v_pricetable.price);--打印每一条记录的price
  	end loop;
  close cur_pricetable;--关闭游标
end;

for循环使用游标举例：
自动打开、关闭游标，不用声明变量，也不用fetch，可以直接使用

declare
  cursor cur_pricetable(v_ownertype number) is select * from t_pricetable where owertypeid = v_ownertype;--声明带参数的游标
begin
  for v_pricetable in cur_pricetable(100) --for循环使用游标，变量不需要声明
  loop
  	DBMS_OUTPUT.putline(v_pricetable.price);--打印每一条记录的price
  end loop;
end;

三、存储函数

存储函数也称为自定义函数，接受一个或多个参数，返回一个结果。
函数中使用PL/SQL进行逻辑处理

存储函数创建语法：
语法is的后面与PL/SQL语法的declare后面是一样的

create [or replace] function 函数名称
(参数名称 参数类型,参数名称 参数类型, ...)  -- 这里只写参数类型，不写长度
return 结果变量数据类型  -- 指定返回值的参数类型，不写长度
is 
  -- 声明变量
begin
  -- 代码逻辑
  return 结果变量;
[exception
  -- 异常处理
]
end;

举例：

create or replace function fn_getaddress
(v_id number)    -- 函数的参数是number类型
return varchar2  -- 函数的返回值是varchar2类型
is 
	v_name varchar2(30);
begin
    --根据传入的v_id查询name，并返回name
	select name into v_name from t_address where id=v_id;
	return v_name;
end;

-- 调用函数查询id=3的地址
select fn_getaddress(3) from dual;
-- 调用函数查询addressid对应的地址，不用再进行表关联
select id,name,fn_getaddress(addressid) from t_owners;

四、存储过程

存储函数和存储过程的区别：

1. 存储函数只能返回一个值，存储过程可以传出多个值（返回多个值）
2. 存储函数可以直接在select语句中使用，而存储过程不能
3. 存储函数一般封装一个查询结果，存储过程一般封装一段事务代码

存储过程创建语法：
相比存储函数，把function换成了procedure，且没有了返回值

create [or replace] procedure 存储过程名称
(参数名称 参数类型,参数名称 参数类型, ...)  -- 这里只写参数类型，不写长度，参数可以传入，也可以传出
is
	-- 声明变量
begin
	-- 代码逻辑
[exception
  -- 异常处理
]
end;

过程参数的三种模式：
IN 传入参数（默认）
OUT 传出参数，主要用于返回程序运行结果
IN OUT 传入传出参数

不带传出参数的存储过程举例：

create or replace procedure pro_students_add
(
v_id number,
v_name varchar2
)
is 
begin 
	insert into t_students values(v_id,v_name);
	commit;
end;

-- 调用存储过程
call pro_students_add(10,"啦啦啦");

带传出参数的存储过程举例：

create or replace procedure pro_students_add
(
v_id number,
v_name varchar2,
v_stuid out number --声明一个传出参数
)
is 
begin 
	insert into t_students values(v_id,v_name);
	commit;
	-- 对传出参数赋值
	select id into v_stuid from t_students where id = v_id;
end;

-- 调用传出参数的存储过程
declare
	v_stuid number; -- 声明一个变量，用来接收存储过程的传出参数
begin
	pro_students_add(10,"啦啦啦",v_stuid); -- 执行完该语句后，v_stuid就有值了，后面可以直接用
	DBMS_OUTPUT.putline(v_stuid);
end;

五、触发器

触发器是基于某一张表的增删改操作的，在对应的增删改操作执行之前/之后，执行一段PL/SQL代码
触发器分类：
前置触发器，在对应语句之前执行
后置触发器，在对应语句之后执行
行级触发器，每操作一条记录就执行一次触发器（一般都是行级触发器）
语句级触发器，不管操作多少条记录，一个SQL语句只对应执行一次触发器
创建触发器语法：

create [or replace] trigger 触发器名
	before|after
	[delete][[or] insert][[or] update [of 列名]]
	on 表名
	[for each row][when(条件)] -- for each row表名该触发器是行级触发器
declare
begin
	PL/SQL块
end;

触发器中:old和:new所代表的值：

触发语句	:old	:new
Insert	所有字段都是空(null)	将要插入的数据
update	更新前该行的值	更新后该行的值
delete	删除以前该行的值	所有字段都是空(null)

后置触发器举例：

-- 创建日志表，记录业务名称修改前和修改后的值
create table t_owners_log(
updatetime date,
ownerid number,
oldname varchar2(30),
newname varchar2(30)
);

create or replace trigger tri_owners_log
after
update of name  
on t_owners     -- 当t_owners.name被update之后触发该触发器
for each row    -- 行级触发器
declare

begin
	insert into t_owners_log values(sysdate,:new.id,:old.name,:new.name);\
	-- 注意触发器里不用commit，会自动commit，如果是存储过程则需要commit
end;