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);
    --     复用表中指定字段数据类型
    v_name stu.name%type;
begin
    select no, name into v_no,v_name from stu where ROWNUM<2;
    DBMS_OUTPUT.PUT_LINE('v_no:' || v_no||'  v_name:' || v_name);
end;

-- 字段类型复用表行类型，这样可以复用表一行中字段个数及类型
declare
    --     复用表中指定字段数据类型
    v_stu stu%rowtype;
begin
    select * into v_stu from stu where ID=1;
    DBMS_OUTPUT.PUT_LINE('v_stu.no:' || v_stu.no||'  v_stu.name:' || v_stu.name);
end;

-- 定义结构体变量
declare
    type stu_type is record (
        v_id INTEGER,
        v_no varchar2(50),
        v_name varchar2(50)
        );
    v_stu stu_type;
begin
    select Id, NO, NAME into v_stu from stu where id=1;
    DBMS_OUTPUT.PUT_LINE('v_id:' || v_stu.v_id || ' v_no:' || v_stu.v_no || ' v_name:' || v_stu.v_name);
end;

-- 数组类型使用
declare
--     定义数组类型
    type v_arr_type is varray(3) of varchar2(50);
--     定义数组变量
    v_arr v_arr_type;
begin
--     给数据赋值
    v_arr := v_arr_type('yes');
    DBMS_OUTPUT.PUT_LINE('v_arr(1):'||v_arr(1));
end;

-- table类型,类似于变长的数据类型
declare
    -- 复用表中指定字段数据类型
    type v_stu_table_type is table of stu%rowtype;
    v_stu_table v_stu_table_type;
begin
    select * bulk collect into v_stu_table from stu;
    DBMS_OUTPUT.PUT_LINE('v_stu_table.count:' || v_stu_table.count);
    DBMS_OUTPUT.PUT_LINE('v_stu_table(1).no:' || v_stu_table(1).no||'  v_stu_table(1).name:' || v_stu_table(1).name);
end;

3.2 条件判断

- 条件判断
declare
    v_n integer;
begin
    v_n:= DBMS_RANDOM.RANDOM();
    if v_n <0 then
        DBMS_OUTPUT.PUT_LINE(v_n||'小于0');
    elsif v_n>0 and v_n<1000 then
        DBMS_OUTPUT.PUT_LINE(v_n||'大于0且小于1000');
    else
        DBMS_OUTPUT.PUT_LINE(v_n||'大于1000');
    end if;
end;

-- 条件判断
declare
    v_n integer;
    v_r varchar(50);
begin
    v_n:= DBMS_RANDOM.VALUE(1,3);
    v_r :=
    case v_n
    when 1 then 'v_n:1'
    when 2 then 'v_n:2'
    else 'other v_n:'||v_n
    end ;
    DBMS_OUTPUT.PUT_LINE(v_r);
end;

3.3 循环

-- 循环
declare
    v_i integer :=0;
begin
    while v_i <10 loop
        DBMS_OUTPUT.PUT_LINE(v_i);
        v_i:=v_i+1;
    end loop;
end;

declare
    v_i integer :=0;
begin
    while v_i <10 loop
        DBMS_OUTPUT.PUT_LINE(v_i);
        v_i:=v_i+1;
    end loop;
end;

begin
    for v_i in 1..10 loop
        DBMS_OUTPUT.PUT_LINE(v_i);
    end loop;
end;

declare
    type v_arr_type is varray (5) of varchar(50) ;
    v_arr v_arr_type := v_arr_type('one', 'two', 'three');
begin
    for v_i in v_arr.first..v_arr.last
        loop
            DBMS_OUTPUT.PUT_LINE(v_arr(v_i));
        end loop;
end;

3.4 异常

-- 异常处理
declare
    v_t integer;
    v_s varchar2(20);
begin
    select id into v_s from stu where id>50;
    select 1/0 into v_t from dual;
    exception
    when no_data_found then DBMS_OUTPUT.PUT_LINE('no data');
    when others then DBMS_OUTPUT.PUT_LINE('sqlcode:'||sqlcode||'  sqlerrm:'||sqlerrm);
end;

3.5 游标

