储存过程/存储函数 --procedure#
什么是存储过程:#
存储过程就是一段已经封装好了的 plsql代码片段, 这个代码片段提前已经编译好了,用户在调用的时候就不用再编译大大提高了 数据库的吞吐效率
存储过程不编译是不能被调用的!!!
语法:#
create or replace procedure 存储过程的名(参数1 in|out 参数类型,.......) is | as -- 声明部分 begin -- 业务逻辑 end;
in 表示: 该参数是输入参数
out 表示: 该参数是输出参数
例子: 使用存储过程给员工涨工资,
create or replace procedure pro_update_sal(vempno in number , vsal in number) is -- 声明部分 -- 存储涨工资之前的 工资 v_sal number ; begin -- 存储涨工资之前的工资 select sal into v_sal from emp where EMPNO=vempno; -- 涨工资 update emp set SAL=v_sal+vsal where EMPNO=vempno; -- 提交事务, 我们的存储过程里面的事务通常都是在里面直接提交的 commit; exception when others then rollback ; end;
一般存储过程中加上 事务的提交,和出现异常时回滚
如何调用
-- 调用方式一: call pro_update_sal(7369,100); -- 调用方式二: declare begin pro_update_sal(7369,100); end; -- 方式三: begin pro_update_sal(7369,100); end;
带返回值的存储过程#
create or replace procedure get_pro (v_no in number , total_sal out number) is begin select SAL*12+ nvl(comm,0) into total_sal from EMP where EMPNO = v_no; commit; exception when others then rollback ; end;
不用显示的声明,声明返回值,把查询的结果赋值给 入参位置的out类型的参数就行,她自动的返回
调用
declare t_sal number; begin get_pro(7369,t_sal); dbms_output.put_line(t_sal); end;
传递进去两个参数,不需要我们写
t_sal = get_pro(7369,t_sal)(错误的写法) 她会自动的把数据传递进t_sal ,
什么是存储函数#
同样是一段封装在oracle 服务器中的一段plsql 代码片段, 他是已经编译好了的代码片段
语法:
create [or replace] function 存储函数的名称 (参数名 in|out 参数类型,参数名 in|out 参数类型.参数名 in|out 参数类型) return 参数类型 is | as begin end;
例: 使用存储函数 查询指定员工的年薪
create or replace function fun_get_empsal(v_empno number) return number is -- 声明变量 保存年薪 total_sal number; begin select sal*12 + nvl(comm,0) into total_sal from EMP where EMPNO=v_empno; -- nvl(p1,p2) 第一个字段为空,那么就取第二个字段的值 return total_sal; end;
调用
declare v_cal number; begin v_cal := fun_get_empsal(number); dbms_output.put_line(v_cal); end;
标准sql调用 存储函数
select ename, fun_get_empsal(EMPNO) from EMP;
存储过程和存储函数的区别#
- 其实他们本质上是没有区别的,两者都可以实现相互的功能
- 语法上的区别
3. 存储函数参数的小括号后面紧跟着 return语句
4. 关键字 一个数 procedure 一个是 function - 存储函数大多数情况下是给存储过程调用的
- 存储函数可以给标准的sql语句调用,但是存储过程不行
触发器#
什么是触发器 --trigger#
Oracle 和 Mysql 中都有触发器,
他是一段代码,用来监听用户对数据表的具体某个操作,可以在这个操作前后进行拦截,执行我们添加进去的操作
分类#
- 语句级触发器(表级别)
操作无论影响多好行, 触发器执行一次
- 行级触发器 -- 关键字 for each row
操作影响多少行,触发多少次
简单的触发器 语法#
create or replace trigger -- 创建触发器 before | after -- 指定触发的时机 insert | delete | update -- 监听的操作 on 表名 -- 针对哪张表 [for each row] 时候针对每一行 declare begin end;
例: 插入员工之后,输出一句话 XXX -- 每次有inset语句, 都会先执行我们的 输出XXX
create or replace trigger trrigger_one after insert on EMP declare begin dbms_output.put_line('XXX'); end;
- 行级触发器: for each row
行级触发器有两个内置的对象
:new 记录了表中某个字段更新前的值
:old 记录表中的某个字段更新后的值
在 on 表名 和 declare 之间添加 for each row , 使触发器升级成 行级触发器
触发器的练习--- 模拟mysql的 id 自增长
思路: 触发器监听用户对表的insert操作,每当监听到有插入操作的时候,就会使用我们的 序列自增1,给它补全
-- 创建表 create table person( id number primary key , name varchar2(10) ); -- 创建序列--并编译执行 create sequence id_auto_increament; -- 创建监听对person表 的insert操作的 触发器 -- 并编译执行 create or replace trigger trigger_insert before insert on person for each row declare begin select id_auto_increament.nextval into :NEW.id from dual; end; 插入数据 insert into person values(null,'长沙'); -- 成功插入
注意点: :NEW这个内置对象只属于行级触发器
-- create 关键字 :
-- 创建表
-- 创建view
-- 创建index
-- 创建synonym
-- 创建存储过程
-- 创建用户
-- 创建触发器
-- 创建序列
