LightDB 22.3新特性兼容Oracle存储过程

创建测试数据

create table PRODUCTS (PRODUCT_ID NUMBER,PRICE NUMBER,PRODUCT_TYPE_ID NUMBER);
insert into PRODUCTS values(1,10000,8);
insert into PRODUCTS values(2,1000,9);
insert into PRODUCTS values(3,100,8);
insert into PRODUCTS values(4,20000,9);
insert into PRODUCTS values(5,2000,8);
insert into PRODUCTS values(6,200,9);
insert into PRODUCTS values(7,2,8);
insert into PRODUCTS values(8,100000,9);
insert into PRODUCTS values(9,100,10);

Oracle：

SQL> CREATE OR REPLACE FUNCTION GET_AVG_PRICE(P_TYPE_ID IN PRODUCTS.PRODUCT_TYPE_ID%TYPE)
  2    RETURN PRODUCTS.PRICE%TYPE IS
  3    V_AVG_PRICE PRODUCTS.PRICE%TYPE;
  4  BEGIN
  5    SELECT AVG(PRICE)
  6      INTO V_AVG_PRICE
  7      FROM PRODUCTS
  8     WHERE PRODUCT_TYPE_ID = P_TYPE_ID
  9     GROUP BY PRODUCT_TYPE_ID;
 10    RETURN V_AVG_PRICE;
 11  END GET_AVG_PRICE;
 12  /
Function created.

SQL> SELECT get_avg_price(9) FROM dual;
GET_AVG_PRICE(9)
----------------
           30300
SQL> SELECT get_avg_price(10) from dual;
GET_AVG_PRICE(10)
-----------------
              100

LightDB

lightdb@postgres=# CREATE OR REPLACE FUNCTION GET_AVG_PRICE(P_TYPE_ID IN PRODUCTS.PRODUCT_TYPE_ID%TYPE)
lightdb@postgres-#   RETURN PRODUCTS.PRICE%TYPE IS
lightdb@postgres$#   V_AVG_PRICE PRODUCTS.PRICE%TYPE;
lightdb@postgres$# BEGIN
lightdb@postgres$#   SELECT AVG(PRICE)
lightdb@postgres$#     INTO V_AVG_PRICE
lightdb@postgres$#     FROM PRODUCTS
lightdb@postgres$#    WHERE PRODUCT_TYPE_ID = P_TYPE_ID
lightdb@postgres$#    GROUP BY PRODUCT_TYPE_ID;
lightdb@postgres$#   RETURN V_AVG_PRICE;
lightdb@postgres$# END GET_AVG_PRICE;
lightdb@postgres$# /
NOTICE:  type reference products.product_type_id%TYPE converted to numeric
NOTICE:  type reference products.product_type_id%TYPE converted to numeric
NOTICE:  type reference products.price%TYPE converted to numeric
NOTICE:  type reference products.product_type_id%TYPE converted to numeric
CREATE FUNCTION
lightdb@postgres=# SELECT get_avg_price(9) FROM dual;
   get_avg_price    
--------------------
 30300.000000000000
(1 row)
lightdb@postgres=# SELECT get_avg_price(10) FROM dual;
    get_avg_price     
----------------------
 100.0000000000000000
(1 row)

下面测试下存储过程包的情况

CREATE OR REPLACE PACKAGE PRODUCT_PACKAGE 
IS
  TYPE T_REF_CURSOR IS REF CURSOR;
  FUNCTION GET_PRODUCTS_REF_CURSOR RETURN T_REF_CURSOR;
  PROCEDURE UPDATE_PRODUCT_PRICE(P_PRODUCT_ID IN PRODUCTS.PRODUCT_ID%TYPE,P_FACTOR IN NUMBER);
END PRODUCT_PACKAGE;
/*包体*/
CREATE OR REPLACE PACKAGE BODY PRODUCT_PACKAGE
 IS
  FUNCTION GET_PRODUCTS_REF_CURSOR RETURN T_REF_CURSOR IS
    V_PRODUCTS_REF_CURSOR T_REF_CURSOR;
  BEGIN
    OPEN V_PRODUCTS_REF_CURSOR FOR
      SELECT PRODUCT_ID, PRICE FROM PRODUCTS;
    RETURN V_PRODUCTS_REF_CURSOR;
  END GET_PRODUCTS_REF_CURSOR;
  PROCEDURE UPDATE_PRODUCT_PRICE(P_PRODUCT_ID IN PRODUCTS.PRODUCT_ID%TYPE,
                                 P_FACTOR     IN NUMBER) IS
    V_PRODUCT_COUNT NUMBER;
  BEGIN
    SELECT COUNT(*)
      INTO V_PRODUCT_COUNT
      FROM PRODUCTS
     WHERE PRODUCT_ID = P_PRODUCT_ID;
    IF V_PRODUCT_COUNT = 1 THEN
      UPDATE PRODUCTS
         SET PRICE = PRICE * P_FACTOR
       WHERE PRODUCT_ID = P_PRODUCT_ID;
      COMMIT;
    END IF;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      ROLLBACK;
  END UPDATE_PRODUCT_PRICE;
