在mybatis中调用mysql存储过程和存储函数

• 数据库表

1、tb_base_company

2、tb_base_manager

• 创建存储过程、存储函数，并测试

/*insert存储过程，返回数据主键*/

CREATE PROCEDURE insert_com_by_code_name (OUT com_pkid INT,IN com_code VARCHAR(20),IN com_name VARCHAR(100))

BEGIN

       INSERT INTO tb_base_company (company_code,company_name) VALUES (com_code,com_name);

       SET com_pkid = LAST_INSERT_ID();

END

/*delete存储过程。（参数名和字段名千万别写成一样的，否则会删库，能不能跑路就看你的本事了）*/

CREATE PROCEDURE delete_com_by_id (IN com_pkid INT)

BEGIN

       DELETE FROM tb_base_company where pkid = com_pkid;

END

/* select存储过程*/

CREATE PROCEDURE select_com_by_id (IN com_pkid INT)

READS SQL DATA

BEGIN

       SELECT * FROM tb_base_company c WHERE pkid = com_pkid;

END

/*求和存储函数*/

CREATE FUNCTION add_decimal_param (num1 DECIMAL(10,2),num2 DECIMAL(10,2))

returns DECIMAL(10,2)

BEGIN

       RETURN num1+num2;

END

/*select count(1) 存储过程*/

CREATE PROCEDURE com_count_by_managername (IN managername VARCHAR(20),OUT com_count INT)

READS SQL DATA

BEGIN

       SELECT count(c.pkid) INTO com_count

       FROM tb_base_company c

       LEFT JOIN tb_base_manager m

       ON c.company_code = m.company_code

       WHERE INSTR(m.manager_name,managername)>0;

END

-- 测试

-- set @com_code='com008',@com_name='企业008';  CALL insert_com_by_code_name(@com_pkid,@com_code,@com_name);  select @com_pkid com_pkid;

-- set @com_pkid='1';  CALL delete_com_by_id(@com_pkid);

-- set @com_pkid='2';  CALL select_com_by_id(@com_pkid);

-- select add_decimal_param(1.1,2.2);

-- set @com_name='张';  CALL com_count_by_name(@com_name,@com_count);  select @com_count com_count;

•  

1、mappper.xml

<!-- 调用存储过程 -->   <!-- 返回结果会放在参数map中 -->   <insert id="insertProcedure" parameterType="map" statementType=