³õѧPL/SQL Óαê
1,ÏÔʾÓαêµÄ²Ù×÷
(1) ¶¨ÒåÓαê
¸ù¾Ý²éѯµÄÊý¾ÝÇé¿ö£¬ÔÚPL/SQL¿éµÄÉùÃ÷²¿·Ö¶¨ÒåÓα꣬Ó﷨Ϊ£º
cursor cursor_name is select_statement;
×¢Ò⣺
ÓÎ±ê±ØÐëÔÚPL/SQL¿éµÄÉùÃ÷²¿·Ö½øÐж¨Ò壻
Óα궨Òåʱ¿ÉÒÔÒýÓÃPL/SQL±äÁ¿£¬µ«±äÁ¿±ØÐëÔÚÓα궨Òå֮ǰ¶¨Ò壻
¶¨ÒåÓαêʱ²¢Ã»ÓÐÉú³ÉÊý¾Ý£¬Ö»Êǽ«¶¨ÒåÐÅÏ¢±£´æµ½Êý¾Ý×ÖµäÖУ»
Óα궨Òåºó£¬¿ÉÒÔʹÓÃcursor_name%rowtype ¶¨Òå±äÁ¿£»
£¨2£© ´ò¿ªÓαê
ΪÁËÔÚÄÚ´æÖзÖÅ仺³åÇø£¬²¢´ÓÊý¾Ý¿âÖмìË÷Êý¾Ý£¬ÐèÒªÔÚPL/SQL¿éµÄÖ´Ðв¿·Ö´ò¿ªÓα꣬Ó﷨Ϊ£º
open cursor_name;
×¢Ò⣺
Ö»ÓÐÔÚ´ò¿ªÓαêʱ£¬²ÅÊÇÕæÕýµÄ´´½¨»º³åÇø£¬²¢´ÓÊý¾Ý¿â¼ìË÷Êý¾Ý£»
ÓαêÒ»µ©´ò¿ª£¬¾ÍÎÞ·¨Ôٴδò¿ª£¬³ý·ÇÏȹرգ»
Èç¹ûÓα궨ÒåÖеıäÁ¿Öµ·¢Éú±ä»¯£¬ÔòÖ»ÄÜÔÚÏ´δò¿ªÓαêʱ²ÅÆð×÷Óã»
£¨3£©¼ìË÷Óαê
Ó﷨Ϊ£º
Fetch cursor_name into variable_list|record_variable;
×¢Ò⣺
ÔÚʹÓÃfetchÓï¾ä֮ǰ±ØÐëÏÈ´ò¿ªÓα꣬±£Ö¤»º³åÇøÓÐÊý¾Ý£»
¶ÔÓαêµÚÒ»´ÎʹÓÃfetchÓï¾äʱ£¬ÓαêÖ¸ÕëÖ¸ÏòµÚÒ»Ìõ¼Ç¼£¬Òò´Ë²Ù×÷µÄ¶ÔÏóÊǵÚÒ»Ìõ¼Ç¼¡£²Ù×÷Íêºó£¬ÓαêÖ¸ÕëÖ¸ÏòÏÂÒ»Ìõ¼Ç¼£»
ÓαêÖ¸ÕëÖ»ÄÜÏòÏÂÒÆ¶¯£¬²»ÄÜ»ØÍË¡£
into×Ó¾äÖбäÁ¿¸öÊý£¬Ë³Ðò£¬Êý¾ÝÀàÐͱØÐëºÍ»º³åÇøÖеĶÔÓ¦¡£
ÓÉÓÚÓαê¶ÔÓ¦µÄ»º³åÇøÖпÉÄÜÓжà¸ö¼Ç¼£¬Òò´Ë¼ìË÷ÓαêµÄ¹ý³ÌÊÇÒ»¸öÑ»·¹ý³Ì¡£
£¨4£© ¹Ø±ÕÓαê
Óαê¶ÔÓ¦»º³åÇøµÄÊý¾Ý´¦ÀíÍêºó£¬Ó¦¸Ã¼°Ê±¹Ø±ÕÓα꣬ÒÔÊÍ·ÅËüÕ¼ÓõÄϵͳ×ÊÔ´¡£¹Ø±ÕÓαêµÄÓ﷨Ϊ£º
close cursor_name;
ÀýÈ磺¸ù¾ÝÊéÃû²éѯ¶ÔÓ¦ÊéµÄÐÅÏ¢£¬ÓÉÓÚ²éѯµÄ½á¹û¿ÉÄÜÓжà¸ö£¬Òò´Ë²ÉÓÃÓαꣻ
declare
v_bookname book.bookname%type;
cursor c_book is select * from book where bookName=v_bookname;
v_book c_book%rowtype;
begin
v_bookname:=&x;
op
Ïà¹ØÎĵµ£º
1¡¢Á½±í£¬Ò»ÕÅÓÐÉϰÙÍòµÄÊý¾Ý£¨±íA)£¬ÁíÒ»ÕÅÖ»ÓÐÒ»Íò¶àÌõÊý¾Ý£¨±íB£©£¬A±íµÄÍâ¼ü¹ØÁªµ½B±íµÄÖ÷¼üÉÏ¡£
ÎÊÏÂÃæÁ½ÌõSQLÓï¾ä£¬ÄÇÌõÐÔÄܸü¸ß£¿
(1)SELECT A.ID, B.ID from A, B WHERE A.FID = B.ID
(2)SELECT A.ID, B.ID from B, A WHERE A.FID = B.ID ......
´æ´¢¹ý³Ì
ÔÚ´óÐÍÊý¾Ý¿âϵͳÖУ¬Ëæ×Ź¦ÄܵIJ»¶ÏÍêÉÆ£¬ÏµÍ³Ò²±äµÃÔ½À´Ô½¸´ÔÓ£¬´óÁ¿µÄʱ¼ä½«»áºÄ·ÑÔÚSQL´úÂëºÍÓ¦ÓóÌÐò´úÂëÉÏ£¬¿öÇÒÔںܶàÇé¿öÏ£¬Ðí¶à´úÂë±»ÖØ¸´Ê¹Óöà´Î£¬Ã¿´Î¶¼ÊäÈëÏàͬµÄ´úÂë¼È·±ËöÓֻήµÍϵͳÔËÐÐЧÂÊ¡£Òò´Ë£¬SQL ServerÌṩÁËÒ»ÖÖ·½·¨£¬Ëü¿ÉÒÔ½«Ò»Ð©¹Ì¶¨µÄ²Ù×÷¼¯ÖÐÆðÀ´ÓÉSQL ServerÊý¾Ý¿â·þÎñÆ÷À´Íê³É£ ......
ͼ±í
ͼ±í(ÓֳƹØÏµÍ¼)ÊÇSQL ServerÖÐÒ»ÀàÌØÊâµÄÊý¾Ý¿â¶ÔÏó£¬ËüÌṩ¸øÓû§Ö±¹ÛµØ¹ÜÀíÊý¾Ý¿â±íµÄ·½·¨¡£Í¨¹ýͼ±í£¬Óû§¿ÉÒÔÖ±¹ÛµØ´´½¨¡¢±à¼Êý¾Ý¿â±íÖ®¼äµÄ¹ØÏµ£¬Ò²¿ÉÒÔ±à¼±í¼°ÆäÁеÄÊôÐÔ¡£ÀûÓÃÆóÒµ¹ÜÀíÆ÷´´½¨Í¼±íµÄ¾ßÌå²½ÖèÈçÏ£º
(1)ÔÚÆóÒµ¹ÜÀíÆ÷ÖУ¬Õ¹¿ªÖ¸¶¨µÄ·þÎñÆ÷ºÍÊý¾Ý¿â£¬´ò¿ªÒª½¨Á¢µÄͼ±íËùÔÚµÄÊý¾Ý¿â£¬ÓÒ»÷ͼ±íÍ ......
´¥·¢Æ÷
´¥·¢Æ÷ÊÇÒ»ÖÖÌØÊâÀàÐ͵Ĵ洢¹ý³Ì£¬Ëü²»Í¬Ç°Ãæ½éÉܹýµÄ´æ´¢¹ý³Ì¡£´¥·¢Æ÷Ö÷ÒªÊÇͨ¹ýʼþ½øÐд¥·¢¶ø±»Ö´Ðе쬶ø´æ´¢¹ý³Ì¿ÉÒÔͨ¹ý´æ´¢¹ý³ÌÃû³Æ¶ø±»Ö±½Óµ÷Óᣴ¥·¢Æ÷ÊÇÒ»¸ö¹¦ÄÜÇ¿´óµÄ¹¤¾ß£¬Ëüʹÿ¸öÕ¾µã¿ÉÒÔÔÚÓÐÊý¾ÝÐÞ¸Äʱ×Ô¶¯Ç¿ÖÆÖ´ÐÐÆäÒµÎñ¹æÔò¡£´¥·¢Æ÷¿ÉÒÔÓÃÓÚSQL ServerÔ¼Êø¡¢Ä¬ÈÏÖµºÍ¹æÔòµÄÍêÕûÐÔ¼ì²é¡£
µ±Íùijһ ......
/*****************************
³£ÓÃÈÕÆÚº¯Êý
*****************************/
use studentInfo
--ÏòÊý¾Ý¿âÖвåÈëÈÕÆÚµÄ³£¼û¸ñʽ£ºÈç²åÈë2010Äê10ÔÂ3ÈÕÕâ¸öÈÕÆÚ
--ÆäÖÐtb_dateΪһ¸ö²âÊÔÓÃµÄ±í£¬ÀïÃæ¾ÍÒ»¸ö×Ö¶Îtime£¬ÎªdatetimeÀàÐÍ
insert tb_date values('2010.10.3')
insert tb_date values('2010-10-3 12:14:35 ......