Mysql5.0 stored procedure _ MySQL

Mysql5.0 Stored procedure Stored routines require the proc table in the mysql database. (mysql. proc)

Tips:

1. before creating a stored procedure, use delimiter $ to define the statement terminator. after creation, restore the statement terminator delimiter;

Because MySQL uses the separator ";" by default, every sentence in the process body is compiled by MySQL as a stored procedure, an error is reported during the compilation process, therefore, use the DELIMITER keyword to declare that the DELIMITER of the current segment is restored when it is used up.

2. use BEGIN... END combination to start and END the process

3. use the show procedure status function to obtain the stored procedure information.

Stored procedure getCount:
DELIMITER $

Drop procedure if exists 'firstdb'. 'getcount' $
Create procedure 'getcount' (OUT totalCount INT)
DETERMINISTIC
BEGIN
Select count (*) INTO totalCount FROM users;
Select totalCount;
END $

DELIMITER;

Call getCount (@ count );

Stored procedure getCount2:
DELIMITER $

Drop procedure if exists 'firstdb'. 'getcount2' $
Create procedure 'firstdb'. 'getcount2 '()
BEGIN
Select count (*) from users;
END $

DELIMITER;

Use firstdb;
CALL getCount2 ();

Function helloFunc:
DELIMITER $

Drop function if exists 'firstdb'. 'hellofunc' $
Create function 'firstdb'. 'hellofunc' (s char (20) returns char (50)

BEGIN
Return concat ('Hello, ', s ,'! ');
END $

DELIMITER;

Select helloFunc ('king ');

Mysql5 stored procedure writing practices ()
From: http://www.linuxsky.net

MySql5.0 and later support stored procedures. I recently studied this

Format:

Create procedure process name ([process parameter [,...])
[Features...] process body

Create function name ([FUNCTION parameter [,...])
RETURNS return type
[Features...] function body

Process parameters:
[IN | OUT | INOUT] parameter name parameter type

Function parameters:
Parameter name parameter type

Return type:
A valid MySQL data type is enough.

Features:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| {Contains SQL | no SQL | reads SQL data | modifies SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}
| COMMENT 'string'

Process body/function body: the format is as follows:
BEGIN
Valid SQL statements
END

I don't care about proprietary features here. these are not compatible with SQL specifications, so characteristic (feature) related content is not considered.
//
Note the following points during development:
1. Stored procedure annotation: MySQL supports -- or/**/annotation, where the former is a line annotation and the latter is a segment annotation.
2. use declare to declare a variable. the temporary variable can be directly prefixed with @ for reference.
3. when editing directly using the MySQL Administrator manager, you can directly use the following function text input;
However, when a stored procedure or function is automatically imported into a script, because MySQL uses the ";" separator by default, every statement of the process body is compiled by MySQL as a stored procedure, an error is reported during the compilation process. Therefore, you must use the DELIMITER keyword to declare that the current segment separator is used up and then restore the DELIMITER. As follows:
DELIMITER $
Stored Procedures and Functions
DELIMITER;
4. MySQL supports a large number of embedded functions, some of which are consistent with those of large commercial databases such as oracle, informix, and sybase, but some of which have different names but have the same functions; or some of them have the same name but different functions. This is especially important for DBAs that are transferred from these databases.
5. debugging of stored procedures or functions: I have not studied the various tool kits provided by MySQL yet. it is not clear whether the debugging tools are provided, but the compilation errors are relatively good; as for business process debugging, you can use a stupid method, that is, to create a debugging table and insert a record into each process point in the package body to observe the program execution process. This is also a convenient and stupid method. Pai_^

The following are two examples. a string encryption algorithm is provided. each call with the same input parameter will get different encryption results,
The algorithm is relatively simple and not powerful. The functions and procedures are implemented as follows:
(1) functions

Eg:
Create function fun_addmm (inpass varchar (10) RETURNS varchar (11)
BEGIN
Declare string_in varchar (39 );
Declare string_out varchar (78 );
Declare offset tinyint (2 );
Declare outpass varchar (30) default ';
Declare len tinyint;
/* Declare I tinyint ;*/

/**/
Set len = LENGTH (inpass );
If (len <= 0) or (len> 10) then
Return "";
End if;

Set offset = (SECOND (NOW () mod 39) + 1;/* modulo based on the number of seconds */
/* Insert into testtb values (offset, 'offset :');*/
Set string_out = 'yn8k1jozvurb3mdets5gpl27axwihq94c6f0 #$ _ ';/* Key */
Set string_in = '_ $ # ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ';

Set outpass = CONCAT (outpass, SUBSTRING (string_out, offset, 1 ));
/* Insert into testtb values (2, outpass );*/
Set string_out = CONCAT (string_out, string_out );
Set @ I = 0;
REPEAT
Set @ I = @ I + 1;
Set outpass = CONCAT (outpass, SUBSTR (string_out, INSTR (string_in, SUBSTRING (inpass, @ I, 1) + offset, 1 ));
/* Insert into testtb values (@ I + 2, outpass );*/
UNTIL (@ I> = len)
End REPEAT;

Return outpass;
END

(2) process

Create procedure 'Pro _ addmm' (IN inpass varchar (10), OUT outpass varchar (11 ))
BEGIN
Declare string_in varchar (39 );
Declare string_out varchar (78 );
Declare offset tinyint (2 );
Declare len tinyint;

Set outpass = ';

Set len = LENGTH (inpass );
If (len <= 0) or (len> 10) then
Set outpass = ';
Else
Set offset = (SECOND (NOW () mod 39) + 1;

Set string_out = 'yn8k1jozvurb3mdets5gpl27axwihq94c6f0 #$ _';
Set string_in = '_ $ # ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ';

Set outpass = CONCAT (outpass, SUBSTRING (string_out, offset, 1 ));

Set string_out = CONCAT (string_out, string_out );
Set @ I = 0;
REPEAT
Set @ I = @ I + 1;
Set outpass = CONCAT (outpass, SUBSTR (string_out, INSTR (string_in, SUBSTRING (inpass, @ I, 1) + offset, 1 ));
UNTIL (@ I> = len)
End REPEAT;
End if;
END

//
The execution result is as follows:
Mysql> call pro_addmm ('zhouys ', @ );
Query OK, 0 rows affected (0.00 sec)

Mysql> SELECT @;
+ --------- +
| @ A |
+ --------- +
| U_PI6 $4 |
+ --------- +
1 row in set (0.00 sec)

Mysql> call pro_addmm ('zhouys ', @ );
Query OK, 0 rows affected (0.00 sec)

Mysql> SELECT @;
+ --------- +
| @ A |
+ --------- +
| 9P8UEGM |
+ --------- +
1 row in set (0.00 sec)

Mysql> select fun_submm ('U _ PI6 $4 ');
+ ---------------------- +
| Fun_submm ('U _ PI6 $ 4') |
+ ---------------------- +
| ZHOUYS |
+ ---------------------- +
1 row in set (0.00 sec)

Encryption algorithms have several weaknesses:
1. case insensitive
2. Chinese characters are not supported
3. insufficient encryption strength