sql 学习笔记之:存储过程和用户自定义函数

sql 学习笔记之:存储过程和用户自定义函数
2009-05-21 13:28

一:存储过程的简单创建,修改与删除
1.创建简单的存储过程

use AdventureWorks
go
create proc spEmployee
as
select * from HumanResources.Employee
执行上面的代码就创建了一个存储过程
如果想运行这个存储过程
可以直接执行exec spEmployee这个语句
2.更改存储过程
ALTER proc [dbo].[spEmployee]
as
select top 13 * from HumanResources.Employee
3.删除存储过程
drop proc dbo.spEmployee

二:存储过程的输入参数和输出参数
1.有输入参数的存储过程
use AdventureWorks
go
create proc spEmployee
    
@LastName nvarchar(50) = null
as
if @LastName is null
    
select top 13 * from HumanResources.Employee
else
    
select top 10 * from HumanResources.Employee

查看该存储过程的结果可以用
exec spEmployee '123'
或直接exec spEmployee
存储过程的重载...
2.有输出参数的存储过程
use AdventureWorks
go
alter proc spEmployee
    
@LastName nvarchar(50) = null output
as
if @LastName is null
    
begin
        
print 'null'
        
return '123'
    
end
else
    
begin
        
print @LastName
        
return '456'
    
end
看第一个测试该存储过程的语句
declare @myval nvarchar(50)
exec @myval = spEmployee @myval output
print @myval
输出null 123
第二个测试该存储过程的语句
declare @myval nvarchar(50)
set @myval = 'xland'
exec @myval = spEmployee @myval output
print @myval
输出xland 456


三:用户定义函数
1.返回标量值的用户定义函数
先做一个简单的日期处理函数
把长日期缩短成短日期
Create function dbo.DayOnly(@date datetime)
returns varchar(12)
as
begin
    
return convert(varchar(12),@date,101)
end
为了测试上面的函数先做个脚本
use Accounting
declare @counter int
set @counter = 1
while @counter <= 10
begin
    
insert into Orders values(1,dateadd(mi,@counter,getdate()),1)
    
set @counter = @counter +1
end
然后检索这个脚本 新插入的数据记录
use Accounting
select * from orders where dbo.DayOnly(date1) = dbo.DayOnly(getdate())
2.返回表的用户定义函数
先看例子
use AdventureWorks
go
create function dbo.fnContactSearch(@LastName nvarchar(50))
returns table
as
return (select * from Person.Contact where LastName like @LastName+'%')
执行这个例子
use AdventureWorks
select * from fnContactSearch('Ad')
3.综合例子:返回表,有输入参数
use xland
go
create function dbo.funGetMytable
     (
@id as int)
    
returns @allrows table
     (
         id  
int not null,
         title  
nvarchar(max) null
     )
as
begin
    
insert into @allrows  select id,title from mytable where id = @id
return
end
go
执行这个例子
select * from funGetMytable(1)
posted @ 2009-05-22 11:33  夜色狼  阅读(360)  评论(0编辑  收藏  举报