The basic syntax of mysql stored procedure articles

分类:Database 2010-06-24 来源:CodeWeblog.com 人气:170

Learning the basic grammar of the main reference to the mysql User's Guide

Mysql database stored procedure needs to have proc Table. The table created during the MySQL 5.1 installation. If you are upgrading from an earlier version to MySQL 5.1, make sure to update your grant tables to ensure that the proc table exists. (Mysql database installed in the mysql database which has a proc table)

Create mysql stored procedure permissions problem

. Creating Stored Routines and CREATE ROUTINE privileges.
Remind or remove storage routines need ALTER ROUTINE privileges. The permission granted automatically subroutine founder.
* Implementation of the subroutine needs EXECUTE permissions. However, the rights automatically granted to the creator of subroutines. Similarly, the subroutine default SQL SECURITY characteristic is DEFINER, which allows users to access the database of the subroutine and the implementation of the subroutine linked together.

Stored procedures and functions is to use CREATE PROCEDURE and CREATE FUNCTION statement to create a subroutine. The current study only the CREATE PROCEDURE

In MySQL 5.1, a stored subprogram or a function associated with a particular database. Here are a few meanings:
* When a subroutine is called, an implicit USEdb_name be executed (when the subroutine to stop the implementation of the termination). USE statements within stored routines when not allowed.
* You can use the database name qualified subroutine name. This can be used to refer to a subroutine is not in the current database. For example, to refer to a database associated with the test procedure p or function stored f, you can say CALL test.p () or test.f ().
* Remove the database when it is associated with all the storage routines are also removed.

CREATE PROCEDURE basic syntax:
CREATE PROCEDURE sp_name ([proc_parameter [,...]])
[Characteristic ...] routine_body

characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| (CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA)
| SQL SECURITY (DEFINER | INVOKER)
| COMMENT 'string'

routine_body:
Valid SQL procedure statement or statements

Note: The specified parameter is IN, OUT, or INOUT is valid only PROCEDURE. (FUNCTION parameters are always considered to be IN parameters)

Compound statement BEGIN ... END
[Begin_label:] BEGIN
[Statement_list]
END [end_label]

Storage subroutine can use BEGIN ... END compound statement to include multiple statements. statement_list statement on behalf of one or more of the list. Each statement within statement_list must use a semicolon (;) to the end.
Begin_label exist unless otherwise end_label can not be given, and if both are present.

Stored procedure variables
1, DECLARE Local Variables
2, the variable SET statement
3, SELECT ... INTO statement

1.1 DECLARE var_name [,...] type [DEFAULT value]
This statement is used to declare local variables. To give the variable a default value, please include a DEFAULT clause. Value can be specified as an expression, do not need a constant. If no DEFAULT clause, the initial value of NULL.
Scope of local variables declared in it is within the BEGIN ... END block. It can be used in the nested block, in addition to those variables with the same name statement block.

2.1 Variable SET Statement
SET var_name = expr [, var_name = expr] ...

3.1 SELECT ... INTO statement
SELECT col_name [,...] INTO var_name [,...] table_expr
This SELECT syntax stores the selected columns directly into variables. Therefore, only a single line can be recovered.
SELECT id, data INTO x, y FROM test.t1 LIMIT 1;
Note that user variable names in the MySQL 5.1 is not case sensitive.

Important: SQL variable names and column names can not be the same. If such a SQL SELECT ... INTO statement contains a column reference, and includes a local variable the same name listed, MySQL currently interpreted to refer to a variable name. For example, in the following statement, xname be interpreted as reference to xnamevariable not to xnamecolumn of:
CREATE PROCEDURE sp1 (x VARCHAR (5))
BEGIN
DECLARE xname VARCHAR (5) DEFAULT 'bob';
DECLARE newname VARCHAR (5);
DECLARE xid INT;

SELECT xname, id INTO newname, xid
FROM table1 WHERE xname = xname;
SELECT newname;
END;
When this procedure is called, regardless of the value of table.xname out what the return value variable newname 'bob'.

分享到:
blog comments powered by Disqus

相关文章

  • Create a database with the MySQL database tables and 2010-12-31

    Learn some basic operation command, let us learn how to create a database and database tables. 1, using the SHOW statement to find out what currently exists on the server database: mysql> SHOW DATABASES; +----------+ | Database | +----------+ | my

  • Creating a database using the MySQL database and tables 2010-12-31

    Understand some of the most basic operation command, let us learn how to create a database and database tables. 1, use the SHOW statement to find out what currently exists on the server database: mysql> SHOW DATABASES; +----------+ | Database | +-

  • [Transfer] mysql database and backup and recovery 2010-11-10

    In the database table is missing or damaged, the backup of your database is very important. In the event of system crash, you'll want your table to the least possible loss of data back to the state when the crash occurred. Sometimes the damage is mysq

  • Ubuntu9.04 Mysql Database installation guide 2010-03-12

    1. Install Mysql Database 1.1 Automatic installation This installation thanks to apt-get command, and to see what can be as simple scary! Steps can be in the following order: ================================================== ======================== ...

  • How to backup Mysql database 2011-04-18

    mysqldump-u [user]-p [password] [databasename]> [dump_name] In the dump_name Copy this file to another computer on another computer first to enter the mysql The establishment of a database and then run the following command mysql-u [user]-p [passw

  • Twitter users jumped 20-fold wish to abandon the MySQL database 2010-04-02

    Twitter's an engineer named Ryan King has revealed to the blog MyNoSQL, the company plans to migrate to Cassandra from the MySQL database, because the latter has a greater flexibility, scalability, and a lot of social networking open-source devel ...

  • MySQL database file location for their specific brief 2011-07-13

    A, MySQL database file description Each database corresponds to MySQL's stored in a folder with the same name in the database, MySQL, including MySQL database files and MySQL database files built storage engine used to create the database file. 1, MyS

  • weblogic11 Germany under the mysql database configuration 2011-09-29

    This article describes the domain in weblogic 11.1 under the mysql connection pool configuration steps: 1 in the weblogic directory common \ lib, add the mysql driver mysql-connector-java-5.1.13-bin.jar 2 in the weblogic directory common \ bin to fin

  • Transfer: Flex + blazeds Implementation and mySQL database connection 2011-06-01

    Transfer from: http://xaajie.iteye.com/blog/375553 Several Download blazeds_turnkey_3-0-0-544.zip Download: http://download.macromedia.com/pub/opensource/blazeds/blazeds_turnkey_3-0-0-544.zip This small example of communication through remoteobject. Flex

  • bat command to the java implementation of import and export MySQL database 2010-12-30

    Action: public String exportCases () throws Exception {String flag = getRequest (). getParameter ("flag"); if (flag == null | | flag.equals ("")){ return "success";} String caseidBegin = getRequest ( ). getParameter (

iOS 开发

Android 开发

Python 开发

JAVA 开发

开发语言

PHP 开发

Ruby 开发

搜索

前端开发

数据库

开发工具

开放平台

Javascript 开发

.NET 开发

云计算

服务器

Copyright (C) codeweblog.com, All Rights Reserved.

CodeWeblog.com 版权所有 黔ICP备15002463号-1

processed in 0.143 (s). 14 q(s)