OraclePL/SQL基础

PL/SQL概述

什么是PL/SQL   		是一种块结构语言，
PL/SQL (Procedural Language/SQL)
一种过程化语言，通过增加编程语言的特点，实现对SQL的扩展.

PL/SQL的特点

• 支持所有SQL的语法
• 支持case语句，方便的实现循环
• 通过继承,实现子类具有父类的属性和方法
• 设置了新的日期类型
  运算符和表达式： PL/SQL语音支持操作符包含关系运算符，一般运算符和逻辑运算符，与 SQL语音类似。

变量的作用域:
PL/SQL中作用域的范围是从变量声明的开始一 直到这个PL/SQL的结束

分号的注意事项:
DECL ARE、BEGIN、EXCEPTION后没有分号。而END以及所有SQL语句和
PL/SQL语句必须已分号结束。

%type ：定义一个变量，其数据类型与已经定义的某个数据变量（尤其是 表的某一列）的数据类型相一致这时可以使用%Type
（优点：可以不必知道所 引用的数据库列的数据类型。所引用的数据类型可以实现时改变，容易保持一 致，不必修改PL/SQL程序）
%rowtype: 返回一个记录类型，其数据类型和数据库表的数据结构相一 致，这时可以使用%ROWTYPE.（优点：可以不必知道所引用数据库列的个数 和数据类型。所引用的数据库中列的个数和数据库类型可以实现改变，容易保持 一致，不用修改PL/SQL程序）

示例：

declare --声明部分
        p_id number:=5;--商品编号
        p_stock product.stock_count%type; --商品库存
        
        --pro product%rowtype;--用来保存一个商品对象的数据
        
begin--程序部分     into 赋值  dbms:oracle管理系统 
   select productname,price,stock_count
   into pro.productname,pro.price,pro.stock_count
   from product where productid=p_id;
   dbms_output.put_line('姓名：'||pro.productname);
   dbms_output.put_line('价格：'||pro.price);
   dbms_output.put_line('库存：'||pro.stock_count);
   exception
     when others then  --异常部分，可省略
       dbms_output.put_line('该商品不存在');
end; --结束

注释：
在PL/SQL可以使用两种注释符号：
1 .‘‐‐’ 双减号
2. ‘/ /’

SQL语句，过程语句
三部分：
1.声明部分Declare
2.程序部分begin
3.异常部分Exception
声明
使用DECLARE关键字
用于定义变量或者常量

DECLARE variable_name [CONSTANT] type [not null][:=value]; 
   声明    变量名称  是否为常量  变量的类型 是否为空 变量初始化

变量命名规则

1. 变量名首字母必须是英文字母，其后可以是字母、数字或者特殊字符$、#和下划线

2. 变量名长度不超过30个字符

3. 变量名中不能有空格

查询只能返回一条记录，多条或0条都会产生异常 INTO语句必须在SELECT和FROM子句之间
INTO后需要赋值的变量的数据类型必须和SELECT子句中检索 的数据类型一致
INTO后需要赋值的变量个数需要和SELECT查询出的字段数一致 只能运行一个结构

控制语句 条件语句 - IF语句
语法：

if  then
PL/SQL和SQL语句 
else   
PL/SQL和SQL语句
end if; 

示例：

declare 
  p_id product.productid%type:=9;--商品编号
  p_stock product.stock_count%type;--商品库存 
begin
  select stock_count into p_stock from product
  where productid =p_id; --查询当前编号的库存并保存
  if p_stock> 0 then
    update product set stock_count=stock_count-1 where productid=p_id;
    commit;--保存
    dbms_output.put_line('商品:'||p_id||'购买成功！');
    elsif p_stock<0 then 
      dbms_output.put_line('商品:'||p_id||'数据异常！');
    else
      dbms_output.put_line('商品:'||p_id||'库存不足！');
  end if;
end;

条件语句 - CASE语句
语法：

CASE 表达式 
WHEN 表达式 THEN
语句段1 
WHEN 表达式2 THEN 
语句段2 
ELSE 
语句段
END CASE; 

示例：

 ---根据订单 id 查询订单的状态 id ，并输出相应的文本
declare 
  o_id order2.orderid%type:=2;
  o_status order2.status%type;
begin
  select status into o_status from order2 where orderid=o_id;
  case o_status
    when '1'then  --引号可省略
      dbms_output.put_line('订单已提交！');
    when '2'then
      dbms_output.put_line('订单已付款！');
    when '3'then
      dbms_output.put_line('订单已发货！');
    when '4'then
      dbms_output.put_line('订单已完成！');    
     else
       dbms_output.put_line('订单异常！'); 
       end case;
end;

循环结构 ­ LOOP循环
语法:

LOOP 
要执行的语句; 
 IF 条件语句 THEN ‐‐条件满足时跳出循环 
EXIT; 
END IF; 
END LOOP;

示例：

-- 实现循环输出1-5的商品名称
declare 
  p_id product.productid%type:=1;
  p_name product.productname%type;
begin
  loop --循环开始
    if p_id>5 then
      exit;
      end if;
      select productname into p_name from product
      where productid=p_id;
      dbms_output.put_line(p_id||':'||p_name);
      p_id:=p_id+1;
  end loop; --循环结束
end;

循环结构

WHILE-LOOP循环
	类似于while循环
		WHILE循环条件LOOP
		statements;
		END LOOP; 

FOR-LOOP循环
类似与for循环

PL/SQL的预定义异常

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

PL/SQL异常处理
PL/SQL支持程序员在程序中使用 EXCEPTION 块捕获这些发生错误的条件，并针对错误情况采取适当的措施。 PL/SQL中有两种异常
1.系统定义的异常
2.用户定义的异常

语法
使用 RAISE 关键字引发异常 使用 EXCEPTION 关键字处理异常

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

示例：

--.检查学生“语文”课最近一次考试是否有不及格（60分及格）的学生。如有，每人加2分，高于95分的学生不再加分，直至所有学生这次考试成绩均及格
declare 
  stu_no student3.studentno%type;
  stu_name student3.studentname%type;
  sub_name subject.subjectname%type:='语文';--课程名称
  sub_no subject.subjectno%type:=25;--课程编号
  rescj result.studentresult%type;--成绩
  num number;
  rownu number;
  temp_ex exception;
begin
   select subjectno into sub_no from subject where subjectname=sub_name;
   select count(1) into num  from result where subjectno=sub_no;
  if num=0 then
    raise temp_ex;
    end if;  
   for i in 1..num loop
      rownu:=1;
      select studentresult into rescj from 
      (select r.*,rownum rn from result r where subjectno in sub_no) studentresult
      where rn=rownu;  
      if rescj<60 then
        rescj:=rescj+2;    
       end if;
        dbms_output.put_line(sub_name||'成绩：'||rescj);   
        end loop;
        exception
          when temp_ex then
            dbms_output.put_line('异常');
end;

动态SQL

什么是动态SQL

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

语法
Excute immediate dynamic_sql;

动态SQL的执行

• 绑定变量

• 通过占位符绑定参数

• 参数类型可以是集合、对象等.

• 不支持PL/SQL定义的类型

 --添加数据 
declare 
 plsql varchar2(200);
 pl_id product.productid%type:=11;
 pl_name product.productname%type:='牛奶';
 pl_price product.price%type:=9.9;
 pl_stock product.stock_count%type:=77;
 
begin
   plsql:='insert into product values(:1,:2,:3,:4)';
   execute immediate plsql using pl_id,pl_name,pl_price,pl_stock;--	using: 解决占位符
   commit;
end;

什么是游标

游标(CURSOR) 用来处理使用select语句从数据库中检索到的多行记录的工具 查询数据,获取记录集合的指针

游标的分类

1. 显示游标 返回多条记录时，使用显示游标逐行读取
2. 隐式游标 PL/SQL自动为DML语句创建隐式游标，包含一条返回记录

游标
	CURSOR cursor_name [(parammeter [,parammeter]......)]
	2 [RETURN return_type] IS select_sql; 

	CURSOR:用于声明- -个游标
	parameter:可选参数,用于指定参数类型、模式等
	return:可选,指定游标的返回类型
	selectsql:需要处理的select语句，不能含INTO子句

打开游标

open cursor_ name   --	使用OPEN语句开启一-个游标

提取游标

FETCH cursor_ name INTO variable_ list
		使用FETCH语句实现对游标内容的读取
		variable_ list必须与从游标提取的结果集类型相同

关闭游标

close cursor_ name	
	使用CLOSE语句关闭一-个游标	
	关闭游标后，所有资源都将被释放，且不能再次被打开

--使用游标完成对商品表的输出打印
	declare
 		pl_id product.productid%type;
 		pl_name product.productname%type;
 		cursor cs_pro is select productid,productname from product;
	begin
  		--打开游标 ,使用for循环，会自动打开游标
     		--open cs_pro;
       		  -- fetch cs_pro into pl_id,pl_name;--从游标中读取数据保存至变量
         		for c in cs_pro loop
           		dbms_output.put_line(c.productid||c.productname);
           		end loop;
     		--close cs_pro;--结束游标
	end;

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

总结
PL/SQL是一 种什么语言?
结构块语言：声明，程序，异常，end
在PL/SQL中如何实现流程控制?
if,elsif,case,for, while,loop

使用游标的步骤?
1.声明游标
2.打开游标
3.提取游标
4.关闭游标

请描述出PL/SQL中的异常处理结构

    temp_ex except ion;--------在这预定义异常temp_ ex BEGIN    .................
      RAISE temp_ ex;--------在这里触发异常temp_ ex    .................               --------发生异常后不会执行这里的代码 EXCEPTION
    WHEN temp_ ex THEN --------控制被转到异常处理部分，这里的代码被执行    ................. END ; ```