Inceptor支持部分PL/SQL。使用方法参照Oracle文档
a int := 11 b string := 'b1.1'
BEGIN put_line( "Hello" ); END;
create or replace procedure test(arg int) is
begin
put_line('hello world ' || arg)
end;
begin
test(5)
end;
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
declare cursor c is select * from t1 as t
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;
declare var int begin var := 1 <> put_line("Label print hit on var = " || var || ".") if var < 5 then var := var + 1 goto print end if end;
declare var int begin var := 1 <> loop put_line("Simple loop body " || var || ".") var := var + 1 exit simple_loop when var = 5 end loop simple_loop end;
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;
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
put_line("Forall 3...")
forall forall_v in indices of cr_v
select * from t1 where id = cr_v(forall_v).id and value = cr_v(forall_v).value
put_line("Forall 4...")
forall forall_v in indices of cr_v between -1 and 1
select * from t1 where id = cr_v(forall_v).id and value = cr_v(forall_v).value
put_line("Forall 5...")
forall forall_v in indices of cr_v between 1 and 3
select * from t1 where id = cr_v(forall_v).id and value = cr_v(forall_v).value
put_line("Forall 6...")
forall forall_v in indices of cr_v between 1 and 2
select * from t1 where id = cr_v(forall_v).id and value = cr_v(forall_v).value
end;
declare
var int
begin
var := 1
while var != 5
loop
put_line("While loop body " || var || ".")
var := var + 1
end loop
end;
declare var1 int var2 int begin var1 := 1 <> loop var2 := 1 put_line("Outer loop body " || var1) var1 := var1 + 1 < > 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;
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;
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)
<>
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
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;
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
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
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;