pl/sql学习

<一> PL/SQL 简介

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

PL/SQL 是对 SQL 的扩展。
支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构。
可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑

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

<二>PL/SQL优点

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

1）数据库操纵命令  2）事务控制命令3）游标控制4）SQL运算和SQL运算符

2.支持面向对象编程（oop）

3.可移植性，可运行在任何操纵系统和平台上的oracle数据库

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

5.与 SQL 紧密集成，简化数据处理。

1）支持所有 SQL 数据类型
2）支持 NULL 值
3）支持 %TYPE 和 %ROWTYPE 属性类型

6.安全性，可以通过存储过程限制用户访问

<三>PL/SQL 的体系结构

1.PL/SQL引擎驻留在oracle服务器中

2.该引擎接受PL/SQL块并对其进行编译执行

pl/sql执行过程流程图如如下：

  1）

2)

3）

4)

5）

<四>PL/SQL 块简介

PL/SQL 块是构成 PL/SQL 程序的基本单元，将逻辑上相关的声明和语句组合在一起。PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分
   [DECLARE 
    declarations]
    BEGIN
    executable statements
    [EXCEPTION 
    handlers]
    END;

例如如下pl/sql块：

----------------------------------------------------------------------------------------------

DECLARE
  qty_on_hand NUMBER(5);     // 声明部分定义变量、游标和自定义异常
BEGIN
  SELECT quantity INTO qty_on_hand
  FROM Products
  WHERE product = '芭比娃娃'
  FOR UPDATE OF quantity;
  IF qty_on_hand > 0 THEN
    UPDATE Products SET quantity = quantity + 1
    WHERE product = '芭比娃娃';
    INSERT INTO purchase_record
    VALUES ('已购买芭比娃娃', SYSDATE);
  END IF;
  COMMIT;
EXCEPTION  /* 异常处理语句 */
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('出错:'|| SQLERRM);  
END;

----------------------------------------------------------------------------------------------

1.PL/SQL块常用命令

1)set serveroutput on[off]
打开(或关闭)一个缓冲区存储PL/SQL块的输出
2)DBMS_OUTPUT.PUT_LINE('string')
用来显示PL/SQL缓冲区的内容
3)PL/SQL接收一个用户输入
&请输入一个数字
请注意：PL/SQL的声明部分和可执行部分都是以分号结尾(如：END;)。那么Oracle如何知道PL/SQL块已经结束了呢？
我们在空行上输入”/”就会执行这个块了

2. PL/SQL 变量和常量

1>PL/SQL 块中可以使用变量和常量
1)在声明部分声明，使用前必须先声明
2)声明时必须指定数据类型，每行声明一个标识符
3)在可执行部分的 SQL 语句和过程语句中使用
2>声明变量和常量的语法：

identifier [CONSTANT] datatype [NOT NULL]   
[:= | DEFAULT expr];

3>给变量赋值有两种方法：
1)使用赋值语句 :=
2)使用 SELECT INTO 语句

eg:

------------------------------------------------------------

declare
  s varchar2(20);  --变量定义
  n constant number(3,2) := 3.14;--常量
  r number(5,2) not null default 0; --半径 默认值
begin
  s := '&请输入圆的半径';　--接收收用户的输入
  r := to_number(s);  --使用  ' := '给变量赋值
  --select 1 into r from dual; --使用select方式赋值
  dbms_output.put_line('圆的面积等于：' || n*r*r);
end;
/

------------------------------------------------------------------

3. PL/SQL 数据类型

1> PL/SQL 支持的内置数据类型

1)标量类型： 数字、字符、布尔型、日期时间

2）LOB类型 (存储非结构化数据块) ：BFILE、BLOB、CLOB、NCLOB

3）属性类型：%TYPE （提供某个变量或数据库表列的数据类型）、%ROWTYPE （提供表示表中一行的记录类型 ）

       1.1）数字数据类型—> 指定数值的存储格式

BINARY_INTEGER:   NATURAL、NATURALLN、POSITIVE、POSITIVEN、SIGNTYPE（存储有符号整数，所需存储空间少于NUMBER类型值）

