- 数据库表
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="CALLABLE"> {call insert_com_by_code_name(#{ |