Oracle中的PL/SQL介绍

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

PL/SQL程序结构

declare
      说明部分    （变量说明，光标申明，例外说明 〕
begin
      语句序列   （DML语句〕… 
exception
      例外处理语句   
End;
/

PL/SQL 的hello word

set serveroutput on

declare
   --变量的说明
begin
   --程序
   dbms_output.put_line('Hello World');
end;
/

PL/SQL 中的变量和常量
说明变量 （char, varchar2, date, number, boolean, long)
varl vhar(12) 说明变量名、数据类型和长度后用分号结束说明语句。
psal emp.sal%type; 引用型变量，即my_name的类型与emp表中ename列的类型一样
emp_rec emp%rowtype; 记录型变量

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

declare
  --定义引用型变量
  pename emp.ename%type;
  psal   emp.sal%type;
begin

  --得到7839的姓名和薪水
  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;
/

PL/SQL中的IF语句

IF   条件  THEN 语句1;
语句2;
END IF;

IF  条件  THEN  语句序列1；   
ESLE   语句序列 2；
END   IF；

 IF   条件  THEN 语句;
 ELSIF  语句  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;
/

PL/SQL中的循环语句

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
说明光标语法：

CURSOR  光标名  [ (参数名  数据类型[,参数名 数据类型]...)]
      IS  SELECT   语句;

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

打开光标： open c1; (打开光标执行查询)
取一行光标的值：fetch c1 into pjob; (取一行到变量中)
关闭光标： close c1;(关闭游标释放资源)

注意： 上面的pjob必须与emp表中的job列类型一致：

定义：pjob emp.empjob%type;

--使用游标查询员工姓名和工资，并打印

set serveroutput on

/*
1. 光标的属性
%isopen 是否打开
%rowcount 记录数
%notfound 没有记录

2. 
SQL> show parameters cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     20
修改300
alter system set open_cursors=400 scope=both;

*/
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 cemp%notfound;

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

  end loop;
  close cemp;
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的异常处理
1.系统定义例外
No_data_found (没有找到数据)
Too_many_rows (select …into语句匹配多个行)
Zero_Divide ( 被零除)
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;
/

2.用户定义的例外

--查询50号部门的员工姓名
set serveroutput on

declare
  --定义光标代表50号部门的员工
  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;

  --如果产生例外，自动关闭
  close cemp;

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

demo

--给员工涨工资  总裁1000 经理800 其他400

set serveroutput on

declare
  --定义光标
  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;
  
  --提交
  commit;
  
  dbms_output.put_line('完成');
end;
/