联 系 我 们
售前咨询
售后咨询
微信关注:星环科技服务号
更多联系方式 >
6.8.6.13 系统预定义函数/过程
更新时间:10/24/2023, 7:20:26 AM

ArgoDB 内置了一些预定义的函数和存储过程,帮助您快速调用以实现业务需求。

查看预定义函数/过程
相关命令合集
  • 查看已有函数/过程

    SHOW PLSQL FUNCTIONS db_name;
    复制

    如果不指定数据库名称(db_name),即对当前数据库操作。

  • 查看某一 PL/SQL 函数/过程的详细信息

    DESC PLSQL FUNCTION EXTENDED function_name
    复制

    EXTENDED 关键字会列出该 PL/SQL 函数/过程的原文

  • 创建函数/过程

    CREATE (OR REPLACE) FUNCTION/PROCEDURE
    复制
  • 删除函数/过程/包/包体

    DROP PLSQL FUNCTION/PROCEDURE
    复制
  • 查看正在运行的PLSQL程序的SESSION ID

    PS PLSQL
    复制

    这条命令会显示 SESSION ID 和 PLSQL 语句,使用者需要从中找到自己想要终止的 PLSQL 的 SESSION ID。

  • 终止正在运行的 PLSQL 程序

    KILL PLSQL <SESSION_ID>
    复制
    • 由于 PLSQL 是运行在 ArgoDB Server 端的 SESSION 中,如果 PLSQL 语句正在运行时,客户端通过 Ctrl+C 结束执行,此时 Server 端由于还在执行 PLSQL,无法侦听到客户端的结束请求,该 PLSQL 会继续执行到结束。如果它包含了大量的 SQL 语句执行或者存在死循环,会耗费大量的服务端资源。通过该命令可发送一个终止信号给该 SESSION ID 中的 PLSQL 进程,待该进程下一次自检之时,发现有终止信号,则会结束自己。

    • 自检是借鉴了操作系统中进程调度的思想。自检的粒度为一条 PLSQL 语句,也就是说每执行一条 PLSQL 语句会自检一次(也就是被 OS 调度一次)。所以如果有一条 SQL 语句执行时间特别长,那么 KILL 该进程后并不会马上生效,需要等到该 SQL 语句执行完毕才会发生自检并终止。这种情况如果确定要终止该进程,可到 Spark 的 4040 页面去 KILL 掉当前的 SQL,这样外层 PLSQL 就会立即终止。

案例合集
  • 例 1:查看已有 PLSQL 函数(不指定 db_name)

    SHOW PLSQL FUNCTIONS;
    复制

    返回结果

    +---------------------------------------------------+
    |                  plsql functions                  |
    +---------------------------------------------------+
    | --------System functions---------                 |
    | owa_util.who_called_me(string,string,int,string)  |
    | dbms_output.put_line(string)                      |
    | set_env(string,string)                            |
    | sqlcode(void)                                     |
    | sqlerrm(void)                                     |
    | raise_application_error(int,string,bool)          |
    | get_env(string)                                   |
    | dbms_lock.sleep(double)                           |
    | sqlerrm(int)                                      |
    | get_columns(string,nestedtable<string>)           |
    | put_line(string)                                  |
    | --------User defined functions---------           |
    +---------------------------------------------------+
    复制
  • 例 2:查看某一 PL/SQL 函数/过程的详细信息

    使用 EXTENDED 关键字查看自定义过程 grant_priv 的详细信息

    DESC PLSQL FUNCTION EXTENDED create_test;
    复制

    返回结果

    +-----------------------------------------------------------------------+
    |                              description                              |
    +-----------------------------------------------------------------------+
    | Prototype:                                                            |
    | PROCEDURE guichen.grant_priv(priv IN  STRING, username IN  STRING)    |
    | Text:                                                                 |
    | CREATE OR REPLACE PROCEDURE grant_priv(priv STRING, username STRING)  |
    | BEGIN                                                                 |
    |   DECLARE priv_stat STRING;                                           |
    |     SET priv_stat =' GRANT '|| priv || ' TO user ' || username;       |
    |    EXECUTE IMMEDIATE priv_stat;                                       |
    |   END;                                                                |
    +-----------------------------------------------------------------------+
    复制
  • 例 3:删除函数/过程

    删除自定义过程 overload_test_proc;

    DROP PLSQL procedure overload_test_proc;
    复制

    再次通过 `SHOW plsql functions; ` 查看已有的 PL/SQL 函数/过程,确认自定义过程 overload_test_proc 已被删除。

  • 例 4:查看正在运行的 PLSQL 程序的 SESSION ID

    PS PLSQL;
    复制
  • 例 5:终止正在运行的 PLSQL 程序

    KILL PLSQL <SESSION_ID>
    复制
