读书笔记-MySQL存储过程-变量_MySQL

DECLARE l_int1 int default -2000000DECLARE l_int2 INT unsigned 4000000DECLARE l_bigint1 BIGINT DEFAULT 4000000000000000 DECLARE l_float FLOAT DEFAULT 1.8e8DECLARE l_double DOUBLE DEFAULT 2e45DECLARE l_numeric NUMERIC(8,2) DEFAULT 9.95;DECLARE l_date DATE DEFAULT '1999-12-31'DECLARE l_datetime DATETIME DEFAULT '1999-12-31 23:59:59'DECLARE l_char CHAR(255) DEFAULT 'This will be padded to 255 chars';DECLARE l_varchar VARCHAR(255) DEFAULT 'This will not be padded'DECLARE l_text TEXT DEFAULT 'This is a really long string. In sored programs we can use text colums fairly freely, but in tables there are some limiations regarding indexing and use in various expressions.' 

CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)BEGIN /*We can see the value of the IN parameter*/ SELECT p_in; /*We can modify it */ SET p_in = 2; /* show that the modification took effect */ select p_in;END;/*This output shows that the changes made within the stored program cannot be accessed from the calling program(in this case, the mysql client):*/set @p_in = 1call sp_demo_in_parameter(@p_in)select @p_in, 'We can''t see the changed value from the calling program'

CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)BEGIN /*We can't see the value of the OUT parameter*/ SELECT p_out, 'We can''t see the value of the OUT parameter'; /*We can modify it */ SET p_out = 2; SELECT p_out, 'OUT parameter value has been changed';END;SET @p_out = 1CALL sp_demo_out_parameter(@p_out)SELECT @p_out, "Calling program can see the value of the changed OUT parameter"

CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)BEGIN SELECT p_inout, 'We can see the value of the INOUT parameter in the stored program'; SET p_inout = 2; SELECT p_inout, 'INOUT parameter value has been changed';END;set @p_inout = 1call sp_demo_inout_parameter(@p_inout)select @p_inout, "Calling program can see the value of the changed INOUT parameter"

SELECT 'Hello World' into @x;SET @y = 'Goodbye Cruel World';SELECT @y;SET @z = 1+2+3;SELECT @z;

CREATE PROCEDURE GreetWorld()SELECT CONCAT(@greeting, ' World');SET @greeting = 'Hello';CALL GreetWorld);

CREATE PROCEDURE p1()SET @last_procedure = 'p1';CREATE PROCEDURE p2()SELECT CONCAT('Last procedure was ', @last_procedure);CALL p1();CALL p2();

CREATE PROCEDUREoperators()BEGIN DECLARE a int default 2; DECLARE b int default 3; DECLARE c FLOAT; SET c = a + b; SELECT 'a+b=', c; SET c = a/b; SELECT 'a/b=', c; SET c = a*b; SELECT 'a*b=', c; IF (a < b) THEN SELECT 'a is less than b'; END IF; IF NOT (a=b) THEN SELECT 'a is not equal to b'; END IF;END;