Oracle PL/SQL

示例的数据表下载的地址：http://download.csdn.net/detail/u010342038/9506955

示例1：

--启动后台输入

set serveroutput on
declare
  --定义基本变量类型
  --基本数据类型
  pnumber number(7,2);
  --字符串变量
  pname varchar2(20);
  --日期变量
  pdate date;
begin
  --赋值
  pnumber:=1;
  --打印该变量
  dbms_output.put_line(pnumber);
  
  pname:='Tom';
  dbms_output.put_line(pname);
  
  pdate:=sysdate;
  dbms_output.put_line(pdate);
  --计算明天的日期
  dbms_output.put_line(pdate+1);

end;

示例2：

set serveroutput on
--引用型变量
declare
  --定义引用型变量：查询并打印7839的姓名和薪水
  --pname varchar2(20);
  --psal number;(相当于下面二个)
  pname emp.ename%type;
  psal emp.sal%TYPE;
begin
  --得到7839的姓名和薪水(into 把ename,sal的值赋给pname,psal)
  select ename,sal into pname,psal from emp where empno=7839;
  --打印姓名和薪水
  dbms_output.put_line(pname||'的薪水是'||psal);
end;
/

示例3：

set serveroutput on
--使用记录变量，查询并打印7839的姓名和薪水
declare
  --定义记录型变量：注意代表一行
  emp_rec emp%rowtype;
begin
  --得到7839一行的信息
  select * into emp_rec from emp where empno=7839;
  --打印姓名和薪水
  dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/

示例4：

set serveroutput on
--接收一个键盘输入
--num：地址值，含义：在该地址上保存了输入的值
accept num prompt '请输入一个数字';


declare
  --定义变量保存用户从键盘输入的数字（number类型，所以键盘必须输入是数字，否则报错）
  pnum number:=#
begin
--执行if语句进行条件判断
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('其他数字是'|| pnum);
   end if;
   dbms_output.put_line(pnum);
end;
/

示例5：

set serveroutput on
declare
  --定义循环变量
  pnum number:=1;
begin
  /*
  --使用while循环打印数字的1-10
  while pnum <= 10 loop
    --循环体
    --打印该变量的值
    dbms_output.put_line(pnum);
    --使该变量+1
    pnum:=pnum + 1;
  end loop;
  */
  /*
  --使用loop循环打印1-10
  loop
    --退出条件：循环变量大于10
    exit when pnum > 10;
    --打印该变量的值
    dbms_output.put_line(pnum);
    --使该变量+1
    pnum := pnum + 1;
  end loop;
  */
  --范围为（1-10）
  for pnum in 1..10 loop
    --打印该变量的值
    dbms_output.put_line(pnum);
  end loop;
end;
/

示例6：

set serveroutput on
/*

  光标就是结果集

  光标的属性：
  %found：查找有值就返回true

  %notfound：查找没有值就返回true

  %isopen：判断光标是否打开，打开返回true

 %rowcount：影响的行数（如果总共有一百条记录，我取走10条记录，那影响的行数就是:10条）

 光标的限制：（一个会话只有300光标的限制，查看sql如下）

SQL> conn system/todaytech
已连接。
SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
ursor_sharing                       string      EXACT
cursor_space_for_time         boolean   FALSE
open_cursors                        integer     300
session_cached_cursors       integer     50

修改光标open的数量

alter system set open_cursors=400 scope=both;

scope的取值:both(包括memory/spfile),memory(表示只更改当前的实例),spfile（表示只更改参数文件，不更改当前实例，只有oracle重启才生效）；
*/
declare
  --定义一个光标(光标相当数组)
  cursor cemp is select ename,sal from emp;
  --
  pname emp.ename%type;
  psal  emp.sal%type;
begin
   --
   open cemp;
     loop
       --取一条记录
       fetch cemp into pname,psal;
       --exit when没有取到记录（cemp%notfound：=true）
       exit when cemp%notfound;
       --打印
       dbms_output.put_line(pname||'的薪水是'||psal);
     end loop;
   --关闭光标
   close cemp;
