你的位置:首页 > 数据库

[数据库]Oracle学习笔记十 使用PL/SQL


PL/SQL 简介

PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言,是对 SQL 的扩展,它支持多种数据类型,如大对象和集合类型,可使用条件和循环等控制结构,可用于创建存储过程、触发器和程序包,给SQL语句的执行添加程序逻辑,与 Oracle 服务器和 Oracle 工具紧密集成,具备可移植性、灵活性和安全性。
 

PL/SQL 的优点

  • 支持 SQL,在 PL/SQL 中可以使用:

  1.数据操纵命令
  2.事务控制命令
  3.游标控制
  4.SQL 函数和 SQL 运算符
  • 支持面向对象编程 (OOP)

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

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

 
  
  • 与 SQL 紧密集成,简化数据处理。

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

  
 
  • 安全性,可以通过存储过程限制用户对数据的访问


 

PL/SQL 的体系结构

PL/SQL 引擎驻留在 Oracle 服务器中,该引擎接受 PL/SQL 块并对其进行编译执行。

 指定数值的存储格式

数据类型

SQL类型

PL/SQL类型

CHAR

1~2000

1~32767

LONG

1~2GB

1~32760

LONG RAW

1~2GB

1~32760

RAW

1~2000

1~32767

VARCHAR2

1~4000

1~32767

 

日期时间和布尔数据类型

日期时间类型
1.存储日期和时间数据
2.常用的两种日期时间类型
①DATE
②TIMESTAMP
 
布尔数据类型
1.此类别只有一种类型,即BOOLEAN类型
2.用于存储逻辑值(TRUE、FALSE和NULL)
3.不能向数据库中插入BOOLEAN数据
4.不能将列值保存到BOOLEAN变量中
5.只能对BOOLEAN变量执行逻辑操作

LOB  数据类型

用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。
LOB 数据类型可存储最大 4GB的数据。
LOB 类型包括:
  1.BLOB   将大型二进制对象存储在数据库中
  2.CLOB   将大型字符数据存储在数据库中
  3.NCLOB   存储大型UNICODE字符数据
  4.BFILE     将大型二进制对象存储在操作系统文件中
LOB 类型的数据库列仅存储定位符,该定位符指向大型对象的存储位置
DBMS_LOB程序包用于操纵 LOB 数据
SET SERVEROUTPUT ONDECLARE 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;

 

  属性类型

用于引用数据库列的数据类型,以及表示表中一行的记录类型。
 
属性类型有两种:
1.%TYPE  -  引用变量和数据库列的数据类型
2.%ROWTYPE  -  提供表示表中一行的记录类型
 
使用属性类型的优点:
1.不需要知道被引用的表列的具体类型
2.如果被引用对象的数据类型发生改变,PL/SQL 变量的数据类型也随之改变
icode itemfile.itemcode%TYPE;emp_rec scott.emp%ROWTYPE;

 

 逻辑比较

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

 

关系运算符

说明

=

比较两个变量是否相等,如果值相当,则返回 True

<>, !=

比较两个变量,如果不相等,则返回 True

<

比较两个变量,检查值 1 是否小于值 2

>

比较两个变量,检查值 1 是否大于 值 2

<=

比较两个变量,检查变量 1 是否小于等于变量 2

>=

比较两个变量,检查变量 1 是否大于等于变量 2


 
布尔表达式的结果为TRUE、FALSE或NULL,通常由逻辑运算符AND、OR和NOT连接。
布尔表达式有三种类型:
  1.数字布尔型
  2.字符布尔型
  3.日期布尔型
 

控制结构

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

  条件控制
    1.IF 语句
    2.CASE 语句
  循环控制
    1.LOOP 循环
    2.WHILE 循环
    3.FOR 循环
  顺序控制
    1.GOTO 语句
    2.NULL 语句

 

条件控制

DECLARE  icode VARCHAR2(4);  irate NUMBER;BEGIN  icode := 'i203'; SELECT itemrate INTO irate FROM itemfile  WHERE itemcode = icode; IF irate > 200 THEN   --条件判断  UPDATE itemfile SET itemrate = itemrate - 200  WHERE itemcode = icode; ELSE  UPDATE itemfile SET itemrate = itemrate - 50  WHERE itemcode = icode; END IF;  --结束 DBMS_OUTPUT.PUT_LINE('itemrate='|| irate);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;

 循环控制


循环控制用于重复执行一系列语句。
循环控制语句包括:
  1.LOOP、EXIT 和 EXIT WHEN
 
循环控制的三种类型:
  1.LOOP   -   无条件循环
LOOP  sequence_of_statementsEND LOOP;


  2.WHILE  -  根据条件循环
WHILE condition LOOP  sequence_of_statementsEND LOOP;


  3.FOR  -  循环固定的次数

