ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Oracle开发学习

Oracle开发学习

原创 Oracle 作者：梓沐 时间：2015-12-23 16:22:53 0 删除 编辑

1.rowtype的使用

create or replace procedure PD_ROWTYPE is

   v_emp_rec emp%rowtype;

begin

   select * 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 :=#

begin

  if pnum=0 then dbms_output.put_line('您输入的数字是0');

     elsif pnum=1 then dbms_output.put_line('您输入的数字是1');

     elsif pnum=1 then dbms_output.put_line('您输入的数字是2');

     else dbms_output.put_line('其他数字');

  end if;

end;

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

declare

  pnum number:=1;

begin

  while pnum<=10 loop

    dbms_output.put_line(pnum);

    pnum:=pnum+1;

  end loop;

end;

4.使用loop循环打印

declare

 pnum number:=1;

begin

 loop

    exit when pnum>10;

    dbms_output.put_line(pnum);

    pnum:=pnum+1;

 end loop;

end;

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

declare

 pnum number:=1;

begin

 for pnum in 1..10 loop

  dbms_output.put_line(pnum);

 end loop;

end;

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

--光标的属性

--%found  %notfound

declare

 cursor cemp is select ename,sal from emp;

--为光标定义对应的变量

 pename emp.ename%type;

 psal   emp.sal%type;

begin

 -- Test statements here

 open cemp;

 loop

   --取一条记录

   fetch cemp into pename,psal;

   --思考：1.循环什么时候退出？2.fetch不一定能取到记录

   exit when cemp%notfound;

   dbms_output.put_line(pename||'的薪水是'||psal);

   --打印

 end loop;

 close cemp;

end;

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

declare

 cursor cemp is select empno,job from emp;

 pempno emp.empno%type;

 pjob emp.job%type;

begin

 -- Test statements here

 open cemp;

 loop

   --取出一个员工

   fetch cemp into pempno,pjob;

   exit when cemp%notfound;

   --判断员工的职位

   if pjob='PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;

   elsif pjob='MANAGER' then update emp set sal=sal+800 where empno=pempno;

   else update emp set sal=sal+500 where empno=pempno;

   end if;

 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 is select to_char(hiredate,'yyyy') from emp;

 phiredate varchar2(4);

 --每年入职的员工人数

 count80 number:=0;

 count81 number:=0;

 count82 number:=0;

 count87 number:=0;

begin

 open cemp;

 loop

   --取出一个员工的入职年份

   fetch cemp into phiredate;

   exit when 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;

   end if;

 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 is select empno,sal from emp order by sal;

  pempno emp.empno%type;

  psal   emp.sal%type;

  --涨工资的人数

  countEmp number:=0;

  --涨后的工资总额

  salTotal number;

begin

 --得到工资总额的初始值

 select sum(sal) into salTotal from emp;

 --打开光标

 open cemp;

 loop

   --1工资总额>5w

   exit when salTotal>50000;

   --取一个员工涨工资

   fetch cemp into pempno,psal;

   --2%notfound

   exit when 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 is select * from emp1;

 empinfo csr_update%rowtype;

 saleinfo emp1.sal%type;

 empnoinfo emp1.empno%type;

begin

  open csr_update;

  loop

 

    fetch csr_update into empinfo;

    exit when csr_update%notfound;

      IF empInfo.SAL<1500 THEN

        saleInfo:=empInfo.SAL*1.2;

       elsif empInfo.SAL<2000 THEN

        saleInfo:=empInfo.SAL*1.5;

        elsif empInfo.SAL<3000 THEN

        saleInfo:=empInfo.SAL*2;

        else

          saleInfo:=empInfo.sal;

      END IF;

      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 for update OF SAL;

        empInfo csr_Update%rowtype;

        saleInfo  emp1.SAL%TYPE;

begin

    FOR empInfo IN csr_Update LOOP

      IF empInfo.SAL<1500 THEN

        saleInfo:=empInfo.SAL*1.2;

       elsif empInfo.SAL<2000 THEN

        saleInfo:=empInfo.SAL*1.5;

        elsif empInfo.SAL<3000 THEN

        saleInfo:=empInfo.SAL*2;

        else

          saleInfo:=empInfo.sal;

      END IF;

      UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_Update;

     END LOOP;

