MySQL import stored procedure under Linux syntax error, need to add delimiter in the file//

Source: Internet
Author: User
Tags mysql client mysql create mysql command line mysql import

http://my.oschina.net/zerotime/blog/113126

MySQL command line when you create a stored procedure, you first enter a delimiter

DELIMITER//
CREATE PROCEDURE proc ()
Begin
......
End
//
OK, you can create the completed.

Show stored procedure commands

Show create procedure stored procedure name;

Delete stored procedure commands

drop procedure stored procedure name;

Http://database.51cto.com/art/201011/235017.htm

MySQL command line use is the basic knowledge of the process of learning MySQL database, then how to use the MySQL command line to create a stored procedure?

Also tried some methods, but the first semicolon MySQL thought the statement ended, for example:

    1. CREATE PROCEDURE P7 (in B INTEGER (12))
    2. Begin
    3. Declare a INTEGER (12);
    4. Set a=5;
    5. INSERT into T VALUES (a);
    6. SELECT s1*a from T WHERE b<=s1;
    7. End

This stored procedure, created directly at the command line, in the third row you enter a semicolon after the error. Because MySQL thinks this statement is over, it's for MySQL.

    1. CREATE PROCEDURE P7 (in B INTEGER (12))
    2. Begin
    3. Declare a INTEGER (12);

Must be a false statement.

And then I looked at something, and I had to add a separator--..........

    1. DELIMITER//
    2. CREATE PROCEDURE P7 (in B INTEGER (12))
    3. Begin
    4. Declare a INTEGER (12);
    5. Set a=5;
    6. INSERT into T VALUES (a);
    7. SELECT s1*a from T WHERE b<=s1;
    8. End
    9. //

It's ok ...... This means that the//......//is executed as a statement, so MySQL receives the complete creation statement for this stored procedure.

http://zhuixue.iteye.com/blog/375353

Querying stored procedures in a database

Method One:

Select ' Name ' from Mysql.proc where db = ' your_db_name ' and ' type ' = ' PROCEDURE '

Method Two:

Show procedure status;

To view the creation code for a stored procedure or function

Show CREATE PROCEDURE Proc_name;
Show Create function Func_name;

Http://www.111cn.net/database/mysql/35817.htm

Method One: (direct query)

Select ' Specific_name ' from mysql.proc where db = ' your_db_name ' and ' type ' = ' procedure '

Method Two: (View all stored procedures in the database + content)

Show procedure status;

Method Three: (View the list of stored procedures in the current database)

Select Specific_name from Mysql.proc;

Method Four: (View the specific contents of a stored procedure)

Select Body from mysql.proc where specific_name = ' your_proc_name ';

To view the creation code for a stored procedure or function:

Show CREATE PROCEDURE Your_proc_name;
Show Create function Your_func_name;

To delete a stored procedure:

drop procedure Your_proc_name;

=============

http://543197.blog.51cto.com/533197/114141

Article reference: [Url]http://bbs.tarena.com.cn/viewthread.phptid=166328[/url]mysql stored procedure is a new feature that has been added since MySQL 5.0. The advantages of a stored procedure are a whole bunch. But the main thing is the execution efficiency and the SQL code encapsulation. In particular, the SQL Code encapsulation feature, if there is no stored procedure, when the external program accesses the database (for example, PHP), to organize a lot of SQL statements. Especially when business logic is complicated, a lot of SQL and conditions are mixed in PHP code, which makes people shudder. Now that you have a MySQL stored procedure, the business logic can encapsulate the stored procedure, which is not only easy to maintain, but also highly efficient to execute. One, MySQL create stored procedure
"Pr_add" is a simple MySQL stored procedure that has two input parameters of type int "A", "B", and returns the and of the two parameters.
drop procedure if exists pr_add;
--Calculate the sum of two numbers
CREATE PROCEDURE Pr_add
(
a int,
b int
)
Begin
declare c int;
If A is null then
Set a = 0;
End If;
If B is null and then
Set B = 0;
End If;
Set C = a + B;
Select C as Sum;
/*
return C; --cannot be used in a MySQL stored procedure. Return can only appear in the function.
*/
End
Second, call the MySQL stored procedure
Call Pr_add (10, 20);
Executes the mysql stored procedure with the stored procedure parameter as a MySQL user variable.
Set @a = 10;
Set @b = 20;
Call Pr_add (@a, @b);
Third, the MySQL stored procedure characteristic
The simple syntax for creating a MySQL stored procedure is:
Create procedure stored procedure name ()
(
[In|out|inout] Parameter datatype
)
Begin
MySQL statement;
End
MySQL stored procedure Parameters If you do not explicitly specify "in", "Out", and "InOut", the default is "in". In practice, we do not explicitly specify parameters that are "in".
1. The "()" after the name of the MySQL stored procedure is required, even if there is no parameter, "()"
2. MySQL stored procedure parameter, cannot add "@" before parameter name, for example: "@a int". The following create stored procedure syntax is wrong in MySQL (correct in SQL Server). The variables in the MySQL stored procedure do not need to be "@" before the variable name, although the MySQL client user variable should be added "@".
CREATE PROCEDURE Pr_add
(
@a int,--Error
b INT--Correct
)
3. The parameters of the MySQL stored procedure cannot specify a default value.
4. The MySQL stored procedure does not need to precede the procedure body with "as". The SQL Server stored procedure must be added with the "as" keyword.
CREATE PROCEDURE Pr_add
(
a int,
b int
)
As--error, MySQL does not need "as"
Begin
MySQL statement ...;
End
5. If the MySQL stored procedure contains more than one MySQL statement, the BEGIN END keyword is required.
CREATE PROCEDURE Pr_add
(
a int,
b int
)
Begin
MySQL statement 1 ...;
MySQL statement 2 ...;
End
6. At the end of each statement in the MySQL stored procedure, add a semicolon ";"
...
declare c int;
If A is null then
Set a = 0;
End If;
...
End
7. Comments in the MySQL stored procedure.
/*
This is a
Multi-line MySQL annotations.
*/
declare c int; --This is a single-line MySQL note (note--there must be at least one space)
If a is null and then # This is also a single-line MySQL comment
Set a = 0;
End If;
...
End
8. The "return" keyword cannot be used in a MySQL stored procedure.
Set C = a + B;
Select C as Sum;
/*
return C; --cannot be used in a MySQL stored procedure. Return can only appear in the function.
*/
End
9. When calling the MySQL stored procedure, you need to add "()" After the procedure name, even if there is no parameter, "()"
Call Pr_no_param ();
10. Because the MySQL stored procedure parameter does not have a default value, the parameter cannot be omitted when the MySQL stored procedure is called. can be substituted with null.
Call Pr_add (NULL);

===================================================

MySQL import stored procedure under Linux syntax error, need to add delimiter in the file//

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.