mysql存储过程学习笔记1

一、一个简单的入门例子

(1)创建表

<!--StartFragment-->CREATETABLE`users`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`name`varchar(30)DEFAULTNULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDBAUTO_INCREMENT=9DEFAULTCHARSET=utf8

(2)创建存储过程

<!--StartFragment--> CREATEPROCEDURE`zxc`(outcntint)
begin
declaremaxidint;
selectmax(id)+1intomaxidfromusers;
insertintousersvalues(maxid,'hello');
selectcount(id)intocntfromusers;
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 intdefault 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;//

--根据出生日期求年龄的函数

<!--StartFragment-->

CREATEFUNCTIONf_age (in_dobdatetime)RETURNSint(11)
BEGIN
DECLAREl_ageINT;
IFDATE_FORMAT(NOW(),'00-%m-%d')>=DATE_FORMAT(in_dob,'00-%m-%d')THEN
SETl_age=DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(in_dob,'%Y');
ELSE
SETl_age=DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(in_dob,'%Y')-1;
ENDIF;
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;