文档库 最新最全的文档下载
当前位置:文档库 › sql server存储过程详细代码(超赞!)

sql server存储过程详细代码(超赞!)

sql server存储过程详细代码(超赞!)
sql server存储过程详细代码(超赞!)

use jxgl

--首先判断有没有已经建立up_getallstudents存储过程,有则先删除

if exists

(select name from sysobjects where name='up_getallstudents'and type ='p')

drop procedure up_getallstudents

--编写存储过程up_getallstudents,用于获取学生表students的所有记录

create procedure up_getallstudents

as

select*from students

--使用execute执行存储过程up_getallstudents

exec up_getallstudents

--也可写成

execute up_getallstudents

--编写一个存储过程up_insertstudent,完成学生表students数据的插入

--1、不带默认值的参数

create procedure up_insertstudent

@sid varchar(15),@sname varchar(30),@ssex char(10),

@sbirth datetime,@sbirthplace varchar(300)

as

begin

insert into students

(stu_id,stu_name,stu_sex,stu_birth,stu_birthplace)

values

(@sid,@sname,@ssex,@sbirth,@sbirthplace)

end

exec up_insertstudent'200712110111','肖玉峰','男','1975-02-05','山东省滕州市木石镇'

--等同于

exec up_insertstudent

@sname='肖玉峰',@sid='200712110111',@ssex='男',@sbirth=

'1975-02-05',@sbirthplace='山东省滕州市木石镇'

drop procedure up_insertstudent

delete students where stu_name='肖玉峰'

insert into students(stu_id,stu_name,stu_sex,stu_birth,

stu_birthplace)

values('200712110110','马缪','男','1986-010-1700:00:00.000','广东广州')

--编写一个存储过程up_delstudentbyname,根据输入的学生姓名,删除该学生记录

if object_id('up_delstudentbyname','p')is not null

drop procedure up_delstudentbyname

go

create procedure up_delstudentbyname

@sname varchar(30)

as

begin

delete from students

where stu_name=@sname

end

--调用存储过程的代码如下:

exec up_delstudentbyname'马缪'

select*from students

--编写一个存储过程up_getstuinformationbyname,根据输入的学生姓名,显示该学生的学号、姓名、课程名和成绩

if object_id('up_getstuinformationbyname','p')is not null

drop procedure up_getstuinformationbyname

go

create procedure up_getstuinformationbyname

@sname varchar(30)

as

begin

select"S".stu_id,stu_name,cour_name,score

from students"S",courses"C",course_score"CS"

where"S".stu_id="CS".stu_id and"CS".cour_id="C".cour_id and stu_name =@sname

end

--调用存储过程的代码如下:

exec up_getstuinformationbyname'仇立权'--ok

--2、带默认值的参数

--编写一个存储过程up_insertstuwithdefault,给参数定义默认值,完成学生表students数据的插入

drop procedure up_insertstuwithdefault

go

create procedure up_insertstuwithdefault

@sid varchar(15),@sname varchar(30),@ssex char(10)='男',

@sbirth datetime,@sbirthplace varchar(300)='',@semail varchar(50)= ''

as

begin

insert into students

(stu_id,stu_name,stu_sex,stu_birth,stu_birthplace,stu_email) values

(@sid,@sname,@ssex,@sbirth,@sbirthplace,@semail)

end

--调用存储过程的代码如下:

exec up_insertstuwithdefault@sid='11',@sname='赵小乐',@sbirth= '1976-07-05'

select*from students

--3、带输出参数

--在创建存储过程时,可以用关键字OUTPUT来创建一个输出参数,另外,调用时也必须给出OUTPUT关键字

--根据给定的学生姓名,获取该生的平均成绩

use jxgl

go

create procedure up_getAvgScorebyname

@aname varchar(30),@avgscore int output

as

begin

select@avgscore=avg(score)from students"S",course_score"CS" where"S".stu_id="CS".stu_id and"S".stu_name=@aname

end

--调用过程代码

declare@avgscore int---@avgscore可以改成任意变量名

exec up_getAvgScorebyname'仇立权',@avgscore output--ok

print@avgscore

--不能写成print'@avgscore'或print('@avgscore'),这是输出字符串

--等同于

select avg(score)from students s,course_score c

where s.stu_id=c.stu_id and s.stu_name='仇立权'

--4、带返回值的存储过程

--()print语句可以将用户定义的消息返回给客户端

--编写一个存储过程up_insertstudent2,在插入学生数据前,先判断一下学号是否存在,--如果存在,输出“要插入的学生的学号已经存在”;否则,插入学生数据,并返回”恭喜,数据插入成功“

create procedure up_insertstudent2

@sid varchar(15),@sname varchar(30),@ssex char(10)='男',@sbirth datetime

as

begin

if exists(select*from students where stu_id=@sid)

print('要插入的学生的学号已经存在')

--也可写成raiserror('要插入的学生的学号已经存在',16,1)

