¹ØÓÚoracle´¥·¢Æ÷µÄÎÊÌâ - Oracle / ¸ß¼¶¼¼Êõ
ÏÖÔÚÓÐÈý¸ö±íA£¬B£¬C,±íÖÐ×Ö¶ÎÈçÏ£º
±íA£º[×Ö¶Î1][×Ö¶Î2][×Ö¶Î3][×Ö¶Î4]
±íB£º[×Ö¶Î1][×Ö¶Î2]
±íC£º[×Ö¶Î3][×Ö¶Î4]
ÒªÇóµ±±íAÖ´ÐÐinsert²Ù×÷ʱ£¬Ïò±íBºÍ±íCÖвåÈëÏàÓ¦×ֶΣ¬ÆäÖÐ×Ö¶Î1ºÍ×Ö¶Î2²åÈë±íB£¬×Ö¶Î3ºÍ×Ö¶Î4²åÈë±íC
ÇëÎÊ´¥·¢Æ÷¸ÃÔõôд°¡£¿
ÎÒ³¢ÊÔ×ÅÔÚÒ»¸ö´¥·¢Æ÷ÁËдÁËÁ½ÌõinsertÓï¾ä£¬µ«ÊÇûÓÐÖ´Ðгɹ¦¡£
SQL code:
CREATE OR REPLACE TRIGGER tr_A_insert
AFTER INSERT ON table_A
FOR EACH ROW
BEGIN
--ʹÓÃ:newÀ´ÒýÓÃвåÈëÖµ
INSERT INTO ±íB VALUES (:new.×Ö¶Î1, new.×Ö¶Î2);
INSERT INTO ±íC VALUES (:new.×Ö¶Î3, :new.×Ö¶Î4);
END;
´¥·¢Æ÷£º
create or replace trigger asdf
after insert on A
for each row
begin
insert into B values (:new.×Ö¶Î1,:new.×Ö¶Î2);
insert into C values(:new.×Ö¶Î3,:new.×Ö¶Î4);
end;
ÄÇÄãµÄ´¥·¢Æ÷Óï¾äÌù³öÀ´¿´¿´
±¨Ê²Ã´´í
SQL code:
create or replace trigger in_a before insert on A for each row
as
begin
insert into B values(:new.×Ö¶Î1,:new.×Ö¶Î2);
insert into C values(:new.×Ö¶Î3,:new.×Ö¶Î4);
commit;
end;
--2:Ò²¿ÉÒÔÓÃmerge intoÀ´
merge into B using A on(A.×Ö¶Î1=B.×Ö¶Î1 and A.×Ö¶Î2=B.×Ö¶Î2)
when not matched then insert(B.×Ö¶Î1,B.×Ö¶Î2) values(A.×Ö¶Î1,A.×Ö¶Î2)
merge into C using
Ïà¹ØÎÊ´ð£º
ÎÒÊÇÓÃÔ¶³Ì×ÀÃæÁ¬²Ù×÷·þÎñÆ÷ÉϵÄÊý¾Ý¿â¡£
ÔÚ´´½¨Ò»¸öÓû§Ö®ºó£¬ÔÙÓÃPLSQLµÇ¼£¬ÔòPLSQLËÀµôÁË¡£
ÎҹصôPLSQLÖ®ºó£¬ÓÃÆäËûÓû§Ò²²»ÄܵǼ¡£
ÎÒ°ÑoracleµÄ·þÎñÍ£Ö¹£¬½á¹û¾ÍÊÇoracleµÄ·þÎñ״̬¾ÍÍ£ÁôÔÚ¡°Í ......
´ó¼ÒºÃ,ÎÒÏÖÔÚ°Ñoracle·þÎñÆ÷ÉÏÃæµÄÔʼÎļþ,ÏÂÔص½±¾»úÁË.ÎÒÏëÔÚ±¾»ú·ÃÎÊÊý¾Ý¿âÔõôÉèÖð¡.ÊDz»ÊÇÀàËÆ¿ÉÒÔ½¨Á¢Ò»¸öʲôÐéÄâ·þÎñÆ÷À´ÊµÏÖ.Çë´ó¼Ò³ö³öÖ÷Òâ
ÒýÓÃ
´ó¼ÒºÃ,ÎÒÏÖÔÚ°Ñoracle·þÎñÆ÷ÉÏÃæ ......
¼ÙÉètable01 ÖÐÓÐ ÒÔÏÂ×ÊÁÏ
emp_no emp_name
------- ------------
0001 TOM
0002 JOHN
0003 MARY
³£Óõ绰
¶øÎÒÃÇÒªµÃµ½ÒÔϵÄOUTPUT (»òÊǸ÷ÖÖÆäËûµÄoutput)
0001,TOM
0002,JOHN
......
oracleµÄ°²×°ºÍʹÓö¼ºÜÄÑÂð£¿ÔÀ´Ê²Ã´¶¼²»»áʱѧÁËMySQL£¬Ò²Ã»Ôõôѧ¹ý£¬¾Í×Ô¼º×°¼¸´Î£¬Óü¸Ï£¬¾Í´ïµ½ÁË´óÌåÄÜʹÓõij̶ÈÁË£¬¿ÉÊÇoracleµ½ÏÖÔÚ²»»á×°²»»áÓ㬲»»áÅ䣬ÎÒ¾ÍÏë×°¸öoracle£¬È»ºó½¨¸öÊý¾Ý¿â£¬ÀïÃæÓм¸Õ ......
java.sql.SQLException: Io Òì³£: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=186646784)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBErro ......