卢雨杰12.7

学习内容：

--查询 JAMES 的工资，如果大于900元，则发奖金800元。
declare 
 mysal emp.sal%type;
begin 
  select sal into mysal from emp where ename = 'KING';
  if mysal > 900 then
    update emp set comm = 900 where ename  = 'KING';
    end if;
    COMMIT;
end; 
-- 示例：查询JAMES的工资，如果大于1500元，则发放奖金100元，如果工作大于900元，则发奖金800
-- 元，否则发奖金400元。

update emp set sal = 1600 where ename = 'JAMES';

declare
  mysal emp.sal%type ;
begin 
  select sal into mysal from emp  where ename = 'JAMES';
  if mysal > 3000 then
    update emp set comm = 300 where ename = 'JAMES';
  elsif mysal > 2000 and mysal <= 3000 then 
    update emp set comm = 200 where ename = 'JAMES';
  elsif mysal >1500 and mysal <= 2000 THEN
    update emp set comm = 150 where ename = 'JAMES';
  else 
    update emp set comm = 100 where ename = 'JAMES'; 
    end if ;
    commit;
end;     

-- -- 无子句 EXECUTE IMMEDIATE
  begin 
    execute immediate 
    'create table aa as select * from emp ';   
  end;  


-- using 子句
declare 
  n number :=123;
  na varchar(20) :='WANGWU';
  S number :=2000;

begin 
  execute immediate 
  'insert into aa(empno,ename,sal) values(:1,:2,:3)'
  using n, na, s;
  commit;
end;

-- into 子句
declare
 e emp%rowtype;
begin 
  execute immediate
  'select * from aa where empno = :1'
  into e 
  using 7788;
  dbms_output.put_line(e.empno|| '-----' || e.ename);
end;  

--returning 子句
declare
    empid number(4) :=7788;
    salary number(7,2);
begin 
  execute immediate
  'update aa set sal = 2222 where empno = :1 returning sal into :2'
  using empid
  returning into salary;
  --输出
  dbms_output.put_line(salary);
 end;  


 --异常处理
declare 
   newsal emp.sal%type;
begin 
  select sal into newsal from emp;
   exception 
     when TOO_MANY_ROWS then
       dbms_output.put_line('返回纪录太多');
       when others then 
       dbms_output.put_line('未知错误');
      
end;


--示例：EMP表中员工 ALLEN 在入职时劳动合同条款是每半年工资提升5%。为调用方便，请编写过程实
--现该操作。
create or replace procedure Displaysal
as
    newsal emp.sal%type;
    oldsal emp.sal%type;
begin 
   select sal into oldsal from emp where ename = 'luyujie';

   update emp set sal =sal+sal*0.05  where ename = 'luyujie';

   SELECT sal into newsal from emp where ename = 'luyujie';
     
   dbms_output.put_line('原来的工资是' || oldsal || '现在的工资是' || newsal);
   exception
   when no_data_found then e
     dbms_output.put_line('找不到此员工');
end;    

begin
  Displaysal;
end;


--带参数的过程，示例：根据员工编号和工资增长率输出员工的工资。
create or replace procedure Display(pere number,eno number)
as
    newsal emp.sal%type;
    oldsal emp.sal%type;
    en emp.ename%type;
begin
  select sal into oldsal from emp where emp.empno = eno;

  update emp set sal = sal+sal*pere where emp.empno =eno;

  select ename ,sal into en,newsal from emp where emp.empno = eno;

  dbms_output.put_line(en || '原来的工资是' || oldsal || '现在的工资是' ||newsal);
  exception
    when no_data_found then 
      dbms_output.put_line('此用户不存在');
end Display;

begin
  Display(0.1,7788);
end;   
      
      
      
      
      
      mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc14 -
Dversion=10.2.0.5.0 -Dpackaging=jar -Dfile=D:\ojdbc14.jar