一、一个简单的入门例子
(1)创建表
CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
(2)创建存储过程
CREATE PROCEDURE `zxc`(out cnt int) begin declare maxid int; select max(id)+1 into maxid from users; insert into users values(maxid,'hello'); select count(id) into cnt from users; end
(3)java代码调用存储过程zxc
import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.Types;public class Test {
/** * @param args */ public static void main(String[] args) { // TODO Auto-generated method stub try{ Class.forName("com.mysql.jdbc.Driver"); Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123"); CallableStatement call=conn.prepareCall("{call zxc(?)}"); call.registerOutParameter(1, Types.INTEGER); call.execute(); System.out.println(call.getInt(1)); }catch(Exception e){ e.printStackTrace(); System.out.println(e.getMessage()); } }
}二、存储过程常见语法
1.delimiter // 声明一个分隔符
2.drop procedure if exists pro_name ;//
3.IN example 输入的例子mysql> CREATE PROCEDURE p5(p INT) SET @x = p //Query OK, 0 rows affected (0.00 sec)mysql> CALL p5(12345)//Query OK, 0 rows affected (0.00 sec)mysql> SELECT @x//+-------+| @x |+-------+| 12345 |+-------+1 row in set (0.00 sec)
4.OUT example 输出的例子
mysql> CREATE PROCEDURE p6 (OUT p INT)-> SET p = -5 //mysql> CALL p6(@y)//mysql> SELECT @y//+------+| @y |+------+| -5 |+------+
5.declare variable int default 1 声明一个默认值是1的整形变量
6.if...then ...end if和if...then ...else ...end if
7.case 指令
case variable
when 0 then ...;
when 1 then ...;
...
else ...;
end case;
8.循环语句
(1)while ... end while
while a>5 do
...;
set a=a+1；
end while
(2)repeat...end repeat
repeat
...;
set a=a+1;
util a>=5
end repeat;
(2)loop...end loop
loop_label:loop
...;
set a=a+1;
if a>=5 then
level loop_label;
end if;
end loop;
(3)iterate loop_label 继续循环
(4)DECLARE HANDLER syntax 声明异常处理的语法DECLARE{ EXIT | CONTINUE }HANDLER FOR{ error-number | { SQLSTATE error-string } | condition }SQL statement
例子:
CREATE TABLE t4 (s1 int,primary key(s1));//CREATE PROCEDURE p23 ()BEGINDECLARE CONTINUE HANDLERFOR SQLSTATE '23000' SET @x2 = 1;SET @x = 1;INSERT INTO t4 VALUES (1);SET @x = 2;INSERT INTO t4 VALUES (1);SET @x = 3;END;//
三、游标
(1)声明游标
declare cursor_name cursor for select column from tablename;
(2)打开游标
open cursor_name;
(3)从游标里读取
fetch cursor_name into variable;
(4)关闭游标
close cursor_name;
例子:
DELIMITER //CREATE PROCEDURE p25 (OUT return_val INT)BEGINDECLARE a,b INT;DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;DECLARE CONTINUE HANDLER FOR NOT FOUNDSET b = 1;OPEN cur_1;REPEATFETCH cur_1 INTO a;UNTIL b = 1END REPEAT;CLOSE cur_1;SET return_val = a;END;//
CALL p25(@Y);SELECT @Y;
(5)游标的特性
read only 只读属性
not scrollable 顺序读取
asensitive 敏感
fetch cursor1 into variable1;
update t1 set column='value' where current of cursor1;
游标也是不可以滚动的，只允许逐一读取下一行，不能在结果集中前进或后退
四、函数
1.入门--求N的阶乘的函数DELIMITER //CREATE FUNCTION f (n INT) RETURNS INT(11)BEGIN DECLARE a INT DEFAULT 1;WHILE n>0 DOSET a=a*n;SET n=n-1;END WHILE;RETURN a;END;//
--根据出生日期求年龄的函数
CREATE FUNCTION f_age (in_dob datetime) RETURNS int(11) BEGIN DECLARE l_age INT; IF DATE_FORMAT(NOW( ),'00-%m-%d') >= DATE_FORMAT(in_dob,'00-%m-%d') THEN SET l_age=DATE_FORMAT(NOW( ),'%Y')-DATE_FORMAT(in_dob,'%Y'); ELSE SET l_age=DATE_FORMAT(NOW( ),'%Y')-DATE_FORMAT(in_dob,'%Y')-1; END IF; RETURN(l_age); END
--字符串连接函数
CREATE PROCEDURE tables_concat(OUT parameter1 VARCHAR(1000))BEGINDECLARE variable2 CHAR(100);DECLARE c CURSOR FORSELECT table_name FROM information_schema.tables;DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END; /* 1 */SET sql_mode='ansi'; /* 2 */SET parameter1 = '';OPEN c;LOOPFETCH c INTO variable2; /* 3 */SET parameter1 = parameter1 || variable2 || '.';END LOOP;CLOSE c;END;