FOR counter IN [REVERSE] value1..value2LOOP  sequence_of_statementsEND LOOP;

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

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

loop 循环体; exit when循环条件;end loop

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

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

while 条件loop 执行语句段;end loop;

 

循环例子:

--while循环--循环输出Hello worlddeclare  var_i int := 10;begin  loop    dbms_output.put_line('Hello world');    if var_i = 0 then      exit;    end if;    var_i := var_i - 1;  end loop;end;--loop输出1-10declare  var_i int := 1;begin  loop    dbms_output.put_line('i = ' || var_i);    exit when var_i = 10;    var_i := var_i + 1;  end loop;End--while循环1-5Declare  var_i int:=1;Begin  while var_i<=10 loop    Dbms_output.put_line('I='||var_i);    Var_i:=var_i+1;  End loop;End;--for 循环 : for 变量 in 范围 loop--1-10输出begin  for i in 1..10 loop    dbms_output.put_line('i = ' || i);  end loop;end--对员工的工资判断,如果少于2000则加50,直到工资满足为止!declare  var_sal int;begin  loop    select sal into var_sal from emp where empno = 7499;    exit when var_sal >= 2000;    update emp set sal = sal + 50 where empno = 7499;    dbms_output.put_line('加50');  end loop;end;

 

 

顺序控制

顺序控制用于按顺序执行语句。
顺序控制语句包括:
  1.GOTO 语句 -  无条件地转到标签指定的语句
  2.NULL 语句 -  什么也不做的空语句
DECLARE qtyhand itemfile.qty_hand%type; relevel itemfile.re_level%type;BEGIN SELECT qty_hand,re_level INTO qtyhand,relevel FROM itemfile WHERE itemcode = 'i201'; IF qtyhand < relevel THEN  GOTO updation; --跳转到<<updation>>语句块 ELSE  GOTO quit; --跳转到<<quit>> END IF; <<updation>> UPDATE itemfile SET qty_hand = qty_hand + re_level WHERE itemcode = 'i201'; <<quit>> NULL;END;

 动态 SQL


动态 SQL 是指要执行的SQL语句需要在执行的时候才能确定的SQL语句,是在PL/SQL程序执行时生成的 SQL 语句,编译程序对动态 SQL 不做处理,而是在程序运行时动态构造语句、对语句进行语法分析并执行。DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用,但是可以通过动态 SQL 来执行。
 
执行动态 SQL 的语法:
  EXECUTE IMMEDIATE dynamic_sql_string   [INTO define_variable_list]   [USING bind_argument_list];

 例:

DECLARE sql_stmt VARCHAR2(200); emp_id NUMBER(4) := 7566; emp_rec emp%ROWTYPE;BEGIN  EXECUTE IMMEDIATE   'CREATE TABLE bonus1 (id NUMBER, amt NUMBER)';  sql_stmt := 'SELECT * FROM emp WHERE empno = :id';  EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;END;

 


--显示员工编号为7499的所有信息declare  Var_row emp%rowtype;begin  select * into var_row from emp where empno = 7499;  dbms_output.put_line('编号=' || var_row.empno || '姓名=' || var_row.ename);end;

 

--动态执行declare  var_row emp%rowtype;  var_sql varchar(100);  var_empno int;begin  var_sql := 'select * from emp where empno = :num' ;  execute immediate var_sql into var_row using var_empno;  dbms_output.put_line('编号=' || var_row.empno || '姓名=' || var_row.ename);end;

--用动态SQL来实现分页begin  execute immediate 'select * from (select rownum rn, d.* from (select * from :tableName order by sal desc) d) where rn > :min and rn <= :max' using 'emp' , 1, 5;End;

 


 错误处理

 在运行程序时出现的错误叫做异常,发生异常后,语句将停止执行,控制权转移到 PL/SQL 块的异常处理部分。
 
异常有两种类型:
1.预定义异常 -  当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
2.用户定义异常  -  用户可以在 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  --在执行SELECT INTO语句后返回多行时出现value_error  --在产生大小限制错误时出现zero_divide  --以零作除数时出现Others     --针对所有异常

 处理用户定义异常


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;

 引发应用程序错误

DECLARE rate itemfile.itemrate%TYPE; rate_exception EXCEPTION; --定义异常BEGIN SELECT NVL(itemrate,0) INTO rate FROM itemfile  WHERE itemcode = 'i207'; IF rate = 0 THEN  RAISE rate_exception; --引发异常 ELSE  DBMS_OUTPUT.PUT_LINE('项费率为:' || rate); END IF;EXCEPTION WHEN rate_exception THEN --扑捉异常  RAISE_APPLICATION_ERROR(-20001, '未指定项费率');END;

引发系统级错误

begin  if 10 > 5 then    raise_application_error(-20001, '这个是自定义错误显示');  end if;end;