NUMBER:  DECIMAL、FLOAT、INTEGER、REAL（存储整数、实数和浮点数）

PLS_INTEGER:  PLS_INTEGER（存储有符号整数，可使算术计算快速而有效）

1.2）字符数据类型

CHAR、VARCHAR2、LONG、RAW、LONG RAW

PL/SQL 的数据类型与 SQL数据类型的比较

数据类型SQL类型PL/SQL类型CHAR1..20001..32767LONG1..2GB1..32760LONG RAW1..2GB1..32760RAW1..20001..32767VARCHAR21..40001..32767

1.3）日期时间类型：存储日期和时间数据，常用的两种日期时间类型，DATE和TIMESTAMP

1.4）布尔数据类型：此类别只有一种类型，即BOOLEAN类型；用于存储逻辑值(TRUE、FALSE和NULL)；不能向数据库中插入BOOLEAN数据；不能将列值保存到BOOLEAN变量中；只能对BOOLEAN变量执行逻辑操作

2.1）LOB  数据类型：用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。LOB 数据类型可存储最大 4GB的数据。

LOB 类型包括：

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

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

NCLOB   存储大型UNICODE字符数据；

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

LOB 类型的数据库列仅存储定位符，该定位符指向大型对象的存储位置DBMS_LOB程序包用于操纵 LOB 数据，

eg：CLOB数据的读取

---------------------------------------------------------------------------------------------------------------------------------------------------------

SET SERVEROUTPUT ON
DECLARE
  clob_var   CLOB;
  amount     INTEGER;
  offset     INTEGER;
  output_var VARCHAR2(100);
BEGIN
  SELECT chapter_text INTO clob_var   --从表中选择 CLOB 定位符到 clob_var变量中
  FROM my_book_text
  WHERE chapter_id=5;
  amount := 24;  -- 要读取的字符数
  offset := 1;   -- 起始位置
  DBMS_LOB.READ(clob_var,amount,offset,output_var);   --从CLOB数据中读取24个字符存储到 output_var 变量中
  DBMS_OUTPUT.PUT_LINE(output_var);  //显示读到的信息
END;
/

---------------------------------------------------------------------------------------------------------------------------------------------------------

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

属性类型有两种:

%TYPE ：引用变量或数据库列的类型

%ROWTYPE：提供表示数据库一行的数据类型

使用属性类型的优点：

不需要知道被引用的表列的具体类型；如果被引用对象的数据类型发生改变，pl/sql变量的数据类型也随之改变

eg：属性类型的使用

-------------------------------------------------------------------------------------------------------------------

declare
  v_userName Users.Username%type;
  v_row Users%rowtype;
begin
  select userName into v_userName from Users where userId=1;
  dbms_output.put_line(v_userName);
  
  select * into v_row from Users where userId=1;
  dbms_output.put_line(v_row.userName);
end;
/

-------------------------------------------------------------------------------------------------------------------

<5>逻辑比较

逻辑比较用于比较变量和常量的值，这些表达式称为布尔表达式。布尔表达式由关系运算符与变量或常量组成

关系运算符：=（比较两个变量是否相等，如果值相当，则返回 True）、<>, != （比较两个变量，如果不相等，则返回 True）、<、<=、>、>=

<6>PL/SQL 支持的流程控制结构

6.1>条件控制: IF 语句 、CASE 语句

6.2>循环控制：LOOP 循环、WHILE 循环、FOR 循环

6.3>顺序控制： GOTO 语句、NULL 语句

6.1.1>IF 语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIF

eg:

--------------------------------------------------------------------------

declare
  num int;
begin
  num := &请输入一个数字;
  if(num > 0) then
    dbms_output.put_line('正数');
  end if;
  
  if(num > 0) then
    dbms_output.put_line('正数');
  else
    dbms_output.put_line('0或负数');
  end if;
  
  if(num > 0 ) then
    dbms_output.put_line('正数');
  elsif (num < 0) then
    dbms_output.put_line('负数');
  else
    dbms_output.put_line('0');
  end if;
