How to define, modify and delete stored procedures-Mysql Tutorial-php.cn

How to define, modify and delete stored procedures

一个新手

Release： 2017-10-18 10:18:00

Original

2610 people have browsed it

1. Classification of stored procedures

System stored procedures
Local stored procedures (user-defined)
Temporary stored procedures (local [#], global [##] temporary stored procedures)

2. Create stored procedures

--选出价格区间的商品信息create procedure sp_goods_price@minprice float ,@maxprice floatas select * from goods 
where price>=@minprice and price <=@maxpricego

Copy after login

Execute the stored procedure: execute sp_goods_price 200 2000

3. Modify the stored procedure

create procedure sp_goods_betw@minprice float =200,@maxprice float=3000as select * from goods 
where price>=@minprice and price <=@maxpricego

Copy after login

4. Delete the stored procedure

drop procedure sp_goods_price

Copy after login

5. View the stored procedure

sp_helptext procedureName
sp_help procedureName

Copy after login

6. Rename the stored procedure

exec sp_rename oldName newName

Copy after login

**Local stored procedures

<p style="margin-bottom: 7px;">create procedure #sp_goods_betw@minprice float ,@maxprice floatas select * from goods <br/>where price>=@minprice and price <=@maxpricego<br/></p>

Copy after login

**Global stored procedures

create procedure ##sp_goods_betw@minprice float ,@maxprice floatas select * from goods 
where price>=@minprice and price <=@maxpricego

Copy after login

** Uncached stored procedure

Copy after login

with recompile

Copy after login

as select * from goods 
where price>=@minprice and price <=@maxpricego

Copy after login

**Encrypted stored procedure

Copy after login

with enctyption

Copy after login

as select * from goods 
where price>=@minprice and price <=@maxpricego

Copy after login

The above is the detailed content of How to define, modify and delete stored procedures. For more information, please follow other related articles on the PHP Chinese website!