预定义函数/过程/包的介绍

ArgoDB 支持下述系统预定义的函数/过程,我们将分章节介绍每一个函数/过程的具体内容及使用方法。

预定义的函数/过程 说明

set_env(string,string)

set_env 是一个过程,形参 enVar 的参数类型为 IN,数据类型为字符串,用来存放环境变量的名称;形参 value 的参数类型为 IN,数据类型为字符串,相应地用来存放环境变量的值。

语法:PROCEDURE set_env(enVar IN STRING, value IN STRING)

get_env(string)

get_env 是一个函数,形参 enVar 的参数类型为 IN,数据类型为字符串,该函数用来返回 set_env 中环境变量的所对应的值。

语法:FUNCTION get_env(enVar IN STRING) RETURN STRING

sqlcode(void)

sqlcode() 是一个不带参数的函数,用来返回当异常发生时,当前异常的 Error code。

语法:FUNCTION sqlcode() RETURN INT

sqlerrm(void)

不带参数的 sqlerrm(),用来返回当异常发生时,当前异常的 Error message。

语法:FUNCTION sqlerrm() RETURN STRING

sqlerrm(int)

带参数的 sqlerrm(),用来返回既定 Error code 下的 Error message。

语法:FUNCTION sqlerrm(errCode IN INT) RETURN STRING

put_line

put_line 是一个过程,形参 msg 的参数类型为 IN,数据类型为字符串,该过程用来打印出变量或者常量的值。

语法:PROCEDURE dbms_output.put_line(msg IN STRING)

raise_application_error(int,string,bool)

您可以使用预定义函数 raise_application_error,来抛出带有指定 error code、error message 的异常,目前第三个参数 keepExistError 是可选的且没有任 何作用。

语法:FUNCTION raise_application_error(errorCode IN INT, msg IN STRING, keepExistError IN BOOL) RETURN EXCEPTION

预定义函数 get_columns(string,nestedtable<string>) 在 Db2 SQLPL 的环境里不可使用。

预定义函数/过程的使用
set_env 与 get_env
  • set_env 与 get_env 的使用示例

    !set plsqlUseSlash true
    BEGIN
       DECLARE a STRING;
       DECLARE b STRING;
       -- 调用过程 set_env,定义一个名为 aa 的环境变量,值为 hello。
       set_env('aa','hello');
       -- 调用函数 get_env,获取环境变量 aa 的值,并赋值给 a。
       SET a = get_env('aa');
       -- 输出变量 a 的值,可以发现变量 a 与环境变量 aa 的值相同。
       PUT_LINE('the value of a is: '||a);
       set_env('bb','world');
       SET b = get_env('bb');
       PUT_LINE('the value of a is: '||b);
    END;
    /
    复制

    输出结果:

    +---------------------------+
    |          output           |
    +---------------------------+
    | the value of a is: hello  |
    | the value of a is: world  |
    +---------------------------+
    复制
sqlcode 与 sqlerrm
  • sqlcode 与 sqlerrm 的使用示例

    !set plsqlUseSlash true
    BEGIN
      DECLARE TYPE testrecord AS ROW anchor to row of transactions;
      DECLARE test_record testrecord;
      DECLARE v_code INT;
      DECLARE v_errm STRING;
        SELECT *
        INTO test_record
        FROM transactions
        WHERE price=12.12;
        -- 当异常发生时,获取当前异常的 Error code,并赋值给变量 v_code。
        dbms_output.put_line('error code is: ' ||sqlcode());
        -- 当异常发生时,获取当前异常的 Error message,并赋值给变量 v_errm。
        dbms_output.put_line('error message is: ' ||sqlerrm());
    END;
    /
    复制

    输出结果:

    为便于信息阅读和展示,下述信息的展示方式为垂直显示,即通过 Beeline 连接数据库时,添加了参数 --outputformat=vertical

    output  NOT FOUND: Code: 100 Message: No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty tableANONYMOUS BLOCK (LINE 0, COLUMN 0, TEXT "SELECT *
    
    output      INTO test_record
    
    output      FROM transactions
    
    output      WHERE price=12.12;")
    
    output  error code is: 0
    
    output  error message is:
    复制
sqlerrm(int)
  • 使用示例:

    !set plsqlUseSlash true
    CREATE OR REPLACE PROCEDURE test_errm(IN b INT)
    BEGIN
      DECLARE errmessage STRING;
      SET errmessage = sqlerrm(b);
      PUT_LINE('error message is:'||errmessage);
    END;
    /
    BEGIN
      test_errm(438);
      test_errm(100);
      test_errm(110);
    END;
    /
    复制

    输出结果:

    | error message is:Application raised error or warning with diagnostic text: "".                             |
    | error message is:No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table  |
    | error message is:Unknown error code: 110
    复制
PUT_LINE

在 ArgoDB 中使用 PUT_LINE 函数时,如果程序正常运行,则 PUT_LINE 会统一打印到终端。如果程序运行过程中出现了未被处理的异常,可以设置相关命令,将 PUT_LINE 正常打印出的内容连同异常栈一起打印到终端。

  • 程序正常运行时,PUT_LINE 的打印示例

    !set plsqlUseSlash true
    -- 使用 put_line 函数,打印出 v3 的值。
    -- 创建名为 put_line_test 的函数,形参 v1 的数据类型为整数型,返回值为双精度类型。
    CREATE OR REPLACE FUNCTION put_line_test(v1 int)
       RETURNS DOUBLE
    BEGIN
       DECLARE v2 DOUBLE;
       DECLARE v3 STRING;
       -- 使用 put_line 函数,打印出 null 值。
       put_line(null);
       DBMS_OUTPUT.PUT_LINE(v1);
       SET v2 = 2;
       SET v3 = "I'm a string.";
       -- 使用 put_line 函数,打印出变量 v2 的值。
       put_line(v2);
       DBMS_OUTPUT.PUT_LINE('v2: ' || v2);
       -- 使用 put_line 函数,打印出字符串 v2+v1。
       put_line('v2 + v1');
       DBMS_OUTPUT.PUT_LINE(v2 + v1);
       put_line(v3);
       SET v3 = null;
       DBMS_OUTPUT.PUT_LINE(v3);
       -- 函数返回值为 v1 和 v2 的乘积。
       return v2 * v1;
    END;
    /
    BEGIN
      dbms_output.put_line("Executing put_line_test(1)");
      -- 使用 put_line 函数,打印出 put_line_test(1) 的全部值。
      put_line(put_line_test(1));
    END;
    /
    复制

    输出结果:

    +-----------------------------+
    |           output            |
    +-----------------------------+
    | Executing put_line_test(1)  |
    | null                        |
    | 1                           |
    | 2.0                         |
    | v2: 2.0                     |
    | v2 + v1                     |
    | 3.0                         |
    | I'm a string.               |
    | null                        |
    | 2.0                         |
    +-----------------------------+
    复制
raise_application_error
  • 示例一:返回异常信息

    !set plsqlUseSlash true
    BEGIN
      DECLARE num_tables INT;
      -- 查询表 transactions 的行数,并赋值给变量 num_tables。
      SELECT COUNT(*) INTO num_tables FROM transactions;
       -- 如果表 transactions 的行数小于 1000,则抛出异常。
       IF num_tables < 1000 THEN
         RAISE_APPLICATION_ERROR
          (-20101, 'Expecting at least 1000 tables');
        ELSE
          -- 否则,就输出表 transactions 的行数。
          dbms_output.put_line(num_tables);
        END IF;
    END;
    /
    复制

    输出结果:

    Error: EXECUTION FAILED: Task EXEC PLSQL error PLException: [Error -20101] Expecting at least 1000 tables
    *********************************************
    System function raise_application_error (LINE -1, COLUMN -1, TEXT "null")
    ANONYMOUS BLOCK (LINE 7, COLUMN 5, TEXT "RAISE_APPLICATION_ERROR
          (-20101, 'Expecting at least 1000 tables');")
    ********************************************* (state=08S01,code=-20101)
    复制
  • 示例二:当示例一不抛出异常的情况

    !set plsqlUseSlash true
    BEGIN
       DECLARE num_tables INT;
      -- 查询表 transactions 的行数,并赋值给变量 num_tables。
      SELECT COUNT(*) INTO num_tables FROM transactions;
       -- 如果表 transactions 的行数小于 10,则抛出异常。
       IF num_tables < 10 THEN
         RAISE_APPLICATION_ERROR
          (-20101, 'Expecting at least 1000 tables');
        ELSE
          -- 否则,就输出表 transactions 的行数。
          dbms_output.put_line(num_tables);
        END IF;
    END;
    /
    复制

    输出结果:

    +---------+
    | output  |
    +---------+
    | 20      |
    +---------+
    复制