直接看代码吧。
sql.sh:
#! /bin/bash
host="127.0.0.1"
port="3306"
username="root"
password="123456"
dbname="mapan"
dbset="--default-character-set=utf8 -A"
/usr/local/mysql/bin/mysql -h${host} -u${username} -p${password} ${dbname} -P${port} -e "source test.sql"
再看test.sql:
delimiter $
create function fun2_bok(func_id int)
returns varchar(20)
begin
return(select * from tmp where id=func_id);
end $
select fun2_bok(1)$
查看存储函数和查看原表:
mysql> show function status;
Empty set (0.00 sec)
mysql> select * from tmp;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
执行sql.sh和查看存储过程:
]# ./sql.sh
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| fun2_bok(1) |
+-------------+
| 1 |
+-------------+
+-------+----------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-------+----------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| mapan | fun2_bok | FUNCTION | root@localhost | 2018-05-22 09:41:07 | 2018-05-22 09:41:07 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
+-------+----------+----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
删除存储函数:
mysql> drop function fun2_bok;
Query OK, 0 rows affected (0.00 sec)
mysql> show function status;
Empty set (0.00 sec)
mysql>
上面只是简单的例子,供大家学习参考。