MySQL创建存储过程语法和分隔符。

I am trying to create a stored procedure in MySQL using a delimiter like this:

我正在尝试使用这样的分隔符在MySQL中创建一个存储过程:

use am;

DELIMITER $$

CREATE PROCEDURE addfields()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE acc INT(16);
  DECLARE validId INT DEFAULT 0;

END $$

DELIMITER ;

It gives me an error:

它给了我一个错误:

#1304 - PROCEDURE addfields already exists

What is the proper syntax for making a stored procedure with a delimiter and dropping it if it exists first?

使用定界符创建存储过程并在它首先存在时删除它的正确语法是什么?

4 个解决方案

#1

Here is the sample MYSQL Stored Procedure with delimiter and how to call..

下面是带有分隔符的示例MYSQL存储过程以及如何调用。

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_user_login` $$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_user_login`(
  IN loc_username VARCHAR(255),
  IN loc_password VARCHAR(255)
)
BEGIN

  SELECT user_id,
         user_name,
         user_emailid,
         user_profileimage,
         last_update
    FROM tbl_user
   WHERE user_name = loc_username
     AND password = loc_password
     AND status = 1;

END $$

DELIMITER ;

and call by, mysql_connection specification and

并调用by, mysql_connection规范和

$loginCheck="call sp_user_login('".$username."','".$password."');";

it will return the result from the procedure.

它将返回过程的结果。

#2

Getting started with stored procedure syntax in MySQL:

Good programmers use the terminal, the GUI is making you soft in the middle. When you get used to it and memorize the commands, it is 5 times faster than any GUI. Productivity = success.

好的程序员使用终端，GUI让你在中间变得柔软。当您习惯并记住命令时，它比任何GUI都快5倍。生产力=成功。

1. Open a terminal and login to mysql like this:

1。打开终端，像这样登录到mysql:

el@apollo:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
mysql> 

2. Take a look to see if you have any procedures:

2。看看你是否有什么程序:

mysql> show procedure status;
+-----------+---------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db        | Name          | Type      | Definer | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-----------+---------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
|   yourdb  | sp_user_login | PROCEDURE | root@%  | 2013-12-06 14:10:25 | 2013-12-06 14:10:25 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+-----------+---------------+-----------+---------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)

I have one defined, you probably have none to start out.

我定义了一个，你可能一开始就没有。

3. Change to the database, delete it.

3所示。修改数据库，删除它。

mysql> use yourdb;
Database changed

mysql> drop procedure if exists sp_user_login;
Query OK, 0 rows affected (0.01 sec)

mysql> show procedure status;
Empty set (0.00 sec)

4. Ok so now I have no stored procedures defined. Make the simplest one:

4所示。现在我还没有定义存储过程。使最简单的一个:

mysql> delimiter //
mysql> create procedure foobar()
    -> begin select 'hello'; end//
Query OK, 0 rows affected (0.00 sec)

The // will communicate to the terminal when you are done entering commands for the stored procedure. the stored procedure name is foobar. it takes no parameters and should return "hello".

当您完成存储过程的命令时，//将与终端通信。存储过程名是foobar。它没有参数，应该返回“hello”。

5. See if it's there, remember to set back your delimiter!:

5。看看它是否在那里，记住要回退你的分隔符!

 mysql> show procedure status;
 -> 
 -> 

Gotcha! Why didn't this work? You set the delimiter to // remember? Set it back to ;

明白了!为什么不工作?您将分隔符设置为//还记得吗?把它放回去;

6. Set the delimiter back and look at the procedure:

6。将分隔符设置回，并查看过程:

mysql> delimiter ;
mysql> show procedure status;
+-----------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db        | Name   | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-----------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| yourdb    | foobar | PROCEDURE | root@localhost | 2013-12-06 14:27:23 | 2013-12-06 14:27:23 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+-----------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

7. Run it:

7所示。运行该程序:

mysql> call foobar();
+-------+
| hello |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Hello world complete, lets overwrite it with something better.

Hello world complete，让我们用更好的东西覆盖它。

8. Drop foobar, redefine it to accept a parameter, and re run it:

8。Drop foobar，重新定义它接受一个参数，并重新运行它:

mysql> drop procedure foobar;
Query OK, 0 rows affected (0.00 sec)

mysql> show procedure status;
Empty set (0.00 sec)

mysql> delimiter //
mysql> create procedure foobar (in var1 int)
    -> begin select var1 + 2 as result;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call foobar(5);
+--------+
| result |
+--------+
|      7 |
+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

Nice! We made a procedure that takes input, modifies it, and does output. Now lets do an out variable.

好了!我们做了一个接受输入、修改和输出的过程。现在我们来做一个out变量。

