关于PL/SQL函数和PL/SQL存储过程可以重名的问题说明

2021-06-17 SQL报错

内容纲要

问题描述

客户在通过 system.procedures_v 或者 system.functions_v 查看存储过程或者函数时，发现大量的同名存储过程和函数。无法确认调用的是哪个，调用时也容易出现调用错误的问题。

本文主要从原理角度解释下该问题存在的原因，以及规避方法。

注意：我们是支持存储过程同名同参，但是需要不同的database，或者不同的package下面。

详细说明

1. 获取 inceptor 中视图的DDL，通过 dblink 指向 txsql 的视图

--system.procedures_v，指向txsql的metastore_inceptorX.procedures_v
CREATE VIEW system.procedures_v AS 
select 
    plsql_function_name(procedure_name) procedure_name,
    plsql_parameters(procedure_name) parameters,
    full_text,
    owner_name,
    owner_type,
    create_time,
    database_name 
from procedures_v@system_dblink;

--system.functions_v，指向txsql的metastore_inceptorX.functions_v
CREATE VIEW system.functions_v AS 
select 
    plsql_function_name(function_name) function_name,
    plsql_parameters(function_name) parameters,
    full_text,
    owner_name,
    owner_type,
    create_time,
    database_name 
from functions_v@system_dblink;

2. 获取txsql中视图的DDL，指向元数据表FUNCS

--metastore_inceptorX.procedures_v，指向元数据的FUNCS和DBS
CREATE ALGORITHM=UNDEFINED DEFINER=inceptoruser@% SQL SECURITY DEFINER VIEW procedures_v
(procedure_id, procedure_name, full_text, owner_name, owner_type, create_time, database_name) AS
SELECT
    FUNCS.FUNC_ID                    AS procedure_id,
    FUNCS.FUNC_NAME                  AS procedure_name,
    FUNCS.FULL_TEXT                  AS full_text,
    FUNCS.OWNER_NAME                 AS owner_name,
    FUNCS.OWNER_TYPE                 AS owner_type,
    from_unixtime(FUNCS.CREATE_TIME) AS create_time,
    DBS.NAME                         AS database_name
FROM
    (FUNCS
JOIN
    DBS
ON
    ((
            FUNCS.DB_ID = DBS.DB_ID)))
WHERE
    (
        FUNCS.FUNC_TYPE = 2);

--metastore_inceptorX.functions_v，指向元数据的FUNCS和DBS
CREATE ALGORITHM=UNDEFINED DEFINER=inceptoruser@% SQL SECURITY DEFINER VIEW functions_v
(function_id, function_name, full_text, owner_name, owner_type, create_time, database_name) AS
SELECT
    FUNCS.FUNC_ID                    AS function_id,
    FUNCS.FUNC_NAME                  AS function_name,
    FUNCS.FULL_TEXT                  AS full_text,
    FUNCS.OWNER_NAME                 AS owner_name,
    FUNCS.OWNER_TYPE                 AS owner_type,
    from_unixtime(FUNCS.CREATE_TIME) AS create_time,
    DBS.NAME                         AS database_name
FROM
    (FUNCS
JOIN
    DBS
ON
    ((
            FUNCS.DB_ID = DBS.DB_ID)))
WHERE
    (
        FUNCS.FUNC_TYPE = 1);

可以看到，存储过程和函数对应的元数据表是同一个FUNCS，只是FUNC_TYPE的区别（FUNC_TYPE为1是函数，2为存储过程）。

3. 获取元数据表FUNCS的DDL

CREATE TABLE
    FUNCS
    (
        FUNC_ID bigint NOT NULL,
        CREATE_TIME INT NOT NULL,
        DB_ID bigint,
        FUNC_NAME VARCHAR(255) COLLATE utf8_bin,
        FUNC_TYPE INT NOT NULL,
        OWNER_NAME VARCHAR(128) COLLATE latin1_bin,
        OWNER_TYPE VARCHAR(10) COLLATE latin1_bin,
        FULL_TEXT mediumtext COLLATE utf8_bin,
        FUNC_DESC mediumblob NOT NULL,
        CLASS_NAME VARCHAR(4000) COLLATE latin1_bin,
        PRIMARY KEY (FUNC_ID),
        CONSTRAINT FUNCS_FK1 FOREIGN KEY (DB_ID) REFERENCES DBS (DB_ID),
        CONSTRAINT UNIQUEFUNCTION UNIQUE (FUNC_NAME, DB_ID),
        INDEX FUNCS_N49 (DB_ID)
    )
    ENGINE=InnoDB DEFAULT CHARSET=latin1 DEFAULT COLLATE=latin1_swedish_ci;

可以看到，FUNCS表的FUNC_NAME和DB_ID字段是作为联合唯一键的，按理说一个数据库DB_ID下应该只有一个FUNC_NAME才对，但是，我们检查一下FUNCS.FUNC_NAME字段会发现异样：

file

_z16_d7defaultp0ppp01stringstringstringstringstringstringstringstringstringstringstring
_z16_d7defaultp0ppp01stringstringstringstringstringstringstringstringstringstringstringstring

也就是说，FUNC_NAME字段是"数据库名称"+"函数名称"+"N个参数类型"+"其他字符"拼接而成的，图中红框部分，库名为default，函数名称为ppp01,一个函数使用了11个参数，参数类型为string，而另外一个同库同名函数使用了12个参数，这样在使用的时候多写一个参数或者少写一个参数就会调用错误。

解决方法

创建函数时，参数数量不一致或者参数类型不同（包含入参和出参）都会引发该问题，而且CREATE OR REPLACE的方式只能够把相同入参数量、相同入参类型的函数替代。

所以，这类同库同名的函数/存储过程，建议使用DROP FUNCTION {IF EXISTS} .../DROP PROCEDURE {IF EXISTS} ...的方式删除重建，而不建议使用CREATE OR REPLACE FUNCTION .../CREATE OR REPLACE PROCEDURE ...的方式。

FAQ

同名不同参的存储过程，如何指定任意一个进行删除？

-- 创建
!set plsqlUseSlash true
CREATE OR REPLACE PROCEDURE param1(var1 IN  string)
IS
BEGIN
    PUT_LINE('hello world');
END;
/

- 删除
drop plsql procedure param1(var1 IN string);