甲骨文

异常处理

declare

v_name varchar2(8); begin

select 姓名 into v_name

from student

where 学号=0805070213;

dbms_output.put_line('该学生的姓名为：'||v_name); exception

when no_data_found then

dbms_output.put_line('错误编号，没有找到相应的学生！'); when others then

dbms_output.put_line('发生其他错误！'); end;

触发器1 --创建记录表

create table logs

(log_id number(10) primary key, log_table varchar2(10) not null, log_dml varchar2(10), log_key_id number(10), log_date date,

log_user varchar2(15) )

/

--创建主键序列

create sequence log_id_squ increment by 1

start with 1 maxvalue 9999999 nocycle nocache /

--创建触发器

create or replace trigger dml_log before

delete or insert or update on student for each row begin

if inserting then

insert into logs

values(log_id_squ.nextval,'student','insert',:new.学号,sysdate,user); elsif deleting then insert into logs

values(log_id_squ.nextval,'student','delete',:old.学号,sysdate,user);

else

insert into logs

values(log_id_squ.nextval,'student','update',:new.学号,sysdate,user); end if; end;

--检查LOGS表中记录的信息 select * from logs 存储过程

create or replace procedure s_count as

v_total number; begin

select count(*) into v_total from student;

dbms_output.put_line('学生总数为：'||v_total); end; --创建索引

create index student_籍贯 on student(籍贯) --使用索引

select 姓名,学号,专业 from student

where 籍贯='陕西西安' --删除索引

drop index student_籍贯 --创建复合索引

create index student_jn

on student(籍贯,出生年月) --使用复合索引

select 姓名,学号,专业

from student

where 籍贯='陕西西安' and 出生年月<'31-12月-89'

--查看索引

select index_name,index_type from user_indexes

where table_name='STUDENT' --显示索引的列 select column_name

from user_ind_columns

where index_name='STUDENT_JN' 1--创建序列 create sequence abc increment by 2 start with 10 maxvalue 999999999 nocycle nocache --删除序列 drop sequence abc 2--创建序列 create sequence abc increment by 1 start with 00006 maxvalue 99999 nocycle

nocache

--产生序列的第一个值 select abc.nextval from class --下一个值

select abc.nextval from class