oracle中pl/sql编程---存储过程，函数，触发器，包

1.pl/sql编程
pl/sql 指procedure language 过程化/sql.
pl/sql 是oracle在标准的sql语句基础上扩展的一种对oracle数据库进行编程的的语言。
可以定义变量和常量，而且可以使用条件语句和循环语句。

2.为什么要有pl/sql编程？
平时是通过java对数据操作，但是有个缺点
(1)不能模块化编程，比如完成订单，可能需要发几条sql语句
(2)执行速度慢
(3)安全问题(在程序中会有表名)
(4)浪费带宽
可以通过在数据库里面编写过程，然后用java去调用过程
缺点：不能跨平台

3.创建存储过程基本语法：
create procedure 存储过程名
is 
begin
创建语句
end;
例子：
create procedure emp_test is
begin
insert into emp(empno,ename) values (2343,'小白是土鳖');
end;
/

//执行存储过程：
exec emp_test;

4.存储过程中的参数是先写参数名，再写参数类型
create procedure del_emp(in_empno number )
is
begin
delete from emp where empno=in_empno;
end;
/
//执行存储过程：
exec del_emp(2345);


5.pl/sql 可以做什么？
利用pl/sql 可以开发过程，函数，包(包体)，触发器，他们的基本编程单元是块

6.编写规范：
①注释
单行注释 --
多行注释： /* */
②标识符号的命名规范：
1)定义变量时，建议使用v_作为前缀，如：v_sal
2)定义常量时，建议用c_作为前缀，如：c_rate (constant)
3)定义游标时，建议用_cursor作为后缀，如：emp_cursor
4)定义例外时，建议用e_作为前缀，如：e_error (exception)

7.pl/sql块的介绍：
块(block)是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块。
要完成相对简单的应用功能，可能只需要编写一个块，但是如果要实现比较复杂的功能，可能要在一个pl/sql块中嵌套其他的pl/sql块

8.块的组成部分：
有三部分组成：定义部分，执行部分，例外处理部分。如下所示：
declare
/*定义部分：-----定义常量，变量，游标，例外，复杂数据类型*/
begin
/*执行部分-----要执行的pl/sql语句，和sql语句*/
exception
/*例外处理部分：-----处理运行的各种错误*/
end;

9:dbms_output 是oracle所提供的包(类似java里面的开发包，)
该包包含一些过程，put_line 就是dbms_output包的一个过程
单引号
只有执行过程的块：
set serveroutput on; --设置输出
begin
dbms_output.put_line('hello,world');
end;
/
有定义变量部分和执行过程的块：
declare
--定义变量的格式是：变量名称 变量的类型
v_ename varchar2(16);
begin
select ename into v_ename from emp where empno=&empno;--把查询的ename值放入v_ename
--输出v_ename
dbms_output.put_line('雇员名是'||v_ename); --||是oracle里面的拼接
end;
/
【注意】块执行完后就没有了
将上面的语句改为存储过程：
create procedure emp_pro2(in_empnum number) is
--定义变量的格式是：变量名称 变量的类型
v_ename varchar2(16);
begin
select ename into v_ename from emp where empno=in_empnum;--把查询的ename值放入v_ename
--输出v_ename
dbms_output.put_line('雇员名是'||v_ename); --||是oracle里面的拼接
end;
/

3.实例3包含定义部分，执行部分，和例外处理部分
为了避免pl/sql程序的运行错误，提高pl/sql的健壮性，应该对可能的错误进行处理，这个很有必要：
①比如实例2中，如果输入了不存在的雇员号，应当做例外处理
②有时出现异常，希望用另外的逻辑处理：比如如果不存在，就加入编号为1，名字为“马大哈”这个人

4.案例：包含定义部分，执行部分，例外处理部分
declare
v_ename varchar2(32);
begin
select ename into v_ename from emp where empno=&empno;
--输出用户的名字
dbms_output.put_line('雇员名'||v_ename);
exception
when no_data_found then  --【注意】no_data_found用于select into中
dbms_output.put_line('你输入的编号有误');
end;
/
//异常处理的语法
exception
when 异常的名称 then
//对异常处理的代码
//对异常处理的代码
when 异常的名称 then
//对异常处理的代码
end;
//【可以使用异常处理进行业务逻辑的处理】

4.存储过程的输入变量和输出变量：
create procedure 过程名
(变量名 in 变量类型……，变量名 out 变量类型……)
is
//这里可以定义变量
begin
end;

【注意】如果没有指名变量的in/out,默认为in
in/out 均可以有多个
如：
create procedure pro5
(in_ename in varchar2,in_new_sal in number)--varchar2不用指定长度
is
begin
update emp set sal=in_new_sal where ename=in_ename;
end;
//oracle中查看错误：show error
调用存储过程：exec/call pro5

5.函数：用于返回特定的数据，当建立函数时，在函数头部必须包含return子句，
而在函数体内必须包含return语句返回的数据，可以使用
create function来建立函数。
语法：
create function 函数名(参数1……)
return 数据类型 is
定义变量;
begin
执行语句;
end;

