Oracle数据库之PL/SQL

Oracle数据库总结：

Oracle数据库之集合运算
Oracle数据库之数据处理
Oracle数据库之建表和管理表
Oracle数据库之对象视图、索引、序列、同义词

一、语法结构：

declare 
--说明部分
begin
--sql语句
exception
--例外处理语句
end;
/

二、变量说明

变量类型：char、varhchar2、number、long、date、boolean
普通变量：
    var1 varchar2(20)；
记录型变量：
    emp1 emp%rowtype；
引用型变量：
    emp2 emp.empno%type;

--查询并打印5478的姓名和薪水
set serveroutput on

declare
 --定义变量保存姓名和薪水
 --pename varchar(20);
  --psal number;
  pename emp.ename%type;
  psal        emp.sal%type;
begin
 --得到姓名和薪水
 select ename,sal into pename,psal from emp where empno=7839;
  dbms_output.put_line(pename||'的薪水是'||psal);
end;
/

--查询并打印5478的姓名和薪水
set serveroutput on
declare
 --定义记录型变量：代表一行
 emp_rec emp%rowtype;
begin
 select * into emp_rec from emp where empno=7839;

  dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/

三、循环体
1）if判断语句
语法结构：

if 条件 then 语句1
语句2
end if;

if 条件 then 语句1
语句2
elsif 条件 then 语句3
语句4
end if;

if 条件 then 语句1
语句2
elsif 条件 then 语句3
语句4
else
语句5
end if;

--判断用户从键盘上输入的数字
set serveroutput on

--接收键盘输入 
--num: 地址值，在地址上保存了输入的数字
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;
/

2）循环结构

while 条件
loop
语句
end loop；

loop
exit when 条件
语句
end loop；

for i in 1..3
loop
语句
end loop;

--打印1~10
set serveroutput on
declare
 pnum number := 1;
begin
 loop
 --退出条件
 exit when pnum > 10;

    dbms_output.put_line(pnum);

    --加一
    pnum := pnum + 1;
  end loop;
end;
/

四、光标（cursor）
1）语法规则

cursor cursorname[(参数名 数据类型...)]
is select语句

2）使用步骤

打开光标：open cursorname;

抓取数据：fetch cursorname into 变量;

关闭光标：close cursorname;

--查询并打印员工的姓名和薪水
/*
1. 光标的属性
 %isopen %rowcount (影响的行数)
 %found %notfound

*/
set serveroutput on
declare
 -- 定义光标
 cursor cemp is select ename,sal from emp;
  pename emp.ename%type;
  psal       emp.sal%type;
begin
 --打开光标
 open cemp;

  loop
    --取一条记录
    fetch cemp into pename,psal;
    --exit when 没有取到记录;
    exit when cemp%notfound;

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

  end loop;

  --关闭光标
  close cemp;
end;
/

set serveroutput on

declare
 --说明部分
begin
 --程序
 dbms_output.put_line('Hello World');
end;
/

五、异常处理

1）系统异常

no_data_found：没有数据
to_many_rows:太多行
zero_divide:被0除
value_error:算数或者转换错误
timeout_on_resource:等待资源时超时

--被0除
set serveroutput on

declare 
  pnum number;
begin
  pnum := 1/0;

exception
  when zero_divide then dbms_output.put_line('1:0不能做被除数');
                                         dbms_output.put_line('2:0不能做被除数');
  when value_error then dbms_output.put_line('算术或者转换错误');
  when others then dbms_output.put_line('其他例外');
end;
/

实例一：

--涨工资，总裁10000 经理5000 其他4000
set serveroutput on

declare
 --员工的集合
 --alter table "SCOTT"."EMP" rename column "JOB" to empjob
 cursor cemp is select empno,empjob from emp;
  pempno emp.empno%type;
  pjob   emp.empjob%type;
begin
 rollback;

  open cemp;

  loop
    --取一个员工
    fetch cemp into pempno,pjob;
    exit when cemp%notfound;

    --判断职位
    if pjob = 'PRESIDENT' then update emp set sal=sal+10000 where empno=pempno;
      elsif pjob = 'MANAGER' then update emp set sal=sal+5000 where empno=pempno;
      else update emp set sal=sal+4000 where empno=pempno;
    end if;
  end loop;

  close cemp;


  commit;

  dbms_output.put_line('完成');
end;
/

实例二：

--查询某个部门的员工姓名
set serveroutput on
declare
 cursor cemp(dno number) is select ename from emp where deptno=dno;
  pename emp.ename%type;
begin
 open cemp(10);
  loop
    fetch cemp into pename;
    exit when cemp%notfound;

    dbms_output.put_line(pename);

  end loop;
  close cemp;
end;
/

实例三：

--查询并打印100号部门的员工姓名
set serveroutput on

declare
 cursor cemp is select ename from emp where deptno=100;
  pename emp.ename%type;

  --自定义例外
  no_emp_found exception;
begin
 open cemp;

  --取一条记录
  fetch cemp into pename;

  if cemp%notfound then 
    --抛出例外
    raise no_emp_found;
  end if;  

  close cemp;

exception
  when no_emp_found then dbms_output.put_line('没有找到员工');
  when others then dbms_output.put_line('其他例外');
end;
/