Mysql stored procedure _ MySQL

Mysql stored procedure conversion Terminator:

Delimiter/

Stored Procedure:

Create procedure test

() # Or (a int, B int, out B int)

Begin

# Write your operations here

End/

Call: call test ()/

Storage functions:

Create function test

() # Or (a int, B int)

Returns int # The function body must contain a RETURN value statement.

Begin

# Write your operations here

Return 1;

End/

Call: select test ()/

Note: the stored function cannot query tables (select statements cannot be used ).

Syntax:

1. The local variables in the stored procedure are defined by declare without the @ symbol and can only be in the begin end block. The global variable is @ var, with the @ symbol in front.
   Create procedure test (out a int)
   
   Begin
   
   Declare AB int default 1;
   
   Set a = 100;
   
   Set a = AB;
   
   End

   The variables defined by declare have the highest priority. all variables such as out a and local variables with the same name are valid only in the begin block.

   SELECT id, data INTO x, y FROM test. t1

   Begin cannot be used in combination.

2. the returned value is obtained through the OUT parameter.

3. Judgment

If condition then

Operation

End if;

Case value (optional)

When condition then operation

When condition then operation

End

4. Loop

While condition do... End while

Loop... End loop

Repeat... End repeat

Goto

While... End while example

Create procedure p14 ()

BEGIN

DECLARE v INT;

SET v = 0;

WHILE v <5 DO

Insert into t VALUES (v );

SET v = v + 1;

End while;

END ;//

Use labels

Lab1: begin

Operation

End lab1;

Lab2: while condition do

# Operation

# Leave lab2 can be used; skip loop

# You can use iterate lab2 to skip the current loop.

End while lab2

Use GOTO

B1: begin

Declare I int;

Set I = 1;



Label lab1;

Select 'hi ';

Set I = I + 1;

If (I <2)

Goto lab1;



End b1;

5. handle errors

Declare continue handler for sqlstate 'error number' operation end;

Declare exit handler for sqlstate 'error number' operation end;

Sqlstate 'error number' can also be

Not found # empty rows

Sqlexception # Error

Sqlwarning # Warning

The preceding statements are triggered only when an error is sent.

6. cursor

Read-only and non-scroll, declared before the handler, the SELECT statement cannot have an INTO clause.

Example:

Create procedure curdemo ()

BEGIN

Declare a, B, done int;

DECLARE cur1 cursor for select id, data FROM test. t1;

Declare continue handler for sqlstate '000000' SET done = 1; # handle declared errors

OPEN cur1; # OPEN the cursor

While done! = 1 do

Fetch cur1 into a, B;

If a> B then select 'A ';

Else select 'B ';

End if;

End while;

Close cur1;

End;

Note: The Last inserted ID function is last_insert_id (). all stored procedures and other information are stored in the INFORMATION_SCHEMA database.

Query stored procedures

Show create procedure test/# Query stored procedure details

Show create function test/

TRIGGER: TRIGGER

Type:

INSERT: The Trigger program is activated when a new row is inserted into the table, for example, through INSERT, load data, and REPLACE statements.

UPDATE: The Trigger program is activated when a row is changed, for example, through the UPDATE statement.

DELETE: The Trigger program is activated when a row is deleted from the table, for example, through the DELETE and REPLACE statements.

Use OLD.Col_nameTo reference columns of a row before the update, you can also use NEW.Col_nameTo reference columns in the updated row.

Time: BEFORE AFTER

Example:

Create trigger testtrio before insert on test1

FOR EACH ROW BEGIN

# Operation

END

Dynamic SQL:

Syntax:

PREPARE stmt_name FROM preparable_stmt;
EXECUTE stmt_name [USING @ var_name [, @ var_name]...];
{DEALLOCATE | DROP} PREPARE stmt_name;

Instance:

Mysql> SET @ a = 1;
Mysql> prepare stmt from "SELECT * FROM tbl LIMIT? ";
Mysql> execute stmt using @;
Mysql> SET @ skip = 1; SET @ numrows = 5;
Mysql> prepare stmt from "SELECT * FROM tbl LIMIT ?, ? ";
Mysql> execute stmt using @ skip, @ numrows;