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Êý¾Ý
Ïà¹ØÎĵµ£º
Ò»¡¢Ð½¨databack.shÎļþ£¬´æ·ÅĿ¼/opt/databack/
#Êý¾Ý¿âÓû§×ÊÁÏ
USERNAME=Êý¾Ý¿âÓû§Ãû(ÐÅÏ¢Òþ²Ø)
PASSWORD=Êý¾Ý¿âÃÜÂë(ÐÅÏ¢Òþ²Ø)
#FTP·þÎñÆ÷ÐÅÏ¢
FTP_SERV=FTP·þÎñÆ÷(ÐÅÏ¢Òþ²Ø)
FTP_USER=FTPÓû§Ãû(ÐÅÏ¢Òþ²Ø)
FTP_PASS=FTPÃÜÂë(ÐÅÏ¢Òþ²Ø)
FTP_DIR=FTPĿ¼(ÐÅÏ¢Òþ²Ø)
#±¸·ÝĿ¼
BACKUP_DIR=/opt/databack
#¸ ......
Èç¹ûweb·½Ê½µÄem£¬isqlplus·ÃÎʲ»ÁË¡£
1. ¼ì²éÖ÷»úÃû/IP¡¢¶Ë¿ÚÊÇ·ñÕýÈ·
°²×°Ê±µÄÖ÷»úÃû/IP¡¢¶Ë¿Ú¼Ç¼ÔÚ$ORACLE_HOME/install/portlist.ini ÎļþÖС£
ȱʡÊÇ£º
Ò»°ãÓû§ http://ip:5560/isqlplus
DBAÓû§ &nb ......
Tablespace
ORACLEÖУ¬±í¿Õ¼äÊÇÊý¾Ý¹ÜÀíµÄ»ù±¾·½·¨£¬ËùÓÐÓû§µÄ¶ÔÏóÒª´æ·ÅÔÚ±í¿Õ¼äÖУ¬Ò²¾ÍÊÇÓû§ÓпռäµÄʹÓÃȨ£¬²ÅÄÜ´´½¨Óû§¶ÔÏó£®·ñÔòÊDz»³äÐí´´½¨¶ÔÏó£¬ÒòΪ¾ÍÊÇÏë´´½¨¶ÔÏó,Èç±í,Ë÷ÒýµÈ£¬Ò²Ã»Óеط½´æ·Å,Oracle»áÌáʾ:ûÓд洢Åä¶î£®
¡¡¡¡Òò´Ë£¬ÔÚ´´½¨¶ÔÏó֮ǰ£¬Ê×ÏÈÒª·ÖÅä´æ´¢¿Õ¼ä£®¡¡¡¡
·ÖÅä´æ ......