mySql存储过程的结构

无参数的存储过程

CREATE PROCEDURE P_XSRBB()
BEGIN
		DECLARE VP1 VARCHAR(20);
		SET VP1=4;
SELECT VP1 FROM DUAL;
END;
CALL P_XSRBB();

带输入参数的存储过程

DROP PROCEDURE IF EXISTS P_XSRBB;
CREATE PROCEDURE P_XSRBB(in vp2 INT)
BEGIN
		DECLARE VP1 VARCHAR(20);
		SET VP1=4;
SELECT VP2 FROM DUAL;
END;
CALL P_XSRBB(3434);

带参数的拼接字符串

DROP PROCEDURE IF EXISTS P_TEXT_JCY;
CREATE PROCEDURE P_TEXT_JCY(IN VP VARCHAR(8000))
BEGIN 		
		SET @sql1='SELECT 1 FROM dual';
		PREPARE stmt1 FROM @sql1;
		EXECUTE stmt1;
END;

带参数的动态sql

DROP PROCEDURE IF EXISTS execproc;
CREATE  PROCEDURE execproc(IN tbname  varchar(100))
BEGIN

	set @sql1 = CONCAT('select * from ', tbname);
	set @sql2=concat('select 1 ', 'from dual');
	PREPARE stmt1 FROM @sql1;
  EXECUTE stmt1;

END
CALL execproc('ipos_lsxhd')