Oracle数据库之（五）PL/SQL

五、PL/SQL

5.1 PL/SQL概述

• 什么是PL/SQL？
  PL/SQL是一种过程化语言，通过增加编程语言的特点，实现对SQL的扩展。
• PL/SQL的特点

1. 支持所有SQL的语法；
2. 支持case语句，方便的实现循环；
3. 通过继承，实现子类具有父类的属性和方法；
4. 设置了新的日期类型。

• PL/SQL语法
  PL/SQL程序是按照块结构进行划分的，块是PL/SQL程序的基本单位。

  DECLARE --用于声明变量、游标
  	v_name varchar(30):='jack';--定义用户名
  	v_age:=6;--定义年龄
  BEGIN --表示程序的开始
  	--将用户的姓名和年龄插入到club_user表中
  	insert into club_user values(v_name,v_age);
  Exception
  	--当出现异常是的处理
  	when others then
  		DBMS_OUTPUT.PUT_LINE('插入数据失败！');
  END;--表示程序结束
  

  --declare是声明
  --variable_name是变量名称
  --constant表示是否为常量
  --type是变量的数据类型
  --not null表示是否为空
  --:=value 是变量初始化
  DECLARE variable_name [CONSTANT] type [NOT NULL] [:=value];
  

• 变量命名规则

  1. 变量名首字母必须是英文字母，其后可以是字母、数字或者特殊字符$、#和下划线；
  2. 变量名长不超过30个字符；
  3. 变量名中不能有空格。

• 表达式和运算符

  • 表达式分类
  1. 数值型
  2. 字符型
  3. 日期型
  4. 布尔型
  • 运算符分类
  1. 算数运算符
  2. 关系运算符
  3. 逻辑运算符
  4. 其他运算符

5.2 用PL/SQL实现流程控制

• 条件结构

  • IF-THEN语句

  IF condition THEN
     Statements
  END IF;
  

  • IF-THEN-ELSEIF语句

  IF contion1 THEN
  	Statements1
  ELSEIF contion2 THEN
  	Statements2
  ELSE
  	Statement3
  END IF;
  

  • CASE语句

  CASE variable
  	WHEN value1 THEN statements1;
  	WHEN value2 THEN statements2;
  	...
  	WHEN valuen THEN statementsn;
  	[ELSE ele_statements;]
  END CASE;
  

• 循环结构

  • LOOP循环

  LOOP
  	ststements;
  END LOOP;
  

  • WHILE-LOOP循环（类似于while循环）

  LOOP
  	statements;
  END LOOP;
  

  • FOR-LOOP循环

  --loop_count是指循环变量
  --lower_bound是指循环次数最小值
  --height_bound是指循环次数最大值
  FOR loop_count IN [REVERSE] lower_bound...height_bound LOOP
  	statements;
  END LOOP;
  

5.3 动态SQL

• 动态SQL概述
  编译期间SQL语句是不确定的，并且在运行时允许发生变化。
• 动态SQL的应用场合

1. 要执行一个DDL语句时；
2. 需要增加程序的灵活性；
3. 使用包DBMS_SQL动态执行SQL语句时。

• 动态SQL的执行

  • execute immediate语句

  --dynamic_sql:表示一个SQL语句或者一个PL/SQL语句块的字符串表达式
  EXECUTE IMMEDIATE dynamic_sql;
  --example
  BEGIN
  	v_sql:='create table usersinfo (ids varchar2(20) primary key,username varchar2(20),userpwd varchar2(20))';
     EXECUTE IMMEDIATE v_sql;
   END;
  

  • 绑定变量
  1. 通过占位符绑定参数；
  2. 参数类型可以是集合、对象等；
  3. 不支持PL/SQL定义的类型。

  DECLARE
     v_deptno:=60;
     v_deptname:='行政部';
     v_loc:='洛阳';   
  BEGIN
     v_sql:='insert into dept values(:1,:2,:3)';   
     EXECUTE IMMEDIATE v_sql USING v_deptno,v_deptname,v_loc; 
  END;
  

5.4 用PL/SQL实现对异常的处理

预定义异常	说明
access_into_null	试图给一个没有初始化的对象赋值
case_not_found	在case语句中没有when子句被选择，并且没有else子句
invalid_number	试图将一个非有效的字符和选转换成数字
loggin_denied	使用无效的用户名和口令登录Oracle
no_data_found	查询语句无返回数据，或者引用了一个被删除的元素，或者引用了一个没有被初始化的元素
timeout_on_resource	Oracle在等待资源时发生超时现象

使用raise关键字引发异常，使用exception关键字处理异常。

DECLARE
    temp_ex exception; --预定义异常temp_ex
BEGIN
	...
	RAISE temp_ex; --触发异常
	...  --发生异常时不会执行这里的代码
EXCEPTION
	WHEN temp_ex THEN --控制呗转到异常处理部分，这里的代码被执行
	...
END;

DECLARE
	v_i:=6;
   
   IF v_i=3 THEN
     RAISE  v_excepton;
   END IF;
   IF v_i=5 THEN
     RAISE  v_excepton2;
   END IF; 
      
   Exception
     when v_excepton then 
         dbms_output.put_line('v_i的值不能为3');
     when v_excepton2 then 
         dbms_output.put_line('v_i的值不能为5');
END;

5.5 用PL/SQL实现对游标的控制

• 什么是游标？
  用来处理使用select语句从数据库中检索到的多行记录的工具。
• 游标的分类
• 显式游标：返回多条记录，使用显式游标逐行读取
• 隐式游标：返回一条数据，但是可以是多列数据。
• 游标的使用

  属性名称	说明
  %found	用于检验游标是否成功，通常在fetch语句前使用，当游标按照条件查询出一条记录时，返回true
  %isopen	判断游标是否处于打开状态，试图打开一个已经打开或者已经关闭的游标，将会出现错误
  %notfound	与%found的作用相反，当按照条件无法查询到记录时，返回true
  %rowcount	循环执行游标读取数据时，返回检索出的记录数据的行数

--游标的声明
#cursor 用于声明一个游标
#parameter 可选参数，用于指定参数类型、模式等
#return 可选，指定游标的返回类型
# selectsql 需要处理的select语句，不能含into子句
cursor cursor_name [(parameter[,parameter]......)]
[return return_type] is selectsql

--打开游标
open cursor_name;
--提取游标
#使用fetch语句实现对游标内容的读取
#variable_list 必须与从游标提取的结果集类型相同
fetch cursor_name into variable_list;
--关闭游标
close cusor_name;

读取多行数据时，使用循环结构：

declare 
  --声明游标
  Cursor cur_emp is select sal,sex,ename from emp;
BEGIN
  for c_emp in cur_emp loop    
    dbms_output.put_line(c_emp.sal||'---'||c_emp.sex||'---'||c_emp.ename);
  end loop;
END;

注意：
使用for循环时，自动打开游标，而无需使用open语句；
PL/SQL会自动对变量进行隐式声明；
当循环结束后，游标会自动关闭。