Oracle学习总结4-PL/SQL

系统管理员 2021-03-27 12:16 501阅读 0赞

一 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 := &num;
    
    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;