sql server 存储过程简介与使用方法
程序员文章站
2023-12-31 23:28:34
...
语法：
DECLARE
{
{@local_variable data_type}
} [,...n]
例如：
declare @ID int --申明一个名为@ID的变量，类型为int型
三.在SQL Server窗口中打印出变量的值
语法：
PRINT 'any ASCII text' | @local_variable | @@FUNCTION | string_expr
四.变量赋值
例如：
--从数据表中取出第一行数据的ID,赋值给变量@id，然后打印出来
Declare @ID int
Set @ID = (select top(1) categoryID from categories)
Print @ID
在SQL中，我们不能像代码那样直接给变量赋值，例如@id = 1，如果要达到这样的功能，可以这样写：
Declare @ID int
Set @ID = (select 1) -- 类似 @ID=1
Select @id=1 -- 类似 @ID=1
Print @ID
五.变量运算(+,-,*,/,……)
以下必要时候省略变量申明
Set @ID = (select 1+5) --类似 @ID=1+5
Set @ID=(select 1-@ID) --类似 @ID=1-@ID
六.比较操作符
? > (greater than).
? < (less than).
? = (equals).
? <= (less than or equal to).
? >= (greater than or equal to).
? != (not equal to).
? <> (not equal to).
? ! < (not less than).
? !> (not greater than).
没什么说的
七.语句块：Begin … end
将多条语句作为一个块，类似与C++，C#中的{ }
例如：
Begin
Set @ID1 = (select 1)
Set @ID2 = (select 2)
End
八.If， if…else…
语法：
IF Boolean_expression
{sql_statement | statement_block}
[ELSE
{sql_statement | statement_block}]
例如：
If @id is not null
Print ‘@id is not null
if @ID = 1
begin
Set @ID = (select 1 + 1)
end
else
begin
set @ID=(select 1+2)
end
上面的例子用到了比较操作符，语句块，和IF的语法。
九.执行其他存储过程 EXEC
例如
EXEC dbo.[Sales by Year] @Beginning_Date=’1/01/90’, @Ending_Date=’1/01/08’
十.事务
语法：
BEGIN TRAN[SACTION] [transaction_name | @tran_name_variable]
例如
BEGIN TRAN
-- 做某些操作，例如Insert into …
if @@error <> 0
BEGIN
ROLLBACK TRAN
END
else
BEGIN
COMMIT TRAN
END
十一.游标
我们可以在存储过程中用Select语句取出每一行数据进行操作，这就需要用到游标。
语法：
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR select_statement
[FOR UPDATE [OF column_name [,...n]]]
例如：
DECLARE @au_id varchar(11), @au_fname varchar(20) –申明变量
--申明一个游标
DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname FROM authors
--打开游标
OPEN authors_cursor
--取出值
FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname
--循环取出游标的值
WHILE @@FETCH_STATUS = 0
BEGIN
Print @au_id
Print @au_fname
Print ‘ ’
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname
END
CLOSE authors_cursor –关闭游标
DEALLOCATE authors_cursor --释放游标
我觉得上面的是存储过程常用的一些东东，如果要更深入的了解，更详细的帮助，请参考SQL Server的帮助文档
例子：
我自己做了一个,没有问题,你可以看一下
use Northwind
go
create proc test
@StartOrderID int,
@EndOrderID int,
@Code varchar(1000) Out
As
Begin
Declare @tmp int
Set @Code=''
Declare #cur_orders cursor for Select OrderID From Orders
where OrderID>=@startOrderID and OrderID<=@EndOrderID
for read only
Open #cur_Orders
fetch next from #cur_orders into @tmp
while @@fetch_Status=0
Begin
Set @Code=@Code+'-'+convert(varchar(8),@tmp)
fetch next from #cur_orders into @tmp
End
close #cur_Orders
Deallocate #cur_Orders
return
End
go
续2
String ret=null;
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String url
="jdbc:microsoft:sqlserver://192.168.0.102:1433;DatabaseName=Northwind";
String user="sa";
String password="";
Connection conn= DriverManager.getConnection(url,user,password);
CallableStatement stmt=conn.prepareCall("exec test ?,?,?");
stmt.setInt(1,10248);
stmt.setInt(2,10284);
stmt.registerOutParameter(3,Types.VARCHAR);
stmt.setString(3,ret);
stmt.execute();
System.out.println(stmt.getString(3));
stmt.close();
stmt=null;
conn.close();
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
上面的例子没有问题,针对你的情况,我又写了一个,应该可以解决你现在的问题
-- 新建一个表
Create table tmpOrders (
OrderID int,
CustomerID nchar(5)
)
--把Orders 里的OrderID列全部插入,这样Orders与tmpOrders之间就是1:1关系了
insert into tmpOrders
Select distinct orderID,'tmp' from Orders
create proc test
@StartOrderID int,
@EndOrderID int,
@Code varchar(1000) Out
As
Begin
Declare @newOrderID int
Declare @newCustomerID nchar(5)
Declare @DummyInt int
Declare @DummyChar nchar(5)
Set @Code=''
/*
1:1
temp table/formal table is synchronized tmpOrders <---> Orders
fetch from Orders, update tmpOrders
*/
-- for temp table
Declare #cur_tmpOrders Cursor for select OrderID,CustomerID
From tmpOrders
where OrderID>=@startOrderID
and OrderID<=@EndOrderID
for update
--for formal table
Declare #cur_orders cursor for Select OrderID,CustomerID
From Orders
where OrderID>=@startOrderID
and OrderID<=@EndOrderID
for read only
Open #cur_Orders
Open #cur_tmpOrders
fetch next from #cur_tmpOrders into @DummyInt,@dummyChar --Important!!!
fetch next from #cur_orders into @NewOrderID,@NewCustomerID
while @@fetch_Status=0
Begin
--Set @Code=@Code+'-'+convert(varchar(8),@NewOrderID)
--update tempOrders use corresponding Orders' data
Update tmpOrders set customerID=@newCustomerID
where current of #cur_tmpOrders
--pay attention to sequence of cursor fetch action!
fetch next from #cur_tmpOrders into @DummyInt,@dummyChar
if @@fetch_Status<>0 break; -- 没有行了
fetch next from #cur_orders into @newOrderID,@NewCustomerID
End
close #cur_Orders
close #cur_tmpOrders
Deallocate #cur_Orders
Deallocate #cur_tmpOrders
Set @Code='Ok'
return
End
程序如下
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String url=
"jdbc:microsoft:sqlserver://192.168.0.102:1433;DatabaseName=Northwind";
String user="sa";
String password="";
Connection conn= DriverManager.getConnection(url,user,password);
CallableStatement stmt=conn.prepareCall("exec test ?,?,?");
stmt.setInt(1,10248);
stmt.setInt(2,10284);
stmt.registerOutParameter(3,Types.VARCHAR,1000);
stmt.setString(3,ret);
stmt.executeUpdate();
System.out.println(stmt.getString(3));
stmt.close();
stmt=null;
conn.close();
conn=null;
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
DECLARE
{
{@local_variable data_type}
} [,...n]
例如：
declare @ID int --申明一个名为@ID的变量，类型为int型
三.在SQL Server窗口中打印出变量的值
语法：
PRINT 'any ASCII text' | @local_variable | @@FUNCTION | string_expr
四.变量赋值
例如：
--从数据表中取出第一行数据的ID,赋值给变量@id，然后打印出来
Declare @ID int
Set @ID = (select top(1) categoryID from categories)
Print @ID
在SQL中，我们不能像代码那样直接给变量赋值，例如@id = 1，如果要达到这样的功能，可以这样写：
Declare @ID int
Set @ID = (select 1) -- 类似 @ID=1
Select @id=1 -- 类似 @ID=1
Print @ID
五.变量运算(+,-,*,/,……)
以下必要时候省略变量申明
Set @ID = (select 1+5) --类似 @ID=1+5
Set @ID=(select 1-@ID) --类似 @ID=1-@ID
六.比较操作符
? > (greater than).
? < (less than).
? = (equals).
? <= (less than or equal to).
? >= (greater than or equal to).
? != (not equal to).
? <> (not equal to).
? ! < (not less than).
? !> (not greater than).
没什么说的
七.语句块：Begin … end
将多条语句作为一个块，类似与C++，C#中的{ }
例如：
Begin
Set @ID1 = (select 1)
Set @ID2 = (select 2)
End
八.If， if…else…
语法：
IF Boolean_expression
{sql_statement | statement_block}
[ELSE
{sql_statement | statement_block}]
例如：
If @id is not null
Print ‘@id is not null
if @ID = 1
begin
Set @ID = (select 1 + 1)
end
else
begin
set @ID=(select 1+2)
end
上面的例子用到了比较操作符，语句块，和IF的语法。
九.执行其他存储过程 EXEC
例如
EXEC dbo.[Sales by Year] @Beginning_Date=’1/01/90’, @Ending_Date=’1/01/08’
十.事务
语法：
BEGIN TRAN[SACTION] [transaction_name | @tran_name_variable]
例如
BEGIN TRAN
-- 做某些操作，例如Insert into …
if @@error <> 0
BEGIN
ROLLBACK TRAN
END
else
BEGIN
COMMIT TRAN
END
十一.游标
我们可以在存储过程中用Select语句取出每一行数据进行操作，这就需要用到游标。
语法：
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR select_statement
[FOR UPDATE [OF column_name [,...n]]]
例如：
DECLARE @au_id varchar(11), @au_fname varchar(20) –申明变量
--申明一个游标
DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname FROM authors
--打开游标
OPEN authors_cursor
--取出值
FETCH NEXT FROM authors_cursor INTO @au_id, @au_fname
--循环取出游标的值
WHILE @@FETCH_STATUS = 0
BEGIN
Print @au_id
Print @au_fname
Print ‘ ’
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname
END
CLOSE authors_cursor –关闭游标
DEALLOCATE authors_cursor --释放游标
我觉得上面的是存储过程常用的一些东东，如果要更深入的了解，更详细的帮助，请参考SQL Server的帮助文档
例子：
我自己做了一个,没有问题,你可以看一下
use Northwind
go
create proc test
@StartOrderID int,
@EndOrderID int,
@Code varchar(1000) Out
As
Begin
Declare @tmp int
Set @Code=''
Declare #cur_orders cursor for Select OrderID From Orders
where OrderID>=@startOrderID and OrderID<=@EndOrderID
for read only
Open #cur_Orders
fetch next from #cur_orders into @tmp
while @@fetch_Status=0
Begin
Set @Code=@Code+'-'+convert(varchar(8),@tmp)
fetch next from #cur_orders into @tmp
End
close #cur_Orders
Deallocate #cur_Orders
return
End
go
续2
String ret=null;
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String url
="jdbc:microsoft:sqlserver://192.168.0.102:1433;DatabaseName=Northwind";
String user="sa";
String password="";
Connection conn= DriverManager.getConnection(url,user,password);
CallableStatement stmt=conn.prepareCall("exec test ?,?,?");
stmt.setInt(1,10248);
stmt.setInt(2,10284);
stmt.registerOutParameter(3,Types.VARCHAR);
stmt.setString(3,ret);
stmt.execute();
System.out.println(stmt.getString(3));
stmt.close();
stmt=null;
conn.close();
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}
上面的例子没有问题,针对你的情况,我又写了一个,应该可以解决你现在的问题
-- 新建一个表
Create table tmpOrders (
OrderID int,
CustomerID nchar(5)
)
--把Orders 里的OrderID列全部插入,这样Orders与tmpOrders之间就是1:1关系了
insert into tmpOrders
Select distinct orderID,'tmp' from Orders
create proc test
@StartOrderID int,
@EndOrderID int,
@Code varchar(1000) Out
As
Begin
Declare @newOrderID int
Declare @newCustomerID nchar(5)
Declare @DummyInt int
Declare @DummyChar nchar(5)
Set @Code=''
/*
1:1
temp table/formal table is synchronized tmpOrders <---> Orders
fetch from Orders, update tmpOrders
*/
-- for temp table
Declare #cur_tmpOrders Cursor for select OrderID,CustomerID
From tmpOrders
where OrderID>=@startOrderID
and OrderID<=@EndOrderID
for update
--for formal table
Declare #cur_orders cursor for Select OrderID,CustomerID
From Orders
where OrderID>=@startOrderID
and OrderID<=@EndOrderID
for read only
Open #cur_Orders
Open #cur_tmpOrders
fetch next from #cur_tmpOrders into @DummyInt,@dummyChar --Important!!!
fetch next from #cur_orders into @NewOrderID,@NewCustomerID
while @@fetch_Status=0
Begin
--Set @Code=@Code+'-'+convert(varchar(8),@NewOrderID)
--update tempOrders use corresponding Orders' data
Update tmpOrders set customerID=@newCustomerID
where current of #cur_tmpOrders
--pay attention to sequence of cursor fetch action!
fetch next from #cur_tmpOrders into @DummyInt,@dummyChar
if @@fetch_Status<>0 break; -- 没有行了
fetch next from #cur_orders into @newOrderID,@NewCustomerID
End
close #cur_Orders
close #cur_tmpOrders
Deallocate #cur_Orders
Deallocate #cur_tmpOrders
Set @Code='Ok'
return
End
程序如下
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String url=
"jdbc:microsoft:sqlserver://192.168.0.102:1433;DatabaseName=Northwind";
String user="sa";
String password="";
Connection conn= DriverManager.getConnection(url,user,password);
CallableStatement stmt=conn.prepareCall("exec test ?,?,?");
stmt.setInt(1,10248);
stmt.setInt(2,10284);
stmt.registerOutParameter(3,Types.VARCHAR,1000);
stmt.setString(3,ret);
stmt.executeUpdate();
System.out.println(stmt.getString(3));
stmt.close();
stmt=null;
conn.close();
conn=null;
}catch(ClassNotFoundException e){
e.printStackTrace();
}catch(SQLException e){
e.printStackTrace();
}