MySQL stored procedures create deletes and instances

The MySQL tutorial stored procedure is a stored procedure with parameters (Dynamic execution of SQL statements), which queries the user's information based on the conditions and sort criteria entered by the user, and the sorting criteria may not be invoked:

MySQL 5.0
The new features tutorial is written for old MySQL users who need to understand the new features of version 5.0. The simple thing is to introduce "stored procedures, triggers, views, information schema views,"

Call Getusersdynamic (' age<=30 ', ');

/******** dynamically query User information ********/CREATE PROCEDURE getusersdynamic (wherecondition varchar (s), orderbyexpress varchar (100    ) BEGIN DECLARE stmt varchar (2000); If Length (orderbyexpress) >0 then begin set @sqlstr =concat (' Select Id,name,password,age,getdate (adddate) as a    Dddate from the users where ', WhereCondition, ' ORDER by ', orderbyexpress);    End Else begin set @sqlstr =concat (' Select Id,name,password,age,getdate (adddate) as adddate from users where ', where    condition);    End    End If;    Prepare stmt from @sqlstr;    Execute stmt;   　　End GETDATE () is a custom function that returns a short form of the date

Create definer= ' root @ ' localhost ' function ' getdate ' ($date datetime) returns varchar (m) CharSet Latin1 return date_for  　　Mat ($date, '%y-%m-%d '); The mysql stored procedure for dynamically inserting data (note that four single quotes denote one quotation mark):

Create definer= ' root ' @ ' localhost ' procedure ' Insertuser ' (in name2 varchar (x), in Password2 varchar (x), in Age2    ADDDATE2 datetime) BEGIN DECLARE stmt varchar (2000);    Set @sqlstr =concat (' INSERT INTO users ' (name,password,age,adddate) values (');    Set @sqlstr =concat (@sqlstr, ' ", name2, ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ', ',    Prepare stmt from @sqlstr;    Execute stmt; End

Here's an example:mysql> drop function f;query OK, 0 rows Affected (0.00 sec)

If the instance is larger, you need to annotate some lines and paragraphs, and I will use the "<--" symbol on the right side of the page to emphasize.

For example:

Mysql> CREATE PROCEDURE P ()

-> begin

->/* This procedure does nothing * * * <--

-> end;//query OK, 0 rows Affected (0.00 sec)

Sometimes I get rid of the "mysql>" and "->" systems in the example, and you can copy the code directly to the MySQL client (if you're reading the
Not the electronic version, you can download the relevant script on the MySQL.com website) so the examples have been tested on SuSE 9.2 linux, MySQL 5.0.3 public edition.

As you read this book, MySQL already has a higher version and can support more OS, including Windows,sparc,hp-ux. So the example here will be able to run normally on your computer. However, if the operation still fails, you can consult the senior MySQL users you know to get long-term support and help.

Definition and example of a definition and an example

Definition and instance stored procedures are a program stored in stacks (like subroutines in regular languages), and, to be exact, MySQL supports "routines (routines)" In two ways:
One is the stored procedure that we are talking about, and the second is the function that can return a value in other SQL statements (like Pi (), as in a function that is preloaded with MySQL). I will use it more often in this book.
Process, because this is our past habit, I believe we will accept.

A stored procedure includes a name, a list of arguments, and a set of SQL statements that can include many SQL statements.

There is a new syntax definition for local variables, exception handling, loop control, and if conditional sentences.

The following is an instance declaration that includes a stored procedure: (in order to facilitate reading, the program does not add any Chinese comments)

CREATE PROCEDURE procedure1/* Name Stored procedure name * *

(in Parameter1 integer)/* Parameters parameter * *

Begin/* Start of block statement size * *

DECLARE variable1 char (10); /* Variables Variable declaration * *

If Parameter1 = then/* Start of IF if condition starts * *

Set variable1 = ' birds '; /* Assignment Assigned value * *

Else

Set variable1 = ' beasts '; /* Assignment Assigned value * *

End If; /* End of If if ending/

INSERT INTO table1 values (VARIABLE1);/* Statement SQL statement * *

End/* Ends of block statement blocks closed */