Oracle存储过程开发学习-无忧网

1.rowtype的使用

            create
            or
            replace
            procedure PD_ROWTYPE is
   v_emp_rec emp%rowtype;
beginselect*into v_emp_rec from emp where empno=7839;
   dbms_output.put_line(v_emp_rec.ename||‘的薪水是‘||v_emp_rec.sal);
end PD_ROWTYPE;

2.判断用户从键盘输入的数字

accept num prompt‘请输入一个数字‘;
declare  
  pnum number :=&num;
beginif pnum=0then dbms_output.put_line(‘您输入的数字是0‘);
     elsif pnum=1then dbms_output.put_line(‘您输入的数字是1‘);
     elsif pnum=1then dbms_output.put_line(‘您输入的数字是2‘);
     else dbms_output.put_line(‘其他数字‘);
  endif;
end;

3.使用while循环打印数字的1~10

            declare
            
  pnum 
            number:=1;
beginwhile pnum<=10 loop
    dbms_output.put_line(pnum);
    pnum:=pnum+1;
  end loop;
end;

4.使用loop循环打印

            declare
            
 pnum 
            number:=1;
begin
 loop
    exitwhen pnum>10;
    dbms_output.put_line(pnum);
    pnum:=pnum+1;
 end loop;
end;

5. 使用for循环打印1~10

            declare
            
 pnum 
            number:=1;
beginfor pnum in1..10 loop
  dbms_output.put_line(pnum);
 end loop;
end;

6.-查询并打印员工的姓名和薪水

            --
            光标的属性
            
--
            %found  %notfound
            declare
            cursor cemp isselect ename,sal from emp;
--为光标定义对应的变量
 pename emp.ename%type;
 psal   emp.sal%type;
begin-- Test statements hereopen cemp;
 loop
   --取一条记录fetch cemp into pename,psal;
   --思考：1.循环什么时候退出？2.fetch不一定能取到记录exitwhen cemp%notfound;
   dbms_output.put_line(pename||‘的薪水是‘||psal);
   --打印end loop;
 close cemp;
end;

7.给员工涨工资，总裁1000，经理800，其他400

            declare
            cursor cemp isselect empno,job from emp;
 pempno emp.empno%type;
 pjob emp.job%type;
begin-- Test statements hereopen cemp;
 loop
   --取出一个员工fetch cemp into pempno,pjob;
   exitwhen cemp%notfound;
   --判断员工的职位if pjob=‘PRESIDENT‘thenupdate emp set sal=sal+1000where empno=pempno;
   elsif pjob=‘MANAGER‘thenupdate emp set sal=sal+800where empno=pempno;
   elseupdate emp set sal=sal+500where empno=pempno;
   endif;  
 end loop;
 close cemp;
 --事物的提交commit;
 dbms_output.put_line(‘涨工资完成‘);
end;

8.光标
1)光标的属性
%found %notfound
%isopen 判断光标是否打开
%rowcount 影响的行数，不是总行数，到目前取走的行数
2)光标的限制:默认情况下，oracle数据库只允许在同一个会话中，打开300个光标
修改光标数的限制：
alter system set open_cursor=400 scope=both;
9.统计每年的入职员工数

            declare
            cursor cemp isselect to_char(hiredate,‘yyyy‘) from emp;
 phiredate varchar2(4);
 --每年入职的员工人数
 count80 number:=0;
 count81 number:=0;
 count82 number:=0;
 count87 number:=0;
beginopen cemp;
 loop
   --取出一个员工的入职年份fetch cemp into phiredate;
   exitwhen cemp%notfound;
   --判断入职年份if phiredate =‘1980‘then count80:=count80+1;
      elsif phiredate =‘1981‘then count81:=count81+1;
      elsif phiredate =‘1982‘then count82:=count82+1;
      else count87:=count87+1;
   endif;
 end loop;
 close cemp;
 --输出结构
 dbms_output.put_line(‘Total:‘||(count80+count81+count82+count87));
 dbms_output.put_line(‘1980:‘||count80);
 dbms_output.put_line(‘1981:‘||count81);
 dbms_output.put_line(‘1982:‘||count82);
 dbms_output.put_line(‘1987:‘||count87);
end;

10、员工涨工资问题（有bug待完善）

            declare
            cursor cemp isselect empno,sal from emp orderby sal;
  pempno emp.empno%type;
  psal   emp.sal%type;
  --涨工资的人数
  countEmp number:=0;
  --涨后的工资总额
  salTotal number;
