Oracle学习总结4-PL/SQL

一 PL/SQL

1.语法：

declare

begin

exception

end

2.变量类型：

char,varchar2,date,number,boolean,long,

%type：引用类型

%rowtype:记录

 

3.if语句

if 条件 then 语句1；

elsif 条件 then 语句2；

else 语句3

end if;

4.循环语句

loop

exit when 条件；

执行语句；

end loop;

 

5.游标（cursor）:用来过程化处理一条或者多条记录

定义：cursor pc is select * from emp;

 

定义想要接收的值：pemp emp%rowtype;

 

打开游标：open pc；

 

接收游标的值：fetch pc into pemp;

 

游标结束方式：exit when pc%notfound

 

光标的属性：%isopen   %rowcount(影响的行数)

                 %found    %notfound

6.例外

使用：

exception

          when 异常名1 then 处理语句1；

          when 异常名2 then 处理语句2；

 

系统定义的例外：

no_data_found(没有找到数据)

too_many_rows(into值时匹配到多行数据)

zero_divide(被0除)

value_error(算术或者转换错误)

timeout_on_resource(等待资源发生超时，分布式系统中会遇到)

 

自定义异常：

         异常定义：no_emp_found exception;

         调用自定义异常：if 条件 then raise 自定义异常名；

自定义异常处理：

exception

         when 自定义异常名 then 处理语句；

二 PL/SQL操作

在cmd命令窗口显示：set serveroutput on

1.hello world

declare

begin

   dbms_output.put_line('Hello world');

end;

2.--得到7839的姓名和薪水

declare

pename emp.ename%type;

pesal emp.sal%type;

begin

   select ename,sal into pename,pesal from emp where empno='7839';

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

end;

 

或者

declare

pemp emp%rowtype;

begin

   select * into pemp from emp where empno='7839';

   dbms_output.put_line(pemp.ename||'薪水是'||pemp.sal);

end;

3.-- 判断用户从键盘输入的数字（PL/SQL中会报错，sqldeveloper不会）

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=2 then dbms_output.put_line('亲输入的是2');

      else dbms_output.put_line('亲输入的是其他数字');

    end if;

end;

4．输出一个1-10的循环

declare

   pnum number:=1;

begin

   loop

        exit when pnum>10;

        dbms_output.put_line(pnum);

        pnum:=pnum+1;      

   end loop;

end;

 

5. -- 查询并打印员工的姓名和薪水       注意连接符||,不能用+连接

declare

   cursor pc is select * from emp;

   pemp emp%rowtype;

  

begin

   open pc;

         loop

              fetch pc into pemp;

              exit when pc%notfound;

             

              dbms_output.put_line(pemp.ename||':'||pemp.sal);   

         end loop;

    close pc;

end;

 

 

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

declare

   cursor pc is select empno,job from emp;

   pempno emp.empno%type;

   pjob emp.job%type;

begin

   rollback;

   open pc;

   loop

        --取数据

        fetch pc into pempno,pjob;

        exit when pc%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+400 where empno=pempno;

        end if;

    end loop;

     

   close pc;

  

   commit;

end;

 

7. -- 查询某个部门的员工姓名

declare

   cursor pc(dno number) is select ename from emp where deptno=dno;

   pename emp.ename%type;

begin

   open pc(20);--在这里传参

        loop

          fetch pc into pename;

          exit when pc%notfound;

          dbms_output.put_line(pename);

        end loop;

   close pc;

end;

 

8. -- 系统例外

declare

   i number;

begin

   i:=1/0;

  

   exception

   when zero_divide then dbms_output.put_line('1:0不能做分母');

end;

 

9. -- 自定义例外查询50号部门的员工姓名

declare

   cursor pc is select ename from emp where deptno=50;

   pename emp.ename%type;

  

   no_emp_found exception;

begin

   open pc;  

 

     fetch pc into pename;

     if pc%notfound then  raise no_emp_found;

     end if;

   close pc;

  

   exception

         when no_emp_found then dbms_output.put_line('没有记录');

         when others then dbms_output.put_line('其它异常');

end;

 

10.瀑布模型

         需求分析-概要设计-详细设计-编程-测试-上线

 

11．-- 1980、1981、1982、1987入职员工的个数

--思路，计数器，遍历的时候，判断年份，是哪一年就给哪一年的计数加1

--sql语句

--变量

 

declare

   cursor pc is select to_char(hiredate,'yyyy') from emp;

   theyear varchar2(20);

   count80 number:=0;

   count81 number:=0;

   count82 number:=0;

   count87 number:=0;

begin

   open pc;

   loop

        fetch pc into theyear;

        exit when pc%notfound;

        if theyear='1980' then count80:=count80+1;

        elsif theyear='1981' then count81:=count81+1;

        elsif theyear='1982' then count82:=count82+1;

        else count87:=count87+1;

        end if;

       

   end loop;

  

   close pc;

  

   dbms_output.put_line(count80+count81+count82+count87||'---'||count80||'---'||count81||'---'||count82||'---'||count87);

end;

 

12. -- 工资从低往高，每个员工涨10%工资，但所有员工总工资不能超过5万元,输出涨工资的人数和涨后的工资总额

--遍历的sql语句：select * from emp order by sal;

--退出遍历条件：1结束2总工资超过5W

--变量  遍历变量工资员工no 工资总额涨工资总人数

 

declare

   cursor pc is select empno,sal from emp order by sal;

   pempno emp.empno%type;

   psal emp.sal%type;

  

   countNum number:=0;

   totalSal number;

begin

   rollback;

   select sum(sal) into totalSal from emp;

   open pc;

   loop

        exit when totalSal>50000;

        fetch pc into pempno,psal;

        exit when pc%notfound;

        if totalSal<50000  then

                if totalSal+psal*0.1<50000 then

                      update emp set sal=sal*1.1 where empno=pempno;

                      totalSal:=totalSal+psal*0.1;

                      countNum:=countNum+1;

                end if;

        end if;

   end loop;

   close pc;

   dbms_output.put_line('涨薪人数'||countNum||'，总工资'||totalSal);

   commit;

 end;

 

 

 

13.

 

 

--sql：select deptno from dept

--sql: select sal from emp where deptno=dno;

 

--变量：count0 count3 count6 totalSal

declare

    cursor pc0 is select deptno from dept;

    cursor pc1(dno number) is select sal from emp where deptno=dno;

   

    dno dept.deptno%type;

    psal emp.sal%type;

    count0 number;

    count3 number;

    count6 number;

    totalSal number;

begin

    --外循环确定遍历的部门号

    open pc0;

    loop

         fetch pc0 into dno;

         --重置变量

         count0:=0;

         count3:=0;

         count6:=0;

         totalSal:=0;

         exit when pc0%notfound;

         open pc1(dno);

         loop--内循环根据条件设置或统计值

              fetch pc1 into psal;    

              exit when pc1%notfound;

              totalSal:=totalSal+psal;

              if psal<3000 then count0:=count0+1;

              elsif psal<6000 then count3:=count3+1;

              else count6:=count6+1;

              end if;

         end loop;

         close pc1;

         --在新的一张表中，存这些数据

         insert into msg values(dno,count0,count3,count6,totalSal);

    end loop;

    close pc0;

    commit;

 end;