9. Remove foobar, Make an out variable, run it:

9。删除foobar，创建out变量，运行:

mysql> delimiter ;
mysql> drop procedure foobar;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql> create procedure foobar(out var1 varchar(100))
    -> begin set var1="kowalski, what's the status of the nuclear reactor?";
    -> end//
Query OK, 0 rows affected (0.00 sec)


mysql> delimiter ;
mysql> call foobar(@kowalski_status);
Query OK, 0 rows affected (0.00 sec)

mysql> select @kowalski_status;
+-----------------------------------------------------+
| @kowalski_status                                    |
+-----------------------------------------------------+
| kowalski, what's the status of the nuclear reactor? |
+-----------------------------------------------------+
1 row in set (0.00 sec)

10. Example of INOUT usage in MySQL:

10。MySQL中的INOUT用法示例:

mysql> select 'ricksays' into @msg;
Query OK, 1 row affected (0.00 sec)


mysql> delimiter //
mysql> create procedure foobar (inout msg varchar(100))
-> begin
-> set msg = concat(@msg, " never gonna let you down");
-> end//


mysql> delimiter ;


mysql> call foobar(@msg);
Query OK, 0 rows affected (0.00 sec)


mysql> select @msg;
+-----------------------------------+
| @msg                              |
+-----------------------------------+
| ricksays never gonna let you down |
+-----------------------------------+
1 row in set (0.00 sec)

Ok it worked, it joined the strings together. So you defined a variable msg, passed in that variable into stored procedure called foobar, and @msg was written to by foobar.

它成功了，它把弦连接在一起。因此，您定义了一个变量msg，将该变量传递到名为foobar的存储过程中，而@msg是由foobar编写的。

Now you know how to make stored procedures with delimiters. Continue this tutorial here, start in on variables within stored procedures: http://net.tutsplus.com/tutorials/an-introduction-to-stored-procedures/

现在您了解了如何使用分隔符创建存储过程。继续这里的教程，从存储过程中的变量开始:http://net.tutsplus.com/tutorials/an- tion/stordures/。

#3

Here is my code to create procedure in MySQL :

下面是我在MySQL中创建过程的代码:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `procedureName`(IN comId int)
BEGIN
select * from tableName 
         (add joins OR sub query as per your requirement)
         Where (where condition here)
END $$
DELIMITER ;

To call this procedure use this query :

要调用此过程，请使用以下查询:

call procedureName(); // without parameter
call procedureName(id,pid); // with parameter

Detail :

细节:

1) DEFINER : root is the user name and change it as per your username of mysql localhost is the host you can change it with ip address of the server if you are execute this query on hosting server.

1) DEFINER: root是用户名，根据您的用户名mysql localhost是主机，如果您在托管服务器上执行此查询，您可以使用服务器的ip地址对其进行修改。

Read here for more detail

详情请阅读这里

#4

Create Procedure Syntax in mysql:-

在mysql中创建过程语法:-

DELIMITER //

分隔符/ /

CREATE PROCEDURE user_rank(IN group_id int)

创建过程user_rank(在group_id int中)

BEGIN SET @total_like:=0, @rank:=0,@rnk :=0; select * from (select IF(@total_like = total_like, @rank:=@rank, @rank:=@rank + 1) rank, @rnk:=@rnk + 1 as rank_offset, @total_like:=total_like as total_likes, user_id, user_name, thumb from (SELECT like_count as total_like, like_counts.user_id, user_name, thumb from like_counts inner join class_grades ON (class_grades.user_id = like_counts.user_id) inner join class_groups ON (class_groups.class_id = user_grades.grade_id and class_groups.class_group_id = group_id ) where role_id = '8' and like_counts.status = '1' and country_code ='+91' group by like_counts.user_id order by total_like desc LIMIT 10 OFFSET 0) r) t, (SELECT @total_like:=0, @rank:=0, @rnk:=0) s;

开始设置@total_like:=0， @rank:=0，@rnk:=0;选择* from(选择IF(@total_like =total_like， @rank:=@rank:=@rank:=@rank + 1) rank， @rnk:=@rnk + 1作为rank_offset， @total_like:=total_like, user_id, user_name, thumb from (select like_count as total_like, like_count)。user_id, user_name，来自like_counts的内部连接class_grades ON (class_grades)。user_id = like_counts.user_id)在(class_groups)上加入class_groups。class_id = user_grades。grade_id class_groups。class_group_id = group_id)，其中role_id = '8'和like_counts = '8'。status =' 1'， country_code ='+91' group by like_counts。user_id order by total_like desc LIMIT 10 OFFSET 0) r) t， (SELECT @total_like:=0， @rank:=0， @rnk:=0) s;

END // DELIMITER ;

/ /分隔符;