begin--得到工资总额的初始值selectsum(sal) into salTotal from emp;
 --打开光标open cemp;
 loop
   --1工资总额>5wexitwhen salTotal>50000;
   --取一个员工涨工资fetch cemp into pempno,psal;
   --2%notfoundexitwhen cemp%notfound;
   --涨工资update emp set sal*1.1 empno=pempno;
   countEmp:=countEmp+1;
   --涨后的工资总额=涨钱的工资总额+sal*0.1
   salTotal:=salTotal+psal*0.1;
 end loop;
 --关闭光标close cemp;
 commit;
 dbms_output.put_line(‘人数：‘||countEmp||‘涨后的工资总额:‘||salTotal);
end;

11、对比2种存储过程的写法，该存储过程是用来为emp1表中对于不同层次工资进行相应的增长，2中写法都能达到同样的效果。
第一种写法：

            declare
            cursor csr_update isselect*from emp1;
 empinfo csr_update%rowtype;
 saleinfo emp1.sal%type;
 empnoinfo emp1.empno%type;
beginopen csr_update;
  loop 
    fetch csr_update into empinfo;
    exitwhen csr_update%notfound;
      IF empInfo.SAL<1500THEN
        saleInfo:=empInfo.SAL*1.2;
       elsif empInfo.SAL<2000THEN
        saleInfo:=empInfo.SAL*1.5;
        elsif empInfo.SAL<3000THEN
        saleInfo:=empInfo.SAL*2;
        else
          saleInfo:=empInfo.sal;
      ENDIF;
      empnoinfo:=empInfo.empno;
      update emp1 set sal=saleinfo where empno=empnoinfo;
  end loop;
  close csr_update;
end;

第二种写法：

            declare
            cursor
            
        csr_Update 
            is
            select
            *
            from  emp1 forupdateOF SAL;
        empInfo csr_Update%rowtype;
        saleInfo  emp1.SAL%TYPE;
beginFOR empInfo IN csr_Update LOOP
      IF empInfo.SAL<1500THEN
        saleInfo:=empInfo.SAL*1.2;
       elsif empInfo.SAL<2000THEN
        saleInfo:=empInfo.SAL*1.5;
        elsif empInfo.SAL<3000THEN
        saleInfo:=empInfo.SAL*2;
        else
          saleInfo:=empInfo.sal;
      ENDIF;
      UPDATE emp1 SET SAL=saleInfo WHERECURRENTOF csr_Update;
     END LOOP;
END;

12、对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50元，输出更新前后的薪水，员工姓名，所在部门编号。

            declare
            cursor
            
        csr_Update 
            is
            select
            *
            from  emp1 forupdateOF SAL;
        empInfo csr_Update%rowtype;
        saleInfo  emp1.SAL%TYPE;
beginFOR empInfo IN csr_Update LOOP
      IF empInfo.SAL<1500THEN
        saleInfo:=empInfo.SAL*1.2;
       elsif empInfo.SAL<2000THEN
        saleInfo:=empInfo.SAL*1.5;
        elsif empInfo.SAL<3000THEN
        saleInfo:=empInfo.SAL*2;
        else
          saleInfo:=empInfo.sal;
      ENDIF;
      UPDATE emp1 SET SAL=saleInfo WHERECURRENTOF csr_Update;
     END LOOP;
END;

13.放入存储过程中嵌套

            declare
            cursor
            
        csr_Update 
            is
            select
            *
            from  emp1 forupdateOF SAL;
        empInfo csr_Update%rowtype;
        saleInfo  emp1.SAL%TYPE;
beginFOR empInfo IN csr_Update LOOP
      IF empInfo.SAL<1500THEN
        saleInfo:=empInfo.SAL*1.2;
       elsif empInfo.SAL<2000THEN
        saleInfo:=empInfo.SAL*1.5;
        elsif empInfo.SAL<3000THEN
        saleInfo:=empInfo.SAL*2;
        else
          saleInfo:=empInfo.sal;
      ENDIF;
      UPDATE emp1 SET SAL=saleInfo WHERECURRENTOF csr_Update;
     END LOOP;
END;

原文：http://www.cnblogs.com/cancer-sun/p/5168420.html

【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！

Oracle存储过程开发学习

相关教程推荐

其他课程推荐