//
you're reading...
Oracle PL/SQL

Oracle PL/SQL Examples

--Turn on the server output

/**example**/  
set serveroutput on;
show serveroutput;  
  
set verify off;  
show verify; 


--The transfer of values in the database
declare  
  emp_first_name varchar2(30);  
  emp_last_name varchar2(30);  
  emp_phone varchar2(30);  
begin  
  select first_name,last_name,phone_number into emp_first_name,emp_last_name,emp_phone from it_employees where employee_id=&v_employee_id;  
  DBMS_OUTPUT.PUT_LINE('The information of this employee is '||emp_first_name||'  '||emp_last_name||'  '||emp_phone);  
exception  
  when no_data_found then dbms_output.put_line('There is not any information for this employee!');  
end;

--Substitution variables
declare  
  input_value number:=&v_input;  
  output_result number;  
begin  
  output_result:=power(input_value, 2);  
  DBMS_OUTPUT.PUT_LINE(output_result);  
end;

--Circular area
declare  
  radius number:=&v_radius;  
  pai constant number:=3.14;  
  area number;  
begin  
  area:=power(radius,2)*pai;  
  DBMS_OUTPUT.PUT_LINE('The area is '||area);  
end; 

--Output System Time
declare   
  v_day varchar2(20);  
begin  
  v_day:=to_char(sysdate,'Day, HH24:MI');  
  DBMS_OUTPUT.PUT_LINE('Today is '||v_day);  
end;  

--Nested statement blocks and tags  
<< outer_block >>  
declare  
  num_test number:=123;  
begin  
  DBMS_OUTPUT.PUT_LINE('outer_block:'||num_test);  
  << inner_block >>  
  declare  
    num_test number:=345;  
  begin  
    DBMS_OUTPUT.PUT_LINE('inner_block:'||num_test);  
   DBMS_OUTPUT.PUT_LINE('in inner_block call outer_block:'||outer_block.num_test);  
  END inner_block;  
end outer_block;

--Variable declarations and nested IF statements
declare  
  course_name varchar2(30);  
  num number(8,2);  
  room_num constant varchar2(10):='603D';  
  check_res BOOLEAN:=true;  
  begin_date date:=sysdate+7;  
begin  
  course_name:='Introduction to Oracle PL/SQL';  
  num:=987654.55;/*NUMBER TYPE*/  
  dbms_output.put_line('room number:'||room_num||',and begin date is:'||begin_date);  
  if course_name='Introduction to Underwater Basketweaving'  
  then dbms_output.put_line('course name is :'||course_name);  
  else  
    if room_num='603D'  
    then dbms_output.put_line('course name is: '||course_name||',and room number is '||room_num);  
    else dbms_output.put_line('there is not any information for this course!');  
    end if;  
  end if;  
exception  
  when no_data_found then dbms_output.put_line('NO DATA!');  
end;

--PL/SQL SELECT statement blocks
declare  
  v_salary number;  
  v_department_id number;  
  v_department_name DEPARTMENTS.DEPARTMENT_NAME%type:='&Department_Name';  
  cursor num is select salary from it_employees where department_id=v_department_id;  
begin  
  select department_id into v_department_id from departments where department_name=v_department_name;  
  open num;  
  loop  
  fetch num into v_salary;  
  exit when num%notfound;  
  v_salary:=v_salary+&increase_salary;  
  update it_employees set salary=v_salary where department_id=v_department_id;  
  end loop;  
  close num;  
end;  

--Insert a new employee record
declare  
  v_employee_id number;  
begin  
  select max(employee_id) into v_employee_id from it_employees;  
  insert into it_employees (employee_id, first_name, last_name, e_mail, phone_number, job_id, salary, manager_id, birth_date, department_id)  
  values (v_employee_id+1,'&First_name','&Last_name','&E_mail','&Phone_number',&Job_id,&Salary,&Manager_id,'&Birth_date',&Department_id);  
end;  
  
--COMMIT  
declare  
  v_counter number;  
begin  
  v_counter:=0;  
  for i in 1..100  
  loop  
  v_counter:=v_counter+1;  
  if v_counter=10  
  then commit; v_counter:=0;  
  end if;  
  end loop;  
end;

--ROLLBACK and SAVEPOINT  
select * from chap4;  
create table chap4 (id number,name varchar2(20));  
create sequence chap4_seq increment by 5;  
declare  
  v_name varchar2(30);  
  v_id number;  
begin  
  select first_name||'.'||last_name fullname into v_name from it_employees where salary=(select max(salary) from it_employees);  
  insert into chap4 (id, name)values(chap4_seq.nextval,v_name);  
  savepoint A;  
  select first_name||'.'||last_name fullname into v_name from it_employees where salary=(select min(salary) from it_employees);   
  insert into chap4 (id, name)values(chap4_seq.nextval,v_name);  
  savepoint B;  
  select first_name||'.'||last_name fullname into v_name from it_employees where employee_id=10003;  
  insert into chap4 (id, name)values(chap4_seq.nextval,v_name);  
  savepoint C;  
  select job_id into v_id from it_employees where employee_id=10003;  
  DBMS_OUTPUT.put_line('JOB ID is: '||v_id);  
  rollback to savepoint A;  
end;  
  
select * from chap4;    
delete chap4;

--Comparison of two numerical size
declare  
  large_num number:=&number1;  
  small_num number:=&number2;  
  temp_num number;  
begin  
  if large_num<small_num  
  then temp_num:=large_num;  
       large_num:=small_num;  
       small_num:=temp_num;  
  end if;  
  DBMS_OUTPUT.put_line('large number is: '||large_num||', and small number is: '||small_num);  
end;

--Discrimination parity
declare  
  input_num number:=&new_number;  
begin  
  if mod(input_num,2)=0  
  then dbms_output.put_line(input_num||' is an even number!');  
  else dbms_output.put_line(input_num||' is an old number!');  
  end if;  
  DBMS_OUTPUT.PUT_LINE('done');  
end;  

--Show time is not the weekend provided
declare  
  v_date date:=TO_DATE('&input_new_date','DD-MON-YYYY');  
  v_day varchar2(15);  
begin  
  v_day:=to_char(v_date,'DAY');  
  if v_day in ('Sunday','Saturday')  
  then dbms_output.put_line('This day is weekend!');  
  else dbms_output.put_line('This day is '||v_day||', not weekends!');  
  end if;  
end;

--Analyzing today's date and time
declare  
  v_day varchar2(15);  
  v_hour varchar2(10);  
  v_time varchar2(10);  
begin  
  v_day:=to_char(sysdate,'DAY');  
  v_hour:=to_char(sysdate,'HH24');  
  v_time:=to_char(sysdate,'HH24:MI');  
  if v_day not in ('Sunday','Saturday')  
  then dbms_output.put_line('Today is '||v_day||', not weekends!');  
  else dbms_output.put_line('Today is weekend!');  
       if v_hour<12  
       then dbms_output.put_line(v_time||', Morning right now!');  
       elsif v_hour=12  
       then dbms_output.put_line(v_time||', Noon right now!');  
       else dbms_output.put_line(v_time||', Afternoon right now!');  
       end if;  
  end if;  
end;

--CASE statement to achieve date
declare  
  v_date date:=to_date('&Input_Date','DD-MON-YYYY');  
  v_day_num varchar2(10);  
begin  
  v_day_num:=to_char(v_date, 'D');  
  dbms_output.put_line(v_day_num);  
  case v_day_num  
  when '1' then dbms_output.put_line('Today is Sunday');  
  when '2' then dbms_output.put_line('Today is Monday');  
  when '3' then dbms_output.put_line('Today is Tuesday');  
  when '4' then dbms_output.put_line('Today is Wednesday');  
  when '5' then dbms_output.put_line('Today is Thursday');  
  when '6' then dbms_output.put_line('Today is Friday');  
  when '7' then dbms_output.put_line('Today is Saturday');  
  end case;  
end;

--Searched CASE achieve the look wage scale 
declare   
  v_emp_id number(10):=&Employee_ID;  
  v_salary number(20);  
  v_level char(1);  
begin  
  select salary into v_salary from it_employees where employee_id=v_emp_id;  
  case  
    when v_salary is null then dbms_output.put_line('This employee has no any salary record!');  
    else   
      case  
        when v_salary>=20000 then v_level:='A';  
        when v_salary>=18000 then v_level:='B';  
        when v_salary>=15000 then v_level:='C';  
        when v_salary>=12000 then v_level:='D';  
        when v_salary>=10000 then v_level:='F';  
        else v_level:='E';  
      end case;  
      dbms_output.put_line('This employee salary level is '||v_level);  
  end case;  
end;

--NULLIF function to see parity
declare  
  v_num number:=&Input_Number;  
  v_res number;  
begin  
  v_res:=nullif(mod(v_num,2),0);  
  DBMS_OUTPUT.PUT_LINE('result is '||v_res);  
end;  
  
--????  
create sequence seq_num increment by 1;  
drop SEQUENCE seq_num;  
begin  
  loop  
    DBMS_OUTPUT.PUT_LINE('No.'||seq_num.nextval);  
    exit when seq_num.currval=100;  
  end loop;  
end;

--Simple traffic light problem
declare  
  s_timer_green number(10):=20;  
  s_timer_red number(10):=30;  
  v_trigger boolean:=&Trigger;  
begin  
  while s_timer_green!=0 loop  
  dbms_output.put_line('Allow Crossing the Road, and remaining time is: '||s_timer_green);  
  s_timer_green:=s_timer_green-1;  
  end loop;  
  DBMS_OUTPUT.PUT_LINE('Cannot Cross the Road any more! Please wait for red light!');  
  while s_timer_red!=0 loop  
  dbms_output.put_line('Cannot Cross the Road, wait for time: '||s_timer_red);  
  s_timer_red:=s_timer_red-1;  
  if (v_trigger = true) and (s_timer_red<=5)--exit when  
  then exit;  
  end if;  
  end loop;  
end;

--Reverse multiplied
declare  
  v_num number(20):=1;  
begin  
  for counter in reverse 1..10 loop  
  v_num:=v_num*counter;  
  DBMS_OUTPUT.PUT_LINE('v_num: '||v_num);  
  end loop;  
  DBMS_OUTPUT.PUT_LINE('the final v_num: '||v_num);  
end;

-- From 1 to 100 Every 10 digital summation
declare  
  v_num number:=0;  
  v_sum number;  
  v_counter number:=0;  
begin  
  v_sum:=v_num;  
  loop  
    v_counter:=v_counter+1;  
    v_num:=v_num+1;  
    v_sum:=v_sum+v_num;  
    continue when v_counter<10;--if v_counter<10 then continue; end if;  
    DBMS_OUTPUT.PUT_LINE('sum is: '||v_sum);  
    v_counter:=0;  
    v_sum:=0;  
    if v_num=100 then exit;  
    end if;  
  end loop;  
end;

--*????  
declare  
begin  
  for i in 1..10 loop  
    for j in 1..i loop  
      dbms_output.put('*');  
      DBMS_OUTPUT.PUT('  ');   
      end loop;  
    DBMS_OUTPUT.PUT_LINE('');  
    end loop;  
end;  
  
--Shaped triangle  
declare  
  v_dep_id number(3);  
  v_name varchar2(30);  
  e_dep_id exception;  
begin  
  v_dep_id:=&Department_ID;  
  if v_dep_id<0 then  
    raise e_dep_id;  
    end if;  
  select first_name||' '||last_name into v_name from it_employees e, departments d where e.department_id=d.department_id and d.department_id=v_dep_id;  
  dbms_output.put_line('The name of this student is: '||v_name);  
exception  
  when e_dep_id then dbms_output.put_line('The department id cannot be negative!');  
  when no_data_found then dbms_output.put_line('There is not any record for this student!');  
  when too_many_rows then dbms_output.put_line('Returns one more student records!');  
  when value_error or invalid_number then dbms_output.put_line('Error occurs for values!');  
end;

--When the declaration part of PL / SQL statements pieces of run-time errors, 
--the exception-handling part of the statement pieces can not catch this error.
--Once again thrown
declare  
  v_num number(10);  
  e_v_num exception;  
begin  
  begin  
    v_num:=&In_num;  
    if v_num<0 then raise e_v_num;  
    else DBMS_OUTPUT.PUT_LINE('Number is: '||v_num);  
    end if;  
  exception  
    when e_v_num then raise;  
  end;  
exception  
  when e_v_num then dbms_output.put_line('The value cannot be negative!');  
end;  
  
--Raise_application_error()  
declare  
  count_total number;  
begin  
  select count(*) into count_total from it_employees where department_id=&department_id;  
  if count_total>1 then raise_application_error(-20000,'The number of employee in this department is invaild!');  
  end if;  
end; 

--SQLCODE and SQLEERM  
declare  
  num number(2);  
  error_number number;  
  error_msg varchar2(200);  
begin  
  num:=#  
  dbms_output.put_line(num);  
exception  
  when others then  
  error_number:=SQLCODE;  
  error_msg:=substr(SQLERRM,1,200);  
  dbms_output.put_line('Error Code: '||error_number);  
  dbms_output.put_line('Error Message: '||error_msg);  
end;

--Simple explicit cursor
select * from it_employees;  
declare  
  cursor c_it_emp is select * from it_employees where employee_id<=10003;  
  emp_info it_employees%rowtype;  
begin  
  open c_it_emp;  
  loop  
    fetch c_it_emp into emp_info;  
    exit when c_it_emp%notfound;  
    dbms_output.put_line(emp_info.first_name||'.'||emp_info.last_name||' '||emp_info.e_mail);  
  end loop;  
  close c_it_emp;  
end;

--User-defined types
declare  
  cursor c_it_emp is select first_name,last_name,e_mail from it_employees where employee_id<=10003;  
  type emp_info is record (firstname it_employees.first_name%type, lastname it_employees.last_name%type, email it_employees.e_mail%type);  
  emp_information emp_info;  
begin  
  open c_it_emp;  
  loop  
    fetch c_it_emp into emp_information;  
    exit when c_it_emp%notfound;  
    DBMS_OUTPUT.PUT_LINE(emp_information.firstname||'.'||emp_information.lastname||',email is: '||emp_information.email);  
  end loop;  
  close c_it_emp;  
exception  
  when others then   
  if c_it_emp%isopen then close c_it_emp;  
  end if;  
end;

--Cursor FOR loop to achieve the number of sectors
declare  
  dep_id it_employees.department_id%type;  
  cursor c_emp_info is select * from it_employees where department_id=dep_id;  
begin  
  dep_id:=&department_id;  
  for emp_info in c_emp_info  
  loop  
    dbms_output.put_line(emp_info.first_name||'.'||emp_info.last_name);  
  end loop;  
exception  
 -- when no_data_found then dbms_output.put_line('There is not any employees from this department!');  
  when value_error then dbms_output.put_line('ERROR on input data!!!');  
end;

select * from departments; 

