错误处理日志综合练习_建表.txt

orale培训教材包括了所有的sql说明和实例

主要知识：
练习1
0：实际问题的分析解决：错误处理，日志处理的方法

1表格的建立，表的关联管理， pk,fk的定义设定。pk,fk约束下的dml

练习2
2:存储函数，过程的定义调用
3:view的使用，关联查询
4：存储函数，过程的使用
5：例外处理的机制，方法。内置例外，用户定义例外

------------------------------------建立错误信息，日志信息表------------------

--错误信息表，用于显示提示给用户的各种错误提示。
create table err_msg(
code number(3,0) primary key,
message varchar2(50),
leixing varchar2(2),--错误类型号
constraint
 fk_err foreign key(leixing) references err_leixing(leixing));

--错误类型表，存储error所属的类型，如用户自定义类型，系统内置类型
create table err_leixing(
leixing varchar2(2) primary key,--错误类型号
shuoming varchar2(50));

--插入错误类型表（父表）
insert into err_leixing values('01','用户定义错误');
insert into err_leixing values('02','系统内置错误');

--插入错误信息表.
insert into err_msg values(1,'没有对应的雇员编号,重新输入雇员编号','01');
--以后用于plsql的exception，或找雇员的问题

insert into err_msg values(2,'雇员必须有联系电话,输入雇员联系电话','01');
--trigger,sp,exception

insert into err_msg values(3,'此雇员目前没有销售业绩','01');
--以后用于plsql的exception，或找雇员的问题

--100号以后为系统错误
insert into err_msg values(101,'被除数不可以为0','02');



--日志信息表,比如发生的错误----------------------------------------------------------
--认为同一系统同一用户在同一时间发生错误的机会很少
--方法1：使用fk
create table log_msg (
system_name varchar2(50), --系统名称
time date,                 --纪录日志的时间
err_code number(3,0),      --错误号.log还可能用于处理其它问题.此时err_code 置null
user_name varchar2(10),    --当前用户
beizhu varchar2(50),      --备注   
primary key (system_name,time,user_name),
constraint fk_log
 foreign key (err_code) references err_msg(code));
--在log_msg中使用fk于err_msg通过err_cokd=code相限制。
--fk约束，允许子表在关联字段输入null!


--方法2：不使用fk
create table log_msg (
system_name varchar2(50), --系统名称
time date,                 --纪录日志的时间
err_code number(3,0),      --错误号.log还可能用于处理其它问题.此时err_code 置null
user_name varchar2(10),    --当前用户
beizhu varchar2(50),      --备注   
primary key (system_name,time,user_name));
--不用在err_code与err_msg (code)之间建立建立fk.因为log还可能用于处理其它问题
--此时err_code 置null



--练习2-------------------------------------------------------------------
------------------------------------建立处理错误信息，日志信息的存储函数过程------------------

--1由指定errcode,返回err_message-------------------------------------------------------

create or replace function get_errmsg (p_code number) return varchar2 is
  msg err_msg.message%TYPE;
begin

  select message into msg from err_msg where code= p_code;
  return msg;
exception
  when no_data_found then
    dbms_output.put_line('没有对应编号的错误！');
    return null;--如没此句会发生运行错误，函数没return语句
end; 

--测试程序
--在plsql中dbms_output ok
create or replace procedure test_msg is 
 r varchar2(50);
begin
 r:=get_errmsg (10);
end;

--测试.在sql语句中dbms_output不正常。
 SELECT GET_ERRMSG(1) FROM DUAL;




--2向日志信息表(log_msg)插入数据-----------------------------------------------------------

--p_sysname:发生错误，或要生成日志的应用程序
--p_errcode number :错误号。当不是错误日志插入null
--注意：如果不使用fk约束错误号于err_msg,需在此sp中检查err_code是否在err_msg中。

create or replace procedure set_log 
( p_sysname in varchar2, p_errcode in number,p_beizhu varchar2) is
begin
  insert into log_msg(system_name,time,err_code,user_name,beizhu)
  values(p_sysname , sysdate, p_errcode,user,p_beizhu);
  commit;
end;

call set_log('雇员管理雇员查询系统',null,'测试');
select SYSTEM_NAME,to_char(time,'yyyy/mon/dd hh24:mi:ss') from log_msg;
 call set_log('雇员管理雇员查询系统',2,'测试');


-----------查询log信息中的错误日志，及其错误信息------------------------------------------------------------
select SYSTEM_NAME,time,err_code ,message from err_msg ,log_msg 
where err_code=code;

--取出所有日志信息，和相应的错误信息。不是错误的日志，错误信息补空
--建立一个view对应相应sql
create or replace view view_log as
select SYSTEM_NAME,to_char(time,'yyyy/mon/dd hh24:mi:ss') time_date,err_code ,beizhu ,message from err_msg ,log_msg 
where err_code=code(+);


-------------------------------在使用plsql中使用错误处理机制( log_msg,err_msg)-------------------------

--练习：
--求求出给定雇员编号的雇员的工资：
--要求根据雇员总交易额
--雇员总交易额 < 100 ,工资为1000。 < 200 工资为1500  。< 300  1800.  高于300   2000.

--如果对应人员的售额 使用3号错误信息。同时，将此错误记入日志文件(日志表)
--使用通用函数 get_errmsg (p_code number),
--set_log( p_sysname in varchar2, p_errcode in number,p_beizhu varchar2)


create or replace procedure set_salary ( id char) is
	totalsal number:=0;
	v_salary number :=0;
	v_err_msg err_msg.message%TYPE;--存放错误信息
