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,'司马迁');
if (exists (select * from sys.objects where name = 'getAllBooks'))
drop proc proc_get_student
go
--创建存储过程
create procedure getAllBooks
as
select * from books;
go
--修改存储过程
alter procedure dbo.getAllBooks
as
select book_auth from books;
--删除存储过程
drop procedure getAllBooks;
--重命名存储过程
sp_rename getAllBooks,proc_get_allBooks;
--调用,执行存储过程
exec getAllBooks;
--创建带参数的存储过程1
create proc searchBooks(@bookID int)
as
select * from books where book_id=@bookID;
exec searchBooks 1;
--带2个参数存储过程
create proc searchtwoBooks(
@bookID int,
@bookAuth varchar(20),
@booName varchar(20)
)
as
select * from books where book_id=@bookID and book_auth = @bookAuth;
exec searchtwoBooks 2,'金庸';
--带输出参数的存储过程
if (exists (select * from sys.objects where name = 'getBookId'))
drop proc getBookId
go
create proc getBookId(
@bookAuth varchar(20),
@bookName varchar(20) output
)
as
select @bookName=book_name from books where book_auth=@bookAuth
declare @bookname varchar(20)
exec getBookId '孔子',@bookName output
select @bookName as bookname;
if (exists (select * from sys.objects where name = 'orderlist'))
drop proc orderlist
go
create proc orderlist(
@orderno varchar(20),
@product varchar(20)
)
as
insert into t_product (orderno,product) VALUES(@orderno,@product)
-- select @bookName=book_name from books where book_auth=@bookAuth
declare @orderno varchar(20),
@product varchar(20)
set @orderno='ANS'+CONVERT(CHAR(10),GETDATE(),112)+'000002'
set @product='电视机'
exec orderlist @orderno,@product
---------------------------有用---------------------------------------
CREATE TABLE [dbo].[SriaNum] (
[Num] [int] NOT NULL
)
CREATE PROC dpIDS_GetSerialNumber
@SerialNumber VARCHAR(9) OUTPUT -- 指明为输出参数
AS
IF NOT EXISTS(SELECT
*
FROM
SriaNum)
BEGIN
INSERT INTO SriaNum values(1)
END
ELSE
BEGIN
UPDATE SriaNum SET Num=Num+1
END
SELECT
@SerialNumber = REPLICATE('0',9-LEN(Num))+CONVERT(VARCHAR(9),Num) --生成[000000001, 999999999]范围内的流水号
FROM
SriaNum
DECLARE
@TEST VARCHAR(9)
EXECUTE [dbo].dpIDS_GetSerialNumber @TEST OUTPUT -- 指明为输出变量
SELECT @TEST AS SERIALNUMBER -- 获得流水号
-----------------------------------------解决并发问题-------------------------------------------
CREATE TABLE [dbo].[SriaNum] (
[Num] [int] NOT NULL
)
-- WAITFOR DELAY '0:0:1'
CREATE PROC dpIDS_GetSerialNumber
@SerialNumber VARCHAR(9) OUTPUT -- 指明为输出参数
AS
IF NOT EXISTS(SELECT
*
FROM
SriaNum)
BEGIN
INSERT INTO SriaNum values(1)
END
ELSE
BEGIN TRANSACTION
BEGIN
UPDATE SriaNum SET Num=Num+1
END
WAITFOR DELAY '0:0:3'
COMMIT
SELECT
@SerialNumber = 'ANS'+REPLICATE('0',6-LEN(Num))+CONVERT(VARCHAR(9),Num)
FROM
SriaNum
DECLARE
@TEST VARCHAR(9)
EXECUTE [dbo].dpIDS_GetSerialNumber @TEST OUTPUT -- 指明为输出变量
SELECT @TEST AS SERIALNUMBER -- 获得流水号
网友评论