触发器中实现生成流水编号功能

Question

本帖最后由 wangzairong 于 2009-08-29 17:49:30 编辑

Answer 1

用trigger来做，可以用before的trigger，通过:new.jcbh := :new.ISBN||:new.yc||:new.bc||to_char(sysdate,'yyyymmdd')
或者是在应用做插入之前，自己拼装好不过就是需要先根据A表先确定一下顺序，然后在进行拼装。而且这里还有不明的一点，如果顺序变化以后，B表里的以前的数据的jcbh要变吗？

Answer 2

create or replace trigger before_b_insert
before insert 
on b
for each row
declare
v_isbn number;
v_bc number;
v_yc number;
begin
select isbn,bc,yc into v_isbn,v_bc,v_yc from(
  select max(decode(colname,'isbn',cx))isbn,
    max(decode(colname,'bc',cx))bc,
    max(decode(colname,'yc',cx))yc from a);
    
if v_isbn=1 then
  if v_bc=2 then
    :new.jcbh:=:new.isbn||:new.bc||:new.yc||to_char(sysdate,'YYYYMMDD');
    dbms_output.put_line(:new.isbn||:new.bc||:new.yc||to_char(sysdate,'YYYYMMDD'));
  else
     :new.jcbh:=:new.isbn||:new.yc||:new.bc||to_char(sysdate,'YYYYMMDD');
     dbms_output.put_line(:new.isbn||:new.yc||:new.bc||to_char(sysdate,'YYYYMMDD')); 
  end if;
elsif v_isbn=2 then
   if v_bc=1 then
    :new.jcbh:=:new.bc||:new.isbn||:new.yc||to_char(sysdate,'YYYYMMDD');
    dbms_output.put_line(:new.bc||:new.isbn||:new.yc||to_char(sysdate,'YYYYMMDD'));
  else
    :new.jcbh:=:new.yc||:new.isbn||:new.bc||to_char(sysdate,'YYYYMMDD');  
     dbms_output.put_line(:new.yc||:new.isbn||:new.bc||to_char(sysdate,'YYYYMMDD'));
   end if;  
else
  if v_bc=1 then
    :new.jcbh:=:new.bc||:new.yc||:new.isbn||to_char(sysdate,'YYYYMMDD');
   dbms_output.put_line(:new.bc||:new.yc||:new.isbn||to_char(sysdate,'YYYYMMDD'));
  else
    :new.jcbh:=:new.yc||:new.bc||:new.isbn||to_char(sysdate,'YYYYMMDD');  
    dbms_output.put_line(:new.yc||:new.bc||:new.isbn||to_char(sysdate,'YYYYMMDD'));
  end if;
end if;
     
end before_b_insert;

Answer 3

谢谢答复，这样是可以，这样只是解决了A表中出现的顺序，但如果A表中出现的项目个数不止3个又怎么办呢？因为A表主要是用于存放的编号规则，又用户设定，在编号规则里可以设如下信息：
ISBN
版次(bc)
印次(yc)
条形码(barcode)
教材学科(jcxk)
教材类别(jclb)
出版社编码(cbsbh)
出版日期(cbrq)另外
select isbn,bc,yc into v_isbn,v_bc,v_yc from(
  select max(decode(colname,'isbn',cx))isbn,
    max(decode(colname,'bc',cx))bc,
    max(decode(colname,'yc',cx))yc from a);
里不能直接使用Order by 吗？

Answer 4

create or replace trigger before_b_insert
after insert 
on b
declare
v_1 a.colname%type;
v_2 a.colname%type;
v_3 a.colname%type;
begin
select max(decode(cx,1,colname)),
   max(decode(cx,2,colname)),
   max(decode(cx,3,colname)) into v_1,v_2,v_3 from a;
execute immediate 'update b set jcbh='||v_1||'||'||v_2||'||'||v_3||'||to_char(sysdate,''YYYYMMDD'') where jcbh is null';    
end before_b_insert;
这个例子中的项目个数为3，如果有多个，自己添加上去试试

Answer 5

那么jcbh这个字段值的长度不一定？
你可以在触发器中定义N个参数(n是可能取到得最大项目数)
就OK了。用户没选的项目，值会为空，不影响结果

Answer 6

上面两段不同的地方在于，一个是行级触发器，一个是表级
行级的每插入一行前触发，插入jcbh值
下面的那段在插入记录后触发，对jcbh为空的记录进行修改赋jcbh值
忘了改名称了，应该改为after_b_insert，避免误解

Answer 7

非常感谢，但是这样只能得到形如：YCBCISBN20090831这样的编码，我要的是插入这条记录的YC,BC,ISBN字段的值，而不是字段名，能够留下你的QQ，与你交流吗？

Answer 8

wildwave,刚才试了给的那个是可以的，不过我想在B表插入前设置编号如何做呢?，还有就是要求生成的编号长度最长为23位，又该如何做呢？应该给你加分的，不过还是等待你的最后答复了！

Answer 9

你可以加一句
jcbh=substr('||v_1||'||'||v_2||'||'||v_3||'||to_char(sysdate,''YYYYMMDD''),1,23)

Answer 10

