pl/sql

1. anonymous block
      [declare]
           ...
      begin
           ...
           [exception]
               ...
      end;
      /                              // / is a signal to instruct sql client to execute pl/sql block

      --/                           // --/..../ is a signal to instruct dbvisualizer to execute pl/sql block
      begin
         null;
      end;
      /


2. declare variable
      declare
         v_last_name            varchar2(20);
         v_first_name           table_1.column_1%type;
         n_employee_id       number;
         d_hire_date             date;
         b_flag                      boolean;

         type tableType is table of existing_table%rowtype;      //define table based on existing
         t_my_table                 tableType ;

         r_my_row               existing_table%rowtype;
      begin
         v_first_name := 'Mary';
         v_last_name := 'Jane';
         d_hire_date := to_date('19700101','yyyymmdd');
         b_flag := false;
      end;


3. function
      create [or replace] function function_name (
         parameter_1 [in] [out] data_type,
         parameter_2 [in] [out] data_type,
         parameter_n [in] [out] data_type
      )
      return return_data_type IS
      ...                                                                       //declare
      begin
         ...                                                                    //execute
         return return_data_type;
         exception
         ...                                                                    //exception
      end;


4. call a function
      declare
        n_x number;
      begin
         n_x := try_parse('574');
         dbms_output.put_line(n_x);
      end;

or

      select try_parse('1234') from dual;


5. procedure

• no return value compared with function

      create or replace procedure [schema.]name[( parameter[, parameter...] ) ]
      is
      ...
      begin
        ...
        exception
        ...
      end [name];


6. call procedure
      exec procedure_name(param1,param2…paramn);


reference
1. pl/sql tutorial
2. pl/sql working with record
3. sql topics