END PRODUCT_PACKAGE;  
================================================================
SQL> CREATE OR REPLACE PACKAGE PRODUCT_PACKAGE 
  2  IS
  3    TYPE T_REF_CURSOR IS REF CURSOR;
  4    FUNCTION GET_PRODUCTS_REF_CURSOR RETURN T_REF_CURSOR;
  5    PROCEDURE UPDATE_PRODUCT_PRICE(P_PRODUCT_ID IN PRODUCTS.PRODUCT_ID%TYPE,P_FACTOR IN NUMBER);
  6  END PRODUCT_PACKAGE;
  7  /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY PRODUCT_PACKAGE
 IS
  2    3    FUNCTION GET_PRODUCTS_REF_CURSOR RETURN T_REF_CURSOR IS
  4      V_PRODUCTS_REF_CURSOR T_REF_CURSOR;
  5    BEGIN
  6      OPEN V_PRODUCTS_REF_CURSOR FOR
  7        SELECT PRODUCT_ID, PRICE FROM PRODUCTS;
  8      RETURN V_PRODUCTS_REF_CURSOR;
  9    END GET_PRODUCTS_REF_CURSOR;
 10    PROCEDURE UPDATE_PRODUCT_PRICE(P_PRODUCT_ID IN PRODUCTS.PRODUCT_ID%TYPE,
 11                                   P_FACTOR     IN NUMBER) IS
 12      V_PRODUCT_COUNT NUMBER;
  BEGIN
 13   14      SELECT COUNT(*)
 15        INTO V_PRODUCT_COUNT
 16        FROM PRODUCTS
 17       WHERE PRODUCT_ID = P_PRODUCT_ID;
 18      IF V_PRODUCT_COUNT = 1 THEN
 19        UPDATE PRODUCTS
 20           SET PRICE = PRICE * P_FACTOR
 21         WHERE PRODUCT_ID = P_PRODUCT_ID;
 22        COMMIT;
 23      END IF;
  EXCEPTION
 24   25      WHEN NO_DATA_FOUND THEN
 26        ROLLBACK;
 27  
 28    END UPDATE_PRODUCT_PRICE;
 29  END PRODUCT_PACKAGE;  
 30  /
Package body created.
SQL> select PRODUCT_PACKAGE.GET_PRODUCTS_REF_CURSOR from dual;
GET_PRODUCTS_REF_CUR
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
PRODUCT_ID      PRICE
---------- ----------
         1      10000
         2       1000
         3        100
         4      20000
         5       2000
         6        200
         7          2
         8     100000
         9        100
9 rows selected.
================================================================
lightdb@postgres=# CREATE OR REPLACE PACKAGE PRODUCT_PACKAGE 
lightdb@postgres-# IS
lightdb@postgres$#   TYPE T_REF_CURSOR IS REF CURSOR;
lightdb@postgres$#   FUNCTION GET_PRODUCTS_REF_CURSOR RETURN T_REF_CURSOR;
lightdb@postgres$#   PROCEDURE UPDATE_PRODUCT_PRICE(P_PRODUCT_ID IN PRODUCTS.PRODUCT_ID%TYPE,P_FACTOR IN NUMBER);
lightdb@postgres$# END PRODUCT_PACKAGE;
lightdb@postgres$# /
NOTICE:  type reference products.product_id%TYPE converted to numeric
CREATE PACKAGE
lightdb@postgres=# /*包体*/
lightdb@postgres-# CREATE OR REPLACE PACKAGE BODY PRODUCT_PACKAGE
lightdb@postgres-#  IS
lightdb@postgres$#   FUNCTION GET_PRODUCTS_REF_CURSOR RETURN T_REF_CURSOR IS
lightdb@postgres$#     V_PRODUCTS_REF_CURSOR T_REF_CURSOR;
lightdb@postgres$#   BEGIN
lightdb@postgres$#     OPEN V_PRODUCTS_REF_CURSOR FOR
lightdb@postgres$#       SELECT PRODUCT_ID, PRICE FROM PRODUCTS;
lightdb@postgres$#     RETURN V_PRODUCTS_REF_CURSOR;
lightdb@postgres$#   END GET_PRODUCTS_REF_CURSOR;
lightdb@postgres$#   PROCEDURE UPDATE_PRODUCT_PRICE(P_PRODUCT_ID IN PRODUCTS.PRODUCT_ID%TYPE,
lightdb@postgres$#                                  P_FACTOR     IN NUMBER) IS
lightdb@postgres$#     V_PRODUCT_COUNT NUMBER;
lightdb@postgres$#   BEGIN
lightdb@postgres$#     SELECT COUNT(*)
lightdb@postgres$#       INTO V_PRODUCT_COUNT
lightdb@postgres$#       FROM PRODUCTS
lightdb@postgres$#      WHERE PRODUCT_ID = P_PRODUCT_ID;
lightdb@postgres$#     IF V_PRODUCT_COUNT = 1 THEN
lightdb@postgres$#       UPDATE PRODUCTS
lightdb@postgres$#          SET PRICE = PRICE * P_FACTOR
lightdb@postgres$#        WHERE PRODUCT_ID = P_PRODUCT_ID;
lightdb@postgres$#       COMMIT;
lightdb@postgres$#     END IF;
lightdb@postgres$#   EXCEPTION
lightdb@postgres$#     WHEN NO_DATA_FOUND THEN
lightdb@postgres$#       ROLLBACK;
lightdb@postgres$# 
lightdb@postgres$#   END UPDATE_PRODUCT_PRICE;
lightdb@postgres$# END PRODUCT_PACKAGE;  
lightdb@postgres$# /
NOTICE:  type reference products.product_id%TYPE converted to numeric
CREATE PACKAGE BODY
lightdb@postgres=# select PRODUCT_PACKAGE.GET_PRODUCTS_REF_CURSOR from dual;
 get_products_ref_cursor 
-------------------------
 <unnamed portal 1>
(1 row)

两者返回的都是隐式游标名，只是两者对于隐式游标的命名方式不同，查询结果不同，使用内容一致。