begin 
  
  --取得对应id的总销售额
  select sum(totalprice) into totalsal 
  from sm_saleorderlist where employid = id;
  
  --if 嵌套处理如没有对应人员的售额
  if totalsal is null then
	 
	v_err_msg :=get_errmsg(3);
	set_log('set_salary过程',3,'在工资设定功能中');
	--procedure在plsql中直接调用。只有在sqlplus或宿主语言才用call
	dbms_output.put_line(v_err_msg);
  ELSE 
	--内层 IF 语句
  	if totalsal < 100 then
		v_salary :=1000;
  	elsif totalsal < 200 then
		v_salary :=1500;
  	elsif totalsal < 300 then 
  		v_salary :=1800;

  	else 
 		v_salary :=2000;
  	end if;
  
  	update sm_emp set salary =v_salary where empid = id;
  	commit;

  END IF ;
  --外层IF  结束
end;
--测试
SQL> call set_salary(90);

------------------------------在内置exception中使用错误处理机制( log_msg,err_msg)-----------------
--练习：
--求求出给定雇员编号的雇员的工资：
--要求根据雇员总交易额
--雇员总交易额 < 100 ,工资为1000。 < 200 工资为1500  。< 300  1800.  高于300   2000.

--如果没有对应的雇员编号， 使用1号错误信息。同时，将此错误记入日志文件(日志表)
--如果对应人员的售额 使用3号错误信息。同时，将此错误记入日志文件(日志表)
--使用通用函数 get_errmsg (p_code number),
--set_log( p_sysname in varchar2, p_errcode in number,p_beizhu varchar2)
create or replace procedure set_salary2 (id in char) is 
  totalsal number;
  v_salary number:=0;
  v_id sm_emp.empid%TYPE; --检查是否有对应empid
  v_err_msg err_msg.message%TYPE;--存放错误信息
  
begin
  select empid into v_id from sm_emp where empid=id;
  --如果没有对应empid 跳到exception处理

  --取得对应id的总销售额
  select sum(totalprice) into totalsal 
  from sm_saleorderlist where employid = id;
  
  --if 嵌套处理如没有对应人员的售额
  if totalsal is null then
	 
	v_err_msg :=get_errmsg(3);
	set_log('set_salary过程',3,'在工资设定功能中');
	--procedure在plsql中直接调用。只有在sqlplus或宿主语言才用call
	dbms_output.put_line(v_err_msg);
  ELSE 
	--内层 IF 语句
  	if totalsal < 100 then
		v_salary :=1000;
  	elsif totalsal < 200 then
		v_salary :=1500;
  	elsif totalsal < 300 then 
  		v_salary :=1800;

  	else 
 		v_salary :=2000;
  	end if;
  
  	update sm_emp set salary =v_salary where empid = id;
  	commit;

  END IF ;
  --外层IF  结束
exception
  
  when no_data_found then
  --没有对应的雇员编号  
    v_err_msg :=get_errmsg(1);
    set_log('set_salary过程2',1,'在工资设定功能中');
	--procedure在plsql中直接调用。只有在sqlplus或宿主语言才用call
    dbms_output.put_line(v_err_msg);
end;


------------------------------在内置，用户定义exception中使用错误处理机制( log_msg,err_msg)-----------------
--练习：
--求求出给定雇员编号的雇员的工资：
--要求根据雇员总交易额
--雇员总交易额 < 100 ,工资为1000。 < 200 工资为1500  。< 300  1800.  高于300   2000.

--如果没有对应的雇员编号， 使用1号错误信息。同时，将此错误记入日志文件(日志表)
--如果对应人员的售额 使用3号错误信息。同时，将此错误记入日志文件(日志表)
--使用通用函数 get_errmsg (p_code number),
--set_log( p_sysname in varchar2, p_errcode in number,p_beizhu varchar2)
create or replace procedure set_salary3 (id in char) is 
  totalsal number;
  v_salary number:=0;
  v_id sm_emp.empid%TYPE; --检查是否有对应empid
  v_err_msg err_msg.message%TYPE;--存放错误信息

  no_totalsal exception;--自定义例外，处理有empid 没有业绩
  
  
begin
  select empid into v_id from sm_emp where empid=id;
  --如果没有对应empid 跳到exception处理

  --取得对应id的总销售额
  select sum(totalprice) into totalsal 
  from sm_saleorderlist where employid = id;
  
  --处理如没有对应人员的售额
  if totalsal is null then
	raise no_totalsal;
	
  end if; 

	--单层 IF 语句
  	if totalsal < 100 then
		v_salary :=1000;
  	elsif totalsal < 200 then
		v_salary :=1500;
  	elsif totalsal < 300 then 
  		v_salary :=1800;

  	else 
 		v_salary :=2000;
  	end if;
  
  	update sm_emp set salary =v_salary where empid = id;
  	commit;  
        --单层IF  结束

exception
  
  when no_data_found then
  --没有对应的雇员编号 
    v_err_msg :=get_errmsg(1);
    set_log('set_salary过程1',1,'在工资设定功能中');
	--procedure在plsql中直接调用。只有在sqlplus或宿主语言才用call
    dbms_output.put_line(v_err_msg);

  when no_totalsal then
  --没有对应人员的售额
    v_err_msg :=get_errmsg(3);
    set_log('set_salary3过程',3,'在工资设定功能中');
	--procedure在plsql中直接调用。只有在sqlplus或宿主语言才用call
    dbms_output.put_line(v_err_msg);

  when others then
    set_log('set_salary1过程',null,'在工资设定功能中,出现未处理的错误！');
	--procedure在plsql中直接调用。只有在sqlplus或宿主语言才用call
    dbms_output.put_line('在工资设定功能中,出现未处理的错误！');
    
end;

call set_salary3(88); 
call set_salary3('9');

 select * from view_log;
--查询结果