4.4. PLSQL

本节主要介绍统一SQL支持原生Oracle数据库中的PLSQL

4.4.1. 数据类型

描述

PL 中每个常量、变量、参数、函数返回值都有一个数据类型，用来定义存储格式、使用限制、有效值范围以及合法操作等。

分类

SQL 数据类型
布尔型
RECOED
PLS_INTEGER
%TYPE
联合数组
嵌套表
可变数组
自定义子类型
游标指针

4.4.1.1. SQL 数据类型

请参考Oracle2LightDB-Oracle中数据类型：: 数据类型

警告

DECLARE 中变量有如下限制：

CONSTANT 暂不支持
XMLTYPE 暂不支持赋值
INTERVAL YEAR TO MONTH 暂不支持赋值
INTERVAL YEAR(p) TO MONTH 暂不支持赋值
INTERVAL DAY TO SECOND 暂不支持赋值
INTERVAL DAY(p) TO SECOND(s) 暂不支持赋值
TIMESTAMP 暂不支持赋值
TIMESTAMP WITH TIME ZONE 暂不支持赋值
TIMESTAMP(p) WITH TIME ZONE 暂不支持赋值
赋值暂时只支持简单表达式
长度、精度、标度暂时不做范围限制，具体是否错误由执行层保证

示例

-- 转换前Oracle SQL：
DECLARE
   acct_id1 BINARY_FLOAT := 0.0;
   acct_id2 BINARY_DOUBLE := 0.0;
   acct_id3 ROWID := '(0,1)';
   acct_id4 BLOB := EMPTY_BLOB();
   acct_id5 CHAR := 'A';
   acct_id6 CHARACTER := 'A';
   acct_id7 CLOB := EMPTY_CLOB();
   acct_id8 DATE := TO_DATE('2024-03-19', 'YYYY-MM-DD');
   acct_id9 DECIMAL := 0;
   acct_id10 DEC := 0;
   acct_id12 DOUBLE PRECISION := 0.0;
   acct_id13 FLOAT := 0.0;
   acct_id14 INTEGER := 0;
   acct_id15 INT := 0;
   acct_id16 INTERVAL YEAR TO MONTH ;
   acct_id17 INTERVAL YEAR(2) TO MONTH ;
   acct_id18 INTERVAL DAY TO SECOND ;
   acct_id19 INTERVAL DAY(1) TO SECOND(6) ;
   acct_id20 LONG := 'Sample text';
   acct_id21 LONG RAW := utl_raw.cast_to_raw('Sample text');
   acct_id22 NCHAR := 'A';
   acct_id23 NCHAR VARYING(2) := 'AA';
   acct_id24 NCLOB := EMPTY_CLOB();
   acct_id25 NUMBER := 9999;
   acct_id26 NUMERIC := 0;
   acct_id27 NVARCHAR2(2000) := 'Initial value';
   acct_id28 RAW(2000) := hextoraw('0102030405060708');
   acct_id29 REAL := 0.0;
   acct_id31 SMALLINT := 0;
   acct_id32 TIMESTAMP := TO_TIMESTAMP('2024-03-19 12:34:56', 'YYYY-MM-DD HH24:MI:SS');
   acct_id33 TIMESTAMP WITH TIME ZONE ;
   acct_id34 TIMESTAMP(3) WITH TIME ZONE ;
   acct_id35 UROWID := 'AAAACPAABAAAAShAAA';
   acct_id36 VARCHAR(51) := 'NULL';
   acct_id37 VARCHAR2(151) := 'NULL';
   acct_id38 XMLTYPE ;

BEGIN
   acct_id25 := 9999;
END;


