LightDB-PL/SQL介绍_lightdb plorasql-CSDN博客

PL/oraSQL是LightDB为了兼容Oracle的PL/SQL引入的过程语言，支持匿名块、变量声明与赋值、条件语句（IF/CASE/循环）、游标、错误处理和函数、过程。它允许嵌套代码块，声明常量，以及处理游标和集合，如关联数组、嵌套表和可变数组。此外，PL/oraSQL还支持自治事务，允许子程序独立于主事务进行提交和回滚。

PL/OraSQL是LightDB数据库新增的一种过程语言，用于兼容Oracle的PL/SQL。实际跟PL/pgSQL类似。

下面简单对plorasql进行介绍

1. 匿名块支持

PL/oraSQL 是一种块状语言，因此存储过程和函数以代码块的形式进行组织。以下是一个 PL/oraSQL 代码块的定义：

[ DECLARE 
    declarations ] 
BEGIN 
    statements 
[ EXCEPTION 
    Exception-handling part ] 
END; 
/

代码块又称匿名块；Oracle语法的匿名块是PL/oraSQL的重要特性。 DECLARE 是一个可选的声明部分，用于定义变量； BEGIN 和 END 之间是代码主体，也就是主要的功能代码；所有的语句都使用分号（;）结束；其中’/’为ltsql下的匿名块结束符，在java客户端则无需输入。

以下是一个简单的代码块示例：

DECLARE
    myname text;
BEGIN
    myname := 'PL/oraSQL';
    DBMS_OUTPUT.PUT_LINE('Hello ' || myname || '.');
END;
/

以上是一个匿名块，与此相对的是命名块（也就是存储过程和函数）；我们定义了一个字符串变量 myname，然后给它赋值并输出一个信息；DBMS_OUTPUT.PUT_LINE用于输出通知消息。

PL/oraSQL 支持代码块的嵌套，也就是将一个代码块嵌入其他代码块的主体中。被嵌套的代码块被称为子块（subblock），包含子块的代码块被称为外部块（outerblock）。子块可以将代码进行逻辑上的拆分，子块中可以定义与外部块重名的变量，而且在子块内拥有更高的优先级。

例如：

DECLARE
    myname varchar2(64);
BEGIN
    myname := 'outer_block';
    DBMS_OUTPUT.PUT_LINE('This is ' || myname);
    DECLARE
         myname varchar2(64) := 'sub_block';      
    BEGIN         
        DBMS_OUTPUT.PUT_LINE('This is ' || myname);  
    END;        
    DBMS_OUTPUT.PUT_LINE('This is ' || myname);
END;
/

首先，外部块中定义了一个变量 myname，值为“outer_block”，输出该变量的值；然后在子块中定义了同名的变量，值为“sub_block”，输出该变量的值;最后再次输出该变量的值。

2. 声明与赋值

1. 变量

变量是一个有意义的名字，代表了内存中的某个位置。变量总是属于某个数据类型，变量的值可以在运行时被修改。在使用变量之前，需要在代码的声明部分进行声明：

name [ CONSTANT ] type [ NOT NULL ] { DEFAULT | := } expression;

以下是一些变量声明的示例：

user_id integer; 
quantity numeric(5) default 0;
url varchar(64) := ‘https://www.hs.net/lightdb’;

除了基本的 SQL 数据类型之外，PL/pgSQL 还支持基于表的字段或行或者其他变量定义变量：

myrow tablename%ROWTYPE; 
myfield tablename.columnname%TYPE; 
amount quantity%TYPE;

myrow是一个行类型的变量，可以存储查询语句返回的数据行（数据行的结构要和tablename相同）；myfield的数据类型取决于tablename.columnname字段的定义；amount和quantity的类型一致。

与行类型变量类似的还有记录类型变量，例如：

arow RECORD;

记录类型的变量没有预定义的结构，只有当变量被赋值时才确定，而且可以在运行时被改变。记录类型的变量可以用于任意查询语句或者FOR循环变量。

除此之外，PL/pgSQL 还可以使用 ALIAS 定义一个变量别名：

newname ALIAS FOR oldname;

此时，newname 和 oldname 代表了相同的对象。

2. 常量

如果在定义变量时指定了CONSTANT关键字，意味着定义的是常量。常量的值需要在声明时初始化，并且不能修改。

以下示例通过定义常量 PI 计算圆的面积：

DECLARE
    PI CONSTANT NUMERIC := 3.14159265;
    radius NUMERIC;
BEGIN
    radius := 1.0;
    DBMS_OUTPUT.PUT_LINE('The area is ' || PI * radius * radius);
END;
/

常量可以用于避免魔数（magic number），提高代码的可读性；也可以减少代码的维护工作，所有使用常量的代码都会随着常量值的修改而同步，不需要修改多个硬编码的数据值。

3. if/case/循环语句

1. if语句

IF 语句可以基于条件选择性执行操作，PL/oraSQL提供了三种形式的IF语句。

IF ... THEN ... END IF
IF ... THEN ... ELSE ... END IF
IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF

2. case语句

CASE 语句分为两种：简单CASE语句和搜索CASE语句。

简单 CASE 语句的结构如下：

CASE search-expression 
    WHEN expression [, expression [ ... ]] THEN 
        statements 
  [ WHEN expression [, expression [ ... ]] THEN 
        statements ... ] 
  [ ELSE 
        statements ] 
END CASE;

简单 CASE 语句只能进行简单的等值比较，搜索 CASE 语句可以实现更复杂的控制逻辑：

CASE 
    WHEN boolean-expression THEN 
        statements 
  [ WHEN boolean-expression THEN 
        statements ... ] 
  [ ELSE 
        statements ] 
END CASE;

3. 循环语句

LightDB提供4种循环执行命令的语句：LOOP、WHILE、FOR和FOREACH循环，以及循环控制的EXIT和CONTINUE语句。

首先，LOOP 用于定义一个无限循环语句：

[ <<label>> ] LOOP statements END LOOP [ label ];

一般需要使用EXIT或者RETURN语句退出循环，label可以用于EXIT或者CONTINUE语句退出或者跳到执行的嵌套循环中。语法如下：

EXIT [ label ] [ WHEN boolean-expression ]; 
CONTINUE [ label ] [ WHEN boolean-expression ];

WHILE 循环的语法如下：

[ <<label>> ]
WHILE boolean-expression LOOP 
    statements 
END LOOP [ label ];

FOR循环可以用于遍历一个整数范围或者查询结果集，遍历整数范围的语法如下：

[ <<label>> ] 
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP 
    statements 
END LOOP [ label ];

FOR循环默认从小到大进行遍历，REVERSE表示从大到小遍历；BY用于指定每次的增量，默认为1。 FOREACH循环与FOR循环类似，只不过变量的是一个数组。这里不进行介绍。

4. 游标

PL/oraSQL游标允许我们封装一个查询，然后每次处理结果集中的一条记录。游标可以将大结果集拆分成许多小的记录，避免内存溢出；另外，我们可以定义一个返回游标引用的函数，然后调用程序可以基于这个引用处理返回的结果集。

使用游标的步骤大体如下：

1) 声明游标变量；

2) 打开游标；

3) 从游标中获取结果；

4) 判断是否存在更多结果。如果存在，执行第3步；否则，执行第5步；

5) 关闭游标。

1. 隐式游标

主要做了4处兼容：

1）SQL%ISOPEN

2）SQL%FOUND

3）SQL%NOTFOUND

4）SQL%ROWCOUNT

其中SQL%ISOPEN总是返回FALSE，因为隐式游标总是在其关联语句运行后关闭。

SQL%FOUND返回值：

1) 当没执行Select语句或者DML语句时，返回null;

2) 当执行过Select语句或者DML语句时，返回true;

3) 其它语句，返回false；

create table t100(a int);
BEGIN
    insert into t100 values (1);
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('FOUND');
    ELSE
        DBMS_OUTPUT.PUT_LINE('NO FOUND');
    end if;
END;
/

SQL%NOTFOUND返回值：

1) 当没执行Select语句或者DML语句时，返回null;

2) 当执行过Select语句或者DML语句时，返回false;

create table t100(a int);
BEGIN
    insert into t100 values (1);
    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('NO NOTFOUND');
    ELSE
        DBMS_OUTPUT.PUT_LINE('NOTFOUND');
    END IF;
END;
/

SQL%ROWCOUNT返回值：

1) 当没执行Select语句或者DML语句时，返回null;

2) 当执行过Select语句或者DML语句时，返回查询或者操作的行数；

注意：只是记录最近一次操作的行数。示例ROWCOUNT值为1。

BEGIN
    insert into t100 values (1);
    insert into t100 values (2);
    insert into t100 values (3);
    DBMS_OUTPUT.PUT_LINE('ROWCOUNT: ' || SQL%ROWCOUNT);
END;
/

2. 显示游标

显示游标跟PL/pgSQL中的游标一致。我们直接通过一个示例演示使用游标的过程:

DECLARE
	rec_emp RECORD;
	cur_emp CURSOR(p_deptid INTEGER) FOR
	SELECT first_name,last_name,hire_date FROM employees WHERE department_id = p_deptid;
BEGIN
-- 打开游标
	OPEN cur_emp(60);
	
	LOOP
-- 获取游标中的记录
		FETCH cur_emp INTO rec_emp;
		EXIT WHEN NOT FOUND;
		DBMS_OUTPUT.PUT_LINE(rec_emp.first_name);
		DBMS_OUTPUT.PUT_LINE(rec_emp.last_name);
		DBMS_OUTPUT.PUT_LINE(hire_date.last_name);
	END LOOP;
-- 关闭游标
	CLOSE cur_emp;
END;
/

首先，声明了一个游标 cur_emp，并且绑定了一个查询语句，通过一个参数p_deptid获取指定部门的员工；然后使用 OPEN 打开游标；接着在循环中使用FETCH语句获取游标中的记录，如果没有找到更多数据退出循环语句；变量rec_emp用于存储游标中的记录；最后使用CLOSE语句关闭游标，释放资源。

5. 错误处理

1. 报告错误和信息

PL/oraSQL提供了RAISE语句，用于抛出异常。

PL/oraSQL也提供了Oracle打印语法DBMS_OUTPUT.PUT_LINE。

2. 捕获异常

默认情况下，PL/pgSQL 遇到错误时会终止代码执行，同时撤销事务。我们也可以在代码块中使用 EXCEPTION 捕获错误并继续事务：

EXCEPTION
  WHEN ex_name_1 THEN 
    statements_1 -- Exception handler 
  WHEN ex_name_2 OR ex_name_3 THEN 
    statements_2 -- Exception handler 
  WHEN OTHERS THEN 
    statements_3 -- Exception handler 
END;

3. 支持Oracle sqlcode语法

因为错误码仅做展示以及定位问题使用，所以在LightDB中执行后就不能用Oracle的内置错误码，应该使用LightDB内置的错误码来代替。在LightDB中，我们可以使用sqlstate来代替sqlcode，这样问题又出现了，LightDB中的sqlstate是一个5位长度的字符串(从00000~ZZZZZ)，而Oracle的sqlcode是整型的，所以在类型上也不兼容，还是得需要业务改代码，所以针对这种情况，LightDB在23.2版本开始，要完全支持sqlcode。效果如下：

DECLARE
	a varchar2(32);
	b int;
BEGIN
	raise;
EXCEPTION
	WHEN OTHERS THEN
		a := sqlstate;
		b := sqlcode;
		DBMS_OUTPUT.PUT_LINE(sqlstate);
		DBMS_OUTPUT.PUT_LINE(sqlcode);
		DBMS_OUTPUT.PUT_LINE(sqlcode_to_sqlstate(b));
END;
/

其中sqlstate为LightDB原生函数，sqlcode为sqlstate的转码前状态，这样问题也来了，用户拿到sqlcode为16777248时，并不知道这是什么意思，所以我们也提供了转换函数sqlcode_to_sqlstate。

Class P0 — PL/pgSQL Error
P0000	plpgsql_error
P0001	raise_exception
P0002	no_data_found
P0003	too_many_rows
P0004	assert_failure
P0005	forall_need_dml

错误码参考地址：http://www.light-pg.com/docs/lightdb/current/errcodes-appendix.html

4. 支持自定义异常

示例：

DECLARE
exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT(exception_name,error_code);
BEGIN
...
RAISE exception_name;
...
EXCEPTION WHEN exception_name THEN
...
END;
/
-- error_code取值范围为正100,大于-100000且不等于-1403的负数

6. 函数

要创建一个自定义的 PL/oraSQL 函数，可以使用 CREATE FUNCTION 语句：

CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ]
  FUNCTION plsql_function_source
[ schema. ] function_name
  [ ( parameter_declaration [, parameter_declaration]... ) ] RETURN datatype 
[ sharing_clause ]
  [ { invoker_rights_clause
    | accessible_by_clause
    | default_collation_clause    
    | deterministic_clause
    | parallel_enable_clause
    | result_cache_clause 
    | aggregate_clause
    | pipelined_clause
    | sql_macro_clause
       }...
  ]
{ IS | AS } { [ declare_section ] body 
              | call_spec
            }
  ;

CREATE表示创建函数，OR REPLACE表示替换函数定义；function_name是函数名；括号内是参数，多个参数使用逗号分隔；argmode 可以是 IN（输入）、OUT（输出）、INOUT（输入输出）或者VARIADIC（数量可变），默认为IN；argname 是参数名称；argtype是参数的类型；default_expr 是参数的默认值；rettype是返回数据的类型；AS后面是函数的定义，和上文中的匿名块相同；

函数的调用方法：

1) select func_name(params...);

2) select * from func_name(params...);

大多数情况下我们用1和3即可。那有什么区别呢？

1. 在非匿名块下，只能用1和2。

函数原型如下：

create or replace function func_test2(a int) return void as
	p1 int;
begin
	p1 := a;
	DBMS_OUTPUT.PUT_LINE(p1);
end;
/

select func_test2(1);
select * from func_test2(1);

非匿名块下，可以用select直接调用函数，不能用perform调用函数。

不能用pg语法perform来调用函数。

7. 过程

使用CREATE PROCEDURE语句创建：

CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ]
   PROCEDURE plsql_procedure_source
[ schema. ] procedure_name
   [ ( parameter_declaration [, parameter_declaration ]... ) ] [ sharing_clause ]
   [ ( default_collation_option | invoker_rights_clause | accessible_by_clause)... ] 
   { IS | AS } { [ declare_section ] body | call_spec } ;

create or replace procedure proc_test(a int) as
	p1 int;
begin
	p1 := a;
	DBMS_OUTPUT.PUT_LINE(p1);
end;
/

过程的调用方法：

call proc_name(params...);

call proc_test(100);

begin
    call proc_test(100);
end;
/

不管在不在匿名块中，都可以直接用call直接调用procedure。

函数和过程的区别：

1）有无返回值；

2）自动提交属性；

3）调用方式；

函数有返回值(void也算)，过程无返回值；

函数中的语句是自动提交的，执行一条提交一条，过程执行到最后才提交。

函数用select调用，过程用call调用。

这里需要说明的是，目前函数支持commit和rollback

8. 包

针对Oracle中的package，PL/oraSQL也对应的支持包，包是PL/oraSQL的关键特性。

包的构成：包有两部分构成：包规格和包体(即Package specification and Package body)。其中包规格中用来定义变量/自定义类型/游标/集合/函数声明/过程声明：

FUNCTIONS
PROCEDURES
CURSORS
TYPES
VARIABLES
RECORD TYPES
ASSOCIATIVE ARRAYS
NESTES TABLES
SUBTYPES

包体将包规格中的函数与过程实现。当包规格中没定义函数/过程等需要实现的子程序，这时包体是不需要定义的。包体可以定义私有变量，定义的私有变量，在包体外是不可见的。

CREATE [ OR REPLACE ] PACKAGE [schema.]package_name  [IS | AS] 
   item_list[, item_list ...]
END [package_name];
 

 
item_list: 
[
   function_declaration    | 
   procedure_declaration   | 
   type_definition         | 
   cursor_declaration      | 
   item_declaration
]
 
 
function_declaration:
     FUNCTION function_name [(parameter_declaration[, ...])] RETURN datatype;
 
procedure_declaration:
     PROCEDURE procedure_name [(parameter_declaration[, ...])]
 
type_definition:
     record_type_definition      |
     ref_cursor_type_definition
 
cursor_declaration:
   CURSOR name [(cur_param_decl[, ...])] RETURN rowtype;
 
item_declaration:
     cursor_declaration             |
     cursor_variable_declaration    |
     record_variable_declaration    |
     variable_declaration           |
 
record_type_definition:
   TYPE record_type IS RECORD  ( variable_declaration [, variable_declaration]... ) ;
 
