我开始的做法是作了一个批量增加的函数,然后又对相应的数据表做了一个触发器来实现自动编号.结果发现在函数执行过程中,触发器无法工作.
函数如下:
NewWGPECOSTFunc
(ORIG_MONTH IN STRING,
NEW_MONTH IN STRING,
WTUNITCOST IN NUMBER
)
RETURN NUMBER IS MYTOTAL NUMBER(6);
BEGIN
SELECT COUNT(EMPNO) INTO MYTOTAL FROM WGPECOST
WHERE CHECK_DATE LIKE TO_DATE(NEW_MONTH,'MM/DD/YYYY');
IF MYTOTAL=0 THEN
INSERT INTO WGPECOST (EMPNO, WMETER, PMETER, GMETER, CHECK_DATE, WUNITCOST, PUNITCOST,
GUNITCOST, WMETER_P, PMETER_P, GMETER_P, W_USEVALUE, P_USEVALUE, G_USEVALUE, W_COST,
P_COST, G_COST, M_COST, Y_COST,CHECKER, COSTID )
SELECT CUSTOMERS.CUSTOMERID , 0,0,0, TO_DATE(NEW_MONTH,'MM/DD/YYYY') AS CHECK_DATE,
(SELECT NVL(UNITCOST,0.5) FROM WGPE_UNITCOST WHERE TYPEID='WT'),
(SELECT NVL(UNITCOST,1) FROM WGPE_UNITCOST WHERE TYPEID='GS'),
(SELECT NVL(UNITCOST,0.6) FROM WGPE_UNITCOST WHERE TYPEID='PW'),
NVL(WGPECOST.WMETER,0),NVL(WGPECOST.PMETER,0),NVL(WGPECOST.GMETER,0),
0,0,0,0,0,0,(SELECT UNITCOST FROM WGPE_UNITCOST WHERE TYPEID=(SELECT HOUSE_TYPE
FROM CUSTOMERS WHERE CUSTOMERS.CUSTOMERID=WGPECOST.EMPNO)),
WGPECOST.Y_COST, NVL(WGPECOST.CHECKER,'-'), NVL(WGPECOST.COSTID,'-')
FROM WGPECOST RIGHT OUTER JOIN CUSTOMERS
ON ( WGPECOST.EMPNO = CUSTOMERS.CUSTOMERID )
WHERE WGPECOST.CHECK_DATE LIKE TO_DATE(ORIG_MONTH,'MM/DD/YYYY') OR WGPECOST.CHECK_DATE IS NULL;
END IF;
RETURN(MYTOTAL);
END;
触发器设计如下:
T_INSERT_WGPECOST_NOdeclare
myYear Varchar(10);
myTAALIAS Varchar(2);
myYearCount NUMBER(10);
myNewCOSTID Varchar(10);
--seq_val NUMBER(5);
BEGIN
select (to_char(sysdate,'yyyymm')||'%') into myYear from dual;
select count(COSTID) into myYearCount from WGPECOST where COSTID like myYear;
select to_char(sysdate,'yyyymm')||lpad(to_char(myYearCount+1),4,'0') into myNewCOSTID from dual;
if myNewCOSTID<>:new.COSTID then
select to_char(sysdate,'yyyymm')||lpad(to_char(myYearCount+1),4,'0') into :New.COSTID from dual;
end if;
END;
现在我的想法就是,执行函数的时候停止触发器(我不会实现),当函数执行完后恢复触发器.
在函数插入批量数据的同时,生成对应的costid序列编号(我不会实现)
望高手指教
谢谢
函数如下:
NewWGPECOSTFunc
(ORIG_MONTH IN STRING,
NEW_MONTH IN STRING,
WTUNITCOST IN NUMBER
)
RETURN NUMBER IS MYTOTAL NUMBER(6);
BEGIN
SELECT COUNT(EMPNO) INTO MYTOTAL FROM WGPECOST
WHERE CHECK_DATE LIKE TO_DATE(NEW_MONTH,'MM/DD/YYYY');
IF MYTOTAL=0 THEN
INSERT INTO WGPECOST (EMPNO, WMETER, PMETER, GMETER, CHECK_DATE, WUNITCOST, PUNITCOST,
GUNITCOST, WMETER_P, PMETER_P, GMETER_P, W_USEVALUE, P_USEVALUE, G_USEVALUE, W_COST,
P_COST, G_COST, M_COST, Y_COST,CHECKER, COSTID )
SELECT CUSTOMERS.CUSTOMERID , 0,0,0, TO_DATE(NEW_MONTH,'MM/DD/YYYY') AS CHECK_DATE,
(SELECT NVL(UNITCOST,0.5) FROM WGPE_UNITCOST WHERE TYPEID='WT'),
(SELECT NVL(UNITCOST,1) FROM WGPE_UNITCOST WHERE TYPEID='GS'),
(SELECT NVL(UNITCOST,0.6) FROM WGPE_UNITCOST WHERE TYPEID='PW'),
NVL(WGPECOST.WMETER,0),NVL(WGPECOST.PMETER,0),NVL(WGPECOST.GMETER,0),
0,0,0,0,0,0,(SELECT UNITCOST FROM WGPE_UNITCOST WHERE TYPEID=(SELECT HOUSE_TYPE
FROM CUSTOMERS WHERE CUSTOMERS.CUSTOMERID=WGPECOST.EMPNO)),
WGPECOST.Y_COST, NVL(WGPECOST.CHECKER,'-'), NVL(WGPECOST.COSTID,'-')
FROM WGPECOST RIGHT OUTER JOIN CUSTOMERS
ON ( WGPECOST.EMPNO = CUSTOMERS.CUSTOMERID )
WHERE WGPECOST.CHECK_DATE LIKE TO_DATE(ORIG_MONTH,'MM/DD/YYYY') OR WGPECOST.CHECK_DATE IS NULL;
END IF;
RETURN(MYTOTAL);
END;
触发器设计如下:
T_INSERT_WGPECOST_NOdeclare
myYear Varchar(10);
myTAALIAS Varchar(2);
myYearCount NUMBER(10);
myNewCOSTID Varchar(10);
--seq_val NUMBER(5);
BEGIN
select (to_char(sysdate,'yyyymm')||'%') into myYear from dual;
select count(COSTID) into myYearCount from WGPECOST where COSTID like myYear;
select to_char(sysdate,'yyyymm')||lpad(to_char(myYearCount+1),4,'0') into myNewCOSTID from dual;
if myNewCOSTID<>:new.COSTID then
select to_char(sysdate,'yyyymm')||lpad(to_char(myYearCount+1),4,'0') into :New.COSTID from dual;
end if;
END;
现在我的想法就是,执行函数的时候停止触发器(我不会实现),当函数执行完后恢复触发器.
在函数插入批量数据的同时,生成对应的costid序列编号(我不会实现)
望高手指教
谢谢
minvalue 1
maxvalue 999999999999999999999999999
start with 61
increment by 1
cache 30
order;使用:insert into tname values(序列名.nextval,其他字段值)
关键问题是用序列不行。
我通过函数生成一批数据,这些数据有一个costid的子段,这个子段的编码是这样的年份-流水号,如:2004-0001等。我的触发器就用于生成这样的序号。
现在的问题是 函数执行的时候,触发器无法工作。
insert into tableA (select a,b,year || SEQ.nextval from tableB)
(ORIG_MONTH IN STRING,
NEW_MONTH IN STRING
)
RETURN NUMBER IS MYTOTAL NUMBER(6);
BEGIN
EXECUTE IMMEDIATE ('ALTER TRIGGER T_INSERT_WGPECOST_NO DISABLE');
SELECT COUNT(EMPNO) INTO MYTOTAL FROM WGPECOST
WHERE CHECK_DATE LIKE TO_DATE(NEW_MONTH,'MM/DD/YYYY');
IF MYTOTAL=0 THEN
--select WGPECOST_SQUENCE.nextval into MYTOTAL from dual;
INSERT INTO WGPECOST (EMPNO, WMETER, PMETER, GMETER, CHECK_DATE, WUNITCOST, PUNITCOST,
GUNITCOST, WMETER_P, PMETER_P, GMETER_P, W_USEVALUE, P_USEVALUE, G_USEVALUE, W_COST,
P_COST, G_COST, M_COST, Y_COST,CHECKER, COSTID )
SELECT CUSTOMERS.CUSTOMERID , 0,0,0, TO_DATE(NEW_MONTH,'MM/DD/YYYY') AS CHECK_DATE,
(SELECT NVL(UNITCOST,0.5) FROM WGPE_UNITCOST WHERE TYPEID='WT'),
(SELECT NVL(UNITCOST,1) FROM WGPE_UNITCOST WHERE TYPEID='GS'),
(SELECT NVL(UNITCOST,0.6) FROM WGPE_UNITCOST WHERE TYPEID='PW'),
NVL(WGPECOST.WMETER,0),NVL(WGPECOST.PMETER,0),NVL(WGPECOST.GMETER,0),
0,0,0,0,0,0,(SELECT UNITCOST FROM WGPE_UNITCOST WHERE TYPEID=(SELECT HOUSE_TYPE
FROM CUSTOMERS WHERE CUSTOMERS.CUSTOMERID=WGPECOST.EMPNO)),
WGPECOST.Y_COST, NVL(WGPECOST.CHECKER,'-'),
(select to_char(sysdate,'yyyymm')||lpad(to_char(WGPECOST_SQUENCE.nextval),4,'0') from dual) AS COSTID
FROM WGPECOST RIGHT OUTER JOIN CUSTOMERS
ON ( WGPECOST.EMPNO = CUSTOMERS.CUSTOMERID )
WHERE WGPECOST.CHECK_DATE LIKE TO_DATE(ORIG_MONTH,'MM/DD/YYYY') OR WGPECOST.CHECK_DATE IS NULL;
END IF;
EXECUTE IMMEDIATE ('ALTER TRIGGER T_INSERT_WGPECOST_NO ENABLE');
RETURN(MYTOTAL);
END;
在这个函数中使用序列部分,无法编译通过,是不是不能在插入批量数据时使用序列功能??????
换成to_char(sysdate,'yyyymm')||lpad(to_char(WGPECOST_SQUENCE.nextval),4,'0')
SQL> select * from e; C1 C2
---------- ----------
1 11
1 22
2 33
2 44
2 55
3 66
2 33已选择7行。SQL> insert into e (select 3,testa.nextval from f);已创建4行。SQL> select * from e; C1 C2
---------- ----------
1 11
1 22
2 33
2 44
2 55
3 66
2 33
3 477
3 478
3 479
3 480已选择11行。SQL>