PL/SQL Blocks


declare
        --<Variables, Cursors, user defined exception>
begin
        --<Sql statements>
        --<PL/SQL statements>
exception
When <exception name> then
        --<Actions to perform when errors, occur>
end;
EX:

declare
      v_variable varchar2(15);
begin
      select column_name
        into v_variable
          from table_name;
exception
    when others then
       null;
end;
create or replace procedure <procedure_name> as
             --<declare any variables here>
begin
       null;
end;
create or replace function <function_name> return <datatype> as
              --<declare any veriables here>
begin
         return <value>;
end;
Variable Types:

scalar (single value) -- varchar2, number, date, subtype
composite (records) -- record, plsql table
reference (pointer) -- ref cursor
LOB (large objects) -- BLOB, CLOB, LOB
Bind variables -- variables defined outside plsql block, ex: .NET, Java, sqlplus
Scalar data types:

Char, varchar2, long, number, binary_integer, boolean, date, timestamp, timestamp
with timezone, timestamp with local time zone
declare
str_job           varchar2(15);
int_count          binary_integer := 0;
n_tot_sal           number(9,2);
cons_num           constant number(3,2) := 8.25;
b_valid           boolean not null default true;
_name             employees.last_name%TYPE;
date_dt             date := sysdate+7;
subtype         type_my_varchar varchar2(100);
type_my_char         my_varchar;
EX:

declare
     n_deptno              number(4); --departments.department_no%TYPE
     n_location_id         number(4); --departments.locations_id%TYPE
begin
     select department_id, location_id
     into v_deptno, v_location_id
      from departments where department_name = 'Sales';
end;
Composite data types:

Records:

rec_tbl_based is table_name%ROWTYPE;
rec_cur_based is cur_name%ROWTYPE;

type type_rec_var_based is (field1 datatype1, field2 datatype2, .. )
rec_my_record type_rec_var_based

select col_name into rec_my_record.col_name from table_name;
select rec_my_record.col_name from dual;
EX:

declare
           rec_emp employees%ROWTYPE;
           n_employee_number number := 1024;
begin
      select * into emp_rec from employees
      where employee_id = n_employee_number;
      insert into retired_emps
       (emp_no, ename, job, mng, hire_date, leave_date, sql, comm, rept_no)
values
        (emp_rec.employee_id, emprec.lastname,...);
commit;
end;
EX:

declare
          type emp_record_type is record
(
       last_name varchar2(25),
         job_id varchar2(10),
          salary number(8,2)
);
           emp_record emp_record_type;
begin
           emp_record.last_name := 'ERGEM';
              dbms_output.put_line(emp_record.last_name);
end;
Plsql Tables:

type type_tbl_city_codes is table of varchar2(50);
type type_tbl_city_codes is table of t_city.code%TYPE;

type type_tbl_based_on_cur is table of type_rec_var_based
  [index by binary integer];

tbl_city_codes type_tbl_city_codes;

select code into tbl_city_codes(15) from t_city where city_id=15;
select tbl_city_codes(15) from dual;

type ename_table_type is table of employees.lastname%TYPE
    index by binary_integer;
ename_table ename_table_type;
EX:

declare
-- declare the table type
type ename_table_type is table of
      varchar2(100);--employees.last_name%TYPE index by binary integer;
 
type hiredate_table_type is table of
    date index by binary_integer;

-- declare the table type variable
  ename_table ename_table_type;
  hiredate_table hiredate_table_type;
begin
-- initialize the table by a call to the constructor
ename_table := ename_table_type();
-- extend the table
ename_table.extend(1);
-- assign a value
ename_table(1) := 'cameron';
-- indexed tables do not need initializing and extending
-- hiredate_table := hiredate_table_type();
-- hiredate_table.extend(10);
hiredate_table(8) := sysdate + 7;

if ename_table.exists(1) then
--insert into ...
        dbms_output.put_line(ename_table(1));
        dbms_output.put_line(hiredate_table(8));
end if;
end;
Pl/SQL table attributes:

Exists [(n)]
Count
First
Last
Prior [(n)]
Next [(n)]
Trim [(n)]    -- rename one element from end table
Delete [(n)]  -- remove elements
EX:

declare
        type emp_table_type is table of employees%ROWTYPE
           index by binary_integer;
   my_emp_table emp_table_type;
   v_count number(3) := 104;
begin
     for i in 10.. v_count loop
       select * into my_emp_table(i) from employees
         where employee_id = i;
end loop;
        for i in my_emp_table.first .. my_emp_table.last loop
        dbms_output.put_line (my_emp_table.lastname(i));
end loop;
end;
Variables in nested blocks:

declare
        d_birth_date date;
begin
        declare
           d_birth_date date;
      begin
      ...
Outer.d_birth_date:=to_Date(’03-NOV-2013’,’DD-MON-YYYY’);
end;
...
end;
EX:
<<outer>> -- label the outer block
Declare

n_sum_sal number(10,2);
n_dept_no number not null := 60;
--n_employee_id employees.employee_id%TYPE;
 
Begin

declare
        n_sum_sal number(10,2) := 1111;
begin

/*
select sum(salary) into outer.n_sum_sal
from employees
where department_id = n_dept_no;
*/
-- call to outer block variable
 
outer.n_sum_sal := 2532.22;

end;

dbms_output.put_line('The outer sum salary is: ' ||
to_char(n_sum_sal));

/*
select hire_date from employees
where employee_id = n_employee_id;
*/
end;

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: