No Name Type NULL Illumination
1 streamnumber number no 流水号
2 user_name varchar2(20) no 发生订单的用户会员名
3 shop_id varchar2(20) no 发生订单的店铺号
4 event_time char(14) no 订单发生时间
5 Order_serial Varchar2(18) yes 订单号-- Create table
create table ORDER_MANAGE
(
streamnumber NUMBER not null,
user_name VARCHAR2(20) not null,
shop_id VARCHAR2(20) not null,
event_time CHAR(14) default to_char(sysdate, 'yyyymmddHH24MISS') not null,
order_serial VARCHAR2(18)
)
CREATE SEQUENCE order_manage_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER order_manage_trigger
BEFORE INSERT ON order_manage
FOR EACH ROW
BEGIN
SELECT order_manage_seq.nextval INTO :new.streamnumber FROM dual;
END;
/
想要实现功能,入库的时候,只入user_name、shop_id,streamnumber、event_time由系统生成,Order_serial由event_time与streamnumber拼接成,拼接规则:
取event_time的前8位,然后,取streamnumber来拼接
如果streamnumber位长不够10位的话,加前导0,比如,streamnumber=1,event_time[1,8]=20111011
结果就是:201110110000000001
想要这功能。建了如下触发器,但编译错误,各位路过的帮忙看看?谢谢。
SQL> CREATE OR REPLACE TRIGGER orderManage_TRG_serail
2 BEFORE INSERT ON order_manage
3 FOR EACH ROW
4 declare m_streamnumber number;
5 declare i number;
6 declare orderSerialLen number;
7 declare eventtime varchar2(14);
8 declare v_order_serial varchar2(18);
9 BEGIN
10 m_streamnumber := :new.streamnumber;
11 eventtime := :new.event_time;
12 orderSerialLen := 18 - 8 - length(m_streamnumber);
13
14 if orderSerialLen < 1 then
15 begin
16 orderSerialLen:=0;
17 end;
18 end if;
19
20 i:=0;
21 v_order_serial := "";
22 while i < orderSerialLen loop
23 begin
24 v_order_serial:=v_order_serial||"0";
25 i:=i+1;
26 end;
27 end loop;
28
29 :new.order_serial := substr(eventtime,1,8)||v_order_serial||m_streamnumber;
30 --:new.order_serial := v_order_serial;
31
32 -- :new.order_serial := :new.streamnumber || :new.event_time;
33
34 END orderManage_TRG_serail;
35 /警告: 创建的触发器带有编译错误。SQL> show err
TRIGGER ORDERMANAGE_TRG_SERAIL 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3 PLS-00103: 出现符号 "DECLARE"在需要下列之一时:
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
符号 "begin" 被替换为 "DECLARE" 后继续。3/3 PLS-00103: 出现符号 "DECLARE"在需要下列之一时:
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
符号 "begin" 被替换为 "DECLARE" 后继续。LINE/COL ERROR
-------- -----------------------------------------------------------------4/3 PLS-00103: 出现符号 "DECLARE"在需要下列之一时:
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
符号 "begin" 被替换为 "DECLARE" 后继续。5/3 PLS-00103: 出现符号 "DECLARE"在需要下列之一时:
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursorLINE/COL ERROR
-------- -----------------------------------------------------------------
符号 "begin" 被替换为 "DECLARE" 后继续。31/27 PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin case declare
end exception exit for goto if loop mod null pragma raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipeSQL>
1 streamnumber number no 流水号
2 user_name varchar2(20) no 发生订单的用户会员名
3 shop_id varchar2(20) no 发生订单的店铺号
4 event_time char(14) no 订单发生时间
5 Order_serial Varchar2(18) yes 订单号-- Create table
create table ORDER_MANAGE
(
streamnumber NUMBER not null,
user_name VARCHAR2(20) not null,
shop_id VARCHAR2(20) not null,
event_time CHAR(14) default to_char(sysdate, 'yyyymmddHH24MISS') not null,
order_serial VARCHAR2(18)
)
CREATE SEQUENCE order_manage_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER order_manage_trigger
BEFORE INSERT ON order_manage
FOR EACH ROW
BEGIN
SELECT order_manage_seq.nextval INTO :new.streamnumber FROM dual;
END;
/
想要实现功能,入库的时候,只入user_name、shop_id,streamnumber、event_time由系统生成,Order_serial由event_time与streamnumber拼接成,拼接规则:
取event_time的前8位,然后,取streamnumber来拼接
如果streamnumber位长不够10位的话,加前导0,比如,streamnumber=1,event_time[1,8]=20111011
结果就是:201110110000000001
想要这功能。建了如下触发器,但编译错误,各位路过的帮忙看看?谢谢。
SQL> CREATE OR REPLACE TRIGGER orderManage_TRG_serail
2 BEFORE INSERT ON order_manage
3 FOR EACH ROW
4 declare m_streamnumber number;
5 declare i number;
6 declare orderSerialLen number;
7 declare eventtime varchar2(14);
8 declare v_order_serial varchar2(18);
9 BEGIN
10 m_streamnumber := :new.streamnumber;
11 eventtime := :new.event_time;
12 orderSerialLen := 18 - 8 - length(m_streamnumber);
13
14 if orderSerialLen < 1 then
15 begin
16 orderSerialLen:=0;
17 end;
18 end if;
19
20 i:=0;
21 v_order_serial := "";
22 while i < orderSerialLen loop
23 begin
24 v_order_serial:=v_order_serial||"0";
25 i:=i+1;
26 end;
27 end loop;
28
29 :new.order_serial := substr(eventtime,1,8)||v_order_serial||m_streamnumber;
30 --:new.order_serial := v_order_serial;
31
32 -- :new.order_serial := :new.streamnumber || :new.event_time;
33
34 END orderManage_TRG_serail;
35 /警告: 创建的触发器带有编译错误。SQL> show err
TRIGGER ORDERMANAGE_TRG_SERAIL 出现错误:LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3 PLS-00103: 出现符号 "DECLARE"在需要下列之一时:
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
符号 "begin" 被替换为 "DECLARE" 后继续。3/3 PLS-00103: 出现符号 "DECLARE"在需要下列之一时:
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
符号 "begin" 被替换为 "DECLARE" 后继续。LINE/COL ERROR
-------- -----------------------------------------------------------------4/3 PLS-00103: 出现符号 "DECLARE"在需要下列之一时:
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
符号 "begin" 被替换为 "DECLARE" 后继续。5/3 PLS-00103: 出现符号 "DECLARE"在需要下列之一时:
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursorLINE/COL ERROR
-------- -----------------------------------------------------------------
符号 "begin" 被替换为 "DECLARE" 后继续。31/27 PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
begin case declare
end exception exit for goto if loop mod null pragma raise
return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipeSQL>
BEFORE INSERT ON order_manage
FOR EACH ROW
DECLARE
M_STREAMNUMBER NUMBER;
I NUMBER;
ORDERSERIALLEN NUMBER;
EVENTTIME VARCHAR2(14);
V_ORDER_SERIAL VARCHAR2(18);
BEGIN
M_STREAMNUMBER := :NEW.STREAMNUMBER;
EVENTTIME := :NEW.EVENT_TIME;
ORDERSERIALLEN := 18 - 8 - LENGTH(M_STREAMNUMBER); IF ORDERSERIALLEN < 1 THEN
BEGIN
ORDERSERIALLEN := 0;
END;
END IF; I := 0;
V_ORDER_SERIAL := ' ';
WHILE I < ORDERSERIALLEN LOOP
BEGIN
V_ORDER_SERIAL := V_ORDER_SERIAL || '0';
I := I + 1;
END;
END LOOP; :NEW.ORDER_SERIAL := SUBSTR(EVENTTIME, 1, 8) || V_ORDER_SERIAL ||
M_STREAMNUMBER;
--:new.order_serial := v_order_serial; -- :new.order_serial := :new.streamnumber || :new.event_time;END ORDERMANAGE_TRG_SERAIL;
不是我意料之中的啊。
用lpad 格式化一下
怀疑是不是前面:
ORDERSERIALLEN := 18 - 8 - LENGTH(M_STREAMNUMBER);
这里出问题了?
直接用序列+lpad函数CREATE SEQUENCE order_manage_seq START WITH 1 INCREMENT BY 1;select SUBSTR(EVENTTIME, 1, 8) || LPAD(V_ORDER_SERIAL,10,'0') from table 即可
(
streamnumber NUMBER not null,
user_name VARCHAR2(20) not null,
shop_id VARCHAR2(20) not null,
event_time CHAR(14) default to_char(sysdate, 'yyyymmddHH24MISS') not null,
order_serial VARCHAR2(18)
)CREATE SEQUENCE order_manage_seq START WITH 1 INCREMENT BY 1;CREATE OR REPLACE TRIGGER order_manage_trigger
BEFORE INSERT ON order_manage
FOR EACH ROW
BEGIN
:new.streamnumber := order_manage_seq.nextval;
:new.order_serial := concat(substr(:new.event_time, 1, 8), lpad(:new.streamnumber, 10, '0'));
END;
/insert into ORDER_MANAGE(user_name, Shop_Id) values('张三', 'iPhone4s');
commit;select * from ORDER_MANAGE;
STREAMNUMBER USER_NAME SHOP_ID EVENT_TIME ORDER_SERIAL
------------ -------------------- -------------------- -------------- ------------------
1 张三 iPhone4s 20111012151137 201110120000000001