--Check departments implement nested CURSOR 
declare  
  dep_id it_employees.department_id%type;  
  cursor c_loc_id is select department_id,department_name from departments where location_id=&Location_id;  
  cursor c_emp_name is select first_name||'.'||last_name name from it_employees where department_id=dep_id;  
begin  
  for loc_id in c_loc_id  
  loop  
    dep_id:=loc_id.department_id;  
    dbms_output.put('Employees who are in '||loc_id.department_name||': ');  
    for emp_name in c_emp_name  
    loop  
      dbms_output.put(emp_name.name||'; ');  
      end loop;  
    dbms_output.put_line('');  
    end loop;  
end;

 --Nested with reference CURSOR achieve all employee information output
 declare  
  cursor c_emp_info is select employee_id, first_name||'.'||last_name name, department_id from it_employees;  
  cursor c_dep_info (dep_id in departments.department_id%type) is select department_name from departments where departments.department_id=dep_id;  
begin  
  for emp_info in c_emp_info  
  loop  
    dbms_output.put(emp_info.employee_id||'   '||emp_info.name||'   ');  
    for dep_info in c_dep_info(emp_info.department_id)  
    loop  
      dbms_output.put(dep_info.department_name);  
    end loop;  
  dbms_output.put_line('');  
  end loop;  
end;

--Before Trigger achieve insert new staff and assigned to a manager
create or replace trigger emp_insert_bi  
before insert on it_employees  
for each row  
declare  
  v_emp_id it_employees.employee_id%type;  
begin  
  select max(employee_id)+1 into v_emp_id from it_employees;  
  :new.employee_id:=v_emp_id;  
  :new.salary:=10000;  
  :new.manager_id:=10001;  
end;  
insert into it_employees (first_name, last_name, e_mail, phone_number, job_id, birth_date, department_id) values ('Lily','Black','Lily.B@oracle.com',10163735464,1002,'30-5? -89',101);  
delete from it_employees where employee_id=(select max(employee_id) from it_employees);  
drop trigger emp_insert_bi;

--After Trigger employees achieve record operating table
create table statistics (record_id number(10), table_name varchar2(30), transaction_name varchar2(10), transaction_user varchar2(30), transaction_date date);--create record table  
create or replace trigger tab_stat--create the trigger
after insert or delete on it_employees  
declare  
  v_id statistics.record_id%type;  
  v_type varchar2(10);  
  v_count number(10);  
  PRAGMA autonomous_transaction;  
begin  
  select count(*) into v_count from statistics;  
  if v_count=0 then v_id:=1;  
  else select max(record_id)+1 into v_id from statistics;  
  end if;  
  if inserting then v_type:='Insert';  
  elsif deleting then v_type:='Delete';  
  end if;  
  insert into statistics (record_id, table_name, transaction_name, transaction_user, transaction_date) values (v_id,'it_employees',v_type,user,sysdate);  
  commit;  
end;  
select * from STATISTICS order by record_id asc;--check the record
delete from STATISTICS;  
 

--Trigger achieve office hours!
create or replace trigger check_date
before insert or delete or update on it_employees
declare
   v_date_day varchar2 (30);
   v_date_time number (10);
begin
   v_date_day: = rtrim (to_char (sysdate, 'DAY'));
   v_date_time: = to_number (to_char (sysdate, 'HH24'));
   if v_date_day like 'S%' then raise_application_error (-20001, 'Today is Weekend, it is invaild day to operate tables!');
   else
     if v_date_time <9 or v_date_time> = 18 then raise_application_error (-20002, 'Invaild time to operate tables!');
     end if;
   end if;
end;
update it_employees set salary = 11000 where employee_id = 10009;
drop trigger check_date;


--Implement complex trigger on the table insert and update IT_EMPLOYEES
create table operation_record (record_id number (10), table_name varchar2 (30), operation_name varchar2 (30), operation_user varchar2 (20), operation_date varchar2 (20));
create sequence record_operation_id increment by 1;
create or replace trigger emp_table_trig_comp
for insert or update on it_employees
compound trigger
  v_date_day varchar2 (30);
  v_date_time number (10);
  v_emp_id it_employees.employee_id% type;
  v_record_id operation_record.record_id% type: = record_operation_id.nextval;
  v_name operation_record.operation_name% type;
before statement is
begin
  v_date_day: = rtrim (to_char (sysdate, 'DAY'));
  v_date_time: = to_number (to_char (sysdate, 'HH24'));
  if v_date_day like 'S%' then raise_application_error (-20001, 'Today is Weekend, it is invaild day to operate tables!');
  else
    if v_date_time <9 or v_date_time> = 18 then raise_application_error (-20002, 'Invaild time to operate tables!');
    end if;
  end if;
end before statement;
before each row is
begin
  if inserting then
    select max (employee_id) +1 into v_emp_id from it_employees;
    : New.employee_id: = v_emp_id;
  end if;
