Oracle数据库系列(五)、PL/SQL

第五章、PL/SQL(难重点)

学习目标

什么是对象查询？
如何使用Example查询已有实体对象的类似对象？
如何使用Order对Criteria查询结果进行排序？
如何使用Criteria限定查询返回的数据行数？
什么是PL/SQL？
PL/SQL语法结构？
PL/SQL如何实现异常处理？
游标的使用分为哪几个步骤？
编写PL/SQL实现流程控制？
使用PL/SQL编写动态SQL？
使用PL/SQL实现异常处理？
使用PL/SQL实现对游标的控制？
了解PL/SQL的特点？
掌握Pl/SQL的语法？
会使用PL/SQL实现流程控制？
会使用PL/SQL实现对异常的处理？
会使用PL/SQL实现对游标的控制？

5.1、PL/SQL

PL/SQL（Procedural Language/SQL）
一种过程化语言，通过增加编程语言的特点，实现对SQL的扩展。

5.2、PL/SQL的特点

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

5.3、PL/SQL的开发及工作环境

PL/SQL的开发环境
- Oracle数据库服务器
- Oracle开发工具
PL/SQL的工作原理
- 由PL/SQL引擎接收指令
- 将指令传递给Oracle数据库服务器执行
工作原理如下图所示：

5.4、PL/SQL语句块

PL/SQL程序是按照快结构进行划分

块是PL/SQL程序的基本单位

--创建club_user表
create table club_user(
 v_name varchar2(30) primary key not null, --姓名
 v_age number not null         --年龄 
)

--PL/SQL语句块结构
Declare
  v_name varchar2(30) := 'jack'; --定义用户名
  v_age number := 6;             --定义用户年龄
Begin
  --将定义的用户姓名和年龄插入到club_user表中
  insert into club_user values(v_name,v_age);
Exception
  When others then
    DBMS_OUTPUT.PUT_LINE('插入数据失败');
END;

5.4.1、Declare声明

使用declare关键字
用于定义变量、常量或者游标
语法如下：

小贴士：
其中[ ]代表可选；
‘:=’ 是代表赋值，变量可以在声明的时候初始化赋值，也可以咋begin……end结构体中赋值。

5.4.2、变量命名规则

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

5.4.3、输出语句

--大小写都可以
dbms_output.put_line();

5.5、表达式和运算符

表达式的分类
- 数值型
- 字符型
- 日期型
- 布尔型
运算符的分类
- 算数运算符
- 关系运算符
- 逻辑运算符
- 其他运算符

5.5.1、IF-THEN语句

declare 
	v_name varchar2(20);
	v_age number(3);
begin
  --v_name := '小方';
  --v_age := 21;
  if v_age>=18 then
	  dbms_output.put_line(v_name||'---这孩子已成年！');
  if v_age<18
	  dbms_output.put_line(v_name||'---这孩子未成年!');
  end if;
end;

5.5.2、IF-THEN-ELSE语句

declare 
	v_name varchar2(20);
	v_age number(3);
begin
   --v_name := '小方';
   --v_age := 21;
   if v_age>=18 then
     dbms_output.put_line(v_name||'---这孩子已成年！');
   else
     dbms_output.put_line(v_name||'---这孩子未成年!');
   end if;
end;

5.5.3、IF-THEN-ELSIF语句

declare
  v_score number;
begin
  v_score := 100;
   if v_score>=90 and v_score<=100 then
     dbms_output.put_line('优秀');
   elsif v_score>=80 and v_score<90 then
     dbms_output.put_line('良好');
   elsif v_score>=70 and v_score<80 then
     dbms_output.put_line('一般');
   elsif v_score>=60 and v_score<70 then
     dbms_output.put_line('及格');
   elsif v_score<60 then
     dbms_output.put_line('差');
   end if;
end;

5.5.4、case语句

declare 
   v_mc varchar2(10);
begin
   v_mc := 12;
   case v_mc
     when 1 then dbms_output.put_line('第一名');
     when 2 then dbms_output.put_line('第二名');
     when 3 then dbms_output.put_line('第三名');
     else
       dbms_output.put_line('其他名次'); 
   end case;
end;

5.6、LOOP循环

5.6.1、实现计数器功能

