Sybase ASE 系列第二讲: 存储过程

Sybase ASE 存储过程

Sybase ASE 存储过程基础知识

0.存储过程规则

1.一定要切换到存储过程所在数据库,使用 USE XXX
2.判断和提示信息 IF ELSE print
3.存储过程开始,见GO结束
4.变量定义 DECLARE @lol_account int
5.删除临时表前要先truncate,不能用delete
6.@@rowcount常用系统变量,返回受上一语句影响的行数
-- SELECT@row = @@rowcount

1.创建存储过程

create procedure [procedure_name]
as begin
   SQL_statements [return]
end

2.执行存储过程

exec [procedure_name] [参数]

3.查看自建的存储过程

select name from sysobjects where type="P"
go

4.查看创建的存储过程源代码

sp_helptext [procedure_name] 

5.创建存储过程案例

-- 带有返回参数的存储过程
if exists ( select 1 from sysobjects where name = 'sp_test' )
drop proc sp_test
go
 
if exists ( select 1 from sysobjects where name = 'tbl_name')
drop table tbl_name
go
 
create table tbl_name(id int primary key, col2 varchar(32) not null)
insert into tbl_name values(1, 'iihero')
insert into tbl_name values(2, 'Sybase')
insert into tbl_name values(3, 'ASE')
go
 
create proc sp_test (@id_min int, @num_t123 int output) with recompile
as
select @num_t123 = count( a.id ) from tbl_name a where a.id > = @id_min   -- 计算id大于等于参数@id_min的id数量存入参数@num_123中返回
go 

-- 执行存储过程
declare @num_t123 int
exec sp_test 1, @num_t123 output
select  @num_t123
go

6.局部变量

## 定义局部变量
declare @msg varchar(40) declare @myqty int, @myid char(4)

-- 使用SELECT语句将指定值赋给局部变量
declare @var1 int select @var1=99

局部变量必须先用DECLARE定义,再用SELECT语句赋值后才能使用。 局部变量只能使用在T－SQL语句中使用常量的地方。 局部变量不能使用在表名、列名、
其它数据库对象名、保留字使用的地方。 局部变量是标量,它们拥有一个确切的值。 赋值给局部变量的SELECT语句应该返回单个值。如果赋值的SELECT语句没有返
回值,则该局部变量的值保持不变；如果赋值的SELECT语句返回多个值,则该局 部变量取最后一个返回的值。
注意: 在一个赋值给局部变量的select 语句中, 可以使用常数、 从表中取值、或使用表达式给局部变量赋值。
不能使用同一SELECT 语句既给局部变量赋值,又检索 数据返回给客户。 — 一个赋值给局部变量的SELECT 语句,不向用户显示任 何值。

7.全局变量

全局变量由sql server系统提供并赋值,用户不能创建或赋值,由@@开头

可使用系统存储过程sp_monitor显示当前全局变量的值。

常用全局变量
@@error :由最近一个语句产生的错误号
@@rowcount : 被最近一个语句影响的行数
@@version : sql server版本号
@@max_connections :允许最大用户连接量
@@Servername ： 该sql_server的名字

8.流程控制

IF EXISTS 和 IF NOT EXISTS
语法(ASE)  
if [not] exists (select statement) statement block
 
 举例(ASE) 举例 /* 是否存在姓'Smith'的作者 */ 
declare @lname varchar(40) 
select @lname = 'Smith' if exists ( select * from authors where au_lname = @lname)
select 'here is a ' + @lname else select 'here is no author called'+@lname

BEGIN ... END
功能 当需要将一个以上的SQL 语句作为一组语句对待时, 可以 使用BEGIN 和END 将它们括起来形成一个SQL 语句块。从 语法上看,一个SQL 语句块相当于一个SQL 语句。在流控制 语言中, 允许用一个SQL 语句块替代单个SQL 语句出现的地 方。
语法 BEGIN statement block END
statement block 为一个以上的sql语句
WHILE
语法(ASE) 语法
while boolean exprission statement block
语法(IQ) 语法
while boolean exprission loop statement block end loop

例：

while (select avg(price) from titles) < $40
begin
select title_id, price from titles where price > $20 update titles set price = price + $2
end
select title_id, price from titles
print "Too much for the market to bear"

9.嵌套事务