end before each row;
after each row is
begin
  if inserting then v_name: = 'Insert';
  elsif updating then v_name: = 'Update';
  end if;
  insert into operation_record (record_id, table_name, operation_name, operation_user, operation_date) values ??(v_record_id, 'IT_EMPLOYEES', v_name, user, to_char (sysdate, 'DD-MON-YYYY HH24: MI'));
end after each row;
after statement is
begin
  DBMS_OUTPUT.PUT_LINE ('This Operation has been Completed!');
end after statement;
end emp_table_trig_comp;
insert into it_employees (first_name, last_name, e_mail, phone_number, job_id, salary, manager_id, birth_date, department_id) values ??('Hill', 'Jobs', 'Hill.J @ oracle.com', 10167445585,1003,10000,10001, '13 - July -1992 ', 102);
update it_employees set manager_id = 10012 where employee_id = 10006;
select * from operation_record;


--The name of the index table to achieve storage
declare  
  cursor c_name is select first_name||'.'||last_name name from it_employees;  
  type t_name_table is table of varchar2(30) index by binary_integer;  
  name_table t_name_table;  
  counter number:=0;  
begin  
  for r_name in c_name  
  loop  
    counter:=counter+1;  
    name_table(counter):=r_name.name;  
    dbms_output.put_line('Name ('||counter||') is: '||name_table(counter));  
  end loop;  
end;

--The method of the index table and nested table
declare  
  type t_num_table1 is table of number(10) index by binary_integer;  
  num_table1 t_num_table1;  
  type t_num_table2 is table of number(10);  
  num_table2 t_num_table2:=t_num_table2(11,21,31,41,51,61,71,81,91,101);  
begin  
  for n in 1..10  
  loop  
    num_table1(n):=n+1;  
    end loop;  
  if num_table1.exists(10) then dbms_output.put_line('NO.10 is '||num_table1(10));  
  end if;  
  dbms_output.put_line('Table1 total has '||num_table1.count);  
    
  num_table2.delete(3);  
  if num_table2.exists(3) then dbms_output.put_line('No.3 is'||num_table2(3));  
  else dbms_output.put_line('No.3 has been deleted!');  
  end if;  
  dbms_output.put_line('Table2 total has '||num_table2.count);  
    
  if num_table2.exists(9) then dbms_output.put_line('Prior num is '||num_table2.prior(9)||', next num is '||num_table2.next(9));  
  end if;  
  num_table2.trim(2);  
  dbms_output.put_line('Last number is '||num_table2.last);  
end;

--Variable-length arrays for storage name 2 times
declare  
  cursor c_name is select first_name||'.'||last_name name from it_employees;  
  type t_name_var is varray(25) of varchar2(30);  
  name_var t_name_var:=t_name_var();  
  counter number:=0;  
begin  
  for r_name in c_name loop  
    counter:=counter+1;  
    name_var.extend;  
    name_var(counter):=r_name.name;  
    end loop;  
  for n in 1..counter loop  
    name_var.extend(1,n);--????,????n???  
    end loop;  
  for n in 1..name_var.count loop  
    dbms_output.put_line('Name ('||n||') is '||name_var(n));  
    end loop;  
end;

--Multi-frequency array implement two yuan Output
declare  
  type t_var1_tab is varray(10) of number(5);  
  type t_var2_tab is varray(10) of t_var1_tab;  
  var1_tab t_var1_tab:=t_var1_tab(2,3,7,9);  
  var2_tab t_var2_tab:=t_var2_tab(var1_tab);  
begin  
  var2_tab.extend;  
  var2_tab(2):=t_var1_tab(7,8,3,6);  
  for n in 1..2 loop  
    for m in 1..4 loop  
      dbms_output.put_line('varrary ('||n||')('||m||') is '||var2_tab(n)(m));  
      end loop;  
    end loop;  
end;

--Nested record types to achieve personal information output
declare   
  type name_type is record (first_name it_employees.first_name%type, last_name it_employees.last_name%type);  
  type info_p_type is record (name name_type, email it_employees.e_mail%type, phonenum number(20));  
  info_person info_p_type;  
begin  
  select first_name, last_name, e_mail, phone_number into info_person.name.first_name, info_person.name.last_name, info_person.email, info_person.phonenum from it_employees where employee_id=&employee_id;  
  dbms_output.put_line('Name: '||info_person.name.first_name||'.'||info_person.name.last_name);  
  dbms_output.put_line('Email: '||info_person.email);  
  dbms_output.put_line('Name: '||info_person.phonenum);  
exception  
  when no_data_found then dbms_output.put_line('Please input vaild employee id!');  
end;  


--Dynamic SQL execute immediate
declare  
  sql_stmt varchar2(300);  
  total_emp_num number;  
  v_emp_id it_employees.employee_id%type:=&employee_id;  
  v_salary number(10);  
begin  
  sql_stmt:='select count(*) from it_employees';  
  execute IMMEDIATE sql_stmt into total_emp_num;  
  DBMS_OUTPUT.PUT_LINE('Total employee number is: '||total_emp_num);  
  sql_stmt:='declare v_date varchar2(30); begin v_date:=to_char(sysdate, ''DD-MON-YYYY''); dbms_output.put_line(''v_date is: ''||v_date); end;';--?????????''***''  
  execute IMMEDIATE sql_stmt;  
  sql_stmt:='select salary from it_employees where employee_id=:10001';  
  EXECUTE IMMEDIATE sql_stmt into v_salary using v_emp_id;  
  DBMS_OUTPUT.PUT_LINE('Salary is '||v_salary);  
