artin', '19960725', '20060725', 1234.56, 'Toron
to', 'Programmer'),
-> (2,'Alison', 'Mathews', '19760321', '19860221', 6661.78, 'Vancouve
r','Tester'),
-> (3,'James', 'Smith', '19781212', '19900315', 6544.78, 'Vancouve
r','Tester'),
-> (4,'Celia', 'Rice', '19821024', '19990421', 2344.78, 'Vancouve
r','Manager'),
-> (5,'Robert', 'Black', '19840115', '19980808', 2334.78, 'Vancouve
r','Tester'),
-> (6,'Linda', 'Green', '19870730', '19960104', 4322.78,'New York'
, 'Tester'),
-> (7,'David', 'Larry', '19901231', '19980212', 7897.78,'New York'
, 'Manager'),
-> (8,'James', 'Cat', '19960917', '20020415', 1232.78,'Vancouver
', 'Tester');
Query OK, 8 rows affected (0.03 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> DELIMITER //
mysql> CREATE PROCEDURE merge_employee (IN old_id INT, IN new_id INT, OUT error
VARCHAR(100))
-> SQL SECURITY DEFINER
-> COMMENT 'This is the comment'
-> BEGIN
-> DECLARE old_count INT DEFAULT 0;
-> DECLARE new_count INT DEFAULT 0;
-> DECLARE addresses_changed INT DEFAULT 0;
->
-> ## check to make sure the old_id and new_id exists
-> SELECT count(*) INTO old_count FROM employee WHERE id = old_
id;
-> SELECT count(*) INTO new_count FROM employee WHERE id = new_
id;
->
-> IF !old_count THEN
-> SET error = 'old id does not exist';
-> ELSEIF !new_count THEN
-> SET error = 'new id does not exist';
-> ELSE
-> UPDATE employee SET id = new_id WHERE id = old_id;
-> SELECT row_count() INTO addresses_changed;
->
-> DELETE FROM employee WHERE id = old_id;
->
-> SELECT addresses_changed;
->
-> END IF;
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call merge_employee(1,4,@error);
+-------------------+
| addresses_changed |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.08 sec)
Query OK, 0 rows affected (0.09 sec)
mysql> select @error;
+--------+
| @error |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
一个简单的while循环,计算前四个字母的ASCII码:
mysql> delimiter $$
mysql> CREATE FUNCTION myFunction5(in_string varchar(80) )
-> RETURNS VARCHAR(256)
-> NO SQL
-> BEGIN
-> DECLARE i INT DEFAULT 1;
-> DECLARE string_len INT;
-> DECLARE out_string VARCHAR(256) DEFAULT '';
-> SET string_len=length(in_string);
-> WHILE (i -> SET out_string=CONCAT(out_string,ASCII(substr(in_string,i,1
)),' ');
-> SET i=i+1;
-> END WHILE;
-> RETURN (out_string);
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> select myFunction5('ABCDE');
+----------------------+
| myFunction5('ABCDE') |
+----------------------+
| 65 66 67 68 |
+----------------------+
1 row in set (0.00 sec)
另一个demo(拼sql语句)：
mysql> delimiter $$
mysql> CREATE PROCEDURE sp_customer_search
-> (in_Description VARCHAR(30
-> in_contact_surname VARCHA
-> in_contact_firstname VARC
-> in_city VARCHAR(10))
-> BEGIN
-> DECLARE l_where_clause VARCH
-> IF in_Description IS NOT NUL
-> SET l_where_clause=CONCA
-> ' description="',in_D
-> END IF;
-> select l_where_clause;
-> IF in_contact_surname IS NOT
-> IF l_where_clause<>'WHERE
-> SET l_where_clause=CON
-> END IF;
-> SET l_where_clause=CONCAT
-> ' last_name="',in_con
-> END IF;
-> select l_where_clause;
-> IF in_contact_firstname IS N
-> IF l_where_clause<>'WHERE
-> SET l_where_clause=CON
-> END IF;
-> SET l_where_clause=CONCAT
-> ' first_name="',in_co
-> END IF;
-> select l_where_clause;
-> IF in_city IS NOT NULL THEN
-> IF l_where_clause<>'WHERE
-> SET l_where_clause=CON
-> END IF;
-> SET l_where_clause=CONCAT
-> ' city="',in_city,'"'
-> END IF;
-> select l_where_clause;
-> SET @sql= |