Anonymous blocks are essential part of PL/SQL and plpgsql. They are mostly used in any database to unit test code, debugging issues or testing any business logic.
A block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END in both Oracle and PostgreSQL. These keywords divide the block into a declarative part, an executable part, and an exception-handling part.
Below shows the syntax of a block. A block must have the executable part and rest all are optional.
[ <<label>> ]
[ DECLARE
declarations
... ]
BEGIN
statements;
...
[ EXCEPTION
statements;
... ]
END [ label ];The minimum block contains the BEGIN section with at least one executable statement. The executable statement can be NULL.
BEGIN
statement;
END;Executing block
You can execute a block in PL/SQL as below.
BEGIN
NULL;
END;
/DO executes an anonymous code block. The syntax to execute a block is as below.
DO [ LANGUAGE lang_name ] plpgsqlblock
or
DO plpgsqlblock [ LANGUAGE lang_name ]DO $$
BEGIN
NULL;
END $$;Specifying LANGUAGE is optional and you can include/exclude in any plpgsql block.
DO LANGUAGE plpgsql
$$
BEGIN
NULL;
END $$;LANGUAGE can be specified after DO or at the end.
DO $$
BEGIN
NULL;
END; $$
LANGUAGE plpgsql;Converting an Anonymous block
Anonymous blocks are never stored in either Oracle or PostgreSQL database. They are parsed each time you execute an anonymous block.
SET SERVEROUTPUT ON;
DECLARE
l_today DATE := sysdate;
BEGIN
IF to_char(l_today,'D') < 4 THEN
dbms_output.put_line(
'Have a wonderful week');
ELSE
dbms_output.put_line(
'Enjoy the rest of the week');
END IF;
dbms_output.put_line('today is '||
to_char(l_today,'Day')||
' day '||to_char(l_today,'D')||
' of the week.');
END;
/
today is Sunday day 1 of the week.
PL/SQL procedure successfully completed.
DO $$
DECLARE
l_today DATE := current_date;
BEGIN
IF to_char(l_today,'D') < 4 THEN
raise notice 'Have a wonderful week';
ELSE
raise notice 'Enjoy the rest of the week';
END IF;
raise notice 'today is % day % of the week.',
to_char(l_today,'Day'),
to_char(l_today,'D');
END; $$
LANGUAGE plpgsql;
LINE 1: SELECT to_char(l_today,'D') < 4
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT to_char(l_today,'D') < 4
CONTEXT: PL/pgSQL function inline_code_block line 5 at IF
Adding explicit type casting to the above code.
DO $$
DECLARE
l_today DATE := current_date;
BEGIN
IF to_char(l_today,'D')::INT < 4 THEN
raise notice 'Have a wonderful week';
ELSE
raise notice 'Enjoy the rest of the week';
END IF;
raise notice 'today is % day % of the week.',
to_char(l_today,'Day'),
to_char(l_today,'D');
END; $$
LANGUAGE plpgsql;
NOTICE: Have a wonderful week
NOTICE: today is Sunday day 1 of the week.
DO