´æ´¢¹ý³Ì³£Óü¼ÇÉ
ÉÏһƪ / ÏÂһƪ 2009-03-30 10:52:36
×÷ÕߣºÔ¬¹â¶«
ÎÒÃÇÔÚ½øÐÐpl/sql±à³Ìʱ´ò½»µÀ×î¶àµÄ¾ÍÊÇ´æ´¢¹ý³ÌÁË¡£´æ´¢¹ý³ÌµÄ½á¹¹ÊǷdz£µÄ¼òµ¥µÄ£¬ÎÒÃÇÔÚÕâÀï³ýÁËѧϰ´æ´¢¹ý³ÌµÄ»ù±¾½á¹¹Í⣬»¹»áѧϰ±àд´æ´¢¹ý³ÌʱÏà¹ØµÄһЩʵÓõÄ֪ʶ¡£È磺ÓαêµÄ´¦Àí£¬Òì³£µÄ´¦Àí£¬¼¯ºÏµÄÑ¡ÔñµÈµÈ
1.´æ´¢¹ý³Ì½á¹¹
1.1 µÚÒ»¸ö´æ´¢¹ý³Ì
ÉÏÃæ¾ÍÊÇÒ»¸ö×î¼òµ¥µÄ´æ´¢¹ý³Ì¡£Ò»¸ö´æ´¢¹ý³Ì´óÌå·ÖΪÕâô¼¸¸ö²¿·Ö:
´´½¨Óï¾ä£ºcreate or replace procedure ´æ´¢¹ý³ÌÃû
Èç¹ûûÓÐor replaceÓï¾ä£¬Ôò½ö½öÊÇн¨Ò»¸ö´æ´¢¹ý³Ì¡£Èç¹ûϵͳ´æÔڸô洢¹ý³Ì£¬Ôò»á±¨´í¡£Create or replace procedure Èç¹ûϵͳÖÐûÓд˴洢¹ý³Ì¾Íн¨Ò»¸ö£¬Èç¹ûϵͳÖÐÓд˴洢¹ý³ÌÔò°ÑÔÀ´É¾³ýµô£¬ÖØд´½¨Ò»¸ö´æ´¢¹ý³Ì¡£
´æ´¢¹ý³ÌÃû¶¨Ò壺°üÀ¨´æ´¢¹ý³ÌÃûºÍ²ÎÊýÁÐ±í¡£²ÎÊýÃûºÍ²ÎÊýÀàÐÍ¡£²ÎÊýÃû²»ÄÜÖظ´£¬ ²ÎÊý´«µÝ·½Ê½:IN, OUT, IN OUT
IN ±íʾÊäÈë²ÎÊý£¬°´Öµ´«µÝ·½Ê½¡£
OUT ±íʾÊä³ö²ÎÊý£¬¿ÉÒÔÀí½âΪ°´ÒýÓô«µÝ·½Ê½¡£¿ÉÒÔ×÷Ϊ´æ´¢¹ý³ÌµÄÊä³ö½á¹û£¬¹©Íⲿµ÷ÓÃÕßʹÓá£
IN OUT ¼´¿É×÷ÊäÈë²ÎÊý£¬Ò²¿É×÷Êä³ö²ÎÊý¡£
²ÎÊýµÄÊý¾ÝÀàÐÍÖ»ÐèÒªÖ¸Ã÷ÀàÐÍÃû¼´¿É£¬²»ÐèÒªÖ¸¶¨¿í¶È¡£
²ÎÊýµÄ¿í¶ÈÓÉÍⲿµ÷ÓÃÕß¾ö¶¨¡£
¹ý³Ì¿ÉÒÔÓвÎÊý£¬Ò²¿ÉÒÔûÓвÎÊý
±äÁ¿ÉùÃ÷¿é£º½ô¸ú×ŵÄas (is )¹Ø¼ü×Ö£¬¿ÉÒÔÀí½âΪpl/sqlµÄdeclare¹Ø¼ü×Ö£¬ÓÃÓÚÉùÃ÷±äÁ¿¡£
±äÁ¿ÉùÃ÷¿éÓÃÓÚÉùÃ÷¸Ã´æ´¢¹ý³ÌÐèÒªÓõ½µÄ±äÁ¿£¬ËüµÄ×÷ÓÃÓòΪ¸Ã´æ´¢¹ý³Ì¡£ÁíÍâÕâÀïÉùÃ÷µÄ±äÁ¿±ØÐëÖ¸¶¨¿í¶È¡£×ñÑPL/SQLµÄ±äÁ¿ÉùÃ÷¹æ·¶¡£
¹ý³ÌÓï¾ä¿é£º´Óbegin ¹Ø¼ü×Ö¿ªÊ¼Îª¹ý³ÌµÄÓï¾ä¿é¡£´æ´¢¹ý³ÌµÄ¾ßÌåÂß¼ÔÚÕâÀïÀ´ÊµÏÖ¡£
Òì³£´¦Àí¿é£º¹Ø¼ü×ÖΪexception £¬Îª´¦ÀíÓï¾ä²úÉúµÄÒì³£¡£¸Ã²¿·ÖΪ¿ÉÑ¡
½áÊø¿é£ºÓÉend¹Ø¼ü×Ö½á¹û¡£
1.2 ´æ´¢¹ý³ÌµÄ²ÎÊý´«µÝ·½Ê½
´æ´¢¹ý³ÌµÄ²ÎÊý´«µÝÓÐÈýÖÖ·½Ê½:IN,OUT,IN OUT .
IN °´Öµ´«µÝ£¬²¢ÇÒËü²»ÔÊÐíÔÚ´æ´¢¹ý³ÌÖб»ÖØи³Öµ¡£Èç¹û´æ´¢¹ý³ÌµÄ²ÎÊýûÓÐÖ¸¶¨´æ²ÎÊý´«µÝÀàÐÍ£¬Ä¬ÈÏΪIN
OUT ²ÎÊý£º×÷ΪÊä³ö²ÎÊý£¬ÐèҪעÒ⣬µ±Ò»¸ö²ÎÊý±»Ö¸¶¨ÎªOUTÀàÐÍʱ£¬¾ÍËãÔÚµ÷Óô洢¹ý³Ì֮ǰ¶Ô¸Ã²ÎÊý½øÐÐÁ˸³Öµ£¬ÔÚ´æ´¢¹ý³ÌÖиòÎÊýµÄÖµÈÔÈ»ÊÇnull.
INOUT ÊÇÕæÕýµÄ°´ÒýÓô«µÝ²ÎÊý¡£¼´¿É×÷Ϊ´«Èë²ÎÊýÒ²¿ÉÒÔ×÷Ϊ´«³ö²ÎÊý¡£
Ê×ÏÈ£¬ÎÒÃÇÒªÃ÷°×£¬ÎÒÃÇÎÞ·¨ÔÚ´æ´¢¹ý³ÌµÄ¶¨ÒåÖÐÖ¸¶¨´æ´¢²ÎÊýµÄ¿í¶È£¬Ò²¾Íµ¼ÖÂÁËÎÒÃÇÎÞ·¨ÔÚ´æ´¢¹ý³ÌÖпØÖÆ´«Èë±äÁ¿µÄ¿í¶È¡£Õâ¸ö¿í¶ÈÊÇÍêÈ«ÓÉÍⲿ´«Èëʱ¾ö¶¨µÄ¡£
ÎÒÃÇÔÙÀ´¿´¿´OUTÀàÐ͵IJÎÊýµÄ¿í¶È¡£
¶øÔÚÍⲿµÄµ÷Óùý³ÌÖУ¬p2Õâ¸ö²ÎÊý½ö½ö±»¶¨ÒåΪvarchar2(1).
¶ø°Ñp2×÷Ϊ²ÎÊýµ÷ÓÃÕâ¸ö¹ý³Ì£¬È´²¢Ã»Óб¨´í¡£¶øÇÒËüµÄÕæʵֵ¾ÍÊÇ20¸öa
¿É¼û£¬¶ÔÓÚIN²ÎÊý£¬Æä¿í¶ÈÊÇÓÉÍⲿ¾ö¶¨¡£
¶ÔÓÚOUT ºÍIN OUT ²ÎÊý£¬Æä¿í¶ÈÊÇÓÉ´æ´¢¹ý³ÌÄÚ²¿¾ö¶¨¡£
Òò´Ë£¬ÔÚд´æ´¢¹ý³Ìʱ£¬¶Ô²ÎÊýµÄ¿í¶È½øÐÐ˵Ã÷ÊǷdz£ÓбØÒªµÄ£¬×îÃ÷Öǵķ½·¨¾ÍÊDzÎÊýµÄÊý¾ÝÀàÐÍʹÓÃ%type¡£ÕâÑùË«·½¾Í´ï³ÉÁËÒ»Ö¡£
1.3 ²ÎÊýµÄĬÈÏÖµ
´æ´¢¹ý³ÌµÄ²ÎÊý¿ÉÒÔÉèÖÃĬÈÏÖµ
¿ÉÒÔͨ¹ýdefault ¹Ø¼ü×ÖΪ´æ´¢¹ý³ÌµÄ²ÎÊýÖ¸¶¨Ä¬ÈÏÖµ¡£ÔÚ¶Ô´æ´¢¹ý³Ìµ÷ÓÃʱ£¬¾Í¿ÉÒÔÊ¡ÂÔĬÈÏÖµ¡£
ÐèҪעÒâµÄÊÇ£ºÄ¬ÈÏÖµ½ö½öÖ§³ÖIN´«ÊäÀàÐ͵IJÎÊý¡£OUT ºÍ IN OUT²»ÄÜÖ¸¶¨Ä¬ÈÏÖµ
¶ÔÓÚÓÐĬÈÏÖµµÄ²ÎÊý²»ÊÇÅÅÔÚ×îºóµÄÇé¿ö¡£
exec procdefault2('aa');
ÕâÑùÊǻᱨ´íµÄ¡£
ÄÇÔõô±äÄØ£¿¿ÉÒÔÖ¸¶¨²ÎÊýµÄÖµ¡£
remark
ÕâÑù¾ÍOKÁË£¬Ö¸¶¨aa´«¸ø²ÎÊýp2
2. ´æ´¢¹ý³ÌÄÚ²¿¿é
2.1 ÄÚ²¿¿é
ÎÒÃÇÖªµÀÁË´æ´¢¹ý³ÌµÄ½á¹¹£¬Óï¾ä¿éÓÉbegin¿ªÊ¼£¬ÒÔend½áÊø¡£ÕâЩ¿éÊÇ¿ÉÒÔǶÌס£ÔÚÓï¾ä¿éÖпÉÒÔǶÌ×ÈκÎÒÔϵĿ顣
3.´æ´¢¹ý³ÌµÄ³£Óü¼ÇÉ
3.1 ÄÄÖÖ¼¯ºÏ?
ÎÒÃÇÔÚʹÓô洢¹ý³ÌµÄʱºò¾³£ÐèÒª´¦Àí¼Ç¼¼¯£¬Ò²¾ÍÊǶàÌõÊý¾Ý¼Ç¼¡£·ÖΪµ¥ÁжàÐкͶàÁжàÐУ¬ÕâЩÀàÐͶ¼¿ÉÒÔ³ÆΪ¼¯ºÏÀàÐÍ¡£ÎÒÃÇÔÚÕâÀï½øÐбȽÏÕâЩ¼¯ºÏÀàÐÍ£¬ÒÔ±ãÓÚÔÚ±à³Ìʱ×ö³öÕýÈ·µÄÑ¡Ôñ¡£
Ë÷Òý±í,Ò²³ÆΪpl/sql±í£¬²»ÄÜ´æ´¢ÓÚÊý¾Ý¿âÖУ¬ÔªËصĸöÊýûÓÐÏÞÖÆ,ϱê¿ÉÒÔΪ¸ºÖµ¡£
ǶÌ×±í£¬Ë÷Òý±íûÓÐ index by×Ó¾ä¾ÍÊÇǶÌ×±í£¬Ëü¿ÉÒÔ´æ·ÅÓÚÊý¾ÝÖУ¬ÔªËظöÊýÎÞÏÞ£¬Ï±ê´Ó1¿ªÊ¼£¬²¢ÇÒÐèÒª³õʼ»¯
varray(20)¾Í¶¨ÒåÁ˱䳤Êý×éµÄ×î´óÔªËظöÊýÊÇ20¸ö
±ä³¤Êý×éÓëǶÌ×±íÒ»Ñù£¬Ò²¿ÉÒÔÊÇÊý¾Ý±íÁеÄÊý¾ÝÀàÐÍ¡£
ͬʱ£¬±ä³¤Êý×éµÄʹÓÃÒ²ÐèÒªÊÂÏȳõʼ»¯¡£
ÀàÐÍ ¿É´æ´¢ÓÚÊý¾Ý¿â ÔªËظöÊý ÊÇ·ñÐè³õʼ»¯ ³õʼϱêÖµ
Ë÷Òý±í ·ñ ÎÞÏÞ ²»Ðè
ǶÌ×±í ¿É ÎÞÏÞ Ðè 1
¿É±äÊý×é ¿É ÓÐÏÞ(×Ô¶¨Òå) Ðè 1
Óɴ˿ɼû£¬Èç¹û½ö½öÊÇÔÚ´æ´¢¹ý³ÌÖе±×÷¼¯ºÏ±äÁ¿Ê¹Óã¬Ë÷Òý±íÊÇ×îºÃµÄÑ¡Ôñ¡£
3.2 Ñ¡ÓúÎÖÖÓαê?
ÏÔʾÓαê·ÖΪ£ºÆÕͨÓα꣬²ÎÊý»¯ÓαêºÍÓαê±äÁ¿ÈýÖÖ¡£
ÏÂÃæÒÔÒ»¸ö¹ý³ÌÀ´½øÐÐ˵Ã÷
cursor c_postype is select pos_type from pos_type_tbl where rownum =1
ÕâÒ»¾äÊǶ¨ÒåÁËÒ»¸ö×îÆÕͨµÄÓα꣬°ÑÕû¸ö²éѯÒѾдËÀ,µ÷ÓÃʱ²»¿ÉÒÔ×÷Èκθı䡣
cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum;
ÕâÒ»¾ä²¢Ã»ÓÐдËÀ£¬²éѯ²ÎÊýÓɱäÁ¿v_rownumÀ´¾ö¶¨¡£ÐèҪעÒâµÄÊÇv_rownum±ØÐëÔÚÕâ¸öÓα궨Òå֮ǰÉùÃ÷¡£
cursor c_postype2(p_rownum number) is select pos_type from pos_type_tbl where rownum = p_rownum;
ÕâÒ»ÌõÓï¾äÓëµÚ¶þÌõ×÷ÓÃÏàËÆ£¬¶¼ÊÇ¿ÉÒÔΪÓαêʵÏÖ¶¯Ì¬µÄ²éѯ¡£µ«ÊÇËü½øÒ»²½µÄËõСÁ˲ÎÊýµÄ×÷ÓÃÓò·¶Î§¡£µ«ÊǿɶÁÐÔ½µµÍÁ˲»ÉÙ¡£
type t_postype is ref cursor ;
c_postype3 t_postype;
Ïȶ¨ÒåÁËÒ»¸öÒýÓÃÓαêÀàÐÍ£¬È»ºóÔÙÉùÃ÷ÁËÒ»¸öÓαê±äÁ¿¡£
open c_postype3 for select pos_type from pos_type_tbl where rownum =1;
È»ºóÔÙÓÃopen for À´´ò¿ªÒ»¸ö²éѯ¡£ÐèҪעÒâµÄÊÇËü¿ÉÒÔ¶à´ÎʹÓÃ,ÓÃÀ´´ò¿ª²»Í¬µÄ²éѯ¡£
´Ó¶¯Ì¬ÐÔÀ´Ëµ£¬Óαê±äÁ¿ÊÇ×îºÃÓõģ¬µ«ÊÇÔĶÁÐÔÒ²ÊÇ×î²îµÄ¡£
×¢Ò⣬ÓαêµÄ¶¨ÒåÖ»ÄÜÓÃʹ¹Ø¼ü×ÖIS£¬ËüÓëAS²»Í¨Óá£
3.3 ÓαêÑ»·×î¼Ñ²ßÂÔ
ÎÒÃÇÔÚ½øÐÐPL/SQL±à³Ìʱ£¬¾³£ÐèҪѻ·¶ÁÈ¡½á¹û¼¯µÄÊý¾Ý¡£½øÐÐÖðÐд¦Àí£¬Õâ¸ö¹ý³Ì¾ÍÐèÒª¶ÔÓαê½øÐÐÑ»·¡£¶ÔÓαê½øÐÐÑ»·µÄ·½·¨ÓжàÖÖ£¬ÎÒÃÇÔÚ´ËÒ»Ò»·ÖÎö¡£
ʹÓÃÓαê֮ǰÐèÒª¿ª´òÓαê,open cursor£¬Ñ»·ÍêºóÔٹرÕÓαêclose cursor.
ÕâÊÇʹÓÃÓαêÓ¦¸ÃÉ÷¼ÇÓÚÐĵķ¨Ôò¡£
ÉÏÃæµÄ¹ý³ÌÑÝʾÁËÓαêÑ»·µÄÈýÖÖ·½·¨¡£
ÔÚÌÖÂÛÑ»··½·¨Ö®Ç°£¬ÎÒÃÇÏÈ¿´¿´%foundºÍ%notfoundÕâЩÓαêµÄÊôÐÔ¡£
µÚÒ»ÖÖʹÓÃloop Ñ»·
´¦ÀíÂß¼ÐèÒª¸úÔÚexit whenÖ®ºó¡£ÕâÒ»µãÐèÒª¶à¼ÓСÐÄ¡£
Ñ»·½áÊøºóÒª¼ÇµÃ¹Ø±ÕÓαꡣ
µÚ¶þÖÖʹÓÃwhileÑ»·¡£
ÎÒÃÇÖªµÀÁËÒ»¸öÓαê´ò¿ªºó£¬±ØÐëÖ´ÐÐÒ»´ÎfetchÓï¾ä£¬ÓαêµÄÊôÐԲŻáÆð×÷Óá£ËùÒÔʹÓÃwhile Ñ»·Ê±£¬¾ÍÐèÒªÔÚÑ»·Ö®Ç°½øÐÐÒ»´Îfetch¶¯×÷¡£
¶øÇÒÊý¾Ý´¦Àí¶¯×÷±ØÐë·ÅÔÚÑ»·ÌåÄÚµÄfetch·½·¨Ö®Ç°¡£Ñ»·ÌåÄÚµÄfetch·½·¨Òª·ÅÔÚ×îºó¡£·ñÔò¾Í»á¶à´¦ÀíÒ»´Î¡£ÕâÒ»µãÒ²Òª·Ç³£µÄСÐÄ¡£
×ÜÖ®£¬Ê¹ÓÃwhileÀ´Ñ»·´¦ÀíÓαêÊÇ×Ôӵķ½·¨¡£
µÚÈýÖÖ forÑ»·
Ê×ÏÈ£¬Ëü»á×Ô¶¯openºÍcloseÓαꡣ½â¾öÁËÄãÍü¼Ç´ò¿ª»ò¹Ø±ÕÓαêµÄ·³ÄÕ¡£
ÆäËü,×Ô¶¯¶¨ÒåÁËÒ»¸ö¼Ç¼ÀàÐͼ°ÉùÃ÷¸ÃÀàÐ͵ıäÁ¿£¬²¢×Ô¶¯fetchÊý¾Ýµ½Õâ¸ö±äÁ¿ÖС£
ÎÒÃÇÐèҪעÒâv_pos Õâ¸ö±äÁ¿ÎÞÐèÒªÔÚÑ»·Íâ½øÐÐÉùÃ÷£¬ÎÞÐèҪΪÆäÖ¸¶¨Êý¾ÝÀàÐÍ¡£
ËüÓ¦¸ÃÊÇÒ»¸ö¼Ç¼ÀàÐÍ£¬¾ßÌåµÄ½á¹¹ÊÇÓÉÓαê¾ö¶¨µÄ¡£
Õâ¸ö±äÁ¿µÄ×÷ÓÃÓò½ö½öÊÇÔÚÑ»·ÌåÄÚ¡£
°Ñv_pos¿´×÷Ò»¸ö¼Ç¼±äÁ¿¾Í¿ÉÒÔÁË£¬Èç¹ûÒª»ñµÃijһ¸öÖµ¾ÍÏñµ÷ÓüǼһÑù¾Í¿ÉÒÔÁË¡£
Èçv_pos.pos_type
Óɴ˿ɼû£¬forÑ»·ÊÇÓÃÀ´Ñ»·ÓαêµÄ×îºÃ·½·¨¡£¸ßЧ£¬¼ò½à£¬°²È«¡£
µ«Òź¶µÄÊÇ£¬³£³£¼ûµ½µÄÈ´ÊǵÚÒ»ÖÖ·½·¨¡£ËùÒÔ´Ó½ñÖ®ºóµÃ¸Ä±äÕâ¸öÏ°¹ßÁË¡£
3.4 select into²»¿ÉºõÊÓµÄÎÊÌâ
ÎÒÃÇÖªµÀÔÚpl/sqlÖÐÒªÏë´ÓÊý¾Ý±íÖÐÏò±äÁ¿¸³Öµ£¬ÐèҪʹÓÃselect into ×Ӿ䡣
µ«ÊÇËü»á´ø¶¯À´Ò»Ð©ÎÊÌ⣬Èç¹û²éѯûÓмǼʱ£¬»áÅ׳öno_data_foundÒì³£¡£
Èç¹ûÓжàÌõ¼Ç¼ʱ£¬»áÅ׳ötoo_many_rowsÒì³£¡£
Õâ¸öÊDZȽÏÔã¸âµÄ¡£Ò»µ©Å׳öÁËÒì³££¬¾Í»áÈùý³ÌÖжϡ£ÌرðÊÇno_data_foundÕâÖÖÒì³££¬Ã»ÓÐÑÏÖص½ÒªÈóÌÐòÖжϵĵز½£¬¿ÉÒÔÍêÈ«½»¸øÓɳÌÐò½øÐд¦Àí¡£
´¦ÀíÕâ¸öÓÐÈý¸ö°ì·¨
1£® Ö±½Ó¼ÓÉÏÒì³£´¦Àí¡£
2. select into×öΪһ¸ö¶ÀÁ¢µÄ¿é£¬ÔÚÕâ¸ö¿éÖнøÐÐÒì³£´¦Àí
3.ʹÓÃÓαê
µÚ¶þÖÖÇé¿öÊÇtoo_many_rows Òì³£µÄÎÊÌâ¡£
Too_many_rows Õâ¸öÎÊÌâ±ÈÆðno_data_foundÒª¸´ÔÓһЩ¡£
¸øÒ»¸ö±äÁ¿¸³ÖµÊ±£¬µ«ÊDzéѯ½á¹ûÓжà¸ö¼Ç¼¡£
´¦ÀíÕâÖÖÎÊÌâÒ²ÓÐÁ½ÖÖÇé¿ö£º
1£® ¶àÌõÊý¾ÝÊÇ¿ÉÒÔ½ÓÊܵģ¬Ò²¾ÍÊÇ˵´Ó½á¹û¼¯ÖÐËæ±ãÈ¡Ò»¸öÖµ¾ÍÐС£ÕâÖÖÇé¿öÓ¦¸ÃºÜ¼«¶ËÁË°É£¬Èç¹û³öÏÖÕâÖÖÇé¿ö£¬Ò²ËµÃ÷Á˳ÌÐòµÄÑϽ÷ÐÔ´æÔÚÎÊÌâ¡£
2£® ¶àÌõÊý¾ÝÊDz»¿ÉÒÔ±»½ÓÊܵģ¬ÔÚÕâÖÖÇé¿ö¿Ï¶¨ÊdzÌÐòµÄÂß¼³öÁËÎÊÌ⣬Ҳ˵ÊÇ˵ÔÀ´¸ù±¾¾Í²»»áÏëµ½Ëü»á²úÉú¶àÌõ¼Ç¼¡£
¶ÔÓÚµÚÒ»ÖÖÇé¿ö£¬¾Í±ØÐë²ÉÓÃÓαêÀ´´¦Àí£¬¶ø¶ÔÓÚµÚ¶þÖÖÇé¿ö¾Í±ØÐëʹÓÃÄÚ²¿¿éÀ´´¦Àí£¬ÖØÐÂÅ׳öÒì³£¡£
¶àÌõÊý¾Ý¿ÉÒÔ½ÓÊÜ,Ëæ±ãÈ¡Ò»Ìõ£¬Õâ¸ö¸úno_data_foundµÄ´¦Àí·½Ê½Ò»Ñù£¬Ê¹ÓÃÓαꡣ
ÎÒÕâÀï½ö˵µÚ¶þÖÖÇé¿ö£¬²»¿É½ÓÊܶàÌõÊý¾Ý£¬µ«ÊDz»ÒªÍüÁË´¦Àíno_data_foundŶ¡£Õâ¾Í²»ÄÜʹÓÃÓαêÁË£¬±ØÐëʹÓÃÄÚ²¿¿é¡£
×ÜÖ®¶ÔÓÚselect intoµÄÓï¾äÐèҪעÒâÕâÁ½ÖÖÇé¿öÁË¡£ÐèÒªÍ×µ±´¦Àí°¡¡£
3.5 ÔÚ´æ´¢¹ý³ÌÖзµ»Ø½á¹û¼¯
ÎÒÃÇʹÓô洢¹ý³Ì¶¼ÊÇ·µ»ØÖµ¶¼Êǵ¥Ò»µÄ£¬ÓÐʱÎÒÃÇÐèÒª´Ó¹ý³ÌÖзµ»ØÒ»¸ö¼¯ºÏ¡£¼´¶àÌõÊý¾Ý¡£ÕâÓм¸ÖÖ½â¾ö·½°¸¡£±È½Ï¼òµ¥µÄ×ö·¨ÊÇдÁÙʱ±í£¬µ«ÊÇÕâÖÖ×ö·¨²»Áé»î¡£¶øÇÒά»¤Âé·³¡£ÎÒÃÇ¿ÉÒÔʹÓÃǶÌ×±íÀ´ÊµÏÖ.ûÓÐÒ»¸ö¼¯ºÏÀàÐÍÄܹ»ÓëjavaµÄjdbcÀàÐÍÆ¥Åä¡£Õâ¾ÍÊǶÔÏóÓë¹ØϵÊý¾Ý¿âµÄ×迹°É¡£Êý¾Ý¿âµÄ¶ÔÏó²¢²»Äܹ»Íêȫת»»Îª±à³ÌÓïÑԵĶÔÏ󣬻¹±ØÐëʹÓùØϵÊý¾Ý¿âµÄ´¦Àí·½Ê½¡£
ÔÚ°üÍ·Öж¨ÒåÁËÒ»¸öÓαê±äÁ¿,²¢°ÑËü×÷Ϊ´æ´¢¹ý³ÌµÄ²ÎÊýÀàÐÍ¡£
ÔÚ´æ´¢¹ý³ÌÖж¨ÒåÁËÒ»¸öǶÌ×±í±äÁ¿£¬¶ÔÊý¾Ýд½øǶÌ×±íÖУ¬È»ºó°ÑǶÌ×±í½øÐÐÀ
ÎÒÃÇÔÚ½øÐÐpl/sql±à³Ìʱ´ò½»µÀ×î¶àµÄ¾ÍÊÇ´æ´¢¹ý³ÌÁË¡£´æ´¢¹ý³ÌµÄ½á¹¹ÊǷdz£µÄ¼òµ¥µÄ£¬ÎÒÃÇÔÚÕâÀï³ýÁËѧϰ´æ´¢¹ý³ÌµÄ»ù±¾½á¹¹Í⣬»¹»áѧϰ±àд´æ´¢¹ý³ÌʱÏà¹ØµÄһЩʵÓõÄ֪ʶ¡£È磺ÓαêµÄ´¦Àí£¬Òì³£µÄ´¦Àí£¬¼¯ºÏµÄÑ¡ÔñµÈµÈ
1.´æ´¢¹ý³Ì½á¹¹
1.1 µÚÒ»¸ö´æ´¢¹ý³Ì
Java´úÂë
- create or replace procedure proc1(
- p_para1 varchar2,
- p_para2 out varchar2,
- p_para3 in out varchar2
- )as
- v_name varchar2(20);
- begin
- v_name := 'ÕÅÈý·á';
- p_para3 := v_name;
- dbms_output.put_line('p_para3:'||p_para3);
- end;
create or replace procedure proc1( p_para1 varchar2, p_para2 out varchar2, p_para3 in out varchar2 )as v_name varchar2(20); begin v_name := 'ÕÅÈý·á'; p_para3 := v_name; dbms_output.put_line('p_para3:'||p_para3); end;
ÉÏÃæ¾ÍÊÇÒ»¸ö×î¼òµ¥µÄ´æ´¢¹ý³Ì¡£Ò»¸ö´æ´¢¹ý³Ì´óÌå·ÖΪÕâô¼¸¸ö²¿·Ö:
´´½¨Óï¾ä£ºcreate or replace procedure ´æ´¢¹ý³ÌÃû
Èç¹ûûÓÐor replaceÓï¾ä£¬Ôò½ö½öÊÇн¨Ò»¸ö´æ´¢¹ý³Ì¡£Èç¹ûϵͳ´æÔڸô洢¹ý³Ì£¬Ôò»á±¨´í¡£Create or replace procedure Èç¹ûϵͳÖÐûÓд˴洢¹ý³Ì¾Íн¨Ò»¸ö£¬Èç¹ûϵͳÖÐÓд˴洢¹ý³ÌÔò°ÑÔÀ´É¾³ýµô£¬ÖØд´½¨Ò»¸ö´æ´¢¹ý³Ì¡£
´æ´¢¹ý³ÌÃû¶¨Ò壺°üÀ¨´æ´¢¹ý³ÌÃûºÍ²ÎÊýÁÐ±í¡£²ÎÊýÃûºÍ²ÎÊýÀàÐÍ¡£²ÎÊýÃû²»ÄÜÖظ´£¬ ²ÎÊý´«µÝ·½Ê½:IN, OUT, IN OUT
IN ±íʾÊäÈë²ÎÊý£¬°´Öµ´«µÝ·½Ê½¡£
OUT ±íʾÊä³ö²ÎÊý£¬¿ÉÒÔÀí½âΪ°´ÒýÓô«µÝ·½Ê½¡£¿ÉÒÔ×÷Ϊ´æ´¢¹ý³ÌµÄÊä³ö½á¹û£¬¹©Íⲿµ÷ÓÃÕßʹÓá£
IN OUT ¼´¿É×÷ÊäÈë²ÎÊý£¬Ò²¿É×÷Êä³ö²ÎÊý¡£
²ÎÊýµÄÊý¾ÝÀàÐÍÖ»ÐèÒªÖ¸Ã÷ÀàÐÍÃû¼´¿É£¬²»ÐèÒªÖ¸¶¨¿í¶È¡£
²ÎÊýµÄ¿í¶ÈÓÉÍⲿµ÷ÓÃÕß¾ö¶¨¡£
¹ý³Ì¿ÉÒÔÓвÎÊý£¬Ò²¿ÉÒÔûÓвÎÊý
±äÁ¿ÉùÃ÷¿é£º½ô¸ú×ŵÄas (is )¹Ø¼ü×Ö£¬¿ÉÒÔÀí½âΪpl/sqlµÄdeclare¹Ø¼ü×Ö£¬ÓÃÓÚÉùÃ÷±äÁ¿¡£
±äÁ¿ÉùÃ÷¿éÓÃÓÚÉùÃ÷¸Ã´æ´¢¹ý³ÌÐèÒªÓõ½µÄ±äÁ¿£¬ËüµÄ×÷ÓÃÓòΪ¸Ã´æ´¢¹ý³Ì¡£ÁíÍâÕâÀïÉùÃ÷µÄ±äÁ¿±ØÐëÖ¸¶¨¿í¶È¡£×ñÑPL/SQLµÄ±äÁ¿ÉùÃ÷¹æ·¶¡£
¹ý³ÌÓï¾ä¿é£º´Óbegin ¹Ø¼ü×Ö¿ªÊ¼Îª¹ý³ÌµÄÓï¾ä¿é¡£´æ´¢¹ý³ÌµÄ¾ßÌåÂß¼ÔÚÕâÀïÀ´ÊµÏÖ¡£
Òì³£´¦Àí¿é£º¹Ø¼ü×ÖΪexception £¬Îª´¦ÀíÓï¾ä²úÉúµÄÒì³£¡£¸Ã²¿·ÖΪ¿ÉÑ¡
½áÊø¿é£ºÓÉend¹Ø¼ü×Ö½á¹û¡£
1.2 ´æ´¢¹ý³ÌµÄ²ÎÊý´«µÝ·½Ê½
´æ´¢¹ý³ÌµÄ²ÎÊý´«µÝÓÐÈýÖÖ·½Ê½:IN,OUT,IN OUT .
IN °´Öµ´«µÝ£¬²¢ÇÒËü²»ÔÊÐíÔÚ´æ´¢¹ý³ÌÖб»ÖØи³Öµ¡£Èç¹û´æ´¢¹ý³ÌµÄ²ÎÊýûÓÐÖ¸¶¨´æ²ÎÊý´«µÝÀàÐÍ£¬Ä¬ÈÏΪIN
- create or replace procedure proc1(
- p_para1 varchar2,
- p_para2 out varchar2,
- p_para3 in out varchar2
- )as
- v_name varchar2(20);
- begin
- p_para1 :='aaa';
- p_para2 :='bbb';
- v_name := 'ÕÅÈý·á';
- p_para3 := v_name;
- dbms_output.put_line('p_para3:'||p_para3);
- null;
- end;
- Warning: Procedure created with compilation errors
- SQL> show error;
- Errors for PROCEDURE LIFEMAN.PROC1:
- LINE/COL ERROR
- -------- ----------------------------------------------------------------------
- 8/3 PLS-00363: expression 'P_PARA1' cannot be used as an assignment target
- 8/3 PL/SQL: Statement ignored
create or replace procedure proc1( p_para1 varchar2, p_para2 out varchar2, p_para3 in out varchar2 )as v_name varchar2(20); begin p_para1 :='aaa'; p_para2 :='bbb'; v_name := 'ÕÅÈý·á'; p_para3 := v_name; dbms_output.put_line('p_para3:'||p_para3); null; end; Warning: Procedure created with compilation errors SQL> show error; Errors for PROCEDURE LIFEMAN.PROC1: LINE/COL ERROR -------- ---------------------------------------------------------------------- 8/3 PLS-00363: expression 'P_PARA1' cannot be used as an assignment target 8/3 PL/SQL: Statement ignoredÕâÒ»µãÓëÆäËü¸ß¼¶ÓïÑÔ¶¼²»Í¬¡£ËüÏ൱ÓÚjavaÔÚ²ÎÊýÇ°Ãæ¼ÓÉÏfinal¹Ø¼ü×Ö¡£
OUT ²ÎÊý£º×÷ΪÊä³ö²ÎÊý£¬ÐèҪעÒ⣬µ±Ò»¸ö²ÎÊý±»Ö¸¶¨ÎªOUTÀàÐÍʱ£¬¾ÍËãÔÚµ÷Óô洢¹ý³Ì֮ǰ¶Ô¸Ã²ÎÊý½øÐÐÁ˸³Öµ£¬ÔÚ´æ´¢¹ý³ÌÖиòÎÊýµÄÖµÈÔÈ»ÊÇnull.
- create or replace procedure proc1(
- p_para1 varchar2,
- p_para2 out varchar2,
- p_para3 in out varchar2
- )as
- v_name varchar2(20);
- begin
- v_name := 'ÕÅÈý·á';
- p_para3 := v_name;
- dbms_output.put_line('p_para1:'||p_para1);
- dbms_output.put_line('p_para2:'||p_para2);
- dbms_output.put_line('p_para3:'||p_para3);
- end;
- SQL> var p1 varchar2(10);
- SQL> var p2 varchar2(10);
- SQL> var p3 varchar2(10);
- SQL> exec :p1 :='aaaa';
- SQL> exec :p2 :='bbbb';
- SQL> exec :p3 :='cccc';
- SQL> exec proc1(:p1,:p2,:p3);
- p_para1:aaaa
- p_para2:
- p_para3:ÕÅÈý·á
- SQL> exec dbms_output.put_line(:p2);
- PL/SQL procedure successfully completed
- p2
- ---------
create or replace procedure proc1( p_para1 varchar2, p_para2 out varchar2, p_para3 in out varchar2 )as v_name varchar2(20); begin v_name := 'ÕÅÈý·á'; p_para3 := v_name; dbms_output.put_line('p_para1:'||p_para1); dbms_output.put_line('p_para2:'||p_para2); dbms_output.put_line('p_para3:'||p_para3); end; SQL> var p1 varchar2(10); SQL> var p2 varchar2(10); SQL> var p3 varchar2(10); SQL> exec :p1 :='aaaa'; SQL> exec :p2 :='bbbb'; SQL> exec :p3 :='cccc'; SQL> exec proc1(:p1,:p2,:p3); p_para1:aaaa p_para2: p_para3:ÕÅÈý·á SQL> exec dbms_output.put_line(:p2); PL/SQL procedure successfully completed p2 ---------
INOUT ÊÇÕæÕýµÄ°´ÒýÓô«µÝ²ÎÊý¡£¼´¿É×÷Ϊ´«Èë²ÎÊýÒ²¿ÉÒÔ×÷Ϊ´«³ö²ÎÊý¡£
- 1.3 ´æ´¢¹ý³Ì²ÎÊý¿í¶È
- create or replace procedure proc1(
- p_para1 varchar2,
- p_para2 out varchar2,
- p_para3 in out varchar2
- )as
- v_name varchar2(2);
- begin
- v_name := p_para1;
- end;
- SQL> var p1 varchar2(10);
- SQL> var p2 varchar2(20);
- SQL> var p3 varchar2(30);
- SQL> exec :p1 :='aaaaaa';
- SQL> exec proc1(:p1,:p2,:p3);
- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
- ORA-06512: at "LIFEMAN.PROC1", line 8
- ORA-06512: at line 1
1.3 ´æ´¢¹ý³Ì²ÎÊý¿í¶È create or replace procedure proc1( p_para1 varchar2, p_para2 out varchar2, p_para3 in out varchar2 )as v_name varchar2(2); begin v_name := p_para1; end; SQL> var p1 varchar2(10); SQL> var p2 varchar2(20); SQL> var p3 varchar2(30); SQL> exec :p1 :='aaaaaa'; SQL> exec proc1(:p1,:p2,:p3); ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "LIFEMAN.PROC1", line 8 ORA-06512: at line 1
Ê×ÏÈ£¬ÎÒÃÇÒªÃ÷°×£¬ÎÒÃÇÎÞ·¨ÔÚ´æ´¢¹ý³ÌµÄ¶¨ÒåÖÐÖ¸¶¨´æ´¢²ÎÊýµÄ¿í¶È£¬Ò²¾Íµ¼ÖÂÁËÎÒÃÇÎÞ·¨ÔÚ´æ´¢¹ý³ÌÖпØÖÆ´«Èë±äÁ¿µÄ¿í¶È¡£Õâ¸ö¿í¶ÈÊÇÍêÈ«ÓÉÍⲿ´«Èëʱ¾ö¶¨µÄ¡£
ÎÒÃÇÔÙÀ´¿´¿´OUTÀàÐ͵IJÎÊýµÄ¿í¶È¡£
- create or replace procedure proc1(
- p_para1 varchar2,
- p_para2 out varchar2,
- p_para3 in out varchar2
- )as
- v_name varchar2(2);
- begin
- p_para2 :='aaaaaaaaaaaaaaaaaaaa';
- end;
- SQL> var p1 varchar2(1);
- SQL> var p2 varchar2(1);
- SQL> var p3 varchar2(1);
- SQL> exec :p2 :='a';
- SQL> exec proc1(:p1,:p2,:p3);
create or replace procedure proc1( p_para1 varchar2, p_para2 out varchar2, p_para3 in out varchar2 )as v_name varchar2(2); begin p_para2 :='aaaaaaaaaaaaaaaaaaaa'; end; SQL> var p1 varchar2(1); SQL> var p2 varchar2(1); SQL> var p3 varchar2(1); SQL> exec :p2 :='a'; SQL> exec proc1(:p1,:p2,:p3);Ôڸùý³ÌÖУ¬p_para2±»¸³ÓèÁË20¸ö×Ö·ûa.
¶øÔÚÍⲿµÄµ÷Óùý³ÌÖУ¬p2Õâ¸ö²ÎÊý½ö½ö±»¶¨ÒåΪvarchar2(1).
¶ø°Ñp2×÷Ϊ²ÎÊýµ÷ÓÃÕâ¸ö¹ý³Ì£¬È´²¢Ã»Óб¨´í¡£¶øÇÒËüµÄÕæʵֵ¾ÍÊÇ20¸öa
- SQL> select dump(:p2) from dual;
- DUMP(:P2)
- ---------------------------------------------------------------------------
- Typ=1 Len=20: 97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97
- p2
- ---------
- aaaaaaaaaaaaaaaaaaaa
- ÔÙÀ´¿´¿´IN OUT²ÎÊýµÄ¿í¶È
- create or replace procedure proc1(
- p_para1 varchar2,
- p_para2 out varchar2,
- p_para3 in out varchar2
- )as
- v_name varchar2(2);
- begin
- p_para3 :='aaaaaaaaaaaaaaaaaaaa';
- end;
- SQL> var p1 varchar2(1);
- SQL> var p2 varchar2(1);
- SQL> var p3 varchar2(1);
- SQL> exec proc1(:p1,:p2,:p3);
SQL> select dump(:p2) from dual; DUMP(:P2) --------------------------------------------------------------------------- Typ=1 Len=20: 97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97 p2 --------- aaaaaaaaaaaaaaaaaaaa ÔÙÀ´¿´¿´IN OUT²ÎÊýµÄ¿í¶È create or replace procedure proc1( p_para1 varchar2, p_para2 out varchar2, p_para3 in out varchar2 )as v_name varchar2(2); begin p_para3 :='aaaaaaaaaaaaaaaaaaaa'; end; SQL> var p1 varchar2(1); SQL> var p2 varchar2(1); SQL> var p3 varchar2(1); SQL> exec proc1(:p1,:p2,:p3);Ö´ÐÐÕâ¸ö¹ý³Ì£¬ÈÔÈ»ÕýÈ·Ö´ÐС£
¿É¼û£¬¶ÔÓÚIN²ÎÊý£¬Æä¿í¶ÈÊÇÓÉÍⲿ¾ö¶¨¡£
¶ÔÓÚOUT ºÍIN OUT ²ÎÊý£¬Æä¿í¶ÈÊÇÓÉ´æ´¢¹ý³ÌÄÚ²¿¾ö¶¨¡£
Òò´Ë£¬ÔÚд´æ´¢¹ý³Ìʱ£¬¶Ô²ÎÊýµÄ¿í¶È½øÐÐ˵Ã÷ÊǷdz£ÓбØÒªµÄ£¬×îÃ÷Öǵķ½·¨¾ÍÊDzÎÊýµÄÊý¾ÝÀàÐÍʹÓÃ%type¡£ÕâÑùË«·½¾Í´ï³ÉÁËÒ»Ö¡£
1.3 ²ÎÊýµÄĬÈÏÖµ
´æ´¢¹ý³ÌµÄ²ÎÊý¿ÉÒÔÉèÖÃĬÈÏÖµ
- create or replace procedure procdefault(p1 varchar2,
- p2 varchar2 default 'mark')
- as
- begin
- dbms_output.put_line(p2);
- end;
- SQL> set serveroutput on;
- SQL> exec procdefault('a');
create or replace procedure procdefault(p1 varchar2, p2 varchar2 default 'mark') as begin dbms_output.put_line(p2); end; SQL> set serveroutput on; SQL> exec procdefault('a');mark
¿ÉÒÔͨ¹ýdefault ¹Ø¼ü×ÖΪ´æ´¢¹ý³ÌµÄ²ÎÊýÖ¸¶¨Ä¬ÈÏÖµ¡£ÔÚ¶Ô´æ´¢¹ý³Ìµ÷ÓÃʱ£¬¾Í¿ÉÒÔÊ¡ÂÔĬÈÏÖµ¡£
ÐèҪעÒâµÄÊÇ£ºÄ¬ÈÏÖµ½ö½öÖ§³ÖIN´«ÊäÀàÐ͵IJÎÊý¡£OUT ºÍ IN OUT²»ÄÜÖ¸¶¨Ä¬ÈÏÖµ
¶ÔÓÚÓÐĬÈÏÖµµÄ²ÎÊý²»ÊÇÅÅÔÚ×îºóµÄÇé¿ö¡£
- create or replace procedure procdefault2(p1 varchar2 default 'remark',
- p2 varchar2 )
- as
- begin
- dbms_output.put_line(p1);
- end;
create or replace procedure procdefault2(p1 varchar2 default 'remark', p2 varchar2 ) as begin dbms_output.put_line(p1); end;µÚÒ»¸ö²ÎÊýÓÐĬÈÏÖµ£¬µÚ¶þ¸ö²ÎÊýûÓС£Èç¹ûÎÒÃÇÏëʹÓõÚÒ»¸ö²ÎÊýµÄĬÈÏֵʱ
exec procdefault2('aa');
ÕâÑùÊǻᱨ´íµÄ¡£
ÄÇÔõô±äÄØ£¿¿ÉÒÔÖ¸¶¨²ÎÊýµÄÖµ¡£
SQL> exec procdefault2(p2 =>'aa');
remark
ÕâÑù¾ÍOKÁË£¬Ö¸¶¨aa´«¸ø²ÎÊýp2
2. ´æ´¢¹ý³ÌÄÚ²¿¿é
2.1 ÄÚ²¿¿é
ÎÒÃÇÖªµÀÁË´æ´¢¹ý³ÌµÄ½á¹¹£¬Óï¾ä¿éÓÉbegin¿ªÊ¼£¬ÒÔend½áÊø¡£ÕâЩ¿éÊÇ¿ÉÒÔǶÌס£ÔÚÓï¾ä¿éÖпÉÒÔǶÌ×ÈκÎÒÔϵĿ顣
- Declare ¡ begin ¡ exception ¡ end;
- create or replace procedure innerBlock(p1 varchar2)
- as
- o1 varchar2(10) := 'out1';
- begin
- dbms_output.put_line(o1);
- declare
- inner1 varchar2(20);
- begin
- inner1 :='inner1';
- dbms_output.put_line(inner1);
- declare
- inner2 varchar2(20);
- begin
- inner2 := 'inner2';
- dbms_output.put_line(inner2);
- end;
- exception
- when others then
- null;
- end;
- end;
Declare ¡ begin ¡ exception ¡ end; create or replace procedure innerBlock(p1 varchar2) as o1 varchar2(10) := 'out1'; begin dbms_output.put_line(o1); declare inner1 varchar2(20); begin inner1 :='inner1'; dbms_output.put_line(inner1); declare inner2 varchar2(20); begin inner2 := 'inner2'; dbms_output.put_line(inner2); end; exception when others then null; end; end;ÐèҪעÒâ±äÁ¿µÄ×÷ÓÃÓò¡£
3.´æ´¢¹ý³ÌµÄ³£Óü¼ÇÉ
3.1 ÄÄÖÖ¼¯ºÏ?
ÎÒÃÇÔÚʹÓô洢¹ý³ÌµÄʱºò¾³£ÐèÒª´¦Àí¼Ç¼¼¯£¬Ò²¾ÍÊǶàÌõÊý¾Ý¼Ç¼¡£·ÖΪµ¥ÁжàÐкͶàÁжàÐУ¬ÕâЩÀàÐͶ¼¿ÉÒÔ³ÆΪ¼¯ºÏÀàÐÍ¡£ÎÒÃÇÔÚÕâÀï½øÐбȽÏÕâЩ¼¯ºÏÀàÐÍ£¬ÒÔ±ãÓÚÔÚ±à³Ìʱ×ö³öÕýÈ·µÄÑ¡Ôñ¡£
Ë÷Òý±í,Ò²³ÆΪpl/sql±í£¬²»ÄÜ´æ´¢ÓÚÊý¾Ý¿âÖУ¬ÔªËصĸöÊýûÓÐÏÞÖÆ,ϱê¿ÉÒÔΪ¸ºÖµ¡£
type t_table is table of varchar2(20) index by binary_integer; v_student t_table;varchar2(20)±íʾ´æ·ÅÔªËصÄÊý¾ÝÀàÐÍ£¬binary_integer±íʾԪËØϱêµÄÊý¾ÝÀàÐÍ¡£
ǶÌ×±í£¬Ë÷Òý±íûÓÐ index by×Ó¾ä¾ÍÊÇǶÌ×±í£¬Ëü¿ÉÒÔ´æ·ÅÓÚÊý¾ÝÖУ¬ÔªËظöÊýÎÞÏÞ£¬Ï±ê´Ó1¿ªÊ¼£¬²¢ÇÒÐèÒª³õʼ»¯
type t_nestTable is table of varchar2(20); v_class t_nestTable ;½öÊÇÕâÑùÉùÃ÷ÊDz»ÄÜʹÓõģ¬±ØÐë¶ÔǶÌ×±í½øÐгõʼ»¯£¬¶ÔǶÌ×±í½øÐгõʼ»¯¿ÉÒÔʹÓÃËüµÄ¹¹Ô캯Êý
v_class :=t_nestTable('a','b','c');±ä³¤Êý×飬±ä³¤Êý×éÓë¸ß¼¶ÓïÑÔµÄÊý×éÀàÐͷdz£ÏàËÆ£¬Ï±êÒÔ1¿ªÊ¼£¬ÔªËظöÊýÓÐÏÞ¡£
type t_array is varray (20) of varchar2(20);
varray(20)¾Í¶¨ÒåÁ˱䳤Êý×éµÄ×î´óÔªËظöÊýÊÇ20¸ö
±ä³¤Êý×éÓëǶÌ×±íÒ»Ñù£¬Ò²¿ÉÒÔÊÇÊý¾Ý±íÁеÄÊý¾ÝÀàÐÍ¡£
ͬʱ£¬±ä³¤Êý×éµÄʹÓÃÒ²ÐèÒªÊÂÏȳõʼ»¯¡£
ÀàÐÍ ¿É´æ´¢ÓÚÊý¾Ý¿â ÔªËظöÊý ÊÇ·ñÐè³õʼ»¯ ³õʼϱêÖµ
Ë÷Òý±í ·ñ ÎÞÏÞ ²»Ðè
ǶÌ×±í ¿É ÎÞÏÞ Ðè 1
¿É±äÊý×é ¿É ÓÐÏÞ(×Ô¶¨Òå) Ðè 1
Óɴ˿ɼû£¬Èç¹û½ö½öÊÇÔÚ´æ´¢¹ý³ÌÖе±×÷¼¯ºÏ±äÁ¿Ê¹Óã¬Ë÷Òý±íÊÇ×îºÃµÄÑ¡Ôñ¡£
3.2 Ñ¡ÓúÎÖÖÓαê?
ÏÔʾÓαê·ÖΪ£ºÆÕͨÓα꣬²ÎÊý»¯ÓαêºÍÓαê±äÁ¿ÈýÖÖ¡£
ÏÂÃæÒÔÒ»¸ö¹ý³ÌÀ´½øÐÐ˵Ã÷
- create or replace procedure proccursor(p varchar2)
- as
- v_rownum number(10) := 1;
- cursor c_postype is select pos_type from pos_type_tbl where rownum =1;
- cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum;
- cursor c_postype2(p_rownum number) is select pos_type from pos_type_tbl where rownum = p_rownum;
- type t_postype is ref cursor ;
- c_postype3 t_postype;
- v_postype varchar2(20);
- begin
- open c_postype;
- fetch c_postype into v_postype;
- dbms_output.put_line(v_postype);
- close c_postype;
- open c_postype1;
- fetch c_postype1 into v_postype;
- dbms_output.put_line(v_postype);
- close c_postype1;
- open c_postype2(1);
- fetch c_postype2 into v_postype;
- dbms_output.put_line(v_postype);
- close c_postype2;
- open c_postype3 for select pos_type from pos_type_tbl where rownum =1;
- fetch c_postype3 into v_postype;
- dbms_output.put_line(v_postype);
- close c_postype3;
- end;
create or replace procedure proccursor(p varchar2) as v_rownum number(10) := 1; cursor c_postype is select pos_type from pos_type_tbl where rownum =1; cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum; cursor c_postype2(p_rownum number) is select pos_type from pos_type_tbl where rownum = p_rownum; type t_postype is ref cursor ; c_postype3 t_postype; v_postype varchar2(20); begin open c_postype; fetch c_postype into v_postype; dbms_output.put_line(v_postype); close c_postype; open c_postype1; fetch c_postype1 into v_postype; dbms_output.put_line(v_postype); close c_postype1; open c_postype2(1); fetch c_postype2 into v_postype; dbms_output.put_line(v_postype); close c_postype2; open c_postype3 for select pos_type from pos_type_tbl where rownum =1; fetch c_postype3 into v_postype; dbms_output.put_line(v_postype); close c_postype3; end;
cursor c_postype is select pos_type from pos_type_tbl where rownum =1
ÕâÒ»¾äÊǶ¨ÒåÁËÒ»¸ö×îÆÕͨµÄÓα꣬°ÑÕû¸ö²éѯÒѾдËÀ,µ÷ÓÃʱ²»¿ÉÒÔ×÷Èκθı䡣
cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum;
ÕâÒ»¾ä²¢Ã»ÓÐдËÀ£¬²éѯ²ÎÊýÓɱäÁ¿v_rownumÀ´¾ö¶¨¡£ÐèҪעÒâµÄÊÇv_rownum±ØÐëÔÚÕâ¸öÓα궨Òå֮ǰÉùÃ÷¡£
cursor c_postype2(p_rownum number) is select pos_type from pos_type_tbl where rownum = p_rownum;
ÕâÒ»ÌõÓï¾äÓëµÚ¶þÌõ×÷ÓÃÏàËÆ£¬¶¼ÊÇ¿ÉÒÔΪÓαêʵÏÖ¶¯Ì¬µÄ²éѯ¡£µ«ÊÇËü½øÒ»²½µÄËõСÁ˲ÎÊýµÄ×÷ÓÃÓò·¶Î§¡£µ«ÊǿɶÁÐÔ½µµÍÁ˲»ÉÙ¡£
type t_postype is ref cursor ;
c_postype3 t_postype;
Ïȶ¨ÒåÁËÒ»¸öÒýÓÃÓαêÀàÐÍ£¬È»ºóÔÙÉùÃ÷ÁËÒ»¸öÓαê±äÁ¿¡£
open c_postype3 for select pos_type from pos_type_tbl where rownum =1;
È»ºóÔÙÓÃopen for À´´ò¿ªÒ»¸ö²éѯ¡£ÐèҪעÒâµÄÊÇËü¿ÉÒÔ¶à´ÎʹÓÃ,ÓÃÀ´´ò¿ª²»Í¬µÄ²éѯ¡£
´Ó¶¯Ì¬ÐÔÀ´Ëµ£¬Óαê±äÁ¿ÊÇ×îºÃÓõģ¬µ«ÊÇÔĶÁÐÔÒ²ÊÇ×î²îµÄ¡£
×¢Ò⣬ÓαêµÄ¶¨ÒåÖ»ÄÜÓÃʹ¹Ø¼ü×ÖIS£¬ËüÓëAS²»Í¨Óá£
3.3 ÓαêÑ»·×î¼Ñ²ßÂÔ
ÎÒÃÇÔÚ½øÐÐPL/SQL±à³Ìʱ£¬¾³£ÐèҪѻ·¶ÁÈ¡½á¹û¼¯µÄÊý¾Ý¡£½øÐÐÖðÐд¦Àí£¬Õâ¸ö¹ý³Ì¾ÍÐèÒª¶ÔÓαê½øÐÐÑ»·¡£¶ÔÓαê½øÐÐÑ»·µÄ·½·¨ÓжàÖÖ£¬ÎÒÃÇÔÚ´ËÒ»Ò»·ÖÎö¡£
- create or replace procedure proccycle(p varchar2)
- as
- cursor c_postype is select pos_type, description from pos_type_tbl where rownum < 6;
- v_postype varchar2(20);
- v_description varchar2(50);
- begin
- open c_postype;
- if c_postype%found then
- dbms_output.put_line('found true');
- elsif c_postype%found = false then
- dbms_output.put_line('found false');
- else
- dbms_output.put_line('found null');
- end if;
- loop
- fetch c_postype into v_postype,v_description ;
- exit when c_postype%notfound;
- dbms_output.put_line('postype:'||v_postype||',description:'||v_description);
- end loop;
- close c_postype;
- dbms_output.put_line('---loop end---');
- open c_postype;
- fetch c_postype into v_postype,v_description;
- while c_postype%found loop
- dbms_output.put_line('postype:'||v_postype||',description:'||v_description);
- fetch c_postype into v_postype,v_description ;
- end loop;
- close c_postype;
- dbms_output.put_line('---while end---');
- for v_pos in c_postype loop
- v_postype := v_pos.pos_type;
- v_description := v_pos.description;
- dbms_output.put_line('postype:'||v_postype||',description:'||v_description);
- end loop;
- dbms_output.put_line('---for end---');
- end;
create or replace procedure proccycle(p varchar2) as cursor c_postype is select pos_type, description from pos_type_tbl where rownum < 6; v_postype varchar2(20); v_description varchar2(50); begin open c_postype; if c_postype%found then dbms_output.put_line('found true'); elsif c_postype%found = false then dbms_output.put_line('found false'); else dbms_output.put_line('found null'); end if; loop fetch c_postype into v_postype,v_description ; exit when c_postype%notfound; dbms_output.put_line('postype:'||v_postype||',description:'||v_description); end loop; close c_postype; dbms_output.put_line('---loop end---'); open c_postype; fetch c_postype into v_postype,v_description; while c_postype%found loop dbms_output.put_line('postype:'||v_postype||',description:'||v_description); fetch c_postype into v_postype,v_description ; end loop; close c_postype; dbms_output.put_line('---while end---'); for v_pos in c_postype loop v_postype := v_pos.pos_type; v_description := v_pos.description; dbms_output.put_line('postype:'||v_postype||',description:'||v_description); end loop; dbms_output.put_line('---for end---'); end;
ʹÓÃÓαê֮ǰÐèÒª¿ª´òÓαê,open cursor£¬Ñ»·ÍêºóÔٹرÕÓαêclose cursor.
ÕâÊÇʹÓÃÓαêÓ¦¸ÃÉ÷¼ÇÓÚÐĵķ¨Ôò¡£
ÉÏÃæµÄ¹ý³ÌÑÝʾÁËÓαêÑ»·µÄÈýÖÖ·½·¨¡£
ÔÚÌÖÂÛÑ»··½·¨Ö®Ç°£¬ÎÒÃÇÏÈ¿´¿´%foundºÍ%notfoundÕâЩÓαêµÄÊôÐÔ¡£
- open c_postype;
- if c_postype%found then
- dbms_output.put_line('found true');
- elsif c_postype%found = false then
- dbms_output.put_line('found false');
- else
- dbms_output.put_line('found null');
- end if;
open c_postype; if c_postype%found then dbms_output.put_line('found true'); elsif c_postype%found = false then dbms_output.put_line('found false'); else dbms_output.put_line('found null'); end if;ÔÚ´ò¿ªÒ»¸öÓαêÖ®ºó£¬ÂíÉϼì²éËüµÄ%found»ò%notfoundÊôÐÔ£¬ËüµÃµ½µÄ½á¹û¼´²»ÊÇtrueÒ²²»ÊÇfalse.¶øÊÇnull.±ØÐëÖ´ÐÐÒ»ÌõfetchÓï¾äºó£¬ÕâЩÊôÐÔ²ÅÓÐÖµ¡£
µÚÒ»ÖÖʹÓÃloop Ñ»·
- loop
- fetch c_postype into v_postype,v_description ;
- exit when c_postype%notfound;
- ¡¡
- end loop
loop fetch c_postype into v_postype,v_description ; exit when c_postype%notfound; ¡¡ end loopÕâÀïÐèҪעÒ⣬exit whenÓï¾äÒ»¶¨Òª½ô¸úÔÚfetchÖ®ºó¡£±Ø±ÜÃâ¶àÓàµÄÊý¾Ý´¦Àí¡£
´¦ÀíÂß¼ÐèÒª¸úÔÚexit whenÖ®ºó¡£ÕâÒ»µãÐèÒª¶à¼ÓСÐÄ¡£
Ñ»·½áÊøºóÒª¼ÇµÃ¹Ø±ÕÓαꡣ
µÚ¶þÖÖʹÓÃwhileÑ»·¡£
- fetch c_postype into v_postype,v_description;
- while c_postype%found loop
- ¡¡
- fetch c_postype into v_postype,v_description ;
- end loop;
fetch c_postype into v_postype,v_description; while c_postype%found loop ¡¡ fetch c_postype into v_postype,v_description ; end loop;
ÎÒÃÇÖªµÀÁËÒ»¸öÓαê´ò¿ªºó£¬±ØÐëÖ´ÐÐÒ»´ÎfetchÓï¾ä£¬ÓαêµÄÊôÐԲŻáÆð×÷Óá£ËùÒÔʹÓÃwhile Ñ»·Ê±£¬¾ÍÐèÒªÔÚÑ»·Ö®Ç°½øÐÐÒ»´Îfetch¶¯×÷¡£
¶øÇÒÊý¾Ý´¦Àí¶¯×÷±ØÐë·ÅÔÚÑ»·ÌåÄÚµÄfetch·½·¨Ö®Ç°¡£Ñ»·ÌåÄÚµÄfetch·½·¨Òª·ÅÔÚ×îºó¡£·ñÔò¾Í»á¶à´¦ÀíÒ»´Î¡£ÕâÒ»µãÒ²Òª·Ç³£µÄСÐÄ¡£
×ÜÖ®£¬Ê¹ÓÃwhileÀ´Ñ»·´¦ÀíÓαêÊÇ×Ôӵķ½·¨¡£
µÚÈýÖÖ forÑ»·
- for v_pos in c_postype loop
- v_postype := v_pos.pos_type;
- v_description := v_pos.description;
- ¡
- end loop;
for v_pos in c_postype loop v_postype := v_pos.pos_type; v_description := v_pos.description; ¡ end loop;¿É¼ûforÑ»·ÊDZȽϼòµ¥ÊµÓõķ½·¨¡£
Ê×ÏÈ£¬Ëü»á×Ô¶¯openºÍcloseÓαꡣ½â¾öÁËÄãÍü¼Ç´ò¿ª»ò¹Ø±ÕÓαêµÄ·³ÄÕ¡£
ÆäËü,×Ô¶¯¶¨ÒåÁËÒ»¸ö¼Ç¼ÀàÐͼ°ÉùÃ÷¸ÃÀàÐ͵ıäÁ¿£¬²¢×Ô¶¯fetchÊý¾Ýµ½Õâ¸ö±äÁ¿ÖС£
ÎÒÃÇÐèҪעÒâv_pos Õâ¸ö±äÁ¿ÎÞÐèÒªÔÚÑ»·Íâ½øÐÐÉùÃ÷£¬ÎÞÐèҪΪÆäÖ¸¶¨Êý¾ÝÀàÐÍ¡£
ËüÓ¦¸ÃÊÇÒ»¸ö¼Ç¼ÀàÐÍ£¬¾ßÌåµÄ½á¹¹ÊÇÓÉÓαê¾ö¶¨µÄ¡£
Õâ¸ö±äÁ¿µÄ×÷ÓÃÓò½ö½öÊÇÔÚÑ»·ÌåÄÚ¡£
°Ñv_pos¿´×÷Ò»¸ö¼Ç¼±äÁ¿¾Í¿ÉÒÔÁË£¬Èç¹ûÒª»ñµÃijһ¸öÖµ¾ÍÏñµ÷ÓüǼһÑù¾Í¿ÉÒÔÁË¡£
Èçv_pos.pos_type
Óɴ˿ɼû£¬forÑ»·ÊÇÓÃÀ´Ñ»·ÓαêµÄ×îºÃ·½·¨¡£¸ßЧ£¬¼ò½à£¬°²È«¡£
µ«Òź¶µÄÊÇ£¬³£³£¼ûµ½µÄÈ´ÊǵÚÒ»ÖÖ·½·¨¡£ËùÒÔ´Ó½ñÖ®ºóµÃ¸Ä±äÕâ¸öÏ°¹ßÁË¡£
3.4 select into²»¿ÉºõÊÓµÄÎÊÌâ
ÎÒÃÇÖªµÀÔÚpl/sqlÖÐÒªÏë´ÓÊý¾Ý±íÖÐÏò±äÁ¿¸³Öµ£¬ÐèҪʹÓÃselect into ×Ӿ䡣
µ«ÊÇËü»á´ø¶¯À´Ò»Ð©ÎÊÌ⣬Èç¹û²éѯûÓмǼʱ£¬»áÅ׳öno_data_foundÒì³£¡£
Èç¹ûÓжàÌõ¼Ç¼ʱ£¬»áÅ׳ötoo_many_rowsÒì³£¡£
Õâ¸öÊDZȽÏÔã¸âµÄ¡£Ò»µ©Å׳öÁËÒì³££¬¾Í»áÈùý³ÌÖжϡ£ÌرðÊÇno_data_foundÕâÖÖÒì³££¬Ã»ÓÐÑÏÖص½ÒªÈóÌÐòÖжϵĵز½£¬¿ÉÒÔÍêÈ«½»¸øÓɳÌÐò½øÐд¦Àí¡£
- create or replace procedure procexception(p varchar2)
- as
- v_postype varchar2(20);
- begin
- select pos_type into v_postype from pos_type_tbl where 1=0;
- dbms_output.put_line(v_postype);
- end;
create or replace procedure procexception(p varchar2) as v_postype varchar2(20); begin select pos_type into v_postype from pos_type_tbl where 1=0; dbms_output.put_line(v_postype); end;Ö´ÐÐÕâ¸ö¹ý³Ì
- SQL> exec procexception('a');
- ±¨´í
- ORA-01403: no data found
- ORA-06512: at "LIFEMAN.PROCEXCEPTION", line 6
- ORA-06512: at line 1
SQL> exec procexception('a'); ±¨´í ORA-01403: no data found ORA-06512: at "LIFEMAN.PROCEXCEPTION", line 6 ORA-06512: at line 1
´¦ÀíÕâ¸öÓÐÈý¸ö°ì·¨
1£® Ö±½Ó¼ÓÉÏÒì³£´¦Àí¡£
- create or replace procedure procexception(p varchar2)
- as
- v_postype varchar2(20);
- begin
- select pos_type into v_postype from pos_type_tbl where 1=0;
- dbms_output.put_line(v_postype);
- exception
- when no_data_found then
- dbms_output.put_line('ûÕÒµ½Êý¾Ý');
- end;
create or replace procedure procexception(p varchar2) as v_postype varchar2(20); begin select pos_type into v_postype from pos_type_tbl where 1=0; dbms_output.put_line(v_postype); exception when no_data_found then dbms_output.put_line('ûÕÒµ½Êý¾Ý'); end;ÕâÑù×ö»»ÌÀ²»»»Ò©£¬³ÌÐòÈÔÈ»±»Öжϡ£¿ÉÄÜÕâÑù²»ÊÇÎÒÃÇËùÏëÒªµÄ¡£
2. select into×öΪһ¸ö¶ÀÁ¢µÄ¿é£¬ÔÚÕâ¸ö¿éÖнøÐÐÒì³£´¦Àí
- create or replace procedure procexception(p varchar2)
- as
- v_postype varchar2(20);
- begin
- begin
- select pos_type into v_postype from pos_type_tbl where 1=0;
- dbms_output.put_line(v_postype);
- exception
- when no_data_found then
- v_postype := '';
- end;
- dbms_output.put_line(v_postype);
- end;
create or replace procedure procexception(p varchar2) as v_postype varchar2(20); begin begin select pos_type into v_postype from pos_type_tbl where 1=0; dbms_output.put_line(v_postype); exception when no_data_found then v_postype := ''; end; dbms_output.put_line(v_postype); end;ÕâÊÇÒ»ÖֱȽϺõĴ¦Àí·½Ê½ÁË¡£²»»áÒòΪÕâ¸öÒì³£¶øÒýÆð³ÌÐòÖжϡ£
3.ʹÓÃÓαê
- create or replace procedure procexception(p varchar2)
- as
- v_postype varchar2(20);
- cursor c_postype is select pos_type from pos_type_tbl where 1=0;
- begin
- open c_postype;
- fetch c_postype into v_postype;
- close c_postype;
- dbms_output.put_line(v_postype);
- end;
create or replace procedure procexception(p varchar2) as v_postype varchar2(20); cursor c_postype is select pos_type from pos_type_tbl where 1=0; begin open c_postype; fetch c_postype into v_postype; close c_postype; dbms_output.put_line(v_postype); end;ÕâÑù¾ÍÍêÈ«µÄ±ÜÃâÁËno_data_foundÒì³£¡£ÍêÈ«½»ÓɳÌÐòÔ±À´½øÐпØÖÆÁË¡£
µÚ¶þÖÖÇé¿öÊÇtoo_many_rows Òì³£µÄÎÊÌâ¡£
Too_many_rows Õâ¸öÎÊÌâ±ÈÆðno_data_foundÒª¸´ÔÓһЩ¡£
¸øÒ»¸ö±äÁ¿¸³ÖµÊ±£¬µ«ÊDzéѯ½á¹ûÓжà¸ö¼Ç¼¡£
´¦ÀíÕâÖÖÎÊÌâÒ²ÓÐÁ½ÖÖÇé¿ö£º
1£® ¶àÌõÊý¾ÝÊÇ¿ÉÒÔ½ÓÊܵģ¬Ò²¾ÍÊÇ˵´Ó½á¹û¼¯ÖÐËæ±ãÈ¡Ò»¸öÖµ¾ÍÐС£ÕâÖÖÇé¿öÓ¦¸ÃºÜ¼«¶ËÁË°É£¬Èç¹û³öÏÖÕâÖÖÇé¿ö£¬Ò²ËµÃ÷Á˳ÌÐòµÄÑϽ÷ÐÔ´æÔÚÎÊÌâ¡£
2£® ¶àÌõÊý¾ÝÊDz»¿ÉÒÔ±»½ÓÊܵģ¬ÔÚÕâÖÖÇé¿ö¿Ï¶¨ÊdzÌÐòµÄÂß¼³öÁËÎÊÌ⣬Ҳ˵ÊÇ˵ÔÀ´¸ù±¾¾Í²»»áÏëµ½Ëü»á²úÉú¶àÌõ¼Ç¼¡£
¶ÔÓÚµÚÒ»ÖÖÇé¿ö£¬¾Í±ØÐë²ÉÓÃÓαêÀ´´¦Àí£¬¶ø¶ÔÓÚµÚ¶þÖÖÇé¿ö¾Í±ØÐëʹÓÃÄÚ²¿¿éÀ´´¦Àí£¬ÖØÐÂÅ׳öÒì³£¡£
¶àÌõÊý¾Ý¿ÉÒÔ½ÓÊÜ,Ëæ±ãÈ¡Ò»Ìõ£¬Õâ¸ö¸úno_data_foundµÄ´¦Àí·½Ê½Ò»Ñù£¬Ê¹ÓÃÓαꡣ
ÎÒÕâÀï½ö˵µÚ¶þÖÖÇé¿ö£¬²»¿É½ÓÊܶàÌõÊý¾Ý£¬µ«ÊDz»ÒªÍüÁË´¦Àíno_data_foundŶ¡£Õâ¾Í²»ÄÜʹÓÃÓαêÁË£¬±ØÐëʹÓÃÄÚ²¿¿é¡£
- create or replace procedure procexception2(p varchar2)
- as
- v_postype varchar2(20);
- begin
- begin
- select pos_type into v_postype from pos_type_tbl where rownum < 5;
- exception
- when no_data_found then
- v_postype :=null;
- when too_many_rows then
- raise_application_error(-20000,'¶Ôv_postype¸³ÖµÊ±£¬ÕÒµ½¶àÌõÊý¾Ý');
- end;
- dbms_output.put_line(v_postype);
- end;
create or replace procedure procexception2(p varchar2) as v_postype varchar2(20); begin begin select pos_type into v_postype from pos_type_tbl where rownum < 5; exception when no_data_found then v_postype :=null; when too_many_rows then raise_application_error(-20000,'¶Ôv_postype¸³ÖµÊ±£¬ÕÒµ½¶àÌõÊý¾Ý'); end; dbms_output.put_line(v_postype); end;ÐèҪעÒâµÄÊÇÒ»¶¨Òª¼ÓÉ϶Ôno_data_foundµÄ´¦Àí£¬¶Ô³öÏÖ¶àÌõ¼Ç¼µÄÇé¿öÔò¼ÌÐøÅ׳öÒì³££¬ÈÃÉÏÒ»²ãÀ´´¦Àí¡£
×ÜÖ®¶ÔÓÚselect intoµÄÓï¾äÐèҪעÒâÕâÁ½ÖÖÇé¿öÁË¡£ÐèÒªÍ×µ±´¦Àí°¡¡£
3.5 ÔÚ´æ´¢¹ý³ÌÖзµ»Ø½á¹û¼¯
ÎÒÃÇʹÓô洢¹ý³Ì¶¼ÊÇ·µ»ØÖµ¶¼Êǵ¥Ò»µÄ£¬ÓÐʱÎÒÃÇÐèÒª´Ó¹ý³ÌÖзµ»ØÒ»¸ö¼¯ºÏ¡£¼´¶àÌõÊý¾Ý¡£ÕâÓм¸ÖÖ½â¾ö·½°¸¡£±È½Ï¼òµ¥µÄ×ö·¨ÊÇдÁÙʱ±í£¬µ«ÊÇÕâÖÖ×ö·¨²»Áé»î¡£¶øÇÒά»¤Âé·³¡£ÎÒÃÇ¿ÉÒÔʹÓÃǶÌ×±íÀ´ÊµÏÖ.ûÓÐÒ»¸ö¼¯ºÏÀàÐÍÄܹ»ÓëjavaµÄjdbcÀàÐÍÆ¥Åä¡£Õâ¾ÍÊǶÔÏóÓë¹ØϵÊý¾Ý¿âµÄ×迹°É¡£Êý¾Ý¿âµÄ¶ÔÏó²¢²»Äܹ»Íêȫת»»Îª±à³ÌÓïÑԵĶÔÏ󣬻¹±ØÐëʹÓùØϵÊý¾Ý¿âµÄ´¦Àí·½Ê½¡£
- create or replace package procpkg is
- type refcursor is ref cursor;
- procedure procrefcursor(p varchar2, p_ref_postypeList out refcursor);
- end procpkg;
- create or replace package body procpkg is
- procedure procrefcursor(p varchar2, p_ref_postypeList out refcursor)
- is
- v_posTypeList PosTypeTable;
- begin
- v_posTypeList :=PosTypeTable();--³õʼ»¯Ç¶Ì×±í
- v_posTypeList.extend;
- v_posTypeList(1) := PosType('A001','¿Í»§×ÊÁϱä¸ü');
- v_posTypeList.extend;
- v_posTypeList(2) := PosType('A002','ÍÅÌå×ÊÁϱä¸ü');
- v_posTypeList.extend;
- v_posTypeList(3) := PosType('A003','ÊÜÒæÈ˱ä¸ü');
- v_posTypeList.extend;
- v_posTypeList(4) := PosType('A004','ÐøÆÚ½»·Ñ·½Ê½±ä¸ü');
- open p_ref_postypeList for select * from table(cast (v_posTypeList as PosTypeTable));
- end;
- end procpkg;
create or replace package procpkg is type refcursor is ref cursor; procedure procrefcursor(p varchar2, p_ref_postypeList out refcursor); end procpkg; create or replace package body procpkg is procedure procrefcursor(p varchar2, p_ref_postypeList out refcursor) is v_posTypeList PosTypeTable; begin v_posTypeList :=PosTypeTable();--³õʼ»¯Ç¶Ì×±í v_posTypeList.extend; v_posTypeList(1) := PosType('A001','¿Í»§×ÊÁϱä¸ü'); v_posTypeList.extend; v_posTypeList(2) := PosType('A002','ÍÅÌå×ÊÁϱä¸ü'); v_posTypeList.extend; v_posTypeList(3) := PosType('A003','ÊÜÒæÈ˱ä¸ü'); v_posTypeList.extend; v_posTypeList(4) := PosType('A004','ÐøÆÚ½»·Ñ·½Ê½±ä¸ü'); open p_ref_postypeList for select * from table(cast (v_posTypeList as PosTypeTable)); end; end procpkg;
ÔÚ°üÍ·Öж¨ÒåÁËÒ»¸öÓαê±äÁ¿,²¢°ÑËü×÷Ϊ´æ´¢¹ý³ÌµÄ²ÎÊýÀàÐÍ¡£
ÔÚ´æ´¢¹ý³ÌÖж¨ÒåÁËÒ»¸öǶÌ×±í±äÁ¿£¬¶ÔÊý¾Ýд½øǶÌ×±íÖУ¬È»ºó°ÑǶÌ×±í½øÐÐÀ
Ïà¹ØÔĶÁ:
- ʲôÊÇ´æ´¢¹ý³Ì (overn, 2007-4-18)
- ASPµ÷Óô洢¹ý³Ì£¨×ª£© (overn, 2007-5-11)
- aspµ÷ÓÃÊÓͼºÍ´æ´¢¹ý³Ì (overn, 2007-5-13)
- CreateParameter ·½·¨ (overn, 2007-5-23)
- SQLѧϰ֮Èý¡ª¡ª¡ªÔ¼ÊøºÍ´æ´¢¹ý³Ì (freedomwar3, 2008-12-16)
TAG: ´æ´¢¹ý³Ì
ÎÒµÄÀ¸Ä¿
±êÌâËÑË÷
ÈÕÀú
|
|||||||||
ÈÕ | Ò» | ¶þ | Èý | ËÄ | Îå | Áù | |||
1 | 2 | 3 | 4 | 5 | 6 | ||||
7 | 8 | 9 | 10 | 11 | 12 | 13 | |||
14 | 15 | 16 | 17 | 18 | 19 | 20 | |||
21 | 22 | 23 | 24 | 25 | 26 | 27 | |||
28 | 29 | 30 |
ÎҵĴ浵
Êý¾Ýͳ¼Æ
- ·ÃÎÊÁ¿: 3342
- ÈÕÖ¾Êý: 7
- ½¨Á¢Ê±¼ä: 2009-01-12
- ¸üÐÂʱ¼ä: 2011-06-24