end;  

--Dynamic SQL OPEN-FOR outputs according to department staff name
declare  
  first_name varchar2(10);  
  last_name varchar2(10);  
  type emp_cur_type is ref cursor;  
  emp_cur emp_cur_type;--Defines a cursor variable
  dep_id number:=&department_id;  
begin  
  open emp_cur for 'select first_name, last_name from it_employees where department_id=:101' using dep_id;  
  loop  
    fetch emp_cur into first_name, last_name;  
    exit when emp_cur%notfound;  
    DBMS_OUTPUT.PUT_LINE('Name is:'||first_name||'.'||last_name);  
    end loop;  
  close emp_cur;  
exception  
  when no_data_found then dbms_output.put_line('INVALID DEPARTMENT ID!');  
  when others then   
    if emp_cur%isopen then close emp_cur;  
    end if;  
end;

--For efficiency and Forall contrast (batch better) inserting bulk data into the index table
create table cc_emp (emp_id number(10),emp_name varchar2(30));  
select * from cc_emp;  
truncate table cc_emp;  
declare  
  type emp_id_type is table of cc_emp.emp_id%type index by pls_integer;  
  type emp_name_type is table of cc_emp.emp_name%type index by pls_integer;  
  emp_id_cc emp_id_type;  
  emp_name_cc emp_name_type;  
  start_time integer;  
  end_time integer;  
  v_total number;  
  em_id number:=10000;  
begin  
  select count(*) into v_total from it_employees;  
  for i in 1..v_total loop  
    em_id:=em_id+1;  
    select employee_id into emp_id_cc(i) from it_employees where employee_id=em_id;  
    select first_name||'.'||last_name name into emp_name_cc(i) from it_employees where employee_id=em_id;  
    end loop;  
      
  start_time:=dbms_utility.get_time;  
  for i in 1..v_total loop  
    insert into cc_emp (emp_id, emp_name)values (emp_id_cc(i),emp_name_cc(i));  
    end loop;  
  end_time:=dbms_utility.get_time;  
  SYS.DBMS_OUTPUT.PUT_LINE('For total time is: '||(end_time-start_time));  
    
  start_time:=dbms_utility.get_time;  
  forall i in 1..v_total   
    insert into cc_emp (emp_id, emp_name)values (emp_id_cc(i),emp_name_cc(i));  
  end_time:=dbms_utility.get_time;  
  SYS.DBMS_OUTPUT.PUT_LINE('Forall total time is: '||(end_time-start_time));  
  commit;  
end; 

--To retrieve data using bulk collect into batches into an array of co-existence
declare  
  type emp_id_type is table of it_employees.employee_id%type;  
  type emp_name_type is table of varchar2(30);  
  emp_id emp_id_type;  
  emp_name emp_name_type;  
begin  
  select employee_id, first_name||'.'||last_name name bulk collect into emp_id, emp_name from it_employees;---??select bluk collect into ??????????????,?????  
  for i in emp_id.first..emp_id.last loop  
    dbms_output.put_line('Employee ID is: '||emp_id(i));  
    dbms_output.put_line('Employee Name is: '||emp_name(i));  
    end loop;  
end;  

--With the bulk collect into employee information will be placed to achieve the collection of employee information class  
declare  
  cursor emp_info_cur is select employee_id, first_name||'.'||last_name, salary from it_employees;  
  type emp_rec is record (emp_id it_employees.employee_id%type, emp_name varchar(30),emp_salary it_employees.salary%type);--Define a type
  type emp_tab_type is table of emp_rec;  
  emp_tab emp_tab_type;--Define a set
begin  
  open emp_info_cur;  
  loop  
    fetch emp_info_cur bulk collect into emp_tab;  
    exit when emp_tab.count=0;  
    for i in emp_tab.first..emp_tab.last loop  
      dbms_output.put('Employee ID is: '||emp_tab(i).emp_id);  
      DBMS_OUTPUT.PUT('  Employee name is: '||emp_tab(i).emp_name);  
      DBMS_OUTPUT.PUT('  Employee Salary is: '||emp_tab(i).emp_salary);  
      DBMS_OUTPUT.PUT_LINE('');  
      end loop;  
    end loop;  
end;  

-- The name of parametric processes to achieve employee output at the same address
create or replace procedure name_procedure (loc_id in number, v_first_name out varchar2, v_last_name out varchar2)  
as  
cursor name_cur is select e.first_name, e.last_name from it_employees e, departments d where e.department_id=d.department_id and d.location_id=loc_id;  
begin  
  for i in name_cur loop  
    v_first_name:=i.first_name;  
    v_last_name:=i.last_name;     
    DBMS_OUTPUT.PUT_LINE('Name is: '||v_first_name||'.'||v_last_name);  
    end loop;  
