工资_ok.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;



------------------------------------------基本-----------------------------
create or replace procedure set_salary ( id char) is
	totalsal number :=0;
	v_salary number :=0;
	no_total exception;
begin 
  
  --取得对应id的总销售额
  select sum(totalprice)into totalsal 
  from sm_saleorderlist where employid = id;

  if totalsal is null then 
    raise no_total;
  end 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;
		--注意:此时没有此雇员的销售情况,被else处理!

  	end if;
	
	dbms_output.put_line('雇员' || id || 'salary is ' || v_salary);
  	update sm_emp set salary =v_salary where empid = id;
  	commit;
exception 
  when no_total then 
    dbms_output.put_line('emp' || id || '没有销售额');
  
end;

--没区分em_emp 中无,sm_saleorderlist 中无