- /*创建有输入输出参数的存储过程*/
- DELIMITER $$
- DROP PROCEDURE IF EXISTS `cosbuyer`.`pp`$$
- CREATE
- PROCEDURE `cosbuyer`.`pp`(IN a INT,IN b INT,OUT c INT)
- BEGIN
- SET c=a+b;
- SELECT c;
- END$$
- DELIMITER ;
- CALL pp(10,20,@a);
- SELECT @a;
- /*创建有输入输出参数分页的存储过程*/
- DELIMITER $$
- DROP PROCEDURE IF EXISTS `cosbuyer`.`fenye`$$
- CREATE
- PROCEDURE `cosbuyer`.`fenye`(
- IN p_table_name VARCHAR(1024),
- IN p_fields VARCHAR(1024),
- IN p_page_size INT,
- IN p_page_now INT,
- IN p_order_string VARCHAR(128),
- IN p_where_string VARCHAR(1024),
- OUT p_out_rows INT)
- NOT DETERMINISTIC
- SQL SECURITY DEFINER
- COMMENT '分页存储过程'
- BEGIN
- /*定义变量*/
- DECLARE m_begin_row INT DEFAULT 0;
- DECLARE m_limit_string CHAR(64);
- /*构造语句*/
- SET m_begin_row = (p_page_now - 1)*p_page_size;
- SET m_limit_string = CONCAT(' limit ',m_begin_row,',',p_page_size);
- /*定义变量*/
- SET @COUNT_STRING = CONCAT('select count(*) into @ROWS_TOTAL from ',p_table_name,' ',p_order_string);
- SET @MAIN_STRING = CONCAT('select ',p_fields,' from ',p_table_name,' ',p_where_string,' ',p_order_string,' ',m_limit_string);
- /*预处理*/
- PREPARE count_stmt FROM @COUNT_STRING;
- EXECUTE count_stmt;
- DEALLOCATE PREPARE count_stmt;
- SET p_out_rows = @ROWS_TOTAL;
- PREPARE main_stmt FROM @MAIN_STRING;
- EXECUTE main_stmt;
- DEALLOCATE PREPARE main_stmt;
- END$$
- DELIMITER ;
- CALL fenye( 'tb_pdt_product','id,pdtname,price',10,11,'order by id desc ',' ',@abc);
- SELECT @abc;
- 说明:
- declare v_name varchar(50) default 'abc';
- set abc = 'singhoo';
- set @name='singhoo';
- @name与v_name的区别是:
- 它们的作用范围不一样@name是在会话过程起作用,它的作用是全局的,在存储过程之外也可以调用
- v_name是在begin...end语句块内,只能在块中使用
- 而且@name要定义在最后,否则会出错
MySQL存储过程
最新推荐文章于 2024-04-15 10:15:00 发布