名站网址导航为大家提供关于数据库教程相关的教程网站知识。
oracle生成动态前缀且自增号码的函数分享
复制具体相关代码 具体相关代码如下:,create or replace,Function GetInvitationNO(prev varchar2, num1 varchar2, num2 varchar2, sessionSetting varchar2),Return Varchar2,Authid Current_User Is PRAGMA AUTONOMOUS_TRANSACTION;-- 需要使用“Current_User”的权限防止无法运行“Execute Immediat,Totalprev Varchar2(50);,hassequences Number;,ReNO varchar2(50);,Begin,-- 号码的前缀,Totalprev := Prev || Num1 || Num2 || Sessionsetting;,-- 创建序列,Select Count(*) Into hassequences From User_Sequences Where Sequence_Name=Totalprev;,If hassequences <> 1 Then,Execute Immediate 'Create Sequence '||Totalprev||' Increment By 1 Start With 1 maxvalue 9999999 Nocy,End If;,-- 通过动态的序列名取序列值，把获取的序列值转换为7位长度的字符串，长度不足用“0”填充 ,Execute Immediate 'Select '''|| Totalprev ||''' || to_char('||Totalprev||'.Nextval,''FM0000000'') Fr,-- to_char()中FM后跟的0代表了数字的位数，不足的用“0”填充；加上“FM”可以避免空格的出现，前面的空格是为符号位保留的，假如如果是对负数使用to_char，则空格部分是用来显示－的，因为是,Return ReNO;,end;,测试,复制具体相关代码 具体相关代码如下:,Select GetInvitationNO('p', '1', '01', '114') From dual -- Dual 是 Oracle中的一个实际存在的表，任何用户均可读取，经常用的在没有目标表,
使用oracle触发器 实现对某个表的增改删的监控操作办法,并记录到另一个表中。
具体相关代码：
复制具体相关代码 具体相关代码如下:
create or replace trigger test_trigger
before insert or update or delete on test_table
for each row
declare
v_id varchar2(30);
v_bdlb varchar2(1);
v_jgdm VARChAR2(12);
v_jgmc VARChAR2(60);
v_gajgmc VARChAR2(60);
v_gajgwsmc VARChAR2(30);
v_jz VARChAR2(30);
v_ksdwsdwmc VARChAR2(30);
begin
/*
插入时往历史表中存放的是新插入的数据.
修改时往历史表中存放的是修改后的数据.
删除时往历史表中存放的是删除之前的数据.
*/
select org_id_s.nextval into v_id from dual; -- 利用seq生成主键
v_jgdm := :new.row_id;
v_jgmc := :new.dept_name;
v_gajgmc := :new.dept_name;
v_gajgwsmc := :new.bmjc;
v_jz := substr(v_jgdm, 7, 2);
if '2' = :new.depttype then
v_ksdwsdwmc := 'shiju';
else
if '03' = v_jz then
v_ksdwsdwmc := 'zhi';
elsif '05' = v_jz then
v_ksdwsdwmc := 'xing';
elsif '51' = v_jz then
v_ksdwsdwmc := 'she';
else
v_ksdwsdwmc := 'qita';
end if;
end if;
if inserting then
v_bdlb := '1';
insert into test_table_h
(id, bdlb, jgdm, jgmc, gajgmc, gajgwsmc, jz, ksdwsdwmc)
values
(v_id,
v_bdlb,
v_jgdm,
v_jgmc,
v_gajgmc,
v_gajgwsmc,
v_jz,
v_ksdwsdwmc);
elsif updating then
v_bdlb := '2';
insert into test_table_h
(id, bdlb, jgdm, jgmc, gajgmc, gajgwsmc, jz, ksdwsdwmc)
values
(v_id,
v_bdlb,
v_jgdm,
v_jgmc,
v_gajgmc,
v_gajgwsmc,
v_jz,
v_ksdwsdwmc);
else
v_bdlb := '3';
v_jgdm := :old.row_id;
v_jgmc := :old.dept_name;
v_gajgmc := :old.dept_name;
v_gajgwsmc := :old.bmjc;
v_jz := substr(v_jgdm, 7, 2);
if '2' = :old.depttype then
v_ksdwsdwmc := 'shiju';
else
if '03' = v_jz then
v_ksdwsdwmc := 'zhi';
elsif '05' = v_jz then
v_ksdwsdwmc := 'xing';
elsif '51' = v_jz then
v_ksdwsdwmc := 'she';
else
v_ksdwsdwmc := 'qita';
end if;
end if;
insert into test_table_h
(id, bdlb, jgdm, jgmc, gajgmc, gajgwsmc, jz, ksdwsdwmc)
values
(v_id,
v_bdlb,
v_jgdm,
v_jgmc,
v_gajgmc,
v_gajgwsmc,
v_jz,
v_ksdwsdwmc);
end if;
end;
关于数据库教程相关的教程网站知识今天我们就说到这里了，希望可以帮到大家。
