An anonymous block is defined as:

[ DECLARE
    declarations ]
BEGIN
    statements
[ EXCEPTION 
    Exception-handling part ]
END;
/

Each declaration and each statement within a block is terminated by a semicolon. A block that appears within another block must have a semicolon after END, as shown above; however the final END that concludes a function body does too require a semicolon. where / is the end character of pl/sql lower block structure.

In anonymous blocks, support converting space to NUMERIC. Effective in Oracle compatibility mode.

For example, use an anonymous block to output a value of 'quantity':

DECLARE
  quantity integer := 80;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Quantity here is '||quantity);  -- Prints 80
END;
/

Before using the DBMS_OUTPUT.PUT_LINE function, you need to turn on the print permission.

 select dbms_output.serveroutput(true);

Of course, this function can only be used in Oracle compatibility mode.

Starting with LightDB version 23.4, anonymous blocks support binding variables when used with libpq. It should be noted that the parameter types needs to be passed to server from client. The number of binding variables is limited to 65535.

For example, use an anonymous block in libpq with variables:

/*
 * src/test/examples/lt_testlibpq.c
 *
 *
 * lt_testlibpq.c
 *		this test program shows to use LIBPQ to exec dostmt with bind param
 *
 */
#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"

static void
exit_nicely(PGconn *conn)
{
    PQfinish(conn);
    exit(1);
}

int
main(int argc, char **argv)
{
    const char *conninfo;
    PGconn     *conn;
    PGresult   *res;
    const char *paramValues[10];
    Oid paramTypes[10];
	int			nFields;
	int			i,
				j;

    if (argc > 1)
        conninfo = argv[1];
    else
        conninfo = "dbname = postgres";

    
    conn = PQconnectdb(conninfo);

    
    if (PQstatus(conn) != CONNECTION_OK)
    {
        fprintf(stderr, "Connection to database failed: %s",
                PQerrorMessage(conn));
        exit_nicely(conn);
    }


    res = PQexec(conn, "create table test_dostmt(key1 int, key2 text);");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    PQclear(res);

    res = PQexec(conn, "insert into test_dostmt values(1,'a');");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    PQclear(res);

    paramTypes[0] = 23;
    paramValues[0] = "1";

    printf("update test_dostmt key2 to 'test_dostmt'\n");
    res = PQexecParams(conn, "begin\
                 begin\
                        update test_dostmt set key2 =  'test_dostmt'  where key1 =  $1  ;\
                 end;\
         end;", 1, paramTypes, paramValues, NULL, NULL, 0);

    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    PQclear(res);

    printf("get test_dostmt key2 \n");
    res = PQexec(conn, "select key2 from test_dostmt where key1=1;");
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

	nFields = PQnfields(res);

	/* print out the instances */
	for (i = 0; i < PQntuples(res); i++)
	{
		for (j = 0; j < nFields; j++)
			printf("%s: %s", PQfname(res, j), PQgetvalue(res, i, j));
		printf("\n");
	}

    PQclear(res);


    printf("update test_dostmt key2 to 'test_dostmt_new'\n");
    paramTypes[0] = 23;
    paramValues[0] = "1";
    paramTypes[1] = 25;
    paramValues[1] = "test_dostmt1";
    res = PQexecParams(conn, " \
                declare\
                    id int := $1; \
                    val text := $2; \
                begin\
                    begin\
                        val = val || '_new'; \
                        update  test_dostmt set key2 = val where key1 = id  ;\
                    end;\
         end;", 2, paramTypes, paramValues, NULL, NULL, 0);

    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    PQclear(res);

    printf("get test_dostmt key2 \n");
    res = PQexec(conn, "select key2 from test_dostmt where key1=1;");
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }


	nFields = PQnfields(res);
	/* print out the instances */
	for (i = 0; i < PQntuples(res); i++)
	{
		for (j = 0; j < nFields; j++)
			printf("%s: %s", PQfname(res, j), PQgetvalue(res, i, j));
		printf("\n");
	}

    PQclear(res);


    res = PQexec(conn, "drop table test_dostmt;");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "SET failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    PQclear(res);

    PQfinish(conn);

    return 0;
}

The pl/sql compiler ignores comments. Their purpose is to help other application developers understand your source text. Typically, you use comments to describe the purpose and use of each code segment. You can also disable obsolete or unfinished pieces of code by turning them into comments.

DECLARE
  howmany     NUMBER;
  num_tables  NUMBER;
BEGIN
  -- Begin processing
  SELECT COUNT(*) INTO howmany
  FROM USER_OBJECTS
  WHERE OBJECT_TYPE = 'TABLE'; -- Check number of tables
  num_tables := howmany;       -- Compute another value
END;
/

BEGIN
  /*
  IF 2 + 2 = 4 THEN
    some_condition := TRUE;
  END IF;
  */
  NULL;
END;
/