end;
/

----------------------------------------------------------------------------

6.1.2>CASE 语句用于根据单个变量或表达式与多个值进行比较。执行 CASE 语句前，先计算选择器的值。

eg：

-------------------------------------------------------------------------------

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;

-------------------------------------------------------------------------------

6.2.1>循环控制：循环控制用于重复执行一系列语句

循环控制语句包括：LOOP、EXIT 和 EXIT WHEN。

循环控制的三种类型：LOOP   -   无条件循环、WHILE  -  根据条件循环、FOR  -  循环固定的次数

eg：循环语句

-------------------------------------------------------------------------------

declare
  i int := 10;
  j int;
  k int;
begin
  loop
    exit when i < 0;
    dbms_output.put_line(i);
    i := i - 1;
  end loop;
  
  j:= &请输入while循环的次数;
  while(j < 100) loop
    dbms_output.put_line(j);
    j := j + 1;
  end loop;
  
  k:= &请输入for循环的次数;
  for a in 0.. k loop
    dbms_output.put_line(a);
  end loop;
end;
/

-------------------------------------------------------------------------------

6.3.1>顺序控制：顺序控制用于按顺序执行语句。顺序控制语句包括：GOTO 语句 -  无条件地转到标签指定的语句和NULL 语句 -  什么也不做的空语句。

eg：--------------------------------------------------------------------------------------------

declare
  v_row int;
  v_col int;
begin
  dbms_output.put_line('请输入您的座位号');
  v_row := &您坐在第几排;
  v_col := &您坐在第几列;
  
  if(v_row > 10 or v_row < 0) then
    goto lable2;  
  else
    if(v_col < 0 or v_col > 30) then
      goto lable2;
    else 
      goto lable1;
    end if;
  end if;
  
  <<lable1>>
    dbms_output.put_line('欢迎您，您的座位号是：' || v_row || '-' || v_col );
    goto lable3;
  <<lable2>>
    dbms_output.put_line('没有这个座位：' || v_row || '-' || v_col || '不存在！');
  <<lable3>>
    null;
end;
/

---------------------------------------------------------------------------------------------------------

<7>pl/sql错误处理 

在运行程序时出现的错误叫做异常。发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分。

异常有两种类型：

预定义异常 -  当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发。
用户定义异常  -  用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发。

7.1>处理预定义异常、处理用户定义异常

eg：

------------------------------------------------------------------------------

DECLARE 
  invalidCATEGORY EXCEPTION;
  category VARCHAR2(10);
BEGIN
  category := '&Category';
  IF category NOT IN ('附件','顶盖','备件') THEN
    RAISE invalidCATEGORY;
  ELSE
    DBMS_OUTPUT.PUT_LINE('您输入的类别是'|| category);
  END IF;
EXCEPTION
  WHEN invalidCATEGORY THEN
    DBMS_OUTPUT.PUT_LINE('无法识别该类别');
END;

------------------------------------------------------------------------------

7.2>引发应用程序错误

7.2.1>RAISE_APPLICATION_ERROR 过程
-用于创建用户定义的错误信息
-可以在可执行部分和异常处理部分使用
-错误编号必须介于 –20000 和 –20999 之间
-错误消息的长度可长达 2048 个字节

7.2.2>引发应用程序错误的语法：
RAISE_APPLICATION_ERROR(error_number, error_message);
eg:

----------------------------------------------------------------------------------------------

declare
 v_exception exception;
 v_sex char(2);
begin
 v_sex := '&请输入性别';
 if(v_sex = '男') then
   dbms_output.put_line('男性');
 elsif(v_sex = '女') then
   dbms_output.put_line('女性');
 else
   raise v_exception;
 end if;
exception
  when v_exception then
    dbms_output.put_line('产生了一个自定义异常');
    raise_application_error(-20001,'使用raise_application_error引发一个应用程序异常');
end;
/

----------------------------------------------------------------------------------------------