精华内容
下载资源
问答
  • MySQL存储过程

    2021-02-02 07:53:47
    简介存储过程就是一条或多条SQL语句的集合,当对数据库进行一系列复杂操作时,存储过程可以将这些复杂的操作封装程一个代码块,可以重复使用,大大减少数据库开发人员的工作量。存储过程的创建创建存储过程使用...

    简介

    存储过程就是一条或多条SQL语句的集合,当对数据库进行一系列复杂操作时,存储过程可以将这些复杂的操作封装程一个代码块,可以重复使用,大大减少数据库开发人员的工作量。

    存储过程的创建

    创建存储过程

    使用CREATE PROCEDURE语句创建存储过程,基本语法格式如下:

    CREATE PROCEDURE sp_name([proc_parameter])[characteristics...]routine_body

    上述语法格式中CREATE PROCEDURE:为用来创建存储过程的关键字;sp_name:存储过程的名称;proc_parameter:存储过程的参数列表,该参数列表形式如下:

    [IN|OUT|INOUT]param_name type

    上述参数列表形式中 IN:输入参数;OUT:输出参数;INOUT:即可以输入也可以输出;param_name:参数名称;type:参数类型,可以是MySQL数据库中的任意类型;

    在创建存储过程语法中,characteristics用于指定存储过程的特性,取值具体说明如下:

    LANGUAGE SQL:说明routine_body部分是由SQL语句组成的,当前系统支持的语言为SQL,SQL是LANGUAGE的唯一值。

    [NOT]DETERMINISTIC:指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时,相同的输入会得到相同的输出;NOT DETERMINISTIC表示结果是不确定的,相同的输入可能得到不同的输出。如果没有指定任意一个值,默认为NOT DE-TERMINISTIC。

    {CONTAINS SQL|NO SQL|READS SQLDATA|MODIFIES SQL DATA}:指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句,但是不包含读写数据的语句;NOSQL表明子程序不包含SQL语句;READS SQLDATA说明子程序包含读写数据的语句;MODI-FIES SQL DATA表明子程序包含写数据的语句。默认情况下,系统会指定为CONTAINS SQL。

    SQL SECURITY{DEFINER|INVOKER}:指明谁有权限来执行。DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下,系统指定为DEFINER。

    COMMENT‘string’:注释信息,可以用来描述存储过程。

    routime_body是SQL代码的内容,可以用BEGIN…END来表示SQL代码的开始和结束。

    创建存储过程案例:

    mysql> DELIMITER //mysql> CREATE PROCEDURE Proc()-> BEGIN

    -> SELECT * FROMtable_name;-> END //Query OK,0 rows affected (0.00sec)

    mysql> DELIMITER ;

    在上述执行过程中,“DELIMITER //”语句的作用是将MySQL的结束符设置为//,因为MySQL默认的语句结束符号为分号“;”,为了避免与存储过程中SQL语句结束符相冲突,需要使用DELIMITER改变存储过程的结束符,并以“END //”结束存储过程。存储过程定义完毕后再使用“DELIMITER ;”恢复默认结束符。DELIM-ITER也可以指定其他符号作为结束符。需要格外注意的是,DELIMITER与要设定的结束符之间一定要有一个空格,否则设定无效。

    变量的使用

    在编写存储过程时,有时会需要使用变量保存数据处理过程中的值。在MySQL中,变量可以在子程序中声明并使用,这些变量的作用范围是在BEGIN…END程序中。

    在存储过程中使用DECLARE语句定义变量,具体语法格式如下:

    DECLARE var_name[,varname]…date_type[DEFAULT value];

    上述语法格式中var_name:局部变量的名称;DEFAULT value子句给变量提供一个默认值。该值除了可以被声明为一个常数之外,还可以被指定为一个表达式。如果没有DEFAULT子句,变量的初始值为NULL。

    接下来定义一个名称为myvariable的变量,类型为INT类型,默认值为100,示例代码如下:

    DECLARE myvariable INT DEFAULT 100;

    使用SET语句为变量赋值,可以改变变量的默认值,语法格式如下:

    SET var_name = expr[,var_name = expr]…;

    在存储过程中的SET语句是一般SET语句的扩展版本。被参考变量可能是子程序内声明的变量,或者是全局服务器变量,如系统变量或者用户变量。

    在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x,b=y,…这样的扩展语法。其中不同的变量类型(局域声明变量及全局变量)可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。

    接下来声明三个变量,分别为var1、var2、var3,数据类型为INT,使用SET为变量赋值,示例代码如下:

    DECLARE var1,var2,var3 INT;SET var1=10,var2=20;SET var3=var1+var2;SET @x=var1+var2;

    注意:“@x”表示用户变量,使用SET语句为其赋值,用户变量与连接有关,一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时,该客户连接的所有变量将自动释放。

    除了可以使用SET语句为变量赋值外,MySQL中还可以通过SELECT…INTO为一个或多个变量赋值,该语句可以把选定的列直接存储到对应位置的变量。使用SELECT…INTO的具体语法格式如下:

    SELECT col_name[…] INTO var_name[…] table_expr;

    在上述语法格式中,col_name表示字段名称;var_name表示定义的变量名称;table_expr表示查询条件表达式,包括表名称和WHERE子句。

    例:声明变量s_grade和s_gender,通过SELECT…INTO语句查询指定记录并为变量赋值,具体代码如下:

    DECLARE s_grade FLOAT;DECLARE s_gender CHAR(2);SELECT grade, gender INTOs_grade, s_genderFROM student WHERE name = 'rose';

    定义条件和处理程序

    定义条件是事先定义程序执行过程中遇到的问题,处理程序定义了在遇到这些问题时应当采取的处理方式,并且保证存储过程在遇到警告或错误时能继续执行。

    定义条件

    在编写存储过程时,定义条件使用DECLARE语句,语法格式如下:

    DECLARE condition_name CONDITION FOR [condition_type];

    // condition_type的两种形式: [condition_type]:SQLSTATE[VALUE] sqlstate_value|mysql_error_code

    上述语法格式中,condition_name表示所定义的条件的名称;condition_type表示条件的类型;sqlstate_value和mysql_error_code都可以表示MySQL的错误,sqlstate_value是长度为5的字符串类型错误代码,mysql_error_code为数值类型的错误代码。例如:ERROR1142(42000)中,sql-state_value的值是42000,mysql_error_code的值是1142。

    上述语法格式指定了需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HAN-DLER语句中。

    例:定义“ERROR1148(42000)”错误,名称为command_not_allowed的两种方式:

    //方法一:使用sqlstate_valueDECLARE command_not_allowed CONDITION FOR SQLSTATE'42000';//方法二:使用mysql_error_codeDECLARE command_not_allowed CONDITION FOR 1148;

    定义处理程序

    定义完条件后,还需要定义针对此条件的处理程序。MySQL中用DECLARE语句定义处理程序,具体语法格式如下:

    DECLARE handler_type HANDLER FOR condition_value[,…]sp_statement//handler_type: CONTINUE|EXIT|UNDO//condition_value: |condition_name |SQLWARNING |NOT FOUND |SQLEXCEPTION |mysql_error_code

    handler_type为错误处理方式,CONTINUE表示遇到错误不处理,继续执行;EXIT表示遇到错误马上退出;UNDO表示遇到错误后撤回之前的操作,MySQL中暂时不支持这样的操作。

    sp_statement表示在遇到定义的错误时,需要执行的存储过程;

    condition_value为错误类型,取值:

    (1)SQLSTATE[VALUE] sqlstate_value包含5个字符的字符串错误值。

    (2)condition_name表示DECLARE CON-DITION定义的错误条件名称。

    (3)SQLWARNING匹配所有以01开头的SQLSTATE错误代码。

    (4)NOT FOUND匹配所有以02开头的SQLSTATE错误代码。

    (5)SQLEXCEPTION匹配所有没有被SQL-WARNING或NOT FOUND捕获的SQLSTATE错误代码。

    (6)mysql_error_code匹配数值类型错误代码。

    例:定义处理程序几种方式:

    //方法一:捕获sqlstate_valueDECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'SET @info='NO_SUCH_TABLE';//方法二:捕获mysql_error_codeDECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE';//方法三:先定义条件,然后调用DECLARE no_such_table CONDITION FOR 1146;DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='ERROR';//方法四:使用SQLWARNING SQL-WARNING捕获所有以01开头的sqlstate_value值,然后执行EXIT操作,并且输出“ERROR”信息。DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';//方法五:使用NOT FOUND NOTFOUND捕获所有以02开头的sqlstate_value值,然后执行EXIT操作,并且输出“NO_SUCH_TA-BLE”信息。DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';//方法六:使用SQLEXCEPTION SQLEXCEPTION捕获所有没有被SQLWARN-ING或NOT FOUND捕获的sqlstate_value值,然后执行EXIT操作,并且输出“ERROR”信息DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

    光标的使用

    在编写存储过程时,查询语句可能会返回多条记录,如果数据量非常大,则需要使用光标来逐条读取查询结果集中的记录。光标是一种用于轻松处理多行数据的机制。

    光标的声明

    光标必须声明在声明变量、条件之后,声明处理程序之前。MySQL中使用DECLARE关键字来声明光标,具体语法格式如下:

    DECLARE cursor_name CURSOR FORselect_statement;

    cursor_name:光标名称;

    select_statement:select语句,用于创建光标的结果集;

    例:声明名为cursor_student的光标

    DECLARE cursor_student CURSOR FOR select s_name,s_gender FROM student;

    光标的使用

    声明完光标就可以使用了,使用前首先要大开光标;MySQL中打开和使用光标,语法格式如下:

    OPENcursor_nameFETCH cursor_name INTO var_name[,var_name]…

    cursor_name:光标名称;

    var_name:将光标中的SELECT语句查询出来的信息存入该参数中,需要注意的是,var_name必须在声明光标之前就定义好。

    例:使用名称为cursor_student的光标。将查询出来的信息存入s_name和s_gender中

    FETCH cursor_student INTO s_name, s_gender;

    光标的关闭

    使用完光标要将光标关闭;语法格式如下:

    CLOSE cursor_name

    值得一提的是,如果没有明确地关闭光标,它会在其声明的复合语句的末尾被关闭。

    流程控制的使用

    在编写存储过程时还有一个非常重要的部分——流程控制。MySQL中的流程控制语句包括:IF语句、CASE语句、LOOP语句、WHILE语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句。

    每个流程中可能包含一个单独语句,也可能是使用BEGIN…END构造的复合语句,可以嵌套。

    IF语句

    IF实现了一个基本的条件构造。用法:

    IF val IS NULL

    THEN SELECT 'val is NULL';ELSE SELECT 'val is not NULL';END IF;

    需要注意的是,MySQL中还有一个IF()函数,它不同于这里描述的IF语句。IF语句都需要使用END IF来结束,不可省略。

    CASE语句

    CASE是另一种条件判断的语句,该语句有两种格式,第一种如下:

    CASEcase_exprWHEN when_value THENstatement_list[WHEN when_value THEN statement_list]…[ELSE statement_list]

    END CASE

    例:

    CASEvalWHEN 1 THEN SELECT 'val is 1';WHEN 2 THEN SELECT 'val is 2';ELSE SELECT 'val is not 1 or 2';END CASE;

    第二种如下:

    CASE

    WHEN expr_condition THENstatement_list[WHEN expr_condition THEN statement_list]

    [ELSE statement_list]

    END CASE;

    需要注意的是,这里说的用在存储过程里的CASE语句与“控制流程函数”里描述的SQL CASE表达式中的CASE语句有些不同。存储过程里的CASE语句不能有ELSE NULL子句,并且用ENDCASE替代END来终止。

    LOOP语句

    LOOP循环语句是用来重复执行某些语句,直到跳出循环语句。语法格式如下:

    [loop_label:]LOOP

    statement_listEND LOOP [loop_label]

    loop_label:指LOOP语句的标注名称,可省略。

    statement_list:需要循环执行的语句。

    例:使用LOOP语句进行循环操作

    DECLARE id INT DEFAULT 0;

    add_loop:LOOPSET id=id+1;IF id>=10 THENLEAVE add_loop;END IF;END LOOP add_loop;

    当id值小于10时,循环重复执行;当id值大于或者等于10时,使用LEAVE语句退出循环。

    LEAVE语句

    LEAVE语句用于退出任何被标注的流程控制构造,如LOOP例子中,当不满足循环条件时,需要使用LEAVE语句退出循环。基本语法如下:

    LEAVE label

    label表示循环的标志。通常LEAVE语句与BEGIN。。。END、循环语句一起使用。

    ITERATE语句

    ITERATE即再次循环,用于将执行顺序转到语句段的开头处。基本语法如下:

    ITERATE lable

    lable表示循环的标志。需要注意的是,ITERATE 语句只能出现在LOOP、REPEAT和WHILE语句内。

    例:演示了ITERATE语句在LOOP语句内的使用

    CREATE PROCEDUREdoiterate()BEGIN

    DECLARE p1 INT DEFAULT 0;

    my_loop:LOOPSET p1=p1+1;IF p1<10 THENITERATE my_loop;

    ELSEIF p1>20 THENLEAVE my_loop;END IF;SELECT 'p1 is between 10 and 20';ENDLOOP my_loop;END

    REPEAT语句

    REPEAT语句用于创建一个带有条件判断的循环过程,每次执行后,会对条件进行判断,如果为真,则循环结束;否则重复执行循环中的语句。基本语法如下:

    [repeat_lable:]REPEAT

    statement_list

    UNTIL expr_conditionEND REPEAT[repeat_lable]

    repeat_lable指REPEAT语句的标注名称(可选);REPEAT语句内的语句或语句群被重复,直至expr_condition为真。

    例:演示使用REPEAT语句执行循环过程

    DECLARE id INT DEFAULT 0;

    REPEATSET id=id+1;

    UNTIL id>=10;END REPEAT;

    WHILE语句

    WHILE语句用于创建一个带有条件判断的循环过程,与REPEAT不同的是在语句执行时,先对指定的表达式进行判断,如果为真,则执行循环内的语句,否则退出循环。基本语法如下:

    [while_lable:] WHILEexpr_condition DO

    Statement_listEND WHILE [while_lable]

    while_lable指WHILE语句的标注名称;

    expr_condition为进行判断的表达式,如果为真,WHILE语句内的语句或语句群被执行,直至expr_condition为假,退出循环。

    例:示使用WHILE语句进行循环操作

    DECLARE i INT DEFAULT 0;WHILE i<10DOSET i=i+1;END WHILE;

    存储过程的使用

    调用存储过程

    存储过程必须用CALL语句调用,如果调用其他数据库的存储过程,需要指定数据库名。调用语法格式如下:

    CALL sp_name([parameter[,…]])

    其中,sp_name:存储过程的名称;parameter:存储过程的参数;

    例:定义一个名为CountProc1的存储过程,然后调用这个存储过程,具体操作如下:

    定义存储过程:

    mysql> DELIMITER //mysql> CREATE PROCEDURE CountProc1(IN s_gender VARCHAR(50),OUT num INT)-> BEGIN

    -> SELECT COUNT(*) INTO num FROM student WHERE gender=s_gender;-> END//Query OK,0 rows affected (0.13sec)

    mysql> DELIMITER;

    调用存储过程:

    mysql> CALL CountProc1("女",@num);

    Query OK,1 row affected (0.17 sec)

    查看返回结果

    mysql> SELECT @num;+------+

    | @num |

    +------+

    | 2 |

    +------+

    1 row in set (0.00 sec)

    查看存储过程

    SHOW STATUS语句

    基本语法结构:

    SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern'\]

    上述语法格式中,PROCEDURE和FUNCTION分别表示查看存储过程和函数,LIKE语句表示匹配的名称。

    示例:获取数据库中所有名称以C开头的存储过程的信息。

    SHOW PROCEDURE STATUS LIKE'C%'\G

    SHOW CREATE语句

    基本语法结构:

    SHOW CREATE{PROCEDURE|FUNCTION} sp_name

    示例:

    SHOW CREATE PROCEDURE chapter06.CountProc1\G

    从information_schema.Routines表中查看存储过程的信息

    在MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程的信息,查询语句如下:

    SELECT * FROM information_schema.RoutinesWHERE ROUTINE_NAME='CountProc1' AND ROUTINE_TYPE='PROCEDURE'\G

    需要注意的是,ROUTINE_NAME:指定存储过程的名称;ROUTINE_TYPE:指定存储程序的类型。

    修改存储过程

    使用ALTER语句修改存储过程的特性,其基本语法格式如下:

    ALTER {PROCEDURE|FUNCTION} sp_name[characteristic…]

    上述语法格式中,sp_name:存储过程或函数的名称;characteristic表示要修改存储过程的哪个部分,characteristic的取值具体如下:

    CONTAINS SQL表示子程序包含SQL语句,但不包含读或写数据的语句;

    NO SQL表示子程序中不包含SQL语句;

    READS SQL DATA表示子程序中包含读数据的语句;

    MODIFIES SQL DATA表示子程序中包含写数据的语句;

    SQL SECURITY{DEFINER|INVOKER}指明谁有权限来执行;(DEFINER:只有定义者自己才能够执行;INVOKER:调用者可以执行)

    COMMENT‘string’表示注释信息。

    目前,MySQL还不提供对已存在的存储过程代码的修改,如果一定要修改存储过程代码,必须先将存储过程删除之后,再重新编写代码,或创建一个新的存储过程。

    删除存储过程

    使用DROP语句删除存储过程,其基本语法格式如下:

    DROP{ PROCEDURE|FUNCTION }[IF EXISTS] sp_name

    示例:删除存储过程CountProc1

    DROP PROCEDURE CountProc1;

    综合案例-存储过程应用

    创建一个stu表

    表结构:

    b456b1210f19fd8677ab7098c4beb2ce.png

    表数据:

    77b96c9a8b32b1864b39ffff3e770902.png

    建表及初始化数据脚本:

    CREATE TABLE stu(id INT,name VARCHAR(50),class VARCHAR(50));INSERT INTO stu VALUE (1,'Lucy','class1'),(2,'Tom','class1'),(3,'Rose','class2');

    创建一个存储过程

    创建一个存储过程addcount能够获取表stu中的记录数和id的和,代码格式如下:

    CREATE PROCEDURE addcount(out count INT)BEGIN

    DECLARE itmp INT;DECLARE cur_id CURSOR FOR SELECT id FROMstu;DECLARE EXIT HANDLER FOR NOT FOUND CLOSEcur_id;SELECT count(*) INTO count FROMstu;SET @sum=0;OPENcur_id;

    REPEATFETCH cur_id INTOitmp;IF itmp<10

    THEN SET @sum=@sum+itmp;END IF;

    UNTIL0 ENDREPEAT;CLOSEcur_id;END;

    上述存储过程用到了变量的声明、光标、流程控制。SQL语句执行情况如下:

    mysql> DELIMITER //mysql> CREATE PROCEDURE addcount(out count INT)-> BEGIN

    -> DECLARE itmp INT;-> DECLARE cur_id CURSOR FOR SELECT id FROMstu;-> DECLARE EXIT HANDLER FOR NOTFOUNDCLOSEcur_id;-> SELECT count(*) INTO count FROMstu;-> SET @sum=0;-> OPENcur_id;->REPEAT-> FETCH cur_id INTOitmp;-> IF itmp<10

    -> THEN SET @sum=@sum+itmp;-> END IF;-> UNTIL 0 ENDREPEAT;-> CLOSEcur_id;-> END //Query OK,0 rows affected (0.00sec)

    mysql> CALL addcount(@count) //Query OK,0 rows affected (0.00sec)

    mysql> SELECT @count,@sum //

    +--------+------+

    | @count | @sum |

    +--------+------+

    | 3 | 6 |

    +--------+------+

    1 row in set (0.00sec)

    mysql> DELIMITER;

    从调用存储过程的结果可以看出,stu表中共有三条数据,id之和为6。这个存储过程创建了一个cur_id的光标,使用这个光标来获取每条记录的id,使用REPEAT循环语句来实现所有id号相加。

    展开全文
  • MySQL存储过程解析

    2021-01-19 12:15:16
    1.1 创建存储过程MySQL中,创建存储过程的基本形式如下:CREATE PROCEDURE sp_name ([proc_parameter[,...]])[characteristic ...] routine_body其中,sp_name参数是存储过程的名称;proc_parameter表示存储过程的...

    1.1 创建存储过程

    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':注释信息。

    技巧:创建存储过程时,系统默认指定CONTAINS

    SQL,表示存储过程中使用了SQL语句。但是,如果存储过程中没有使用SQL语句,最好设置为NO

    SQL。而且,存储过程中最好在COMMENT部分对存储过程进行简单的注释,以便以后在阅读存储过程的代码时更加方便。

    【示例】 下面创建一个名为num_from_employee的存储过程。代码如下:

    CREATE  PROCEDURE  num_from_employee (IN emp_id INT, OUT count_num INT )

    READS SQL DATA

    BEGIN

    SELECT  COUNT(*)  INTO  count_num

    FROM  employee

    WHERE  d_id=emp_id ;

    END

    上述代码中,存储过程名称为num_from_employee;输入变量为emp_id;输出变量为count_num。SELECT语句从

    employee表查询d_id值等于emp_id的记录,并用COUNT(*)计算d_id值相同的记录的条数,最后将计算结果存入count_num

    中。代码的执行结果如下:

    mysql> DELIMITER &&

    mysql> CREATE  PROCEDURE  num_from_employee

    (IN emp_id INT, OUT count_num INT )

    -> READS SQL DATA

    -> BEGIN

    -> SELECT  COUNT(*)  INTO  count_num

    -> FROM  employee

    -> WHERE  d_id=emp_id ;

    -> END &&

    Query OK, 0 rows affected (0.09 sec)

    mysql> DELIMITER ;

    代码执行完毕后,没有报出任何出错信息就表示存储函数已经创建成功。以后就可以调用这个存储过程,数据库中会执行存储过程中的SQL语句。

    说明:MySQL中默认的语句结束符为分号(;)。存储过程中的SQL语句需要分号来

    结束。为了避免冲突,首先用"DELIMITER

    &&"将MySQL的结束符设置为&&。最后再用"DELIMITER

    ;"来将结束符恢复成分号。这与创建触发器时是一样的。

    1.2 创建存储函数

    在MySQL中,创建存储函数的基本形式如下:

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

    RETURNS type

    [characteristic ...] routine_body

    其中,sp_name参数是存储函数的名称;func_parameter表示存储函数的参数列表;RETURNS

    type指定返回值的类型;characteristic参数指定存储函数的特性,该参数的取值与存储过程中的取值是一样的,请读者参照14.1.1小节

    的内容;routine_body参数是SQL代码的内容,可以用BEGIN…END来标志SQL代码的开始和结束。

    func_parameter可以由多个参数组成,其中每个参数由参数名称和参数类型组成,其形式如下:

    param_name type

    其中,param_name参数是存储函数的参数名称;type参数指定存储函数的参数类型,该类型可以是MySQL数据库的任意数据类型。

    【示例】 下面创建一个名为name_from_employee的存储函数。代码如下:

    CREATE  FUNCTION  name_from_employee (emp_id INT )

    RETURNS VARCHAR(20)

    BEGIN

    RETURN  (SELECT  name

    FROM  employee

    WHERE  num=emp_id );

    END

    上述代码中,存储函数的名称为name_from_employee;该函数的参数为emp_id;返回值是VARCHAR类型。SELECT语句从employee表查询num值等于emp_id的记录,并将该记录的name字段的值返回。代码的执行结果如下:

    mysql> DELIMITER &&

    mysql> CREATE  FUNCTION  name_from_employee (emp_id INT )

    -> RETURNS VARCHAR(20)

    -> BEGIN

    -> RETURN  (SELECT  name

    -> FROM  employee

    -> WHERE  num=emp_id );

    -> END&&

    Query OK, 0 rows affected (0.00 sec)

    mysql> DELIMITER ;

    结果显示,存储函数已经创建成功。该函数的使用和MySQL内部函数的使用方法一样。

    1.3  变量的使用

    在存储过程和函数中,可以定义和使用变量。用户可以使用DECLARE关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是BEGIN…END程序段中。本小节将讲解如何定义变量和为变量赋值。

    1.定义变量

    MySQL中可以使用DECLARE关键字来定义变量。定义变量的基本语法如下:

    DECLARE  var_name[,...]  type  [DEFAULT value]

    其中, DECLARE关键字是用来声明变量的;var_name参数是变量的名称,这里可以同时定义多个变量;type参数用来指定变量的类型;DEFAULT value子句将变量默认值设置为value,没有使用DEFAULT子句时,默认值为NULL。

    【示例】 下面定义变量my_sql,数据类型为INT型,默认值为10。代码如下:

    DECLARE  my_sql  INT  DEFAULT 10 ;

    2.为变量赋值

    MySQL中可以使用SET关键字来为变量赋值。SET语句的基本语法如下:

    SET  var_name = expr [, var_name = expr] ...

    其中,SET关键字是用来为变量赋值的;var_name参数是变量的名称;expr参数是赋值表达式。一个SET语句可以同时为多个变量赋值,各个变量的赋值语句之间用逗号隔开。

    【示例】 下面为变量my_sql赋值为30。代码如下:

    SET  my_sql = 30 ;

    MySQL中还可以使用SELECT…INTO语句为变量赋值。其基本语法如下:

    SELECT  col_name[,…]  INTO  var_name[,…]

    FROM  table_name  WEHRE  condition

    其中,col_name参数表示查询的字段名称;var_name参数是变量的名称;table_name参数指表的名称;condition参数指查询条件。

    【示例】 下面从employee表中查询id为2的记录,将该记录的d_id值赋给变量my_sql。代码如下:

    SELECT  d_id  INTO  my_sql

    FROM  employee  WEHRE  id=2 ;

    1.4  定义条件和处理程序定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题,并提出

    解决办法。这样可以增强程序处理问题的能力,避免程序异常停止。MySQL中都是通过DECLARE关键字来定义条件和处理程序。本小节中将详细讲解如何

    定义条件和处理程序。

    1.定义条件

    MySQL中可以使用DECLARE关键字来定义条件。其基本语法如下:

    DECLARE  condition_name  CONDITION  FOR  condition_value

    condition_value:

    SQLSTATE [VALUE] sqlstate_value | mysql_error_code

    其中,condition_name参数表示条件的名称;condition_value参数表示条件的类型;sqlstate_value参数和

    mysql_error_code参数都可以表示MySQL的错误。例如ERROR 1146

    (42S02)中,sqlstate_value值是42S02,mysql_error_code值是1146。

    【示例】 下面定义"ERROR 1146 (42S02)"这个错误,名称为can_not_find。可以用两种不同的方法来定义,代码如下:

    //方法一:使用sqlstate_value

    DECLARE  can_not_find  CONDITION  FOR  SQLSTATE  '42S02' ;

    //方法二:使用mysql_error_code

    DECLARE  can_not_find  CONDITION  FOR  1146 ;

    2.定义处理程序

    MySQL中可以使用DECLARE关键字来定义处理程序。其基本语法如下:

    DECLARE handler_type HANDLER FOR

    condition_value[,...] sp_statement

    handler_type:

    CONTINUE | EXIT | UNDO

    condition_value:

    SQLSTATE [VALUE] sqlstate_value |

    condition_name  | SQLWARNING

    | NOT FOUND  | SQLEXCEPTION  | mysql_error_code

    其中,handler_type参数指明错误的处理方式,该参数有3个取值。这3个取值分别是CONTINUE、EXIT和UNDO。CONTINUE表

    示遇到错误不进行处理,继续向下执行;EXIT表示遇到错误后马上退出;UNDO表示遇到错误后撤回之前的操作,MySQL中暂时还不支持这种处理方式。

    注意:通常情况下,执行过程中遇到错误应该立刻停止执行下面的语句,并且撤回前面的操作。但是,MySQL中现在还不能支持UNDO操作。因此,遇到错误时最好执行EXIT操作。如果事先能够预测错误类型,并且进行相应的处理,那么可以执行CONTINUE操作。

    condition_value参数指明错误类型,该参数有6个取值。sqlstate_value和mysql_error_code与条件定义中的是

    同一个意思。condition_name是DECLARE定义的条件名称。SQLWARNING表示所有以01开头的sqlstate_value值。

    NOT FOUND表示所有以02开头的sqlstate_value值。SQLEXCEPTION表示所有没有被SQLWARNING或NOT

    FOUND捕获的sqlstate_value值。sp_statement表示一些存储过程或函数的执行语句。

    【示例】 下面是定义处理程序的几种方式。代码如下:

    //方法一:捕获sqlstate_value

    DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'

    SET @info='CAN NOT FIND';

    //方法二:捕获mysql_error_code

    DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';

    //方法三:先定义条件,然后调用

    DECLARE  can_not_find  CONDITION  FOR  1146 ;

    DECLARE CONTINUE HANDLER FOR can_not_find SET

    @info='CAN NOT FIND';

    //方法四:使用SQLWARNING

    DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';

    //方法五:使用NOT FOUND

    DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';

    //方法六:使用SQLEXCEPTION

    DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

    上述代码是6种定义处理程序的方法。第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42S02,执行

    CONTINUE操作,并且输出"CAN NOT

    FIND"信息。第二种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146,执行CONTINUE操

    作,并且输出"CAN NOT

    FIND"信息。第三种方法是先定义条件,然后再调用条件。这里先定义can_not_find条件,遇到1146错误就执行CONTINUE操作。第四

    种方法是使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值,然后执行EXIT操作,并且输

    出"ERROR"信息。第五种方法是使用NOT

    FOUND。NOT FOUND捕获所有以02开头的sqlstate_value值,然后执行EXIT操作,并且输出"CAN NOT

    FIND"信息。第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或NOT

    FOUND捕获的sqlstate_value值,然后执行EXIT操作,并且输出"ERROR"信息。

    展开全文
  • mysql 存储过程详解

    2021-01-18 20:59:51
    MySQL存储过程14.1.1 创建存储过程MySQL中,创建存储过程的基本形式如下:CREATE PROCEDURE sp_name ([proc_parameter[,...]])[characteristic ...] routine_body其中,sp_name参数是存储过程的名称;proc_parameter...

    MySQL存储过程

    14.1.1 创建存储过程

    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':注释信息。

    技巧:创建存储过程时,系统默认指定CONTAINS SQL,表示存储过程中使用了SQL语句。但是,如果存储过程中没有使用SQL语句,最好设置为NO SQL。而且,存储过程中最好在COMMENT部分对存储过程进行简单的注释,以便以后在阅读存储过程的代码时更加方便。

    【示例14-1】 下面创建一个名为num_from_employee的存储过程。代码如下:

    CREATE PROCEDURE num_from_employee (IN emp_id INT, OUT count_num INT )

    READS SQL DATA

    BEGIN

    SELECT COUNT(*) INTO count_num

    FROM employee

    WHERE d_id=emp_id ;

    END

    上述代码中,存储过程名称为num_from_employee;输入变量为emp_id;输出变量为count_num。SELECT语句从employee表查询d_id值等于emp_id的记录,并用COUNT(*)计算d_id值相同的记录的条数,最后将计算结果存入count_num中。代码的执行结果如下:

    mysql> DELIMITER &&

    mysql> CREATE PROCEDURE num_from_employee

    (IN emp_id INT, OUT count_num INT )

    -> READS SQL DATA

    -> BEGIN

    -> SELECT COUNT(*) INTO count_num

    -> FROM employee

    -> WHERE d_id=emp_id ;

    -> END &&

    Query OK, 0 rows affected (0.09 sec)

    mysql> DELIMITER ;

    代码执行完毕后,没有报出任何出错信息就表示存储函数已经创建成功。以后就可以调用这个存储过程,数据库中会执行存储过程中的SQL语句。

    说明:MySQL中默认的语句结束符为分号(;)。存储过程中的SQL语句需要分号来    结束。为了避免冲突,首先用"DELIMITER &&"将MySQL的结束符设置为&&。最后再用"DELIMITER ;"来将结束符恢复成分号。这与创建触发器时是一样的。

    14.1.2 创建存储函数

    在MySQL中,创建存储函数的基本形式如下:

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

    RETURNS type

    [characteristic ...] routine_body

    其中,sp_name参数是存储函数的名称;func_parameter表示存储函数的参数列表;RETURNS type指定返回值的类型;characteristic参数指定存储函数的特性,该参数的取值与存储过程中的取值是一样的,请读者参照14.1.1小节的内容;routine_body参数是SQL代码的内容,可以用BEGIN…END来标志SQL代码的开始和结束。

    func_parameter可以由多个参数组成,其中每个参数由参数名称和参数类型组成,其形式如下:

    param_name type

    其中,param_name参数是存储函数的参数名称;type参数指定存储函数的参数类型,该类型可以是MySQL数据库的任意数据类型。

    【示例14-2】 下面创建一个名为name_from_employee的存储函数。代码如下:

    CREATE FUNCTION name_from_employee (emp_id INT )

    RETURNS VARCHAR(20)

    BEGIN

    RETURN (SELECT name

    FROM employee

    WHERE num=emp_id );

    END

    上述代码中,存储函数的名称为name_from_employee;该函数的参数为emp_id;返回值是VARCHAR类型。SELECT语句从employee表查询num值等于emp_id的记录,并将该记录的name字段的值返回。代码的执行结果如下:

    mysql> DELIMITER &&

    mysql> CREATE FUNCTION name_from_employee (emp_id INT )

    -> RETURNS VARCHAR(20)

    -> BEGIN

    -> RETURN (SELECT name

    -> FROM employee

    -> WHERE num=emp_id );

    -> END&&

    Query OK, 0 rows affected (0.00 sec)

    mysql> DELIMITER ;

    结果显示,存储函数已经创建成功。该函数的使用和MySQL内部函数的使用方法一样。

    14.1.3  变量的使用

    在存储过程和函数中,可以定义和使用变量。用户可以使用DECLARE关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是BEGIN…END程序段中。本小节将讲解如何定义变量和为变量赋值。

    1.定义变量

    MySQL中可以使用DECLARE关键字来定义变量。定义变量的基本语法如下:

    DECLARE  var_name[,...]  type  [DEFAULT value]

    其中, DECLARE关键字是用来声明变量的;var_name参数是变量的名称,这里可以同时定义多个变量;type参数用来指定变量的类型;DEFAULT value子句将变量默认值设置为value,没有使用DEFAULT子句时,默认值为NULL。

    【示例14-3】 下面定义变量my_sql,数据类型为INT型,默认值为10。代码如下:

    DECLARE  my_sql  INT  DEFAULT 10 ;

    2.为变量赋值

    MySQL中可以使用SET关键字来为变量赋值。SET语句的基本语法如下:

    SET  var_name = expr [, var_name = expr] ...

    其中,SET关键字是用来为变量赋值的;var_name参数是变量的名称;expr参数是赋值表达式。一个SET语句可以同时为多个变量赋值,各个变量的赋值语句之间用逗号隔开。

    【示例14-4】 下面为变量my_sql赋值为30。代码如下:

    SET  my_sql = 30 ;

    MySQL中还可以使用SELECT…INTO语句为变量赋值。其基本语法如下:

    SELECT  col_name[,…]  INTO  var_name[,…]

    FROM  table_name  WEHRE  condition

    其中,col_name参数表示查询的字段名称;var_name参数是变量的名称;table_name参数指表的名称;condition参数指查询条件。

    【示例14-5】 下面从employee表中查询id为2的记录,将该记录的d_id值赋给变量my_sql。代码如下:

    SELECT  d_id  INTO  my_sql

    FROM  employee  WEHRE  id=2 ;

    14.1.4  定义条件和处理程序

    定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题,并提出解决办法。这样可以增强程序处理问题的能力,避免程序异常停止。MySQL中都是通过DECLARE关键字来定义条件和处理程序。本小节中将详细讲解如何定义条件和处理程序。

    1.定义条件

    MySQL中可以使用DECLARE关键字来定义条件。其基本语法如下:

    DECLARE condition_name CONDITION FOR condition_value

    condition_value:

    SQLSTATE [VALUE] sqlstate_value | mysql_error_code

    其中,condition_name参数表示条件的名称;condition_value参数表示条件的类型;sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。例如ERROR 1146 (42S02)中,sqlstate_value值是42S02,mysql_error_code值是1146。

    【示例14-6】 下面定义"ERROR 1146 (42S02)"这个错误,名称为can_not_find。可以用两种不同的方法来定义,代码如下:

    //方法一:使用sqlstate_value

    DECLARE can_not_find CONDITION FOR SQLSTATE '42S02' ;

    //方法二:使用mysql_error_code

    DECLARE can_not_find CONDITION FOR 1146 ;

    2.定义处理程序

    MySQL中可以使用DECLARE关键字来定义处理程序。其基本语法如下:

    DECLARE handler_type HANDLER FOR

    condition_value[,...] sp_statement

    handler_type:

    CONTINUE | EXIT | UNDO

    condition_value:

    SQLSTATE [VALUE] sqlstate_value |

    condition_name | SQLWARNING

    | NOT FOUND | SQLEXCEPTION | mysql_error_code

    其中,handler_type参数指明错误的处理方式,该参数有3个取值。这3个取值分别是CONTINUE、EXIT和UNDO。CONTINUE表示遇到错误不进行处理,继续向下执行;EXIT表示遇到错误后马上退出;UNDO表示遇到错误后撤回之前的操作,MySQL中暂时还不支持这种处理方式。

    注意:通常情况下,执行过程中遇到错误应该立刻停止执行下面的语句,并且撤回前面的操作。但是,MySQL中现在还不能支持UNDO操作。因此,遇到错误时最好执行EXIT操作。如果事先能够预测错误类型,并且进行相应的处理,那么可以执行CONTINUE操作。

    condition_value参数指明错误类型,该参数有6个取值。sqlstate_value和mysql_error_code与条件定义中的是同一个意思。condition_name是DECLARE定义的条件名称。SQLWARNING表示所有以01开头的sqlstate_value值。NOT FOUND表示所有以02开头的sqlstate_value值。SQLEXCEPTION表示所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值。sp_statement表示一些存储过程或函数的执行语句。

    【示例14-7】 下面是定义处理程序的几种方式。代码如下:

    //方法一:捕获sqlstate_value

    DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'

    SET @info='CAN NOT FIND';

    //方法二:捕获mysql_error_code

    DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';

    //方法三:先定义条件,然后调用

    DECLARE can_not_find CONDITION FOR 1146 ;

    DECLARE CONTINUE HANDLER FOR can_not_find SET

    @info='CAN NOT FIND';

    //方法四:使用SQLWARNING

    DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';

    //方法五:使用NOT FOUND

    DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';

    //方法六:使用SQLEXCEPTION

    DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

    上述代码是6种定义处理程序的方法。第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42S02,执行CONTINUE操作,并且输出"CAN NOT FIND"信息。第二种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146,执行CONTINUE操作,并且输出"CAN NOT FIND"信息。第三种方法是先定义条件,然后再调用条件。这里先定义can_not_find条件,遇到1146错误就执行CONTINUE操作。第四种方法是使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值,然后执行EXIT操作,并且输出"ERROR"信息。第五种方法是使用NOT FOUND。NOT FOUND捕获所有以02开头的sqlstate_value值,然后执行EXIT操作,并且输出"CAN NOT FIND"信息。第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值,然后执行EXIT操作,并且输出"ERROR"信息。

    感谢阅读此文,希望能帮助到大家,谢谢大家对本站的支持!

    展开全文
  • 1.存储过程(procedure)语法:create ...),但是默认情况下分号表示客户端代码发送到服务器执行。必须更改结束符通过delimiter指令来跟结束符delimiter // #将结束字符定义为//(原来是;)(1)创建存储过程#简单的c...

    1.存储过程(procedure)

    语法:

    create procedure 存储过程名(参数,…)

    begin

    //代码

    end//

    注意:存储过程中有很多的SQL语句,SQL语句的后面为了保证语法结构必须要有分号(;),但是默认情况下分号表示客户端代码发送到服务器执行。必须更改结束符

    通过delimiter指令来跟结束符

    delimiter // #将结束字符定义为//(原来是;)

    (1)创建存储过程

    #简单的

    create procedure pro_1()

    select * from stuinfo;

    //

    #如果存储过程中就一条SQL语句,begin…end两个关键字可以省略。

    #调用存储过程

    call pro_1()//

    #包涵多条sql语句的

    #in代表输入参数,可以省略

    create procedure pro_2(in param int)

    begin

    select * from stuinfo where sid=param;

    select * from stumarks where sid=param;

    end//

    #调用

    call pro_2(10)//

    (2)参数的类别

    在存储过程中,没有return,如果需要返回值,通过输出参数来实现

    在MySQL中,参数分为3类,输入参数(in),输出参数(out),输入输出参数(inout),默认情况下是是输入参数(in)

    (3)删除存储过程

    语法:drop procedure [if exists] 存储过程名

    drop procedure if exists pro_1//

    (4)查看存储过程的信息

    show create procedure pro_2\G

    (5)局部变量

    语法:declare 变量名 数据类型 [初始值]

    通过:select ...into…或set命令给变量赋值

    例题通过sid查询姓名和年龄

    create procedure pro_3(in id int)

    begin

    declare name varchar(10);

    declare sexx char(10);

    select sname,sex into name,sexx from stuinfo where sid=id;

    select name,sexx from dual;

    end //

    #调用pro_3

    call pro_3(10)//

    #注意:声明的变量名不能和列名(字段名)同名

    例题:查找同桌

    create procedure pro_4(in name varchar(32))

    begin

    declare stuseat tinyint;

    select seat into stuseat from stuinfo where sname=name;

    select * from stuinfo where seat=stuseat+1 or seat=stuseat-1;

    end //

    #调用

    call pro_4('百强')//

    #通过set给变量赋值

    create procedure pro_5(in num1 year,in num2 year,in name varchar(32))

    begin

    declare num int;

    set num=num2-num1; #得到年龄

    update stuinfo set age=num where sname=name;

    select * from stuinfo where sname = name;

    end//

    call pro_5(1991,2018,'小力')//

    (6)全局变量(用户变量)

    全局变量前面必须有一个@,全局变量的数据类型取决于变量的值。如果一个全局变量没有赋值,他的数据类型为null。

    set @name='百强'//

    select * from stuinfo where sname=@name//

    (7)系统变量

    通过两个@开头的都是系统变量

    select @@version from dual//

    系统命令

    作用

    @@version

    版本号

    current_date

    当前日期

    current_time

    当前时间

    current_timestamp

    当前日期和时间

    (8)带有输出参数的存储过程

    #带有out关键字的参数,在存储过程运行结束以后,默认返回

    create procedure pro_6(in num int,out result int)

    begin

    set result=num*num;

    end//

    #调用

    #@result 接受返回值

    call pro_6(6,@result)//

    select @result from dual//

    (9)带有输入输出参数的存储过程

    create procedure pro_7(inout num int)

    begin

    set num=num*num;

    end //

    #调用

    set @num=10//

    call pro_7(@num)//

    select @num from dual//

    2.SQL编程(了解)

    (1) if-elseif-else语句

    #语法:

    if 条件 then

    //代码1

    elseif 条件 then

    //代码2

    else

    //代码3

    end if;

    create procedure pro_8(in grade int)

    begin

    if grade=1 then

    select '金牌会员' as '等级';

    elseif grade=2 then

    select '普通会员' as '等级';

    else

    select '游客' as '等级';

    end if;

    end //

    #调用

    call pro_8(3)//

    (2) case-when语句

    create procedure pro_9(in num int)

    begin

    case num

    when 1 then select '杀马特' as '气质';

    when 2 then select '屌丝' as '气质';

    when 3 then select '正常人' as '气质';

    when 4 then select '贵族' as '气质';

    else select '输入不正确' as '气质';

    end case;

    end //

    call pro_9(0)//

    #显示学员的学号、姓名、性别、语文成绩、等级

    select sid,sname,sex,ch,case

    when ch>=90 then '等级A'

    when ch>=80 then '等级B'

    when ch>=70 then '等级C'

    when ch>=60 then '等级D'

    else '等级E'

    end as '等级' from stuinfo left join stumarks using(sid)//

    select sid,sname,sex,ch from stuinfo left join stumarks using(sid)//

    (3)loop循环

    loop遇到leave退出

    create procedure proc(in num int)

    begin

    declare total int default 0;

    declare i int default 0;

    sign:loop

    set total=total+i;

    set i=i+1;

    if i>=num then

    leave sign;# leave=break

    end if;

    end loop;

    select total from dual;

    end //

    call proc(100)//

    #如果没有设置标签名,leave loop

    #sign是循环名,用于结束循环,可以自己随意取名字

    (4)while循环

    #语法:

    while 条件 do

    //代码

    end while

    create procedure pro_11(in num int)

    begin

    declare total int default 0;

    declare i int default 0;

    while num>=i do

    set total=total+i;

    set i=i+1;

    end while;

    select total from dual;

    end //

    call pro_11(100)//

    (5)repeat循环

    #语法

    repeat

    代码

    until 条件 -- 直重复到条件为true才结束

    end repeat

    create procedure pro_12(in num int)

    begin

    declare total int default 0;

    declare i int default 0;

    repeat

    set total=total+i;

    set i=i+1;

    until i>num

    end repeat;

    select total from dual;

    end //

    call pro_12(100)//

    (6)leave和iterate

    leave类似于break,iterate类似于continue

    create procedure pro_13()

    begin

    declare i int default 0;

    sign:while i<5 do

    set i=i+1;

    if(i=3) then

    leave sign; -- 类似于break

    #iterate sign; -- 类似于continue

    end if;

    select i from dual;

    end while;

    end //

    call pro_13()//

    3.MySql函数

    内置函数

    (1).数字类

    语句

    含义

    select rand() from dual;

    随机数

    select * from stuinfo order by rand();

    随机排序

    select round(5.6);

    四舍五入

    select ceil(5.3);

    向上取整

    select floor(5.6);

    向下取整

    (2).大小写转换

    语句

    含义

    select ucase('i am lyb');

    大写

    select lcase('I AM LYB');

    小写

    (3).截取字符串

    语句

    含义

    select left('abcdefg',3);

    截取左边的3位

    select right('abcdefg',3);

    截取右边3位

    select substring('abcdefg',2,3);

    从第2位开始取3个字符,起始位置从1开始

    (4).字符串拼接

    select concat(sid,sname,age,sex,city) from stuinfo;

    mysql> select concat(sid,sname,age,sex,city) from stuinfo;

    +--------------------------------+

    | concat(sid,sname,age,sex,city) |

    +--------------------------------+

    | 7小明18male上海 |

    | 8小刚20male北京 |

    | 9小强22male重庆 |

    | 10小力23male天津 |

    | 11小丽21female北京 |

    | 12小月20female天津 |

    | 13小yb18male重庆 |

    | 17百强18male黑龙江 |

    | 18百强118male黑龙江 |

    | 19百强218male黑龙江 |

    +--------------------------------+

    (5).coalesce(str1,str2):如果str1不为null则显示str1,否则显示str2

    select sid,sname,coalesce(ch,'缺考'),coalesce(math,'缺考') from stuinfo left join stumarks using(sid);

    mysql> select sid,sname,coalesce(ch,'缺考'),coalesce(math,'缺考') from stuinfo l

    eft join stumarks using(sid);

    +-----+-------+---------------------+-----------------------+

    | sid | sname | coalesce(ch,'缺考') | coalesce(math,'缺考') |

    +-----+-------+---------------------+-----------------------+

    | 11 | 小丽 | 100 | 80 |

    | 8 | 小刚 | 60 | 98 |

    | 10 | 小力 | 50 | 51 |

    | 9 | 小强 | 67 | 88 |

    | 7 | 小明 | 88 | 10 |

    | 12 | 小月 | 96 | 97 |

    | 17 | 百强 | 缺考 | 缺考 |

    | 18 | 百强1 | 缺考 | 缺考 |

    | 19 | 百强2 | 缺考 | 缺考 |

    +-----+-------+---------------------+-----------------------+

    (6).length(字节长度)、char_length(字符长度)、trim(去两边空格)、repace(替换)

    select length('千锋');

    select char_length('千锋');

    select length(trim(' 千锋 '));

    select replace('pgone','one','two');

    (7).时间戳

    select unix_timestamp();

    (8).将时间戳转成当前时间

    select from_unixtime(unix_timestamp());

    (9).获取当前时间

    select now(),year(now()),month(now()),day(now()),hour(now()), minute(now()),second(now())\G

    #现在时间,年,月,日,时,分,秒

    (10).dayname(),monthname(),dayofyear()

    select dayname(now()) as `星期`,monthname(now()) as `月份`,dayofyear(now()) as `本年第几天`;

    (11).datediff(结束日期,开始日期)

    例题计算自己活了多少天

    select datediff(now(),'1970-1-1');

    (12).md5():md5加密

    select md5('@123456.');

    3.自定义函数

    #语法:

    Create function 函数名(形参) returns 返回的数据类型

    begin

    //函数体

    end

    #第一步

    delimiter //

    #不带参数的函数

    create function myfun() returns varchar(32)

    begin

    return '千锋python';

    end //

    #调用函数

    select myfun()//

    #带参数

    create function myfun_1(num1 int,num2 int) returns int

    begin

    declare num int default 0;

    set num=num1+num2;

    return num;

    end //

    select myfun_1(100,200)//

    #删除函数

    drop function myfun_1//

    4.触发器

    1、触发器是一个特殊的存储过程

    2、不需要直接调用,在MySQL自动调用的

    3、是一个事务,可以回滚

    (1)触发器的类型(触发事件)

    1、insert触发器

    2、update触发器

    3、delete触发器

    (2)创建触发器

    #语法:

    Create trigger 触发器名 触发时间[before|after] 触发事件 on 表名 for each row

    Begin

    //代码

    end

    (3)new表和old表

    1、这两个表是个临时表

    2、当触发器触发的时候在内存中自己创建,触发器执行完毕后自动销毁

    3、他们的表结构和触发器触发的表的结构一样

    4、只读,不能修改

    stuinfo curd

    打开文件,内存中需要加载,会随即分配一个空间用来保存文件的所有数据,->old 6

    在新的一轮操作后,内存会生成新的空间,这个空间里面保存了新的数据变化->new 7

    (5)insert触发器

    #在stuinfo中插入一个值,就会自动在stumarks中插入一条数据

    #after insert 表示的是在insert动作执行完毕以后触发

    #on stuinfo for each row 针对的stuinfo表,并且可以读取到每一行的变化

    #触发器中定义的局部变量不能与表中的字段名一致,否则会发生字段识别问题(识别不出到底是字段,还是变量)

    create trigger trig1

    after insert on stuinfo for each row

    begin

    declare sidno int default 0;

    declare nch int default 0;

    declare nmath int default 0;

    declare nseat int default 0;

    set sidno=new.sid;

    set nseat=new.seat;

    insert into stumarks set sid=sidno,ch=nch,math=nmath,seat=nseat;

    end //

    insert into stuinfo values(null,'随便','male',20,'合肥',12)//

    (6)update触发器

    create trigger trig2

    after update on stuinfo for each row

    begin

    declare sidno int default 0;

    declare seatno int default 0;

    set seatno=new.seat;

    set sidno =new.sid;

    update stumarks set seat=seatno where sid =sidno;

    end //

    select ((select max(seat) from stuinfo)+1)//

    update stuinfo set seat=12 where sid=12//

    (7)delete触发器

    create trigger trig3

    after delete on stuinfo for each row

    begin

    declare sidno int default 0;

    set sidno =old.sid; #删除了新表里面就没有了,只能从老表里面拿

    delete from stumarks where sid=sidno;

    end //

    delete from stuinfo where sid =13//

    #触发器能做钩子函数

    (8)查看 和 删除 触发器

    show triggers\G

    drop trigger if exists trig1//

    ##5.用户管理

    mysqld --skip--grant--tables

    #(5.5最好用)

    #--skip--grant--tables 跳过登陆验证(MYSQL服务器开起中)

    (1)创建用户

    语法:create user ‘用户名’@’允许登录的主机地址’ identified by 密码

    #%代表数据库的库名

    create user 'ruidong'@'%' identified by '123456';

    (2)删除用户

    语法:drop user 用户

    drop user ruidong;

    (3)增加用户权限

    #将python的所有表的select权限付给ruidong用户

    grant select on python.* to 'ruidong'@'%';

    #将所有数据库中所有表的所有权限付给ruidong用户

    grant all privileges on *.* to 'ruidong'@'%';

    #创建用户并授权

    grant all privileges on *.* to 'hal'@'%' identified by '123456' with grant option;

    #创建好用户以后,刷新mysql用户权限表

    flush privileges ;(linux ,mac)

    revoke select on python.* from 'ruidong'@'%'; #删除select权限

    revoke all privileges on *.* from 'ruidong'@'%'; #删除所有权限

    展开全文
  • MySQL存储过程MySQL中,创建存储过程的基本形式如下:CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body其中,sp_name参数是存储过程的名称;proc_parameter表示存储过程的参数...
  • (传输过程中发生异常)"); } } /** * 调用job文件 * @Description: TODO * @param @param jobName * @param @throws Exception * @return void * @throws * @Author: Pru * @Email : Pengru@mail.ru * @date 2017年12...
  • 运行完毕后,再次执行上面的第1步(1、定义一个函数用来随机生成一个字符串) 为什么要创建一个这样的函数?因为下面的存储过程中会用到该函数。 2、定义一个存储过程用来插入指定条数的测试数据delimiter $$ ...
  • //打开控制台输出的命令语法:declare声明部分begin//执行部分异常,事物,语句块等end;变量的类型oracle变量类型,oracle数据类型:integer varchar2等自定义数据类型1. 定义和列的类型保持一致v_sal emp.sal%type...
  • MySQL操作之存储过程

    2021-09-19 18:13:49
    二、存储过程的创建 2.1创建存储过程 2.2 变量的使用 2.3 定义条件和处理程序 2.4 光标的使用 2.5、流程控制的使用 三、存储过程的使用 3.1、调用存储过程 3.2、查看存储过程 3.3、修改存储过程 3.4、删除存储过程 ...
  • MySQL中,创建存储过程的基本形式如下:CREATEPROCEDUREsp_name([proc_parameter[,...]])[characteristic...]routine_body其中,sp_name参数是存储过程的名称;proc_parameter表示存储过程的参数列表; ...
  • 1、存储过程1》创建存储过程:create procedurecreate procedure sp_name ([in | out | inout]param_name type)[characteristics ...] routine_bodycharacteristics指定存储过程的特性:1>language sql:说明...
  • 存储过程MySQL中,创建存储过程的基本形式如下:CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body其中,sp_name参数是存储过程的名称;proc_parameter表示存储过程的参数列表...
  • 摘要:本文通过实验和事件跟踪来分析Oracle Job执行过程中修改下次执行时间的机制。有些人问,Oracle的JOB在设定完next_date和interval之后,到底是什么时候决定下一次运行时间的。可以归纳成以下几个问题。1.假设...
  • 2 存储过程存储函数MySQL中提供存储过程存储函数机制,我们姑且将存储过程存储函数合称为存储程序。与一般的SQL语句需要先编译然后立即执行不同,存储程序是一组为了完成特定功能的SQL语句集,经编译后存储在...
  • mysql存储过程学习

    2021-02-07 17:24:57
    适用人群:常写mysql存储过程的人仅想看懂存储过程的人mysql学习中的人1、创建存储过程:delimiter //将存储过程的结束符改为//。可以改,这是为了区分存储过程结束符和sql语句的结束符,增加可读create ...
  • 可以视为批文件,但是其作用仅仅局限于批处理。本文主要介绍如何创建存储过程存储函数,以及如何调用、查看、修改、删除存储过程存储函数等。创建存储过程和函数存储程序分为存储过程存储函数。Mysql创建...
  • 【MySQL】存储过程

    2021-07-09 10:39:34
    一、存储过程基本用法 1、创建存储过程 CREATE PROCEDURE 存储过程名 (参数列表) BEGIN SQL语句代码块 END
  • 存储过程是一组为了完成特定功能的SQL语句集,经过编译后存储在数据库中,用户通过指定存储过程的名字,并给出参数来执行它。存储过程是数据库中的一个重要对象, 任何一个设计良好的数据库应用程序都应该用到存储...
  • MySQL中,创建存储过程的基本形式如下:CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body其中,sp_name参数是存储过程的名称;proc_parameter表示存储过程的参数列表; ...
  • 简单地说,存储过程就是一条或者多条SQL语句的集合,可视为批文件,但是其作用不仅限于批处理。 10.1 存储过程和函数 存储程序可以存储过程和函数。在MySQL中,创建存储过程和函数使用的语句分别是CREATE PROCEDURE...
  • 这一篇《我的MYSQL学习心得(十)》将会讲解MYSQL的存储过程和函数MYSQL中创建存储过程和函数分别使用CREATE PROCEDURE和CREATE FUNCTION使用CALL语句来调用存储过程存储过程也可以调用其他存储过程函数可以从语句外...
  • 局部变量一般只在begin…end代码块中有效,作用域仅限于该语句块,在该语句执行完毕后,局部变量就消失了,局部变量的定义语法: DECLARE var_name data_type [ DEFAULT value ]; 说明 :declare为定义变量的关键字...
  • 什么是存储过程简单说,存储过程就是一条或多条SQL语句的集合,可视为批文件,但是起作用不仅限于批处理。本文主要讲解如何创建存储过程存储函数以及变量的使用,如何调用、查看、修改、删除存储过程存储函数等...
  • 注意: 存储过程和函数的 CREATE 语法支持使用 CREATE ORREPLACE 对存储过程和函数进行修改,如果需要对已有的存储过程或者函数进行修改,需要执行 ALTER 语法。 characteristic 特征值的部分进行简单的说明。 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 90,158
精华内容 36,063
关键字:

存储过程执行完毕但是不退出