最后还是发现有问题，如果A表里COLNAME字段只有3个值，触发器里的变量多于了3个，就会说表达式错误

Answer 11

对了，忘了这个问题
可以设个字符串，根据colname里的值设定变量的个数。
待会我把修改后的代码贴上来

Answer 12

你试试这个
create or replace trigger before_b_insert
after insert 
on b
declare
v_1 a.colname%type;
v_2 a.colname%type;
v_3 a.colname%type;
v_4 a.colname%type;
v_5 a.colname%type;
v_6 a.colname%type;
v_7 a.colname%type;
v_8 a.colname%type;
v_str varchar2(200):='v_1';
v_str1 varchar2(200):='v_1';
col_str varchar2(2000):='max(decode(cx,1,colname))';
sqlstr varchar2(2000);
v_count number;
begin
select max(decode(cx,1,colname)),
   max(decode(cx,2,colname)),
   max(decode(cx,3,colname)),
   max(decode(cx,4,colname)),
   max(decode(cx,5,colname)),
   max(decode(cx,6,colname)),
   max(decode(cx,7,colname)),
   max(decode(cx,8,colname)) into 
   v_1,v_2,v_3,v_4,v_5,v_6,v_7,v_8 from a;
if v_1 is null then v_1:=''''''; end if;
if v_2 is null then v_2:=''''''; end if;
if v_3 is null then v_3:=''''''; end if;
if v_4 is null then v_4:=''''''; end if;
if v_5 is null then v_5:=''''''; end if;
if v_6 is null then v_6:=''''''; end if;
if v_7 is null then v_7:=''''''; end if;
if v_8 is null then v_8:=''''''; end if;
  
sqlstr:= 'update b set jcbh='||v_1||'||'||v_2||'||'||v_3||'||'||v_4||'||'
  ||v_5||'||'||v_6||'||'||v_7||'||'||v_8||'||to_char(sysdate,''YYYYMMDD'') where jcbh is null';    execute immediate sqlstr;  
end before_b_insert;

Answer 13

v_str varchar2(200):='v_1';
v_str1 varchar2(200):='v_1';
col_str varchar2(2000):='max(decode(cx,1,colname))';
v_count number;
这几个是测试用的变量，可以从declare中删掉

Answer 14

根据你的要求修改如下
drop trigger before_b_insert;create or replace trigger after_b_insert
after insert 
on b
declare
v_1 a.colname%type;
v_2 a.colname%type;
v_3 a.colname%type;
v_4 a.colname%type;
v_5 a.colname%type;
v_6 a.colname%type;
v_7 a.colname%type;
v_8 a.colname%type;
sqlstr varchar2(2000);
begin
select max(decode(cx,1,colname)),
   max(decode(cx,2,colname)),
   max(decode(cx,3,colname)),
   max(decode(cx,4,colname)),
   max(decode(cx,5,colname)),
   max(decode(cx,6,colname)),
   max(decode(cx,7,colname)),
   max(decode(cx,8,colname)) into 
   v_1,v_2,v_3,v_4,v_5,v_6,v_7,v_8 from a;
if v_1 is null then v_1:=''''''; end if;
if v_2 is null then v_2:=''''''; end if;
if v_3 is null then v_3:=''''''; end if;
if v_4 is null then v_4:=''''''; end if;
if v_5 is null then v_5:=''''''; end if;
if v_6 is null then v_6:=''''''; end if;
if v_7 is null then v_7:=''''''; end if;
if v_8 is null then v_8:=''''''; end if;
  
sqlstr:= 'update b set jcbh=substr('||v_1||'||'||v_2||'||'||v_3||'||'||v_4||'||'
  ||v_5||'||'||v_6||'||'||v_7||'||'||v_8||',1,17)||to_char(sysdate,''YYYYMMDD'') where jcbh is null';    execute immediate sqlstr;  
end after_b_insert;

Answer 15

ORA-01747: 无效的用户.表.列，表.列，或列规格
ORA-06512: 在"FREEMAN.TRIGGER_ON_JCXX", line 33
ORA-04088: 触发器 'FREEMAN.TRIGGER_ON_JCXX' 执行过程中出错

Answer 16

ORA-01747: 无效的用户.表.列，表.列，或列规格  
表的列名看有没有问题!

Answer 17

1.创建函数，返回用户定义的编号顺序对应的字段顺序；
   create or replace function ft
   return  varchar2 
   is
   type cur is ref cursor;
   c cur;
   vt varchar2(100);
   v_str varchar2(1000);
   begin
        open c for select col2 from a order by col1;
        loop
            fetch c into vt;
            exit when c%notfound;
            v_str := v_str||vt||'||';
            dbms_output.put_line('v_str='||v_str);
        end loop; 
        return(substr(v_str,1,length(v_str)-2));
   end ft;2.创建触发器；
   create or replace trigger tr_a
   before insert on b 
   for each row
   declare
   v_sql varchar2(1000);
   begin
        v_sql := 'update b set jcbh='||ft||'||to_char(sysdate,''YYYYMMDD'') where jcbh is null';
        execute immediate v_sql;
end tr_a;

Answer 18

补充：a表中的从col1为cx，col2为colname