博客访问： 335788
博文数量： 75
博客积分： 1415
博客等级： 中尉
技术积分： 712
注册时间：
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Mysql/postgreSQL
1.Sql存储过程概述
在大型数据库系统中，存储过程和触发器具有很重要的作用。无论是存储过程还是触发器，都是SQL 语句和流程控制语句的集合。就本质而言，触发器也是一种存储过程。存储过程在运算时生成执行方式，所以，以后对其再运行时其执行速度很快。SQL Server 2000 不仅提供了用户自定义存储过程的功能，而且也提供了许多可作为工具使用的系统存储过程
存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
在SQL Server 的系列版本中存储过程分为两类：系统提供的存储过程和用户自定义存储过程。系统过程主要存储在master 数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server 提供支持。通过系统存储过程，MS SQL Server 中的许多管理性或信息性的活动（如了解数据库对象、数据库信息）都可以被顺利有效地完成。尽管这些系统存储过程被放在master 数据库中，但是仍可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。在本章中所涉及到的存储过程主要是指用户自定义存储过程。
1.2 存储过程优点
当利用MS SQL Server 创建一个应用程序时，Transaction-SQL 是一种主要的编程语言。若运用Transaction-SQL 来进行编程，有两种方法。其一是，在本地存储Transaction- SQL 程序，并创建应用程序向SQL Server 发送命令来对结果进行处理。其二是，可以把部分用Transaction-SQL 编写的程序作为存储过程存储在SQL Server 中，并创建应用程序来调用存储过程，对数据结果进行处理存储过程能够通过接收参数向调用者返回结果集，结果集的格式由调用者确定；返回状态值给调用者，指明调用是成功或是失败；包括针对数据库的操作语句，并且可以在一个存储过程中调用另一存储过程。 我们通常更偏爱于使用第二种方法，即在SQL Server 中使用存储过程而不是在客户计算机上调用Transaction-SQL 编写的一段程序，原因在于存储过程具有以下优点：1） 存储过程允许标准组件式编程存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的SQL 语句。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。（2） 存储过程能够实现较快的执行速度如果某一操作包含大量的Transaction-SQL 代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的执行计划。而批处理的Transaction- SQL 语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。（3） 存储过程能够减少网络流量对于同一个针对数据数据库对象的操作（如查询、修改），如果这一操作所涉及到的 Transaction-SQL 语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，否则将是多条SQL 语句，从而大大增加了网络流量，降低网络负载。（4） 存储过程可被作为一种安全机制来充分利用系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。（我们将在14 章“SQLServer 的用户和安全性管理”中对存储过程的这一应用作更为清晰的介绍）注意：存储过程虽然既有参数又有返回值，但是它与函数不同。存储过程的返回值只是指明执行是否成功，并且它不能像函数那样被直接调用，也就是在调用存储过程时，在存储过程名字前一定要有EXEC保留字。
2.基本语法
2.1创建存储过程
Create procedure sp_name //sp_name 自己给存储过程去的名称
Create Proc dbo.存储过程名存储过程参数AS执行语句RETURN执行存储过程GO
-- 要创建存储过程的数据库
-- 判断要创建的存储过程名是否存在
if Exists(Select name From sysobjects Where name='csp_AddInfo' And
-- 删除存储过程
Drop Procedure dbo.csp_AddInfo
-- 创建存储过程
Create Proc dbo.csp_AddInfo
-- 存储过程参数
@UserName varchar(16),
@Pwd varchar(50),
@Age smallint,
@*** varchar(6)
-- 存储过程语句体
insert into Uname (UserName,Pwd,Age,***)
values (@UserName,@Pwd,@Age,@***)
-- 执行存储过程
EXEC csp_AddInfo 'Junn.A','123456',20,'男'
新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。　　要创建局部临时过程，可以在 procedure_name 前面加一个编号符 (#procedure_name)，要创建全局临时过程，可以在 procedure_name 前面加两个编号符 (##procedure_name)。完整的名称（包括 # 或 ##）不能超过 128 个字符。指定过程所有者的名称是可选的。
2.2调用存储过程
Call procedure sp-name （）
注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递
1）第一种方法: 使用output参数
USE AdventureWorks;
IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL
DROP PROCEDURE Production.usp_GetList;
CREATE PROCEDURE Production.usp_GetList
@product varchar(40)
, @maxprice money
, @compareprice money OUTPUT
, @listprice money OUT
SELECT p.name AS Product, p.ListPrice AS 'List Price'
FROM Production.Product p
JOIN Production.ProductSubcategory s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.name LIKE @product AND p.ListPrice < @maxprice;
-- Populate the output variable @listprice.
SET @listprice = (SELECT MAX(p.ListPrice)
FROM Production.Product p
JOIN Production.ProductSubcategory s
ON p.ProductSubcategoryID = s.ProductSubcategoryID
WHERE s.name LIKE @product AND p.ListPrice < @maxprice);
-- Populate the output variable @compareprice.
SET @compareprice = @maxprice;
--另一个存储过程调用的时候:
Create Proc Test
DECLARE @compareprice money, @cost money
EXECUTE Production.usp_GetList '%Bikes%', 700,
@compareprice OUT,
@cost OUTPUT
IF @cost <= @compareprice
PRINT 'These products can be purchased for less than
$'+RTRIM(CAST(@compareprice AS varchar(20)))+'.'
PRINT 'The prices for all products in this category exceed
$'+ RTRIM(CAST(@compareprice AS varchar(20)))+'.'
2）第二种方法:创建一个临时表
create proc GetUserNameasbeginselect 'UserName'end
Create table #tempTable (userName nvarchar(50))insert into #tempTable(userName)exec GetUserName
select #tempTable
--用完之后要把临时表清空drop table #tempTable--需要注意的是，这种方法不能嵌套。例如：
procedure a begin ... insert #table exec b end procedure b begin ... insert #table exec c select * from #table end procedure c begin ... select * from sometable end
--这里ａ调ｂ的结果集，而ｂ中也有这样的应用ｂ调了ｃ的结果集，这是不允许的，--会报“INSERT EXEC 语句不能嵌套”错误。在实际应用中要避免这类应用的发生。
3）第三种方法:声明一个变量,用exec(@sql)执行:
2.3删除存储过程、
drop procedure sp_name//
注意：不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程
2.4查看存储过程信息
1.show procedure status显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等
2.show create procedure sp_name显示某一个mysql存储过程的详细信息
mysql存储过程可使用两种风格的注释双横杠：--
该风格一般用于单行注释c风格：/* 注释内容 */ 一般用于多行注释
2.6循环语句
BEGIN执行语句END
BEGIN执行语句END
-- 打印PRINT '正确'ENDELSE BEGINPRINT '错误'END
2.6.2 多条件选择语句
Sql 里的多条件选择语句.
DECLARE @iRet INT, @PKDisp VARCHAR(20)SET @iRet = 1Select @iRet = CASEWHEN @PKDisp = '一' THEN 1WHEN @PKDisp = '二' THEN 2WHEN @PKDisp = '三' THEN 3WHEN @PKDisp = '四' THEN 4WHEN @PKDisp = '五' THEN 5ELSE 100END
2.6.3 循环语句
WHILE 条件 BEGIN 执行语句END
Eg: CREATE PROCEDURE [dbo].[P_TestWhilr]
DECLARE @i INT
SET @i = 1
WHILE @i<1000000 BEGIN
set @i=@i+1
exec [dbo].[P_TestWhilr]
3.参数变量
3.1变量及定义
自定义变量：DECLARE a INT ; SET a=100; 可用以下语句代替：DECLARE a INT DEFAULT 100;
变量分为用户变量和系统变量，系统变量又分为会话和全局级变量
用户变量：用户变量名一般以@开头，滥用用户变量会导致程序难以理解及管理
-- 变量的声明,sql里面声明变量时必须在变量前加@符号DECLARE @I INT-- 变量的赋值，变量赋值时变量前必须加setSET @I = 30-- 声明多个变量DECLARE @s varchar(10),@a INT
3.2.1算术运算符
+ 加 SET var1=2+2; 4- 减 SET var2=3-2; 1* 乘 SET var3=3*2; 6/ 除 SET var4=10/3; 3.3333DIV 整除 SET var5=10 DIV 3; 3% 取模 SET var6=10%3 ; 1
3.2.2比较运算符
> 大于 1>2 False< 小于 2<1 False<= 小于等于 2<=2 True>= 大于等于 3>=2 TrueBETWEEN 在两值之间 5 BETWEEN 1 AND 10 TrueNOT BETWEEN 不在两值之间 5 NOT BETWEEN 1 AND 10 FalseIN 在集合中 5 IN (1,2,3,4) FalseNOT IN 不在集合中 5 NOT IN (1,2,3,4) True= 等于 2=3 False, != 不等于 23 False 严格比较两个NULL值是否相等 NULLNULL TrueLIKE 简单模式匹配 "Guy Harrison" LIKE "Guy%" TrueREGEXP 正则式匹配 "Guy Harrison" REGEXP "[Gg]reg" FalseIS NULL 为空 0 IS NULL FalseIS NOT NULL 不为空 0 IS NOT NULL True
3.逻辑运算符
4.位运算符| 或& 与<< 左移位>> 右移位~ 非(单目运算，按位取反)
4.输入输出 （不详）
mysql存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUTCreate procedure|function([[IN |OUT |INOUT ] 参数名 数据类形...])
IN 输入参数表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT 输出参数该值可在存储过程内部被改变，并可返回
INOUT 输入输出参数调用时指定，并且可被改变和返回
--创建存储过程求最大值
CREATE PROCEDURE [dbo].[P_Max]
@a int, -- 输入
@b int, -- 输入
@Returnc int output --输出
if (@a>@b)
set @Returnc =@a
set @Returnc =@b
declare @Returnc int
exec P_Max 2,3,@Returnc output
select @Returnc
mysql存储过程基本函数包括：字符串类型，数值类型，日期类型
5.1字符串类
CHARSET(str) //返回字串字符集CONCAT (string2 [,… ]) //连接字串INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0LCASE (string2 ) //转换成小写LEFT (string2 ,length ) //从string2中的左边起取length个字符LENGTH (string ) //string长度LOAD_FILE (file_name ) //从文件读取内容LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为lengthLTRIM (string2 ) //去除前端空格REPEAT (string2 ,count ) //重复count次REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_strRPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为lengthRTRIM (string2 ) //去除后端空格STRCMP (string1 ,string2 ) //逐字符比较两字串大小,SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1mysql> select substring(’abcd’,0,2);+———————–+| substring(’abcd’,0,2) |+———————–+| |+———————–+1 row in set (0.00 sec)
mysql> select substring(’abcd’,1,2);+———————–+| substring(’abcd’,1,2) |+———————–+| ab |+———————–+1 row in set (0.02 sec)
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符UCASE (string2 ) //转换成大写RIGHT(string2,length) //取string2最后length个字符SPACE(count) //生成count个空格
5.2 数值类型
ABS (number2 ) //绝对值BIN (decimal_number ) //十进制转二进制CEILING (number2 ) //向上取整CONV(number2,from_base,to_base) //进制转换FLOOR (number2 ) //向下取整FORMAT (number,decimal_places ) //保留小数位数HEX (DecimalNumber ) //转十六进制注：HEX()中可传入字符串，则返回其ASC-11码，如HEX(’DEF’)返回4142143也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19LEAST (number , number2 [,..]) //求最小值MOD (numerator ,denominator ) //求余POWER (number ,power ) //求指数RAND([seed]) //随机数ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]
注：返回类型并非均为整数，如：
(1)默认变为整形值mysql> select round(1.23);+————-+| round(1.23) |+————-+| 1 |+————-+1 row in set (0.00 sec)
mysql> select round(1.56);+————-+| round(1.56) |+————-+| 2 |+————-+1 row in set (0.00 sec)
(2)可以设定小数位数，返回浮点型数据
mysql> select round(1.567,2);+—————-+| round(1.567,2) |+—————-+| 1.57 |+—————-+1 row in set (0.00 sec)
SIGN (number2 ) //返回符号,正负或0SQRT(number2) //开平方
5.3日期类型
ADDTIME (date2 ,time_interval ) //将time_interval加到date2CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区CURRENT_DATE ( ) //当前日期CURRENT_TIME ( ) //当前时间CURRENT_TIMESTAMP ( ) //当前时间戳DATE (datetime ) //返回datetime的日期部分DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetimeDATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间DATEDIFF (date1 ,date2 ) //两个日期差DAY (date ) //返回日期的天DAYNAME (date ) //英文星期DAYOFWEEK (date ) //星期(1-7) ,1为星期天DAYOFYEAR (date ) //一年中的第几天EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串MAKETIME (hour ,minute ,second ) //生成时间串MONTHNAME (date ) //英文月份名NOW ( ) //当前时间SEC_TO_TIME (seconds ) //秒数转成时间STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示TIMEDIFF (datetime1 ,datetime2 ) //两个时间差TIME_TO_SEC (time ) //时间转秒数]WEEK (date_time [,start_of_week ]) //第几周YEAR (datetime ) //年份DAYOFMONTH(datetime) //月的第几天HOUR(datetime) //小时LAST_DAY(date) //date的月的最后日期MICROSECOND(datetime) //微秒MONTH(datetime) //月MINUTE(datetime) //分
注：可用在INTERVAL中的类型：DAY ,DAY_HOUR ,DAY_MINUTE ,DAY_SECOND ,HOUR ,HOUR_MINUTE ,HOUR_SECOND ,MINUTE ,MINUTE_SECOND,MONTH ,SECOND ,YEARDECLARE variable_name [,variable_name...] datatype [DEFAULT value]; 其中，datatype为mysql的数据类型，如:INT, FLOAT, DATE, VARCHAR(length)
DECLARE l_int INT unsigned default 4000000; DECLARE l_numeric NUMERIC(8,2) DEFAULT 9.95; DECLARE l_date DATE DEFAULT ''; DECLARE l_datetime DATETIME DEFAULT ' 23:59:59';DECLARE l_varchar VARCHAR(255) DEFAULT 'This will not be padded';
6.存储过程的应用
SQL存储过程进行分页的方法: 存储过程: CREATE procedure p_splitpage @sql nvarchar(4000), --要执行的sql语句 @page int=1, --要显示的页码 @pageSize int, --每页的大小 @pageCount int=0 out, --总页数 @recordCount int=0 out --总记录数 as set nocount on declare @p1 int exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output set @recordCount = @pageCount select @pagecount=ceiling(1.0*@pagecount/@pagesize) ,@page=(@page-1)*@pagesize+1 exec sp_cursorfetch @p1,16,@page,@pagesize exec sp_cursorclose @p1 GO ASP页面的内容 sql = "Select id, c_s_name from tabNews where deleted1 Order By id Desc" page = cint(page_get) if page_post""then page = cint(page_post) end if if not page > 0 then page = 1 end if pagesize=20’每页的条数 set cmd = server.CreateObject("mand") cmd.ActiveConnection = conn <mandType = 4 <mandText = "p_SplitPage" cmd.Parameters.Append cmd.CreateParameter("@sql",8,1, 4000, sql) cmd.Parameters.Append cmd.CreateParameter("@page",4,1, 4, page) cmd.Parameters.Append cmd.CreateParameter("@pageSize",4,1, 4, pageSize) cmd.Parameters.Append cmd.CreateParameter("@pageCount",4,2, 4, pageCount) cmd.Parameters.Append cmd.CreateParameter("@recordCount",4,2, 4, recordCount) set rs = cmd.Execute set rs = rs.NextRecordSet pageCount = cmd.Parameters("@pageCount").value recordCount = cmd.Parameters("@recordCount").value if pageCount = 0 then pageCount = 1 if page>pageCount then response.Redirect("?page="&pageCount) end if set rs = cmd.Execute
游标(Cursor)它使户可逐行访问由SQL Server返回的结果集。
使用游标(cursor)的一个主要的原因就是把集合操作转换成单个记录处理方式。用SQL语言从数据库中检索数据后，结果放在内存的一块区域中，且结果往往是一个含有多个记录的集合。游标机制允许用户在SQL server内逐行地访问这些记录，按照用户自己的意愿来显示和处理这些记录。
2. 游标的优点
从游标定义可以得到游标的如下优点，这些优点使游标在实际应用中发挥了重要作用：1）允许程序对由查询语句select返回的行集合中的每一行执行相同或不同的操作，而不是对整个行集合执行同一个操作。2）提供对基于游标位置的表中的行进行删除和更新的能力。3）游标实际上作为面向集合的数据库管理系统（RDBMS）和面向行的程序设计之间的桥梁，使这两种处理方式通过游标沟通起来。
3．游标的使用
讲了这个多游标的优点，现在我们就亲自来揭开游标的神秘的面纱。
使用游标的顺序： 声名游标、打开游标、读取数据、关闭游标、删除游标
CREATE PROCEDURE PK_Test
--声明个变量
declare @O_ID nvarchar(20)
declare @A_Salary float
--声明一个游标mycursor，
declare mycursor cursor for select O_ID,A_Salary from AddSalary
--打开游标
open mycursor
--从游标里取出数据(select语句中参数的个数必须要和从游标取出的变量名相同)赋值到我们刚才声明的个变量中
fetch next from mycursor into @O_ID,@A_Salary
/*//判断游标的状态
//0 fetch语句成功
//-1 fetch语句失败或此行不在结果集中
//-2被提取的行不存在*/
--循环 一次次读取 游标，得到@O_ID,@A_Salary的值
while (@@fetch_status=0)
--开始读执
--将@O_ID,@A_Salary 当做一个已知量，随便使用。
--显示出我们每次用游标取出的值
print '游标成功取出一条数据'
print @O_ID
print @A_Salary
--用游标去取下一条记录
fetch next from mycursor into @O_ID,@A_Salary
--关闭游标
close mycursor
--撤销游标
deallocate mycursor
Top 的使用
select Top 5 lngWorkID,strWorkName,strExecHumanName,strBeginDate
from worklist where lngExecHumanID= @lngUserID
取出 使用sql语句得到的记录的 前5行
阅读(14763) | 评论(3) | 转发(4) |
相关热门文章
给主人留下些什么吧！~~
存储过程兼容性不好，切换数据库大多需要针对存储过程做改动
泥亚鳅: 存储过程就是经编译后存储在数据库吧~.....亦是SQL语句，无需编译
存储过程就是经编译后存储在数据库吧~
请登录后评论。SqlServer（10）
一.创建一个存储过程
里面有个输出参数output
create proc multi
@num1 int,
@num2 int=10 output
--output的功能类似于C#中的out修饰参数
set @num2=@num2*@num1
print @num2
二.执行存储过程
exec multi 5,5这时输出：25
忽略了输出参数的默认&#20540;10
exec multi 5这时输出：50
输出参数的默认&#20540;10起作用
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：16827次
排名：千里之外
原创：80篇
转载：26篇
(1)(2)(2)(21)(8)(4)(14)(8)(42)在SQL Server中使用SQL语句查询一个存储过程被其它所有的存储过程引用的存储过程名_mssql,sql server_ThinkSAAS
在SQL Server中使用SQL语句查询一个存储过程被其它所有的存储过程引用的存储过程名
在SQL Server中使用SQL语句查询一个存储过程被其它所有的存储过程引用的存储过程名
内容来源： 网络
PHP开发框架
开发工具/编程工具
服务器环境
ThinkSAAS商业授权：
ThinkSAAS为用户提供有偿个性定制开发服务
ThinkSAAS将为商业授权用户提供二次开发指导和技术支持
让ThinkSAAS更好，把建议拿来。
开发客服微信