主要知識(shí)：
0：實(shí)際問題的分析解決：錯(cuò)誤處理，日志處理的方法

1表格的建立，表的關(guān)聯(lián)管理， pk,fk的定義設(shè)定。pk,fk約束下的dml
2:存儲(chǔ)函數(shù)，過程的定義調(diào)用
3:view的使用，關(guān)聯(lián)查詢
4：存儲(chǔ)函數(shù)，過程的使用
5：例外處理的機(jī)制，方法。內(nèi)置例外，用戶定義例外

------------------------------------建立錯(cuò)誤信息，日志信息表------------------

--錯(cuò)誤信息表，用于顯示提示給用戶的各種錯(cuò)誤提示。
create table err_msg(
code number(3,0) primary key,
message varchar2(50),
leixing varchar2(2),--錯(cuò)誤類型號(hào)
constraint
 fk_err foreign key(leixing) references err_leixing(leixing));

--錯(cuò)誤類型表，存儲(chǔ)error所屬的類型，如用戶自定義類型，系統(tǒng)內(nèi)置類型
create table err_leixing(
leixing varchar2(2) primary key,--錯(cuò)誤類型號(hào)
shuoming varchar2(50));

--插入錯(cuò)誤類型表（父表）
insert into err_leixing values('01','用戶定義錯(cuò)誤');
insert into err_leixing values('02','系統(tǒng)內(nèi)置錯(cuò)誤');

--插入錯(cuò)誤信息表.
insert into err_msg values(1,'沒有對(duì)應(yīng)的雇員編號(hào),重新輸入雇員編號(hào)','01');
--以后用于plsql的exception，或找雇員的問題

insert into err_msg values(2,'雇員必須有聯(lián)系電話,輸入雇員聯(lián)系電話','01');
--trigger,sp,exception

insert into err_msg values(3,'此雇員目前沒有銷售業(yè)績','01');
--以后用于plsql的exception，或找雇員的問題

--100號(hào)以后為系統(tǒng)錯(cuò)誤
insert into err_msg values(101,'被除數(shù)不可以為0','02');



--日志信息表,比如發(fā)生的錯(cuò)誤----------------------------------------------------------
--認(rèn)為同一系統(tǒng)同一用戶在同一時(shí)間發(fā)生錯(cuò)誤的機(jī)會(huì)很少
--方法1：使用fk
create table log_msg (
system_name varchar2(50), --系統(tǒng)名稱
time date,                 --紀(jì)錄日志的時(shí)間
err_code number(3,0),      --錯(cuò)誤號(hào).log還可能用于處理其它問題.此時(shí)err_code 置null
user_name varchar2(10),    --當(dāng)前用戶
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約束，允許子表在關(guān)聯(lián)字段輸入null!


--方法2：不使用fk
create table log_msg (
system_name varchar2(50), --系統(tǒng)名稱
time date,                 --紀(jì)錄日志的時(shí)間
err_code number(3,0),      --錯(cuò)誤號(hào).log還可能用于處理其它問題.此時(shí)err_code 置null
user_name varchar2(10),    --當(dāng)前用戶
beizhu varchar2(50),      --備注   
primary key (system_name,time,user_name));
--不用在err_code與err_msg (code)之間建立建立fk.因?yàn)閘og還可能用于處理其它問題
--此時(shí)err_code 置null



------------------------------------建立處理錯(cuò)誤信息，日志信息的存儲(chǔ)函數(shù)過程------------------

--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('沒有對(duì)應(yīng)編號(hào)的錯(cuò)誤！');
    return null;--如沒此句會(huì)發(fā)生運(yùn)行錯(cuò)誤，函數(shù)沒return語句
end; 

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

--測(cè)試.在sql語句中dbms_output不正常。
 SELECT GET_ERRMSG(1) FROM DUAL;




--2向日志信息表(log_msg)插入數(shù)據(jù)-----------------------------------------------------------

--p_sysname:發(fā)生錯(cuò)誤，或要生成日志的應(yīng)用程序(系統(tǒng)名稱)
--p_errcode number :錯(cuò)誤號(hào)。當(dāng)不是錯(cuò)誤日志插入null
--注意：如果不使用fk約束錯(cuò)誤號(hào)于err_msg,需在此sp中檢查err_code是否在err_msg中。
--p_beizhu:備注

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('雇員管理雇員查詢系統(tǒng)',null,'測(cè)試');
select SYSTEM_NAME,to_char(time,'yyyy/mon/dd hh24:mi:ss') from log_msg;
 call set_log('雇員管理雇員查詢系統(tǒng)',2,'測(cè)試');


-----------查詢log信息中的錯(cuò)誤日志，及其錯(cuò)誤信息------------------------------------------------------------
select SYSTEM_NAME,time,err_code ,message from err_msg ,log_msg 
where err_code=code;

--取出所有日志信息，和相應(yīng)的錯(cuò)誤信息。不是錯(cuò)誤的日志，錯(cuò)誤信息補(bǔ)空
--建立一個(gè)view對(duì)應(yīng)相應(yīng)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中使用錯(cuò)誤處理機(jī)制( log_msg,err_msg)-------------------------

--練習(xí)：
--求出給定雇員編號(hào)的雇員的工資：
--要求根據(jù)雇員總交易額
--雇員總交易額 < 100 ,工資為1000。 100--200 工資為1500  。200--300  1800.  高于300   2000.

