oracle开发学习

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;

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

declare  pnum number:=1;begin for pnum in 1..10 loop  dbms_output.put_line(pnum);  end loop; end;

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

--光标的属性--%found  %notfounddeclare  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;

放入存储过程中嵌套

create or replace procedure proc_tmp_dept(out_cnt out number)asbegin  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;