正文
SQL基础系列(3)-变量、函数、存储过程等
小程序:扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序
【扫一扫了解最新限行尾号】
复制小程序
1. 变量
定义变量 DECLARE @a INT 赋值 SET @a=1 PRINT @a DECLARE @name NVARCHAR(30) --select 赋值 SELECT @name='zcx' PRINT @name SELECT @name=FirstName FROM dbo.Persons PRINT @name DECLARE @name NVARCHAR(50) --update 赋值 UPDATE Persons SET @name = FirstName WHERE ID = 1 PRINT @name
2. 临时表
--创建临时表1
create table #persion ( [id] [int] NOT NULL, [name] [nvarchar](5) NOT NULL, [pwd] [nvarchar](20) NULL ) insert into #persion values(1,'zcx','') SELECT * INTO #psersion1 FROM #persion select * from #persion UNION ALL select * from #psersion1 DROP TABLE #persion DROP TABLE #psersion1
插入select的数据
insert into #persion select id,FirstName,LastName from dbo.Persons
注意和select into的区别
Select into 会创建新的临时表
修改临时表结构
alter table #persion add [tmpid] int NOT NULL IDENTITY(10,1)
alter table #persion add [tmpid1] uniqueidentifier NOT NULL default(newid())
给查询结果集增加自增长列
要求主表Persons没得主键
select IDENTITY(int,1,1)as ID, * into #temp from Persons
SELECT * FROM #temp
和count性质类似
select SUM(1) from Persons
有主键的表添加列
select (select SUM(1) from Persons where id<= a.id) as myID,* from Persons a order by myID
创建表变量:下面代码要同时执行
declare @mytable table ( id int not null, name nvarchar(50) null ) INSERT INTO @mytable ( id, name ) VALUES ( 0, -- id - int N'zcx' -- name - nvarchar(50) ) INSERT INTO @mytable ( id, name ) VALUES ( 0, -- id - int N'zjj' -- name - nvarchar(50) ) select * from @mytable
3. 循环
DECLARE @index INT
DECLARE @resukt INT
SET @index = 1
SET @resukt = 0
WHILE @index <= 100
BEGIN
SET @resukt += @index
SET @index += 1
END
PRINT @resukt
4. 条件
IF ( 3 > 5 )
BEGIN
PRINT 'true'
END
ELSE
BEGIN
PRINT 'false'
END
When then
DECLARE @num INT
DECLARE @name NVARCHAR(30)
SET @num = 3
SET @name = CASE WHEN @num = 1 THEN 'elephant'
WHEN @num = 2 THEN 'hippo'
WHEN @num = 3 THEN 'trigger'
ELSE 'false'
END
PRINT @name
5. 游标
DECLARE @index INT DECLARE @resukt INT SET @index = 1 SET @resukt = 0 WHILE @index <= 100 BEGIN SET @resukt += @index SET @index += 1 END PRINT @resukt
IF ( 3 > 5 ) BEGIN PRINT 'true' END ELSE BEGIN PRINT 'false' END When then DECLARE @num INT DECLARE @name NVARCHAR(30) SET @num = 3 SET @name = CASE WHEN @num = 1 THEN 'elephant' WHEN @num = 2 THEN 'hippo' WHEN @num = 3 THEN 'trigger' ELSE 'false' END PRINT @name
5. 游标
原则上尽量不要使用游标
(更多信息:http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html)
DECLARE @name NVARCHAR(30)
DECLARE mycursor CURSOR FOR SELECT FirstName FROM dbo.Persons
--打开游标
OPEN mycursor WHILE @@FETCH_STATUS=0 BEGIN FETCH NEXT FROM mycursor INTO @name PRINT @name END CLOSE mycursor --回收 DEALLOCATE mycursor
6. 触发器
触发器中的临时表:
Inserted
存放进行insert和update 操作后的数据
Deleted
存放进行delete 和update操作前的数据
Create trigger persion_update On dbo.Persons for Update As declare @msg nvarchar(50) --@msg记录修改情况 select @msg = N'姓名从“' + Deleted.FirstName + N'”修改为“' + Inserted.FirstName + '”' from Inserted,Deleted --插入日志表 insert into TAB(Col2)values(@msg)
7. 存储过程
DROP PROCEDURE compute_add
CREATE PROCEDURE compute_add
@p1 INT ,
@p2 INT ,
@p3 INT OUTPUT
AS
BEGIN
SET @p3=@p1+@p2
END
DECLARE @result INT
EXEC compute_add 1,2,@result OUTPUT
PRINT @result
8. 函数
DROP PROCEDURE compute_add CREATE PROCEDURE compute_add @p1 INT , @p2 INT , @p3 INT OUTPUT AS BEGIN SET @p3=@p1+@p2 END DECLARE @result INT EXEC compute_add 1,2,@result OUTPUT PRINT @result
标量函数
CREATE FUNCTION COMPUTE_ADD ( @p1 INT, @p2 INT ) RETURNS INT AS BEGIN RETURN @p1+@p2 END
调用方式
DECLARE @result INT
SET @result=dbo.COMPUTE_ADD(1,2)
PRINT @result
表值函数
CREATE FUNCTION mytable ( @id INT ) RETURNS TABLE AS RETURN ( SELECT * FROM dbo.Persons WHERE id < @id )
调用方式
select * from dbo.mytable(3)
表值函数:返回指定的列
CREATE FUNCTION mytable ( @id INT ) RETURNS @table TABLE ( firstname NVARCHAR(50) , lastname NVARCHAR(50) ) AS BEGIN INSERT INTO @table SELECT firstname , lastname FROM dbo.Persons WHERE id < @id RETURN END
调用方式
select * from dbo.mytable(3)
函数和存储过程区别
函数 |
存储过程 |
可以返回表变量 |
不能返回表变量 |
不能使用output参数 |
可以使用output参数 |
不能用临时表 |
可以执行对数据库表的操作,可以返回数据集 |
函数内部的操作不能影响到外部环境 |
|
不能通过select返回结果集 |
|
不能update,delete,数据库表(在函数内对带副作用的运算符 'UPDATE' 的使用无效。) |
|
必须return 一个标量值或表变量或空 |
可以return一个标量值,也可以省略return |