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

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Êý¾Ý


Ïà¹ØÎĵµ£º

Aix 5.3ÏÂ×Ô¶¯±¸·ÝOracle¶à¸öʵÀý£¬²¢ÉÏ´«ÖÁFTP·þÎñÆ÷

Ò»¡¢Ð½¨databack.shÎļþ£¬´æ·ÅĿ¼/opt/databack/
#Êý¾Ý¿âÓû§×ÊÁÏ
USERNAME=Êý¾Ý¿âÓû§Ãû(ÐÅÏ¢Òþ²Ø)
PASSWORD=Êý¾Ý¿âÃÜÂë(ÐÅÏ¢Òþ²Ø)
#FTP·þÎñÆ÷ÐÅÏ¢
FTP_SERV=FTP·þÎñÆ÷(ÐÅÏ¢Òþ²Ø)
FTP_USER=FTPÓû§Ãû(ÐÅÏ¢Òþ²Ø)
FTP_PASS=FTPÃÜÂë(ÐÅÏ¢Òþ²Ø)
FTP_DIR=FTPĿ¼(ÐÅÏ¢Òþ²Ø)
#±¸·ÝĿ¼
BACKUP_DIR=/opt/databack
#¸ ......

oracle 10g em¡¢isqlplus·ÃÎʲ»Á˵ÄÎÊÌâ½â¾ö£¨»ã±à£©

Èç¹ûweb·½Ê½µÄem£¬isqlplus·ÃÎʲ»ÁË¡£
1. ¼ì²éÖ÷»úÃû/IP¡¢¶Ë¿ÚÊÇ·ñÕýÈ·
°²×°Ê±µÄÖ÷»úÃû/IP¡¢¶Ë¿Ú¼Ç¼ÔÚ$ORACLE_HOME/install/portlist.ini ÎļþÖС£
ȱʡÊÇ£º
Ò»°ãÓû§                     http://ip:5560/isqlplus
DBAÓû§   &nb ......

¡¾×ª¡¿Oracle Tablespace´´½¨

 Tablespace
ORACLEÖУ¬±í¿Õ¼äÊÇÊý¾Ý¹ÜÀíµÄ»ù±¾·½·¨£¬ËùÓÐÓû§µÄ¶ÔÏóÒª´æ·ÅÔÚ±í¿Õ¼äÖУ¬Ò²¾ÍÊÇÓû§ÓпռäµÄʹÓÃȨ£¬²ÅÄÜ´´½¨Óû§¶ÔÏó£®·ñÔòÊDz»³äÐí´´½¨¶ÔÏó£¬ÒòΪ¾ÍÊÇÏë´´½¨¶ÔÏó,Èç±í,Ë÷ÒýµÈ£¬Ò²Ã»Óеط½´æ·Å,Oracle»áÌáʾ:ûÓд洢Åä¶î£®
¡¡¡¡Òò´Ë£¬ÔÚ´´½¨¶ÔÏó֮ǰ£¬Ê×ÏÈÒª·ÖÅä´æ´¢¿Õ¼ä£®¡¡¡¡
  ·ÖÅä´æ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