[数据库汇总]-- 数据库存储过程

存储过程:
1、定义：存储过程是数据库的编程语言。
2、存储过程由于厂商的不同，会有不同的过程，
其中，oracle 的存储过程叫做pl/sql
 sqlserver 的存储过程叫做 tsql
3、存储过程的学习：
set serveroutput on  在服务器端开启打印。
1、存储过程的基本语法结构，主要包含以下几个大块
匿名块
declare
...  主要用来声明变量，其中，declare块可以省略,
注意：declare里面不允许出现单独的赋值语句
begin
...  整个核心功能，通常用来调用函数或者执行sql
exception
...  处理异常，其中，exception块可以省略。
end;
数据类型
number:表示正数，浮点数
integer:整数，如果是小数，就会四舍五入
binary_integer:pl/sql特有的数据类型，保存的是二进制数据。

varchar2：可变长度的字符串，必须指定字符串长度。
long：可变长度的字符串，可以不指定长度。
char：不可变的字符串，可以不指定长度，不指定有默认的长度。

date:日期类型
date类型可以调用to_date()方法指定日期格式,例如to_date('2012-12-12','yyyy-MM-dd')
boolean：布尔类型  取值为true|false，不可以用来输入

复合类型：
record:记录，相当于java中的entity
使用方式： 
TYPE  recode_name IS RECORD{
v_name varchar2(20),
v_age integer,
}
table:表,类似于java中的数组，通过下标进行操作
使用方式：TYPE stu_table IS TABLE OF varchar2(20) index by binary_integer; 
其中，varchar2(20)表示表里面存的数据的类型， binary_integer表示下标
2、dml语言和dql语言
dml语言：data modify language  对应的是insert，update和delete操作
dql语言：data query language  对应的是select语句，只针对单行数据的查询。

%type的使用：在执行dql语句时，可以在declare块中声明变量的时候使用%type来指定变量类型，
declare
v_name t_user.name%type    ----声明v_name的变量类型和t_user表中name列属性的类型相同
begin  ... end;
%rowtype:在执行dql语句时，可以在declare块中声明变量的时候使用%rowtype来指定变量类型，
declare
v_user t_user%rowtype    ----声明t_user的类型和t_user表中每一行的类型相同（）
begin  ... end;
3、流程控制
-------------if  else-----------
1、 if  判断语句 then 
xxx
end if;
2、 if 判断语句 then 
xxx
else
xxxx
end if;
3、 if  判断语句 then
xxx
elsif 判断语句 then
xxx
end if;

-----------case ... when ...then -------
case  xxx
when xx  then
xxx
when xx  then
xxx
else
xxx
end case;

--------loop---------

注意:loop默认是死循环，所以要加退出条件
loop
exit  when xxx
end loop;

-------------while----------------
while 判断条件  loop
xxx
end loop;

------for---------
for  变量名  in 1..3 loop
xxx
end loop;

4、游标：cursor,用来保存多条查询结果，类似于jdbc里面的resultSet。注意：一个游标只能开启一次
1、游标的声明：
declare
v_row t_user%rowtype  
cursor curfir is select * from t_user;
2、游标的使用：
fetch curfir into v_row;  取出当前游标的下一条数据，保存到v_row中
3、游标的几个属性：
cursor_name%FOUND:布尔属性，最近一次fetch操作成功返回true，否则返回false
cursor_name%NOTFOUND:与%FOUND相反
cursor_name%ISOPEN:布尔属性，确定游标是否已经开启
cursor_name%ROWCOUNT:数字属性，返回游标已经读取记录数。
4、基本流程：
declare
cursor c is select * from t_user;
v_row t_user%rowtype;
begin
open c ---- (开启游标)
fetch c into v_row  ---(从游标中拿到一条数据保存到v_row中)
while c%found  loop
xxx
end loop;
close c;
end ;
5、基本流程2
declare
cursor c is select * from t_user;
v_row t_user%Rowtype;
begin
if c%isopen then
 dbms_output.put_line('open');
else
 dbms_output.put_line('close');
 open c;
end if;
fetch c into v_row;
while c%Found loop
  dbms_output.put_line(v_row.id);
  fetch c into v_row;
end loop;
  end;
5、异常：exception
1、预定义异常：对于pl/sql里面预定义的异常，可以直接拿过来使用。
2、自定义异常
1、在declare中声明异常
declare 
v_exception exception;
2、在代码块中抛出：
begin
raise  v_exception
end;
3、处理异常：
exception
when  v_exception  then
dbms_output.put_line(sqlcode ||'---'||sqlerrm);   
3、异常码：
sqlcode：唯一用来标识一个异常
sqlerrm：用来描述异常信息
4、显示错误信息命令：show error！
存储过程：procedure
in：表示输入参数
out：表述输出参数。输出参数在第一次使用的时候，会被格式化。所以在调用的时候，遇到输出参数，必须用变量
in out:既可以当输入参数，也可以当输出参数。在调用的时候，必须用变量代替。
1、定义一个存储过程：
create or replace procedure test_p3(v_id in integer, v_name out varchar2, v_password out varchar2) as v_id1 integer := v_id;
v_name1 varchar2(20) := v_name;
v_password1 varchar2(20) := v_password;
v_excep1 exception;
begin
 if v_id1 > 4 then
raise v_excep1;
 else
select name, password into v_name, v_password from t_user where id = 1;
dbms_output.put_line('select ok!');
 end if;
exception
 when v_excep1 then
dbms_output.put_line('error');
end;
2、调用存储过程：
declare
 v_name     varchar2(20);
 v_password varchar2(20);
begin
 test_p3(5, v_name, v_password);
 if v_name is null then
dbms_output.put_line(v_name || ' ----- ' || v_password);
 else
dbms_output.put_line('没有值！');
 end if;
end;
函数：function
1、查询当前用户下面所有的function： select object_name from user_objects where object_type = 'FUNCTION';
2、查询当前用户下面所有的procedure: select object_name from user_objects where object_type = 'PROCEDURE';
3、注意：oracle里面的函数必须有返回值，也就是说在定义的时候必须有return 语句，否则报错。
4、函数的使用
1、定义：
create or replace function functioin_name [(dateType date,...)] return dataType
as
xxx
begin
xxx
end
2、调用
1、匿名块调用
begin
dbms_output.put_line(test_fun1);
end;
2、用call调用
call dbms_output.put_line(test_fun1);
3、通过sql调用
select test_fun1 from dual;

包：package，类似于java中的接口
定义一个package,类似于java中的interface
create or replace package test_package1
as 
procedure pro1;
function fun1 return integer;
end test_package1;
实现一个package，
create or replace package body test_package1
as
 v_a1 integer:= 12;
 procedure pro1
as 
v_pro integer:=119;
begin
 dbms_output.put_line('hello'||v_pro||'---'||v_a1);
end;
 function fun1 return integer
as
v_fun varchar2(20):='world';
begin
 dbms_output.put_line('-----'||v_fun);
 return 4;
end; 
end test_package1;
调用一个package
declare
v_num integer:= 0;
begin
 test_package1.pro1;
 v_num := test_package1.fun1;
 dbms_output.put_line(v_num);
end;
触发器：trriger,类似于java中监听器，当某一个动作发生则会被触发，进而运行相应的动作。触发器分为行级触发器和表级触发器两种，
触发器发生的时机有两种，分别在动作之前和动作之后。当在数据库中有insert，update或者delete操作的时候，会触发相应的事件。
表级触发器：
创建表级触发器：
create or replace trigger test_tr1 before update on t_user
begin
dbms_output.put_line('');
end;
测试：
begin
update set xxx;
commit;
commit;