Oracle 存储过程小小用法

--创建存储过程
create or replace procedure p (eno in number)
is
  empname varchar2(200);
  begin
    select ename into empname from emp where empno= eno;
    dbms_output.put_line('编号'||eno||'的姓名是'||empname);
  end p;
 
 execute p(7369);
 --带输出参数孤存储过程
 create or replace procedure p1(eno in number,outename out varchar2)
 is
  begin
    select ename into outename from emp where empno=eno;
  end p1;
 --执行带输出参数孤存储过程
  declare
   ename varchar2(200);
   begin
    p1(7369,ename);
    dbms_output.put_line(ename);
   end;
   --带输入输出参数的存储过程
   create or replace procedure p2(n1 in out number,n2 in out number)
   is
   ntemp number;
   begin
     ntemp := n1;
     n1 := n2;
     n2:=ntemp;
     dbms_output.put_line('n1='||n1);
     dbms_output.put_line('n2='||n2);
   end;
   --执行
   declare
   n1 number :=100;
   n2 number :=200;
   begin
     p2(n1,n2);
   end;
   --函数
    create or replace function a (eno number)
    return varchar2
    is
    en varchar2(200);
    begin
      select ename into en from emp where empno=eno;
      return en;
    end;
    --执行
    select a(7369) from dual;
   
     declare
     en varchar2(200);
     begin
       en :=a(7369);
       dbms_output.put_line(en);
     end;
     --自主事务处理
     create or replace procedure p3
     is
     a varchar2(200);
     --自主事务
     pragma autonomous_transaction;
     begin
       select ename into a from emp where empno=7369;
       dbms_output.put_line(a);
       rollback;
     end;
    
     create or replace procedure p4
     is
     b varchar2(200);
     begin
       update emp set ename='aaa' where empno=7369;
       p3();
       select ename into b from emp where empno=7369;
       dbms_output.put_line(b);
     end;
     --程序包
     --定义包的规范
      create or replace package p7 is
      procedure a (emno in number);
      function b(eno number) return varchar2;
      end p;
      --定义包的主体部分
      create or replace package body p7 is
      procedure a (emno in number)
      is
       emname varchar2(200);
       begin
         select ename into emname from emp where empno=eno;
       end a; 
       function b (eno number) return varchar2
       is
       emname varchar2(200);
       begin
         select ename into emname from emp where empno = eno;
         return emname;
       end b;
       end p7;
      
       --程序包中的游标
       create or replace package pack is
       cursor ord_cur (ceno number) return emp%rowtype;
       procedure ord_pro(pempno number);
       end pack;
      
       create or repalce package body pack is
       cursor ord_cur (ceno number) return emp%rowtype is select * from emp where empno=ceno;
       procedure ord_pro(pempno number)
       is
       or_rec emp%rowtype;
       begin
         open ord_cur(pemno);
         loop
           fetch ord_cur into or_rec;
           exit when ord_cur%notfound;
           dbms_output.put_line('返回值为:'||or_rec.ename);
         end loop;
         close ord_cur;
                 end ord_pro;
         end ord_cur;

       end pack;
      
      
       --创建包含以下列的my_toys表ID      
       --varchar2(5),
       --NAME    varchar2(20),
      -- PRICE    number
      --创建包含一个过程和一个函数的toypack包，将过程和函数分别命名为updatetoyprice和avgtoyprice。
      --过程应将每个玩具的单价增加10%，直到所有玩具的平均价格达到400。
      --此外，过程还应保证任一玩具的价格不超过500。
      create table my_toys(
      id varchar2(5),
      name varchar2(20),
      price number
      );
     
      create or replace package toypack is
      procedure updateToyPrice;
      function AvgToyPrice return number;
      end;
     
      create or replace package body toypack is
      procedure updateToyPrice as
      avgprice number;
      begin
        avgprice = AvgToyPrice;
        while (avgprice <=400)
         loop
          update my_toys set price=
          case when price*1.1 < 500 then pirce*1.1
          else price end;
          avgprice := AvgToyPrice;
         end loop;
         commit;
      end;
      function AvgToyPrice return number;
      as
      avgprice number;
      begin
       select avg(price) int avgprice from my_toys;
       return avgprice;
      end;
      end;
     
      ---------
      create or replace procedure
      rate_on_sal (e_code salary_detail.empcode%type)
      as
      v_salary number(10);
      begin
        select salary into v_salary from salary_details where empcode=e_code;
        v_salary :=v_salary*0.95;
        dbms_output.put_line(v_salary);
      end;
     
      -----------上机--员工月工资计算
      create  table in_salary(
       id varchar2(20),
       in_salary number,--应扣
       tody date
      );
      --=======
      create table out_salary(
        id varchar2(20),
        out_salary number,--应发
        today date
      );
     
      insert into in_salary values('7369',200,sysdate);
      insert into in_salary values('7499',300,sysdate);
      insert into in_salary values('7521',400,sysdate);
      insert into in_salary values('7566',100,sysdate);
     
      create or replace procedure cal_salary
      as
       --cursor cur is select sal,nvl(comm) from emp;
       rowresult emp%rowtype;
       out_money number;
      begin
       open cur;
       loop
        fetch cur into rowresult;
        exit when cur%notfound;
        dbms_output.put_line('用户名:'||rowresult.ename||'SAL'||rowresult.sal);
         begin
        select in_salary into out_money from in_salary where id =rowresult.empno;
        dbms_output.put_line(rowresult.empno||'应扣的钱'||out_money);
        insert into out_salary values(rowresult.empno,rowresult.sal+rowresult.comm-out_money,sysdate);
      
        exception
         when others then 
        dbms_output.put_line('这个员工没有钱');
        insert into in_salary values(rowresult.empno,0,sysdate);
        end;
      end loop;
     
      end;
     
      execute cal_salary;