//如：编写一个函数接收用户名，并返回用户的年薪
create function fun1(v_in_ename varchar2)
return number is
--定义变量
v_annual_sal number;
begin
select (sal+nvl(comm,0))*13 into v_annual_sal
from emp where ename=v_in_ename;
return v_annual_sal;
end;
/
//调用函数,跟oracle自定义的函数一样了
select fun1('SMITH') from dual;//直接用虚表

//或者
--直接调用函数
var v_annual_sal number;
call fun1('FORD') into:v_annual_sal;
print v_annual_sal;

6.oracle中的包(package)：主要用来管理过程，函数，异常，游标等
创建语法：
create package 包名 is
--声明函数，过程等，只声明不实现
function 函数名(变量名 变量类型……) return 返回类型;
procedure 过程名(变量名 变量类型……);
end;
//举例：
create package mypackage1 is
--声明一个过程
procedure pro1(v_in_ename varchar2,v_in_newsal number);
--声明一个函数
function fun1(v_in_ename varchar2) return number;
end;

【包声明后，需要去实现才能使用，如下】
create package body mypackage1 is  --实现声明的包体
--实现过程
procedure pro1(v_in_ename varchar2,v_in_newsal number) is
begin 
update emp set sal=v_in_newsal where ename=v_in_ename;
end;
--实现函数
function fun1(v_in_ename varchar2) return number is
v_annual_sal number;
begin
select (sal+nvl(comm,0))*13 into v_annual_sal from emp 
where ename=v_in_ename;
return v_in_ename;
end;
end;

//调用包体
exec mypackage1.pro1('SMITH',3000);
【细节：】
①包体中药实现的方法或者过程，需要在包中声明
②在调用包中的某个方法/过程的时候，在过程和函数前需要带有包名，
如果需要调用其他方案的包，还需要在包前面加上方案名
exec 方案名.包名.过程名(参数值……);
call 方案名.包名.函数名(参数值……);

【注意】在java中调用包中的过程或者方法，跟直接调用差不多，
只需要加上：方案名.包名

7.触发器
触发器是一个隐含执行的过程，它不是有程序或者DBA来显示调用，而是因为某个操作触发执行的。

8.PL/sql详解：
编写pl/sql程序时，可以定义变量和常量，在pl/sql程序中，包括有：
①标量类型(scalar) ②复合类型(composite) ③参照类型(reference) ④lob(large object)
①标量类型(scalar)
语法：
identifier [constant] datatype [not null][:=|defalut expr] //赋初值用:=
identifier:名称
constant：指定常量，需要指定它的初始值，且值是不可改变的
datatype:数据类型
not null:指定变量值不能为null
:= 给变量或者常量指定初始值
default:用于指定初始值
expr：指定初始值的pl/sql表达式，可以是文本值，其他变量，函数等
举例：输入人员编号，显示出该雇员的姓名，工资，个人所得税(税率是0.03)
--创建一个存储过程
create or replace procedure pro1(v_in_empno number) is
--定义变量
v_tax_rate number(3,2):=0.03;
v_sal number;
v_ename varchar2(32);
v_tax number;
begin
select ename,sal into v_ename,v_sal from emp
where empno=v_in_empno;
--计算个人所得税
v_tax:=v_sal*v_tax_rate ;
dbms_output.put_line(v_ename||'工资是='||v_sal||'个人所得税是：'||v_tax);
end;
【注意：】标量使用%type类型
对于上面的pl/sql块有个问题，如果员工的姓名超过5个字符的话，就会有错误，
为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。
用法：标识符名 表名.列名%type;
如：v_name emp.ename%type;
【注意】上面的意思是定义变量的时候跟表的类型自适应


②复合类型(composite)：用于存放多个值得变量，常用的包括：
(1)pl/sql记录
类似于高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时，必须要加
记录变量作为前缀(记录变量.记录成员)，语法如下：
type 自定义的pl/sql记录名 is record(
变量名 变量类型,
变量名 变量类型
);
//使用自定义的pl/sql记录
变量名 自定义的pl/sql记录名
【案例】编写一个过程，该过程可以接收一个用户编号，并显示该用户的名字，薪水，工作岗位（注意：使用pl/sql记录实现）
create or replace procedure pro1(v_in_empno in number)
is
--定义一个记录数据类型
type zy_emp_record is record(
v_ename emp.ename%type;
v_sal emp.sal%type;
v_job emp.job%type;
);
--定义一个变量，该变量的类型是zy_emp_record
v_emp_record zy_emp_record;
begin 
select ename,sal,job into v_emp_record
from emp where empno=v_in_empno;
dbms_output.put_line('名字:'||v_emp_record.v_ename||'工资：'||v_emp_record.v_sal);
end;

(2)pl/sql表【了解】
③参照类型(reference)

④lob(large object)

未完待续……