Reading Notes-MySQL stored procedure-variable _ MySQL

Reading Notes-MySQL stored procedure-variable bitsCN. comDECLARE statements allow us to create variables. It will appear before any cursor and processing in the code block and declaration of any process statement. The syntax of the DECLARE statement is as follows:

Common MySQL data types

An example INT and INTEGER32-bit integer that describes the value of the data type. The value range is-2.1 billion to + 2.1 billion. if the value is unsigned, it can reach 4.2 billion. However, in this case, a negative number of 123,345 and A-2,000,000,000 BIGINT64-bit integer cannot be included. The value range is-9 trillion to + 9 trillion or a non-negative 0 to 18 trillion 9,000,000,000,000,000,000 FLOAT32-bit floating point number. The value range is-1.7e38to1.7e38 or non-negative 0 to 3.4e0000.00000000002, 17897.890790,-345.8908770, 1.7e21DOUBLE64-bit floating point number. The value range is almost infinite (1.7e308) 1.765e23,-1.765e100

DECIMAL (precision, scale)

NUMERIC (precision, scale)

Number of points. The storage condition depends on precision, which can save possible numeric ranges. NUMERIC is usually used to save important decimal numbers, such as cash 78979.00,-87.50, 9.95DATE date types, with no detailed time '2017-12-31 'DATETIME date and time, the time is accurate to the second '2017-12-31 23:59:59 'CHAR (length) fixed length string. The value will be blank and filled to the specified length. The maximum length is 255 bytes. the variable string 'Hello world' BLOB with a maximum length of 64 kB and the maximum length of TEXT is 64 KB, BLOB is used to save binary data. TEXT is used to save any imaginary content of TEXT data. LONGBLOB, LONGTEXTBLOB, and TEXT are extended versions. the storage capacity is 4 GB of any imaginary content, but it can store a larger length than BLOB and TEXT.

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.'  
Literal constant
Below are three basic literal types
Numeric literal constant, a traditional hexadecimal representation, with '0x 'before it '.
Date literal variable
Character literal variable
Variable naming rules
 
Parameters
Unless specifically defined, the IN parameter assumes the IN attribute. This means that their values must be specified by the main program, and any modifications to this parameter within the storage program cannot play a role in the main program.
When the storage program starts, the value of any OUT variable is assigned NULL, regardless of whether the value is assigned to other values in the main program.
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'

Example of the OUT parameter
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"

Example of INOUT 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"
 
User variable
A variable is a variable that is defined in MySQL and can be operated in or out of the storage program. There are two ways to use user variables:
1. because user variables have independent scopes of the storage program, they can be used to describe sessions that can be read and written by any storage program.
2. user variables can be used as the second option to pass parameters to methods. the storage program has read and write permissions on user variables, which can avoid inconvenience caused by passing values through parameters.
User variables can be created and manipulated by the MySQL command line client from any program. To ensure that the MySQL statement uses the SET statement.
SELECT 'Hello World' into @x;SET @y = 'Goodbye Cruel World';SELECT @y;SET @z = 1+2+3;SELECT @z;
Use User variables to transmit information between the caller and the called program
CREATE PROCEDURE GreetWorld()SELECT CONCAT(@greeting, ' World');SET @greeting = 'Hello';CALL GreetWorld);

Use User variables as global variables
CREATE PROCEDURE p1()SET @last_procedure = 'p1';CREATE PROCEDURE p2()SELECT CONCAT('Last procedure was ', @last_procedure);CALL p1();CALL p2();
 
Note
The MySQL stored procedure supports two different annotations:
1. two hyphens keep up with one space, and a comment is created to the end of the current line.
2. c-language style comments. start with/* and end. We call it multi-line annotation.
 
Operator
MySQL includes familiar operators in most languages, but C-style operators (++, ++ =) are not supported.
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; 

 
 
 
BitsCN.com