-- 转换后LightDB-Oracle SQL：
DECLARE
   acct_id1 binary_float := 0.0;
   acct_id2 binary_double := 0.0;
   acct_id3 rowid := '(0,1)';
   acct_id4 blob := EMPTY_BLOB();
   acct_id5 char := 'A';
   acct_id6 character := 'A';
   acct_id7 clob := EMPTY_CLOB();
   acct_id8 date := TO_DATE('2024-03-19', 'YYYY-MM-DD');
   acct_id9 decimal(38,0) := 0;
   acct_id10 dec(38,0) := 0;
   acct_id12 double precision := 0.0;
   acct_id13 float := 0.0;
   acct_id14 bigint := 0;
   acct_id15 bigint := 0;
   acct_id16 interval year to month;
   acct_id17 interval year to month;
   acct_id18 interval day to second;
   acct_id19 interval day to second(6);
   acct_id20 long := 'Sample text';
   acct_id21 bytea := utl_raw.cast_to_raw('Sample text');
   acct_id22 nchar := 'A';
   acct_id23 nchar varying(2) := 'AA';
   acct_id24 clob := EMPTY_CLOB();
   acct_id25 number := 9999;
   acct_id26 numeric(38,0) := 0;
   acct_id27 nvarchar2(2000) := 'Initial value';
   acct_id28 raw(2000) := hextoraw('0102030405060708');
   acct_id29 real := 0.0;
   acct_id31 bigint := 0;
   acct_id32 timestamp := TO_TIMESTAMP('2024-03-19 12:34:56', 'YYYY-MM-DD HH24:MI:SS');
   acct_id33 timestamp with time zone;
   acct_id34 timestamp(3) with time zone;
   acct_id35 varchar(4000) := 'AAAACPAABAAAAShAAA';
   acct_id36 varchar(51) := 'NULL';
   acct_id37 varchar2(151) := 'NULL';
   acct_id38 xmltype;
BEGIN
   acct_id25 := 9999;
END;

4.4.1.2. 布尔型

语法

variable_name BOOLEAN

示例

-- 转换前Oracle SQL：
DECLARE
   acct_id1 BOOLEAN;
   acct_id2 BOOLEAN := FALSE;
BEGIN
   acct_id1 := true;
   acct_id2 := true;
END;


-- 转换后LightDB-Oracle SQL：
DECLARE
   acct_id1 boolean;
   acct_id2 boolean := FALSE;
BEGIN
   acct_id1 := TRUE;
   acct_id2 := TRUE;
END;

4.4.1.3. RECOED

语法

TYPE record_type IS RECORD ( field_definition [, field_definition]... ) ;

警告

field_definition暂时只支持field datatype

示例

-- 转换前Oracle SQL：
DECLARE
type recordType is record(
   age integer,
   tal integer);
record1 recordType;

BEGIN
record1.age:=1;
END;


-- 转换后LightDB-Oracle SQL：
DECLARE
   TYPE recordType IS RECORD(
      age bigint,
      tal bigint
   );
   record1 recordType;
BEGIN
   record1.age := 1;
END;

4.4.1.4. PLS_INTEGER

语法

variable_name PLS_INTEGER

示例

-- 转换前Oracle SQL：
DECLARE
   acct_id40 pls_integer:=-123;
BEGIN
   acct_id40 := 9999;
END;


-- 转换后LightDB-Oracle SQL：
DECLARE
   acct_id40 pls_integer := -123;
BEGIN
   acct_id40 := 9999;
END;

4.4.1.5. %TYPE

语法

variable_name table_or_view_name.column_name%TYPE;

示例

-- 转换前Oracle SQL：
CREATE TABLE te(a int);
DECLARE
   id te.a%TYPE;
BEGIN
id := 9999;
END;


-- 转换后LightDB-Oracle SQL：
CREATE TABLE te (a bigint);
DECLARE
   id te.a%TYPE;
BEGIN
   id := 9999;
END;

4.4.1.6. 联合数组

语法

TYPE typeName IS TABLE OF datetype INDEX BY { PLS_INTEGER | BINARY_INTEGER| VARCHAR2(size)};

警告

暂时只支持定义解析

示例

-- 转换前Oracle SQL：
DECLARE
   TYPE typeName1 IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
   TYPE typeName2 IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
   TYPE typeName3 IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(10);
   v1 typeName1;
   v2 typeName2;
   v3 typeName3;
   v4 int;
BEGIN
   v4:=999;
END;


-- 转换后LightDB-Oracle SQL：
DECLARE
   TYPE typeName1 IS TABLE OF varchar2(10) INDEX BY pls_integer;
   TYPE typeName2 IS TABLE OF varchar2(10) INDEX BY binary_integer;
   TYPE typeName3 IS TABLE OF varchar2(10) INDEX BY varchar2(10);
   v1 typeName1;
   v2 typeName2;
   v3 typeName3;
   v4 bigint;
BEGIN
   v4 := 999;
END;

4.4.1.7. 嵌套表

语法

TYPE typeName IS TABLE OF datetype [NOT NULL];

警告

暂时只支持定义解析

示例

-- 转换前Oracle SQL：
DECLARE
   TYPE typeName IS TABLE OF VARCHAR2(10);
   v1 typeName;
   v2 int;
BEGIN
   v2:=999;
END;


-- 转换后LightDB-Oracle SQL：
DECLARE
   TYPE typeName IS TABLE OF varchar2(10);
   v1 typeName;
   v2 bigint;
BEGIN
   v2 := 999;
END;

4.4.1.8. 可变数组

语法

TYPE typeName IS VARRAY(size) OF datetype [NOT NULL];

警告

暂时只支持定义解析

示例

-- 转换前Oracle SQL：
DECLARE
   TYPE typeName IS VARRAY(2) OF int;
   v1 typeName;
   v2 int;
BEGIN
   v2:=999;
END;


-- 转换后LightDB-Oracle SQL：
DECLARE
   TYPE typeName IS VARRAY(2) OF bigint;
   v1 typeName;
   v2 bigint;
BEGIN
   v2 := 999;
END;

4.4.1.9. 自定义子类型

警告

暂不支持

4.4.1.10. 游标指针

警告

暂不支持

4.4.2. 存储过程和函数

描述: 存储过程（Procedure）和函数（Function）统称为 PL/SQL 程序，它们被命名为 PL/SQL 块，并通过输入输出参数与调用者交换信息。存储过程和函数的唯一区别，就是函数定义时需要声明一个返回值，函数结束前要返回一个值。

4.4.2.1. 创建存储过程

存储过程是 SQL 语句和可选控制流语句的预编译集合，PL/SQL 将其作为一个单元进行处理。一个存储过程可以引用其它存储过程，并且可以返回多个变量。

语法

CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ] PROCEDURE
[ schema. ] procedure_name
[ ( parameter_declaration [, parameter_declaration ]... ) ]
{ IS | AS } { [ declare_section ] body } ;

parameter_declaration :
   parameter [ [ IN ] datatype [ { := | DEFAULT } expression ]
         | { OUT | IN OUT } datatype  ]

declare_section:
   参照PL/SQL数据类型

body:
BEGIN statement ... END [ name ]

参数解释

参数	说明
[schema. ]procedure_name	Schema 指定当前同义词属于哪个用户。 procedure_name 表示存储过程的名称。
parameter_declaration	存储过程的相关声明
declare_section	PL/SQL块的声明部分，参照PL/SQL数据类型
body	PL/SQL块的执行部分，支持BEGIN、END、IF THEN分支，loop循环，变量，赋值，ROLLBACK，COMMIT，RETURN。

示例

