Home Database Mysql Tutorial mysql 存储过程

mysql 存储过程

Jun 07, 2016 pm 03:34 PM
mysql create storage process

1.创建存储过程 Createprocedureprocedure_namebegin---end; 2.创建函数 Createfunctionfunction_name 3.定义存储过程和函数的参数 Parameter_nametype; 如: nameint; 4.关于存储过程和函数的表达式 1)声明变量 Declarevar_nametype 【 defaultvalue 】 2)

1. 创建存储过程

Create  procedure  procedure_name  begin   ---   end  ;

 

2. 创建函数 

Create  function  function_name  

 

3. 定义存储过程 和 函数 的参数

Parameter_name   type   ;

如:  name  int ;

 

4. 关于存储过程 和函数的表达式

1) 声明变量

Declare  var_name  type  【 default  value 

 

2) 赋值变量

Set  var_name = expr

 

3) 使用 select .... Into 实现赋值

 Select  filed_name  into  var_name   from   table_name  where  condition(条件);

 

4) 定义条件

Declare  condition_name   condition  for  condition_value  ;  

 

5. 删除存储过程 和 函数

Drop  procedure  procedure_name  ; 

 

Drop  function   function_name ;

 

6. 修改  存储过程  和函数

Alter  procedure  procedure_name   [ 修改 内容语句 ]  

 

Alter  function  function_name  [ 修改 语句 ]

 

7.使用流程语句

If  search_condition  then  statement_list   

. . .

End if 

 

Case  case_value  

When  when_value  then   statement_list

...

Else  statement_list

End  case

 

8.循环控制语句

 1)  [ begin_label : ]  loop

Statement_list

End  loop  [ end_loop ]

 

Leave  begin_label  // 

 

 2)   [ begin_label : ]  while  searcg_condition do

Statement_list 

End while [ end lable ]

 

3)   [ begin_label : ]  repeat  search_condition  do

Statement_list

End  repeat  [ end_list ]


9.查看 存储过程 和 函数

1)通过 show status  语句查看函数和存储过程

Show procedure status  [ like  pattern ]

Show function  stutus  [ like  pattern ]

 

2)通过 系统表 information_schema.routines  查看函数和存储过程

Use  information_schema

Select  *  from  routines  //

 

3)通过 show  create  语句查看函数和存储过程]
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

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

PHP's big data structure processing skills PHP's big data structure processing skills May 08, 2024 am 10:24 AM

PHP's big data structure processing skills

How to optimize MySQL query performance in PHP? How to optimize MySQL query performance in PHP? Jun 03, 2024 pm 08:11 PM

How to optimize MySQL query performance in PHP?

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

How to use MySQL backup and restore in PHP?

How to insert data into a MySQL table using PHP? How to insert data into a MySQL table using PHP? Jun 02, 2024 pm 02:26 PM

How to insert data into a MySQL table using PHP?

What are the application scenarios of Java enumeration types in databases? What are the application scenarios of Java enumeration types in databases? May 05, 2024 am 09:06 AM

What are the application scenarios of Java enumeration types in databases?

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

How to fix mysql_native_password not loaded errors on MySQL 8.4

How to use MySQL stored procedures in PHP? How to use MySQL stored procedures in PHP? Jun 02, 2024 pm 02:13 PM

How to use MySQL stored procedures in PHP?

How to create a MySQL table using PHP? How to create a MySQL table using PHP? Jun 04, 2024 pm 01:57 PM

How to create a MySQL table using PHP?

See all articles