oracle触发器、函数、存储过程简介
oracle是我们生产环境使用最频繁的数据库之一，因此将之前日常使用的相关操作记录下，备忘和方便使用。
1、触发器
触发器一般用来处理数据在插入、更新或删除时的一些额外操作，比如针对交换的前置表，有数据更新时修改标记位或时间戳。
触发器要慎用，因为对于数据的读写效率有不小的影响。
create or replace trigger trg_table1_insert_update
before insert or update on TABLE1
for each row
declare
-- local variables here
begin
:new.COLUMN1 := sysdate;
if :new.COLUMN2!='1' and :new.COLUMN2!='2' then
:new.COLUMN2 := '0';
end if;
-- 当判断是更新操作时<!-- -->
if updating then
:new.COLUMN3:='XX';
end if;
end trg_table1_insert_update;
2、函数
oracle内置常用函数
内置函数一般用来简化查询、插入等操作，但是会对sql的效率有影响。
时间处理
-- to_char
select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss') from dual;
-- to_date
select to_date('2019-08-27 10:11:33','yyyy-MM-dd hh24:mi:ss') from dual;
字符截取
select substr('asdf',0,2) from dual;
select substr('asdf',1,2) from dual;
去除空格
select trim('as df') from dual;
长度
length字符长度，lengthb字节长度，lengthb与数据库的字符集有关系，如ZHS16GBK采用 2 个byte位来定义一个汉字。而在UTF8，采用 3 个byte。
--字符长度 length
select length('李四') from dual;
--字节长度 lengthb
select lengthb('李四') from dual;
like优化的效率很低 应使用
instr(title,’手册’)>0 相当于like
instr(title,’手册’)=0 相当于not like
自定义函数
有时候对于内置函数无法满足业务实际的需要，比如业务编码、顺序号等，这时候就需要编写自己的函数
国家办件库rowguid生成函数
-- 实际与国家对接过程中，通过原有陕西办件库的主键动态生成国家办件库要求的rowguid，因此利用表guidforgj和函数getGjGuid动态生成。
create or replace function getGjGuid(p1 varchar, p2 varchar, p3 int) return varchar2
is PRAGMA AUTONOMOUS_TRANSACTION;
FunctionResult varchar2(64);
counNum int;
begin
select count(1) into counNum from guidforgj g where g.projid = p1 and g.orgbusno = p2 and g.dataver = p3;
--dbms_output.put_line('counNum_' || counNum || '输出');
if counNum >0
then
select g.gjguid into FunctionResult from guidforgj g where g.projid = p1 and g.orgbusno = p2 and g.dataver = p3;
--dbms_output.put_line('FunctionResult_' || FunctionResult || '输出');
return(FunctionResult);
end if;
if FunctionResult is null
then
FunctionResult:=sys_guid();
--dbms_output.put_line('FunctionResult2_' || FunctionResult || '输出');
insert into guidforgj values(FunctionResult,p1,p3,p2);
COMMIT;
--dbms_output.put_line('ok_' || FunctionResult || '输出');
end if;
return(FunctionResult);
end getGjGuid;
--表guidforgj建表语句如下：
-- Create table
create table GUIDFORGJ
(
gjguid VARCHAR2(64) default sys_guid() not null,
projid VARCHAR2(30),
dataver NUMBER(4),
orgbusno VARCHAR2(64)
)
tablespace WORKINFOGJ
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index INDEX_DATAVER_GJ on GUIDFORGJ (DATAVER)
tablespace WORKINFOGJ
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index INDEX_ORGBUSNO_GJ on GUIDFORGJ (ORGBUSNO)
tablespace WORKINFOGJ
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index INDEX_PROJID_GJ on GUIDFORGJ (PROJID)
tablespace WORKINFOGJ
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table GUIDFORGJ
add constraint PK_GJGUID primary key (GJGUID)
using index
tablespace WORKINFOGJ
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
工作日函数
计算两个时间之间相差多少个工作日，只去掉了周末。
create or replace function workdays(p1 date, p2 date) return varchar2 is
FunctionResult varchar2(64);
begin
select (trunc(p1 - p2) -
((case
WHEN (8 - to_number(to_char(p2,'D'))) > trunc(p1 - p2) + 1 THEN 0
ELSE
trunc((trunc(p1 - p2) -
(8 - to_number(to_char(p2,'D'))))/7) + 1 END) +
(case
WHEN mod(8 - to_char(p2, 'D'), 7) > trunc(p1 - p2) - 1 THEN 0
ELSE
trunc((trunc(p1 - p2) - (mod(8 - to_char(p2,'D'),7) + 1))/7) + 1 END)))
into FunctionResult
from dual;
return(FunctionResult);
end workdays;
3、存储过程
存储过程在早起的数据库运维、开发及管理中是经常使用的，但是随着应用程序、服务器配置的提升，已经越来越依赖应用去处理很多的业务逻辑，所以存储过程也用的越来越少，但是其实这部分在实际项目中还是很方便的，有些不是特别复杂，只需要数据库函数或脚本即可实现的小程序其实可以利用存储过程来实现。
存储过程的效率要远高于普通的sql脚本。
存储过程中的组成也是由基础sql脚本组成的，只是在部分语法上是存在差别，如：定义变量，静态赋值，查询赋值。
-- 这是实际项目中的一个统计存储过程，用于数据库定时任务，不需要看里边的具体逻辑。
create or replace procedure pro_sjzx_tjfx(sjfl in varchar2,sjxl in varchar2) is
czsj date;
t_tjnd varchar(4);
tbnd varchar(4);
hbnd varchar(4);
tbyf varchar(2);
hbyf varchar(2);
--tjyf varchar(2);
v_id varchar2(20);
v_tempSql varchar2(3000);
v_tempSqlSon varchar2(3000);
v_tempid number;
v_dateFrom varchar(12);
v_dateTo varchar(12);
begin
---变量赋值
czsj := to_date('20120101','yyyymmdd');
--确定统计年度
--- if else
if(to_char(czsj,'mm') = '01')
then
t_tjnd := to_char(add_months(czsj,-1),'yyyy');
--tjyf := '12';
else
t_tjnd := to_char(czsj,'yyyy');
--tjyf := to_char(add_months(czsj,-1),'mm');
end if;
tbnd := t_tjnd/1-1;
--确定环比月份
if(sjfl = '月份统计')
then
hbnd := to_char(add_months(to_date(t_tjnd||substr(sjxl,1,2),'yyyymm'),-1),'yyyy');
hbyf := to_char(add_months(to_date(t_tjnd||substr(sjxl,1,2),'yyyymm'),-1),'mm');
end if;
--插入逻辑
insert
....
commit;
--查询结果赋值
select substr(v_id, 1, 9) ||
to_char(substr(v_id, 10) / 1 + nvl(max(rownum),0)+1)
into v_id
from sjzx_tjfx;
--dbms_output.put_line('v_id-------------'||v_id);
commit;
---删除逻辑
delete from sjzx_tjfx t where t.tjfs1 is null;
commit;
end pro_sjzx_tjfx;
存储过程调用
create or replace procedure tj_season is
czsj date;
sjflval varchar(10);
tjjd varchar(10);
tjyf varchar(4);
begin
czsj := to_date('20190101','yyyymmdd');
sjflval := '季度统计';
tjyf := to_char(add_months(czsj,-1),'mm');
if(tjyf = '03')
then
tjjd := '第一季度';
else
if(tjyf = '06')
then tjjd := '第二季度';
else
if (tjyf = '09')
then tjjd := '第三季度';
else tjjd := '第四季度';
end if;
end if;
end if;
dbms_output.put_line('季度统计'||tjjd);
begin
--调用上边定义的存储过程
pro_sjzx_tjfx(sjfl => sjflval,sjxl => tjjd);
end;
end tj_season;原文始发于微信公众号（云户）：oracle–触发器、函数、存储过程简介
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/25922.html