ref_cursor_type_definition:
   TYPE type IS REF CURSOR [ RETURN type%ROWTYPE ];
 
cursor_variable_declaration:
   curvar curtype;
 
record_variable_declaration:
   recvar { record_type | rowtype_attribute | record_type%TYPE };
 
variable_declaration:
   varname datatype [ [ NOT NULL ] := expr ]
 
parameter_declaration:
   parameter_name [IN] datatype [[:= | DEFAULT] expr]

9. 集合

针对Oracle中的集合，PL/oraSQL也做了对应的支持，集合也是PL/oraSQL的关键特性。针对集合，PL/oraSQL做了以下适配：

1. Associative Arrays(关联数组)

DECLARE
  -- Associative array indexed by integer:
  TYPE population IS TABLE OF varchar  -- Associative array type
    INDEX BY integer;                  -- indexed by integer

  city_population  population;        -- Associative array variable
  i                integer;
BEGIN
  -- Add elements (key-value pairs) to associative array:
  city_population('2')  := 'Smallville';
  city_population('5') := 'Midland';
  city_population('3')  := 'Megalopolis';

  FOR i IN 1..6 LOOP
      IF city_population.EXISTS(i) THEN
          dbms_output.put_line ('city_population(' || i || ')' || ' exists');
      ELSE
          dbms_output.put_line ('city_population(' || i || ')' || ' does not exist');
      END IF;
  END LOOP;

END;
/

2. Nested Tables(嵌套表)

DECLARE
TYPE Roster IS TABLE OF VARCHAR(15);  -- nested table type

-- nested table variable initialized with constructor: 
names2 Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');

BEGIN
    DBMS_OUTPUT.PUT_LINE('Initial Values:');
    FOR i IN names2.FIRST .. names2.LAST LOOP  -- For first to last element
      DBMS_OUTPUT.PUT_LINE(names2(i));
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('------------------');
    names2(5) := 'P Perez';  -- add one element
    FOR i IN names2.FIRST .. names2.LAST LOOP  -- For first to last element
      DBMS_OUTPUT.PUT_LINE(names2(i));
    END LOOP;
END;
/

3. Varrays(可变数组)

DECLARE
    TYPE Roster IS VARRAY(10) OF VARCHAR(15);  -- varray type

    -- varray variable initialized with constructor:
    names2 Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');

BEGIN
    DBMS_OUTPUT.PUT_LINE('Initial Values:');
    FOR i IN names2.FIRST .. names2.LAST LOOP  -- For first to last element
        DBMS_OUTPUT.PUT_LINE(names2(i));
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('------------------');
    names2.extend;
    names2(5) := 'P Perez';  -- add one element
    FOR i IN names2.FIRST .. names2.LAST LOOP  -- For first to last element
        DBMS_OUTPUT.PUT_LINE(names2(i));
    END LOOP;
END;
/

集合方法：

方法	描述
DELETE	从集合中删除元素。
EXTEND	将元素添加到嵌套表的末尾。
EXISTS	如果嵌套表的指定元素存在，则返回TRUE。
FIRST	返回集合中的第一个索引。
LAST	返回集合中的最后一个索引。
COUNT	返回集合中的元素数量。

10. 自治事务

AUTONOMOUS_TRANSACTION语法改变事务中子程序的执行方式。通过此语法，子程序的提交，撤销可以独立于主事务。

语法：

PRAGMA AUTONOMOUS_TRANSACTION;

示例：

CREATE OR REPLACE PROCEDURE lower_salary (emp_id NUMBER, amount NUMBER) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE employees
  SET salary =  salary - amount
  WHERE employee_id = emp_id;

  COMMIT;
END lower_salary;
/

CREATE OR REPLACE FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO debug_output (message) VALUES (msg);
  COMMIT;
  RETURN msg;
END;
/

DECLARE
  my_emp_id    NUMBER(6);
  my_last_name VARCHAR2(25);
  my_count     NUMBER;
BEGIN
  my_emp_id := 120;
 
  SELECT debugging.log_msg(last_name)
  INTO my_last_name
  FROM employees
  WHERE employee_id = my_emp_id;
 
  /* 即使回滚debug_output表仍然被插入数据 */
  ROLLBACK;
END;
/