END;

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

DECLARE

     CURSOR

     crs_testAvg

     IS

     select EMPNO,ENAME,JOB,SAL,DEPTNO,AVG(SAL) OVER (PARTITION BY DEPTNO ) AS DEP_AVG

     FROM EMP1 for update of SAL;

     r_testAvg crs_testAvg%rowtype;

     salInfo emp1.sal%type;

     begin

     for r_testAvg in crs_testAvg loop

     if r_testAvg.SAL>r_testAvg.DEP_AVG then

     salInfo:=r_testAvg.SAL-50;

     else salInfo:=r_testAvg.SAL;

     end if;

     update emp1 set SAL=salInfo where current of crs_testAvg;

     end loop;

end;

13、放入存储过程中嵌套

create or replace procedure proc_tmp_dept

(out_cnt out number)

as

begin

  declare cursor cur_dept is select deptno from dept;

  v_count number;

  --此begin不能少，不写此begin与对应的end（即for loop代码块不用begin end括起来，会报编译错误）

  begin

    for dept_rec in cur_dept

      loop

        insert into temp_dept(deptno) values(dept_rec.deptno);

        v_count:=1000000;

        out_cnt:=v_count;

      end loop;

  end;

end;

14、type字典放入存储过程

create or replace procedure pd_test

(

  in_req_id       varchar2,

  out_no     out  varchar2

)

is

  test  tb_test%rowtype;

  type   type_record is record

         (test_code varchar2(50),

          money varchar2(32));

  type_record_user type_record;

  type type_array is table of type_record_user%type index by binary_integer;

  var_array type_array;

begin

  select * into test from tb_fund_test where req_id = in_req_id;

  if test.opr_type = 'A' then

    pd_mid_test(test.test_id, test.test_type, test.user_id, var_array(1).money,

                            var_array(2).money, var_array(3).money, var_array(4).money,

                            var_array(5).money, var_array(6).money, var_array(7).money);

    var_array(1).test_code:='10001';

    var_array(2).test_code:='10002';

    var_array(3).test_code:='10003';

    var_array(4).test_code:='10004';

    var_array(5).test_code:='10005';

    var_array(6).test_code:='10006';

    var_array(7).test_code:='10007';

    for i in 1..var_array.count loop

      dbms_output.put_line(var_array(i).money||','||var_array(i).test_code);

    end loop;

  elsif test.code_type = 'B' then

    out_no := '1';

  end if;

end pd_test;

15、分解字符串函数

CREATE OR REPLACE FUNCTION fc_sys_para

(

  f_srcstr VARCHAR2,

  f_lkpstr VARCHAR2

)

RETURN VARCHAR2

IS

  v_max_len NUMBER(18, 6);

  v_cur_pos NUMBER(18, 6);

  v_rtn_str VARCHAR(1024);

BEGIN

  v_rtn_str := '';

  v_cur_pos := instr(upper(f_srcstr), upper(f_lkpstr) || '=');

 

  IF v_cur_pos > 0 THEN

 

    v_cur_pos := v_cur_pos + length(f_lkpstr) + 1;

    v_max_len := length(f_srcstr) - v_cur_pos + 1;

 

    IF v_max_len > 0 THEN

      v_rtn_str := substr(f_srcstr, v_cur_pos, v_max_len);

      v_cur_pos := instr(v_rtn_str, ',');

 

      IF v_cur_pos > 0 THEN

        v_rtn_str := substr(v_rtn_str, 1, v_cur_pos - 1);

      END IF;

 

    END IF;

 

  END IF;

 

  RETURN rtrim(ltrim(v_rtn_str));

END fc_sys_para;

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29812844/viewspace-1878490/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： Oracle数据泵的备份与恢复
下一篇： [DB2]linux下安装db2 v9.7
请登录后发表评论 登录
全部评论
梓沐
专家姓名：孙伟
擅长PLS/QL开发，SQL调优和改写，数据库设计

注册时间：2014-08-18

  • 博文量
    161
  • 访问量
    1204422

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593