MSSQL为我们提供了两种动态执行SQL语句的命令，分别是EXEC和sp_executesql;通常,sp_executesql则更具有优势，它提供了输入输出接口，而EXEC没有。还有一个最大的好处就是利用sp_executesql，能够重用执行计划，这就大大提供了执行性能，还可以编写更安全的代码。EXEC在某些情况下会更灵活。除非您有令人信服的理由使用EXEC，否侧尽量使用sp_executesql. 1.EXEC的使用 EXEC命令有两种用法，一种是执行一个存储过程，另一种是执行一个动态的批处理。以下所讲的都是第二种用法。 下面先使用EXEC演示一个例子,代码1 代码 DECLARE @TableName VARCHAR(50),@Sql NVARCHAR (MAX),@OrderID INT; SET @TableName = 'Orders'; SET @OrderID = 10251; SET @sql = ’SELECT * FROM '+QUOTENAME(@TableName) +’WHERE OrderID = '+ CAST(@OrderID AS VARCHAR(10))+' ORDER BY ORDERID DESC' EXEC(@sql); 注：这里的EXEC括号中只允许包含一个字符串变量，但是可以串联多个变量，如果我们这样写EXEC： EXEC('SELECT TOP('+ CAST(@TopCount AS VARCHAR(10)) +')* FROM '+ QUOTENAME(@TableName) +' ORDER BY ORDERID DESC'); SQL编译器就会报错，编译不通过，而如果我们这样：
|
1 |
EXEC(@sql+@sql2+@sql3); |
编译器就会通过； 所以最佳的做法是把代码构造到一个变量中，然后再把该变量作为EXEC命令的输入参数，这样就不会受限制了。 EXEC的缺点是不提供接口，这里的接口是指，它不能执行一个包含一个带变量符的批处理，如下 代码 DECLARE @TableName VARCHAR(50),@Sql NVARCHAR(MAX),@OrderID INT; SET @TableName = 'Orders'; SET @OrderID = 10251; SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) + ’WHERE OrderID = @OrderID […]
View DetailsTransact-SQL中的存储过程，非常类似于Java语言中的方法，它可以重复调用。当存储过程执行一次后，可以将语句缓存中，这样下次执行的时候直接使用缓存中的语句。这样就可以提高存储过程的性能。 Ø 存储过程的概念 存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行。 存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。 由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。 1、 存储过程的优点 A、 存储过程允许标准组件式编程 存储过程创建后可以在程序中被多次调用执行，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，但对应用程序源代码却毫无影响，从而极大的提高了程序的可移植性。 B、 存储过程能够实现较快的执行速度 如果某一操作包含大量的T-SQL语句代码，分别被多次执行，那么存储过程要比批处理的执行速度快得多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的存储计划。而批处理的T-SQL语句每次运行都需要预编译和优化，所以速度就要慢一些。 C、 存储过程减轻网络流量 对于同一个针对数据库对象的操作，如果这一操作所涉及到的T-SQL语句被组织成一存储过程，那么当在客户机上调用该存储过程时，网络中传递的只是该调用语句，否则将会是多条SQL语句。从而减轻了网络流量，降低了网络负载。 D、 存储过程可被作为一种安全机制来充分利用 系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。 Ø 系统存储过程 系统存储过程是系统创建的存储过程，目的在于能够方便的从系统表中查询信息或完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程主要存储在master数据库中，以“sp”下划线开头的存储过程。尽管这些系统存储过程在master数据库中，但我们在其他数据库还是可以调用系统存储过程。有一些系统存储过程会在创建新的数据库的时候被自动创建在当前数据库中。 常用系统存储过程有：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
exec sp_databases; --查看数据库 exec sp_tables; --查看表 exec sp_columns student;--查看列 exec sp_helpIndex student;--查看索引 exec sp_helpConstraint student;--约束 exec sp_stored_procedures; exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句 exec sp_rename student, stuInfo;--修改表、索引、列的名称 exec sp_renamedb myTempDB, myDB;--更改数据库名称 exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库 exec sp_helpdb;--数据库帮助，查询数据库信息 exec sp_helpdb master; |
系统存储过程示例：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--表重命名 exec sp_rename 'stu', 'stud'; select * from stud; --列重命名 exec sp_rename 'stud.name', 'sName', 'column'; exec sp_help 'stud'; --重命名索引 exec sp_rename N'student.idx_cid', N'idx_cidd', N'index'; exec sp_help 'student'; --查询所有存储过程 select * from sys.objects where type = 'P'; select * from sys.objects where type_desc like '%pro%' and name like 'sp%'; |
Ø 用户自定义存储过程 1、 创建语法
|
1 2 3 4 5 6 7 |
create proc | procedure pro_name [{@参数数据类型} [=默认值] [output], {@参数数据类型} [=默认值] [output], .... ] as SQL_statements |
2、 创建不带参数存储过程
|
1 2 3 4 5 6 7 8 9 10 |
--创建存储过程 if (exists (select * from sys.objects where name = 'proc_get_student')) drop proc proc_get_student go create proc proc_get_student as select * from student; --调用、执行存储过程 exec proc_get_student; |
3、 修改存储过程
|
1 2 3 4 |
--修改存储过程 alter proc proc_get_student as select * from student; |
4、 带参存储过程
|
1 2 3 4 5 6 7 8 9 10 |
--带参存储过程 if (object_id('proc_find_stu', 'P') is not null) drop proc proc_find_stu go create proc proc_find_stu(@startId int, @endId int) as select * from student where id between @startId and @endId go exec proc_find_stu 2, 4; |
5、 带通配符参数存储过程
|
1 2 3 4 5 6 7 8 9 10 11 |
--带通配符参数存储过程 if (object_id('proc_findStudentByName', 'P') is not null) drop proc proc_findStudentByName go create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%') as select * from student where name like @name and name like @nextName; go exec proc_findStudentByName; exec proc_findStudentByName '%o%', 't%'; |
6、 带输出参数存储过程
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
if (object_id('proc_getStudentRecord', 'P') is not null) drop proc proc_getStudentRecord go create proc proc_getStudentRecord( @id int, --默认输入参数 @name varchar(20) out, --输出参数 @age varchar(20) output--输入输出参数 ) as select @name = name, @age = age from student where id = @id and sex = @age; go -- declare @id int, @name varchar(20), @temp varchar(20); set @id = 7; set @temp = 1; exec proc_getStudentRecord @id, @name out, @temp output; select @name, @temp; print @name + '#' + @temp; |
7、 不缓存存储过程
|
1 2 3 4 5 6 7 8 9 10 11 |
--WITH RECOMPILE 不缓存 if (object_id('proc_temp', 'P') is not null) drop proc proc_temp go create proc proc_temp with recompile as select * from student; go exec proc_temp; |
8、 加密存储过程
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
--加密WITH ENCRYPTION if (object_id('proc_temp_encryption', 'P') is not null) drop proc proc_temp_encryption go create proc proc_temp_encryption with encryption as select * from student; go exec proc_temp_encryption; exec sp_helptext 'proc_temp'; exec sp_helptext 'proc_temp_encryption'; |
9、 带游标参数存储过程
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
if (object_id('proc_cursor', 'P') is not null) drop proc proc_cursor go create proc proc_cursor @cur cursor varying output as set @cur = cursor forward_only static for select id, name, age from student; open @cur; go --调用 declare @exec_cur cursor; declare @id int, @name varchar(20), @age int; exec proc_cursor @cur = @exec_cur output;--调用存储过程 fetch next from @exec_cur into @id, @name, @age; while (@@fetch_status = 0) begin fetch next from @exec_cur into @id, @name, @age; print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age); end close @exec_cur; deallocate @exec_cur;--删除游标 |
10、 分页存储过程
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
---存储过程、row_number完成分页 if (object_id('pro_page', 'P') is not null) drop proc proc_cursor go create proc pro_page @startIndex int, @endIndex int as select count(*) from product ; select * from ( select row_number() over(order by pid) as rowId, * from product ) temp where temp.rowId between @startIndex and @endIndex go --drop proc pro_page exec pro_page 1, 4 -- --分页存储过程 if (object_id('pro_page', 'P') is not null) drop proc pro_stu go create procedure pro_stu( @pageIndex int, @pageSize int ) as declare @startRow int, @endRow int set @startRow = (@pageIndex - 1) * @pageSize +1 set @endRow = @startRow + @pageSize -1 select * from ( select *, row_number() over (order by id asc) as number from student ) t where t.number between @startRow and @endRow; exec pro_stu 2, 2; |
Ø Raiserror Raiserror返回用户定义的错误信息，可以指定严重级别，设置系统变量记录所发生的错误。 语法如下：
|
1 2 3 4 5 |
Raiserror({msg_id | msg_str | @local_variable} {, severity, state} [,argument[,…n]] [with option[,…n]] ) |
# msg_id:在sysmessages系统表中指定的用户定义错误信息 # msg_str:用户定义的信息，信息最大长度在2047个字符。 # severity：用户定义与该消息关联的严重级别。当使用msg_id引发使用sp_addmessage创建的用户定义消息时，raiserror上指定严重性将覆盖sp_addmessage中定义的严重性。 任何用户可以指定0-18直接的严重级别。只有sysadmin固定服务器角色常用或具有alter trace权限的用户才能指定19-25直接的严重级别。19-25之间的安全级别需要使用with log选项。 # state：介于1至127直接的任何整数。State默认值是1。
|
1 2 3 4 5 6 7 |
raiserror('is error', 16, 1); select * from sys.messages; --使用sysmessages中定义的消息 raiserror(33003, 16, 1); raiserror(33006, 16, 1); 转自：<a href="http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html">http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html</a> |
sqlserver2005不支持关键字limit ，所以它的分页sql查询语句将不能用mysql的方式进行，幸好sqlserver2005提供了top，rownumber等关键字，这样就能通过这几个关键字实现分页。 下面是本人在网上查阅到的几种查询脚本的写法： 几种sqlserver2005高效分页sql查询语句 top方案： sql codeselect top 10 * from table1 where id not in(select top 开始的位置 id from table1) max: sql codeselect top 10 * from table1 where id>(select max(id) from (select top 开始位置 id from table1order by id)tt) row: sql codeselect * from ( select row_number()over(order by tempcolumn)temprownumber,* from (select top 开始位置+10 tempcolumn=0,* from table1)t )tt where temprownumber>开始位置 3种分页方式，分别是max方案，top方案，row方案 效率： 第1：row 第2：max 第3：top 缺点： max：必须用户编写复杂sql，不支持非唯一列排序 top：必须用户编写复杂sql,不支持复合主键 row：不支持sqlserver2000 测试数据： 共320万条数据，每页显示10条数据，分别测试了2万页、15万页和32万页。 页码，top方案，max方案，row方案 2万，60ms，46ms，33ms 15万，453ms，343ms，310ms 32万，953ms，720ms，686ms 是一种通过程序拼接sql语句的分页方案， 用户提过的sql语句不需要编写复杂的sql逻辑 诺用户提供sql如下 sql code select * from table1 从第5条开始，查询5条，处理后sql变为 sql code […]
View Details最近在做Silverlight，Windows Phone应用移植到Windows 8平台，在IIS8中测试一些传统WCF服务应用，发现IIS8不支持WCF服务svc请求，后来发现IIS8缺少对WCF服务的Managed Handler，按照以下步骤添加后，IIS8即支持WCF服务。 1. 首先添加MIME类型 扩展名“.svc”，MIME类型 “application/octet-stream” 2. 然后在“Handler Mappings”中添加Managed Handler， Request path: *.svc Type: System.ServiceModel.Activation.HttpHandler Name: svc-Integrated 完成后，IIS8即可支持WCF服务svc请求。 转自：http://www.cnblogs.com/jv9/archive/2012/11/13/2767396.html
View Details你打算从Windows换到Linux上来，还是你刚好换到Linux上来？哎哟！！！我说什么呢，是什么原因你就出现我的世界里了。从我以往的经验来说，当我刚使用Linux，命令，终端啊什么的，吓了我一跳。我担心该记住多少命令，来帮助我完成所有任务。毫无疑问，在线文档，书籍，man pages以及社区帮了我一个大忙，但是我还是坚信有那么一篇文章记录了如何简单学习和理解命令的秘籍。这激发了我掌握Linux和使它容易使用的积极性。本文就是通往那里的阶梯。 1. ls命令 ls命令是列出目录内容(List Directory Contents)的意思。运行它就是列出文件夹里的内容，可能是文件也可能是文件夹。 1 root@tecmint:~# ls 2 3 Android-Games Music 4 Pictures Public 5 Desktop Tecmint.com 6 Documents TecMint-Sync 7 Downloads Templates “ls -l”命令已详情模式(long listing fashion)列出文件夹的内容。 01 root@tecmint:~# ls -l 02 03 total 40588 04 drwxrwxr-x 2 ravisaive ravisaive 4096 May 8 01:06 Android Games 05 drwxr-xr-x 2 ravisaive ravisaive 4096 May 15 10:50 Desktop 06 drwxr-xr-x 2 ravisaive ravisaive 4096 May 16 16:45 Documents 07 drwxr-xr-x 6 ravisaive ravisaive 4096 May 16 14:34 Downloads 08 drwxr-xr-x 2 ravisaive ravisaive 4096 Apr 30 20:50 […]
View Details下载:Newtonsoft.Json.dll 安装: 1.解压下载文件,得到Newtonsoft.Json.dll 2.在项目中添加引用..
View Details我认识到一个非常有悖常理的现象，优秀的程序员都是即懒又傻的。
因为懒，他才会写出各种各样的工具来替自己干活。因为懒，他才会想办法避免去写无聊重复的代码——因此避免的代码的冗余，消减了维护的成本，使重构变得容易。最终，这些由于懒惰激发出的动力而开发出的工具和最佳编程实践方法提升了产品的质量。
View Details在用wordpress做固定链接想实现静态化的时候，有时候会出现死链。这个时候就需要设置一下WordPress的伪静态了。 工具/原料 wordpress、ftp软件 步骤/方法 1 新建一个404.php文件，通过ftp软件上传到网站根目录web下，代码如下： <?php $pos = strrpos($qs, '://'); $pos = strpos($qs, '/', $pos + 4); $_SERVER['REQUEST_URI'] = substr($qs, $pos); $_SERVER['PATH_INFO'] = $_SERVER['REQUEST_URI']; include('index.php'); ?> 2 登录虚拟主机设置下404出错页 3 WordPress控制面板设置固定链接：控制面板-设置-固定链接-自定义结构 4 想设置其他链接格式，可参考以下结构标签： %year%：日志发表的年份，四位数字，如2009 %monthnum%：日志发表的月份，如05 %day%：日志发表的日期，如28 %hour%：日志发表的时间（小时），如15 %minute%：日志发表的时间（分钟），如43 %second%：日志发表的时间（秒），如33 %postname%：日志标题的缩略版本（日志/页面编辑界面上的日志别名）。因此“This Is A Great Post!”在URI中会变成this-is-a-great-post。 %post_id%：日志的唯一ID，如423 %category%：分类名称的缩略版本（添加新分类/编辑界面上的分类别名）。嵌套的子分类会作为嵌套子目录出现在URI中。出于运行方面的考虑，不推荐以%category%作为固定链接的起始部分。 %tag%：标签名称的缩略版本（添加新标签/编辑界面上的标签别名）。出于运行方面的考虑，不推荐以%tag%作为固定链接的起始部分。 %author%：作者名称的缩略版本。 转自：http://jingyan.baidu.com/article/4f7d5712824b4b1a201927bf.html
View Details遇到一个奇怪的问题，C#读取mysql中文正常，写入时发生乱码 网上查阅原因，发现如下信息 ——————————————————————— 一、错误读出 现象：一个已经存在数据的MySQL数据库，该数据库的数据用系统中其它软件、网页查看均正常，使用MySQLcc之类的客户端查看也正常，可是在新写的网页中总是显示乱码。 分析：其它系统都可以正常查看数据，说明数据本身是没有问题的。在网页中显示乱码，一定是网页的编码字符集和获取到的数据的编码字符集不一至。比如数据库的字符集是UTF8的，而网页的字符集是gb2312的，那么网页就会把UTF8编码的字体串当作gb2312的来处理，结果产生乱码。 解决办法：在连接数据库时，设定连接字符集，使连接字符集和当前网页或客户端程序使用的字符集一致。可以使用MySQL的Set Names指令设定连接字符集。假设网页的字体集为gb2312。在连接MySQL后，在连接上执行如下SQL语句： Set Names ‘gb2312’ 在以后所有这个连接上的查询，MySQL都会自动把数据库中的数据转换成gb2312编码格式传过来。 二、错误写入 现象：一个网页或程序向一个MySQL数据库中写数据，写完后，这个网页或程序自己可以正常读取数据，而从其它客户端或网页中读取数据都是乱码。 分析：其它的正常的客户端出现乱码，说明数据库中的字符编码不对。写数据的那个网页能正常读取，是因为写和读都用了错误的编码格式，将错就错，反而能读出正确的数据了。比如数据库中设定的字符集为UTF8，而网页使用gb2312编码执行了插入数据的SQL，那么MySQL就会把这些gb2312的编码当成是UTF8的编码写进数据库。当其它客户端访问数据时，会按系统的设定，以UTF8 格式读取数据，而数据其实是使用gb2312编码的，结果就出现了乱码。只有写数据的那个网页会把这些数据当成gb2312的，也只有那个网页能正常显示数据。 解决办法：同第一条，即：使用Set Names指令设定连接字符集。 在设定了连接字符体的连接上执行数据操作，所有的数据都将被MySQL自动、正确地转换为数据库中设定的编码格式保存。 通过以上两点，我们可以看到，只要在连接MySQL时，正确地设定了字符集，无论数据库本身是使用什么格式编码的，都能得到正确的结果。也许有人会以为写数据时设定的字符集必需和读数据时一致，事实上完全没有必要。程序所要做的只是告诉 MySQL，目前操作MySQL使用的是什么字符集即可。因为MySQL会自动完成如下的转换工作： 写数据库时用的字符集-->存诸数据的字符集-->读取数据的字符集。 笔者以为MySQL对多语言字符集的处理是非常优秀的，并且每次建立到MySQL的连接都会立刻使用Set Names设定字符集，然而最近还是出现了一回乱码，如下面所述。 三、无知的程序包 现象：使用C#编程，使用MySQL提供的连接程序库包访问数据库，使用 MySqlConnection类连接数据库，连接之后立刻调用Set Names设定连接字符串，然后使用MySqlCommand类执行SQL，并使用MySqlDataReader读取数查询结果。然而，当我调用 MySqlDataReader的成员方法GetString获取数据的时候，发现得到的全是乱码。百思不得其解。 分析：经仔细检查，确信问题没有出在MySQL连接上面，这时我想到了C#中对 string类型的处理。在C#中字符串和C/C++中有很大不同。在C/C++中一个字符就是一个字节，而在C#中，按不同的编码格式，一个字符也可以是多个字节的。比如”啊”就是一个字符，如果一个字符串s=”啊”; 那么s的Length属性为1，而不是C/C++中的2。我想MySQL程序包也许并不知道连接上传过来的字符是什么编码的，它因为无知，所以只是按单字节字符把这些数据组织成一个string，这个生成的string就是我得到的乱码。事实上也的确是这样。 解决办法：把这些数据重新组织起来，然后使用正确的编码方法重新生成string。C#中System.Text包内的Encoding类提供了字符集的编/解码方法。 1）首先还是设定连接字符集，以确认收到的字符的编码方式。 2）把GetString得到的字符串转换到byte数组中。 3）使用Systec.Text.Encoding包中相应字符集的解码方法GetString得到新的字符串。 为了通用性，我们使用System.Text.Encoding的默认字符集。连接数据库时，设置数据库连接字符集使用的SQL指令strSetCharset为如下值： string strSetCharset = “Set Names ” + System. Text. Encoding .Default. HeaderName; 在获取数据时，使用下面的函数得到真正的字符串： private string DBStringToNormal(string dbStr) { byte[] str = new byte[dbStr.Length]; for (int i = 0; i < dbStr.Length; ++i) str[i] = (byte)(dbStr[i]); return System.Text.Encoding.Default.GetString(str, 0, […]
View Details