PLSQL存储过程SQLSERVER创建存储过程及调用，Oracle创建存储过程及调用

Oracle使用存储过程实例：例1：查出表字段赋值给存储过程变量createprocproc_stu@snamevarchar(20),默认是输入参数（input），另外还有两种类

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 OUTPUTAS 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_studentgocreate procedure getAllBooksasselect * from books;--调用,执行存储过程exec getAllBooks;回到顶部

2.修改存储过程

alter procedure dbo.getAllBooks asselect 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 searchBooksgocreate proc searchBooks(@bookID int)as --要求book_id列与输入参数相等 select * from books where book_id=@bookID;--执行searchBooksexec searchBooks 1;

(2)带2个参数存储过程

if (exists (select * from sys.objects where name = 'searchBooks1')) drop proc searchBooks1gocreate 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 getBookIdgocreate 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 outputselect @id as bookId;--as是给返回的列值起一个名字

(4)创建带通配符的存储过程

if (exists (select * from sys.objects where name = 'charBooks')) drop proc charBooksgocreate proc charBooks( @bookAuth varchar(20)='金%', @bookName varchar(20)='%')as select * from books where book_auth like @bookAuth and book_name like @bookName;--执行存储过程charBooksexec charBooks '孔%','论%';

(5)加密存储过程

　　with encryption子句对用户隐藏存储过程的文本.下例创建加密过程,使用 sp_helptext 系统存储过程获取关于加密过程的信息,然后尝试直接从 syscomments 表中获取关于该过程的信息.

if (object_id('books_encryption', 'P') is not null) drop proc books_encryptiongocreate proc books_encryption with encryptionas select * from books;--执行此过程books_encryptionexec books_encryption;exec sp_helptext 'books_encryption';--控制台会显示"对象 'books_encryption' 的文本已加密。"

(6).不缓存存储过程

--with recompile不缓存if (object_id('book_temp', 'P') is not null) drop proc book_tempgocreate proc book_tempwith recompileas select * from books;goexec book_temp;exec sp_helptext 'book_temp';

(7).创建带游标参数的存储过程

if (object_id('book_cursor', 'P') is not null) drop proc book_cursorgocreate proc book_cursor @bookCursor cursor varying outputas 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;endclose @cur --关闭游标DEALLOCATE @cur; --释放游标回到顶部

1.创建无参存储过程

--1.创建无参存储过程if (exists (select * from sys.objects where name = 'getAllBooks')) drop proc proc_get_studentgocreate procedure getAllBooksasselect * from books;--调用,执行存储过程exec getAllBooks;回到顶部

2.修改存储过程

alter procedure dbo.getAllBooks asselect book_auth from books;回到顶部

3.删除存储过程

drop procedure getAllBooks;回到顶部

4.重命名存储过程

sp_rename getAllBooks,proc_get_allBooks;