PL/SQL 详例和解释

/**example**/
set serveroutput on;  --将输出server打开
show serveroutput;

set verify off;
show verify;

--调取数据库中的值
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;

--替代变量
declare
  input_value number:=&v_input;
  output_result number;
begin
  output_result:=power(input_value, 2);
  DBMS_OUTPUT.PUT_LINE(output_result);
end;

--圆面积
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;

--输出系统时间
declare 
  v_day varchar2(20);
begin
  v_day:=to_char(sysdate,'Day, HH24:MI');
  DBMS_OUTPUT.PUT_LINE('Today is '||v_day);
end;

--嵌套语句块和标签
<< 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;

--变量声明和IF语句嵌套
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
---<扩展>
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;

--插入一条新员工记录
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和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;

--比较两个数值大小
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;
  
--辨别奇偶数
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;

--查看提供的时间是不是周末
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 ('星期日','星期六')
  then dbms_output.put_line('This day is weekend!');
  else dbms_output.put_line('This day is '||v_day||', not weekends!');
  end if;
end;

--判断今天的日期时间
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 ('星期日','星期六')
  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语句实现日期
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;


--搜索型CASE实现查看工资等级
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函数查看奇偶 
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;

--简单的红绿灯问题
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;

--逆向相乘
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;

--1--100每10个数字求和
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;

--内部异常处理和用户定义异常处理
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;

--当PL/SQL语句块儿的声明部分出现运行时的错误时，该语句块儿的异常处理部分不能捕获此项错误。
--再次抛出异常
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 和 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;

--简单的显式游标
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;

--用户自己定义类型
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;

--游标FOR循环实现部门人数
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;

--嵌套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;

--嵌套的带参CURSOR实现所有员工信息输出
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 实现插入新员工并分配给一个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','[email protected]',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实现对员工表操作的记录
create table statistics (record_id number(10), table_name varchar2(30), transaction_name varchar2(10), transaction_user varchar2(30), transaction_date date);--创建记录表
create or replace trigger tab_stat--创建触发器
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;--查看
delete from STATISTICS;

--触发器实现办公时间！
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;

--复合触发器实现对表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','[email protected]',10167445585,1003,10000,10001,'13-7月 -1992',102);
update it_employees set manager_id=10012 where employee_id=10006;
select * from operation_record;

--实现存放名字的索引表
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;

--索引表和嵌套表的方法
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;

--变长数组实现存储2遍名字
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;

--多层数组实现二元次数组输出
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;

--嵌套记录类型实现个人信息输出
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;

--动态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;

--动态SQL OPEN-FOR根据部门号输出员工名字
declare
  first_name varchar2(10);
  last_name varchar2(10);
  type emp_cur_type is ref cursor;
  emp_cur emp_cur_type;--定义一个游标变量
  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 和 Forall 效率对比（批量效果更佳）把批量数据插入到索引表中
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;

--使用bulk collect into 批量检索数据并存放到数组中
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;

--用bulk collect into实现将员工信息放入到员工信息类的集合中
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);--定义一种类型
  type emp_tab_type is table of emp_rec;
  emp_tab emp_tab_type;--定义一个集合
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;

--含参过程实现同一地址的员工名字输出
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实现员工工资输出
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 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_objects查看数据字典
select object_type, object_name, status from user_objects where object_type in ('FUNCTION','PROCEDURE','PACKAGE','OBJECT_BODY') order by object_type;

--创建一个对象类型的集合展示员工信息
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;

--对象类型的方法
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--执行调用
  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;

--运用UTL_FILE来写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;

--运用utl_file来读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;