testbook

Inceptor支持的PL/SQL

Inceptor支持部分PL/SQL。

基本语句：

赋值语句

":"称绑定变量指示符，基本赋值语句示例:

声明变量，赋予默认值，及赋值语句
declare
  a string := 'a1'
  b int default 1
begin
  dbms_output.put_line('a in block 1: ' || a)
  dbms_output.put_line('b in block 1: ' || b)
  a := 'a2'
  b := 2
  dbms_output.put_line('a in block 1: ' || a)
  dbms_output.put_line('b in block 1: ' || b)
end;

输出结果为：
a in block 1: a1
b in block 1: 1
a in block 1: a2
b in block 1: 2

完整的作用域机制

变量的作用域和可见性，变量的作用域为变量申明开始到当前语句块结束。当外部过程和内嵌过程定义了相同名字的变量的时候，在内嵌过程中如果直接写这个变量名是没有办法访问外部过程的变量的，可以通过给外部过程定义一个名字outername,通过outername变量名来访问外部过程的变量。

declare
  a string := 'a1'
begin
  declare
  b string := 'b1'
  begin
    dbms_output.put_line('a in block 1.1: ' || a)
    dbms_output.put_line('b in block 1.1: ' || b)
  end b1

  dbms_output.put_line('a in block 1.1: ' || a)
  --dbms_output.put_line('b in block 1.1: ' || b)   当去掉这一行注释时，有异常 java.lang.NullPointerException
end;

匿名块执行

PL/SQL 匿名块语句是可以包含 PL/SQL 控制语句和 SQL 语句的可执行语句。它可以用来在脚本语言中实现过程逻辑。
匿名块中可以有一个可选的DECLARE段用以声明本块所使用的变量，直接写在最外层的匿名块会立即执行（区别于写在函数或者包内部的匿名块），也是PL/SQL程序的唯一入口.

最简单的匿名块：
BEGIN
  put_line( "Hello" );
END;

declare
  a string := 'a1'
begin
  dbms_output.put_line('a in block 1: ' || a)
  declare
  b string := 'b1'
  begin
    dbms_output.put_line('a in block 1.1: ' || a)
    dbms_output.put_line('b in block 1.1: ' || b)
  end b1

end;

输出结果为：
a in block 1: a1
a in block 1.1: a1
b in block 1.1: b1

存储过程调用

PL/SQL 过程引用由过程名以及随后的参数（如果有的话）组成。

create or replace procedure test(arg int) is
begin
  put_line('hello world ' || arg)
end;

begin
test(5)
end;

数据类型：

标量类型

标量类型的含义是存放单个值。Inceptor中支持的标量类型为int/double/string/etc。

集合类型

集合类型主要分为3大类：Associative array，Nested table，Variable-size array.
使用之前必须先定义集合类型: i_var int
type aa_type is table of i_var%type index by string
type nt_type is table of int not null
type va_type is varray (100) of string
具体用法参考Oracle官方PLSQL文档.

示例：
create or replace procedure collection_get_test(arg int)
is
declare
type t_intc is table of arg%type
v_idc t_intc
v_valuec t_intc
v_id int
v_value int
begin
  select id, value bulk collect into v_idc, v_valuec from t1 as t1
  dbms_output.put_line(v_idc(arg) || " " || v_valuec(arg))
  if v_idc(arg) is not null and v_valuec(arg) is not null then
    v_id := v_idc(arg)
    v_value := v_valuec(arg)
    v_idc(arg) := v_idc(arg) - 1
    v_valuec(arg) := v_valuec(arg) - 1
    dbms_output.put_line(v_idc(arg) || " " || v_valuec(arg))
    select id, value into v_idc(arg), v_valuec(arg) from t1 as t1 where id = v_id and value = v_value
    dbms_output.put_line(v_idc(arg) || " " || v_valuec(arg))
  end if

end
;

begin
collection_get_test(0)
collection_get_test(1)
collection_get_test(2)
end;

RECORD类型

Record类型：类似C语言struct类型的临时记录对象类型，其变现形式为“单行多列”，也可能是“单列”。在PLSQL当中表的schema对应到一个RECORD类型的，用户也可以定义自己的RECORD类型而不和表schema发生任何关系。

create or replace procedure record_test()
is
i_v int
type rec_t is record(id i_v%type, value string)
rec_v rec_t
begin
  rec_v.id := 0
  rec_v.value := "value"
  put_line(rec_v.id || " " || rec_v.value)
  select id as id, to_char(value) as value into rec_v from t1 where id = 10
  put_line(rec_v.id || " " || rec_v.value)
