mysql5.x函数跟存储过程

     mysql5.x存储过程和函数（尤其是）特性，创建方式更改，差不多是为了排除分号影响（个人笔记，仅供自己参考）

  详见changes：http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

   CREATE PROCEDURE and CREATE FUNCTION Syntax：

CREATE     [DEFINER = { user | CURRENT_USER }]     PROCEDURE sp_name ([proc_parameter[,...]])     [characteristic ...] routine_body  CREATE     [DEFINER = { user | CURRENT_USER }]     FUNCTION sp_name ([func_parameter[,...]])     RETURNS type     [characteristic ...] routine_body  proc_parameter:     [ IN | OUT | INOUT ] param_name type  func_parameter:     param_name type  type:     Any valid MySQL data type  characteristic:     COMMENT 'string'   | LANGUAGE SQL   | [NOT] DETERMINISTIC   | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }   | SQL SECURITY { DEFINER | INVOKER }  routine_body:     Valid SQL routine statement

欢迎大家阅读《mysql5.x函数跟存储过程》,跪求各位点评，by 搞代码

    Official Demo1:

mysql> delimiter //  mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)     -> BEGIN     ->   SELECT COUNT(*) INTO param1 FROM t;     -> END// Query OK, 0 rows affected (0.00 sec)  mysql> delimiter ;  mysql> CALL simpleproc(@a); Query OK, 0 rows affected (0.00 sec)  mysql> SELECT @a; +------+ | @a   | +------+ | 3    | +------+ 1 row in set (0.00 sec)

   Official Demo2:

mysql> CREATE FUNCTION hello (s CHAR(20)) mysql> RETURNS CHAR(50) DETERMINISTIC     -> RETURN CONCAT('Hello, ',s,'!'); Query OK, 0 rows affected (0.00 sec)  mysql> SELECT hello('world'); +----------------+ | hello('world') | +----------------+ | Hello, world!  | +----------------+ 1 row in set (0.00 sec)

    User Demo1:

An example using varchar and character sets  delimiter // CREATE FUNCTION db.fnfullname ( id smallint(5) unsigned ) RETURNS varchar(160) CHARACTER SET utf8 COMMENT 'Returns the full name of person in db.people table referenced by id where FirstName and FamilyName are not null but MiddleName may be null' DETERMINISTIC READS SQL DATA BEGIN DECLARE fulname varchar(160) CHARACTER SET utf8; SELECT CONCAT_WS(' ', db.people.FirstName, db.people.MiddleName, db.people.FamilyName) into fulname from db.people where db.people.id=id; RETURN fulname; END //  delimiter ;

    User Demo2:

Sample for Procedure with insert to trace error logs  DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `add_error_log`( `error_level` int(11), `error_level_name` varchar(512), `error_message` longtext, `error_file` text, `error_line` int(11), `error_context` longtext, `error_query_string` longtext, `error_time` text , `user_id` int(11), `post_data` longtext, `user_msg` text) BEGIN INSERT INTO tbl_error_log(error_level, error_level_name, error_message, error_file,error_line, error_context,error_query_string,error_time, user_id, post_data, user_msg) values(error_level, error_level_name, error_message, error_file,error_line, error_context,error_query_string, error_time, user_id, post_data, user_msg); END$$

    User Demo3:

I didn't find a lot of examples for MySQL Stored Procs so I thought I'd add one. This one uses a temp table and iterates through the rows. It was a pain in the butt to debug in phpMyAdmin. There was a lot of dropping and re-creating.  ############################  delimiter $$  create procedure assignKeywords( currCID int ) begin  declare currKeywordID int; declare currTitle varchar(255); declare currKeyword varchar(255); declare currContent text; declare keywordsCSV varchar(255);  -- get content select id, title, content from content where id = currCID into currCID, currTitle, currContent;  -- create the keywords temp table to iterate drop temporary table if exists keywords_temp; create temporary table keywords_temp as select id, display_val from vars where name = 'KEYWORDS' order by display_order desc;  set keywordsCSV = ","; while (select count(*) from keywords_temp) > 0 do  select id, display_val from keywords_temp limit 1 into currKeywordID, currKeyword;  -- search the content for current keyword if instr(currContent,currKeyword) > 0 || instr(currTitle,currKeyword) > 0 then set keywordsCSV = CONCAT(keywordsCSV, currKeywordID); set keywordsCSV = CONCAT(keywordsCSV, ","); end if;  delete from keywords_temp where id = currKeywordID;  end while;  drop temporary table if exists keywords_temp;  update content set keywords = keywordsCSV where id = currCID;  end;  ###############################

    User Demo4:

CREATE TABLE IF NOT EXIST Osob ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (P_Id) ); CREATE TABLE uzytkownicy2 ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), FOREIGN KEY (P_Id) References Osob(P_Id) ); ----------------------------------- INSERT INTO `osob`(`O_Id`, `OrderNo`, `P_Id`) VALUES (1,2,3) INSERT INTO `uzytkownicy2`(`O_Id`, `OrderNo`, `P_Id`) VALUES (1,2,3) ----------------------------------- DELIMITER $$ CREATE PROCEDURE wam2(OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM trenowanie_mysql.osob; END$$ DELIMITER ; ------------------------------------ CALL wam2(@a);