Oracle PL/SQL Tutorial/PL SQL Programming/Code Block

A PL/SQL Block

SQL>
SQL>
SQL> CREATE TABLE myItemTable (
  2     item_code varchar2(6) PRIMARY KEY,
  3     item_descr varchar2(20) NOT NULL);
Table created.
SQL>
SQL>
SQL> DECLARE
  2    v_item_code VARCHAR2(6);
  3    v_item_descr VARCHAR2(20);
  4  BEGIN
  5    v_item_code :="sqle";
  6    v_item_descr :="a web site for Oracle";
  7    INSERT INTO myItemTable VALUES (v_item_code,v_item_descr);
  8  EXCEPTION WHEN OTHERS THEN
  9    dbms_output.put_line(SQLERRM);
 10  END;
 11  /
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
PL/SQL procedure successfully completed.
SQL>
SQL> select * from myItemTable;
no rows selected
SQL>
SQL> drop table myItemTable;
Table dropped.
SQL>
SQL>

Demonstrate nested PL/SQL blocks

SQL>
SQL> declare
  2     v_custno                NUMBER := 100;
  3  begin
  4     dbms_output.put_line(v_custno) ;
  5     declare
  6             v_state         CHAR(2):= "NY" ;
  7     begin
  8             dbms_output.put_line(v_custno || v_state) ;
  9     end ;
 10  end ;
 11  /
100
100NY
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

Inline procedure

SQL>
SQL>
SQL> DECLARE
  2     PROCEDURE myProc (dt IN VARCHAR2) IS
  3     BEGIN
  4        DBMS_OUTPUT.PUT_LINE (dt || " -> " || ADD_MONTHS (dt, 1));
  5     END;
  6  BEGIN
  7     myProc ("30-JAN-99");
  8     myProc ("27-FEB-99");
  9  END;
 10  /
30-JAN-99 -> 28-FEB-99
27-FEB-99 -> 27-MAR-99
PL/SQL procedure successfully completed.
SQL>
SQL>

Inner function

SQL>
SQL>
SQL>
SQL> create table employee (
  2  id                             number,
  3  employee_type_id                 number,
  4  external_id                    varchar2(30),
  5  first_name                     varchar2(30),
  6  middle_name                    varchar2(30),
  7  last_name                      varchar2(30),
  8  name                           varchar2(100),
  9  birth_date                     date  ,
 10  gender_id                      number );
Table created.
SQL>
SQL>
SQL>
SQL> create table gender (
  2  id                             number,
  3  code                           varchar2(30),
  4  description                    varchar2(80),
  5  active_date                    date          default SYSDATE  not null,
  6  inactive_date                  date );
Table created.
SQL>
SQL>
SQL>
SQL> insert into gender ( id, code, description ) values ( 1, "F", "Female" );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 2, "M", "Male" );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 3, "U", "Unknown" );
1 row created.
SQL>
SQL> create table employee_type (
  2  id                             number,
  3  code                           varchar2(30),
  4  description                    varchar2(80),
  5  active_date                    date          default SYSDATE  not null,
  6  inactive_date                  date );
Table created.
SQL>
SQL> insert into employee_type(id,code,description)values(1,"C","Contractor" );
1 row created.
SQL> insert into employee_type(id,code,description)values(2,"E","Employee" );
1 row created.
SQL> insert into employee_type(id,code,description)values(3,"U","Unknown" );
1 row created.
SQL>
SQL>
SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
  2  n_count                               number := 0;
  3
  4  FUNCTION add_worker(
  5  aiv_first_name                        employee.first_name%TYPE,
  6  aiv_middle_name                       employee.middle_name%TYPE,
  7  aiv_last_name                         employee.last_name%TYPE,
  8  aid_birth_date                        employee.birth_date%TYPE,
  9  aiv_gender_code                       gender.code%TYPE,
 10  aiv_employee_type_code                employee_type.code%TYPE)
 11  return                                number is
 12
 13  v_name                                employee.name%TYPE;
 14
 15  begin
 16    v_name        := rtrim(aiv_last_name||", "||aiv_first_name||" "||aiv_middle_name);
 17    begin
 18      insert into employee (
 19             id,
 20             employee_type_id,
 21             external_id,
 22             first_name,
 23             middle_name,
 24             last_name,
 25             name,
 26             birth_date,
 27             gender_id )
 28      select 1,
 29             myCursor.id,
 30             lpad(to_char(1), 9, "0"),
 31             aiv_first_name,
 32             aiv_middle_name,
 33             aiv_last_name,
 34             v_name,
 35             aid_birth_date,
 36             c2.id
 37      from   employee_type myCursor,
 38             gender c2
 39      where  myCursor.code = aiv_employee_type_code
 40      and    c2.code = aiv_gender_code
 41      and not exists (
 42        select 1
 43        from   employee x
 44        where  x.name       = v_name
 45        and    x.birth_date = aid_birth_date
 46        and    x.gender_id  = c2.id );
 47
 48      return sql%rowcount;
 49    exception
 50      when OTHERS then
 51        raise_application_error(-20001, SQLERRM||" on insert employee"||" in add_worker");
 52    end;
 53  end add_worker;
 54
 55  begin
 56    n_count := n_count + add_worker("JOHN",   "J.", "DOE", to_date("19800101", "YYYYMMDD"), "M", "C");
 57    n_count := n_count + add_worker("JANE",   "J.", "DOE", to_date("19800101", "YYYYMMDD"), "F", "E");
 58    n_count := n_count + add_worker("JOHNNY", "E.", "DOE", to_date("19980101", "YYYYMMDD"), "M", "E");
 59    n_count := n_count + add_worker("JANIE",  "E.", "DOE", to_date("19980101", "YYYYMMDD"), "F", "E");
 60    DBMS_OUTPUT.PUT_LINE(to_char(n_count)||" row(s) inserted.");
 61  end;
 62  /