end
;

begin
record_test()
end;

transwarp -t -h localhost -f test.sql

cursor类型

cursor类型：cursor用于提取多行数据，定义后不会有数据，使用后才会有数据。

create or replace procedure cursor_proc(arg int)
is
declare
  v_id int
  v_value int
  cursor cur(cur_arg int)
  is
    select  from (select  from t1 where id < cur_arg)
begin
  open cur(arg)
  <<cur_loop1>>
  loop
  fetch cur into v_id, v_value
  dbms_output.put_line(v_id || ' ' || v_value)
  exit when cur%notfound
  end loop cur_loop1
  close cur
  open cur(arg)
  <<cur_loop2>>
  loop
  fetch cur into v_id, v_value
  dbms_output.put_line(v_id || ' ' || v_value)
  exit when cur%notfound
  end loop cur_loop2
end;

begin
cursor_proc(12)
end;

隐/显式类型转换

显式类型转换:
to_number(value) 一般为字符类型转换为数值类型
to_date(value, 'yyyymmdd') 字符类型转换为日期类型
to_char(value) 数值类型转换为字符类型
隐式类型转换:
连接时(||)，数值类型一般转为字符类型；赋值调用函数时，以定义的变量类型为准。

参数和变量属性：

参数默认值及形参实参指定符=>

create or replace procedure named_notation(arg1 in int default 10, arg2 in int default 20, arg3 int default null, arg4 int default 40) is
begin
        put_line("arg1 = " || arg1 || ", arg2 = " || arg2 || ", arg3 = " || arg3 || ", arg4 = " || arg4 || ".")
        return
end;

declare
val1 int
val2 int
val3 int
val4 int
begin
        val1 := 0
        val2 := 1
        val3 := 2
        val4 := 3
        named_notation(arg1 => val1, arg2 => val2, arg3 => val3, arg4 => val4)
        named_notation(arg4 => val1, arg3 => val2, arg2 => val3, arg1 => val4)
        named_notation(arg2 => val2, arg4 => val4)
        named_notation(arg2 => val4, arg4 => val2)
        named_notation(val4)
        named_notation()
end;

流程控制语句：

IF/ELSE IF/ELSE语句

Syntax #1: IF-THEN

IF condition THEN

         {...statements...}

END IF;

Syntax #2: IF-THEN-ELSE

IF condition THEN

         {...statements...}

ELSE

         {...statements...}

END IF;

Syntax #3: IF-THEN-ELSIF

IF condition THEN

         {...statements...}

ELSIF condition THEN

         {...statements...}

ELSE

         {...statements...}

END IF;

示例：
declare
var int
begin
  var := 1
  if var = 1 then
    put_line("Label print hit on var = " || var || ".")
    var := var + 1
  end if
end;

GOTO语句

declare
var int
begin
  var := 1
<<print>>
  put_line("Label print hit on var = " || var || ".")
  if var < 5 then
    var := var + 1
    goto print
  end if
end;

LOOP循环

declare
var int
begin
  var := 1
<<simple_loop>>
  loop
    put_line("Simple loop body " || var || ".")
    var := var + 1
    exit simple_loop when var = 5
  end loop simple_loop
end;

FOR循环

declare
  cursor cur(cur_arg int) is
    select * from t1 where id < cur_arg
begin
  for for_var in 0..3 loop
    put_line(for_var)
  end loop
  for for_var in 3 ..0 loop
    put_line(for_var)
  end loop
  for for_var in reverse 0.. 3 loop
    put_line(for_var)
  end loop
  for for_rec in cur(12) loop
    put_line(for_rec.id || " " || for_rec.value)
  end loop
  --Re-loop the cursor without closing it,
  --the last for loop should have done closing.
  for for_rec in cur(12) loop
    put_line(for_rec.id || " " || for_rec.value)
  end loop
end;

FORALL循环

declare
  type rec_t is record(id int, value int)
  type cr_t is table of rec_t
  cr_v cr_t
begin
  put_line("Select into...")
  select * bulk collect into cr_v from t1 as t1

  put_line("Forall 1...")
  forall forall_v in 1..1
    select * from t1 where id = cr_v(forall_v).id and value = cr_v(forall_v).value

  put_line("Forall 2...")
  forall forall_v in 1..2
    select * from t1 where id = cr_v(forall_v).id and value = cr_v(forall_v).value
end;

WHILE循环

declare
var int
begin
  var := 1
  while var != 5
  loop
    put_line("While loop body " || var || ".")
    var := var + 1
  end loop
end;

CONTINUE(WHEN)语句

declare
var1 int
var2 int
begin
  var1 := 1
<<outer_loop>>
  loop
    var2 := 1
    put_line("Outer loop body " || var1)
    var1 := var1 + 1
  <<inner_loop>>
    loop
      continue outer_loop when var1 = 2
      put_line("Inner loop body " || var2)
      var2 := var2 + 1
      exit inner_loop when var2 = 3
    end loop
  end loop outer_loop
end;

EXIT(WHEN)语句

declare
var1 int
var2 int
begin
  var1 := 1
  loop
    var2 := 1
    put_line("Outer loop body " || var1)
    var1 := var1 + 1
    exit when var1 = 3
  end loop outer_loop
end;

与SQL的交互:

动态执行SQL

PL/SQL中执行SQL语句时，动态执行添加参数。

create or replace procedure dynamic_sql_test(tab string, id int, value int) is
declare
i_var int := 0
begin
  put_line('Selecting from ' || tab)
  execute immediate ' select * from (select * from ' || tab || ' where id = :v1 and value = :v2)' using in id, out value
  execute immediate ' begin select count(*) into :v from ' || tab || ' where id = :v1 and id < :V1 + 1 and value = :v2 end' using out i_var, id, in value
end;

begin
  dynamic_sql_test('t1', 10, 100)
  dynamic_sql_test('t2', 20, 200)
end;

显式CURSOR及其基本操作：OPEN/FETCH(BULK COLLECT)/NOTFOUND/etc.

cursor基本操作一

declare
m int := 2
cursor c is select * from t1 as t
type t is record(id int, value int)
r t
begin
  loop
    if !c%isopen then
      open c
    end if
    fetch c into r
    put_line(r.id % m || ' ' || r.value % m)
    exit when c%notfound
  end loop
end;

cursor基本操作二

declare
  type t_c is table of int not null
  type t_r is record(id int, value int)
  type t_cr is table of t_r
  v_cid t_c
  v_cvalue t_c
  v_cr t_cr
  cursor cur(cur_arg int)
  is
    select * from t1 where id < cur_arg
begin
  open cur(arg)
  <<cur_loop>>
  loop
  fetch cur bulk collect into v_cid, v_cvalue limit 10
  put_line(v_cid.count() || " " || v_cvalue.count())
  put_line(v_cid(1) || " " || v_cvalue(1))
  exit when cur%notfound
  end loop cur_loop
end

SELECT (BULK COLLECT) INTO语句

declare
  type t_c is table of int not null
  type t_r is record(id int, value int)
  type t_cr is table of t_r
  v_cid t_c
  v_cvalue t_c
  v_cr t_cr
begin
  select id, value bulk collect into v_cid, v_cvalue from t1 where id < arg
  put_line(v_cid.count() || " " || v_cvalue.count())
  put_line(v_cid(1) || " " || v_cvalue(1))
  select * bulk collect into v_cr from t1 where id < arg
  put_line(v_cr.count())
  put_line(v_cr(1).id || " " || v_cr(1).value)
end;

sql的隐式游标

隐式cursor当然是相对于显式而言的，就是没有明确的cursor的declare。

create or replace procedure implicit_cursor_test(arg int)
is
begin
  dbms_output.put_line('Loop 1...')
  for v_rec in (select  from t1 where id = arg) loop
    dbms_output.put_line('v_rec: ' || v_rec.id || ' ' || v_rec.value)
    dbms_output.put_line('Selecting...')
    select  from (select  from t1 where id = v_rec.id)
  end loop a
end;

begin
  implicit_cursor_test(10)
end;

DDL语句（使用动态SQL执行）

DDL 主要的命令有CREATE、ALTER、DROP、TRUNCATE、COMMENT、GRANT、REVOKE，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上。

create or replace procedure dynamic_sql_test(tab string, id int, value int) is
declare
i_var int := 0
begin
  dbms_output.put_line('Selecting from ' || tab)
  execute immediate ' select  from (select  from ' || tab || ' where id = :v1 and value = :v2)' using in id, out value
  execute immediate ' begin select count() into :v from ' || tab || ' where id = :v1 and id < :V1 + 1 and value = :v2 end' using out i_var, id, in value
  dbms_output.put_line(tab || ' count: ' || i_var)
