mysql实验七存储过程及函数_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>