自定义_exception.txt

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

--练习：
--求出给定雇员编号的雇员的工资：
--要求根据雇员总交易额，要求使用例外处理对应雇员没有交易额的情况
--雇员总交易额 < 100 ,工资为1000。 < 200 工资为1500  。< 300  1800.  高于300   2000.




--------------------------------------data准备-----------------------

DROP TABLE sm_saleorderlist CASCADE CONSTRAINT;
CREATE TABLE sm_saleorderlist(
    TransactionID NUMBER(10) NOT NULL UNIQUE,
    TotalPrice NUMBER(7,2) NOT NULL,
    EmployID CHAR(10) NOT NULL,
    SaleTime DATE NOT NULL,
    PRIMARY KEY(TransactionID));




--data sm_saleorderlist
INSERT INTO sm_saleorderlist VALUES(1,100.10,'0000000001','09_9月_02');
INSERT INTO sm_saleorderlist VALUES(2,222.20,'0000000001','09_9月_02');
INSERT INTO sm_saleorderlist VALUES(3,300.10,'0000000002','09_9月_02');
INSERT INTO sm_saleorderlist VALUES(4,100.10,'0000000003','09_9月_02');
COMMIT;


--if sm_emp exists,
DROP TABLE sm_emp;

CREATE table sm_emp
(EmpID CHAR(10) PRIMARY KEY,
Name   VARCHAR2(10) ,
salary NUMBER(8,2),
TelNo  CHAR(8));
INSERT INTO sm_emp VALUES('0000000001','张飞',100000,'62613546');
INSERT INTO sm_emp VALUES('0000000002','关羽',100000,'62613546');
INSERT INTO sm_emp VALUES('0000000003','刘备',100000,'62613546');
--007的telno与别人不同
INSERT INTO sm_emp VALUES('0000000007','007',100000,'62634546');
COMMIT;


--验证sum(totalprice)是什么值
select sum(totalprice) from 
sm_saleorderlist where EMPLOYID='lll';

create or replace procedure set_salary 
( id char) is

	totalsal number:=0;
	v_salary number :=0;
	errid exception;
begin 
  
  --取得对应id的总销售额
  select sum(totalprice) into totalsal 
  from sm_saleorderlist where employid = id;
  
  --if 处理如没有对应人员的售额
  if totalsal is null then 
	--没有此编号的雇员的业绩
	raise  errid;
  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;

  exception
	when errid then 
	dbms_output.put_line('sorry !没有此雇员销售业绩');
  
end;

SQL> call set_salary(9);
sorry !没有此编号的雇员

调用完成。

SQL>  call set_salary('0000000001');