嵌套事务 是指在存储过程中的事务的间接嵌套, 即嵌套事务的形成是因为调用 含有事务的过程。@@trancount 记录了事务嵌套级次。
@@trancount在第一个 begin tran语句后值为1,以后每遇到一个 begin tran 语句,不论是否在嵌套 过程中,@@trancount的值增加1；
每遇到 一个commit,@@trancount的值就减少 1。若@@trancount的 值 等于 零,表示当前没有事务；若@@trancount的值不等 于零,
其值假定为i,表明当前处于第 i 级嵌套事务中。对于嵌套事务,直到使用@@trancount 的值为零的那个commit语句被执行,整个事务才被提交。 select @@trancount

10.带参数的函数

create function func_test(@id_min int)
returns int
as
begin
    declare @num_t123 int
    select @num_t123 = count( a.id ) from tbl_name a where a.id > = @id_min
    return @num_t123
end

-- 执行函数
select dbo.func_test(1)
go

11.存储过程中的游标

存储过程隐式游标语法：

FOR A AS B CURSOR FOR SELECT ... FROM ...  
DO
.... 过程语句
END FOR;

需要注意的是，这边的A 和 B 在 过程语句中都不能引用，所以为避免过程语句其他字段名与FOR SELECT 语句的字段名称重复，FOR SELECT 语句的字段最好都定义别名区分

12.存储过程中的临时表

在存储过程或应用程序里，带#的临时表必须显式删除，先truncate table，然后drop table

-- 临时表的创建同正常表创建一致，只不过它只保留在内存中(会话中)
truncate table #test
drop table #test 
go

Sybase ASE 存储过程案例

1.Java调用存储过程

这里我们以有返回值的存储过程作为案例展示，首先你需要创建表结构和存储过程

-- 创建表
CREATE TABLE tbl_name (
	id int NOT NULL,
	col2 varchar(32) NOT NULL,
	CONSTRAINT TBL_NAME_PK PRIMARY KEY (id)
);

-- 创建存储过程
CREATE UNIQUE INDEX tbl_name_id_8000028501 ON guest.dbo.tbl_name (id);
create proc sp_test(@id_min int, @num_t123 int output) with recompile
as
select @num_t123 = count(a.id)
from tbl_name a
where a.id >= @id_min;

Mybatis 调用案例

现在很多项目都是Mybatis，先看看Mybatis 如何调用存储过程

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ixan.example.infra.gatewayimpl.mapper.SybaseTestUserMapper">
    <!--计算id大于等于参数@id_min的id数量存入参数@num_123中返回-->
    <select id="countIdGreaterThan" statementType="CALLABLE" parameterType="com.ixan.example.domain.CountValueDTO">
        {call sp_test(
                #{minId,mode=IN,jdbcType=INTEGER},
                #{countNo,mode=OUT,jdbcType=INTEGER}
            )}
    </select>

</mapper>

@Repository
public interface SybaseTestUserMapper extends BaseMapper<SybaseTestUser> {
	void countIdGreaterThan(CountValueDTO countValueDTO);
}

@Getter
@Setter
public class CountValueDTO {
	private Integer minId;
	private Integer countNo;

	public CountValueDTO(Integer minId) {
		if (Objects.isNull(minId)) {
			throw new RuntimeException("minId can not be null");
		}
		this.minId = minId;
	}
}

这里需要注意的是存储过程的入参和返回值需要封装至一个对象中，Mybatis Mapper的方法签名为 void，返回结果会自动封装至对象中

JdbcTemplate 调用案例

原生Jdbc方式这里不展示，大多数情况下都是使用Spring 的 JdbcTemplate调用存储过程

@Component
public class JdbcTemplateWrapper {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public Integer countIdGreaterThan(Integer id_min) {
		return jdbcTemplate.execute((CallableStatementCreator) connection -> {
			String storedProc = "{call sp_test (?,?)}";
			CallableStatement cs = connection.prepareCall(storedProc);
			// 设置输入参数的值
			cs.setInt(1, id_min);
			// 注册输出参数的类型
			cs.registerOutParameter(2, Types.INTEGER);
			return cs;
		}, callableStatement -> {
			callableStatement.execute();
			return callableStatement.getInt(2);
		});
	}
}

未完待续...