一、一个简单的入门例子
(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 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR 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)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END 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 DO
SET 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))
BEGIN
DECLARE variable2 CHAR(100);
DECLARE c CURSOR FOR
SELECT table_name FROM information_schema.tables;
DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END; /* 1 */
SET sql_mode='ansi'; /* 2 */
SET parameter1 = '';
OPEN c;
LOOP
FETCH c INTO variable2; /* 3 */
SET parameter1 = parameter1 || variable2 || '.';
END LOOP;
CLOSE c;
END;