exception  
  when others then  
    dbms_output.put_line('Error!');  
end name_procedure;  
  
declare  
  loc_id departments.location_id%type:=&location_id;  
  v_first_name it_employees.first_name%type;  
  v_last_name it_employees.last_name%type;  
begin  
  name_procedure(loc_id,v_first_name,v_last_name);  
end;  

--function to achieve wages output
create or replace function salary_fuc (emp_id in number)  
return number  
is  
  v_salary number(10);  
begin  
  select salary into v_salary from it_employees where employee_id=emp_id;  
  return v_salary;  
exception  
  when no_data_found then  
    dbms_output.put_line('Invaild employee id!');  
end salary_fuc;  
  
declare  
  emp_id it_employees.employee_id%type;  
begin  
  emp_id:=&employee_id;  
  dbms_output.put_line('the salary of employee '||emp_id||' is: '||salary_fuc(emp_id));  
end;  

--create a package
create or replace package emp_pac  
is  
  v_current_date varchar2(30);  
  procedure name_procedure (loc_id in number, v_first_name out varchar2, v_last_name out varchar2);  
  function salary_fuc (emp_id in number) return number;  
end emp_pac;  
  
create or replace package body emp_pac  
is  
  procedure name_procedure (loc_id in number, v_first_name out varchar2, v_last_name out varchar2)  
  as  
    cursor name_cur is select e.first_name, e.last_name from it_employees e, departments d where e.department_id=d.department_id and d.location_id=loc_id;  
  begin  
    for i in name_cur loop  
      v_first_name:=i.first_name;  
      v_last_name:=i.last_name;  
      DBMS_OUTPUT.PUT_LINE('Name is: '||v_first_name||'.'||v_last_name);  
      end loop;  
    exception  
      when others then  
        dbms_output.put_line('Error!');  
  end name_procedure;   
  function salary_fuc (emp_id in number) return number  
  is  
    v_salary number(10);  
  begin  
    select salary into v_salary from it_employees where employee_id=emp_id;  
    return v_salary;  
  exception  
    when no_data_found then  
      dbms_output.put_line('Invaild employee id!');  
  end salary_fuc;  
  begin--????  
    v_current_date:=to_char(sysdate,'DD-MON-YYYY HH24:MI');  
end emp_pac;  
  
declare  
  loc_id departments.location_id%type;  
  v_first_name it_employees.first_name%type;  
  v_last_name it_employees.last_name%type;  
begin  
  loc_id:=&Location_id;  
  emp_pac.name_procedure(loc_id,v_first_name,v_last_name);  
  DBMS_OUTPUT.PUT_LINE('Current Date is: '||emp_pac.v_current_date);  
end;  


--user user_objects to check the Data Dictionary
select object_type, object_name, status from user_objects where object_type in ('FUNCTION','PROCEDURE','PACKAGE','OBJECT_BODY') order by object_type;  
--Create a collection object type display employee information
create or replace type emp_info_type as object (emp_id number(10),emp_name varchar2(30),emp_email varchar2(30),emp_salary number(10),emp_dep_id number(10));  
declare  
  type emp_tab_type is table of emp_info_type index by binary_integer;  
  emp_tab emp_tab_type;  
begin  
  select emp_info_type(employee_id, first_name||'.'||last_name, e_mail, salary, department_id) bulk collect into emp_tab from it_employees where salary>=15000;  
  for i in 1..emp_tab.count  
  loop  
    dbms_output.put_line('Employee ID is: '||emp_tab(i).emp_id);  
    dbms_output.put_line('Employee Name is: '||emp_tab(i).emp_name);  
    dbms_output.put_line('Employee Email is: '||emp_tab(i).emp_email);  
    dbms_output.put_line('Employee Salary is: '||emp_tab(i).emp_salary);  
    dbms_output.put_line('Employee Department ID: '||emp_tab(i).emp_dep_id);  
    dbms_output.put_line('--------------------------------------');  
    end loop;  
end;  

--Object type methods
create or replace type employ_info_type as object  
(  
  emp_id number(10),  
  emp_first_name varchar2(20),  
  emp_last_name varchar2(20),  
  emp_salary number(10),  
  constructor function employ_info_type(self in out nocopy employ_info_type, i_id in number)  
  return self as result,  
  constructor function employ_info_type(self in out nocopy employ_info_type, i_id in number,i_f_name in varchar2, i_l_name in varchar2, i_salary in number)  
  return self as result,  
  member procedure get_emp_info(out_id out number, out_name out varchar, out_salary out number),  
  static procedure get_cur_date,  
  member function show_emp_salary(i_id in number)return number  
);  
  