-- 转换前Oracle PL/SQL：
CREATE OR REPLACE procedure unisqltradeholder(market_v in char,holder_v in varchar2, fund_v in number, combi_v number default -1,
   c_default_status_p char default '1', combiname_v varchar2 default '0') is
      combi_p number := -1;
      bindseat_v varchar2(10) := '';
      icnt number := 0;
      icnt1 number := 0;
      icnt2 number := 0;
      l_error_code    int := 0;
   begin
      l_error_code := 1;
      combi_p := combi_v;
      l_error_code := 2;
      if combi_v = -1 then
         l_error_code := 3;
      end if;
      icnt := 0;
      l_error_code := 3;
      if (icnt > 0 ) then
         l_error_code := 4;
      end if;
      commit;
   end unisqltradeholder;

-- 转换后LightDB-Oracle PL/SQL：
CREATE OR REPLACE PROCEDURE unisqltradeholder(market_v IN char,holder_v IN varchar2,fund_v IN number,combi_v number DEFAULT -1,
c_default_status_p char DEFAULT '1',combiname_v varchar2 DEFAULT '0')  IS
   combi_p number := -1;
   bindseat_v varchar2(10) := '';
   icnt number := 0;
   icnt1 number := 0;
   icnt2 number := 0;
   l_error_code int := 0;
BEGIN
   l_error_code := 1;
   combi_p := combi_v;
   l_error_code := 2;
   IF combi_v=-1 THEN l_error_code := 3;
END IF;
   icnt := 0;
   l_error_code := 3;
   IF (icnt>0) THEN l_error_code := 4;
END IF;
   COMMIT;END unisqltradeholder;

4.4.2.2. 创建函数

PL/SQL 函数是由一个或多个 SQL 语句组成的子程序，可重复执行。函数只能返回一个变量，并且必须包含一个有效的 RETURN 语句。

语法

CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ] FUNCTION
[ schema. ] function_name
[ ( parameter_declaration [, parameter_declaration ]... ) ] RETURN datatype
{ IS | AS } { [ declare_section ] body } ;

parameter_declaration :
   parameter [ [ IN ] datatype [ { := | DEFAULT } expression ]
         | { OUT | IN OUT } datatype  ]

declare_section:
   参照PL/SQL数据类型

body:
BEGIN statement ... END [ name ]

参数解释

参数	说明
[schema. ]function_name	Schema 指定当前同义词属于哪个用户。 function_name 表示函数的名称。
parameter_declaration	函数的相关声明
declare_section	PL/SQL块的声明部分，参照PL/SQL数据类型
body	PL/SQL块的执行部分，支持BEGIN、END、IF THEN分支，loop循环，变量，赋值（:=），ROLLBACK，COMMIT，RETURN。

示例

-- 转换前Oracle PL/SQL：
CREATE OR REPLACE FUNCTION return_input(
 input_value IN NUMBER
) RETURN NUMBER IS
   sum_result NUMBER := 0;
BEGIN
   FOR i IN 0 .. input_value LOOP
      sum_result := sum_result + i;
   END LOOP;
   RETURN sum_result;
END;

-- 转换后LightDB-Oracle PL/SQL：
CREATE OR REPLACE FUNCTION return_input(input_value IN number) RETURN number IS
   sum_result number := 0;
BEGIN
   FOR i IN 0 .. input_value LOOP sum_result := sum_result+i;
END  LOOP;
   RETURN sum_result;
END;

警告

下面列举的是在使用统一SQL的PL/SQL时的部分疑问点

Oracle的PL/SQL和LightDB PL/SQL存在一定的差异，本版本未强制转化，以Oracle的PL/SQL为输出，后续LightDB版本支持之后，会逐步转化迭代。
PL/SQL块的执行部分，支持BEGIN、END、IF THEN分支，loop循环，变量，赋值（:=），ROLLBACK，COMMIT，RETURN，但存在限制，loop循环的循环变量上、下界前后需要添加空格,仅支持简单默认的循环,不支持REVERSE。
PL/SQL语法是否正确不考虑，具体是否错误由执行层保证。
支持范围仅考虑数据类型，本章节目的仅在展示支持PL/SQL的创建存储过程和函数的框架功能。