oracle forallÓï¾ä
FORALLÓï¾äµÄÒ»¸ö¹Ø¼üÐԸĽø£¬Ëü¿É´ó´ó¼ò»¯´úÂ룬²¢ÇÒ¶ÔÓÚÄÇЩҪÔÚPL/SQL³ÌÐòÖиüкܶàÐÐÊý¾ÝµÄ³ÌÐòÀ´Ëµ£¬Ëü¿ÉÏÔÖøÌá¸ßÆäÐÔÄÜ¡£
1:
ÓÃFORALLÀ´ÔöÇ¿DMLµÄ´¦ÀíÄÜÁ¦
OracleΪOracle8iÖеÄPL/SQLÒýÈëÁËÁ½¸öеÄÊý¾Ý²Ù×ÝÓïÑÔ£¨DML£©Óï¾ä£ºBULK COLLECTºÍFORALL¡£ÕâÁ½¸öÓï¾äÔÚPL/SQLÄÚ²¿½øÐÐÒ»ÖÖÊý×é´¦Àí
£»BULK COLLECTÌṩ¶ÔÊý¾ÝµÄ¸ßËÙ¼ìË÷£¬FORALL¿É´ó´ó¸Ä½øINSERT¡¢UPDATEºÍDELETE²Ù×÷µÄÐÔÄÜ¡£OracleÊý¾Ý¿âʹÓÃÕâЩÓï¾ä´ó´ó¼õÉÙÁË
PL/SQLÓëSQLÓï¾äÖ´ÐÐÒýÇæµÄ»·¾³Çл»´ÎÊý£¬´Ó¶øʹÆäÐÔÄÜÓÐÁËÏÔÖøÌá¸ß¡£
ʹÓÃBULK COLLECT£¬Äã¿ÉÒÔ½«¶à¸öÐÐÒýÈëÒ»¸ö»ò¶à¸ö¼¯ºÏÖУ¬¶ø²»Êǵ¥¶À±äÁ¿»ò¼Ç¼ÖС£ÏÂÃæÕâ¸öBULK COLLECTµÄʵÀýÊǽ«±êÌâÖаüº¬
ÓÐ"PL/SQL"µÄËùÓÐÊé¼®¼ìË÷³öÀ´²¢ÖÃÓڼǼµÄÒ»¸ö¹ØÁªÊý×éÖУ¬ËüÃǶ¼Î»ÓÚͨÏò¸ÃÊý¾Ý¿âµÄµ¥Ò»Í¨µÀÖС£
DECLARE
TYPE books_aat
IS TABLE OF book%ROWTYPE
INDEX BY PLS_INTEGER;
books books_aat;
BEGIN
SELECT *
BULK COLLECT INTO book
from books
WHERE title LIKE '%PL/SQL%';
...
END;
ÀàËƵأ¬FORALL½«Êý¾Ý´ÓÒ»¸öPL/SQL¼¯ºÏ´«Ë͸øÖ¸¶¨µÄʹÓü¯ºÏµÄ±í¡£ÏÂÃæµÄ´úÂëʵÀý¸ø³öÒ»¸ö¹ý³Ì£¬¼´½ÓÊÕÊé¼®ÐÅÏ¢µÄÒ»¸öǶÌ×±í£¬²¢½«¸Ã
¼¯ºÏ£¨°ó¶¨Êý×飩µÄÈ«²¿ÄÚÈݲåÈë¸ÃÊé¼®±íÖС£×¢Ò⣬Õâ¸öÀý×Ó»¹ÀûÓÃÁËOracle9iµÄFORALLµÄÔöÇ¿¹¦ÄÜ£¬¿ÉÒÔ½«Ò»Ìõ¼Ç¼ֱ½Ó²åÈëµ½±íÖС£
BULK COLLECTºÍFORALL¶¼·Ç³£ÓÐÓã¬ËüÃDz»½öÌá¸ßÁËÐÔÄÜ£¬¶øÇÒ»¹¼ò»¯ÁËΪPL/SQLÖеÄSQL²Ù×÷Ëù±àдµÄ´úÂë¡£ÏÂÃæµÄ¶àÐÐFORALL INSERTÏ൱
Çå³þµØ˵Ã÷ÁËΪʲôPL/SQL±»ÈÏΪÊÇOracleÊý¾Ý¿âµÄ×î¼Ñ±à³ÌÓïÑÔ¡£
CREATE TYPE books_nt
IS TABLE OF book%ROWTYPE;
/
CREATE OR REPLACE PROCEDURE add_books (
books_in IN books_nt)
IS
BEGIN
FORALL book_index
IN books_in.FIRST .. books_in.LAST
INSERT INTO book
VALUES books_in(book_index);
...
END;
²»¹ýÔÚOracleÊý¾Ý¿â10g֮ǰ£¬ÒÔFORAll·½Ê½Ê¹Óü¯ºÏÓÐÒ»¸öÖØÒªµÄÏÞÖÆ£º¸ÃÊý¾Ý¿â´ÓIN·¶Î§×Ó¾äÖеĵÚÒ»Ðе½×îºóÒ»ÐУ¬ÒÀ´Î¶ÁÈ¡¼¯ºÏµÄÄÚÈÝ
¡£Èç¹ûÔڸ÷¶Î§ÄÚÓöµ½Ò»¸ö䶨ÒåµÄÐУ¬OracleÊý¾Ý
Ïà¹ØÎĵµ£º
Èç¹ûweb·½Ê½µÄem£¬isqlplus·ÃÎʲ»ÁË¡£
1. ¼ì²éÖ÷»úÃû/IP¡¢¶Ë¿ÚÊÇ·ñÕýÈ·
°²×°Ê±µÄÖ÷»úÃû/IP¡¢¶Ë¿Ú¼Ç¼ÔÚ$ORACLE_HOME/install/portlist.ini ÎļþÖС£
ȱʡÊÇ£º
Ò»°ãÓû§ http://ip:5560/isqlplus
DBAÓû§ &nb ......
¡¾IT168¼¼ÊõÎĵµ¡¿
DATA GUARDµÄ×îÖ÷ÒªµÄ¹¦ÄÜÊÇÈßÔÖ¡£µ±È»¸ù¾ÝÅäÖõIJ»Í¬£¬DATA GUARD»¹¿ÉÒԾ߱¸ÒÔÏÂÌص㣺¸ß¿ÉÓá¢ÐÔÄÜÌáÉý¡¢Êý¾Ý±£»¤ÒÔ¼°¹ÊÕϻָ´µÈ¡£
DATA GUARD¿ÉÒÔ·ÖΪÎïÀíSTANDBYºÍÂß¼STANDBYÁ½ÖÖ¡£¶þÕßµÄ×î´ó²î±ðÔÚÓÚ£¬ÎïÀíSTANDBYÓ¦ÓõÄÊÇÖ÷¿âµÄ¹éµµÈÕÖ¾£¬¶ø ......
ÃüÁîÐÐά»¤Oracle AWR
ÓÐʱºòÓÉÓÚÖÖÖÖÔÒò£¬Ö»ÄÜʹÓÃÃüÁîÐÐÀ´Î¬»¤Oracle10g¡£µ±È»ÎÒÃÇÒÀÈ»¿ÉÒÔʹÓÃstatspack£¬µ«ÓÉÓÚstatspack¹¦Äܲ»ÈçAWR£¬¶ø
ÇÒÈç¹ûʹÓÃÁ½ÖÖÐÔÄÜÕï¶Ï¹¤¾ßÒ²ÊÇÒ»ÖÖÀË·Ñ¡£Òò´ËÊÖ¶¯Î¬»¤AWR»¹ÊÇÓбØÒªµÄ£¬ÏÂÃæÁгöһЩ³£ÓõÄAWRÃüÁÒÔ±¸ºó²é¡£
1¡¢ÐÞ¸ÄAWRµÄ´¥·¢ÆµÂÊ
......
OracleϵÁУºÍ¼Æ¬µÄ´æ´¢
Ò»£ºÊ²Ã´ÊÇ´ó¶ÔÏ󣬴ó¶ÔÏó»ù±¾²Ù×÷£¿
²Î¼ûÎÒµÄBLOG£ºOracleϵÁУºLOB´ó¶ÔÏó´¦Àí
http://blog.csdn.net/qfs_v/archive/2008/05/21/2464599.aspx
¶þ£¬Í¼Æ¬µÄ´æ´¢»ò¶þ½øÖÆÎļþµÄ´æ´¢
1£¬ÏȲåÈëÆÕͨÊý¾Ý£¬Óöµ½´ó¶ÔÏóÁÐʹÓÃempty_blob()¹¹Ôì¿ÕµÄÖ¸Õë¡£
Àý× ......
Oracle ÈýÖÖ¼¯ºÏÊý¾ÝÀàÐ͵ıȽÏ:
PL/SQLÖÐûÓÐÊý×éµÄ¸ÅÄËûµÄ¼¯ºÏÊý¾ÝÀàÐͺÍÊý×éÊÇÏàËƵġ£ÔÚ7.3ÒÔÇ°µÄ°æ±¾ÖÐÖ»ÓÐÒ»ÖÖ¼¯ºÏ£¬³ÆΪPL/SQL±í£¬ÔÚÕâÖ®ºóÓÖÓÐÁ½ÖÖ¼¯ºÏÊý¾ÝÀàÐÍ:ǶÌ×±íºÍvarray¡£ÆäÖÐvarray¼¯ºÏÖеÄÔªËØÊÇÓÐÊýÁ¿ÏÞÖƵģ¬index_by±íºÍǶÌ×±íÊÇûÓÐÕâ¸öÏÞÖƵġ£index-by±íÊÇÏ¡ÊèµÄ£¬Ò²¾ÍÊÇ˵ϱê¿ÉÒÔ²»Á¬Ðø ......