存储过程编写经验和优化措施
auther:csdn
一、适合读者对象：数据库开发程序员，数据库的数据量很多，涉及到对SP（存储过程）的优化的项目开发人员，对数据库有浓厚兴趣的人。
二、介绍：在数据库的开发过程中，经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用SP来封装数据库操作。如果项目的SP较多，书写又没有一定的规范，将会影响以后的系统维护困难和大SP逻辑的难以理解，另外如果数据库的数据量大或者项目对SP的性能要求很，就会遇到优化的问题，否则速度有可能很慢，经过亲身经验，一个经过优化过的SP要比一个性能差的SP的效率甚至高几百倍。
三、内容：
1、开发人员如果用到其他库的Table或View，务必在当前库中建立View来实现跨库操作，最好不要直接使用“databse.dbo.table_name”，因为sp_depends不能显示出该SP所使用的跨库table或view，不方便校验。
2、开发人员在提交SP前，必须已经使用set showplan on分析过查询计划，做过自身的查询优化检查。
3、高程序运行效率，优化应用程序，在SP编写过程中应该注意以下几点：
a)SQL的使用规范：
i. 尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。
ii. 尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。
iii. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。
iv. 注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。
v. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
vi. 尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。
vii. 尽量使用“>=”，不要使用“>”。
viii. 注意一些or子句和union子句之间的替换
ix. 注意表之间连接的数据类型，避免不同类型数据之间的连接。
x. 注意存储过程中参数和数据类型的关系。
xi. 注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升级成表级锁。
b)索引的使用规范：
i. 索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。
ii. 尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name来强制指定索引
iii. 避免对大表查询时进行table scan，必要时考虑新建索引。
iv. 在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。
v. 要注意索引的维护，周期性重建索引，重新编译存储过程。
c)tempdb的使用规范：
i. 尽量避免使用distinct、order by、group by、having、join、cumpute，因为这些语句会加重tempdb的负担。
ii. 避免频繁创建和删除临时表，减少系统表资源的消耗。
iii. 在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。
iv. 如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。
v. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。
vi. 慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。
d)合理的算法使用：
根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令：set statistics io on, set statistics time on , set showplan on 等。
代码: 获取当前客户端IP地址
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HH_GetClientIP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[HH_GetClientIP]
GO
/********************************************************
功能：获取当前客户端IP地址
********************************************************/
/*调用过程：
exec HH_GetClientIP
*/
Create Procedure HH_GetClientIP
as
declare @ip varchar(20)
declare @hst varchar(20)
declare @sql varchar(100)
declare @str varchar(100)
set @str='PING '+Host_Name() -- 这里改为：set @str='PING '+Host_Name() + ' -n 1'
create table #tmp(aa varchar(200))
insert #tmp exec master..xp_cmdshell @str
select top 1 @ip = replace(left(aa,charindex(':',aa)-1),'Reply from ','')
from #tmp where aa like 'reply from %:%'
drop table #tmp
select @ip
return (0)
Go
小写金额转换成英文
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HY_LowerMoney2English]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[HY_LowerMoney2English]
GO
/*--调用示例：
print [dbo].[HY_LowerMoney2English](10235.02)
*/
CREATE FUNCTION [dbo].[HY_LowerMoney2English] (@num numeric(15,2))
RETURNS varchar(400)
AS
BEGIN
/**************************************************
Number to letters Version 1.0
Copyright (C) pbsql 2004
Language: US. English
***************************************************/
DECLARE @i int,@hundreds int,@tenth int,@one int
DECLARE @thousand int,@million int,@billion int
DECLARE @numbers varchar(400),@s varchar(15),@result varchar(400)
SET @numbers='ONE TWO THREE FOUR FIVE '
+'SIX SEVEN EIGHT NINE TEN '
+'ELEVEN TWELVE THIRTEEN FOURTEEN FIFTEEN '
+'SIXTEEN SEVENTEEN EIGHTEEN NINETEEN '
+'TWENTY THIRTY FORTY FIFTY '
+'SIXTY SEVENTY EIGHTY NINETY '
SET @s=RIGHT('000000000000000'+CAST(@num AS varchar(15)),15)
SET @billion=CAST(SUBSTRING(@s,1,3) AS int)--将12位整数分成4段：十亿、百万、千、百十个
SET @million=CAST(SUBSTRING(@s,4,3) AS int)
SET @thousand=CAST(SUBSTRING(@s,7,3) AS int)
SET @result=''
SET @i=0
WHILE @i<=3
BEGIN
SET @hundreds=CAST(SUBSTRING(@s,@i*3+1,1) AS int)--百位0-9
SET @tenth=CAST(SUBSTRING(@s,@i*3+2,1) AS int)
SET @one=(CASE @tenth WHEN 1 THEN 10 ELSE 0 END)+CAST(SUBSTRING(@s,@i*3+3,1) AS int)--个位0-19
SET @tenth=(CASE WHEN @tenth<=1 THEN 0 ELSE @tenth END)--十位0、2-9
IF (@i=1 and @billion>0 and (@million>0 or @thousand>0 or @hundreds>0 or @tenth>0 or @one>0)) or
(@i=2 and (@billion>0 or @million>0) and (@thousand>0 or @hundreds>0 or @tenth>0 or @one>0)) or
(@i=3 and (@billion>0 or @million>0 or @thousand>0) and (@hundreds>0 or @tenth>0 or @one>0))
SET @result=@result+', '--每段之间加连接符,
IF @hundreds>0
SET @result=@result+RTRIM(SUBSTRING(@numbers,@hundreds*10-9,10))+' HUNDRED'
IF @tenth>=2 and @tenth<=9
BEGIN
IF @hundreds>0
SET @result=@result+' AND '
SET @result=@result+RTRIM(SUBSTRING(@numbers,@tenth*10+171,10))
END
IF @one>=1 and @one<=19
BEGIN
IF @tenth>0
SET @result=@result+'-'
ELSE
IF @hundreds>0
SET @result=@result+' AND '
SET @result=@result+RTRIM(SUBSTRING(@numbers,@one*10-9,10))
END
IF @i=0 and @billion>0
SET @result=@result+' BILLION'
IF @i=1 and @million>0
SET @result=@result+' MILLION'
IF @i=2 and @thousand>0
SET @result=@result+' THOUSAND'
SET @i=@i+1
END
IF SUBSTRING(@s,14,2)<>'00'
BEGIN
SET @result=@result+' POINT '
IF SUBSTRING(@s,14,1)='0'
SET @result=@result+'ZERO'
ELSE
SET @result=@result+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,14,1) AS int)*10-9,10))
IF SUBSTRING(@s,15,1)<>'0'
SET @result=@result+' '+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,15,1) AS int)*10-9,10))
END
RETURN(@result)
END
GO
小写金额转换成大写
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HY_LowerMoney2GBWord]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[HY_LowerMoney2GBWord]
GO
/********************************************************
功能：小写金额转换成大写
参数：n_LowerMoney 小写金额
v_TransType 种类 -- 1: directly translate, 0: read it in words
输出：大写金额
********************************************************/
/*调用过程：
declare @ret varchar(200)
exec HY_LowerMoney2GBWord 567983.897,1,@ret output
select @ret
*/
CREATE PROCEDURE HY_LowerMoney2GBWord
( @n_LowerMoney numeric(15,2),
@v_TransType int,
@RET VARCHAR(200) output
) AS
Declare @v_LowerStr VARCHAR(200) -- 小写金额
Declare @v_UpperPart VARCHAR(200)
Declare @v_UpperStr VARCHAR(200) -- 大写金额
Declare @i_I int
set nocount on
select @v_LowerStr = LTRIM(RTRIM(STR(@n_LowerMoney,20,2))) --四舍五入为指定的精度并删除数据左右空格
select @i_I = 1
select @v_UpperStr = ''
while ( @i_I <= len(@v_LowerStr))
begin
select @v_UpperPart = case substring(@v_LowerStr,len(@v_LowerStr) - @i_I + 1,1)
WHEN '.' THEN '元'
WHEN '0' THEN '零'
WHEN '1' THEN '壹'
WHEN '2' THEN '贰'
WHEN '3' THEN '叁'
WHEN '4' THEN '肆'
WHEN '5' THEN '伍'
WHEN '6' THEN '陆'
WHEN '7' THEN '柒'
WHEN '8' THEN '捌'
WHEN '9' THEN '玖'
END
+
case @i_I
WHEN 1 THEN '分'
WHEN 2 THEN '角'
WHEN 3 THEN ''
WHEN 4 THEN ''
WHEN 5 THEN '拾'
WHEN 6 THEN '佰'
WHEN 7 THEN '仟'
WHEN 8 THEN '万'
WHEN 9 THEN '拾'
WHEN 10 THEN '佰'
WHEN 11 THEN '仟'
WHEN 12 THEN '亿'
WHEN 13 THEN '拾'
WHEN 14 THEN '佰'
WHEN 15 THEN '仟'
WHEN 16 THEN '万'
ELSE ''
END
select @v_UpperStr = @v_UpperPart + @v_UpperStr
select @i_I = @i_I + 1
end
--print '//v_UpperStr ='+@v_UpperStr +'//'
if ( @v_TransType=0 )
begin
select @v_UpperStr = REPLACE(@v_UpperStr,'零拾','零')
select @v_UpperStr = REPLACE(@v_UpperStr,'零佰','零')
select @v_UpperStr = REPLACE(@v_UpperStr,'零仟','零')
select @v_UpperStr = REPLACE(@v_UpperStr,'零零零','零')
select @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')
select @v_UpperStr = REPLACE(@v_UpperStr,'零角零分','整')
select @v_UpperStr = REPLACE(@v_UpperStr,'零分','整')
select @v_UpperStr = REPLACE(@v_UpperStr,'零角','零')
select @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万零元','亿元')
select @v_UpperStr = REPLACE(@v_UpperStr,'亿零万零元','亿元')
select @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万','亿')
select @v_UpperStr = REPLACE(@v_UpperStr,'零万零元','万元')
select @v_UpperStr = REPLACE(@v_UpperStr,'万零元','万元')
select @v_UpperStr = REPLACE(@v_UpperStr,'零亿','亿')
select @v_UpperStr = REPLACE(@v_UpperStr,'零万','万')
select @v_UpperStr = REPLACE(@v_UpperStr,'零元','元')
select @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')
end
-- 对壹元以下的金额的处理
if ( substring(@v_UpperStr,1,1)='元' )
select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
if (substring(@v_UpperStr,1,1)= '零')
select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
if (substring(@v_UpperStr,1,1)='角')
select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
if ( substring(@v_UpperStr,1,1)='分')
select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
if (substring(@v_UpperStr,1,1)='整')
select @v_UpperStr = '零元整'
select @ret=@v_UpperStr
GO
程序: 穷举法破解SQLServer用户密码
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_GetPassword]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_GetPassword]
GO
/*--穷举法破解SQLServer用户密码
可以破解中文,特殊字符,字符+尾随空格的密码
为了方便显示特殊字符的密码,在显示结果中,显示了组成密码的ASCII
理论上可以破解任意位数的密码
条件是你的电脑配置足够,时间足够
--邹建 2004.08(引用请保留此信息)--*/
/*--调用示例
--测试特殊字符
declare @pwd sysname
set @pwd=char(0)+'a '
exec sp_password null,@pwd,'sa'
exec p_GetPassword
--测试带空格的密码
exec sp_password null,'a ','sa'
exec p_GetPassword
--测试中文
exec sp_password null,'我 ','sa'
exec p_GetPassword
--清除密码
exec sp_password null,null,'sa'
--*/
create proc p_GetPassword
@username sysname=null, --用户名,如果不指定,则列出所有用户
@pwdlen int=3 --密码破解的位数,默认只破解3位及以下的密码
as
--生成要破解的密码的用户表
select name,password,
type = case when xstatus&2048=2048 then 1 else 0 end,
jm = case when password is null or datalength(password)<46 then 1 else 0 end,
pwdstr = case when datalength(password)<46 then cast(password as sysname)
else cast('' as sysname) end,
pwd = cast('' as varchar(8000))
into #pwd
from master.dbo.sysxlogins a
where srvid is null
and name=isnull(@username,name)
--生成临时表
select top 255 id=identity(int,0,1) into #t from sysobjects a,sysobjects b
alter table #t add constraint PK_#t primary key(id)
--清理不需要的字符
if not exists(select 1 from #pwd where type=1)
delete from #t where id between 65 and 90 or id between 129 and 254
--密码破解处理
declare @l int
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000),@s4 varchar(8000)
--破解1位密码
select @l=0,
@s1='id=a.id',
@s2='#t a',
@s3='char(b.id)',
@s4='cast(b.id as varchar)'
exec('update pwd set jm=1,
pwdstr='+@s3+',
pwd='+@s4+'
from #pwd pwd,#t b
where pwd.jm=0
and pwdcompare('+@s3+',pwd.password,pwd.type)=1')
--破解超过2位的密码
while exists(select 1 from #pwd where jm=0 and @l<@pwdlen-1)
begin
select @l=@l+1,
@s1=@s1+',id'+cast(@l as varchar)+'='+char(@l/26+97)+char(@l%26+97)+'.id',
@s2=@s2+',#t '+char(@l/26+97)+char(@l%26+97),
@s3=@s3+'+char(b.id'+cast(@l as varchar)+')',
@s4=@s4+'+'',''+cast(b.id'+cast(@l as varchar)+' as varchar)'
exec('select '+@s1+' into #tt from '+@s2+'
update pwd set jm=1,
pwdstr='+@s3+',
pwd='+@s4+'
from #pwd pwd,#tt b
where pwd.jm=0
and pwdcompare('+@s3+',pwd.password,pwd.type)=1')
end
--显示破解的密码
select 用户名=name,密码=pwdstr,密码ASCII=pwd from #pwd
go
程序: 获取SQL Server加密存储过程脚本
*---------------------------------------------------------------------
* 程序: 获取SQL Server加密存储过程脚本
* 函数、视图、触发器有效，字节不受限制
* 设计: 红雨
*---------------------------------------------------------------------
Clear
Local lcDataBaseName, lcProcedreName, lcProcedreCode, lnSqlHandle, lcSqlStringConnect
lcProcedreCode = []
lcDataBaseName = [master]
lcProcedreName = [sp_databases]
lcSqlStringConnect = [driver={Sql server};server=127.0.0.1;uid=sa;pwd=Admin;database=Master]
lnSqlHandle = Sqlstringconnect( lcSqlStringConnect )
If lnSqlHandle > 0
= SqlSetProp(lnSqlHandle,[DispWarnings], (.T.))
lcProcedreCode = GetProcedreCode( lnSqlHandle, lcDataBaseName, lcProcedreName )
= Sqldisconnect(lnSqlHandle)
If !Empty(lcProcedreCode)
_Cliptext = lcProcedreCode
Modify File ([(]+lcDataBaseName+[)]+lcProcedreName+[.sql]) Nowait
Keyboard [{CTRL+V}]
Endif
Endif
Return
Function GetProcedreCode( tnSqlHandle, tcDataBase, tcObjectName )
*-----------------------------------------------
#Define CR Chr(13)+Chr(10)
Local lcReturn, lcTempCurs, lcMyProcedure
lcReturn = []
lcTempCurs = [T]+Sys(2015)
lcDataBase = Iif(Type([tcDataBase])=[C] And !Empty(tcDataBase), tcDataBase, [Master])
If Used(lcTempCurs)
Use In (lcTempCurs)
Endif
*-----------------------------------------------
If Type([tnSqlHandle])=[N] And tnSqlHandle>0 And Type([tcObjectName])=[C]
= Sqlexec(tnSqlHandle, "use " + lcDataBase )
lcMyProcedure = "CREATE PROCEDURE " + lcTempCurs + "( @objectName varchar(50) )" ;
+CR+ " AS" ;
+CR+ " begin" ;
+CR+ " set nocount on" ;
+CR+ " begin tran" ;
+CR+ " "
lcMyProcedure = lcMyProcedure ;
+CR+ " Declare @objectname1 varchar(100)," ;
+CR+ " @orgvarbin varbinary(8000)" ;
+CR+ " Declare @sql1 nvarchar(4000)," ;
+CR+ " @sql2 varchar(8000)," ;
+CR+ " @sql3 nvarchar(4000)," ;
+CR+ " @sql4 nvarchar(4000)" ;
+CR+ " Declare @OrigSpText1 nvarchar(4000)," ;
+CR+ " @OrigSpText2 nvarchar(4000)," ;
+CR+ " @OrigSpText3 nvarchar(4000)," ;
+CR+ " @resultsp nvarchar(4000)," ;
+CR+ " @ptrval binary(16)," ;
+CR+ " @prvlen Int" ;
+CR+ " Declare @i Int," ;
+CR+ " @Status Int," ;
+CR+ " @Type varchar(10)," ;
+CR+ " @parentid Int" ;
+CR+ " Declare @colid Int," ;
+CR+ " @N Int," ;
+CR+ " @q Int," ;
+CR+ " @j Int," ;
+CR+ " @k Int," ;
+CR+ " @Encrypted Int," ;
+CR+ " @Number Int"
lcMyProcedure = lcMyProcedure ;
+CR+ " Select @Type=xtype, @parentid=parent_obj From sysobjects Where Id=object_id(@ObjectName)" ;
+CR+ " " ;
+CR+ " If exists (select * from dbo.sysobjects where id = object_id(N'#result') and OBJECTPROPERTY(id, N'IsUserTable') = 1)" ;
+CR+ " Drop Table #result" ;
+CR+ " Create Table #result(ProcName varchar(50), ProcCode text)" ;
+CR+ " Insert #result values (@ObjectName, ')" ;
+CR+ " " ;
+CR+ " If exists (select * from dbo.sysobjects where id = object_id(N'#temp') and OBJECTPROPERTY(id, N'IsUserTable') = 1)" ;
+CR+ " Drop Table #temp" ;
+CR+ " Create Table #temp(Number Int,colid Int,ctext varbinary(8000),Encrypted Int,Status Int)" ;
+CR+ " Insert #temp Select Number,colid,ctext,Encrypted,Status From syscomments Where Id = object_id(@objectName)" ;
+CR+ " " ;
+CR+ " Select @Number=Max(Number) From #temp" ;
+CR+ " Set @k=0" ;
+CR+ " "
lcMyProcedure = lcMyProcedure ;
+CR+ " While @k<=@Number" ;
+CR+ " Begin" ;
+CR+ " If Exists(Select 1 From syscomments Where Id=object_id(@objectname) And Number=@k)" ;
+CR+ " Begin" ;
+CR+ " If @Type='P'" ;
+CR+ " Set @sql1=(" ;
+CR+ " CASE" ;
+CR+ " When @Number>1 Then 'ALTER PROCEDURE '+ @objectName +';'+Rtrim(@k)+' WITH ENCRYPTION AS '" ;
+CR+ " Else 'ALTER PROCEDURE '+ @objectName+' WITH ENCRYPTION AS '" ;
+CR+ " END )" ;
+CR+ " If @Type='TR'" ;
+CR+ " Begin" ;
+CR+ " Declare @parent_obj varchar(255)," ;
+CR+ " @tr_parent_xtype varchar(10)" ;
+CR+ " Select @parent_obj = parent_obj From sysobjects Where Id=object_id(@objectName)" ;
+CR+ " Select @tr_parent_xtype = xtype From sysobjects Where Id=@parent_obj" ;
+CR+ " If @tr_parent_xtype='V'" ;
+CR+ " Begin" ;
+CR+ " Set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION INSTERD OF INSERT AS PRINT 1 '" ;
+CR+ " End" ;
+CR+ " Else" ;
+CR+ " Begin" ;
+CR+ " Set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '" ;
+CR+ " End" ;
+CR+ " End" ;
+CR+ " If @Type='FN' Or @Type='TF' Or @Type='IF'" ;
+CR+ " Set @sql1=(" ;
+CR+ " Case @Type" ;
+CR+ " When 'TF' Then 'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '" ;
+CR+ " When 'FN' Then 'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'" ;
+CR+ " When 'IF' Then 'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'" ;
+CR+ " END )" ;
+CR+ " If @Type='V'" ;
+CR+ " Set @sql1='ALTER VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 as f'" ;
+CR+ " "
lcMyProcedure = lcMyProcedure ;
+CR+ " Set @q=Len(@sql1)" ;
+CR+ " Set @sql1=@sql1+Replicate('-',4000-@q)" ;
+CR+ " Select @sql2=Replicate('-',8000)" ;
+CR+ " Set @sql3='exec(@sql1'" ;
+CR+ " Select @colid=Max(colid) From #temp Where Number=@k" ;
+CR+ " Set @N=1" ;
+CR+ " While @N<=Ceiling(1.0*(@colid-1)/2) And Len(@sQL3)<=3996" ;
+CR+ " Begin" ;
+CR+ " Set @sql3=@sql3+'+@'" ;
+CR+ " Set @N=@N+1" ;
+CR+ " End" ;
+CR+ " Set @sql3=@sql3+')'" ;
+CR+ " Exec sp_executesql @sql3,N'@Sql1 nvarchar(4000),@ varchar(8000)',@sql1=@sql1,@=@sql2" ;
+CR+ " End" ;
+CR+ " Set @k=@k+1" ;
+CR+ " End" ;
+CR+ " "
lcMyProcedure = lcMyProcedure ;
+CR+ " Set @k=0" ;
+CR+ " While @k<=@Number" ;
+CR+ " Begin" ;
+CR+ " If Exists(Select 1 From syscomments Where Id=object_id(@objectname) And Number=@k)" ;
+CR+ " Begin" ;
+CR+ " Select @colid=Max(colid) From #temp Where Number=@k" ;
+CR+ " Set @N=1" ;
+CR+ " While @N<=@colid" ;
+CR+ " Begin" ;
+CR+ " Select @OrigSpText1=ctext,@Encrypted=Encrypted,@Status=Status From #temp Where colid=@N And Number=@k" ;
+CR+ " Set @OrigSpText3=(Select ctext From syscomments Where Id=object_id(@objectName) And colid=@N And Number=@k)" ;
+CR+ " If @N=1" ;
+CR+ " Begin" ;
+CR+ " "
lcMyProcedure = lcMyProcedure ;
+CR+ " If @Type='P'" ;
+CR+ " Set @OrigSpText2=(" ;
+CR+ " CASE" ;
+CR+ " When @Number>1 Then 'CREATE PROCEDURE '+ @objectName +';'+Rtrim(@k)+' WITH ENCRYPTION AS '" ;
+CR+ " Else 'CREATE PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '" ;
+CR+ " END )" ;
+CR+ " "
lcMyProcedure = lcMyProcedure ;
+CR+ " If @Type='FN' Or @Type='TF' Or @Type='IF'" ;
+CR+ " Set @OrigSpText2=(" ;
+CR+ " Case @Type" ;
+CR+ " When 'TF' Then 'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '" ;
+CR+ " When 'FN' Then 'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'" ;
+CR+ " When 'IF' Then 'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'" ;
+CR+ " END )" ;
+CR+ " "
lcMyProcedure = lcMyProcedure ;
+CR+ " If @Type='TR'" ;
+CR+ " Begin" ;
+CR+ " If @tr_parent_xtype='V'" ;
+CR+ " Begin" ;
+CR+ " Set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION INSTEAD OF INSERT AS PRINT 1 '" ;
+CR+ " End" ;
+CR+ " Else" ;
+CR+ " Begin" ;
+CR+ " Set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '" ;
+CR+ " End" ;
+CR+ " End" ;
+CR+ " "
lcMyProcedure = lcMyProcedure ;
+CR+ " If @Type='V'" ;
+CR+ " Set @OrigSpText2='CREATE VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 as f'" ;
+CR+ " " ;
+CR+ " Set @q=4000-Len(@OrigSpText2)" ;
+CR+ " Set @OrigSpText2=@OrigSpText2+Replicate('-',@q)" ;
+CR+ " End" ;
+CR+ " Else" ;
+CR+ " Begin" ;
+CR+ " Set @OrigSpText2=Replicate('-', 4000)" ;
+CR+ " End" ;
+CR+ " "
lcMyProcedure = lcMyProcedure ;
+CR+ " Set @i=1" ;
+CR+ " Set @resultsp = Replicate(N'A', (datalength(@OrigSpText1) / 2))" ;
+CR+ " " ;
+CR+ " While @i<=datalength(@OrigSpText1)/2" ;
+CR+ " Begin" ;
+CR+ " Set @resultsp = Stuff(@resultsp, @i, 1," ;
+CR+ " NCHAR(UNICODE(Substring(@OrigSpText1, @i, 1)) ^" ;
+CR+ " (UNICODE(Substring(@OrigSpText2, @i, 1)) ^" ;
+CR+ " UNICODE(Substring(@OrigSpText3, @i, 1)))))" ;
+CR+ " Set @i=@i+1" ;
+CR+ " End" ;
+CR+ " "
lcMyProcedure = lcMyProcedure ;
+CR+ " Set @orgvarbin=cast(@OrigSpText1 As varbinary(8000))" ;
+CR+ " Set @resultsp=(" ;
+CR+ " Case " ;
+CR+ " When @Encrypted=1 Then @resultsp" ;
+CR+ " Else convert(nvarchar(4000)," ;
+CR+ " Case " ;
+CR+ " When @Status&2=2 Then uncompress(@orgvarbin)" ;
+CR+ " Else @orgvarbin" ;
+CR+ " END )" ;
+CR+ " END )" ;
+CR+ " Print @resultsp" ;
+CR+ " select @ptrval = TEXTPTR(ProcCode) from #result" ;
+CR+ " select @prvlen = DATALENGTH(ProcCode) from #result" ;
+CR+ " UPDATETEXT #result.ProcCode @ptrval @prvlen 0 @resultsp" ;
+CR+ " "
lcMyProcedure = lcMyProcedure ;
+CR+ " Set @N=@N+1" ;
+CR+ " End" ;
+CR+ " End" ;
+CR+ " Set @k=@k+1" ;
+CR+ " End" ;
+CR+ " Select * from #result" ;
+CR+ " Drop Table #temp" ;
+CR+ " Drop Table #result" ;
+CR+ " Rollback Tran" ;
+CR+ " End"
*-----------------------------------------------
= Sqlexec(tnSqlHandle, "if exists (select * from dbo.sysobjects" ;
+CR+ " where id = object_id(N'[dbo].[" + lcTempCurs + "]')" ;
+CR+ " and OBJECTPROPERTY(id, N'IsProcedure') = 1)" ;
+CR+ " drop procedure [dbo].[" + lcTempCurs + "]")
= Sqlexec(tnSqlHandle, lcMyProcedure )
= Sqlexec(tnSqlHandle, "Exec " + lcTempCurs + " ?tcObjectName", lcTempCurs)
= Sqlexec(tnSqlHandle, "if exists (select * from dbo.sysobjects" ;
+CR+ " where id = object_id(N'[dbo].[" + lcTempCurs + "]')" ;
+CR+ " and OBJECTPROPERTY(id, N'IsProcedure') = 1)" ;
+CR+ " drop procedure [dbo].[" + lcTempCurs + "]")
*-----------------------------------------------
If Used(lcTempCurs)
Select (lcTempCurs)
lcReturn = Alltrim(ProcCode)
Use In (lcTempCurs)
Endif
Endif
Return lcReturn
Endfunc
程序: 创建、压缩Access数据库并修改密码演示
*---------------------------------------------------------------------
* 程序: 创建、压缩Access数据库并修改密码演示
* 设计: 红雨
*---------------------------------------------------------------------
Local lcMdbFile, lcRetuStr
lcMdbFile = [C:\Temp\TestCreaMdbFile.mdb]
lcPswd1 = [test1]
lcPswd2 = [test2]
lcPswd3 = [test3]
lcRetuStr = [创建、压缩Access数据库并修改密码演示：] + Chr(13)
If CreateMDB( lcMdbFile, lcPswd1)
lcRetuStr = lcRetuStr + Chr(13) + [1、创建数据库成功 - 密码: ] + lcPswd1
If CompactMDB( lcMdbFile, lcPswd1, lcPswd2 )
lcRetuStr = lcRetuStr + Chr(13) + [2、压缩并修改密码成功 - 密码: ] + lcPswd2
If ChangeMdbPassword( lcMdbFile, lcPswd2, lcPswd3 )
lcRetuStr = lcRetuStr + Chr(13) + [3、单独修改数据库密码成功 - 密码: ] + lcPswd3
Else
lcRetuStr = lcRetuStr + Chr(13) + [3、单独修改数据库密码失败]
Endif
Else
lcRetuStr = lcRetuStr + Chr(13) + [2、压缩并修改密码失败]
Endif
Else
lcRetuStr = lcRetuStr + Chr(13) + [1、创建数据库失败]
Endif
= MessageBox( lcRetuStr, 0+64+0, [红雨提示] )
Return
* --------------------------------------------
Function CreateMDB( tcMdbFile, tcPswdStr )
* 创建 Access 数据库文件(.mdb)
Local IsOK
IsOK = .F.
tcMdbFile = iif(Type([tcMdbFile])=[C], tcMdbFile, [])
tcPswdStr = iif(Type([tcPswdStr])=[C], tcPswdStr, [])
If File(tcMdbFile)
Erase (tcMdbFile)
Endif
If !File(tcMdbFile)
IsOK = .T.
Local loEngine, lcOldError, lcCmdStrs
lcOldError = On([ERROR])
On Error IsOK = .F.
loCatalog = CreateObject( [ADOX.Catalog] )
lcCmdStrs = [Provider=Microsoft.Jet.OLEDB.4.0] ;
+ [;Data Source=] + tcMdbFile ;
+ [;Jet OLEDB:Database Password=] + tcPswdStr
loCatalog.Create( lcCmdStrs )
Release loCatalog
loCatalog = Null
On Error &lcOldError.
Endif
Return IsOK and File(tcMdbFile)
Endfunc
* --------------------------------------------
Function CompactMDB ( tcMdbFile, tcOldPswd, tcNewPswd )
* 压缩 Access 数据库并设置密码
Local IsOK
IsOK = .F.
tcMdbFile = iif(Type([tcMdbFile])=[C], tcMdbFile, [])
tcOldPswd = iif(Type([tcOldPswd])=[C], tcOldPswd, [])
tcNewPswd = iif(Type([tcNewPswd])=[C], tcNewPswd, tcOldPswd)
If File( tcMdbFile )
IsOK = .T.
Local loEngine, lcTmpFile, lcOldError, lcCompOldStr, lcCompNewStr
lcOldError = On([ERROR])
On Error IsOK = .F.
lcTmpFile = Addb(JustPath(tcMdbFile)) + subs(Sys(2015),3) + [.mdb]
Rename (tcMdbFile) To (lcTmpFile)
If !File(tcMdbFile) and File(lcTmpFile)
lcCompOldStr = [Provider=Microsoft.Jet.OLEDB.4.0] ;
+ [;Data Source=] + lcTmpFile ;
+ [;Jet OLEDB:Database Password=] + tcOldPswd
lcCompNewStr = [Provider=Microsoft.Jet.OLEDB.4.0] ;
+ [;Data Source=] + tcMdbFile ;
+ [;Jet OLEDB:Database Password=] + tcNewPswd
loEngine = CreateObject( [JRO.JetEngine] )
loEngine.CompactDatabase( lcCompOldStr, lcCompNewStr )
Release loEngine
loEngine = Null
If File(tcMdbFile)
Erase (lcTmpFile)
Else
Rename (lcTmpFile) To (tcMdbFile)
Endif
Else
IsOK = .F.
Endif
On Error &lcOldError.
Endif
Return IsOK and File(tcMdbFile)
Endfunc
* --------------------------------------------
Function ChangeMdbPassword ( tcMdbFile, tcOldPswd, tcNewPswd )
* 修改 Access 数据库的密码,必须独占打开数据库,使用前请确保没有其他程序使用数据库
Local IsOK
IsOK = .F.
lcRetuStr = []
tcMdbFile = iif(Type([tcMdbFile])=[C], tcMdbFile, [])
tcOldPswd = iif(Type([tcOldPswd])=[C], tcOldPswd, [])
tcNewPswd = iif(Type([tcNewPswd])=[C], tcNewPswd, [])
If File( tcMdbFile )
IsOK = .T.
Local loADODB, lcOldError
lcOldError = On([ERROR])
On Error IsOK = .F.
loADODB = CreateObject( [ADODB.Connection] )
loADODB.Mode = 12
loADODB.Provider = [Microsoft.Jet.OLEDB.4.0]
loADODB.Properties([Jet OLEDB:Database Password]) = tcOldPswd
loADODB.Open([Data Source=] + tcMdbFile)
loADODB.Execute('ALTER DATABASE PASSWORD [' + tcNewPswd + '][' + tcOldPswd + ']')
loADODB.Close
Release loADODB
loADODB = Null
On Error &lcOldError.
Endif
Return IsOK
Endfunc
* --------------------------------------------