存储过程

charming 4月前 ⋅ 87 阅读

定义

存储过程:完成指定操作的一组sql,只需要一次编译

create proc book_proc 

@name VARCHAR(30),

@ncount int OUTPUT

as 

begin 

set @ncount=(SELECT count(1) from books_test where book_name=@name and book_price>30)

SELECT book_name,book_price, @ncount ncount from books_test where book_name=@name

end

DECLARE @ncount int 
EXEC book_proc '雪山飞狐',@ncount output
SELECT @ncount

--加密存储过程

alter proc book_encryption_proc 
@name VARCHAR(20)
with encryption
as 
begin 
        SELECT * from books_test where book_name=@name
end

exec sp_helptext 'book_encryption_proc'

![微信截图_20200403115019.png]

--游标的存储过程
alter proc book_cursor 
--游标变量设为输出
@bookCursor cursor varying output
as
--给游标变量赋值
set @bookCursor=cursor for 
select * from books_test
open @bookCursor
--使用存储过程 
DECLARE @bookCursor cursor,
                @id int ,
                @name VARCHAR(20),
                @author VARCHAR(20),
                @price VARCHAR(10)
exec book_cursor @bookCursor output 
--取游标中的值赋值给变量id name author 
fetch next from @bookCursor into @id ,@name,@author,@price
--判断游标指针位置 读取吓一跳数据是否成功 0代表成功
while(@@fetch_status=0)
--begin end 作用 即{}
begin 
    fetch next from @bookCursor into @id ,@name,@author,@price;
    --convert()转换
    PRINT 'bookid:'+CONVERT(VARCHAR,@id)+',bookName:'+@name+',price:'+@price+',bookAuthor:'+@author;
end 
CLOSE @bookCursor
DEALLOCATE @bookCursor;

stu2获取stu1数据

DECLARE stuCursor CURSOR
for select id, name,age from stu2
open stuCursor
DECLARE @name VARCHAR(20),
        @age int,
        @id int 
FETCH NEXT from stuCursor into @id,@name,@age
while @@fetch_status=0
BEGIN
INSERT into stu1  (id,name,age) VALUES(@id,@name,@age)
FETCH NEXT from stuCursor into @id,@name,@age
END
close stuCursor
DEALLOCATE stuCursor

全部评论: 0

    我有话说: