SQL存储过程和函数(1)

定义:

存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。 存储过程和函数可以避免开发人员重复的编写相同的SQL语句。 而且, 存储过程和函数是在MySQL服务器中存储和执行的,可以减少客户端和服务器端的数据传输。

创建存储过程:

CREATE PROCEDURE sp_name([proc_parameter[,...]])

[characteristic...] routine_body

sp_name参数是存储过程的名称;

proc_parameter表示存储过程的参数列表;characteristic参数指定存储过程的特性;routine_body参数是SQL代码的内容,可以用BEGIN...END来标志SQL代码的开始和结束。proc_parameter中的每个参数由3部分组成。这3部分分别是输入输出类型、参数名称和参数类型。[ IN | OUT | INOUT ] param_name type其中,IN表示输入参数;OUT表示输出参数;INOUT表示既可以是输入,也可以是输出;param_name参数是存储过程的参数名称;type参数指定存储过程的参数类型,该类型可以是MySQL数据库的任意数据类型;Characteristic参数有多个取值。其取值说明如下:LANGUAGE SQL:说明routine_body部分是由SQL语言的语句组成,这也是数据库系统默认的语言。[ NOT ] DETERMINISTIC:指明存储过程的执行结果是否是确定的。DETERMINISTIC表示结果是确定的。每次执行存储过程时,相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是非确定的,相同的输入可能得到不同的输出。默认情况下,结果是非确定的。{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }:指明子程序使用SQL语句的限制;CONTAINS SQL表示子程序包含SQL语句,但不包含读或写数据的语句;NO SQL表示子程序中不包含SQL语句;READS SQL DATA表示子程序中包含读数据的语句;MODIFIES SQL DATA表示子程序中包含写数据的语句。默认情况下,系统会指定为CONTAINS SQL;SQL SECURITY { DEFINER | INVOKER };指明谁有权限来执行。DEFINER表示只有定义者自己才能够执行;INVOKER表示调用者可以执行。默认情况下,系统指定的权限是DEFINER。COMMENT ‘string’:注释信息;

DELIMITER &&

CREATE PROCEDURE pro_book ( IN bT INT,OUT count_num INT)

  READS SQL DATA

  BEGIN

    SELECT COUNT(*) FROM t_book WHERE bookTypeId=bT;

  END

  &&

DELIMITER ;

CALL pro_book(1,@total);

创建存储函数

CREATE FUNCTION sp_name ( [func_parameter[,...]] )

RETURNS type

[ characteristic... ] routine_body

sp_name参数是存储函数的名称;func_parameter表示存储函数的参数列表;RETURNS type指定返回值的类型;characteristic参数指定存储过程的特性,该参数的取值与存储过程中的取值是一样的;routine_body参数是SQL代码的内容,可以用BEGIN...END来标志SQL代码的开始和结束;func_parameter可以由多个参数组成,其中每个参数由参数名称和参数类型组成,其形式如下:param_name type其中,param_name参数是存储函数的参数名称;type参数指定存储函数的参数类型,该类型可以是MySQL数据库的任意数据类型;

DELIMITER &&

CREATE FUNCTION func_book (bookId INT)

RETURNS VARCHAR(20)

BEGIN

  RETURN ( SELECT bookName FROM t_book WHERE id=bookId );

END

    &&

DELIMITER ;

SELECT func_book(2);

变量

定义变量:DECLARE var_name [,...] type [ DEFAULT value ]

变量赋值:SET var_name = expr [,var_name=expr] ...

SELECT col_name[,...] INTO var_name[,...]

FROM table_name WHERE condition

游标

查询语句可能查询出多条记录,在存储过程和函数中使用游标来逐条读取查询结果集中的记录。游标的使用包括声明游标、打开游标、使用游标和关闭游标。游标必须声明在处理程序之前,并且声明在变量和条件之后。

声明游标:DECLARE cursor_name CURSOR FOR select_statement ;

打开游标:OPEN cursor_name;

使用游标:FETCH cursor_name INTO var_name [,var_name ... ];

关闭游标:CLOSE cursor_name;


DELIMITER &&

CREATE PROCEDURE pro_user()

BEGIN

DECLARE a,b VARCHAR(20) ;

INSERT INTO t_user VALUES(NULL,a,b);

END

&&

DELIMITER ;


DELIMITER &&

CREATE PROCEDURE pro_user2()

BEGIN

DECLARE a,b VARCHAR(20) ;

SET a='java1234',b='123456';

INSERT INTO t_user VALUES(NULL,a,b);

END

&&

DELIMITER ;


DELIMITER &&

CREATE PROCEDURE pro_user3()

BEGIN

DECLARE a,b VARCHAR(20) ;

SELECT userName2,password2 INTO a,b FROM t_user2 WHERE id2=1;

INSERT INTO t_user VALUES(NULL,a,b);

END

&&

DELIMITER ;


DELIMITER &&

CREATE PROCEDURE pro_user4()

BEGIN

DECLARE a,b VARCHAR(20) ;

DECLARE cur_t_user2 CURSOR FOR SELECT userName2,password2 FROM t_user2;

OPEN cur_t_user2;

FETCH cur_t_user2 INTO a,b;

INSERT INTO t_user VALUES(NULL,a,b);

CLOSE cur_t_user2;

END

&&

DELIMITER ;

查看存储过程和函数:

SHOW STATUS语句查看存储过程和函数的状态

SHOW CREATE语句查看存储过程的函数的定义

SHOW PROCEDURE STATUS LIKE 'pro_book';

SHOW CREATE PROCEDURE pro_book;

修改存储过程和函数:

ALTER PROCEDURE pro_book  COMMENT '我来测试一个COMMENT';

删除存储过程和函数:

DROP PROCEDURE pro_user3;

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。