else

begin

insert into students

(stu_id,stu_name,stu_sex,stu_birth)

values

(@sid,@sname,@ssex,@sbirth)

print('恭喜,数据插入成功')

--raiserror('恭喜,数据插入成功',16,10)

end

end

drop procedure up_insertstudent2

select*from students

--调用过程代码如下:

exec up_insertstudent2@sid='13',@sname='张小飞',@sbirth=

'1983-02-01'--ok

exec up_insertstudent2@sid='10',@sname='张小龙',@sbirth=

'1988-01-08'--ok

--()return语句,return语句可以从过程、批处理或语句块中退出,不执行其后继语句create procedure up_delstudentbyname2

@sname varchar(30)

as

begin

delete from students

where stu_name=@sname

return@@rowcount--行计数

end

drop procedure up_delstudentbyname2

--调用存储过程的代码如下:

declare@ret_val int

exec@ret_val=up_delstudentbyname2'张小龙'--ok

select@ret_val as"删除的行"

--也可写成print@ret_val

--5、带变量的存储过程

/*在存储过程可以定义变量,包括全局变量(@@变量名)和局部变量(@变量名)。

用于保存存储过程中的临时结果

编写存储过程up_getavgscorebyname2,根据输入的学生姓名,计算该学生的平均成绩。根据该生平局成绩与全体学生平均成绩的关系,返回相应信息*/

use jxgl

go

create procedure up_getavgscorebyname2

@aname varchar(30),@resStr varchar(30)output

as

begin

declare@curAvg decimal(18,2)

declare@totalAvg decimal(18,2)

select@totalAvg=avg(score)from course_score

select curAvg=avg(score)from students"S",course_score"CS" where"S".stu_id="CS".stu_id and"S".stu_name=@aname

if@curAvg>@totalAvg

set@resStr='高于平均分'

else

set@resStr='低于平均分'

print('总平均分为'+convert(varchar(18),@totalAvg))

print('该生平均分'+convert(varchar(18),@curAvg))

print@resStr

drop procedure up_getavgscorebyname2

--调用存储过程的代码

declare@resstring varchar(30)

exec up_getavgscorebyname2'甘明',@resstring output

--这句和要求不一样,不知道哪里出问题

--6、使用output游标参数

--output游标参数用来将存储过程的局部游标传递回执行调用的批处理、存储过程或触发器

/*编写一个带有output型游标参数的存储过程up_getstudent_cursor,再编写一个过程up_printstudentbycursor,对游标中的数据进行显示*/

--存储过程up_getstudent_cursor的代码如下

create procedure up_getstudent_cursor

@student_cursor cursor varying output

as

begin

set@student_cursor=cursor

forward_only static for

select stu_id,stu_name,stu_birth,stu_sex

from students;

open@student_cursor;

end

--存储过程printstudentbycursor代码如下:

create procedure printstudentbycursor

as

declare@Mycursor cursor

declare@axh varchar(15)

declare@asname varchar(30)

declare@asbir datetime

declare@assex char(10)

begin

exec getstudent_cursor@student_cursor=@Mycursor output

fetch next from@Mycursor into@axh,@asname,@asbir,@assex

while(@@fetch_status=0)

begin

print('学号:'+@axh+'姓名:'+@asname+'出生年月:'+convert(varchar(30), @asbir,120)+'性别'+@assex)

fetch next from@Mycursor into@axh,@asname,@asbir,@assex

end

close@Mycursor; deallocate@Mycursor; end

--调用存储过程的代码如下exec printstudentbycursor

mysql分享(1)-sql语句执行的11个步骤

IREDPURE mysql执行查询语句的11个步骤(分享1) ZERO 2016/02/19

