Home > Database > Mysql Tutorial > body text

MySQL存储过程的学习(一)_MySQL

WBOY
Release: 2016-06-01 13:09:51
Original
888 people have browsed it

1.存储过程的创建

CREATE PROCEDURE proc_demo()BEGINSELECT username FROM t_user;END
Copy after login

创建名为proc_demo的存储过程,注意存储过程名称后的括号必须存在,不管有没有参数,否则会报错,如下:


[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGINSELECT username* FROM t_user;END' at line 2
Copy after login

2.存储过程的查看

通过1我们创建好了存储过程,那么接下来我们来看看创建好的存储过程

有两种方式:

(1)根据名称查询存储过程:

          

SHOW CREATE PROCEDURE proc_demo
Copy after login

执行完成后:

(2)查询所有存储过程:

SHOW PROCEDURE STATUS
Copy after login

执行完成后:

这样我们就能查看到自己编写的存储过程了


3.存储过程的修改

存储过程的修改只是指的修改特性,过程体是不能修改的,所以不要试图去用alter修改其中的sql部分了,如果是sql想修改,只能drop后再create了

这是修改的语法:

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...] characteristic:    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }  | SQL SECURITY { DEFINER | INVOKER }  | COMMENT 'string'
Copy after login

注意哦,这里面是没有routine_body的,所以不能修改过程体

4.存储过程的删除

DROP PROCEDURE IF EXISTS proc_demo
Copy after login

好了,初步的关于存储过程的CRUD完成了




source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!