Introduction to Anonymous Blocks

PL/SQL, the Oracle procedural extension of SQL, is a portable, high-performance transaction-processing language that is tightly integrated with SQL. The basic unit of a PL/SQL source program is the block, which groups related declarations and statements.

A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. Only the executable part is required.

begin
   dbms_output.put_line('hello world');
end;
/

Because an anonymous block can have its own declaration and exception sections, developers can use anonymous blocks to provide scope for identifiers and exception handling within a larger program.

declare
    l_today date := sysdate;
begin
    dbms_output.put_line(
           'today is '||to_char(l_today,'Day'));
exception when others then
    dbms_output.put_line(sqlerrm);
end;
/

Conditional Logic

Lets add some conditional logic using if ... then ... else syntax. The TO_CHAR function can be used to format dates and number data types. The D format mask is the day of the week, a number between 1 and 7.

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;
/

Static SQL

With PL/SQL it is easy to integrate SQL statements, the example below shows a an example of using SELECT INTO with an Oracle data dictionary table.

DECLARE
  howmany     INTEGER;
  num_tables  INTEGER;
BEGIN
  -- Begin processing
  SELECT COUNT(*) INTO howmany
  FROM USER_OBJECTS
  WHERE OBJECT_TYPE = 'TABLE'; -- Check number of tables
  num_tables := howmany;       -- Compute another value
  dbms_output.put_line (to_char(num_tables,'999G999G990')||' tables');
END;

Here is an example of using a cursor for loop

DECLARE
  l_table_count integer := 0;
BEGIN
  for c1 in (
  SELECT table_name
  FROM USER_TABLES
  order by 1) loop
      l_table_count := l_table_count + 1;
      dbms_output.put_line(c1.table_name);
  end loop;
  if l_table_count = 0 then
     dbms_output.put_line('You have no tables in your schema');
  end if;
END;

Dynamic SQL

Lets extend our static SQL example to include some dynamic content. To facilitate dynamic SQL passing a value back to the PL/SQL block we will create a package named "PKG".

CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
  table_row_count integer;
END;
/

DECLARE
  l_table_count integer := 0;
  l_sql         varchar2(32767);
BEGIN
  for c1 in (
  SELECT table_name
  FROM USER_TABLES
  order by 1) loop
      l_table_count := l_table_count + 1;
      l_sql := 'begin select count(*) into pkg.table_row_count from "'||c1.table_name||'"; end;';
      execute immediate l_sql;
      dbms_output.put_line(c1.table_name||' - '||to_char(pkg.table_row_count,'999G999G990')||' rows');
  end loop;
  if l_table_count = 0 then
     dbms_output.put_line('You have no tables in your schema');
  end if;
END;
/