PL/SQL详例和解释(二)
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=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 numb