create or replace type body employ_info_type as  
  constructor function employ_info_type(self in out nocopy employ_info_type, i_id in number)  
  return self as result  
  is  
  begin  
    self.emp_id:=i_id;  
    select first_name,last_name, salary into self.emp_first_name, self.emp_last_name, self.emp_salary from it_employees where employee_id=self.emp_id;  
    return;  
  exception  
    when no_data_found then dbms_output.put_line('No related records!');  
  end;  
    
  constructor function employ_info_type(self in out nocopy employ_info_type, i_id in number, i_f_name in varchar2, i_l_name in varchar2, i_salary in number)  
  return self as result  
  is  
  begin  
    self.emp_id:=i_id;  
    self.emp_first_name:=i_f_name;  
    self.emp_last_name:=i_l_name;  
    self.emp_salary:=i_salary;  
    return;  
  end;  
    
  member procedure get_emp_info(out_id out number, out_name out varchar, out_salary out number)  
  is  
  begin  
    out_id:=self.emp_id;  
    out_name:=self.emp_first_name||'.'||self.emp_last_name;  
    out_salary:=self.emp_salary;  
    DBMS_OUTPUT.PUT_LINE('Employee ID: '||out_id);  
    DBMS_OUTPUT.PUT_LINE('Employee Name: '||out_name);  
    DBMS_OUTPUT.PUT_LINE('Employee Salary: '||out_salary);  
  end;  
    
  static procedure get_cur_date  
  is  
  cur_date varchar2(30);  
  begin  
    cur_date:=to_char(sysdate,'HH24:MI');  
    DBMS_OUTPUT.PUT_LINE('Current Time is: '||cur_date);  
  end;  
    
  member function show_emp_salary(i_id in number)  
  return number  
  is   
  v_salary number(10);  
  begin  
    select salary into v_salary from it_employees where employee_id=i_id;  
    return v_salary;  
  end;  
end;  
  
declare--execute the calling
  employ_info employ_info_type;  
  id number(10);  
  name varchar2(30);  
  salary number(10);  
begin  
  employ_info:=employ_info_type(10003);  
  employ_info.get_emp_info(id,name,salary);  
  employ_info_type.get_cur_date;  
  salary:=employ_info.show_emp_salary(id);  
  DBMS_OUTPUT.PUT_LINE('Salary: '||salary);  
end;

--Use UTL_FILE to write log
CREATE OR REPLACE DIRECTORY D_OUTPUT AS 'C:\Users\ziwzhang\Desktop\temp\';---???????dba???  
GRANT READ, WRITE ON DIRECTORY D_OUTPUT TO system;  
GRANT EXECUTE ON utl_file TO system;   
  
create or replace procedure emp_number(log_directory in varchar2, log_name in varchar2)  
as  
  file_handler UTL_FILE.FILE_TYPE;  
  emp_counter number;  
begin  
  select count(*) into emp_counter from it_employees;  
  file_handler:=UTL_FILE.FOPEN(log_directory,log_name,'A');  
  UTL_FILE.NEW_LINE(file_handler);  
  UTL_FILE.PUT_LINE(file_handler,'----------USER LOG-----------');  
  UTL_FILE.NEW_LINE(file_handler);  
  UTL_FILE.PUT_LINE(file_handler,'on '||to_char(sysdate,'DD-MON-YYYY HH24:MI'));  
  UTL_FILE.NEW_LINE(file_handler);  
  UTL_FILE.PUT_LINE(file_handler,'The number of employee is: '||emp_counter);  
  UTL_FILE.NEW_LINE(file_handler);  
  UTL_FILE.PUT_LINE(file_handler,'-----------END LOG-----------');  
  UTL_FILE.NEW_LINE(file_handler);  
  UTL_FILE.FCLOSE(file_handler);  
exception  
  when UTL_FILE.INVALID_FILENAME THEN  
  DBMS_OUTPUT.PUT_LINE('FILE IS INVALID!');  
  when UTL_FILE.WRITE_ERROR THEN  
  DBMS_OUTPUT.PUT_LINE('THE FILE CANNOT BE WRITTEN!');  
  WHEN UTL_FILE.INVALID_PATH THEN  
  DBMS_OUTPUT.PUT_LINE('PATH IS INVALID!');  
END;  
  
BEGIN  
  emp_number('D_OUTPUT','user_log.log');  
END;

--Use utl_file to read a file  
create or replace procedure read_file (file_dir in varchar2, file_name in varchar2)  
as  
  file_handler utl_file.file_type;  
  file_content varchar2(1024);  
begin  
  file_handler:=utl_file.fopen(file_dir, file_name, 'R');  
  loop  
    utl_file.get_line(file_handler,file_content);  
    DBMS_OUTPUT.PUT_LINE(file_content);  
    end loop;  
exception  
  when no_data_found then  
    utl_file.fclose(file_handler);  
end;  
  
begin  
  read_file('D_OUTPUT','user_log.log');  
end;  
  
--  
create or replace procedure my_first_page   
as  
begin  
  htp.htmlopen;  
  htp.headopen;  
  htp.title('My First Page');  
  htp.headclose;  
  htp.bodyopen;  
  htp.p('<p>This is my first web page!</p>');  
  htp.bodyclose;  
  htp.htmlclose;  
exception  
  when others then  
  htp.p('ERROR OCCUR!');  
end;  
  
begin  
 my_first_page;  
end;  

Advertisements

About daviewning

I am an Oracle DBA

Discussion

No comments yet.

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: