Oracle数据库--实用操作(3) PL/SQL

                                Oracle----PL/SQL                        

PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言

PL/SQL 是Oracle特有的，是对 SQL 的扩展。不同数据库厂商都有类似的“方言”，提供的新特性。

支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构

可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑

与Oracle 服务器和Oracle 工具紧密集成，具备可移植性、灵活性和安全性

PL/SQL 的优点

支持 SQL，在 PL/SQL 中可以使用：

–  数据操纵命令

–  事务控制命令

–  游标控制

–  SQL 函数和SQL 运算符

支持面向对象编程 (OOP)

可移植性，可运行在任何操作系统和平台上的Oralce数据库

更佳的性能，PL/SQL 经过编译执行

 

PL/SQL体系结构

PL/SQL[代码]块是构成 PL/SQL 程序的基本单元

将逻辑上相关的声明和语句组合在一起

PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分

 

PL/SQL中的复合符号

:=        赋值操作符

||         连接操作符

--        单行注释

/**/      多行注释

<<>>  标签分隔符

..         范围操作符

**        求幂操作符

变量和常量

PL/SQL 块中可以使用变量和常量

1.  在声明部分声明，使用前必须先声明

2.  声明时必须指定数据类型，每行声明一个标识符

3.  在可执行部分的SQL 语句和过程语句中使用

声明变量和常量的语法：

identifier [CONSTANT] datatype[NOT NULL]  

                   [:= |DEFAULT expr];

给变量赋值有两种方法：

1使用赋值语句:=

2使用 SELECTINTO 语句

DECLARE

  icode VARCHAR2(6);

  p_catg VARCHAR2(20);

  p_rate NUMBER;

  c_rate CONSTANT NUMBER :=0.10;

BEGIN

  ...

  icode := 'i205';

  SELECT p_category, itemrate* c_rate

  INTO  p_catg, p_rate

  FROM itemfile WHERE itemcode= icode;

  ...

END;

DECLARE vv number:=100;   --匿名代码块，不会保存

Begin

  SELECT SAL into vv  from emp where ename='KING';                 --查出KING的工资，并赋值给vv变量

 

  DBMS_OUTPUT.PUT_LINE('SAL'||vv);   --控制台输出

end;

数据类型

数字数据类型

字符数据类型

字符数据类型包括：

1.  CHAR

2.  VARCHAR2

3.  LONG

4.  RAW

5.  LONG RAW

日期时间类型

1.       存储日期和时间数据

2.       常用的两种日期时间类型

①  DATE

②  TIMESTAMP

布尔数据类型

1.       此类别只有一种类型，即BOOLEAN类型

2.       用于存储逻辑值(TRUE、FALSE和NULL)

3.       不能向数据库中插入BOOLEAN数据

4.       不能将列值保存到BOOLEAN变量中

5.       只能对BOOLEAN变量执行逻辑操作

LOB  数据类型

用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。

LOB 数据类型可存储最大 4GB的数据。

LOB 类型包括：

6.       BLOB   将大型二进制对象存储在数据库中

7.       CLOB   将大型字符数据存储在数据库中

8.       NCLOB   存储大型UNICODE字符数据

9.       BFILE     将大型二进制对象存储在操作系统文件中

属性类型

用于引用数据库列的数据类型，以及表示表中一行的记录类型

属性类型有两种：

1.       %TYPE -  引用变量和数据库列的数据类型

2.       %ROWTYPE -  提供表示表中一行的记录类型

使用属性类型的优点：

1.       不需要知道被引用的表列的具体类型

2.       如果被引用对象的数据类型发生改变，PL/SQL 变量的数据类型也随之改变

逻辑比较

逻辑比较用于比较变量和常量的值，这些表达式称为布尔表达式

布尔表达式由关系运算符与变量或常量组成

控制结构

PL/SQL 支持的流程控制结构：

•  条件控制

–  IF 语句

–  CASE 语句

•  循环控制

–  LOOP 循环

–  WHILE 循环

–  FOR 循环

•  顺序控制

–  GOTO 语句

–  NULL 语句

•  循环控制用于重复执行一系列语句

•  循环控制语句包括：

–  LOOP、EXIT 和EXIT WHEN

•  循环控制的三种类型：

–  LOOP   -   无条件循环

–  WHILE  -  根据条件循环

–  FOR  -  循环固定的次数

      loop...exit...when...endloop循环控制

