oracle中PL/SQL使用

oracle中PL/SQL使用

一、简介

PL/SQL(procedural language/structured query language)是oracle上的编程语言，可以像其它语言一样进行变量定义、判断、循环、异常处理等操作。

二、知识点

2.1 PL/SQL整体结构

PL/SQL整体结构如下：

[declare]  定义变量区，非必填
begin  开始逻辑处理，必填
[exception] 定义异常，非必填
end 结束标识

2.2 变量定义

变量定义包含标量类型、复杂类型的变量。

2.2.1 标量类型

标量类型即单一类型，不存在组合。通常包含数值类型、字符类型、布尔类型、日期类型。另外还可以通过符号%type复用已定义类型。

• 数据类型：number(precision,scale)，precision为精度，scale为小数后保留位置。pls_integer、binary_integer、simple_integer;
• 字符类型：char(固定长度)、varchar(不常用)、varchar2(变长字符，变量时可存32767个字节，字段时可存4000个字节)、nchar(unicode字符)、nvarchar2(unicode字符)、long(变长字符，变量时可存32760个字节，字段时可存2GB);
• 布尔类型：不能作为表字段类型，但可在pl/sql中作为逻辑值，有true、false、null；
• 日期类型：date(年/月/日/世纪/时/分/秒)、timestamp(年/月/日/世纪/时/分秒/小数秒)
• %type：引用表字段类型，即表字段是什么数据类型，那么定义的变量即为相应的数据类型。

2.2.2 复杂类型

复杂类型即由多种数据类型组合成的结构体。

• 记录类型：包含一个或多个成员的结构体，自定义结构如下：

  type type_name is record(
  	... 自定义的一个或多个成员类型
  )
  

  另外还可直接引用表(字段)定义，使用%typerow，此时变量字义时可直接引用，如下

  v_name table_name%typerow
  

• 数组类型：包含多个元素的数组，定义如下：

  type type_name is varray(size) of element_type
  

• 关联数组类型：包含多个元素的变长数组，同时每个元素可包含多个成员，定义如下：

  type type_name is table of{
  	... 自定义的一个或多个成员类型
  }
  

2.3 条件语句

条件语句用于条件判断，有if结构和case结构。

2.3.1 if结构

if结构定义如下：

if condition then
	statements;
elseif condition then
  statements；
else
  statements;
end if;

2.3.2 case结构

case结构包含值相等判断类型和条件判断类型两种：
值相等判断，即值等于指定值时，执行相应操作，定义如下:

case v_name
when value1 then
 statements;
when value2 then
 statements;
else statements;
end case;

条件判断，即表达式为true时，执行相应操作，定义如下:

case 
when condition then
 statements;
when condition then
 statements;
else statements;
end case;

2.4 循环语句

循环包含多种类型，包括loop、while…loop、for…loop、cursor for loop。

• loop定义如下：

  loop 
  	statements;
  	if condition then
  		exit; #用于退出循环
  	end if;
  	statments;
  end loop
  

  上面的条件退出还可以简写为：

  exit when condition;
  

• while…loop定义如下：

  while condition loop
  	statements;
  end loop;
  

• for…loop定义如下:

  for idx_name in low_bound..upper_bound loop
  	statements;
  end loop;
  

• cursor for loop用于遍历游标，定义如下：

  for v_name in v_curson loop
  	statements;
  end loop;
  

2.5 异常处理

异常用于捕获程序异常或自定义异常。定义如下：

exception
	when exception_type then 
		statements;
	when exception_type then 
		statements;	
	when others then 
		statements;		

常用异常有：

• NO_DATA_FOUND：select语句时没有数据返回；
• TOO_MANGY_ROWS：select语句时有多于一条数据返回；

2.6 游标

游标是操作存在内存结果集的指针，可以指向结果集的任一记录。包含显式游标和隐式游标。

2.6.1 显式游标

显式游标即需要提前声明。定义如下：

cursor cursor_name is select_statments;

游标的遍历方式有fetch方式和for方式：

• fetch方式:

  open cursor_name;
  loop
  	fetch curson_name into v_param;
  	exit when cursor_name%NOTFOUND
  end loop;
  

  上面是一次性取一条记录，一次取多条记录可以使用：

  fetch cursor_name bulk collect into v_param limit size;
  

• for方式：

  for v_name in v_curson loop
  	statements;
  end loop;
  

显式游标属性有：

• %ISFOUND:游标是否打开；
• %FOUND:数据存在；
• %NOTFOUND:数据不存在；
• %ROWCONUT:已提取多少行数据；

2.6.2 隐式游标

隐式游标不需要提前定义，游标名直接为SQL，使用方式和显式有些类似，在select或dml时会产生隐式游标。

三、使用示例

3.1 变量定义

-- 定义变量(包含默认值)
declare
    v_t varchar2(50) := 'hello';
begin
    DBMS_OUTPUT.PUT_LINE('default v_t:'||v_t);
    v_t := 'start';
    DBMS_OUTPUT.PUT_LINE('new v_t:'||v_t);
end;

-- 包含returning返回结果
declare
    v_id INTEGER;
    v_row_id ROWID;
    v_create_time date;
begin
    insert into stu(no, name) values('014', 'apple14')
    returning rowid,id, create_time into v_row_id, v_id, v_create_time;
    DBMS_OUTPUT.PUT_LINE('v_row_id:'||v_row_id ||' v_id:'||v_id  || '  v_create_time:' || to_char(v_create_time, 'yyyy-MM-dd HH24:mi:ss'));
end;

-- 查询时获取指定字段值
declare
    v_no varchar2(50);