Shulou(Shulou.com)06/01 Report--

CREATE ROUTINE permission is required to execute CREATE PROCEDURE and CREATE FUNCTION statements.

View existing permissions of neo user

Authorization

Mysql > grant create routine on fire.* to neo

Query OK, 0 rows affected (0.12 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.02 sec)

Create a stored procedure

Note: do not use tab when indenting the command line, use spaces, or the following error will be reported

DATE INNER MULTILINESTRING SET UNICODE warnings DATEDIFF INNOBASE MULTILINESTRINGFROMTEXT SHA UNION DATETIME INNODB MULTILINESTRINGFROMWKB SHA1 UNIQUE DATE_ADD INOUT MULTIPOINT SHARE UNIQUE_USERS-> Info;-> Display all 903 possibilities? (y or n)

Execute stored procedure

Authorization

Mysql > grant execute on fire.* to neo

Query OK, 0 rows affected (0.04 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

It can also be executed directly in Navicat.

Edit stored procedure

Call stored procedure

Create a stored procedure without parameters. Unlike Oracle, the stored procedure name must be followed by ().

Mysql > delimiter $$

Mysql > create procedure proc_Subscribers_update ()

-> begin

-> DECLARE v_count INT

-> select ifnull (max (a), 0) into v_count from T2

-> while v_count

< 2 do ->

Select concat ('the maximum value is', v_count)

-> set v_count = v_count+1

-> end while

-> end$$

Query OK, 0 rows affected (0.06 sec)

Create a stored procedure delimiter $$create procedure proc_Subscribers_update (IN v_fetch_cnt INT, IN v_sleep_secs INT) begin DECLARE v_count INT; DECLARE v_times INT DEFAULT 1; DECLARE v_max_value INT; / * compute the times that the loop runs*/ select ceil (count (MSISDN)) / v_fetch_cnt into v_count from tmp_Subscribers_01; / * compute the maximum rows that have been already updated*/ WHILE v_times that contains incoming parameters

< v_count DO select ifnull(max(id),0) into v_max_value from tmp_Subscribers_02; if v_max_value < v_fetch_cnt * v_count then SET v_times = 1 + floor(v_max_value/v_fetch_cnt); update Subscribers s,tmp_Subscribers_01 t set s.LastAccessTimeStamp=1420066800 where s.MSISDN=t.MSISDN and t.id >

V_max_value and t.id grant alter routine on fire.* to neo

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Create a calling stored procedure in the mysql client

MariaDB [test] > delimiter / /

MariaDB [test] > create procedure simpleproc (out param1 int)

-> begin

-> select count (*) into param1 from t

-> end//

Query OK, 0 rows affected (0.12 sec)

MariaDB [test] > delimiter

MariaDB [test] > CALL simpleproc (@ a)

Query OK, 1 row affected (0.08 sec)

MariaDB [test] > select @ a

+-+

| | @ a |

+-+

| | 1 |

+-+

1 row in set (0.00 sec)

When calling, if the parameter does not take @, the following error will be reported

Mysql > call proc_test (a for routine test.proc_test is not a variable or NEW pseudo-variable in BEFORE trigger b); ERROR 1414 (42000): OUT or INOUT argument 1 for routine test.proc_test is not a variable or NEW pseudo-variable in BEFORE trigger

View the status of the stored procedure MariaDB [test] > show procedure status like 'simpleproc'\ G

* * 1. Row *

Db: test

Name: simpleproc

Type: PROCEDURE

Definer: root@localhost

Modified: 2016-07-01 08:16:20

Created: 2016-07-01 08:16:20

Security_type: DEFINER

Comment:

Character_set_client: utf8

Collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)

View information about stored procedures through the view of information_schema

MariaDB [test] > select * from information_schema.routines where routine_name='simpleproc'\ G

* * 1. Row *

SPECIFIC_NAME: simpleproc

ROUTINE_CATALOG: def

ROUTINE_SCHEMA: test

ROUTINE_NAME: simpleproc

ROUTINE_TYPE: PROCEDURE

DATA_TYPE:

CHARACTER_MAXIMUM_LENGTH: NULL

CHARACTER_OCTET_LENGTH: NULL

NUMERIC_PRECISION: NULL

NUMERIC_SCALE: NULL

DATETIME_PRECISION: NULL

CHARACTER_SET_NAME: NULL

COLLATION_NAME: NULL

DTD_IDENTIFIER: NULL

ROUTINE_BODY: SQL

ROUTINE_DEFINITION: begin

Select count (*) into param1 from t

End

EXTERNAL_NAME: NULL

