MYSQL简单存储过程创建和java中的调用

创建存储过程：
-------------------------------------------------------------------------------------------------------------------
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
	 */

}