pl/sql is a block-structured language.A function, procedure, package, trigger or anonymous block contains a block structure, and we call all the parts contained in the anonymous block a block structure.
An anonymous block is defined as:
[ DECLAREdeclarations] BEGINstatements[ EXCEPTIONException-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.
A common mistake is to write a semicolon immediately after
BEGIN. This is incorrect and will result in a syntax error.
Anonymous blocks currently do not support the use of <<label>>.
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.
A single-line comment begins with -- and extends to the end of the line.
This example has three single-line 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; /
A multiline comment begins with /*,
ends with */, and can span multiple lines.
BEGIN
/*
IF 2 + 2 = 4 THEN
some_condition := TRUE;
END IF;
*/
NULL;
END;
/