联 系 我 们
售前咨询
售后咨询
微信关注:星环科技服务号
更多联系方式 >
6.8.12.8 PL/SQL 函数
更新时间:7/30/2024, 7:25:05 AM

Inceptor 中支持创建 PL/SQL 存储过程,同时也支持创建 PL/SQL 函数,一般情况下,存储过程和函数的作用完全相同,主要区别点在于存储过程没有返回值,而函数有一个返回值,也可以通俗地理解为函数是一个有返回值的过程,可根据实际情况,选择存储过程和函数的使用。


创建函数

语法

CREATE [OR REPLACE] FUNCTION <function_name> ([<parameter1> <mode1> <datatype1>, <parameter2> <mode2> <datatype2> ])
RETURN <datatype>
IS
 <plsql_block>;
复制

参数说明

  • <function_name>:函数名称。

  • <parameter>:PLSQL 中可以选择定义不带参数的函数或带参数的函数,本参数用于定义带参数函数中的参数名称。

  • <mode>:表示参数的类型,可分为 IN,INOUT,OUT 三种类型,缺省状态下默认为 IN 类型,更多用法说明,可参见 PL/SQL 参数类型

  • <datatype>:数据类型。

  • <plsql_block>:指定函数行为,用 PLSQL 块定义。

实例

例 91. 创建名为 hello_message 的函数
!set plsqlUseSlash true
CREATE OR REPLACE FUNCTION
hello_message
 (place_in IN STRING)
RETURN STRING
IS
BEGIN
  RETURN 'Hello ' || place_in
END;
/
复制
  • 子程序的名字描述被返回的数据。而不是被执行的过程。

  • 子程序中多了一个 RETURN 从句。

  • 该函数是设置函数返回值的数据类型。

查看函数

语法

展示所以 PLSQL 系统函数及当前数据库中的自定义函数及存储过程
SHOW PLSQL FUNCTIONS;
复制
查看某个 PLSQL 函数的详细信息
DESC PLSQL FUNCTION [EXTENDED] <function_name>; --[1]
复制

参数说明

  • <function_name>:函数名称。

  • 可选项 [1]:可以选择添加关键字 EXTENDED 列出该 PL/SQL 函数的原文详细信息。

实例

例 92. 查看名为 hello_message 的函数
desc plsql function extended hello_message;
复制

结果如下:

+--------------------------------------------------------------------+
|                            description                             |
+--------------------------------------------------------------------+
| Prototype:                                                         |
| FUNCTION guichen.hello_message(place_in IN  STRING) RETURN STRING  |
| Text:                                                              |
| CREATE OR REPLACE FUNCTION                                         |
| hello_message                                                      |
|  (place_in IN STRING)                                              |
| RETURN STRING                                                      |
| IS                                                                 |
| BEGIN                                                              |
|   RETURN 'Hello ' || place_in                                      |
| END;                                                               |
+--------------------------------------------------------------------+
复制
调用函数
单独调用函数
例 93. 单独调用函数 hello_message 的方法
!set plsqlUseSlash true
DECLARE
   l_message string
BEGIN
   l_message := hello_message ('Universe')
   DBMS_OUTPUT.put_line (l_message)
END;
/
复制

输出结果为:

+-----------------+
|     output      |
+-----------------+
| Hello Universe  |
+-----------------+
复制
在过程中调用函数
例 94. 创建过程 hello_place 中调用 hello_message 函数
!set plsqlUseSlash true
CREATE OR REPLACE PROCEDURE
-- 创建带有参数的过程 hello_place。
hello_place
(place_in string)
IS
BEGIN
   DBMS_OUTPUT.put_line (hello_message (place_in))
END;
/
BEGIN
-- 调用函数 hello_message。
   hello_place('world')
END;
/
复制

输出结果为:

+--------------+
|    output    |
+--------------+
| Hello world  |
+--------------+
复制
SQL 语句中调用函数

标准 SQL 调用自定义的 PLSQL 函数,必须满足以下四个条件

  • 必须是函数,不能是过程。

  • 返回值必须是基本类型。

  • 函数中不能有标准 SQL 语句。

  • 更多内容详解,可参见注意事项章节

例 95. 利用函数 hello_message 往表 ra 中插入一条数据
!set plsqlUseSlash true
BEGIN
  INSERT INTO ra VALUES (hello_message('tom'),4.8)
END;
/
复制

再次查看表中的数据:

SELECT * FROM ra;

+------------+------+
|    name    | gpa  |
+------------+------+
| smith      | 3.4  |
| lisi       | 3.3  |
| ll         | 3.3  |
| lily       | 3.6  |
| Hello tom  | 4.8  |
+------------+------+
复制

可以看到,ra 表中成功插入了一条数据 ('hello tom',4.8)。

函数的嵌套调用

Inceptor 可支持函数的嵌套调用,即在一个函数中调用另外一个函数。

例 96. 创建函数 hello_message_exclamation,并在其内部调用 hello_message 函数
!set plsqlUseSlash true
CREATE OR REPLACE FUNCTION
hello_message_exclamation
 (message_in IN STRING)
RETURN STRING
IS
BEGIN
  RETURN hello_message(message_in) || ' !'
END;
/
DECLARE
   l_message_a string
BEGIN
   l_message_a := hello_message_exclamation ('Universe')
   DBMS_OUTPUT.put_line (l_message_a)
END;
/
复制

执行结果为:

+-------------------+
|      output       |
+-------------------+
| Hello Universe !  |
+-------------------+
复制
函数重载
  • Inceptor 中支持函数的重载,即函数的名称相同,但是参数的类型或数目不能相同(参数的名称可以相同),返回值也可能不同。

  • 在实际的执行过程中,Inceptor 会根据实参的数据类型,来决定应该执行的函数。

例 97. 重载函数 overload_test_func
!set plsqlUseSlash true
-- 创建第一个名为 overload_test_func 的过程,形参名为 place_in,其数据类型为字符串。
CREATE OR REPLACE FUNCTION overload_test_func
  (place_in string) RETURN string
  IS
  BEGIN
    RETURN 'Hello ' || place_in
  END;
/
-- 创建第二个名为 overload_test_func 的过程,形参名为 place_in,其数据类型为整数类型。
CREATE OR REPLACE FUNCTION overload_test_func
  (place_in INT) RETURN string
 IS
 BEGIN
   RETURN place_in * place_in
  END;
/
DECLARE
    V1 STRING
    V2 STRING
    V3 STRING
BEGIN
  V1 :=overload_test_func('world')
  V2 :=overload_test_func(11)
  V3 :=overload_test_func('111')
  -- 函数内的实参为 world,类型为字符串,则会执行第一个函数。
  DBMS_OUTPUT.PUT_LINE(V1)
  -- 函数内的实参为 11,类型为整数类型,则会执行第二个函数。
  DBMS_OUTPUT.PUT_LINE(V2)
  -- 函数内的实参为 111,类型为字符串,则会执行第一个函数。
  DBMS_OUTPUT.PUT_LINE(V3)
END;
/
复制

输出结果:

+--------------+
|    output    |
+--------------+
| Hello world  |
| 121          |
| Hello 111    |
+--------------+
复制

需要注意的是,在 Inceptor 中,函数和过程是不能在 DECLARE 声明块内直接声明的,所以以下用法用来创建函数的重载是错误的。

例 98. 在声明块内声明函数 overload_test_func
!set plsqlUseSlash true
DECLARE
  FUNCTION overload_test_func (place_in string) RETURN string
  IS
  BEGIN
    RETURN 'Hello ' || place_in
  END;
/
 FUNCTION overload_test_func (place_in INT) RETURN string
 IS
  BEGIN
   RETURN place_in * place_in
  END;
/
复制

返回报错:

COMPILE FAILED: Parse error: [Error 1101] line 1:0 cannot recognize input near 'FUNCTION overload_test_func
复制
系统预定义函数
  • Inceptor 中有八个系统预定义的函数,分别为

    • sqlcode(void)

    • sqlerrm(void)

    • get_columns(string,nestedtable<string>)

    • raise_application_error(int,string,bool)

    • set_env(string,string)

    • get_env(string)

    • put_line(string)

    • sqlerrm(int)

  • 我们可以使用相关命令来查看系统预定义函数/过程的有关信息,具体内容可参见注意事项章节

  • 系统预定义函数的具体使用方法和案例,可参见预定义函数/过程/包章节