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ÒÔÆäÇ¿´óµÄÊý¾Ý¿â²Ù× ......
Sql Server ÖÐÒ»¸ö·Ç³£Ç¿´óµÄÈÕÆÚ¸ñʽ»¯º¯Êý
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GE ......
1. µ±Ç°ÏµÍ³ÈÕÆÚ¡¢Ê±¼ä
select getdate()
2. dateadd ÔÚÏòÖ¸¶¨ÈÕÆÚ¼ÓÉÏÒ»¶Îʱ¼äµÄ»ù´¡ÉÏ£¬·µ»ØÐ嵀 datetime Öµ
ÀýÈ磺ÏòÈÕÆÚ¼ÓÉÏ2Ìì
&nbs ......
1. ËÀËøÔÀí
¸ù¾Ý²Ù×÷ϵͳÖеĶ¨Ò壺ËÀËøÊÇÖ¸ÔÚÒ»×é½ø³ÌÖеĸ÷¸ö½ø³Ì¾ùÕ¼Óв»»áÊͷŵÄ×ÊÔ´£¬µ«Òò»¥ÏàÉêÇë±»ÆäËû½ø³ÌËùÕ¾Óò»»áÊͷŵÄ×ÊÔ´¶ø´¦ÓÚµÄÒ»ÖÖÓÀ¾ÃµÈ´ý״̬¡£
ËÀËøµÄËĸö±ØÒªÌõ¼þ£º
»¥³âÌõ¼þ(Mutual exclusion)£º×ÊÔ´²»Äܱ»¹²Ïí£¬Ö»ÄÜÓÉÒ»¸ö½ø³ÌʹÓá£
ÇëÇóÓë±£³ÖÌõ¼þ(Ho ......
using System;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true,IsPrecise = true)]
public static bool RegExIsMatch(string pattern,string matchString)
{
......