The basic syntax of mysql stored procedure articles

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'.
  • del.icio.us
  • StumbleUpon
  • Digg
  • TwitThis
  • Mixx
  • Technorati
  • Facebook
  • NewsVine
  • Reddit
  • Google
  • LinkedIn
  • YahooMyWeb

Related Posts of The basic syntax of mysql stored procedure articles

  • js page Jump implementation of a number of ways

    The first is: <script language="javascript" type="text/javascript"> window.location.href = "login.jsp? backurl =" + window.location.href; </ script> The second: <script language="javascript"> alert

  • Dynamic loading JS script four kinds of methods

    To achieve dynamic loading JS script has four kinds of methods: 1, direct document.write <script language="javascript"> document.write ( "<script src='test.js'> <\ / script>"); </ script> 2, dynamic scri

  • RoR explained

    ROR is Ruby on Rails. Ruby is a well-known has been very good dynamic language It's dynamic language. Simple and easy. Dynamic languages are interpreted, but the performance may make a discount, but not absolute, because the application is complex, th

  • Hibernate primary key strategy-sequence

    Today, the use of hibernate in the company encountered a troublesome problem, the use of hibernate when the primary key generation strategy set sequence, but always reported in the implementation could not get next sequence value of the error, then o ...

  • Memo hibernate problem

    hibernate using myeclipse automatically generated HBM mapping file will be schema ="***" catalog ="***" Separately are the database instance name and database name, but in fact has been stated jdbc url connection database, are the ...

  • Hibernate pessimistic locking mechanism for locking and optimistic locking

    hibernate lock mechanism 1. Pessimistic lock It refers to the modification of data by outsiders hold a conservative attitude. The assumption that at any time access to data, may also have another client to access the same data, in order to maintain t ...

  • Nan-Jing 5: When IBatis.Hibernate mixed affairs, pay attention to your SQL

    [Problem] Now, in the development of the so-called multi-storey JavaEE applications, data persistence layer is always essential, and "Automatic" of ORM - Hibernate, and "all-manual-type" of SqlMap - IBatis, equivalent data are Per ...

  • hibernate to use the principle of

    The use of hibernate, implementation of data persistence. Has the following several processes. One configuration database connection information. Hibernate.config 2 configuration mapping. 3 use: the use of the process are the following steps: 3.1: Ge ...

  • Based on JDBC, JPA Annotation achieve simple CRUD Generic Dao

    The origin of ideas are pretty long history of reasons: [Use iBATIS history] The use of iBATIS has been a long time, the system is to use the CRUD template tool to generate the code, although there are tools to generate, but looked at a lot of CRUD the Sq

  • Hibernate's lazy strategy

    hibernate Lazy strategy can be used in: <class> tag, it can be true / false Tags can <PROPERTY> values true / false type of necessary tools to enhance <set> <list> can tag values true / false / extra <many-to-one> <on ...

blog comments powered by Disqus
Recent
Recent Entries
Tag Cloud
Random Entries