end;

光标%isopen和%rowcount的示例：

set serveroutput on
declare
   --定义光标代表给哪些员工涨工资
   cursor cemp is select empno,ejob from emp;
   pempno emp.empno%type;
   pjob   emp.ejob%type;
begin
  --打开光标
  open cemp;
  if cemp%isopen then dbms_output.put_line('光标已经打开');
  else dbms_output.put_line('光标没有打开');
  end if;
  loop
    --取出一个员工
    fetch cemp into pempno,pjob;
    exit when cemp%notfound;
    dbms_output.put_line('rowcount:'||cemp%rowcount);
  end loop;
  --关闭光标
  close cemp;
end;

示例7：

--给员工涨工资，总裁1000,经理800，其他400
set serveroutput on
declare
   --定义光标代表给哪些员工涨工资
   cursor cemp is select empno,ejob from emp;
   pempno emp.empno%type;
   pjob   emp.ejob%type;
begin
  --打开光标
  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+400 where empno=pempno;
    end if;
  end loop;
  --关闭光标
  close cemp;
  --对于oracle,默认的事务隔离级别是read committed
  --事务的ACID(原子性，一致性，隔离性，持久性)
  commit;
  dbms_output.put_line('涨工资完成');
end;

示例8：

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

set serveroutput on 
declare
  --定义带参数的光标
  cursor cemp(dno number) is select ename from emp where deptno=dno;
  pname emp.ename%type;
begin
  --打开光标（查询部门号为10的员工）
  open cemp(10);
  
  loop
    --取出每个员工姓名
    fetch cemp into pname;
    exit when cemp%notfound;
    dbms_output.put_line(pname);
  end loop;
  --关闭光标
  close cemp;
end;

示例9：

set serveroutput on
declare
  years varchar2(4);
  --Total number:=0;
  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 years;
      exit when cemp%notfound;
      --Total :=Total+1;
      if years = '1980' then count80:=count80+1;
      elsif years = '1981' then count81:=count81+1;
      elsif years = '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：

set serveroutput on
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
    exit when salTotal > 60000;
    fetch cemp into pempno,psal;
      exit when cemp%notfound;
      update emp set sal=sal*1.1 where empno=pempno;
      countEmp := countEmp + 1;
      salTotal :=salTotal + psal*0.1;
  end loop;
  close cemp;
  commit;
  dbms_output.put_line('人数：'|| countEmp||'，涨后的工资总额：'||salTotal);
end;

示例11：

/*
统计每个部门的人数和部门的工资总额
*/
set serveroutput on
declare
   cursor cdept is select deptno from dept;
   pdeptno dept.deptno%type;
   cursor cemp(pdeptno number) is select sal from emp where deptno=pdeptno;
   psal emp.sal%type;
   count1 number;
   count2 number;
   count3 number;
   saltotal number;
begin
   open cdept;
    loop
      --取出一个部门
      fetch cdept into pdeptno;
      exit when cdept%notfound;
      --初始化的工作
      count1 :=0;count2 :=0;count3 :=0;--saltotal:=0;--（方法1）
      --得到部门的工资总额
      select sum(sal) into saltotal from emp where deptno=pdeptno;
      --(这个效率低，因为要操作数据库，相于方法1)
      open cemp(pdeptno);
        loop
          fetch cemp into psal;
          exit when cemp%notfound;
            --判断薪水的范围
            if psal < 3000 then count1:= count1+1;
            elsif psal >= 3000 and psal < 6000 then count2:= count2+1;
            else count3:= count3+1;
            end if;
            --saltotal:= saltotal+psal;--（方法1）
        end loop;
      close cemp;
      --保存当前部门的结果（要在第一个loop内保存，否则会在初始化工作时初始化为0）
      insert into msg values(pdeptno,count1,count2,count3,nvl(saltotal,0));
    end loop;
   close cdept;
   commit;
   dbms_output.put_line('统计完成');
end;