Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

ORACLE ´¥·¢Æ÷

oracleѧϰ±Ê¼Ç_´¥·¢Æ÷
´¥·¢Æ÷
ÊÇÌØ¶¨Ê¼þ³öÏÖµÄʱºò£¬×Ô¶¯Ö´ÐеĴúÂë¿é¡£ÀàËÆÓÚ´æ´¢¹ý³Ì£¬µ«ÊÇÓû§²»ÄÜÖ±½Óµ÷ÓÃËûÃÇ¡£
¹¦ÄÜ£º
1¡¢ ÔÊÐí/ÏÞÖÆ¶Ô±íµÄÐÞ¸Ä
2¡¢ ×Ô¶¯Éú³ÉÅÉÉúÁУ¬±ÈÈç×ÔÔö×Ö¶Î
3¡¢ Ç¿ÖÆÊý¾ÝÒ»ÖÂÐÔ
4¡¢ ÌṩÉó¼ÆºÍÈÕÖ¾¼Ç¼
5¡¢ ·ÀÖ¹ÎÞЧµÄÊÂÎñ´¦Àí
6¡¢ ÆôÓø´ÔÓµÄÒµÎñÂß¼­
¿ªÊ¼
create trigger biufer_employees_department_id
 before insert or update
  of department_id
  on employees
 referencing old as old_value
     new as new_value
 for each row
 when (new_value.department_id<>80 )
begin
 :new_value.commission_pct :=0;
end;
/
´¥·¢Æ÷µÄ×é³É²¿·Ö£º
1¡¢ ´¥·¢Æ÷Ãû³Æ
2¡¢ ´¥·¢Óï¾ä
3¡¢ ´¥·¢Æ÷ÏÞÖÆ
4¡¢ ´¥·¢²Ù×÷
1¡¢ ´¥·¢Æ÷Ãû³Æ
create trigger biufer_employees_department_id
ÃüÃûϰ¹ß£º
biufer£¨before insert update for each row£©
employees ±íÃû
department_id ÁÐÃû
2¡¢ ´¥·¢Óï¾ä
±ÈÈ磺
±í»òÊÓͼÉϵÄDMLÓï¾ä
DDLÓï¾ä
Êý¾Ý¿â¹Ø±Õ»òÆô¶¯,startup shutdown µÈµÈ
before insert or update
  of department_id
  on employees
 referencing old as old_value
     new as new_value
 for each row
˵Ã÷£º
1¡¢ ÎÞÂÛÊÇ·ñ¹æ¶¨ÁËdepartment_id £¬¶Ôemployees±í½øÐÐinsertµÄʱºò
2¡¢ ¶Ôemployees±íµÄdepartment_idÁнøÐÐupdateµÄʱºò
3¡¢ ´¥·¢Æ÷ÏÞÖÆ
when (new_value.department_id<>80 )
ÏÞÖÆ²»ÊDZØÐëµÄ¡£´ËÀý±íʾÈç¹ûÁÐdepartment_id²»µÈÓÚ80µÄʱºò£¬´¥·¢Æ÷¾Í»áÖ´ÐС£
ÆäÖеÄnew_valueÊÇ´ú±í¸úÐÂÖ®ºóµÄÖµ¡£
4¡¢ ´¥·¢²Ù×÷
ÊÇ´¥·¢Æ÷µÄÖ÷Ìå
begin
 :new_value.commission_pct :=0;
end;
Ö÷ÌåºÜ¼òµ¥£¬¾ÍÊǽ«¸üкóµÄcommission_pctÁÐÖÃΪ0
´¥·¢£º
insert into employees(employee_id,
last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )
values( 12345,’Chen’,’Donny’, sysdate, 12, ‘donny@hotmail.com’,60,10000,.25);
select commission_pct from employees where employee_id=12345;
´¥·¢Æ÷²»»á֪ͨÓû§£¬±ã¸Ä±äÁËÓû§µÄÊäÈëÖµ¡£
´¥·¢Æ÷ÀàÐÍ£º
1¡¢ Óï¾ä´¥·¢Æ÷
2¡¢ Ðд¥·¢Æ÷
3¡¢ INSTEAD OF ´¥·¢Æ÷
4¡¢ ϵͳÌõ¼þ´¥·¢Æ÷
5¡¢ Óû§Ê¼þ´¥·¢Æ÷
1¡¢ Óï¾ä´¥·


Ïà¹ØÎĵµ£º

ÔÚoracleÖÐ,ÈçºÎ¸øÒÑ´æÔڵıíÌí¼ÓÒ»ÁÐ?

alter table Tablename add(column1 varchar2(20),column2 number(7,2)...)
±ÈÈ磺
ÒÑÓбíA£¬½á¹¹ÈçÏÂ
×Ö¶ÎÃû ÀàÐÍ
------------ -------------
A VARCHAR2(10)
B NUMBER
ÏÖÔÚÒªÔö¼Ó ......

Oracle TO_DATE,to_char

OracleÖÐTO_DATE¸ñʽ
url:http://www.cnblogs.com/ajian/archive/2009/03/25/1421063.html
TO_DATE¸ñʽ(ÒÔʱ¼ä:2007-11-02   13:45:25ΪÀý)
  
        Year:     
        yy two digits Á½ ......

Oracle Sql ÐÔÄÜÓÅ»¯

1£©²»Öª´ó¼ÒÊÇ·ñϲ»¶Ê¹ÓÑNOT IN’ÕâÑùµÄ²Ù×÷£¬Èç¹ûÊÇ£¬ÄǾ¡Á¿Ê¹ÓÃ(NOT) EXISTS Ìæ´ú
2£©ÔÚº£Á¿²éѯʱ¾¡Á¿ÉÙÓøñʽת»»¡£
3£©²éѯº£Á¿Êý¾ÝÊÇ£¬¿ÉÒÔʹÓÃoptimizer['ɔptimaizə] hints£¬ÀýÈç/*+ORDERED */
¾¡Á¿ÉÙÓÃIN²Ù×÷·û£¬»ù±¾ÉÏËùÓеÄIN²Ù×÷·û¶¼¿ÉÒÔÓÃEXISTS´úÌæ¡£
²»ÓÃNOT IN²Ù×÷·û£¬¿ÉÒÔ ......

oracleÖÐnvarchar2×Ö·û¼¯²»Æ¥Åä

oracleµ±¶à±íunionʱÓöµ½nvarchar2ÀàÐÍʱ±¨´í ×Ö·û¼¯²»Æ¥Åä
¶ÔʹÓÃnvarcharµÄµØ·½£¬¼ÓÉÏ to_char( nvarchar µÄ±äÁ¿»ò×Ö¶Î )
È磺
select to_char(name),price from aa
union all
select  to_char(name),price from bb
3Õűíaa,bb,cc¶¼ÓÐ name price ×Ö¶Î ²éѯ¼Û¸ñ×î¸ßµÄǰ3λÐÕÃû
select * from(select to_ch ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