1.创建表test_procedure
create table test_procedure(
id int primary key AUTO_INCREMENT,
name varchar(12)
)
2.插入数据
insert into test_procedure(name) values('qx');
insert into test_procedure(name) values('james');
insert into test_procedure(name) values('curry');
insert into test_procedure(name) values('wade');
3.创建存储过程
create procedure `callprocedurename`(IN in_id int,OUT out_name varchar(12))
BEGIN
#存储过程
select name
into out_name
from test_procedure
where id = in_id;
END
4.执行存储过程
CALL callprocedurename(2,@out_name);
select @out_name;
备注:
1.IN表示输入参数,按类型直接输入值
2.OUT表示输出参数,用@+值(@out_name)输入,并在存储过程中为其赋值后,可以select@out_name,以显示其值。
3.OUT只能接收一行的对应值,如果查询出两行结果会报错
4.可以在过程中定义变量,并为其赋值,再通过操作变量操作值
#定义变量
DECLARE v_userName VARCHAR(50);
db2存储过程示例
CREATE OR REPLACE PROCEDURE "DB2INST1"."PROC_RECON_AMOUNT"
(IN RECON_DATE VARCHAR(19),OUT SUM_AMOUNT DECIMAL(18,0))
LANGUAGE SQL
SPECIFIC SQL190529200848119
BEGIN
SELECT SUM(TBL_UNMC_FLW.AMOUNT)
INTO SUM_AMOUNT
FROM TBL_UNMC_FLW
WHERE TBL_UNMC_FLW.LOCAL_DATETIME = RECON_DATE;
END