问题描述
客户在通过 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
字段会发现异样:
_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);