数据库系统（六）---MySQL语句及存储过程

mysql> delimiter $$
mysql> CREATE PROCEDURE proc_add_stu(
　　 -> IN sNo INTEGER,
　　 -> OUT sid int
　　 -> )

mysql> BEGIN #存储过程开始
　　 -> insert into student(s_no) values(sNo);
　　 -> SELECT LAST_INSERT_ID() into sid;  #将选定列的值直接存储到局部变量中
　　 -> END $$ #存储过程结束

mysql> delimiter;　　#将语句的结束符号恢复为分号

mysql> call pro_add_stu(‘0001‘); 

mysql> delimiter $$
mysql> create procedure in_proce(in p_in int)
    -> begin
    -> 　　select p_in;
    -> 　　set p_in=0; #局部变量赋值（begin...和end之间）
    ->    select P_in;
    -> end$$
mysql> delimiter ;
 
mysql> set @p_in=1; #全局变量@p_in赋值
 
mysql> call in_param(@p_in); #将全局变量@p_in的值作为参数传递给局部变量p_in
+------+
| p_in |
+------+
|    1 |
+------+
 
+------+
| P_in |
+------+
|    0 |
+------+
 
mysql> select @p_in; #输出全局变量@p_in的结果
+-------+
| @p_in |
+-------+
|     1 |
+-------+

mysql> delimiter //
mysql> create procedure out_proce(out p_out int)
    ->   begin
    ->     select p_out;
    ->     set p_out=2;
    ->     select p_out;
    ->   end
    -> //
mysql> delimiter ;
 
mysql> set @p_out=1;
 
mysql> call out_proce(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null

+-------+
| p_out |
+-------+
|     2 |
+-------+
 
mysql> select @p_out; #输出全局变量（用户变量）结果
+--------+
| @p_out |
+--------+
|      2 |
+--------+
#调用了out_proce存储过程，输出参数，改变了p_out变量的值

mysql> delimiter $$
mysql> create procedure inout_proce(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
mysql> delimiter ;
 
mysql> set @p_inout=1;
 
mysql> call inout_proce(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+
 
+---------+
| p_inout |
+---------+
|       2 |
+---------+
 
mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+
#调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

mysql > DELIMITER //
mysql > CREATE PROCEDURE proc3()  
     -> begin 
     -> declare x1 varchar(5) default ‘outer‘;  
     -> begin 
     -> declare x1 varchar(5) default ‘inner‘;  
      -> select x1;  
      -> end;  
       -> select x1;  
     -> end;  
     -> //
mysql > DELIMITER ;

mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc2(IN parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> if var=0 then 
     -> insert into t values(17);  
     -> end if;  
     -> if parameter=0 then 
     -> update t set s1=s1+1;  
     -> else 
     -> update t set s1=s1+2;  
     -> end if;  
     -> end;  
     -> //  
mysql > DELIMITER ;

mysql > DELIMITER // 
mysql > CREATE PROCEDURE proc4()  
     -> begin 
     -> declare var int;  
     -> set var=0;  
     -> while var<6 do  
     -> insert into t values(var);  
     -> set var=var+1;  
     -> end while;  
     -> end;  
     -> //  
mysql > DELIMITER ;

create procedure p1()
begin
    declare id int;
    declare name varchar(15);
    -- 声明游标
    declare mc cursor for select * from class;
    -- 打开游标
    open mc;
    -- 获取结果
    fetch mc into id,name;
    -- 这里是为了显示获取结果
    select id,name;
    -- 关闭游标
    close mc;
    
end;

#删除已经存在的存储函数
DROP FUNCTION IF EXISTS func_stu;

#创建存储函数(声明返回类型为varChar(50))
CREATE FUNCTION func_stu(in_id INT) RETURNS VARCHAR(50)
BEGIN
    DECLARE o_name VARCHAR(50); #声明局部变量
    SELECT name INTO o_name FROM tb_stu WHERE id = in_id; #tb_stu指事先创建好的数据库
    RETURN o_name;
END;

SELECT func_stu(1);

DROP FUNCTION IF EXISTS func_stu;

ALTER FUNCTION func_name [characteristic ...]
characteristic:
    COMMENT ‘string‘
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }