Oracle Óï¾ä¼¶´¥·¢Æ÷
Ïȹ¹ÔìÒ»¸ö±í:
create table emp2(
id number(2),
name varchar(10),
currdate date,
action varchar2(1)
)
´´½¨´¥·¢Æ÷£º
create or replace trigger d_i_u_emp2
after insert or update or delete on mysort
begin
if inserting then
insert into emp2 values (12,'dog',sysdate,'i');
elsif deleting then
insert into emp2 values (12,'dog',sysdate,'d');
elsif updating then
insert into emp2 values (12,'dog',sysdate,'u');
end if;
end;
Óï¾ä¼¶µÄ²»Éæ¼°µ½Êý¾ÝÍêÕûÐÔµÄÎÊÌ⣬ֻҪ×öÏàÓ¦µÄÓï¾ä¶¯×÷¾Í»á´¥·¢£¬ÓëÐÐÎ޹أ¬ÎÞÂÛ¶àÉÙÐÐÖ»Òª×öÁËÕâ¸ö¶¯×÷¾Í´¥·¢£¡
Ìæ»»´¥·¢Æ÷Ö»Äܽ¨ÔÚÊÓͼÉÏÃæ
Ïà¹ØÎĵµ£º
¡¾×ª¡¿http://www.gbunix.com/htmldata/2004_06/2/5/article_53_1.html
oracleϵͳ±í²éѯ¡¾GBUnix¡¿
Êý¾Ý×Öµädict×ÜÊÇÊôÓÚOracleÓû§sysµÄ¡£
¡¡¡¡1¡¢Óû§£º
¡¡¡¡¡¡select username from dba_users;
¡¡¡¡¸Ä¿ÚÁî
¡¡¡¡¡¡alter user spgroup identified by spgtest;
¡¡¡¡2¡¢±í¿Õ¼ä£º
¡¡¡¡¡¡select * fro ......
CASE±í´ïʽ¿ÉÒÔÔÚSQLÖÐʵÏÖif-then-elseÐ͵ÄÂß¼£¬¶ø²»±ØʹÓÃPL/SQL¡£CASEµÄ¹¤×÷·½Ê½ÓëDECODE()ÀàËÆ£¬µ«Ó¦¸ÃʹÓÃCASE,ÒòΪËüÓëANSI¼æÈÝ¡£
CASEÓÐÁ½ÖÖ±í´ïʽ:
1. ¼òµ¥CASE±í´ïʽ,ʹÓñí´ïʽȷ¶¨·µ»ØÖµ.
Óï·¨:
CASE search_expression
WHEN expression1 THEN result1
WHEN expression2 THEN ......
NULLÖ¸µÄÊÇ¿ÕÖµ£¬»òÕß·Ç·¨Öµ¡£
NVL (expr1, expr2)->expr1ΪNULL£¬·µ»Øexpr2£»²»ÎªNULL£¬·µ»Øexpr1¡£×¢ÒâÁ½ÕßµÄÀàÐÍÒªÒ»ÖÂ
NVL2 (expr1, expr2, expr3) ->expr1²»ÎªNULL£¬·µ»Øexpr2£»ÎªNULL£¬·µ»Øexpr3¡£expr2ºÍexpr3ÀàÐͲ»Í¬µÄ»°£¬expr3»áת»»Îªexpr2µÄÀàÐÍ
NULLIF (expr1, expr2) ->ÏàµÈ·µ»ØNULL£¬²»µÈ·µ»Øex ......
Oracle ´¥·¢Æ÷ÓÐÓï¾ä¼¶´¥·¢Æ÷ºÍÐм¶´¥·¢Æ÷
Óï¾ä¼¶´¥·¢Æ÷ µ±É¾³ý²¿ÃűíÖеIJ¿ÃźÅʱ£¬Í¬Ê±É¾³ýµôÔ±¹¤±íÖв¿ÃźÅΪ£ºold.deptnoµÄ¼Ç¼
create or replace trigger del_dept_id
after delete on dept
for each row
begin
delete from emp where deptno=:old.deptno;
end;
µ±Íù²¿Ãűí²åÈëʱ£¬Í¬Ê±ÔÚÔ±¹¤±íÖ ......