SQLÓÅ»¯34Ìõ
£¨1£© Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
ORACLE µÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏÂ,Äã±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í¡£Èç¹ûÓÐ3¸öÒÔÉϵıíÁ¬½Ó²éѯ, ÄǾÍÐèҪѡÔñ½»²æ±í(intersection table)×÷Ϊ»ù´¡±í, ½»²æ±íÊÇÖ¸ÄǸö±»ÆäËû±íËùÒýÓõıí.
£¨2£© WHERE×Ó¾äÖеÄÁ¬½Ó˳Ðò£®£º
ORACLE²ÉÓÃ×Ô϶øÉϵÄ˳Ðò½âÎöWHERE×Ó¾ä,¸ù¾ÝÕâ¸öÔÀí,±íÖ®¼äµÄÁ¬½Ó±ØÐëдÔÚÆäËûWHEREÌõ¼þ֮ǰ, ÄÇЩ¿ÉÒÔ¹ýÂ˵ô×î´óÊýÁ¿¼Ç¼µÄÌõ¼þ±ØÐëдÔÚWHERE×Ó¾äµÄĩβ.
£¨3£© SELECT×Ó¾äÖбÜÃâʹÓà ‘ * ‘£º
ORACLEÔÚ½âÎöµÄ¹ý³ÌÖÐ, »á½«'*' ÒÀ´Îת»»³ÉËùÓеÄÁÐÃû, Õâ¸ö¹¤×÷ÊÇͨ¹ý²éѯÊý¾Ý×ÖµäÍê³ÉµÄ, ÕâÒâζ׎«ºÄ·Ñ¸ü¶àµÄʱ¼ä
£¨4£© ¼õÉÙ·ÃÎÊÊý¾Ý¿âµÄ´ÎÊý£º
ORACLEÔÚÄÚ²¿Ö´ÐÐÁËÐí¶à¹¤×÷: ½âÎöSQLÓï¾ä, ¹ÀËãË÷ÒýµÄÀûÓÃÂÊ, °ó¶¨±äÁ¿ , ¶ÁÊý¾Ý¿éµÈ£»
£¨5£© ÔÚSQL*Plus , SQL*FormsºÍPro*CÖÐÖØÐÂÉèÖÃARRAYSIZE²ÎÊý, ¿ÉÒÔÔö¼Óÿ´ÎÊý¾Ý¿â·ÃÎʵļìË÷Êý¾ÝÁ¿ ,½¨ÒéֵΪ200
£¨6£© ʹÓÃDECODEº¯ÊýÀ´¼õÉÙ´¦Àíʱ¼ä£º
ʹÓÃDECODEº¯Êý¿ÉÒÔ±ÜÃâÖØ¸´É¨ÃèÏàͬ¼Ç¼»òÖØ¸´Á¬½ÓÏàͬµÄ±í.
£¨7£© ÕûºÏ¼òµ¥,ÎÞ¹ØÁªµÄÊý¾Ý¿â·ÃÎÊ£º
Èç¹ûÄãÓм¸¸ö¼òµ¥µÄÊý¾Ý¿â²éѯÓï¾ä,Äã¿ÉÒÔ°ÑËüÃÇÕûºÏµ½Ò»¸ö²éѯÖÐ(¼´Ê¹ËüÃÇÖ®¼äûÓйØÏµ)
£¨8£© ɾ³ýÖØ¸´¼Ç¼£º
×î¸ßЧµÄɾ³ýÖØ¸´¼Ç¼·½·¨ ( ÒòΪʹÓÃÁËROWID)Àý×Ó£º
DELETE from EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
from EMP X WHERE X.EMP_NO = E.EMP_NO);
£¨9£© ÓÃTRUNCATEÌæ´úDELETE£º
µ±É¾³ý±íÖеļǼʱ,ÔÚͨ³£Çé¿öÏÂ, »Ø¹ö¶Î(rollback segments ) ÓÃÀ´´æ·Å¿ÉÒÔ±»»Ö¸´µÄÐÅÏ¢. Èç¹ûÄãûÓÐCOMMITÊÂÎñ,ORACLE»á½«Êý¾Ý»Ö¸´µ½É¾³ý֮ǰµÄ״̬(׼ȷµØËµÊǻָ´µ½Ö´ÐÐɾ³ýÃüÁî֮ǰµÄ×´¿ö) ¶øµ±ÔËÓÃTRUNCATEʱ, »Ø¹ö¶Î²»ÔÙ´æ·ÅÈκοɱ»»Ö¸´µÄÐÅÏ¢.µ±ÃüÁîÔËÐкó,Êý¾Ý²»Äܱ»»Ö¸´.Òò´ËºÜÉÙµÄ×ÊÔ´±»µ÷ÓÃ,Ö´ÐÐʱ¼äÒ²»áºÜ¶Ì. (ÒëÕß°´: TRUNCATEÖ»ÔÚɾ³ýÈ«±íÊÊÓÃ,TRUNCATEÊÇDDL²»ÊÇDML)
£¨10£© ¾¡Á¿¶àʹÓÃCOMMIT£º
Ö»ÒªÓпÉÄÜ,ÔÚ³ÌÐòÖо¡Á¿¶àʹÓÃCO
Ïà¹ØÎĵµ£º
ʲôÊÇPL/SQL
PL/SQL(Procedural Language/SQL,¹ý³Ì»¯SQLÓïÑÔ£©ÊÇOracle¹«Ë¾ÔÚ±ê×¼SQLÓïÑԵĻù´¡ÉÏ·¢Õ¹µÄÓïÑÔ£¬Ëü½«±äÁ¿¡¢¿ØÖƽṹ¡¢¹ý³ÌºÍº¯ÊýµÈ½á¹¹»¯³ÌÐòÉè¼ÆµÄÒªËØÒýÈëSQLÓïÑÔ£¬´Ó¶øÄܹ»±àÖÆ±È½Ï¸´ÔÓµÄSQL³ÌÐò£»ÀûÓÃPL/SQLÓïÑÔ±àдµÄ³ÌÐò³ÆÎªPL/SQL³ÌÐò¿é£¬ÆäÖ÷ÒªÌØµãÈçÏ£º
¾ßÓÐÄ£¿é»¯µÄ½á¹¹
ʹÓùý³Ì»¯ÓïÑÔ¿ØÖƽ ......
1.´´½¨¹ý³Ì
create or replace procedure ¹ý³ÌÃû as
ÉùÃ÷Óï¾ä¶Î;
begin
Ö´ÐÐÓï¾ä¶Î;
exception
Òì³£´¦ÀíÓï¾ä¶Î;
end;
2. ´ø²ÎÊýµÄ¹ý³Ì
²ÎÊýÀàÐÍ3ÖÖ
in²ÎÊý£º¶ÁÈë²ÎÊý£¬Ö÷³ÌÐòÏò¹ý³Ì´«µÝ²ÎÊýÖµ
out²ÎÊý£ºÊä³ö²ÎÊý£¬¹ý³ÌÏòÖ÷³ÌÐò´«µÝ²ÎÊýÖµ
in out²ÎÊý£ºË«Ïò²ÎÊý
¶¨Òå ......
Oracle9iÒì³£´¦Àí·ÖΪϵͳԤ¶¨ÒåÒì³£´¦ÀíºÍ×Ô¶¨ÒåÒì³£´¦ÀíÁ½²¿·Ö¡£
×Ô¶¨ÒåÒì³£´¦Àí
1.¶¨ÒåÒì³£´¦Àí
declare Òì³£Ãû exception;
2.´¥·¢Òì³£´¦Àí
raise Òì³£Ãû
3.´¦ÀíÒì³£
exception
when Òì³£Ãû1 then
Òì³£´¦ÀíÓï¾ä¶Î1;
when Òì³£Ãû2 then
Òì³£´¦ÀíÓï¾ä¶Î2;
ʾÀý£º
se ......
µ¼ÈëµÄÏêϸÁ÷³Ì
1¡¢Ð½¨Ò»¸öÊý¾Ý¿â
2¡¢ÔÚеÄÊý¾Ý¿âÉϵãÓÒ¼ü-¡·“ËùÓÐÈÎÎñ”-¡·“µ¼ÈëÊý¾Ý¿â”£¬µãÏÂÒ»²½
3¡¢Ê²Ã´¶¼²»Òª¸Ä£¬ÔÚÊý¾Ý¿âÖÐÑ¡ÔñÄǸö¾ÉµÄÊý¾Ý¿â£¬µãÏÂÒ»²½
4¡¢ÔÚÕâ¸ö½çÃæµÄÊý¾Ý¿âÖÐÑ¡ÔñÄãн¨µÄÊý¾Ý¿â£¬µãÏÂÒ»²½
5¡¢Ñ¡Ôñ“ÔÚSQL SERVERÊý¾Ý¿âÖ®¼ä¸´ÖƶÔÏóºÍÊý¾Ý”£¬µãÏÂÒ»²½
......
Ò»¸ö¼òµ¥µÄСʵÀý¾ÍÃ÷°×~£¡
user±í
ÔÚÕâÕűíÖн¨Á¢´¥·¢Æ÷
Create trigger tiggername
on user
for delete
as
begin ......