oracleÁÙʱ±íÏà¹Ø֪ʶ
ÔÚOracle8i»òÒÔÉÏ°æ±¾ÖУ¬¿ÉÒÔ´´½¨ÒÔÏÂÁ½ÖÖÁÙʱ±í£º
1¡£»á»°ÌØÓеÄÁÙʱ±í
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification>)
ON COMMIT PRESERVE ROWS£»
2¡£ÊÂÎñÌØÓеÄÁÙʱ±í
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification>)
ON COMMIT DELETE ROWS£»
CREATE GLOBAL TEMPORARY TABLE MyTempTable
Ëù½¨µÄÁÙʱ±íËäÈ»ÊÇ´æÔڵģ¬µ«ÊÇÄãÊÔÒ»ÏÂinsert Ò»Ìõ¼Ç¼ȻºóÓñðµÄÁ¬½ÓµÇÉÏÈ¥select£¬¼Ç¼Êǿյģ¬Ã÷°×ÁË°É£¬ÎÒ°ÑÏÂÃæÁ½¾ä»°ÔÙÌùһϣº
--ON COMMIT DELETE ROWS ˵Ã÷ÁÙʱ±íÊÇÊÂÎñÖ¸¶¨£¬Ã¿´ÎÌá½»ºóORACLE½«½Ø¶Ï±í£¨É¾³ýÈ«²¿ÐУ©
--ON COMMIT PRESERVE ROWS ˵Ã÷ÁÙʱ±íÊǻỰָ¶¨£¬µ±ÖжϻỰʱORACLE½«½Ø¶Ï±í¡£
³åÍ»µÄÎÊÌâ¸ü±¾²»Óÿ¼ÂÇ.
ÁÙʱ±íÖ»ÊDZ£´æµ±Ç°»á»°(session)Óõ½µÄÊý¾Ý£¬Êý¾ÝÖ»ÔÚÊÂÎñ»ò»á»°ÆÚ¼ä´æÔÚ¡£
ͨ¹ýCREATE GLOBAL TEMPORARY TABLEÃüÁî´´½¨Ò»¸öÁÙʱ±í£¬¶ÔÓÚÊÂÎñÀàÐ͵ÄÁÙʱ±í£¬
Êý¾ÝÖ»ÊÇÔÚÊÂÎñÆÚ¼ä´æÔÚ£¬¶ÔÓڻỰÀàÐ͵ÄÁÙʱ±í£¬Êý¾ÝÔڻỰÆÚ¼ä´æÔÚ¡£
»á»°µÄÊý¾Ý¶ÔÓÚµ±Ç°»á»°Ë½ÓС£Ã¿¸ö»á»°Ö»ÄÜ¿´µ½²¢ÐÞ¸Ä×Ô¼ºµÄÊý¾Ý¡£DMLËø²»»á¼Óµ½
ÁÙʱ±íµÄÊý¾ÝÉÏ¡£ÏÂÃæµÄÓï¾ä¿ØÖÆÐеĴæÔÚÐÔ¡£
¡ñ ON COMMIT DELETE ROWS ±íÃûÐÐÖ»ÊÇÔÚÊÂÎñÆÚ¼ä¿É¼û
¡ñ ON COMMIT PRESERVE ROWS ±íÃûÐÐÔÚÕû¸ö»á»°ÆÚ¼ä¿É¼û
¿ÉÒÔ¶ÔÁÙʱ±í´´½¨Ë÷Òý£¬ÊÓͼ£¬³ö·¢Æ÷£¬¿ÉÒÔÓÃexportºÍimport¹¤¾ßµ¼Èëµ¼³ö±íµÄ
¶¨Ò壬µ«ÊDz»Äܵ¼³öÊý¾Ý¡£±íµÄ¶¨Òå¶ÔËùÓеĻỰ¿É¼û¡£
Temporary TablesÁÙʱ±í
1¼ò½é
ORACLEÊý¾Ý¿â³ýÁË¿ÉÒÔ±£´æÓÀ¾Ã±íÍ⣬»¹¿ÉÒÔ½¨Á¢ÁÙʱ±ítemporary tables¡£ÕâЩÁÙʱ±íÓÃÀ´±£´æÒ»¸ö»á»°SESSIONµÄÊý¾Ý£¬
»òÕß±£´æÔÚÒ»¸öÊÂÎñÖÐÐèÒªµÄÊý¾Ý¡£µ±»á»°Í˳ö»òÕßÓû§Ìá½»commitºÍ»Ø¹örollbackÊÂÎñµÄʱºò£¬ÁÙʱ±íµÄÊý¾Ý×Ô¶¯Çå¿Õ£¬
µ«ÊÇÁÙʱ±íµÄ½á¹¹ÒÔ¼°ÔªÊý¾Ý»¹´æ´¢ÔÚÓû§µÄÊý¾Ý×ÖµäÖС£
ÁÙʱ±íÖ»ÔÚoracle8iÒÔ¼°ÒÔÉϲúÆ·ÖÐÖ§³Ö¡£
2Ïêϸ½éÉÜ
OracleÁÙʱ±í·ÖΪ »á»°¼¶ÁÙʱ±í ºÍ ÊÂÎñ¼¶ÁÙʱ±í¡£
»á»°¼¶ÁÙʱ±íÊÇÖ¸ÁÙʱ±íÖеÄÊý¾ÝÖ»ÔڻỰÉúÃüÖÜÆÚÖ®ÖдæÔÚ£¬µ±Óû§Í˳ö»á»°½áÊøµÄʱºò£¬Ora
Ïà¹ØÎĵµ£º
1. ´´½¨ÊÓͼ£º
CREATE OR REPLACE VIEW SM_V_UNIT_AUTH AS
SELECT T2.UNIT_ID,
T2.SUPER_UNIT_ID,
T1.AUTH_ID,
T1.AUTH_NAME,
T1.A ......
Union£¬¶ÔÁ½¸ö½á¹û¼¯½øÐв¢¼¯²Ù×÷£¬²»°üÀ¨Öظ´ÐУ¬Í¬Ê±½øÐÐĬÈϹæÔòµÄÅÅÐò£»
Union All£¬¶ÔÁ½¸ö½á¹û¼¯½øÐв¢¼¯²Ù×÷£¬°üÀ¨Öظ´ÐУ¬²»½øÐÐÅÅÐò£»
Intersect£¬¶ÔÁ½¸ö½á¹û¼¯½øÐн»¼¯²Ù×÷£¬²»°üÀ¨Öظ´ÐУ¬Í¬Ê±½øÐÐĬÈϹæÔòµÄÅÅÐò£»
Minus£¬¶ÔÁ½¸ö½á¹û¼¯½øÐвî²Ù×÷£¬²»°üÀ¨Öظ´ÐУ¬Í¬Ê±½øÐÐĬÈϹæÔòµÄÅÅÐò¡£
¿ÉÒÔÔÚ×î ......
Óαê(CURSOR),ºÜÖØÒª
Óαê:ÓÃÓÚ´¦Àí¶àÐмǼµÄÊÂÎñ
ÓαêÊÇÒ»¸öÖ¸ÏòÉÏÏÂÎĵľä±ú(handle)»òÖ¸Õë,¼òµ¥Ëµ£¬Óαê¾ÍÊÇÒ»¸öÖ¸Õë
1 ´¦ÀíÏÔʽÓαê
ÏÔʽÓα괦ÀíÐè 4¸ö PL/SQL ²½Öè,ÏÔʾÓαêÖ÷ÒªÓÃÓÚ´¦Àí²éѯÓï¾ä
(1) ¶¨ÒåÓαê
¸ñʽ: CURSOR cursor_name [(partment[,parameter]...)] IS s ......
DBMS_CHANGE_NOTIFICATION
¶ÔÏó£¨±í£©Êý¾Ý¸üÐÂ֪ͨ
DBMS_APPLICATION_INFO
ÉèÖÃ/¶ÁÈ¡SESSION APPLICATIONÐÅÏ¢£¬²Ù×÷V$SESSION_LONGOPS
DBMS_ALERT
¸æ¾¯
DBMS_DATAPUMP
Êý¾ÝǨÒÆ
DBMS_DB_VERSION
Êý¾Ý¿â°æ±¾¶¨Òå
DBMS_DDL
Compiles¡¢wrapped¡¢Reorganizes ¶ÔÏó
DBMS_DEBUG
ORACLE DEPL/SQL debugger ¹¤¾ß
DB ......
Ò» ÒýÑÔ
Êý¾Ý²Ö¿â¼¼ÊõÊÇÄ¿Ç°ÒÑÖªµÄ±È½Ï³ÉÊìºÍ±»¹ã·º²ÉÓõĽâ¾ö·½°¸£¬ÓÃÓÚÕûºÍµçÐÅÔËÓªÆóÒµÄÚ²¿ËùÓзÖÉ¢µÄÔʼҵÎñÊý¾Ý£¬²¢Í¨¹ý±ã½ÝÓÐЧµÄÊý¾Ý·ÃÎÊÊֶΣ¬¿ÉÒÔÖ§³ÖÆóÒµÄÚ²¿²»Í¬²¿ÃÅ£¬²»Í¬ÐèÇ󣬲»Í¬²ã´ÎµÄÓû§Ëæʱ»ñµÃ×Ô¼ºËùÐèµÄÐÅÏ¢¡£Êý¾Ý²Ö¿âϵͳÐèÒªÄܹ»¼°Ê±µØ×·×ٺͷÖÎö´óÁ¿µÄÀúÊ·Êý¾Ý£¬²¢Äܹ»¼°Ê±×ö³ö·ÖÎöºÍÔ¤²â£¬Òò´ËÊ ......