EXTERNAL_LANGUAGE: NULL

PARAMETER_STYLE: SQL

IS_DETERMINISTIC: NO

SQL_DATA_ACCESS: CONTAINS SQL

SQL_PATH: NULL

SECURITY_TYPE: DEFINER

CREATED: 2016-07-01 08:16:20

LAST_ALTERED: 2016-07-01 08:16:20

SQL_MODE: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

ROUTINE_COMMENT:

DEFINER: root@localhost

CHARACTER_SET_CLIENT: utf8

COLLATION_CONNECTION: utf8_general_ci

DATABASE_COLLATION: utf8_general_ci

1 row in set (0.00 sec)

View the definition of a stored procedure

MariaDB [test] > show create procedure simpleproc\ G

* * 1. Row *

Procedure: simpleproc

Sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Create Procedure: CREATE DEFINER= `root` @ `localhost` PROCEDURE `simpleproc` (out param1 int)

Begin

Select count (*) into param1 from t

End

Character_set_client: utf8

Collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

1 row in set (0.00 sec)

Create function

The default value cannot be set for the incoming parameters of MySQL, otherwise an error will be reported.

Mysql > delimiter $$

Mysql > CREATE FUNCTION format_selectQuery (THE_TABLE_NAME VARCHAR 75), THE_COLUMNS_NAME VARCHAR 75, THE_CONDITION VARCHAR 75 DEFAULT NULL) RETURNS VARCHAR 200DETERMINISTIC

-> BEGIN

-> / * SELECT concat ('WHERE', THE_CONDITION) INTO @ WHERE_CLAUSE

/ * > IF THE_CONDITION IS NULL THEN

/ * > SET @ WHERE_CLAUSE = NULL

/ * > END IF

/ * > RETURN concat ('SELECT', THE_COLUMNS_NAME, 'FROM', THE_TABLE_NAME, @ WHERE_CLAUSE); * /

-> RETURN 1

-> END $$

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT') RETURNS VARCHAR (42000) DETERMINISTIC

BEGIN

RETURN 1

END' at line 1

Mysql > delimiter

It is important to note that in MySQL, the return keyword after the function declaration in the creation function is RETURNS

Execution function

It should be noted that the declaration order of parameters in functions or stored procedures is incorrect, which can easily lead to syntax errors.

Delimiter $$

CREATE FUNCTION is_ChangeDescColumnExist (THE_VERSION_LEVEL_TABLE_NAME VARCHAR (35)) RETURNS INT DETERMINISTIC

BEGIN

/ * declare variables * /

DECLARE nbr INT

/ * declare exception * /

DECLARE CONTINUE HANDLER FOR NOT FOUND set nbr = 1

/ * assign values to variables * /

SET nbr = 0

Select count (*) into nbr from information_schema.columns where table_name = THE_VERSION_LEVEL_TABLE_NAME and

(column_name = 'ClearCHANGEN description')

IF nbr = 1 THEN

RETURN 1

ELSE

RETURN 0

END IF

END$$

Delimiter

Putting the above order in the wrong order will cause an error

Mysql > CREATE FUNCTION is_ChangeDescColumnExist (THE_VERSION_LEVEL_TABLE_NAME VARCHAR (35)) RETURNS INT DETERMINISTIC

-> BEGIN

-> DECLARE nbr INT

-> SET nbr = 0

-> DECLARE CONTINUE HANDLER FOR NOT FOUND set nbr = 10

->

-> select count (*) into nbr from information_schema.columns where table_name = THE_VERSION_LEVEL_TABLE_NAME and

-> (column_name = 'ClearCHANGEN description')

-> IF nbr = 1 THEN

-> RETURN 1

-> ELSE

ELSE ELSEIF

-> ELSE

-> RETURN 0

-> END IF

-> END$$

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE CONTINUE HANDLER FOR NOT FOUND set nbr = 10

Select c'at line 5

Create two functions that return two variables, one is the DECLARE variable and the other is the @ variable

Delimiter $$

CREATE FUNCTION is_TableMigrated (THE_VERSION_LEVEL_TABLE_NAME VARCHAR (20)) RETURNS INTEGER DETERMINISTIC

BEGIN

DECLARE nbr integer

SET nbr = 222,

RETURN nbr

END$$

Delimiter

Delimiter $$

CREATE FUNCTION is_TableMigrated (THE_VERSION_LEVEL_TABLE_NAME VARCHAR (20)) RETURNS INTEGER DETERMINISTIC

BEGIN

DECLARE nbr integer

SET @ nbr2 = 22222

RETURN @ nbr2

END$$

Delimiter

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.