declare
  v_i number(3) := 1;
begin
   loop
     dbms_output.put_line(v_i);
     v_i := v_i + 1;
     --如果大于等于10，就结束程序
     if v_i>=10 then
       exit;
     end if;
   end loop;
end;

5.6.2、WHILE-LOOP循环

declare
  v_i number(3) := 1; 
begin
  --当条件大于10就结束loop循环
  while v_i <= 10
  loop
    v_i := v_i + 1;
    dbms_output.put_line(v_i);
  end loop;
end;

5.6.3、FOR-LOOP循环

declare
  v_j number;
begin
  --reverse是反转输出
  for v_j in [reverse] 1..11 loop
     dbms_output.put_line(v_j);
  end loop;
end;

5.7、动态SQL

什么是动态SQL？
- 编译期间SQL语句时不确定的，并且在运行时允许发生变化。
动态SQL应用场景？
- 要执行一个DDL语句时
- 需要增加程序的灵活性
- 使用DBMS_SQL动态执行SQL语句时

5.7.1、动态SQL执行

执行建表语句

declare
  v_sql varchar2(200)
begin
  v_sql:='create table userinfo(ids varchar2(20) primary key,username varchar2(20),userpwd varchar2(20))';
  --语法规范
  EXECUTE IMMEDIATE v_sql;
end;

绑定变量

通过占位符绑定参数
参数尅性可以是集合、对象等
不支持PL/SQL定义的类型

declare
   v_id varchar2(20);
   v_name varchar2(20);
   v_v_pwd varchar2(20);
 begin
   v_ids:='1001';
   v_uname:='张三';
   v_pwd:='123321';
   v_sql:='insert into userinfo values(:1,:2,:3)';
   Execute immediate v_sql using v_ids,v_uname,v_pwd;
 end;

5.8、PL/SQL异常处理结构

declare
	v_i number(2);
	v_exception1 Exception;
	v_exception2 Exception;
begin
	v_i := 3;
	if v_i=3 then
		raise v_exception1;
	elsif v_i=5 then 
		raise v_exception2;
	end if;
	
	Exception
		when v_exception1 then
			dbms_output.put_line('v_i值不可以等于3');
		when v_exception2 then
       		dbms_output.put_line('v_i不能为5');
     	when others then
       		dbms_output.put_line('v_i为其他值');
end;

5.9、游标

什么是游标
- 用于处理使用select语句从数据库中检索到的多行记录的工具。
游标的分类
- 显式游标
  - 返回多行记录时，使用显式游标逐行读取。
- 隐式游标
  - PL/SQL自动为DML语句创建隐式游标，包含一条返回记录。
游标的属性
游标的声明

游标的使用方法

声明游标

Cursor cur_emp is select ename,job,sal from emp;

打开游标

open cur_emp;

提取游标

fetch cur_emp into v_name,v_job,v_sal;

判断游标提取内容

--判断游标是否提取到内容
if cur_emp%found then
  dbms_output.put_line(v_name||'-'||v_job||'-'||v_sal);
else
  exit;
end if;

关闭游标

close cur_emp;

隐式游标测试（PL/SQLL自动为DML语句创建隐式游标，包含一条返回记录）

declare
  v_name varchar2(20);
  v_job varchar2(20);
  v_sal number;
begin
  ----隐式游标(只能返回一条数据，但是可以是多列数据)
  select ename,job,sal into v_name,v_job,v_sal from emp2 where empno = 7369;
  dbms_output.put_line(v_name||'---'||v_job||'---'||v_sal);

end;

显式游标测试

declare
  v_name varchar2(20);
  v_job varchar2(20);
  v_sal number;
  --声明显式游标
  Cursor cur_emp is select ename,job,sal from emp;
begin
  --打开游标
  open cur_emp;
  --循环
  Loop
    --提取游标   
    fetch cur_emp into v_name,v_job,v_sal;
    
    --判断游标是否提取到内容
    if cur_emp%found then
      dbms_output.put_line(v_name||'-'||v_job||'-'||v_sal);
    else
      exit;
    end if;
  end loop;
  close cur_emp;
end;

利用FOR-LOOP简化显式游标（此处循环不可打开和提取游标，否则报错）

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