Gq2Q4BcM

1CREATE TABLE `str_test` (
2  `id` int(11) NOT NULL AUTO_INCREMENT,
3  `name` varchar(50) DEFAULT NULL,
4  `ats` text,
5  `dor` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
6  PRIMARY KEY (`id`)
7) ENGINE=InnoDB DEFAULT CHARSET=utf8
8
9delimiter $$
10
11DROP FUNCTION str_filter $$
12
13CREATE FUNCTION str_filter(in_src TEXT)
14RETURNS TEXT DETERMINISTIC
15BEGIN
16    IF in_src IS NOT NULL THEN
17       RETURN CONCAT('''', in_src, '''');
18   ELSE
19      RETURN 'NULL';
20   END IF;    
21END $$
22
23
24CALL test_insert_data_null('guohai','text long long', '2011-07-11 13:33:21', @last_id, @no, @msg) $$
25
26SELECT @last_id, @no, @msg $$
27
28DROP PROCEDURE IF EXISTS test_insert_data_null $$
29
30CREATE PROCEDURE test_insert_data_null(in_name VARCHAR(20), in_ats TEXT, in_dor VARCHAR(20), OUT last_id BIGINT, OUT no INT, OUT msg VARCHAR(50))
31BEGIN
32	DECLARE INSERT_SQL VARCHAR(200);
33
34	SET @MYQUERY := CONCAT('INSERT INTO str_test(name, ats, dor) VALUES(', str_filter(in_name), ',', str_filter(in_ats), ',', 'str_to_date(''', in_dor, ''',', '''%Y-%m-%d %H:%i:%s''))');
35	PREPARE INSERT_SQL FROM @MYQUERY;
36	EXECUTE INSERT_SQL;
37	DEALLOCATE PREPARE INSERT_SQL;
38
39	SELECT LAST_INSERT_ID() INTO last_id;
40END $$
41
42-- of course the efficiency of the method is unsatisfied, but some sometimes we really really hava to do this