从零开始学Oracle—PL/SQL(七)

 继续上篇的学习

                                                               PL/SQL简介
   PL/SQL(Procedural Language Sql,过程语言|SQL)是结合了Oracle过程语言和结构化查询语言（SQL)的一种扩展语言.
   PL/SQL块是一段后台服务程序，它负责将数据从数据库中取出来，在PL/SQL块中进行处理，将处理的结果送到数据库.
优点:

  1,支持SQL
  2，支持面向对象编程(OOP)
  3,更好的性能
  4，可移置性
  5,与SQL集成 --支持所有SQL数据类型和Null值 ，另%type  %rowType属性类型更加强了这种集成
  6,安全性

PL/SQL块包括3个部分:
1,声明部分    --变量，游标，自定义异常, 局部子程序
2,可执行部分   --执行命令，可嵌套子块
3,异常处理部分

 1PL/SQL块
 2  declare
 3
 4  begin
 5   --SQL语句
 6   --直接写的SQL语句(DML/TCL)
 7   --间接写execute immediate <DDL/DCL命令字符串> 
 8   --select 语句
 9         <1>必须带有into子句
             select empno into eno from emp
           where empno =7369;
        <2>只能查到一行**********
        <3>字段个数必须和变量的个数一致
  exception  --异常
    when <异常名字> then --特定异常
        <处理语句>
    when others then  --所有异常都可捕获
        <处理语句>   
  end;

语言特征：
<1>,大小写不敏感
<2>,复合符号的含义
   :=   赋值操作符   ||连接操作符  ..范围操作符  **求幂操作符 《，》标签分隔符    --单行注释  /*，*/多行注释
<3>声明变量   --变量名  数据类型 :=值  例： varA  varchar2(10):='声明变量';
<4>声明常量   --变量名 constant 数据类型 := 值  
---------------- 技巧：初始化变量和常量时，保留字default可替换：=赋值操作符--------------
<5>属性类型
   %type   --引用某个变量或数据库列的数据类型来声明变量
   %rowType  --提供表示表中一行的记录类型
<6>控制结构
   a,条件控制  if  then  或     if  then  else   或    if  then  elsif   或     case
   b,循环控制 loop
                  while              exit或exit when退出
                  for
   c,顺序控制  Goto             Null语句
 语法示例：

  1--PL/SQL 简单语法
  2
  3Begin
  4  null;  
  5End;
  6/
  7
  8Declare
  9  varA number(4);
 10Begin
 11  varA := 1234;
 12  DBMS_output.put_line(varA);
 13End;
 14/
 15
 16Declare
 17  varA number(4);
 18Begin
 19  varA := 1234;
 20  DBMS_output.put_line(varA);
 21  Declare
 22    varB number(4):=2234;
 23  Begin
 24    DBMS_output.put_line(varA);
 25    DBMS_output.put_line(varB);
 26  End;
 27  DBMS_output.put_line(varB);
 28End;
 29/
 30
 31Declare
 32  varA number(4);
 33  varB number(4):=2234;
 34Begin
 35  varA := 1234;
 36  DBMS_output.put_line(varA||'  '||varB);
 37End;
 38/
 39Begin块 注意事项：
 40-- 数据定义语言不能直接执行
 41Begin
 42  drop table depta;
 43End;
 44/
 45
 46-- select不能直接执行
 47select  --必须使用select into 形式，只能且必须返回一行
 48Declare 
 49  varE empa%rowType;  --与表的一行类型一样
 50  vno empa.empno%type;  -- 与表的某列类型一样
 51Begin
 52  vno := 7902;
 53  select * into varE from empa where empno = vno;
 54  DBMS_output.put_line( varE.ename||'  '||varE.job);
 55End;
 56/
 57
 58inset  --可以直接执行
 59Declare
 60  vno empa.empno%type;
 61  vna empa.ename%type;
 62  vjob empa.job%type;
 63Begin
 64  vno := 1236;
 65  vna := 'Wing';
 66  vjob := 'MANAGER';
 67  insert into empa (empno,ename,job) values(vno,vna,vjob);
 68End;
 69/
 70
 71delete --可以直接执行
 72Declare
 73  vno empa.empno%type;
 74Begin
 75  vno := 5555;
 76  delete from empa where empno = vno;
 77End;
 78/
 79
 80
 81update --可以直接执行
 82Declare
 83  vno empa.empno%type;
 84  rsal empa.sal%type;
 85Begin
 86  vno := 1236;
 87  rsal := 100;
 88  update empa set sal = nvl(sal,0) + rsal where empno = vno;
 89End;
 90/
 91
 92 --事物控制语言可以直接执行
 93Declare 
 94  vno empa.empno%type;
 95  rsal empa.sal%type;
 96Begin
 97  vno := 1236;
 98  rsal := 100;
 99  update empa set sal = nvl(sal,0) + rsal where empno = vno;
  commit;
End;
/

 -- 数据安全语言(DCL)不能直接执行
Begin
  grant connect to scott;
End;
/

  例子：

题：编写程序 向DEPT表中插入一条记录，
    从键盘输入数据，如果
    数据类型输入错误要有提示
    无法插入记录 也要有提示
    只能输入正数,如果有负数提示 
   declare
    n number;
    no dept.deptno%type;
    nm dept.dname%type;
    lc dept.loc%type;
    exp exception;   --用户定义的变量
    exp1 exception;   --用户定义的变量
    num number:=0;   --计数器
     pragma exception_init(exp,-1); --预定义语句
    --捆绑Oracle内置异常(-1错误和异常变量关联),-1 主键冲突
    pragma exception_init(exp1,-1476);除零异常
    e1 exception; --用户定义的变量
   begin
    --输入值
      no := '&编号';
     num := num + 1;
     if no < 0 then
        raise e1;    --自定义异常的引发
      end if;

     nm := '&名称';
     num := num +1;

     lc := '&地址';  
     num := num +1;

     n := 10 /0;     

     insert into dept values (no,nm,lc);
     num := num +1;
     commit;

   exception
     --自定义异常
     when e1 then
        dbms_output.put_line('编号不能为负数'); 
     --数据类型不对
     when value_error then 
        if num =0 then  
         dbms_output.put_line('编号数据类型不对'); 
        elsif num = 1 then
         dbms_output.put_line('名称数据类型不对'); 
        elsif num =2 then
         dbms_output.put_line('地址数据类型不对'); 
        end if;
        rollback;
     --主键冲突
     when exp then
         --sqlcode全局变量 异常错误号
         --sqlerrm全局变量 异常的文字信息  
         --dbms_output.put_line('异常的编号:'||sqlcode);
         --dbms_output.put_line('异常的内容:'||sqlerrm);
         --dbms_output.put_line('编号已存在') ;
         rollback;
     --非预定义异常(关联错误号) 
     when exp1 then
        dbms_output.put_line('0做了除数') ; 
         raise_application_error(-20001,'0做了除数'); 
         --引起一个自定义的错误
           --预先保留-20001 到 -29999编号
         rollback; 
     --其他的异常 
     when others then
         dbms_output.put_line('异常的编号:'||sqlcode);
         dbms_output.put_line('异常的内容:'||sqlerrm);
        -- dbms_output.put_line('出现错误');
         rollback;
   end;