Linux, FreeBSD, Juniper, Cisco / Network security articles and troubleshooting guides

FAQ
It is currently Wed Jan 18, 2017 7:37 pm


Mysql Application & Query Optimization, Questions, Indexes, Bugs.

Author Message
debuser
Post  Post subject: How to use a mysql stored procedure  |  Posted: Mon Nov 21, 2011 4:12 pm

Joined: Thu Aug 06, 2009 2:48 am
Posts: 105

Offline
 

How to use a mysql stored procedure

Unlike other sql commands, stored procedures need to be called. Therefore the CALL command needs to be used:

Code:
mysql> CALL stored_procedure_name('argument1,'argument2');





Top
Darwin
Post  Post subject: Re: How to use a mysql stored procedure  |  Posted: Tue Nov 22, 2011 1:23 am

Joined: Tue Nov 08, 2011 7:57 am
Posts: 3

Offline
A stored procedure is a procedure (like a subprogram in a regular computing language) that is stored (in the database). Correctly speaking, MySQL supports "routines" and there are two kinds of routines: stored procedures which you call, or functions whose return values you use in other SQL statements the same way that you use pre-installed MySQL functions like pi(). I'll use the word "stored procedures" more frequently than "routines" because it's what we've used in the past, and what people expect us to use.

A stored procedure has a name, a parameter list, and an SQL statement, which can contain many more SQL statements. There is new syntax for local variables, error handling, loop control, and IF conditions. Here is an example of a statement that creates a stored procedure.

CREATE PROCEDURE procedure1 /* name */
(IN parameter1 INTEGER) /* parameters */
BEGIN /* start of block */
DECLARE variable1 CHAR(10); /* variables */
IF parameter1 = 17 THEN /* start of IF */
SET variable1 = 'birds'; /* assignment */
ELSE
SET variable1 = 'beasts'; /* assignment */
END IF; /* end of IF */
INSERT INTO table1 VALUES (variable1); /* statement */
END /* end of block */

What I'm going to do is explain in detail all the things you can do with stored procedures. We'll also get into another new database object, triggers, because there is a tendency to associate triggers with stored procedures.


Top
Display posts from previous:  Sort by  
E-mail friendPrint view

Topics related to - "How to use a mysql stored procedure"
 Topics   Author   Replies   Views   Last post 
There are no new unread posts for this topic. Mysql> stored procedure for inserting in multiple tables \w common key

debuser

1

2245

Tue Nov 22, 2011 5:17 pm

debuser View the latest post

There are no new unread posts for this topic. Mysql command to show stored procedures

debuser

0

313

Mon Nov 21, 2011 3:57 pm

debuser View the latest post

There are no new unread posts for this topic. Mysql show stored procedures and functions

mandrei99

0

691

Mon Nov 18, 2013 9:06 am

mandrei99 View the latest post

There are no new unread posts for this topic. MySql doesn't start: libexec/mysqld: File './mysql-bin.index' not found (Errcode: 13)

debuser

0

879

Mon Feb 22, 2010 1:43 pm

debuser View the latest post

There are no new unread posts for this topic. Mysql: add column to table

debuser

1

1413

Fri Oct 08, 2010 1:38 am

sonee4 View the latest post

There are no new unread posts for this topic. Rename MySql table

debuser

1

389

Fri Oct 08, 2010 1:36 am

sonee4 View the latest post

There are no new unread posts for this topic. MySql rename table

admin

2

661

Mon Nov 28, 2011 3:39 am

Darwin View the latest post

There are no new unread posts for this topic. Mysql: Cleaning binary logs

debuser

1

686

Tue May 18, 2010 9:45 am

designeru View the latest post

There are no new unread posts for this topic. Mysql change column's default value

debuser

0

870

Thu Aug 12, 2010 3:43 am

debuser View the latest post

There are no new unread posts for this topic. Mysql drop (remove) unique key

mandrei99

0

700

Wed Jan 14, 2015 7:53 pm

mandrei99 View the latest post

 

Who is online
Users browsing this forum: No registered users and 0 guests
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum
Jump to:  
News News Site map Site map SitemapIndex SitemapIndex RSS Feed RSS Feed Channel list Channel list


Delete all board cookies | The team | All times are UTC - 5 hours [ DST ]



phpBB SEO