PL SQL 存储过程 SQL SERVER创建存储过程及调用,Oracle创建存储过程及调用
Oracle使用存储过程实例:
例1: //查出表字段赋值给存储过程变量 create proc proc_stu @sname varchar(20), //默认是输入参数(input),另外还有两种类型 (output)(input output) @pwd varchar(20) as select * from ren where sname=@sname and pwd=@pwd go 查看结果:proc_stu 'admin','admin'
例2: //实现用户验证 下面的存储过程实现用户验证的功能,如果不成功,返回0,成功则返回1. CREATE PROCEDURE VALIDATE @USERNAME CHAR(20),@PASSWORD CHAR(20),@LEGAL BIT OUTPUT AS IF EXISTS(SELECT * FROM REN WHERE SNAME = @USERNAME AND PWD = @PASSWORD) SELECT @LEGAL = 1 ELSE SELECT @LEGAL = 0 在程序中调用该存储过程,并根据@LEGAL参数的值判断用户是否合法。
例3://一个高效的数据分页的存储过程 可以轻松应付百万数据 CREATE PROCEDURE pageTest --用于翻页的测试 --需要把排序字段放在第一列 ( @FirstID nvarchar(20)=null, --当前页面里的第一条记录的排序字段的值 @LastID nvarchar(20)=null, --当前页面里的最后一条记录的排序字段的值 @isNext bit=null, --true 1 :下一页;false 0:上一页 @allCount int output, --返回总记录数 @pageSize int output, --返回一页的记录数 @CurPage int --页号(第几页)0:第一页;-1最后一页。 ) AS if @CurPage=0--表示第一页 begin --统计总记录数 select @allCount=count(ProductId) from Product_test set @pageSize=10 --返回第一页的数据 select top 10 ProductId, ProductName, Introduction from Product_test order by ProductId end else if @CurPage=-1--表示最后一页 select * from (select top 10 ProductId, ProductName, Introduction from Product_test order by ProductId desc ) as aa order by ProductId else begin if @isNext=1 --翻到下一页 select top 10 ProductId, ProductName, Introduction from Product_test where ProductId > @LastID order by ProductId else --翻到上一页 select * from (select top 10 ProductId, ProductName, Introduction from Product_test where ProductId < @FirstID order by ProductId desc) as bb order by ProductId end
SQL SERVER 存储过程
自定义存储过程即用户使用T_SQL语句编写的、为了实现某一特定业务需求,在用户数据库中编写的T_SQL语句集合,自定义存储过程可以接受输入参数、向客户端返回结果和信息,返回输出参数等。创建自定义存储过程时,存储过程名前加上"##"表示创建了一个全局的临时存储过程;存储过程前面加上"#"时,表示创建的局部临时存储过程。局部临时存储过程只能在创建它的回话中使用,会话结束时,将被删除。这两种存储过程都存储在tempdb数据库中。
use sample_db; --创建测试books表 create table books ( book_id int identity(1,1) primary key, book_name varchar(20), book_price float, book_auth varchar(10) ); --插入测试数据 insert into books (book_name,book_price,book_auth) values ('论语',25.6,'孔子'), ('天龙八部',25.6,'金庸'), ('雪山飞狐',32.7,'金庸'), ('平凡的世界',35.8,'路遥'), ('史记',54.8,'司马迁');
1.创建无参存储过程
--1.创建无参存储过程 if (exists (select * from sys.objects where name = 'getAllBooks')) drop proc proc_get_student go create procedure getAllBooks as select * from books; --调用,执行存储过程 exec getAllBooks;
2.修改存储过程
alter procedure dbo.getAllBooks as select book_auth from books;
3.删除存储过程
drop procedure getAllBooks;
4.重命名存储过程
sp_rename getAllBooks,proc_get_allBooks;
(1)带一个参数存储过程
if (exists (select * from sys.objects where name = 'searchBooks')) drop proc searchBooks go create proc searchBooks(@bookID int) as --要求book_id列与输入参数相等 select * from books where book_id=@bookID; --执行searchBooks exec searchBooks 1;
(2)带2个参数存储过程
if (exists (select * from sys.objects where name = 'searchBooks1')) drop proc searchBooks1 go create proc searchBooks1( @bookID int, @bookAuth varchar(20) ) as --要求book_id和book_Auth列与输入参数相等 select * from books where book_id=@bookID and book_auth=@bookAuth; exec searchBooks1 1,'金庸';
(3)创建有返回值的存储过程
if (exists (select * from sys.objects where name = 'getBookId')) drop proc getBookId go create proc getBookId( @bookAuth varchar(20),--输入参数,无默认值 @bookId int output --输入/输出参数 无默认值 ) as select @bookId=book_id from books where book_auth=@bookAuth --执行getBookId这个带返回值的存储过程 declare @id int --声明一个变量用来接收执行存储过程后的返回值 exec getBookId '孔子',@id output select @id as bookId;--as是给返回的列值起一个名字
(4)创建带通配符的存储过程
if (exists (select * from sys.objects where name = 'charBooks')) drop proc charBooks go create proc charBooks( @bookAuth varchar(20)='金%', @bookName varchar(20)='%' ) as select * from books where book_auth like @bookAuth and book_name like @bookName; --执行存储过程charBooks exec charBooks '孔%','论%';
(5)加密存储过程
with encryption子句对用户隐藏存储过程的文本.下例创建加密过程,使用 sp_helptext 系统存储过程获取关于加密过程的信息,然后尝试直接从 syscomments 表中获取关于该过程的信息.
if (object_id('books_encryption', 'P') is not null) drop proc books_encryption go create proc books_encryption with encryption as select * from books; --执行此过程books_encryption exec books_encryption; exec sp_helptext 'books_encryption';--控制台会显示"对象 'books_encryption' 的文本已加密。"
(6).不缓存存储过程
--with recompile不缓存 if (object_id('book_temp', 'P') is not null) drop proc book_temp go create proc book_temp with recompile as select * from books; go exec book_temp; exec sp_helptext 'book_temp';
(7).创建带游标参数的存储过程
if (object_id('book_cursor', 'P') is not null) drop proc book_cursor go create proc book_cursor @bookCursor cursor varying output as set @bookCursor=cursor forward_only static for select book_id,book_name,book_auth from books open @bookCursor; go --调用book_cursor存储过程 declare @cur cursor, @bookID int, @bookName varchar(20), @bookAuth varchar(20); exec book_cursor @bookCursor=@cur output; fetch next from @cur into @bookID,@bookName,@bookAuth; while(@@FETCH_STATUS=0) begin fetch next from @cur into @bookID,@bookName,@bookAuth; print 'bookID:'+convert(varchar,@bookID)+' , bookName: '+ @bookName +' ,bookAuth: '+@bookAuth; end close @cur --关闭游标 DEALLOCATE @cur; --释放游标
1.创建无参存储过程
--1.创建无参存储过程 if (exists (select * from sys.objects where name = 'getAllBooks')) drop proc proc_get_student go create procedure getAllBooks as select * from books; --调用,执行存储过程 exec getAllBooks;
2.修改存储过程
alter procedure dbo.getAllBooks as select book_auth from books;
3.删除存储过程
drop procedure getAllBooks;
4.重命名存储过程
sp_rename getAllBooks,proc_get_allBooks;
提着昨日种种千辛万苦,向明天换一些美满和幸福。