oracle中pl/sql编程案例

oracle中pl/sql编程案例

pl/sql是oracle对标准数据库语言的扩展，是一种过程式语言，也是oracle的专用语言。

为了对pl/sql有更深的了解，举几个pl/sql的案例分享出来。

案例一：打印九九乘法表

SET SERVEROUTPUT ON
DECLARE
  v_i NUMBER(4);
  v_j NUMBER(4);
  v_num NUMBER;
BEGIN
  FOR v_i IN 1..9
  LOOP
    FOR v_j IN 1..v_i
    LOOP
      v_num:=v_i*v_j;
      DBMS_OUTPUT.PUT(v_i||'*'||v_j||'='||v_num||' ');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(NULL);
  END LOOP;
END;
/

案例二：统计每年入职的员工人数。

<span style="font-size:14px;">set serveroutput on
declare
  chiredate varchar2(4);
  count80 number:=0;
  count81 number:=0;
  count82 number:=0;
  count87 number:=0;
  cursor cemp is select to_char(hiredate,'yyyy') from emp;
begin
  open cemp;
  loop
    fetch cemp into chiredate;
  exit when cemp%notfound;
    if chiredate='1980' then count80:=count80+1;
    elsif chiredate='1981' then count81:=count81+1;
    elsif chiredate='1982' then count82:=count82+1;
    else count87:=count87+1;--注意这里的if语句，我很容易出错。
    end if;
  end loop;
  dbms_output.put_line('sum:'||(count81+count82+count80+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);
  close cemp;
end;
/</span>

案例三：实现按部门分段（6000以上、3000元以下、3000-6000）统计各工资段的职工人数、以及各
部门的工资总额（工资总额中不包括奖金）。将部门号、各工资段职工人数、各部门的工资总额保存在
newtable表中。

首先创建newtable表：

<span style="font-size:14px;">create table newtable(
deptno number,
count1 number,
count2 number,
count3 number,
saltotal number
);</span>

pl/sql程序：

<span style="font-size:14px;">set serveroutput on
declare
  cursor dept_cursor is select deptno from dept;
  cdept dept.deptno%type;
  
  cursor emp_cursor(dno number) is select sal from emp where deptno=dno;
  cemp emp.sal%type;
  --用三个计数器统计各个工资段的人数
  count1 number;
  count2 number;
  count3 number;
  
  saltotal number;
begin
  open dept_cursor;
  loop
    fetch dept_cursor into cdept;
  exit when dept_cursor%notfound;
    count1:=0;
    count2:=0;
    count3:=0;
    select sum(sal) into saltotal from emp where deptno=cdept;
    open emp_cursor(cdept);
    loop
      fetch emp_cursor into cemp;
    exit when emp_cursor%notfound;
      if cemp<3000 then count1:=count1+1;
      elsif cemp>3000 and cemp<6000 then count2:=count2+1;
      else count3:=count3+1;
      end if;
    end loop;
    close emp_cursor;
  
    insert into newtable values(cdept,count1,count2,count3,nvl(saltotal,0));
  end loop;
  close dept_cursor;
  --提交。
  commit;
  dbms_output.put_line('操作完成！');
end;
/
--编写pl/sql程序要养成好的习惯，才不会出错。
--养成好习惯：开、关光标；首先将条件语句、循环语句的完整结构写出来，否则忘记了要查找老半天
--变量名的选取要尽可能不同，一混淆就完了。
</span>