T N T

美食博主

0%

$9 存储过程和函数相关操作

Posted on Edited on In

本节记录存储过程和函数相关内容

创建存储过程

  1. CREATE PROCEDURE procedure() 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    (root@localhost) [book]> delimiter $$
    (root@localhost) [book]> create procedure up_major(in id int, in ma char(10))
        -> begin
        -> update student set major=ma where stu_id=id;
        -> end$$
    Query OK, 0 rows affected (0.03 sec)


    (root@localhost) [book]> create procedure search(in id int)
        -> begin
        -> select * from student where stu_id=id;
        -> end$$
    Query OK, 0 rows affected (0.00 sec)


    (root@localhost) [book]> create procedure ins(in id int, in name char(10), in ma char(10), in sex char(1))
        -> begin
        -> insert into student values(id, name, ma, sex);
        -> end$$
    Query OK, 0 rows affected (0.01 sec)


    (root@localhost) [book]> create procedure testadd()         复杂的例子
        -> begin
        -> declare id int;
        -> declare e int default 0;
        -> declare datas cursor for select stu_id from student;
        -> declare continue handler for not found set e=1;
        -> open datas;
        -> fetch datas into id;
        -> repeat
        -> update student set stu_id=stu_id+1000 where stu_id=id;
        -> fetch datas into id;
        -> until e
        -> end repeat;
        -> close datas;
        -> end$$
    Query OK, 0 rows affected (0.01 sec)

调用存储过程

  1. CALL procedure() 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    (root@localhost) [book]> call up_major(1008, '计算机');
    Query OK, 1 row affected (0.01 sec)


    (root@localhost) [book]> call search(1008);
    +--------+----------+-----------+---------+
    | stu_id | stu_name | major     | stu_sex |
    +--------+----------+-----------+---------+
    |   1008 | 王虎     | 计算机    | M       |
    +--------+----------+-----------+---------+
    1 row in set (0.01 sec)
    Query OK, 0 rows affected (0.01 sec)


    (root@localhost) [book]> call ins(1001, '大白', '环境工程', 'F');
    Query OK, 1 row affected (0.01 sec)

删除存储过程

  1. DROP PROCEDURE procedure 
    1
    2
    (root@localhost) [book]> drop procedure testadd;
    Query OK, 0 rows affected (0.00 sec)

创建存储函数

  1. CREATE FUNCTIOn function() RETURNS ... 
    1
    2
    3
    4
    5
    (root@localhost) [book]> create function name_from_test(id int) returns varchar(20)
        -> begin
        -> return(select stu_name from student where stu_id=id);
        -> end$$
    Query OK, 0 rows affected (0.01 sec)

调用存储函数

  1. SELECT function*() 
    1
    2
    3
    4
    5
    6
    7
    (root@localhost) [book]> select name_from_test(2008);
    +----------------------+
    | name_from_test(2008) |
    +----------------------+
    | 王虎                 |
    +----------------------+
    1 row in set (0.00 sec)

删除存储函数

  1. DROP FUNCTION function 
    1
    2
    (root@localhost) [book]> drop function name_from_test;
    Query OK, 0 rows affected (0.02 sec)