START TRANSACTION inside BEGIN ... END context or outside and LOOP syntax

Question

I have two questions about Compound-Statement and Transactions in MySQL.

FIRST:

There are two notes in MySQL Manual:

Note

Within all stored programs, the parser treats BEGIN [WORK] as the beginning of a BEGIN ... END block. To begin a transaction in this context, use START TRANSACTION instead.

Note

Within all stored programs (stored procedures and functions, triggers, and events), the parser treats BEGIN [WORK] as the beginning of a BEGIN ... END block. Begin a transaction in this context with START TRANSACTION instead.

I can't understand what exactly is meant. They mean that I have to put START TRANSACTION instead of BEGIN or right after BEGIN?

// 1st variant:

BEGIN
   START TRANSACTION
   COMMIT
END


// 2nd variant:

START TRANSACTION
COMMIT
END

Which one is the right way, 1st variant or 2nd variant?

SECOND:

I don't want to create a Stored Procedure or Function. I just want to create a Compound-Statement Block with a loop inside it in the general flow, like this:

USE 'someDb';
START TRANSACTION
   ... create table statement
   ... insert statement

// now I want to implement some insert/select statements using loop, I do as follows:

DELIMITER $
BEGIN
  SET @n = 1, @m = 2;
  lab1: LOOP

   ... some insert, select statements here

   END LOOP lab1;
END $
DELIMITER ;

END

COMMIT

Is it possible such kind of structure? Because I have an error thrown:

Query: BEGIN SET @n = 1, @m = 2; lab1: LOOP SELECT ...
Error Code: 1064
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 'SET @n = 1, @m = 2;
lab1: LOOP SELECT ...

My questions are:

Is it allowed to use BEGIN...END just in general flow without creating and using Stored Procedures or Functions?
Is it allowed to use BEGIN...END inside of START TRANSACTION...COMMIT or I have to put START TRANSACTION...COMMIT inside of BEGIN...END?
```
BEGIN
   START TRANSACTION
   COMMIT
END

// vs.

START TRANSACTION
   BEGIN
   END
COMMIT
```
Do I by all means have to use BEGIN...END if I want to use only LOOP? May I just use LOOP syntax without starting BEGIN...END? The only example in the manual for LOOP is this:
```
  CREATE PROCEDURE doiterate(p1 INT)
     BEGIN
       label1: LOOP
         ... 
```

eggyal eggyal · Accepted Answer · 2012-09-04T12:07:49

Is it allowed to use BEGIN...END just in general flow without creating and using Stored Procedures or Functions?

No: compound statements can only be used within the body of stored programs.
Is it allowed to use BEGIN...END inside of START TRANSACTION...COMMIT or I have to put START TRANSACTION...COMMIT inside of BEGIN...END?

START TRANSACTION; and COMMIT; are separate statements. If you want the body of a stored program to contain multiple statements, it will need to enclose those statements in some sort of compound statement block such as BEGIN ... END (which is similar to enclosing a block of statements in braces { ... } within a C-like language).

That said, you could have a stored program which contains only the single-statement START TRANSACTION; or COMMIT;—such a program would not require any compound statement block and would merely commence a new / commit the current transaction respectively.

Outside of a stored program, where compound statement blocks are not permitted, you can issue START TRANSACTION; and COMMIT; statements as & when required.
Do I by all means have to use BEGIN...END if I want to use only LOOP? May I just use LOOP syntax without starting BEGIN...END?

LOOP is also a compound statement block, which is only valid within a stored procedure. It is not necessary to enclose a LOOP block within a BEGIN ... END block, although it is usual (as otherwise it is difficult to perform any required loop initialisation).

In your case, where you apparently want to insert data into a table from a looping construct, you will either need to:

define a stored program in which you use LOOP;
iterate a loop in an external program that executes database queries on each iteration; or
redefine your logic in terms of sets upon which SQL can directly operate.

START TRANSACTION inside BEGIN ... END context or outside and LOOP syntax

1 Answers