START TRANSACTION inside BEGIN ... Context END or external and LOOP syntax

Question

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

FIRST:

There are two notes in the MySQL manual:

Note

Inside all saved programs, the parser considers BEGIN [WORK] as the beginning of the BEGIN ... END block. To start a transaction in this context, use START TRANSACTION instead.

Note

In all stored programs (stored procedures and functions, triggers, and events), the parser considers BEGIN [WORK] as the beginning of BEGIN ... END. Start the transaction in this context with START Instead, the transaction.

I don’t understand what exactly is meant. Do 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 correct, 1st option or 2nd option?

SECOND:

I do not want to create a stored procedure or function. I just want to create a Compound-Statement block with a loop inside it in a common thread, for example:

 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 such a structure possible? Because I have an error:

 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:

• Is it allowed to use BEGIN...END only in a common thread without creating and using stored procedures or functions?

• Is it allowed to use BEGIN...END inside START TRANSACTION...COMMIT or do I need to put START TRANSACTION...COMMIT inside BEGIN...END ?

   BEGIN START TRANSACTION COMMIT END // vs. START TRANSACTION BEGIN END COMMIT 

• Should I use BEGIN...END if I want to use only LOOP ? Is it possible to use LOOP syntax without running BEGIN...END ? The only example in the manual for LOOP is the following:

    CREATE PROCEDURE doiterate(p1 INT) BEGIN label1: LOOP ... 

Answer 1

• Is it allowed to use BEGIN ... END only in a common thread without creating and using stored procedures or functions?

  No: compound statements can only be used in the body of saved programs.

• Is it allowed to use BEGIN...END inside START TRANSACTION...COMMIT or do I need to put START TRANSACTION...COMMIT inside BEGIN...END ?

  START TRANSACTION; and COMMIT; are separate operators. If you want the body of the stored program to contain several statements, you will need to enclose these instructions in some composite statement block, for example BEGIN ... END (which is similar to including an expression block in curly braces { ... } within C- similar language).

  However, you could have a saved program containing only one START TRANSACTION; statement START TRANSACTION; or COMMIT; - such a program would not require any compound block of operators and would simply start a new / commit current transaction, respectively.

  Outside of a saved program, where blocks of compound statements are not allowed, you can issue START TRANSACTION; commands START TRANSACTION; and COMMIT; as and when required.

• Should I use BEGIN...END if I want to use only LOOP ? Can I just use the LOOP syntax without starting BEGIN...END ?

  LOOP also a compound statement block that is valid only in a stored procedure. There is no need to enclose the LOOP block in the BEGIN ... END block, although this is usual (since otherwise it is difficult to complete any required loop initialization).

In your case, when you apparently want to insert data into a table from a loop, you will need:

• identify the saved program in which you use LOOP ;

• repeat the loop in an external program that executes database queries at each iteration; or

• redefine your logic in terms of sets that SQL can work directly on.