BEGIN

Description

BEGIN may be used to initiate an anonymous block or a single transaction.

An anonymous block is a structure that can dynamically create and execute stored procedure code instead of permanently storing code as a database object in the database.

Precautions

None

Syntax

• Enable an anonymous block.

  1 2 3 4 5

  [DECLARE [declare_statements]] BEGIN execution_statements END; /

• Start a transaction.

  1 2 3 4 5 6 7

  BEGIN [ WORK | TRANSACTION ] [ { ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE | REPEATABLE READ } | { READ WRITE | READ ONLY } } [, ...] ];

Parameters

• declare_statements

  Declares a variable, including its name and type, for example, sales_cnt int.

• execution_statements

  Specifies the statement to be executed in an anonymous block.

  Value range: an existing function name

• WORK | TRANSACTION

  Specifies the optional keyword in the BEGIN syntax format without functions.

• ISOLATION LEVEL

  Specifies the transaction isolation level that determines the data that a transaction can view if other concurrent transactions exist.

  

  The isolation level of a transaction cannot be reset after the first clause (INSERT, DELETE, UPDATE, FETCH, or COPY) for modifying data is executed in the transaction.

  Value range:

  • READ COMMITTED: Only committed data can be read. This is the default action.
  • READ UNCOMMITTED: Committed data is probably read. This isolation level is provided to handle CN breakdown emergencies. On this isolation level, you are advised to only read data to prevent inconsistency.
  • REPEATABLE READ: Only the data committed before transaction start is read. Uncommitted data or data committed in other concurrent transactions cannot be read.
  • SERIALIZABLE: Currently, this isolation level is not supported. Setting this isolation level is equivalent to REPEATABLE READ.
• READ WRITE | READ ONLY

  Specifies the transaction access mode (read/write or read only).

Examples

• Start a transaction.

  -- Create a table and insert data into the table.
  gaussdb=# CREATE TABLE tbl_test1(col1 int, col2 int);
  gaussdb=# INSERT INTO tbl_test1 VALUES (1,1), (2,2), (3,3);
  
  -- Start a transaction in default mode.
  gaussdb=# BEGIN;
  INSERT INTO tbl_test1 VALUES (4,4);
  END;
  
  -- Start a transaction with the separation level being REPEATABLE READ.
  gaussdb=# BEGIN ISOLATION LEVEL REPEATABLE READ; 
  SELECT * FROM tbl_test1; 
  END;
  
  -- Drop the table.
  gaussdb=# DROP TABLE tbl_test1;

• Use anonymous blocks.

  1 2 3 4 5

  -- Generate a string using anonymous blocks. gaussdb=# BEGIN dbe_output.print_line('Hello'); END; /

Helpful Links

START TRANSACTION