Oracle CURSORµÄ¼òµ¥Ó÷¨
Ç°ÑÔ£º
OracleµÄ¶Ô±í²Ù×÷ÖÐÓÐÒ»ÖÖÀàËÆÓÚDataSetµÄ¶ÔÏó²Ù×÷·½·¨CURSOR£¬Ëü¿ÉÒÔͨ¹ý½¨Á¢±íµÄ²Ù×÷¶ÔÏó»òÕß˵±íµÄÖ¸Õë¶ÔÏóÀ´´ïµ½´Ó±íÀïÃæÌáÈ¡Êý¾ÝµÄ²Ù×÷¡£
˵Ã÷£º
Ò»°ãͨ¹ýSQLÓïÑÔ¿ÉÒÔÕë¶Ôij¸ö±íµÄijһÐлò¶àÐÐÊý¾Ý½øÐвÙ×÷±ÈÈç˵SELECT£¬UPDATEµÈ¡£ÕâЩ²Ù×÷±ØÐëÒÔSQLÓï¾äµÄÓï·¨¸ñʽÀ´±»½âÊÍÆ÷½âÊͲ¢Ö´ÐС£ÔÚʵ¼Ê±ä³ÉÖо³£Óõ½£¬ÌرðÊÇÓñà³ÌÓïÑÔÀ´¶ÔÊý¾Ý¿â²Ù×÷ʱ¸üÊDZز»¿ÉÉÙ¡£µ±È»Êý¾Ý¿âÒ²²»ÊDz»ÄÜ×Ô¼ºÀ´×öһЩ´¦ÀíµÄ£¬¼òµ¥µÄÊý¾Ý²Ù×÷µÄ»°£¬ÏñORACLEÕâÑùµÄÊý¾Ý¿â¿ÉÒÔÓÃ×ÔÉíµÄPL/SQLÓïÑÔÀ´ÊµÏÖ¡£²»½öÖ´ÐÐËٶȿ죬¶øÇÒ½ÚÊ¡ÍøÂç¡£ÒòΪËüÊÇÖ±½ÓÔÚÊý¾Ý¿â¶ËÖ´Ðеġ£
CURSOR¾ÍÊÇPL/SQLÖеÄÒ»ÖÖʵÏÖ¶Ô±íµÄ¶ÔÏ󻯲Ù×÷·½·¨¡£
ËûÒ»¹²·ÖΪÁ½ÖÖ£º
ÏÔʽÐÍ
½âÊÍ£º¹ËÃû˼Ò壬¾ÍÊÇÔÚ³ÌÐòÊéд¹ý³ÌÖÐÀûÓô´½¨±í¶ÔÏóµÄ¹ý³ÌÖУ¬Ã÷È·µÄʹÓñäÁ¿Ãû¡£
°µÊ½ÐÍ
½âÊÍ£º²»ÊÇÓñäÁ¿Ãû£¬¶øÓüòµ¥µÄÓï·¨À´Íê³É¶ÔÏóµÄ²Ù×÷¡£ORACLEÀïÃæÒѾ¶¨ÒåºÃÁË£¬Ö»ÐèÒªµ÷ÓþͿÉÒÔʹÓá£
¹ØÓÚPL/SQLµÄÊéд¸ñʽ£¬ÕâÀïÊ¡ÂÔ¡£
ÏÔʽÐ͵ÄCURSORÔÚ¶¨Òå¹ý³ÌÖбØÐëдÔÚ¶¨ÒåÇø¼ä
Ò²¾ÍÊÇ˵ÐèҪдÔÚ IS ..... BEGIN »òÕß DECLARE .... BEGIN¡£
¶øÖ´ÐеÄʱºò¾ÍҪдÔÚ BEGIN.....ENDÁË¡£
¿´²»Ã÷°×µÄ»°£¬¾Í¿´Àý×ÓÁË¡£
CREATE OR REPLACE PROCEDURE AAA
IS
CURSOR BBB IS
SELECT CCC from TABLE ;
vBBB BBB%ROWTYPE;
BEGIN
OPEN BBB;
LOOP
FETCH BBB INTO vBBB;
EXIT WHEN BBB%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vBBB);
END LOOP;
CLOSE BBB;
END;
/
Ê×Ïà´´½¨ÁËÒ»¸öPROCEDUREÃû×ÖΪAAA£¬È»ºóÔÚ¶¨ÒåÇø¼äÀïÃ涨ÒåÁËÒ»¸öCURSORÃû×ÖΪBBB£¬
Á¬½Ó±íTABLE²¢´ÓÖжÁÈ¡ÁÐCCC¡£Ö®ºó¶¨ÒåÁËÒ»¸öÐÐÀàÐ͵ıäÁ¿ÓÃÓڴ洢ÿһÐеÄÊý¾Ý¡£
µ½´Ë£¬±íµÄ¶ÔÏóÒÔ¼°¶ÁÈ¡Êý¾ÝµÄÌõ¼þ»¹ÓÐÊý¾Ý´æ´¢ÓõıäÁ¿¾Í¶¼¶¨ÒåºÃÁË¡£
È»ºóOPENÕâ¸ö±í¶ÔÏ󣬽øÈëÑ»·¡£Ã¿Ñ»·Ò»´ÎÖ¸Õë±ã»á×Ô¶¯ÏòºóÒƶ¯£¬½«BBBÀïÃæµÄÊý¾ÝÒÔÐÐΪµ¥Î»¶Á³ö´æÈëvBBB¡£È»ºóµ±BBBµÄÖ¸Õëµ½×îºóÒ²¾ÍÊÇ˵Êý¾Ý¶Áµ½×îºóûÓÐÁ˵Äʱºò±ãÍ˳öÑ»·²¢ÇҹرÕBBB¡£
DBMS_OUTPUT.PUT_LINEÊÇÓÃÀ´ÔÚÆÁÄ»ÉÏ´òÓ¡vBBBÓõġ£
ÓëÖ®Ïàͬ OPEN FETCH CLOSE µÄÓï·¨¸ñʽ¿´À´Í¦Âé·³µÄ¡£ÕâÑùµÄ»°¾ÍÓÃFORÀ´´úÌæÒ²¿ÉÒÔ
BEGIN
FOR vBBB IN BBB;
LOOP
DBMS_OUTPUT.PUT_LINE(vBBB);
END LOOP;
END;
Õâ¾ÍʡʶàÁË¡£Òâ˼ҲһĿÁËÈ»¡£ÒòΪSURSOR¶¨ÒåÁ˱äÀ´Ãû×
Ïà¹ØÎĵµ£º
±¾ÎĽÚÑ¡×Ô¡¶Oracle DBAÊּǗ—Êý¾Ý¿âÕï¶Ï°¸ÀýÓëÐÔÄÜÓÅ»¯Êµ¼ù¡·µÚ2Õ“YangtingkunµÄDBA¹¤×÷Êּǔ £¨×÷ÕߣºÑîÍ¢çû£©
V$SQLÊÓͼÏÔʾ½á¹ûÒì³£µÄÕï¶Ï
ÓÐÒ»´ÎÅöµ½Ò»¸öºÜÆæ¹ÖµÄÎÊÌ⣬ÔÚ¼ì²é»á»°ËùÖ´ÐеÄSQLʱ£¬·¢ÏÖV$SQLÊÓͼÖÐSQL_TEXTÁÐÖеÄÊý¾ÝÊDz»Õý³£µÄ¡£
ÓÉÓÚV$SQLÊǶ¯Ì¬ÐÔÄÜÊÓͼ£¬ÀïÃæ±£´æµÄÊǵ± ......
sqlserverºÍoracle³£Óú¯Êý¶Ô±È
Êýѧº¯Êý
¡¡¡¡1.¾ø¶ÔÖµ
¡¡¡¡S:select abs(-1) value
¡¡¡¡O:select abs(-1) value from dual
¡¡¡¡2.È¡Õû(´ó)
¡¡¡¡S:select ceiling(-1.001) value
¡¡¡¡O:select ceil(-1.001) value from dual
¡¡¡¡3.È¡Õû£¨Ð¡£©
¡¡¡¡S:select floor(-1.001) value
¡¡¡¡O:select floor(-1.0 ......
ÔÚÏÖ´úµÄ¶àÓû§¶àÈÎÎñϵͳÖУ¬±ØÈ»»á³öÏÖ¶à¸öÓû§Í¬Ê±·ÃÎʹ²ÏíµÄij¸ö¶ÔÏó£¬Õâ¸ö¶ÔÏó¿ÉÄÜÊÇ±í£¬ÐУ¬»òÕßÄÚ´æ½á¹¹£¬ÎªÁ˽â¾ö¶à¸öÓû§²¢·¢ÐÔ·ÃÎÊ´øÀ´µÄÊý¾ÝµÄ°²È«ÐÔ£¬ÍêÕûÐÔ¼°Ò»ÖÂÐÔÎÊÌ⣬±ØÐëÒªÓÐÒ»ÖÖ»úÖÆ£¬À´Ê¹¶ÔÕâЩ¹²Ïí×ÊÔ´µÄ²¢·¢ÐÔ·ÃÎÊ´®Ðл¯£¬oracleÖеÄËø¾Í¿ÉÒÔÌṩÕâÑùµÄ¹¦ÄÜ£¬µ±ÊÂÎñÔÚ¶Ôij¸ö¶ÔÏó½ ......
Oracle Spatial ¼ò½é£º
Ê×ÏÈ£¬Oracle Ö§³Ö×Ô¶¨ÒåµÄÊý¾ÝÀàÐÍ£¬Äã¿ÉÒÔÓÃÊý×飬½á¹¹Ìå»òÕß´øÓй¹Ô캯Êý£¬¹¦Äܺ¯ÊýµÄÀàÀ´¶¨Òå×Ô¼ºµÄ¶ÔÏóÀàÐÍ¡£ÕâÑùµÄ¶ÔÏóÀàÐÍ¿ÉÒÔÓÃÓÚÊôÐÔÁеÄÊý¾ÝÀàÐÍ£¬Ò²¿ÉÒÔÓÃÀ´´´½¨¶ÔÏó±í¡£¶øOracle SpatialÒ²ÕýÊÇ»ùÓÚ´ËÖÖÌØÐÔËù¿ª·¢µÄÒ»Ì׿ռäÊý¾Ý´¦Àíϵͳ¡£
Spatial µÄ×Ô¶¨ÒåÊý¾ÝÀàÐÍÓкܶ࣬¶¼ÔÚMDSYS· ......
ÈçºÎʹexpµÄ°ïÖúÒÔ²»Í¬µÄ×Ö·û¼¯ÏÔʾ:
ÖÐÎÄÏÔʾ£ºset nls_lang=simplified chinese_china.zhs16gbk
Ó¢ÎÄÏÔʾ£ºset nls_lang=American_america
EXPµÄËùÓвÎÊý£¨À¨ºÅÖÐΪ²ÎÊýµÄĬÈÏÖµ£©£º
USERID Óû§Ãû/¿ÚÁî È磺 USERID=duanl/duanl &nbs ......