mysql-(v)-Stored procedures

5.1 What is a stored procedure

SQL statements with logic

The previous SQL has no condition to judge, no loops

Stored procedures with Process Control statements (if while)

5.2 Characteristics of stored procedures

1) execution efficiency very fast! Stored procedures are executed on the database server side

2) The transfer value is very poor, different databases can not always

Grammar:

DELIMITER $   -- declaration end symbol CREATEPROCEDURE  pro_test ()BEGIN     -- can write multiple SQL statements    SELECT from employee; END $

--Execute Stored procedure

Call Pro_test ();

Parameters

Stored procedures with input parameters

--Need to pass in an employee ID, query employee information

Stored procedures with input parameters

DELIMITER $

CREATE PROCEDURE Pro_findbyid (in Eid INT)--in incoming parameters

BEGIN

select* from emplotee WHERE id = eid;

END $

---call

Call Pro_findbyid (4);

---stored procedure with output parameters

DELIMITER $

CREATE PROCEDURE pro_testout (out str VARCHAR)--out: Output parameters

BEGIN

SET str = "This is an output parameter"

END $

--Delete stored procedures

Drop PROCEDURE pro_testout;

--How to accept the value of the return parameter

--mysql Global Variables

See all global variables

--show variables

Fuzzy Query Global variables

--show variables like "character_%"

Accept data Encoding settings

--character_set_client;

Output data encoding settings

--character_set_results;

The XXX session variable---only exists in the current client-server connection, and the session variable disappears if the link is broken

Define a variable for a session

SET @NAME = ' Eric '

viewing session Variables

Select @NAME;

XXX local variables, as long as the stored procedure is completed, local variables are lost

XXX1) define a Session variable name,2) using the name session

Call Pro_testout (@NAME);

Stored procedures with input and output parameters

DELIMITER $

CREATE PROCEDURE Pro_testinuut (INOUT n INT)

BEGIN

SELECT N;

SET n = 500;

end$

-A stored procedure with conditional judgment

DELIMITER $

CREATE procefure pro_testif (in Num INT, out str VARCHAR (20))

BEGIN

IF num = 1 Then

SET str= "Monday";

ELSEIF num = 2 Then

SET str= "Tuesday";

ELSE

SET str= "Input Error"

END IF;

end$

Call Pro_testif (1, @str)

SELECT @ str;

* * Stored procedures with cyclic procedures

DELIMITER $

CREATE PROCEDURE pro_testwhile (in num int, out result INT)

BEGIN

DECLARE i INT DEFAULT 1;

DECLARE vsum INT DEFAULT 0;

While I < =num do

SET vsum = vsum + i;

i = i +1;

END while;

SET result = Vsum;

END $

Use the result of a query as the return value

DELIMITER $

Cretae PROCEDURE Pro_findbyid (in Eid INT, out VName VARCHAR (20))

BEGIN

SELECT EmpName into VName from employee WHERE ID = Eid;

END $

mysql-(v)-Stored procedures