SQLSERVER ´æ´¢¹ý³Ì Óï·¨
SQLSERVER´æ儲過³ÌµÄ寫·¨¸ñʽ規¸ñ
*****************************************************
*** author£ºSusan
*** date:2005/08/05
*** expliation:ÈçºÎ寫´æ儲過³ÌµÄ¸ñʽ¼°Àý×Ó£¬ÓÐÓÎ標µÄÓ÷¨£¡
*** ±¾°æ:SQL SERVER °æ£¡
******************************************************/
ÔÚ´æ儲過³ÌÖеĸñʽ規¸ñ£º
CREATE PROCEDURE XXX
/*
ÁÐ舉傳Èë參數
1£ºÃû稱£¬2£º類ÐÍ£¬°üÀ¨長¶È
Eg:@strUNIT_CODE varCHAR(3)
*/
參數1£¬
參數2……………
As
/*
¶¨義內²¿參數
1£ºÃû稱£¬2£º類ÐÍ£¬°üÀ¨長¶È
Eg:@strUNIT_CODE varCHAR(3)
*/
Declare
參數1£¬
參數2……………
/*
³õʼ»¯內²¿參數
Eg:SET @strUNIT_CODE=’’
*/
Set參數1µÄ³õʼֵ
Set參數2µÄ³õʼֵ…………
/*
過³ÌµÄÖ÷內ÈÝ區
Trascation£º這裡Æðµ½µÄ×÷ÓÃÊÇ£¬Èç¹ûËûÖÐ間µÄÈκÎÒ»個執ÐÐ錯誤£¬¾ÍÈ«²¿執Ðж¼·µ»Ø£¬這裡sql sever 7.0ÒÔÇ°Ò»¶¨Òª寫È룬ÒÔááµÄ¾Í¿ÉÒÔÊ¡ÂÔ
Return£º結Êø這Ö§sp
*/
Begin trascation
/*
1:¿ÉÒÔÈ¡µÃÐèÒªµÄÖµÒÔ´æÔÚ內²¿參數ÖÐ
Eg:SELECT @strUNIT_CODE=UNIT_CODE from UNIT WHERE …….
2:¿ÉÒÔÓÃÈ¡µ½µÄ»ò傳ÈëµÄ參數進ÐÐÅÐ斷£¬來進ÐÐupdate,insert,delete µÈµÈ²Ù×÷
eg: IF @strUNIT_CODE=’’
BEGIN
//¾ß體µÄ²Ù×÷
End
Else
Begin
//¾ß體µÄ²Ù×÷
End
3£ºÓÐ關ÓÎ標µÄ問題
Eg:
declare db cursor for //聲Ã÷Ò»個ÓÎ標(d
Ïà¹ØÎĵµ£º
ÈçºÎ²é¿´SQL SERVERÊý¾Ý¿âµ±Ç°Á¬½ÓÊý
1.ͨ¹ý¹ÜÀí¹¤¾ß
¿ªÊ¼->¹ÜÀí¹¤¾ß->ÐÔÄÜ£¨»òÕßÊÇÔËÐÐÀïÃæÊäÈë mmc£©È»ºóͨ¹ýÌí¼Ó¼ÆÊýÆ÷Ìí¼Ó SQL µÄ³£ÓÃͳ¼Æ È»ºóÔÚÏÂÃæÁгöµÄÏîÄ¿ÀïÃæÑ¡ÔñÓû§Á¬½Ó¾Í¿ÉÒÔʱʱ²éѯµ½Êý¾Ý¿âµÄÁ¬½ÓÊýÁË¡£²»¹ý´Ë·½·¨µÄ»°ÐèÒªÓзÃÎÊÄÇ̨¼ÆËã»úµÄȨÏÞ£¬¾ÍÊÇҪͨ¹ýWindowsÕË»§µÇ½½øÈ¥²Å¿ÉÒÔÌí¼Ó´Ë¼ÆÊ ......
create function comm_getpy
(
@str nvarchar(4000)
)
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--Èç¹û·Çºº×Ö×Ö·û£¬·µ»ØÔ×Ö·û
& ......
¡¡¡¡¶ÔÊý¾Ý¿â½øÐжà±í²Ù×÷£¬Èç¹û±íÓë±íÖ®¼ä´æÔÚÒÀÀµ£¬ÄÇôÏÔʽµÄʹÓÃÊÂÎñ£¬¿ÉÒÔ±£³Ö¶ÔÊý¾Ý¿â²Ù×÷µÄÔ×ÓÐÔ¡£ÓÃPython·ÃÎÊSqlServerÊý¾Ý¿â£¬ÎÒʹÓÃpymssql¿â¡£½ñÌìÔÚʹÓÃÕâ¸ö¿âµÄʱºò£¬·¢ÏÖÒ»¸öÎÊÌâ¡£
ÎÊÌâ´ó¸ÅÊÇÕâÑùµÄ£º
¡¡¡¡ÎÒÓÐÁ½ÕÅ±í£¬Ò»ÕÅÖ÷±í£¨ClassInfo£©£¬Ò»ÕÅ´Ó±í£¨Student£©£¬Student±íͨ¹ýÍâ¼üClassIDÓëClassI ......
1.Èç¹ûÏÈprepare ºóÌí¼Ó²ÎÊý£¬ÕâÑùÒ»²¿·ÖÊý¾ÝÀàÐÍ¿ÉÒÔ²»ÓÃÉèÖÃÆäsize´óС£¬ÀýÈçchar
2.Èç¹ûÏÈÌí¼Ó²ÎÊýÔÙprepare£¬¾Í±ØÐëÉèÖòÎÊýµÄÀàÐÍ£¬´óС£¬¾«¶È²ÅÄÜͨ¹ý£¬±ÈÈçchar,varchar,decimalÀàÐÍ£¬¶øint,floatÓй̶¨×Ö½ÚÀàÐ͵ÄÊý¾ÝÀàÐÍÔò¿É²»ÓÃÉèÖôóС¡£
3.¹ØÓÚSqlServerµÄtimestampÀàÐÍ£º¸ÃÀàÐÍΪSqlServerµÄʱ¼ä´ÁÀàÐÍ£¬´´½ ......
declare @areaid varchar(100)
declare @areaname varchar(100)
declare Cur cursor for
select code as areaID,[name] as areaName from dbo.province
open Cur
Fetch next from Cur Into @areaid,@areaname
While @@fetch_status=0
Begin
print('insert into ......