MySql stored procedure creation and deletion and instance

MySql stored procedure creation and deletion and instance

The stored procedure in the mysql tutorial is a stored procedure with parameters (dynamic execution of SQL statements). The Stored Procedure in mysql queries user information based on user input conditions and sorting methods, there is no call Method for sorting conditions:

For mysql 5.0
The new feature tutorial is intended for mysql users who need to know the new features of mysql 5.0. Simply put, it introduces "stored procedures, triggers, views, and information architecture views ",

 

Call getusersdynamic ('Age <= 30 ','');

 

/******** Dynamically query user information ********/create procedure getusersdynamic (wherecondition varchar (500), 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 adddate from users where ', wherecondition, 'ORDER BY', orderbyexpress); end; else begin set @ sqlstr = concat ('select id, name, password, age, getdate (adddate) as adddate from users where ', wherecondition); end if; prepare stmt from @ sqlstr; execute stmt; end; getdate () is a custom function, the function is to return the short format of the date.

 

Create definer = 'root' @ 'localhost' function 'getdate' ($ date datetime) returns varchar (50) charset latin1 return date_format ($ date, '% y-% m-% D'); mysql Stored Procedure for dynamic data insertion (note that the four single quotes indicate one quotation mark ):

 

Create definer = 'root' @ 'localhost' procedure 'insertuser' (in name2 varchar (50), in password2 varchar (32), in age2 int, in adddate2 datetime) begin declare stmt varchar (2000); set @ sqlstr = concat ('insert into users (name, password, age, adddate) values ('); set @ sqlstr = concat (@ sqlstr, ''', name2, ''', ',', ''', password2 ,'''',', ', age2,', ', ''', adddate2, ''','); prepare stmt from @ sqlstr; execute stmt; end;

 

Here is an example: mysql> drop function f; query OK, 0 rows affected (0.00 sec)

If the instance is large, you need to add comments between some rows and paragraphs. At the same time, I will place 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 will remove the "mysql>" and "->" systems in the example. You can copy the Code directly to the mysql client program (if
This is not an electronic version. You can download related scripts on the mysql.com website. Therefore, all examples have been tested on suse 9.2 linux and mysql 5.0.3 public edition.

When you read this book, mysql has a higher version and supports more operating systems, including windows, Linux, and hp-ux. So the example here will run normally on your computer. However, if the operation still fails, you can consult a senior mysql user you know for long-term support and help.

 

A definition and an example definition and example

 

Definition and instance stored procedure is a program stored in the Library (just like a subroutine in a regular language). To be precise, mysql supports the "routines (routine) there are two types:
One is the stored procedure, and the other is the function that can return values in other SQL statements (like the pre-loaded functions in mysql, such as pi ()). I have used storage more frequently in this book.
Cheng, because this is our past habit and we believe everyone will accept it.

 

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

Here, a new syntax is defined for local variables, exception handling, loop control, and if clauses.

The following is an example of a stored procedure statement)

 

Create procedure procedure1/* name stored procedure name */

 

(In parameter1 integer)/* parameters parameter */

 

Begin/* start of block statement header */

 

Declare variable1 char (10);/* variables variable Declaration */

 

If parameter1 = 17 then/* start of if condition start */

 

Set variable1 = 'birds ';/* assignment value */

 

Else

 

Set variable1 = 'beasts';/* assignment value */

 

End if;/* end of if end */

 

Insert into table1 values (variable1);/* statement SQL statement */

 

End/* end of block statement block end */