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

时间:2021-09-30 23:17:19

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 来建立触发器。