oracle--PLSQL

PLSQL

一、基本概念

最简单的pl/sql程序–Hello World

要在屏幕上输出信息，需要将serveroutput开关打开：
set serveroutput on

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

查找oracle提供的程序包：

在对应数据文档中的index.htm中：
Books-->PL/-->
PL/SQL Packages and Types Reference：帮助文档
PL/SQL User's Guide and Reference:语法

也可以在命令行中直接查看程序包的结构：
desc+程序包的名字：
desc dbms_output

1.什么是PL/SQL

PL/SQL(Procedure Language/SQL)
PL/SQL是oracle对sql语言的过程化扩展
指在sql领命语言中增加了过程处理语句（如分支、循环等），是SQL语言具有过程处理能力。

2.PL/SQL程序结构

declare 
   说明部分（变量说明，光标申明，例外说明）
begin
   语句序列（DML语句）...
exception
   例外处理语句
end;

变量和常量说明：

说明变量:char,varchar2,date,number,boolean,long

说明变量名、数据类型和长度后用分号结束说明语句：
var1 char(15);  
married boolean :=true; 
psal number(7,2);

引用型变量，即my_name的类型与emp表中ename列的类型一样
my_name emp.ename%type;

记录型变量
emp_rec emp%rowtype;

记录变量分量的引用：
emp_rec.ename:='adams';

常量：在中间加上constant:
var1 constant char(15);

例：

引用型变量：
--查询7839的姓名和薪水
set serveroutput on

declare
  --定义变量保存姓名和薪水
  --pename varchar2(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;

记录型变量：
--查询7839的姓名和薪水
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.使用冒号等号:=
2.使用into，into前后变量的顺序要保持一致

if语句：

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

2.if 条件 then 语句序列1;
  else 语句序列2;
  end if;

3.if 条件 then 语句;
  elseif 语句 then 语句;
  else 语句;
  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;

循环语句：

当条件成立，执行循环；不成立，退出循环
while total<=25000
loop
...
total:=total+salary;
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 光标名[(参数名 数据类型[,参数名 数据类型]...)]
  is select 语句;

用户存储一个查询返回的多行数据

cursor c1 is select ename from emp;

打开光标： open c1;(打开光标执行查询)(光标的初始位置直接是指向了第一条记录的)
取一行光标的值：fetch c1 into pename;(取一行到变量中)
关闭光标：close c1;(关闭游标释放资源)
注意：
上面的pename必须与emp表中的ename列类型一致：
定义：pename emp.ename%type;

属性：

1.光标的属性：
  %isopen     %rowcount (影响的行数)
  %found      %notfound (是否有取到记录)

2.默认，一个会话中只能打开300个光标
SQL> show parameter cursor
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- -----------
cursor_sharing                       string                           FORCE
cursor_space_for_time                boolean                          FALSE
open_cursors                         integer                          300
session_cached_cursors               integer                          20

注意：
要管理员身份才能修改： 
SQL> show user
USER is "SCOTT"
SQL> conn sys/password@10.10.10.128/orcl as sysdba
Connected.
SQL> show user
USER is "SYS"
alter system set open_cursors=400;

3.(思考):cursor_sharing 什么作用？---> 性能优化
     三个取值： EXACT(默认), FORCE, SIMILAR    

例：

--查询并打印员工的姓名和薪水
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;

--涨工资，总裁1000 经理800 其他400
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+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;

  --why?  ---> ACID
  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(20);
  loop
    fetch cemp into pename;
    exit when cemp%notfound;

    dbms_output.put_line(pename);

  end loop;
  close cemp;
end;

三、例外

例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。

oracle的异常处理：

系统定义例外：
no_data_found(没有找到数据)
too_many_rows(select...into语句匹配多个行)
zero_divide(被零除)
value_error(算术或转换错误)
timeout_on_resource(在等待资源时发生超时)

用户定义的例外

语法：

系统定义：
declare
    说明部分（变量说明，光标申明，例外说明）
begin
    语句序列（DML语句）...
exception
    例外处理语句
end;

自定义：
declare
my_job char(10);
v_sal emp.sal%type;
no_data exception;
cursor c1 is select
distinct job from emp
order by job;   
begin
open c1;
fetch c1 into v_job;
if c1%notfound then raise no_data;
end if;
...
exception
when no_data then insert into emp 
values('fetch语句没有获得数据或数据已经处理完');
end;

在declare节中定义例外:
out_of exception;
在可行语句中引起例外:
raise out_of;
在exception节处理例外：
when out_of then...

例：

系统例外
--被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;

自定义例外：
--查询50号部门的员工姓名
set serveroutput on

declare
  cursor cemp is select ename from emp where deptno=50;
  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;

  --pmon: process monitor
  close cemp;

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