一個最苯的辦法: --example: -------------------------------------------------------------------- CREATE OR REPLACE PROCEDURE PRO_TEST AS CURSOR CUR_TYPE IS SELECT DISTINCT NAME FROM BAO_TEMP; IS_TYPE VARCHAR2(20); I NUMBER(10) ; MIN_ID NUMBER(10) ; MAX_ID NUMBER(10) ; CURSOR CUR_ID IS SELECT ID FROM BAO_TEMP WHERE NAME = IS_TYPE ORDER BY ID ; IS_ID NUMBER(10) ; BEGIN OPEN CUR_TYPE; LOOP FETCH CUR_TYPE INTO IS_TYPE; EXIT WHEN CUR_TYPE%NOTFOUND; SELECT COUNT(*) INTO I FROM BAO_TEMP WHERE NAME = IS_TYPE ; SELECT MIN(ID) INTO MIN_ID FROM BAO_TEMP WHERE NAME = IS_TYPE ; OPEN CUR_ID; LOOP FETCH CUR_ID INTO IS_ID; EXIT WHEN CUR_ID%NOTFOUND; UPDATE BAO_TEMP SET ID = MIN_ID +I-1 WHERE NAME = IS_TYPE AND ID = IS_ID ; I:=I+1 ; END LOOP; CLOSE CUR_ID; END LOOP; CLOSE CUR_TYPE; COMMIT; END; / --------------------------------------------------- SQL> SELECT * FROM BAO_TEMP 2 / ID NAME ---------- -------------------- 401 A 402 A 403 A 502 B 503 B 501 B選取了 6 列目前歷時: 00:00:00.15 SQL> UPDATE BAO_TEMP SET NAME = 'A' WHERE ID = 501 2 /更新了 1 列目前歷時: 00:00:00.00 SQL> COMMIT;確認完成目前歷時: 00:00:00.16 SQL> SELECT * FROM BAO_TEMP 2 / ID NAME ---------- -------------------- 401 A 402 A 403 A 502 B 503 B 501 A選取了 6 列目前歷時: 00:00:00.78 SQL> EXEC PRO_TESTPL/SQL 程序順利完成目前歷時: 00:00:00.32 SQL> SELECT * FROM BAO_TEMP 2 / ID NAME ---------- -------------------- 404 A 405 A 406 A 504 B 504 B 407 A選取了 6 列目前歷時: 00:00:00.78
--tryCREATE OR REPLACE TRIGGER "JAMIS"."T_UPDATE_TASK_ATTR" before UPDATE OF "TASKATTRIBUTE" ON "JAMIS"."TASK" FOR EACH ROW BEGIN if :new.TASKATTRIBUTE!=:old.TASKATTRIBUTE then select max(TASKATTRIBUTEID) into :new.TASKATTRIBUTEID from task where TASKATTRIBUTE=:old.TASKATTRIBUTE; end if; select substr(:new.TASKATTRIBUTEID,1,6)|| substr(10000+substr(:new.TASKATTRIBUTEID,7,4)+1,2,4) into :new.TASKATTRIBUTEID from dual; select End;
--tryCREATE OR REPLACE TRIGGER "JAMIS"."T_UPDATE_TASK_ATTR" before UPDATE OF "TASKATTRIBUTE" ON "JAMIS"."TASK" FOR EACH ROW BEGIN if :new.TASKATTRIBUTE!=:old.TASKATTRIBUTE then select max(TASKATTRIBUTEID) into :new.TASKATTRIBUTEID from task where TASKATTRIBUTE=:old.TASKATTRIBUTE; select substr(:new.TASKATTRIBUTEID,1,6)|| substr(10000+substr(:new.TASKATTRIBUTEID,7,4)+1,2,4) into :new.TASKATTRIBUTEID from dual; end if; End;
to ORARichard(没钱的日子......) 我试一试.顺便问一下,在触发器中怎样调用过程.这样可以吗? CREATE OR REPLACE TRIGGER "JAMIS"."T_UPDATE_TASK_ATTR" AFTER UPDATE OF "TASKATTRIBUTE" ON "JAMIS"."TASK" FOR EACH ROW declare myYear Varchar(10); myTAALIAS Varchar(2); newTaskID Varchar(10); myYearCount NUMBER(10);
BEGIN select (to_char(sysdate,'yyyy')||'%') into myYear from dual; select TAALIAS into myTAALIAS from TASKATTRIB where TASKATTRIBUTEID=:new.TASKATTRIBUTE; select to_char(sysdate,'yyyy')||myTAALIAS||lpad(to_char(myYearCount+1),4,'0') into newTaskID from dual; exec pro_test1(newTaskID,:old.TaskID);--我写一个过程去修改工程编号,可以吗?End;谢谢
把exec去掉,exec是在SQLPLUS的命令,不是数据库的命令
to ORARichard(没钱的日子......) 错误提示:在此类触发器中,无法更新这个new值
CREATE OR REPLACE TRIGGER "JAMIS"."T_UPDATE_TASK_ATTR" before UPDATE OF "TASKATTRIBUTE" ON "JAMIS"."TASK" FOR EACH ROW declare tmp varchar2(20); BEGIN if :new.TASKATTRIBUTE!=:old.TASKATTRIBUTE then select max(TASKATTRIBUTEID) into tmp from task where TASKATTRIBUTE=:old.TASKATTRIBUTE; select substr(tmp,1,6)|| substr(10000+substr(tmp,7,4)+1,2,4) into :new.TASKATTRIBUTEID from dual; end if; End; 这样试试
--example:
--------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE PRO_TEST
AS
CURSOR CUR_TYPE IS
SELECT DISTINCT NAME FROM BAO_TEMP;
IS_TYPE VARCHAR2(20);
I NUMBER(10) ;
MIN_ID NUMBER(10) ;
MAX_ID NUMBER(10) ;
CURSOR CUR_ID IS
SELECT ID FROM BAO_TEMP
WHERE NAME = IS_TYPE
ORDER BY ID ;
IS_ID NUMBER(10) ;
BEGIN
OPEN CUR_TYPE;
LOOP
FETCH CUR_TYPE INTO IS_TYPE;
EXIT WHEN CUR_TYPE%NOTFOUND; SELECT COUNT(*) INTO I
FROM BAO_TEMP
WHERE NAME = IS_TYPE ;
SELECT MIN(ID) INTO MIN_ID
FROM BAO_TEMP
WHERE NAME = IS_TYPE ; OPEN CUR_ID;
LOOP
FETCH CUR_ID INTO IS_ID;
EXIT WHEN CUR_ID%NOTFOUND; UPDATE BAO_TEMP
SET ID = MIN_ID +I-1
WHERE NAME = IS_TYPE
AND ID = IS_ID ; I:=I+1 ; END LOOP;
CLOSE CUR_ID; END LOOP;
CLOSE CUR_TYPE; COMMIT;
END;
/
---------------------------------------------------
SQL> SELECT * FROM BAO_TEMP
2 / ID NAME
---------- --------------------
401 A
402 A
403 A
502 B
503 B
501 B選取了 6 列目前歷時: 00:00:00.15
SQL> UPDATE BAO_TEMP SET NAME = 'A' WHERE ID = 501
2 /更新了 1 列目前歷時: 00:00:00.00
SQL> COMMIT;確認完成目前歷時: 00:00:00.16
SQL> SELECT * FROM BAO_TEMP
2 / ID NAME
---------- --------------------
401 A
402 A
403 A
502 B
503 B
501 A選取了 6 列目前歷時: 00:00:00.78
SQL> EXEC PRO_TESTPL/SQL 程序順利完成目前歷時: 00:00:00.32
SQL> SELECT * FROM BAO_TEMP
2 / ID NAME
---------- --------------------
404 A
405 A
406 A
504 B
504 B
407 A選取了 6 列目前歷時: 00:00:00.78
工程编号 工程名称 工程属性
2005qx0001 123 1
2005qx0002 213 1
2005qx0004 312 1
2005qx0003 121 1
2005lx0001 212 2
to baojianjun(包子)
首先谢谢了.
我认为如何全部搜索修改,会不会很慢?
你设计的结果,好象和我的设想有区别.
如果简单一点:假如一条记录从属性(1)改成属性(2),属性(1)的序列就不管了,让用户自己手工改;记录改完后自动作为属性(2)的最后一条记录.从
工程编号 工程名称 工程属性
2005qx0001 123 1
2005qx0002 213 1
2005lx0001 312 2
2005lx0002 121 2
2005lx0003 212 2改为
工程编号 工程名称 工程属性
2005qx0001 123 1
2005qx0002 213 1
2005lx0001 312 2
2005qx0003 121 1
2005lx0003 212 2这又如何实现?谢谢
ON "JAMIS"."TASK"
FOR EACH ROW
BEGIN
if :new.TASKATTRIBUTE!=:old.TASKATTRIBUTE then
select max(TASKATTRIBUTEID) into :new.TASKATTRIBUTEID
from task
where TASKATTRIBUTE=:old.TASKATTRIBUTE;
end if;
select substr(:new.TASKATTRIBUTEID,1,6)||
substr(10000+substr(:new.TASKATTRIBUTEID,7,4)+1,2,4)
into :new.TASKATTRIBUTEID from dual;
select End;
ON "JAMIS"."TASK"
FOR EACH ROW
BEGIN
if :new.TASKATTRIBUTE!=:old.TASKATTRIBUTE then
select max(TASKATTRIBUTEID) into :new.TASKATTRIBUTEID
from task
where TASKATTRIBUTE=:old.TASKATTRIBUTE;
select substr(:new.TASKATTRIBUTEID,1,6)||
substr(10000+substr(:new.TASKATTRIBUTEID,7,4)+1,2,4)
into :new.TASKATTRIBUTEID from dual;
end if;
End;
我试一试.顺便问一下,在触发器中怎样调用过程.这样可以吗?
CREATE OR REPLACE TRIGGER "JAMIS"."T_UPDATE_TASK_ATTR" AFTER
UPDATE OF "TASKATTRIBUTE"
ON "JAMIS"."TASK"
FOR EACH ROW
declare
myYear Varchar(10);
myTAALIAS Varchar(2);
newTaskID Varchar(10);
myYearCount NUMBER(10);
BEGIN
select (to_char(sysdate,'yyyy')||'%') into myYear from dual;
select TAALIAS into myTAALIAS from TASKATTRIB where TASKATTRIBUTEID=:new.TASKATTRIBUTE;
select to_char(sysdate,'yyyy')||myTAALIAS||lpad(to_char(myYearCount+1),4,'0') into newTaskID from dual; exec pro_test1(newTaskID,:old.TaskID);--我写一个过程去修改工程编号,可以吗?End;谢谢
错误提示:在此类触发器中,无法更新这个new值
ON "JAMIS"."TASK"
FOR EACH ROW
declare tmp varchar2(20);
BEGIN
if :new.TASKATTRIBUTE!=:old.TASKATTRIBUTE then
select max(TASKATTRIBUTEID) into tmp
from task
where TASKATTRIBUTE=:old.TASKATTRIBUTE;
select substr(tmp,1,6)||
substr(10000+substr(tmp,7,4)+1,2,4)
into :new.TASKATTRIBUTEID from dual;
end if;
End;
这样试试