/**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','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实现对员工表操作的记录
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','Hill.J@oracle.com',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;