创建存储过程
- DELIMITER $$
- DROP PROCEDURE IF EXISTS `sp_test1`;
- CREATE PROCEDURE sp_test1(IN a int, IN b int, OUT sum int)
- BEGIN
- DECLARE c int;
- if a is null then set a = 0;
- end if;
- if b is null then set b = 0;
- end if;
- set sum = a + b;
- END; $$
- DELIMITER ;
查询存储过程
- SHOW PROCEDURE | FUNCTION STATUS LIKE '%sp%';
- SHOW PROCEDURE STATUS WHERE Db = 'testdb';
查询存储过程定义
- SHOW CREATE PROCEDURE `usp_test1` \G;
- SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA='testdb' \G;
修改存储过程
使用 ALTER 语句可以修改存储过程或函数的特性, 只能修改特性, 如果想修改过程体只能删除存储过程再重新创建.
ALTER {PROCEDURE|FUNCTION} sp_name [characteriss]
删除存储过程
DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name
创建函数
- SET GLOBAL log_bin_trust_function_creators = 1;
- DELIMITER $$
- DROP FUNCTION IF EXISTS `ufn_test2`;
- CREATE FUNCTION ufn_test2()
- RETURNS VARCHAR(100)
- BEGIN
- RETURN(SELECT 1);
- END $$
- DELIMITER ;
- SET GLOBAL log_bin_trust_function_creators = 0;
来源: http://www.bubuko.com/infodetail-2643364.html