4 row(s) inserted.
PL/SQL procedure successfully completed.
SQL>
SQL> drop table gender;
Table dropped.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL> drop table employee_type;
Table dropped.
SQL>

Inner procedure in an anonymous function

SQL>
SQL> create table t ( x varchar2(30) );
Table created.
SQL>
SQL>
SQL> declare
  2      procedure method1( p_data in varchar2 )
  3      is
  4      begin
  5          execute immediate "insert into t(x) values(:x)"
  6          using p_data;
  7      end method1;
  8
  9      procedure method2( p_data in varchar2 )
 10      is
 11      begin
 12          execute immediate "insert into t(x) values( """ ||replace( p_data,"""", """""" ) || """ )";
 13      end method2;
 14  begin
 15      for i in 1 .. 10000
 16      loop
 17          method1( "row " || I );
 18      end loop;
 19
 20      for i in 1 .. 10000
 21      loop
 22          method2( "row " || I );
 23      end loop;
 24
 25  end;
 26  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL>

Select the first names for the Doe family from the Worker table.

SQL>
SQL>
SQL> create table employee (
  2  id                             number,
  3  employee_type_id               number,
  4  external_id                    varchar2(30),
  5  first_name                     varchar2(30),
  6  middle_name                    varchar2(30),
  7  last_name                      varchar2(30),
  8  name                           varchar2(100),
  9  birth_date                     date  ,
 10  gender_id                      number );
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> set serveroutput on size 1000000;
SQL>
SQL> declare
  2
  3  v_first_name                          employee.first_name%TYPE;
  4  n_id                                  employee.id%TYPE;
  5
  6  FUNCTION get_first_name(
  7  aion_id                        in out employee.id%TYPE,
  8  aiv_last_name                  in     employee.last_name%TYPE)
  9  return                                employee.first_name%TYPE is
 10
 11  v_first_name                          employee.first_name%TYPE;
 12
 13  begin
 14    select id,first_name
 15    into   aion_id,v_first_name
 16    from   employee
 17    where  id > aion_id
 18    and    last_name like aiv_last_name||"%"
 19    and    rownum = 1;
 20
 21    return v_first_name;
 22  exception
 23    when NO_DATA_FOUND then
 24      return v_first_name;
 25    when OTHERS then
 26      raise_application_error(-20001, SQLERRM||" on select employee"||" in show_worker");
 27  end get_first_name;
 28
 29  begin
 30    n_id := 0;
 31    loop
 32      v_first_name := get_first_name(n_id, "DOE");
 33      if v_first_name is NULL then
 34        exit;
 35      end if;
 36      DBMS_OUTPUT.PUT_LINE(v_first_name);
 37    end loop;
 38  end;
 39  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> drop table employee;
Table dropped.

the forward slash on a line by itself says execute this procedure

SQL>
SQL> declare
  2    /*
  3     declare local cursors, variables, and methods here,
  4     but you don"t need to have a declaration section.
  5    */
  6  begin
  7    -- You write your logic here
  8
  9    null;  -- Ahhh, you"ve got to have at least one command!
 10  exception
 11    when NO_DATA_FOUND then
 12      raise_application_error(-20000, "Hey, No Data Found!");
 13  end;
 14  /
PL/SQL procedure successfully completed.
SQL>

This is an anonymous procedure, so it has no name

SQL> declare
  2    /*
  3     declare local cursors, variables, and methods here,
  4     but you don"t need to have a declaration section.
  5    */
  6  begin
  7    -- You write your logic here
  8
  9    null;  -- Ahhh, you"ve got to have at least one command!
 10  exception
 11    when NO_DATA_FOUND then
 12      raise_application_error(-20000, "Hey, No Data Found!");
 13  end;
 14  /
PL/SQL procedure successfully completed.
SQL>

Uses a PL/SQL Nested Block

SQL>
SQL>
SQL> CREATE TABLE myItemTable (
  2     item_code varchar2(6) PRIMARY KEY,
  3     item_descr varchar2(20) NOT NULL);
Table created.
SQL>
SQL>
SQL>
SQL> DECLARE
  2    v_item_code VARCHAR2(6);
  3    v_item_descr VARCHAR2(20);
  4    v_num NUMBER(1);
  5  BEGIN
  6
  7    v_item_code :="sqle";
  8
  9    v_item_descr :="a website for Oracle";
 10
 11    BEGIN
 12
 13      SELECT 1 INTO v_num FROM myItemTable WHERE item_code =v_item_code;
 14
 15    EXCEPTION
 16
 17      WHEN NO_DATA_FOUND THEN
 18        v_num :=0;
 19
 20      WHEN OTHERS THEN
 21        dbms_output.put_line("Error in SELECT:"||SQLERRM);
 22        RETURN;
 23    END;
 24
 25    IF (v_num =0)THEN
 26
 27      INSERT INTO myItemTable VALUES (v_item_code,v_item_descr);
 28
 29    END IF;
 30    dbms_output.put_line("Successful Completion");
 31  EXCEPTION WHEN OTHERS THEN
 32    dbms_output.put_line(SQLERRM);
 33  END;
 34  /
Successful Completion
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from myItemTable;
ITEM_C ITEM_DESCR
------ --------------------
sqle a website for Oracle
1 row selected.
SQL>
SQL> drop table myItemTable;
Table dropped.
SQL>
SQL>