oracle（3）pl/sql编程基础 存储过程、函数、包、触发器

PLSQL编程（1）
1、过程、函数、触发器是pl/sql编写
2、过程、函数触发器是在oracle中
3、pl/sql是非常强大的数据库过程语言
4、过程，函数可以在java程序中调用

PL/SQL
优点：
a 提高应用程序的运行性能
b 模块化的设计思想[分页的过程、订单的过程、转账的过程...]
c 减少网络传输量
d 提高安全性（例如：直接在java里写密码）

缺点：
移植性不好（换数据库 就要重写过程）

案例1；
1 创建一个表
create table mytest(name varchar2(30),passwd varchar2(30));

2 创建过程
create or replace procedure sp_pro1 is
begin
--执行部分
insert into mytest values('kyle','123456');
end;

replace :表示如果有sp_pro1 就替换

如何查看错误信息
show error

3如何调用该过程（两种方法）
a exec 过程名（参数值1，参数值2...）;
b call 过程名（参数值1，参数值2...）;

没有参数值，就直接是 exec 过程名 /call 过程名

案例2；
create or replace procedure sp_pro2 is
begin
delete from mytest where name='kyle';
end;

C.编写规范
a 注释
单行注释 --

select * from emp where empno=7788;--取得员工信息
多行注释
/*....*/来划分
b 标识符号的命名规范
1）当定义变量时，建议用v_做为前缀v_sal
1）当定义常量时，建议用c_做为前缀c_rate
1）当定义游标时，建议用_cursor做为前缀emp_cursor
1）当定义例外时，建议用e_做为前缀e_error

D.块 介绍
块(block)是pl/sql的基础程序单元，编写pl/sql程序实际上就是编写pl/sql块。
要完成相对简单的应用功能，可能只需要编写一个pl/sql块；但是如果要想实现
复杂的功能，可能需要在一个pl/sql块中嵌套起它的pl/sql块。

E.块的结构示意图
pl/sql块由三个部分构成：定义部分、执行部分、例外处理部分。
如下所示：
declear
/*定义部分--定义常量、变量、游标、例外、复杂数据类型*/
begin
/*执行部分--要执行的pl/sql语句和sql语句*/
exception
/*例外处理部分--处理运行的各种错误*/
end;

特别说明：定义部分是从declare开始的，该部分是可选的
执行部分是从begin开始的，该部分是必须的
例外处理部分是从exception开始的，该部分是可选的

案例2
--最简单的块
begin
dbms_output.put_line('hello,world');
end;

set serveroutput on --打开输出选项
set serveroutput off --关闭输出选项

案例3
--有定义和执行部分的块
declare
--定义变量
v_ename varchar2(5);
v_sal number(7,2);
begin
--执行部分
select ename,sal into v_ename,v_sal from emp where empno=&aa;
--在控制台显示用户名
dbms_output.put_line('用户名是：'||v_ename||'工资：'||v_sal);
--异常处理
exception
when no_data_found then
dbms_output.put_line('朋友，你的编号输入有误！');
end;

*相关说明：
&表示要接受从控制台输入的变量

F.简单分类
                             |----过程（存储过程）
                             |
                             |----函数
 块（编程）  -----|
                             |----触发器
                             |
                             |----包

过程：

案例4
create procedure sp_pro3(spName varchar2,newSal number) is
begin
--执行部分，根据用户名去修改工资
update emp set sal=newSal where ename=spName;
end;

exec sp_pro3('SCOTT',4678);

如何在java程序中调用一个存储过程

public class TestOraclePro{
   public static void main(String []args){
 try{
            //1.加载驱动
     Class.forName("oracle.jdbc.driver.OracleDriver");
     //2.得到连接
     Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:hs","system","123456");
     //3.创建CallableStatement
     CallableStatement cs = ct.prepareCall("{call sp_pro3(?,?)}");
     //4.赋值
     cs.setString(1,"kyle");
     cs.setInt(2,10);
     //执行
     cs.execute();
     //关闭
     cs.close();
     ct.close();
 }catch(Exception e){
     e.printStackTrace();
 }

   }
}

函数：函数 用于返回特定的数据，当建立函数时，在函数头部必须包含return字句
，而在函数体内必须包含return语句返回的数据

--函数案例
--输入雇员的姓名，返回该雇员的年薪
create function sp_fun2(spName varchar2)
return number is yearSal number(7,2);
begin
--执行部分
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;

在sqlplus中调用函数
sql> var abc number
sql> call sp_fun2('SCOTT') into:abc；
sql> print abc;

在java程序中调用该函数
select annual.income("SCOTT") from dual;
可以通过rs.getInt(1)得到返回的结果

 

包 
包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。
a.可以使用create package 命令来创建包：
实例：
--创建包
--创建一个包sp_package
--声明该包有一个过程update_sal
--声明该包有一个函数annual_income
create package sp_package is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end;

包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。
包体用于实现包规范中的过程和函数，
b.建立包体可以使用create package body 命令
--给包sp_package 实现包体
create or replace package body sp_package is
procedure update_sal(name varchar2,newsal number);
is
begin
update emp set sal=newsal where ename=name;
end;
function annual_income(name varchar2)
return number is
annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from emp
where ename=name;
return annual_salary;
end;
end;

c.如何调用包的过程或是函数
当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要
访问其他方案的包，还需要在包名前加方案名。
sql>call sp_package.update_sal('SCOTT',1500);

触发器
触发器是指隐含的执行的存储过程。当定义触发器时，必须要指定除非的事件和触发的操作，
常用的触发事件包括insert,update,delete语句，而触发操作实际上就是一个pl/sql
块。可以使用create trigger 来建立触发器。