采用loop...exit...when...end loop循环控制的语法结构如下所示：

loop

         循环体；

         exitwhen循环条件;

end loop

      while...loop...end loop循环控制

这种循环控制的语法如下：

while 条件

loop

         执行语句段;

end loop;

例：

Declare counter number:=0;

Begin

loop     --[无条件]循环

exit when counter>10;

DBMS_OUTPUT.PUT_LINE('lplpl'||counter);

counter:=counter+1;

 

end loop;

end;

……………………………………………………

Declare counter number:=0;

Begin

 while counter<9 loop    --先判断，再执行循环

 DBMS_OUTPUT.PUT_LINE('lplpl'||counter);

counter:=counter+1;

  endloop;

end;

 

…………………………………………………………..

Declare i number:=0;

Begin

  forx in 1..9 loop  --for循环：x在1-9则循环

   DBMS_OUTPUT.PUT_LINE('lplpl'||x);

  

  endloop;

 

end;

CASE 语句用于根据单个变量或表达式与多个值进行比较

执行 CASE 语句前，先计算选择器的值

BEGIN

   CASE ‘&grade’

     WHEN ’A’ THEN DBMS_OUTPUT.PUT_LINE(’优异’);

     WHEN ’B’ THEN DBMS_OUTPUT.PUT_LINE (优秀’);

     WHEN ’C’ THEN DBMS_OUTPUT.PUT_LINE (’良好’);

     WHEN ’D’ THEN DBMS_OUTPUT.PUT_LINE (’一般’);

     WHEN ’F’ THEN DBMS_OUTPUT.PUT_LINE (’较差’);

     ELSE DBMS_OUTPUT.PUT_LINE (’没有此成绩’);

   END CASE;

END;

错误处理

•  在运行程序时出现的错误叫做异常

•  发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分

•  异常有两种类型：

–      预定义异常 -  当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发

–      用户定义异常  -  用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发

处理预定义异常

access_into_null 在未初化对象时出现

case_not_found 在CASE语句中的选项与用户输入的数据不匹配时出现

collection_is_null 在给尚未初始化的表或数组赋值时出现

cursor_already_open 在用户试图打开已经打开的游标时出现

dup_val_on_index 在用户试图将重复的值存在使用唯一索引的数据库列中时出现

invalid_cursor 在执行非法游标运算（如打开一个尚未打开的游标）时出现

invalid_number 在将字符串转换为数字时出现

login_denied 在输入的用户名或密码无效时出现

no_data_found 在表中不存在的请求的行时出现，此外，当程序引用已经删除的元素时

storage_error 在内存损坏或PL/SQL耗尽内存时出现

too_many_rows 在执行SELECTINTO语句后返回多行时出现

value_error 在产生大小限制错误时出现

zero_divide 以零作除数时出现

Others针对所有异常

 

处理用户定义异常

DECLARE

 invalidCATEGORY EXCEPTION;

 category VARCHAR2(10);

BEGIN

 category := '&Category';

  IFcategory NOT IN ('附件','顶盖','备件') THEN

   RAISE invalidCATEGORY;

 ELSE

   DBMS_OUTPUT.PUT_LINE('您输入的类别是'|| category);

  ENDIF;

EXCEPTION

 WHEN invalidCATEGORY THEN

    DBMS_OUTPUT.PUT_LINE('无法识别该类别');

END;

 

DECLARE

 rate itemfile.itemrate%TYPE;

 rate_exception EXCEPTION;

BEGIN

 SELECT NVL(itemrate,0) INTO rate FROM itemfile

 WHERE  itemcode = 'i207';

  IFrate = 0 THEN

   RAISE rate_exception;

 ELSE

   DBMS_OUTPUT.PUT_LINE('项费率为：' || rate);

  ENDIF;

EXCEPTION

 WHEN rate_exception THEN

   RAISE_APPLICATION_ERROR(-20001, '未指定项费率');

END;

 

总结：

      PL/SQL 是一种可移植的高性能事务处理语言

      PL/SQL 引擎驻留在 Oracle 服务器中

      PL/SQL 块由声明部分、可执行部分和异常处理部分组成

      PL/SQL 数据类型包括标量数据类型、LOB数据类型和属性类型

      控制结构包括条件控制、循环控制和顺序控制

      PL/SQL 支持动态 SQL

      运行时出现的错误叫做异常

      异常可以分为预定义异常和用户定义的异常