MySQL Life

MySql的存储过程及函数

时间:2017-12-27 11:24:18  作者:solgle  来源:solgle.com  查看:79  评论:0
内容摘要:MySql存储过程及函数使用案例
mysql> select * from myai;
+----+--------+---------+
| id | city   | country |
+----+--------+---------+
|  1 | ???    | china   |
|  2 | ??     | china   |
|  3 | ??     | china   |
|  4 | ??     | china   |
|  5 | 成都   | china   |
+----+--------+---------+
5 rows in set (0.00 sec)
 
mysql> delimiter $$       --重定义命令结束符号
mysql> create procedure to_print(in str1 varchar(20))
    -> begin 
    -> select id from myai where city=str1;
    -> end; $$
Query OK, 0 rows affected (0.09 sec)
 
mysql> delimiter ;        --将命令结束符改回
 
mysql> call to_print('成都');
+----+
| id |
+----+
|  5 |
+----+
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
 
---过程调用
mysql> delimiter $$
mysql> create procedure to_count(in country1 varchar(30),out rs_num bigint)
    -> begin 
    -> call to_print('成都');   --过程调用
    -> select count(1) into rs_num from myai where country=country1;
    -> end  $$
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;         --将命令结束符改回
mysql> 
 
---参数有返回值的调用
mysql> call to_count('china',@aa);
+----+
| id |
+----+
|  5 |
+----+
1 row in set (0.01 sec)
 
Query OK, 0 rows affected (0.01 sec)
 
 
mysql> drop procedure to_print;
Query OK, 0 rows affected (0.00 sec)
 
 
---定义函数
mysql> create function to_result(id1 bigint) returns bigint
    -> begin 
    -> declare cnt bigint;    --定义变量
    -> select count(1) into cnt from myai where id=id1;
    -> return cnt;
    -> end;  $$
Query OK, 0 rows affected (0.00 sec)
 
--调用函数
mysql> select to_result(4);
+--------------+
| to_result(4) |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
 
mysql> 
--查看过程或函数定义
mysql> show create procedure to_print \G;
*************************** 1. row ***************************
           Procedure: to_print
            sql_mode: 
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `to_print`(in str1 varchar(20))
begin 
select id from myai where city=str1;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)
 
ERROR: 
No query specified
 
mysql> 
---查看存储过程和函数系统表信息
mysql> select * from information_schema.routines where routine_name='to_print' \G; 
*************************** 1. row ***************************
       SPECIFIC_NAME: to_print
     ROUTINE_CATALOG: NULL
      ROUTINE_SCHEMA: test
        ROUTINE_NAME: to_print
        ROUTINE_TYPE: PROCEDURE
      DTD_IDENTIFIER: NULL
        ROUTINE_BODY: SQL
  ROUTINE_DEFINITION: begin 
select id from myai where city=str1;
end
       EXTERNAL_NAME: NULL
   EXTERNAL_LANGUAGE: NULL
     PARAMETER_STYLE: SQL
    IS_DETERMINISTIC: NO
     SQL_DATA_ACCESS: CONTAINS SQL
            SQL_PATH: NULL
       SECURITY_TYPE: DEFINER
             CREATED: 2018-12-06 19:25:14
        LAST_ALTERED: 2018-12-06 19:25:14
            SQL_MODE: 
     ROUTINE_COMMENT: 
             DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
  DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)
 
ERROR: 
No query specified
 
mysql> 
 
标签:MySql存储过程及函数 

solgle.com 版权所有,欢迎分享!!!

相关评论
   Copyright © 2013-2020 solgle.com,All rights reserved.[solgle.com] 公安机关备案号:51010802000219
Email:solgle@solgle.com; weixin:cd1008610000 ICP:蜀ICP备14011070号-1