--如果對(duì)應(yīng)人員的售額 使用3號(hào)錯(cuò)誤信息。同時(shí)，將此錯(cuò)誤記入日志文件(日志表)
--使用通用函數(shù) 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;--存放錯(cuò)誤信息
begin 
  
  --取得對(duì)應(yīng)id的總銷售額
  select sum(totalprice) into totalsal 
  from sm_saleorderlist where employid = id;
  
  --if 嵌套處理如沒有對(duì)應(yīng)人員的售額
  if totalsal is null then
	 
	v_err_msg :=get_errmsg(3);
	set_log('set_salary過程',3,'在工資設(shè)定功能中');
	--procedure在plsql中直接調(diào)用。只有在sqlplus或宿主語言才用call
	dbms_output.put_line(v_err_msg);
  ELSE 
	--內(nèi)層 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  結(jié)束
end;
--測(cè)試
SQL> call set_salary(90);

------------------------------在內(nèi)置exception中使用錯(cuò)誤處理機(jī)制( log_msg,err_msg)-----------------
--練習(xí)：
--求出給定雇員編號(hào)的雇員的工資：
--要求根據(jù)雇員總交易額

--雇員總交易額 < 100 ,工資為1000。 100--200 工資為1500  。200--300  1800.  高于300   2000.

--如果沒有對(duì)應(yīng)的雇員編號(hào)， 使用1號(hào)錯(cuò)誤信息。同時(shí)，將此錯(cuò)誤記入日志文件(日志表)
--如果沒有對(duì)應(yīng)人員的售額  使用3號(hào)錯(cuò)誤信息。同時(shí)，將此錯(cuò)誤記入日志文件(日志表)
--使用通用函數(shù) 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; --檢查是否有對(duì)應(yīng)empid
  v_err_msg err_msg.message%TYPE;--存放錯(cuò)誤信息
  
begin
  select empid into v_id from sm_emp where empid=id;
  --如果沒有對(duì)應(yīng)empid 跳到exception處理

  --取得對(duì)應(yīng)id的總銷售額
  select sum(totalprice) into totalsal 
  from sm_saleorderlist where employid = id;
  
  --if 嵌套處理如沒有對(duì)應(yīng)人員的售額
  if totalsal is null then
	 
	v_err_msg :=get_errmsg(3);
	set_log('set_salary過程',3,'在工資設(shè)定功能中');
	--procedure在plsql中直接調(diào)用。只有在sqlplus或宿主語言才用call
	dbms_output.put_line(v_err_msg);
  ELSE 
	--內(nèi)層 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  結(jié)束
exception
  
  when no_data_found then
  --沒有對(duì)應(yīng)的雇員編號(hào)  
    v_err_msg :=get_errmsg(1);
    set_log('set_salary過程2',1,'在工資設(shè)定功能中');
	--procedure在plsql中直接調(diào)用。只有在sqlplus或宿主語言才用call
    dbms_output.put_line(v_err_msg);
end;


------------------------------在內(nèi)置，用戶定義exception中使用錯(cuò)誤處理機(jī)制( log_msg,err_msg)-----------------
--練習(xí)：
--求求出給定雇員編號(hào)的雇員的工資：
--要求根據(jù)雇員總交易額
--雇員總交易額 < 100 ,工資為1000。 100--200 工資為1500  。200--300  1800.  高于300   2000.

--如果沒有對(duì)應(yīng)的雇員編號(hào)， 使用1號(hào)錯(cuò)誤信息。同時(shí)，將此錯(cuò)誤記入日志文件(日志表)
--如果沒有對(duì)應(yīng)人員的售額 使用3號(hào)錯(cuò)誤信息。同時(shí)，將此錯(cuò)誤記入日志文件(日志表)
--使用通用函數(shù) 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; --檢查是否有對(duì)應(yīng)empid
  v_err_msg err_msg.message%TYPE;--存放錯(cuò)誤信息

  no_totalsal exception;--自定義例外，處理有empid 沒有業(yè)績
  
  
begin
  select empid into v_id from sm_emp where empid=id;
  --如果沒有對(duì)應(yīng)empid 跳到exception處理

  --取得對(duì)應(yīng)id的總銷售額
  select sum(totalprice) into totalsal 
  from sm_saleorderlist where employid = id;
  
  --處理如沒有對(duì)應(yīng)人員的售額
  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  結(jié)束

exception
  
  when no_data_found then
  --沒有對(duì)應(yīng)的雇員編號(hào) 
    v_err_msg :=get_errmsg(1);
    set_log('set_salary過程1',1,'在工資設(shè)定功能中');
	--procedure在plsql中直接調(diào)用。只有在sqlplus或宿主語言才用call
    dbms_output.put_line(v_err_msg);

  when no_totalsal then
  --沒有對(duì)應(yīng)人員的售額
    v_err_msg :=get_errmsg(3);
    set_log('set_salary3過程',3,'在工資設(shè)定功能中');
	--procedure在plsql中直接調(diào)用。只有在sqlplus或宿主語言才用call
    dbms_output.put_line(v_err_msg);

  when others then
    set_log('set_salary1過程',null,'在工資設(shè)定功能中,出現(xiàn)未處理的錯(cuò)誤！');
	--procedure在plsql中直接調(diào)用。只有在sqlplus或宿主語言才用call
    dbms_output.put_line('在工資設(shè)定功能中,出現(xiàn)未處理的錯(cuò)誤！');
    
end;

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

 select * from view_log;
--查詢結(jié)果