每个人都会犯错误,有的人把犯过的错误记录下来,进一步总结,形成了自己的一套理论;有的人,则在同一个错误上一错再错,不停的抱怨,然后 再犯错,然后再抱怨,产生了一个死循环…… 1、项目结束后的思考 每个项目的结束,每个人都会有自己的收获,不同水平的人总结出来的东西可能不一样!但是对自己而言,都是进步,都是让自己在原有的基础上强大 了一点点。我们在每个项目结束后,都应该对自己做一个总结,这是我们强大的来源,日记月累,必定是一笔不小的财富! 2、mysql查询语句执行的11个步骤 (8) select (9) distinct (1) from (1) (3) join (1) (2) on (4) where (5) group by (6) with {cube | rollup} (7) having (10) order by (11) limit 以上是这个11个步骤,这是《mysql技术内幕之sql编程》这本书上面得出的结论,有兴趣的同学也可以去看下,很不错的一本书! ps: Paul DuBois( 杜波依斯) Sun 公司MySQL文档团队的技术作者、开源社区和MySQL社区活跃的技术专家,同时也是一名数据库管理员。他曾参 与过MySQL在线文档的编写工作 接下来我们举一个例子,分别解释sql语句的执行流程! 3、举例说明sql语句的执行流程 【1】进行准备工作 CREATE TABLE `Customer` ( `CustomerID` varchar(10) NOT NULL, `CityName` varchar(10) NOT NULL, PRIMARY KEY (`CustomerID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOCK TABLES `Customer` WRITE; INSERT INTO `Customer` VALUES ('163','hangzhou'),('9you','shanghai'),('tx','hangzhou'), ('baidu','hangzhou'); UNLOCK TABLES; ================================================================================================================= CREATE TABLE `Orders` ( `OrdersID` int(11) NOT NULL AUTO_INCREMENT, `CustomerID` varchar(10) DEFAULT NULL, PRIMARY KEY (`OrdersID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOCK TABLES `Orders` WRITE; INSERT INTO `Orders` VALUES (1,'163'),(2,'163'),(3,'9you'),(4,'9you'),(5,'9you'),(6,'tx'),(7,null); UNLOCK TABLES; =====================================================================================================最终结果如下: 通过如下语句查询来杭州,且订单小于2的用户,并且查询出来他们的订单数量,查询的结果按照订单数从大到小排列: select Customer.CustomerID, Customer.CityName, count(Orders.CustomerID) as OrdersNumber from Customer left join Orders on Customer.CustomerID = Orders.CustomerID where Customer.CityName='HangZhou' group by Customer.CustomerID having count(Orders.CustomerID) < 2 order by OrdersNumber desc; ps:在得出正确的语句之前,我写了两次错误的sql,个人认为还是很有借鉴意义的:

SQLServer存储过程返回值总结.

SQLServer 存储过程返回值总结 1. 存储过程没有返回值的情况 (即存储过程语句中没有 return 之类的语句用方法 int count = ExecuteNonQuery(..执行存储过程其返回值只有两种情况 (1假如通过查询分析器执行该存储过程,在显示栏中假如有影响的行数,则影响几行 count 就是几 (2假如通过查询分析器执行该存储过程, 在显示栏中假如显示 ' 命令已成功完成。 ' 则 count = -1;在显示栏中假如有查询结果,则 count = -1 总结:A.ExecuteNonQuery(该方法只返回影响的行数,假如没有影响行数,则该方法的返回值只能是 -1,不会为 0。 B.不论 ExecuteNonQuery(方法是按照 CommandType.StoredProcedure 或者 CommandType.Text 执行, 其效果和 A 一样。 --------------------------------------------------------------------------------------------------------------------------------------------------- 2. 获得存储过程的返回值 --通过查询分析器获得 (1不带任何参数的存储过程 (存储过程语句中含有 return ---创建存储过程 CREATE PROCEDURE testReturn AS return 145 GO ---执行存储过程

DECLARE @RC int exec @RC=testReturn select @RC ---说明 查询结果为 145 (2带输入参数的存储过程 (存储过程语句中含有 return ---创建存储过程 create procedure sp_add_table1 @in_name varchar(100, @in_addr varchar(100, @in_tel varchar(100 as if(@in_name = '' or @in_name is null return 1 else begin insert into table1(name,addr,tel values(@in_name,@in_addr,@in_tel return 0

SQL语句执行效率及分析

SQL语句执行效率及分析 2. SQL提高查询效率 2008-05-12 21:20 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select id from t where num=0 3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。 4.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用 索引而进行全表扫描,如: select id from t where num=10 or num=20 可以这样查询: select id from t where num=10 union all select id from t where num=20 5.in 和 not in 也要慎用,否则会导致全表扫描,如: select id from t where num in(1,2,3) 对于连续的数值,能用 between 就不要用 in 了: select id from t where num between 1 and 3 6.下面的查询也将导致全表扫描: select id from t where name like '%abc%' 若要提高效率,可以考虑全文检索。 7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因 而无法作为索引选择的输入项。如下面语句将进行全表扫描: select id from t where num=@num 可以改为强制查询使用索引: select id from t with(index(索引名)) where num=@num 8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用 索引而进行全表扫描。如:

SqlServer存储过程基本语法

动态语句基本语法 1 :普通SQL语句可以用exec执行 Select * from tableName exec('select * from tableName') exec sp_executesqlN'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL declare @fnamevarchar(20) set @fname = 'FiledName' Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。exec('select ' + @fname + ' from tableName') -- 请注意加号前后的单引号的边上加空格 当然将字符串改成变量的形式也可 declare @fnamevarchar(20) set @fname = 'FiledName' --设置字段名 declare @s varchar(1000) set @s = 'select ' + @fname + ' from tableName' exec(@s) -- 成功 exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000) set @s = 'select ' + @fname + ' from tableName' exec(@s) -- 成功 exec sp_executesql @s -- 此句正确 3. 输出参数 declare @numint, @sqlsnvarchar(4000) set @sqls='select count(*) from tableName' exec(@sqls) --如何将exec执行结果放入变量中? declare @numint, @sqlsnvarchar(4000) set @sqls='select @a=count(*) from tableName ' execsp_executesql @sqls,N'@aint output',@num output select @num 1 :普通SQL语句可以用Exec执行例: Select * from tableName Exec('select * from tableName')

SQL语句执行顺序

一、sql语句的执行步骤: 1)语法分析,分析语句的语法是否符合规范,衡量语句中各表达式的意义。 2)语义分析,检查语句中涉及的所有数据库对象是否存在,且用户有相应的权限。 3)视图转换,将涉及视图的查询语句转换为相应的对基表查询语句。 4)表达式转换,将复杂的 SQL 表达式转换为较简单的等效连接表达式。 5)选择优化器,不同的优化器一般产生不同的“执行计划” 6)选择连接方式,ORACLE有三种连接方式,对多表连接 ORACLE 可选择适当的连接方式。7)选择连接顺序,对多表连接 ORACLE 选择哪一对表先连接,选择这两表中哪个表做为源数据表。 8)选择数据的搜索路径,根据以上条件选择合适的数据搜索路径,如是选用全表搜索还是利用索引或是其他的方式。 9)运行“执行计划” 二、oracle 共享原理: ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中,可以被所有的数据库用户共享当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径. 这个功能大大地提高了SQL的执行性能并节省了内存的使用 三、oracle 语句提高查询效率的方法:1: where column in(select * from ... where ...); 2:... where exists (select 'X' from ...where ...); 第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询使用EXIST,Oracle系统会首先检查主查询,然后运行子查询直到它找到第一个匹配项,这就节省了时间 Oracle系统在执行IN子查询时,首先执行子查询,并将获得的结果列表存放在在一个加了索引的临时表中避免使用having字句避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销 SQL Select语句完整的执行顺序: 1、from子句组装来自不同数据源的数据; 2、where子句基于指定的条件对记录行进行筛选; 3、group by子句将数据划分为多个分组; 4、使用聚集函数进行计算; 5、使用having子句筛选分组; 6、计算所有的表达式; 7、使用order by对结果集进行排序。

SqlServer存储过程的事务模式编写

SQL Server在存储过程中编写事务处理代码的三种方法 SQL Server中数据库事务处理是相当有用的,鉴于很多SQL初学者编写的事务处理代码存往往存在漏洞,本文我们介绍了三种不同的方法,举例说明了如何在存储过程事务处理中编写正确的代码。希望能够对您有所帮助。 在编写SQL Server 事务相关的存储过程代码时,经常看到下面这样的写法: begin tran update statement 1 ... update statement 2 ... delete statement 3 ... commit tran 这样编写的SQL存在很大隐患。请看下面的例子: create table demo(id int not null) go begin tran insert into demo values (null) insert into demo values (2) commit tran go 执行时会出现一个违反not null 约束的错误信息,但随后又提示(1 row(s) affected)。我们执行select * from demo 后发现insert into demo values(2) 却执行成功了。这是什么原因呢? 原来SQL Server在发生runtime 错误时,默认会rollback引起错误的语句,而继续执行后续语句。 如何避免这样的问题呢? 有三种方法:

1. 在事务语句最前面加上set xact_abort on set xact_abort on begin tran update statement 1 ... update statement 2 ... delete statement 3 ... commit tran go 当xact_abort 选项为on 时,SQL Server在遇到错误时会终止执行并rollback 整个事务。 2. 在每个单独的DML语句执行后,立即判断执行状态,并做相应处理。 begin tran update statement 1 ... if@@error<>0 begin rollback tran goto labend end delete statement 2 ... if@@error<>0 begin rollback tran goto labend end commit tran labend: go 3. 在SQL Server 2005中,可利用try...catch 异常处理机制。

执行一条sql语句update多条记录实现思路

执行一条sql语句update多条记录实现思路 如果你想更新多行数据,并且每行记录的各字段值都是各不一样,怎么办? 通常情况下,我们会使用以下SQL语句来更新字段值: UPDATE mytable SET myfield='value' WHERE other_field='other_value'; 但是,如果你想更新多行数据,并且每行记录的各字段值都是各不一样,你会怎么办呢?举个例子,我的博客有三个分类目录(免费资源、教程指南、橱窗展示),这些分类目录的信息存储在数据库表categories中,并且设置了显示顺序字段 display_order,每个分类占一行记录。如果我想重新编排这些分类目录的顺序,例如改成(教程指南、橱窗展示、免费资源),这时就需要更新categories表相应行的display_order字段,这就涉及到更新多行记录的问题了,刚开始你可能会想到使用循环执行多条UPDATE语句的方式,就像以下的php 程序示例: ? 1 2 3 4 5 foreach ($display_order as $id => $ordinal) { $sql="UPDATE categories SET display_order = $ordinal WHERE id = $id"; mysql_query($sql); } 这种方法并没有什么任何错误,并且代码简单易懂,但是在循环语句中执行了不止一次SQL 查询,在做系统优化的时候,我们总是想尽可能的减少数据库查询的次数,以减少资源占用,同时可以提高系统速度。 幸运的是,还有更好的解决方案,下面列举两种常用的方案只不过SQL语句稍微复杂点,但是只需执行一次查询即可,语法如下: ? 1 2 3 4 5 6 7 8 ?第一种:IF--THEN语句结合UPDATE mytable SET myfield = CASE other_field WHEN 1 THEN 'value' WHEN 2 THEN 'value' WHEN 3 THEN 'value' END

oracle sql语句执行顺序

要知道SQL语句,我想我们有必要知道SQL Server查询分析器怎么执行我们的SQL语句的,我们很多人会看执行计划,或者用Profiler来监视和调优查询语句或者存储过程慢的原因,但是如果我们知道查询分析器的执行逻辑顺序,下手的时候就胸有成竹,那么下手是不是有把握点呢? 一、查询的逻辑执行顺序 (1) FROM left_table (3) join_type JOIN right_table (2) ON join_condition (4) WHERE where_condition (5) GROUP BY group_by_list (6) WITH {cube | rollup} (7) HAVING having_condition (8) SELECT (9) DISTINCT (11) top_specification select_list (9) ORDER BY order_by_list 标准的SQL 的解析顺序为: (1) FROM 子句组装来自不同数据源的数据 (2) WHERE 子句基于指定的条件对记录进行筛选 (3) GROUP BY 子句将数据划分为多个分组 (4) 使用聚合函数(avg)进行计算 (5) 使用HAVING子句筛选分组 (6) 计算所有的表达式 (7) 使用ORDER BY对结果集进行排序 二、执行顺序 1. FROM:对FROM子句中前两个表执行笛卡尔积生成虚拟表vt1 2. ON: 对vt1表应用ON筛选器只有满足join_condition 为真的行才被插入vt2 3. OUTER(join):如果指定了OUTER JOIN保留表(preserved table)中未找到的行将行作为外部行添加到vt2,生成t3,如果from包含两个以上表,则对上一个联结生成的结果表和下一个表重复执行步骤和步骤直接结束。 4. WHERE:对vt3应用WHERE 筛选器只有使where_condition 为true的行才被插入vt4 5. GROUP BY:按GROUP BY子句中的列列表对vt4中的行分组生成vt5 6. CUBE|ROLLUP:把超组(supergroups)插入vt6,生成vt6 7. HAVING:对vt6应用HAVING筛选器只有使having_condition 为true的组才插入vt7 8. SELECT:处理select列表产生vt8 9. DISTINCT:将重复的行从vt8中去除产生vt9 10. ORDER BY:将vt9的行按order by子句中的列列表排序生成一个游标vc10 11. TOP:从vc10的开始处选择指定数量或比例的行生成vt11 并返回调用者 看到这里,那么用过Linq to SQL的语法有点相似啊?如果我们我们了解了SQL Server 执行顺序,那么我们就接下来进一步养成日常SQL的好习惯,也就是在实现功能的同时有考虑性能的思想,数据库是能进行集合运算的工具,我们应该尽量的利用这个工具,所谓集合运算实际就是批量运算,就是尽量减少在客户端进行大数据量的循环操作,而用SQL语句或者存储过程代替。 三、只返回需要的数据 返回数据到客户端至少需要数据库提取数据、网络传输数据、客户端接收数据以及客户

SqlServer 使用存储过程 导出为Excel

SqlServer 使用存储过程导出为Excel 一个脱离office组件的可以将语句结果导出到Excel的过程 --1.执行时所连接的服务器决定文件存放在哪个服务器 [sql]view plain copy print? 1.CREATE PROC ExportFile 2. @QuerySql VARCHAR(max) 3. ,@Server VARCHAR(20) 4. ,@User VARCHAR(20) = 'sa' 5. ,@Password VARCHAR(20) 6. ,@FilePath NVARCHAR(100) = 'c:\ExportFile.csv' 7.AS 8.DECLARE @tmp VARCHAR(50) = '[##Table' + CONVERT(VARCHAR(36),NEWID())+']' 9.BEGIN TRY 10.DECLARE @Sql VARCHAR(max),@DataSource VARCHAR(max)=''; 11.--判断是否为远程服务器 12. IF @Server <> '.'AND @Server <> '127.0.0.1' 13.SET @DataSource = 'OPENDATASOURCE(''SQLOLEDB'',''Data Source='+@Server+';User ID='+@Us er+';Password='+@Password+''').' 14.--将结果集导出到指定的数据库 15.SET @Sql = REPLACE(@QuerySql,' from ',' into '+@tmp+ ' from ' + @DataSource) 16. PRINT @Sql 17.EXEC(@Sql) 18. 19.DECLARE @Columns VARCHAR(max) = '',@Data NVARCHAR(max)='' 20.SELECT @Columns = @Columns + ',''' + name +''''--获取列名(xp_cmdshell导出文件没有列名) 21. ,@Data = @Data + ',Convert(Nvarchar,[' + name +'])'--将结果集所在的字段更新为nvarchar(避 免在列名和数据union的时候类型冲突) 22.FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb..'+@tmp) 23.SELECT @Data = 'SELECT ' + SUBSTRING(@Data,2,LEN(@Data)) + ' FROM ' + @tmp 24.SELECT @Columns = 'Select ' + SUBSTRING(@Columns,2,LEN(@Columns)) 25.--使用xp_cmdshell的bcp命令将数据导出 26.EXEC sp_configure 'xp_cmdshell',1 27. RECONFIGURE 28.DECLARE @cmd NVARCHAR(4000) = 'bcp "' + @Columns+' Union All ' + @Data+'" queryout ' + @Fi lePath + ' -c' + CASE WHEN RIGHT(@FilePath,4) = '.csv'THEN' -t,'ELSE''END + ' -T' 29. PRINT @cmd 30.exec sys.xp_cmdshell @cmd 31.EXEC sp_configure 'xp_cmdshell',0 32. RECONFIGURE 33.EXEC('DROP TABLE ' + @tmp) 34.END TRY 35.BEGIN CATCH

SQL语句执行效率及分析(note)

SQL语句执行效率及分析(note) 1.关于SQL查询效率,100w数据,查询只要1秒,与您分享: 机器情况 p4: 2.4 内存: 1 G os: windows 2003 数据库: ms sql server 2000 目的: 查询性能测试,比较两种查询的性能 SQL查询效率step by step -- setp 1. -- 建表 create table t_userinfo ( userid int identity(1,1) primary key nonclustered, nick varchar(50) not null default '', classid int not null default 0, writetime datetime not null default getdate() ) go -- 建索引 create clustered index ix_userinfo_classid on t_userinfo(classid) go -- step 2. declare @i int declare @k int declare @nick varchar(10) set @i = 1 while @i<1000000 begin set @k = @i % 10 set @nick = convert(varchar,@i) insert into t_userinfo(nick,classid,writetime) values(@nick,@k,getdate()) set @i = @i + 1

end -- 耗时08:27 ,需要耐心等待 -- step 3. select top 20 userid,nick,classid,writetime from t_userinfo where userid not in ( select top 900000 userid from t_userinfo order by userid asc ) -- 耗时8 秒,够长的 -- step 4. select https://www.wendangku.net/doc/292242045.html,erid,b.nick,b.classid,b.writetime from ( select top 20 https://www.wendangku.net/doc/292242045.html,erid from ( select top 900020 userid from t_userinfo order by userid asc ) a order by https://www.wendangku.net/doc/292242045.html,erid desc ) a inner join t_userinfo b on https://www.wendangku.net/doc/292242045.html,erid = https://www.wendangku.net/doc/292242045.html,erid order by https://www.wendangku.net/doc/292242045.html,erid asc -- 耗时1 秒,太快了吧,不可以思议 -- step 5 where 查询 select top 20 userid,nick,classid,writetime from t_userinfo where classid = 1 and userid not in ( select top 90000 userid from t_userinfo where classid = 1 order by userid asc ) -- 耗时2 秒 -- step 6 where 查询 select https://www.wendangku.net/doc/292242045.html,erid,b.nick,b.classid,b.writetime from ( select top 20 https://www.wendangku.net/doc/292242045.html,erid from ( select top 90000 userid from t_userinfo

列出SQLServer数据库中所有的存储过程

列出SQLServer数据库中所有的存储过程 Dim cn As rdoConnection Dim ps As rdoPreparedStatement Dim rs As rdoResultset Dim strConnect As String Dim strSQL As String '利用 DSNless 连接到 pubs 数据库 '改变参数以适合你自己的 SQL Server strConnect = "Driver={SQL Server}; Server=myserver; " & "Database=pubs; Uid=sa; Pwd=" Set cn = rdoEnvironments(0).OpenConnection(dsName:="", Prompt:=rdDriverNoPrompt, ReadOnly:=False, Connect:=strConnect) strSQL = "Select https://www.wendangku.net/doc/292242045.html,,https://www.wendangku.net/doc/292242045.html,,https://www.wendangku.net/doc/292242045.html,,sc.length " & "FROM syscolumns sc,master..systypes st,sysobjects so " & "WHERE sc.id in (select id from sysobjects where type ='P')" & " AND so.type ='P' " & "AND sc.id = so.id " & "AND sc.type = st.type " & "AND sc.type <> 39" Set ps = cn.CreatePreparedStatement("MyPs", strSQL) Set rs = ps.OpenResultset(rdOpenStatic) list1.AddItem "SP Name,Param Name,Data Type,Length" While Not rs.EOF list1.AddItem rs(0) & " , " & rs(1) & " , " & rs(2) & " , " & rs(3) rs.MoveNext Wend rs.Close Set rs = Nothing cn.Close Set cn = Nothing 【

Java调用SQLServer存储过程分析

Java调用SQL Server存储过程 Java调用SQL Server的存储过程详解,主要内容: ●使用不带参数的存储过程 ●使用带有输入参数的存储过程 ●使用带有输出参数的存储过程 ●使用带有返回状态的存储过程 ●使用带有更新计数的存储过程 1.使用不带参数的存储过程 使用JDBC 驱动程序调用不带参数的存储过程时,必须使用call SQL 转义序列。不带参数的call 转义序列的语法如下所示: 实例:在SQL Server 2005 AdventureWorks示例数据库中创建以下存储过程: 此存储过程返回单个结果集,其中包含一列数据(由Person.Contact 表中前十个联系人的称呼、名称和姓氏组成)。 在下面的实例中,将向函数传递AdventureWorks示例数据库的打开连接,然后使用executeQuery方法调用GetContactFormalNames存储过程。

2.使用带有输入参数的存储过程 使用JDBC 驱动程序调用带参数的存储过程时,必须结合SQLServerConnection 类的prepareCall方法使用call SQL转义序列。带有IN参数的call转义序列的语法如下所示: 构造call转义序列时,请使用?(问号)字符来指定IN参数。此字符充当要传递给该存储过程的参数值的占位符。可以使用SQLServerPreparedStatement类的setter方法之一为参数指定值。可使用的setter方法由IN参数的数据类型决定。 向setter方法传递值时,不仅需要指定要在参数中使用的实际值,还必须指定参数在存储过程中的序数位置。例如,如果存储过程包含单个IN参数,则其序数值为1。如果存储过程包含两个参数,则第一个序数值为1,第二个序数值为2。 作为调用包含IN参数的存储过程的实例,使用SQL Server 2005 AdventureWorks示例数据库中的uspGetEmployeeManagers存储过程。此存储过程接受名为EmployeeID的单个输入参数(它是一个整数值),然后基于指定的EmployeeID返回雇员及其经理的递归列表。下面是调用此存储过程的Java代码:

解析SQL执行过程

为了将用户写的SQL文本转化为Oracle认识的且可执行的语句,这个过程就叫做解析过程。解析分为硬解析和软解析。一条SQL语句在第一次被执行时必须进行硬解析。 当客户端发出一条SQL语句(也可以是一个存储过程或者一个匿名PL/SQL块)进入shared pool时(注意,我们从前面已经知道,Oracle对这些SQL不叫做SQL语句,而是称为游标。因为Oracle在处理SQL时,需要很多相关的辅助信息,这些辅助信息与SQL语句一起组成了游标),Oracle首先将SQL文本转化为ASCII值,然后根据hash函数计算其对应的hash值(hash_value)。根据计算出的hash值到library cache中找到对应的bucket,然后比较bucket里是否存在该SQL语句。 如果不存在,则需要按照我们前面所描述的,获得shared pool latch,然后在shared pool 中的可用chunk链表(也就是bucket)上找到一个可用的chunk,之后释放shared pool latch。在获得了chunk以后,这块chunk就可以认为是进入了library cache。接下来,进行硬解析过程。硬解析包括以下几个步骤。 对SQL语句进行文法检查,看是否有文法错误。比如没有写from、select拼写错误等。如果存在文法错误,则退出解析过程。 到数据字典里校验SQL语句涉及的对象和列是否都存在。如果不存在,则退出解析过程。这个过程会加载dictionary cache。 将对象进行名称转换。比如将同名词翻译成实际的对象等。比如select * from t中,t是一个同名词,指向hr.t1,于是Oracle将t转换为hr.t1。如果转换失败,则退出解析过程。 检查发出SQL语句的用户是否具有访问SQL语句里所引用的对象的权限。如果没有权限,则退出解析过程。 通过优化器创建一个最优的执行计划。这个过程会根据数据字典里记录的对象的统计信息,来计算最优的执行计划。这一步牵涉大量数学运算,是最消耗CPU资源的。 将该游标所产生的执行计划、SQL文本等装载进library cache的heap中。 在硬解析的过程中,进程会一直持有library cache latch,直到硬解析结束为止。硬解析结束以后,会为SQL语句产生两个游标,一个是父游标,另一个是子游标。父游标里主要包含两种信息:SQL文本以及优化目标(optimizer goal)。父游标在第一次打开时被锁定,直到其他所有的session都关闭该游标后才被解锁。当父游标被锁定的时候是不能被交换出library cache的,只有在解锁以后才能被交换出library cache。父游标被交换出内存时,父游标对应的所有子游标也被交换出library cache。子游标包括游标所有的信息,比如具体的执行计划、绑定变量等。子游标随时可以被交换出library cache,当子游标被交换出library cache

SqlServer调用存储过程返回结果集

USE[zhuhaioa7-4] GO /****** Object: StoredProcedure [dbo].[proc_records] Script Date: 12/26/2014 20:31:09 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure[dbo].[proc_records] as DECLARE@map_table table(r_key varchar(100),r_value varchar(50)) DECLARE@type_id varchar(40) DECLARE@type_name varchar(50) DECLARE@project_id varchar(40) DECLARE@project_name varchar(50) DECLARE@payTypeMoney numeric(16, 2) DECLARE@sumPayTypeMoney numeric(16, 2) BEGIN set@payTypeMoney= 0 set@sumPayTypeMoney= 0 --查询项目列表 DECLARE project_cursor CURSOR for select ep_id,ep_name from AB_engineeringPhase where account_id='2' open project_cursor fetch next from project_cursor into@project_id,@project_name while@@FETCH_STATUS= 0 begin

PLSQL怎么执行SQL语句

通过f5查看到的执行计划,其实是pl/sql developer工具内部执行查询 plan_table表然后格式化的结果。 select * from plan_table where statement_id=...。其中 description列描述当前的数据库操作, object owner列表示对象所属用户, object name表示操作的对象, cost列表示当前操作的代价(消耗),这个列基本上就是评价sql语句的优劣,cardinality列表示操作影响的行数, bytes列表示字节数篇二:plsqldeveloper工具使用教程 plsql入门 pl/sql的概述 pl/sql的优势 pl/sql是一种块结构的语言,允许你将业务逻辑封装在一起,这是到目前为止使用pl/sql的最大优势 pl/sql是在服务器上运行,可以与数据库和sql引擎直接进行交互, pl/sql是什么? (procedural language/sql) 是oracle在标准的sql语言上的扩展,pl/sql不仅允许嵌入sql语言,还可以定义变量和常量,允许使用条件语句和循环语句,允许使用列外处理各种错误,这样使得它的功能变得更加强大。 特性: 减少java程序的复杂性 一.过程,函数,触发器是pl/sql编写的 二.过程、函数、触发器是在oracle中 三. pl/sql是非常强大的数据库过程语言 四.过程,函数可以再java程序中调用 为什么学? a) 提高应用程序的运行性能 b) 模块化的设计思想[分页的过程,订单的过程,转账的过程] c) 减少网络传输量(传统的方法,用sql语句传输!现在就只需要调用存储过程) d) 提高安全性(传统sql 可以看到表名字段等…) 不好: 移植性不好,(你写好的存储过程,函数等当我们要换数据库时,这些东西就没用了)开发工具: 1. sqlplus 开发工具 是oracle公司提供的一个工具,这个因为我们在以前介绍过: 2. pl/sql developer开发工具 pl/sql developer是用于开发pl/sql块的集成开发环境(ide) 它是一个独立的产品,而不是oracle的一个附带品, createprocedure sp_pro1//存储过程名字 is begin ---执行部分 insert into mytest values(‘’,’’); end; / 查看错误信息

(完整版)SQLSERVER存储过程大总结

SQLSERVER存储过程使用说明书 引言 首先介绍一下什么是存储过程:存储过程就是将常用的或很复杂的工作,预先用SQL语句写好并用一个指定的名称存储起来,并且这样的语句是放在数据库中的,还可以根据条件执行不同SQL语句,那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。 请大家先看一个小例子: create proc query_book as select * from book go --调用存储过程 exec query_book 请大家来了解一下存储过程的语法。 Create PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ] 一、参数简介 1、procedure_name 新存储过程的名称。过程名必须符合标识符规则,且对于数据库及其所有者必须唯一。 要创建局部临时过程,可以在 procedure_name 前面加一个编号 符 (#procedure_name),要创建全局临时过程,可以在 procedure_name 前面加两

个编号符 (##procedure_name)。完整的名称(包括 # 或 ##)不能超过 128 个字符。指定过程所有者的名称是可选的。 2、;number 是可选的整数,用来对同名的过程分组,以便用一条 Drop PROCEDURE 语句即可将同组的过程一起除去。例如,名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。Drop PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符,则数字不应包含在标识符中,只应 在 procedure_name 前后使用适当的定界符。 3、@parameter 过程中的参数。在 Create PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值(除非定义了该参数的默认值)。存储过程最多可以有 2100 个参数。 使用@符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身;相同的参数名称可以用在其它过程中。默认情况下,参数只能代替常量,而不能用于代替表名、列名或其它数据库对象的名称。 4、data_type 参数的数据类型。所有数据类型(包括 text、ntext 和 image)均可以用作存储过程的参数。不过,cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor,也必须同时指定 VARYING 和 OUTPUT 关键字。 说明:对于可以是cursor 数据类型的输出参数,没有最大数目的限制。 5、VARYING 指定作为输出参数支持的结果集(由存储过程动态构造,内容可以变化)。仅适用于游标参数。 6、default 参数的默认值。如果定义了默认值,不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字,那么默认值中可以包含通配符(%、_、[] 和 [^])。 7、OUTPUT

相关文档
相关文档 最新文档