06—PL/SQL±à³Ì
PL/SQL: OracleÖеÄSQL¹ý³Ì»¯±à³ÌÓïÑÔ
1. PL/SQL³ÌÐòÊÇÓÉ¿é½á¹¹¹¹³É£¬¸ñʽÈçÏ£º
[DECLARE
--ÉùÃ÷²¿·Ö
¡¡]
BEGIN
-- Ö÷Ìå
[EXCEPTION
-- Òì³£´¦Àí¿é
]
END;
/ --¿éµÄ½áÊø
2.±äÁ¿
¼òµ¥±äÁ¿£ºµ¥Öµ±äÁ¿
±äÁ¿Ãû ±äÁ¿ÀàÐÍ[:=³õʼֵ];
¸´ºÏ±äÁ¿£ºÏȶ¨Ò帴ºÏÀàÐÍ£¬È»ºóÔÙÓÃÀ´¸´ºÏ±äÁ¿;
±íÀàÐÍ: TYPE ÀàÐÍÃû IS TABLE OF Êý¾ÝÀàÐÍ INDEX BY BINARY_INTEGER;
±äÁ¿Ãû ±íÀàÐÍÃû;
¼Ç¼ÀàÐÍ£ºTYPE ÀàÐÍÃû IS RECORD(±äÁ¿Ãû ÀàÐÍ, ±äÁ¿Ãû ÀàÐÍ...);
ÉùÃ÷±äÁ¿µÄÀàÐÍÓëÖ¸¶¨±íµÄÖ¸¶¨ÁÐͬÀàÐÍ£º
±äÁ¿Ãû ±íÃû.ÁÐÃû%TYPE;
ÉùÃ÷¼Ç¼ÀàÐͱäÁ¿µÄÀàÐÍÓëÖ¸¶¨±íµÄÐÐͬÀàÐÍ£º
±äÁ¿Ãû ±íÃû%ROWTYPE;
3.Ìõ¼þÓï¾ä
IF Ìõ¼þ THEN Óï¾ä;
ELSIF Ìõ¼þ2 THEN Óï¾ä2;
ELSIF Ìõ¼þ2 THEN Óï¾ä2;
ELSE Óï¾än;
END IF;
4.Ñ»·Óï¾ä
1) ¼òµ¥£º
LOOP
Óï¾ä
EXIT WHEN Ìõ¼þ;
END LOOP;
2) WHILE:
WHILE Ìõ¼þ LOOP
Óï¾ä
END LOOP;
3) FOR
FOR Êý×Ö±äÁ¿ IN [REVERSE] ÏÂÏÞ..ÉÏÏÞ LOOP
Óï¾ä
END LOOP;
5. Óαêcursor£ºËüÊÇOracleÊý¾Ý¿âÄÚµÄÒ»¸ö¹¤×÷Çø£¬´æ·ÅÁËSELECTÓï¾äµÄ²éѯ¼¯¡£
1) ËüÓÐÒ»¸öÖ¸Õ룬³õʼʱָÏò²éѯ¼¯ÖеÄÊ×ÐС£
2) ÊôÐÔ£º%found¡¢%notfound¡¢%rowcount¡¢%isopen¡¢
3) ʹÓò½Ö裺
a) ÉùÃ÷±äÁ¿ÓÃÀ´±£´æÊý¾Ý¡£
b) ÉùÃ÷Óα꣬²¢Ö¸¶¨²éѯ¡£cursor v_emp_cursor is selectÓï¾ä;
c) ´ò¿ªÓαꡣopen v_emp_cursor;
d) Ò»´Î´ÓÓαêÖлñÈ¡Ò»ÐмǼ¡£fetch v_emp_cursor into ±äÁ¿Áбí;
e) ¹Ø±ÕÓαꡣclose v_emp_cursor;
4) forÑ»·¿ÉÒÔ×Ô¶¯Íê³É´ò¿ª£¬»ñÈ¡Êý¾Ý£¬¹Ø±ÕµÄ¹¦ÄÜ¡£
declare
v_
Ïà¹ØÎĵµ£º
µ½½ñÌìΪֹ£¬ÈËÃǶԹØϵÊý¾Ý¿â×öÁË´óÁ¿µÄÑо¿£¬²¢¿ª·¢³ö¹ØϵÊý¾ÝÓïÑÔ£¬Îª²Ù×÷¹ØϵÊý¾Ý¿âÌṩÁË·½±ãµÄÓû§½Ó¿Ú¡£¹ØϵÊý¾ÝÓïÑÔÄ¿Ç°Óм¸Ê®ÖÖ£¬¾ßÓÐÔö¼Ó¡¢É¾³ý¡¢Ð޸ġ¢²éѯ¡¢Êý¾Ý¶¨ÒåÓë¿ØÖƵÈÍêÕûµÄÊý¾Ý¿â²Ù×÷¹¦ÄÜ¡£Í¨³£°ÑËüÃÇ·ÖΪÁ½Àࣺ¹Øϵ´úÊýÀàºÍ¹ØϵÑÝËãÀà¡£
ÔÚÕâЩÓïÑÔÖУ¬½á¹¹»¯²éѯÓïÑÔSQLÒÔÆäÇ¿´óµÄÊý¾Ý¿â²Ù× ......
(1)char¡¢varchar¡¢textºÍnchar¡¢nvarchar¡¢ntext
charºÍvarcharµÄ³¤¶È¶¼ÔÚ1µ½8000Ö®¼ä£¬ËüÃǵÄÇø±ðÔÚÓÚcharÊǶ¨³¤×Ö·ûÊý¾Ý£¬¶øvarcharÊDZ䳤×Ö·ûÊý¾Ý¡£Ëùν¶¨³¤¾ÍÊdz¤¶È¹Ì¶¨µÄ£¬µ±ÊäÈëµÄÊý¾Ý³¤¶ÈûÓдﵽָ¶¨µÄ³¤¶Èʱ½«×Ô¶¯ÒÔÓ¢ÎÄ¿Õ¸ñÔÚÆäºóÃæÌî³ä£¬Ê¹³¤¶È´ïµ½ÏàÓ¦µÄ³¤¶È£»¶ø±ä³¤×Ö·ûÊý¾ÝÔò²»»áÒÔ¿Õ¸ñÌî³ä¡£text´æ´¢¿É±ä ......
ת×Ôhttp://www.111cn.cn/database/109/b992816b1dddbb641c25c0999883427e.htm
declare @text nvarchar(max);
with tb
as
(
select blocking_session_id,
session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requests a
CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
),
tb1 a ......
µÚÒ»Ì⣺
Ϊ¹ÜÀíÒµÎñÅàѵÐÅÏ¢£¬½¨Á¢3¸ö±í£º
S(S#,SN,SD,SA)S#,SN,SD,SA·Ö±ð´ú±íѧºÅ£¬Ñ§Ô±ÐÕÃû£¬ËùÊôµ¥Î»£¬Ñ§Ô±ÄêÁä
C(C#,CN)C#,CN·Ö±ð´ú±í¿Î³Ì±àºÅ£¬¿Î³ÌÃû³Æ
SC(S#,C#,G) S#,C#,G·Ö±ð´ú±íѧºÅ£¬ËùÑ¡µÄ¿Î³Ì±àºÅ£¬Ñ§Ï°³É¼¨
(1)ʹÓñê×¼SQLǶÌ×Óï¾ä²éѯѡÐ޿γÌÃû³ÆΪ’Ë°ÊÕ»ù´¡’µÄѧԱѧºÅºÍÐÕÃû?
(2) ......