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Êý¾Ý
Ïà¹ØÎĵµ£º
select * from (select t.*,rownum rn from (select * from emp) t where rownum<=10) where rn>=6;
´´½¨·ÖÒ³½á¹û¼¯µÄÓαê
create or replace package fenyepackage as
type testcursor is ref cursor;
end fenyepackage;
´´½¨·ÖÒ³´æ´¢¹ý³Ì
create or replace procedure fenye3(
tableName varchar2, --±íÃû
......
Ò»¡¢Ð½¨databack.shÎļþ£¬´æ·ÅĿ¼/opt/databack/
#Êý¾Ý¿âÓû§×ÊÁÏ
USERNAME=Êý¾Ý¿âÓû§Ãû(ÐÅÏ¢Òþ²Ø)
PASSWORD=Êý¾Ý¿âÃÜÂë(ÐÅÏ¢Òþ²Ø)
#FTP·þÎñÆ÷ÐÅÏ¢
FTP_SERV=FTP·þÎñÆ÷(ÐÅÏ¢Òþ²Ø)
FTP_USER=FTPÓû§Ãû(ÐÅÏ¢Òþ²Ø)
FTP_PASS=FTPÃÜÂë(ÐÅÏ¢Òþ²Ø)
FTP_DIR=FTPĿ¼(ÐÅÏ¢Òþ²Ø)
#±¸·ÝĿ¼
BACKUP_DIR=/opt/databack
#¸ ......
OracleϵÁУºÍ¼Æ¬µÄ´æ´¢
Ò»£ºÊ²Ã´ÊÇ´ó¶ÔÏ󣬴ó¶ÔÏó»ù±¾²Ù×÷£¿
²Î¼ûÎÒµÄBLOG£ºOracleϵÁУºLOB´ó¶ÔÏó´¦Àí
http://blog.csdn.net/qfs_v/archive/2008/05/21/2464599.aspx
¶þ£¬Í¼Æ¬µÄ´æ´¢»ò¶þ½øÖÆÎļþµÄ´æ´¢
1£¬ÏȲåÈëÆÕͨÊý¾Ý£¬Óöµ½´ó¶ÔÏóÁÐʹÓÃempty_blob()¹¹Ôì¿ÕµÄÖ¸Õë¡£
Àý× ......