end;

declare
  tab_prefix string := 't'
  tab_id string := '3'
begin
  dynamic_sql_test('t1', 10, 100)
  dynamic_sql_test('t2', 20, 200)
  execute immediate ' create table :tab as select  from (select  from t1)' using tab_prefix || tab_id
  execute immediate ' select  from ' || tab_prefix || tab_id
  execute immediate ' truncate table :tab' using tab_prefix || tab_id
  execute immediate ' select  from ' || tab_prefix || tab_id
  execute immediate ' drop table :tab' using tab_prefix || tab_id
end;

异常

支持用户自定义异常和系统预定义异常

系统预定义异常

ACCESS_INTO_NULL                      未定义对象
CASE_NOT_FOUND                        CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时
COLLECTION_IS_NULL                    集合元素未初始化
CURSER_ALREADY_OPEN                   游标已经打开
DUP_VAL_ON_INDEX                      唯一索引对应的列上有重复的值
INVALID_CURSOR                        在不合法的游标上进行操作
INVALID_NUMBER                        内嵌的 SQL 语句不能将字符转换为数字
NO_DATA_FOUND                         使用 select into 未返回行，或应用索引表未初始化的元素时
TOO_MANY_ROWS                         执行 select into 时，结果集超过一行
ZERO_DIVIDE                           除数为 0
SUBSCRIPT_BEYOND_COUNT                元素下标超过嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT               使用嵌套表或 VARRAY 时，将下标指定为负数
VALUE_ERROR                           赋值时，变量长度不足以容纳实际数据
LOGIN_DENIED                          PL/SQL 应用程序连接到数据库时，提供了不正确的用户名或密码
NOT_LOGGED_ON                         PL/SQL 应用程序在没有连接数据库的情况下访问数据
PROGRAM_ERROR                         PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包
ROWTYPE_MISMATCH                      宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL                          使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR                         运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID                        无效的 ROWID 字符串
TIMEOUT_ON_RESOURCE                   在等待资源时超时

用户自定义异常

DECLARE
  my_exception EXCEPTION

声明时赋值PARGMA EXCEPTION_INIT

CREATE OR REPLACE PROCEDURE exception_init_test_0()
IS
declare
aaa exception
bbb exception
PRAGMA EXCEPTION_INIT(aaa,-20002)
PRAGMA EXCEPTION_INIT(bbb,-20002)
PRAGMA EXCEPTION_INIT(aaa,-1476)
PRAGMA EXCEPTION_INIT(bbb,-1476)
BEGIN
  raise aaa
EXCEPTION
    WHEN aaa then
                put_line("Caught exception aaa, error code = " || sqlcode() || " error message is: " || sqlerrm())
END
;

输出结果为：
Caught exception aaa, error code = -1476 error message is: ZERO_DIVIDE

支持RAISE语句及WHEN (OR) THEN (OTHERS)异常处理

DECLARE
  inner EXCEPTION
  outer EXCEPTION
BEGIN
    BEGIN
        RAISE inner
    EXCEPTION
        WHEN inner THEN
            raise outer
    END
EXCEPTION
  WHEN OTHERS THEN
    select * from t1 where id = 10
END

支持存储过程内部和存储过程之间的异常传播

当一个异常错误在执行部分引发时，有下列情况：
如果当前块对该异常错误设置了处理，则执行它并成功完成该块的执行，然后控制转给包含块。
如果没有对当前块异常错误设置定义处理器，则通过在包含块中引发它来传播异常错误。

DECLARE
    var INT
    inner EXCEPTION
    outer EXCEPTION
BEGIN
    var := 3
    IF var > 2 THEN
        BEGIN
            raise inner
        EXCEPTION
            WHEN outer then
                raise outer
        END
    END IF
EXCEPTION
    WHEN inner THEN
        select  from t1 where id = 10
    WHEN outer THEN
        select  from t1 where id = 10
END;

部分系统预定义异常抛出点

支持部分系统预定义异常抛出点。

PACKAGE：

包一般分为2部分，包头和包体： create or replace package pkg
create or replace package body pkg

包内全局变量

包的全局变量定义在包头部分。

包内类型

包内类型在包头定义，用户可以直接使用定义好的type。

包内函数

包内函数在包头处声明，在包体实现。

