创建存储过程:
-------------------------------------------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS p_test1;
DELIMITER $$
CREATE
PROCEDURE `xxdb`.`p_test1`(status1 VARCHAR(2),detail1 VARCHAR(30), sn VARCHAR(10),OUT outmsg VARCHAR(50))
BEGIN
DECLARE cnttask INT;
DECLARE lock_cursor CURSOR FOR
SELECT COUNT(1) AS cnt FROM tasktable;
START TRANSACTION;
OPEN lock_cursor;
FETCH lock_cursor INTO cnttask;
UPDATE tasktable a SET detail= CONCAT('cnt',cnttask,status1,detail1) WHERE a.sn=sn;
CLOSE lock_cursor;
SELECT taskdetail INTO outmsg FROM tasktable WHERE tasktable.sn=sn;
COMMIT;
END$$
DELIMITER ;
-------------------------------------------------------------------------------------------------------------------
调用:
CALL p_test1('XY','dssss2','0000000011',@xx);
SELECT @xx;
-------------------------------------------------------------------------------------------------------------------
创建函数:
DROP FUNCTION IF EXISTS f_test1;
DELIMITER $$
USE `atm`$$
DROP FUNCTION IF EXISTS `f_test1`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `f_test1`( param VARCHAR(10)) RETURNS VARCHAR(50) CHARSET gbk
BEGIN
DECLARE hi VARCHAR(50);
DECLARE len INT DEFAULT 0 ;
SET hi = 'from f_test1';
SET len = LENGTH(param);
RETURN CONCAT('Hi [',param,']',' from[',hi,'] length:',len);
END$$
DELIMITER ;
------------------------------------------------------------------------------------------------------------------------
调用:
SELECT f_test1('abcdefg');
--------------------------------------------------------------------------------------------------------------------------
/* java中调用存储过程: */
package com.database.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
public class TestCallProcedure {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection con = null;
CallableStatement st = null;
String sql = "{ call p_test1(?,?,?,?) }";//3个输入参数,1个输出参数
try {
con = TestMySQLdatasource.getConnection();
st = con.prepareCall(sql);
st.setString(1, "a1" );//输入参数
st.setString(2, "XYZ");//输入参数
st.setString(3, "0000000011");//输入参数
st.registerOutParameter(4, Types.VARCHAR);//输出参数,结果,位于第4
st.execute();
System.out.println(st.getString(4));
con.close();
} catch (SQLException e) {
System.out.println(e.toString());
}
}
/*
*
* CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test1`(status1 VARCHAR(2),detail1 VARCHAR(30), sn VARCHAR(10),out outmsg varchar(50))
BEGIN
DECLARE cnttask INT;
DECLARE lock_cursor CURSOR FOR
SELECT COUNT(1) AS cnt FROM tasktable;
START TRANSACTION;
OPEN lock_cursor;
FETCH lock_cursor INTO cnttask;
UPDATE tasktalbe a SET taskdetail= CONCAT('cnt',cnttask,status1,detail1) WHERE a.sn=sn;
CLOSE lock_cursor;
select taskdetail into outmsg from tasktalbe where tasktalbe.sn=sn;
COMMIT;
END
*/
}