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

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

学习目标

1. 什么是对象查询？
2. 如何使用Example查询已有实体对象的类似对象？
3. 如何使用Order对Criteria查询结果进行排序？
4. 如何使用Criteria限定查询返回的数据行数？
5. 什么是PL/SQL？
6. PL/SQL语法结构？
7. PL/SQL如何实现异常处理？
8. 游标的使用分为哪几个步骤？
9. 编写PL/SQL实现流程控制？
10. 使用PL/SQL编写动态SQL？
11. 使用PL/SQL实现异常处理？
12. 使用PL/SQL实现对游标的控制？
13. 了解PL/SQL的特点？
14. 掌握Pl/SQL的语法？
15. 会使用PL/SQL实现流程控制？
16. 会使用PL/SQL实现对异常的处理？
17. 会使用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;