-- 游标常规查询
declare
    cursor v_c is select no, name from stu where id<10;
    v_no stu.no%type;
    v_name stu.name%type;
begin
    open v_c;
    fetch v_c into v_no, v_name;
--     %found:返回最近一次是否找到，布尔值，找到为true
--     %notfound:返回最近一次是否找到，布尔值，没找到为true
--     %isopen: 游标打开为true
--     %rowcount:返回游标读取的记录数
    while v_c%found loop
            DBMS_OUTPUT.PUT_LINE('v_no:' || v_no || '  v_name:' || v_name);
        fetch v_c into v_no, v_name;
    end loop;
    close v_c;
end;

-- 游标常规查询
declare
    cursor v_c is select no, name from stu where id<10;
begin
    for v_t in v_c loop
        DBMS_OUTPUT.PUT_LINE('no:' || v_t.NO || '  name:' || v_t.NAME);
    end loop;
end;

-- 子查询实现游标功能
begin
    for v_t in (select no, name from stu where id<10) loop
        DBMS_OUTPUT.PUT_LINE('no:' || v_t.NO || '  name:' || v_t.NAME);
    end loop;
end;

-- 游标常规查询,带参数
declare
    cursor v_c(v_lt_id integer) is select no, name from stu where id>v_lt_id;
begin
    for v_t in v_c(v_lt_id => 10) loop
        DBMS_OUTPUT.PUT_LINE('no:' || v_t.NO || '  name:' || v_t.NAME);
    end loop;
end;

-- 游标常规查询,带参数和返回值
declare
    type stu_record_type is record (
        stu_id integer,
        stu_name varchar2(50)
        );
    cursor v_c(v_lt_id integer) return stu_record_type is select id , name from stu where id>v_lt_id;
begin
    for v_t in v_c(v_lt_id => 10) loop
        DBMS_OUTPUT.PUT_LINE('stu_id:' || v_t.stu_id || '  stu_name:' || v_t.stu_name);
    end loop;
end;

select * from stu;
-- 游标常规更新
declare
    cursor v_c is select id, no, name from stu where id<10;
begin
    for v_t in v_c loop
        if v_t.id=5 then
            update stu set name='banana' where id=v_t.ID;
        end if;
    end loop;
    commit;
end;

-- 游标常规更新
declare
    cursor v_c is select id, no, name from stu where id<10 for update ;
begin
    for v_t in v_c loop
        if v_t.id=5 then
            update stu set name='moon' where current of v_c;
        end if;
    end loop;
    commit;
end;

-- 更新
declare
    v_d stu%rowtype;
    cursor v_c is select * from stu where id<20 for update ;
begin
    open v_c;
    loop
        fetch v_c into v_d;
        exit when v_c%notfound;
        if v_d.id >8 then
            update stu set NAME='orange' where current of v_c;
        end if;
    end loop;
    commit ;
    close v_c;
end;

-- 获取更新行数，通过隐式游标： SQL%rowcount
declare
    v_rows integer;
begin
    update stu set name=name||'_x'  where ROWNUM<4;
    if SQL%found then
        v_rows:=SQL%rowcount;
        DBMS_OUTPUT.PUT_LINE('v_rows:'||v_rows);
    end if;
end;

select * from stu;

-- 隐式游标： SQL%found
begin
    delete from stu where id=10;
    if SQL%found then
        DBMS_OUTPUT.PUT_LINE('delete');
    else
        DBMS_OUTPUT.PUT_LINE('not delete');
    end if;
end;

--     游标变量
declare
    type stu_cur_type is ref cursor ;
    v_stu_cur stu_cur_type;
    v_r stu%rowtype;
begin
    open v_stu_cur for select * from stu;
    fetch v_stu_cur into v_r;
    while v_stu_cur%FOUND loop
        DBMS_OUTPUT.PUT_LINE('id:'||v_r.ID);
        fetch v_stu_cur into v_r;
    end loop;
    close v_stu_cur;

    open v_stu_cur for select * from stu where rownum<4;
    fetch v_stu_cur into v_r;
    while v_stu_cur%FOUND loop
        DBMS_OUTPUT.PUT_LINE('name:'||v_r.NAME);
        fetch v_stu_cur into v_r;
    end loop;
    close v_stu_cur;
end;