create or replace package pkg is --中文注释  包头
pv int := 0                      --全局变量
type pvt is pv%type              --包内类型
procedure pf_increase(i int)     --包内函数
end;

create or replace package body pkg is   -- 包体
/中文注释/
declare
pbv int := 1
function pbf_increase() return int
is
begin
  pbv := pbv + 1
  put_line('pbf_increase: pbv = ' || pbv)
  return pbv
end
procedure pf_increase()
is
begin
  pv := pv + pbf_increase()
  put_line('pf_increase: pv = ' || pv)
end
begin
  pv := 1
  pbv := 2
  pbf_increase()
  pf_increase()
end;

declare
v1 pkg.pvt := 10                   --直接使用包类型
v2 pkg.pv%type := 11
begin
  dbms_output.put_line('pkg.pv = ' || pkg.pv)
  pkg.pv := v1
  dbms_output.put_line('pkg.pv = ' || pkg.pv)
  pkg.pv := v2
  dbms_output.put_line('pkg.pv = ' || pkg.pv)
end;


输出结果为：
pbf_increase: pbv = 3
pbf_increase: pbv = 4
pf_increase: pv = 5
pkg.pv = 5
pkg.pv = 10
pkg.pv = 11

部分系统包和函数：

SQLCODE()/SQLERRM()

SQLCODE---返回ORACLE错误号。
SQLERRM---返回ORACLE错误信息。

CREATE OR REPLACE PROCEDURE exception_init_test_1()
IS
declare
aaa exception
bbb exception
PRAGMA EXCEPTION_INIT(aaa,-20002)
PRAGMA EXCEPTION_INIT(bbb,-20002)
BEGIN
  raise aaa
EXCEPTION
    WHEN bbb then
                put_line("Caught exception bbb, error code = " || sqlcode() || " error message is: " || sqlerrm())
END
;

BEGIN
    exception_init_test_1()
    put_line("call finishes")
END;

输出结果：
Caught exception bbb, error code = -20002 error message is: USER_DEFINED

RAISE_APPLICATION_ERROR()

用于在plsql使用程序中自定义不正确消息

CREATE OR REPLACE PROCEDURE raise_application_error_test ()
IS
declare
aaa exception
PRAGMA EXCEPTION_INIT(aaa,-20001)
BEGIN
        RAISE_APPLICATION_ERROR(-20001, "This is an error!")
EXCEPTION
    WHEN aaa then
                  put_line("Caught an exception error code = " || sqlcode() || " error message is: " || sqlerrm())
END
;

BEGIN
    raise_application_error_test()
    put_line("call finishes")
END;

输出结果：
Caught an exception error code = -20001 error message is: This is an error!
call finishes

DBMS_OUTPUT.PUT_LINE()

这是一个输出语句。

OWA_UTIL.WHO_CALLED_ME()

在存储过程中获取自身的名字:

create or replace function outer_outer_func() return int is
owner string
name string
lineno int
ctype string
begin
  put_line('Outer outer func')
  owa_util.who_called_me(owner, name, lineno, ctype)
  dbms_output.put_line('Owner: ' || owner)
  dbms_output.put_line('Name: ' || name)
  dbms_output.put_line('Lineno: ' || lineno)
  dbms_output.put_line('Type: ' || ctype)
  dbms_output.put_line('')
end;

begin
  outer_outer_proc()
end;

部分编译时刻错误检测

待支持的功能：

参数和变量属性

a NOT NULL
b %ROWTYPE类型
c 游标变量

游标与SQL

a 动态游标
b RETURN INTO语句

异常

a 其他系统预定义异常的抛出点

包

a 函数重载

后续功能（短期内不会实现）

1 全面的编译期类型检测，可以检测出变量/函数/语句类型是否匹配（有可能要先拿到MetaData）
2 精确的编译错误报告，指出哪行代码哪一个单词报错

已知问题（基本无法解决，但是可以绕过）

1 PLSQL语句内不能写分号
2 SELECT或SELECT INTO语句后如果跟函数调用（不带EXEC关键字），则SELECT或SELECT INTO语句不能以FROM <TABLE_NAME>结尾，否则函数名会被识别为alias，目前parser中没办法很好的处理这个问题，可以强加AS <ALIAS>绕过，否则会报语义错误
3 目前的SQL中支持使用已经求值的参数/变量/函数/集合元素/记录字段，暂不支持以上元素与表中的列混用