- 資料庫表
1、tb_base_company
2、tb_base_manager
- 建立存儲過程、存儲函數,并測試
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
CREATE PROCEDURE delete_com_by_id (IN com_pkid INT)
BEGIN
DELETE FROM tb_base_company where pkid = com_pkid;
END
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
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(#{pkid,mode=OUT,jdbcType=INTEGER},#{companyCode,mode=IN,jdbcType=VARCHAR},#{companyName,mode=IN,jdbcType=VARCHAR})} </insert> <!-- --> <delete id="deleteByPrimaryKeyProcedure" parameterType="java.lang.Integer" statementType="CALLABLE"> {call delete_com_by_id(#{pkid,mode=IN,jdbcType=INTEGER})} </delete> <!-- --> <select id="selectByIdProcedure" parameterType="java.lang.Integer" resultMap="BaseResultMap" statementType="CALLABLE"> {call select_com_by_id(#{pkid,mode=IN,jdbcType=INTEGER})} </select> <!-- 調用存儲函數,傳回結果會放在參數map中。 注意:不要寫成jdbcType=DECIMAL(10,2) --> <select id="selectAddDecimalProcedure" parameterType="map" statementType="CALLABLE"> {#{resultNum,mode=OUT,jdbcType=DECIMAL}=call add_decimal_param(#{num1,mode=IN,jdbcType=DECIMAL},#{num2,mode=IN,jdbcType=DECIMAL})} </select> |
2、mapper.java
int deleteByPrimaryKeyProcedure(Integer pkid); int insertProcedure(Map<String, Object> map); BaseCompany selectByIdProcedure(Integer pkid); void selectAddDecimalProcedure(Map<String, Object> map); |
3、serviceImpl.java
@Override public int insertProcedure(Map<String, Object> map) throws Exception { int insertResult = companyMapper.insertProcedure(map); return insertResult; } @Override public int deleteByPrimaryKeyProcedure(Integer pkid) throws Exception { int deleteResult = companyMapper.deleteByPrimaryKeyProcedure(pkid); return deleteResult; } @Override public BaseCompany selectByIdProcedure(Integer pkid) throws Exception { BaseCompany company = companyMapper.selectByIdProcedure(pkid); return company; } @Override public void selectAddDecimalProcedure(Map<String, Object> map) { companyMapper.selectAddDecimalProcedure(map); } |
4、serviceTest.java
@Test public void testInsertProcedure() throws Exception { String companyCode = "com008";String companyName = "企業008"; Map<String, Object> map = new HashMap<String, Object>(); map.put("companyCode", companyCode); map.put("companyName", companyName); int insertResult = companyService.insertProcedure(map); logger.warn("insertResult={};map={}",insertResult,JSON.toJSON(map)); } @Test public void testDeleteByPrimaryKeyProcedure() throws Exception { Integer pkid = 67; int deleteResult = companyService.deleteByPrimaryKeyProcedure(pkid); logger.warn("deleteResult={}",deleteResult); } @Test public void testSelectByIdProcedure() throws Exception { Integer pkid = 6; BaseCompany company = companyService.selectByIdProcedure(pkid); logger.warn("company={}",JSON.toJSON(company)); } @Test public void testSelectAddDecimalProcedure() throws Exception { Map<String, Object> map = new HashMap<String, Object>(); map.put("num1", 1.2); map.put("num2", 2.6); companyService.selectAddDecimalProcedure(map); logger.warn("map={}",JSON.toJSON(map)); } |
5、測試結果
testInsertProcedure()測試結果如下: insertResult=1;map={"companyCode":"com008","pkid":76,"companyName":"企業008"} testDeleteByPrimaryKeyProcedure()測試結果如下: deleteResult=1 testSelectByIdProcedure()測試結果如下: company={"companyCode":"com006","firstManagerName":"","certificateCode":"","pkid":6,"regionCode":"","firstManagerTelephone":"","companyAddress":"","companyName":"企業006","groupCode":""} testSelectAddDecimalProcedure()測試結果如下: map={"num1":1.2,"resultNum":3.80,"num2":2.6} |