この記事は約11分49秒で読むことができます。

動的にファンクションの呼び出しを制御する話

環境

18cで

コード表示

[oracle@centos7 ~]$ docker exec -it orcl_18cr3_1 bash
[oracle@f285aba0589a ~]$ sqlplus aine/ORACLE_PWD@pdb1

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 26 20:34:01 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Tue Mar 26 2019 20:28:33 +09:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

ファンクション2つ適当に作る

クエリ

コード表示

create or replace function func___01___(p_num number)
return number
as rt number := p_num;
begin
	return rt * 10;
end;
/

create or replace function func___02___(p_num number)
return number
as rt number := p_num;
begin
	return rt * 100;
end;
/

select FUNC___01___(10) as rt from dual;
select FUNC___02___(10) as rt from dual;

実行例

コード表示

AINE@pdb1> select FUNC___01___(10) as rt from dual;

           RT
-------------
          100

1 row selected.

Elapsed: 00:00:00.00

AINE@pdb1> select FUNC___02___(10) as rt from dual;

           RT
-------------
         1000

1 row selected.

Elapsed: 00:00:00.00

ファンクション呼び出しファンクション

クエリ

コード表示


create or replace function func___invocker___(p_func_type number,p_num number)
return number
as rt number;
build_sql clob := to_clob('');
begin
	build_sql := 'select '||'func'||'___' || lpad(p_func_type,2,0) || '___' || '(' ||':p_num'|| ')' || ' from dual';
	execute IMMEDIATE build_sql into rt using p_num;
	return rt;
end;
/

実行例

コード表示

AINE@pdb1> select func___invocker___(1,10) as rt from dual;

           RT
-------------
          100

1 row selected.

Elapsed: 00:00:00.00
AINE@pdb1> select func___invocker___(2,10) as rt from dual;

           RT
-------------
         1000

1 row selected.

Elapsed: 00:00:00.01

パッケージにくるんでみると、パッケージ内での処理を気にせず、呼び出し側はcallできる。

クエリ

コード表示

create or replace package func___pkg___ is
	function func___01___(p_num number) return number;
	function func___02___(p_num number) return number;
end;
/
create or replace package body func___pkg___ is
	procedure func___fmt___(mtd varchar2) as
	begin
		DBMS_OUTPUT.PUT_LINE(mtd||'is called');
		return;
	end;
	function func___01___(p_num number)
	return number
	as rt number := p_num;
	begin
		func___fmt___('func___01___');
		return rt * 10;
	end;
	function func___02___(p_num number)
	return number
	as rt number := p_num;
	begin
		func___fmt___('func___02___');
		return rt * 100;
	end;
end;
/
select FUNC___PKG___.FUNC___01___(10) as rt from dual;
select FUNC___PKG___.FUNC___02___(10) as rt from dual;

実行例

コード表示

AINE@pdb1> select FUNC___PKG___.FUNC___01___(10) as rt from dual;

           RT
-------------
          100

1 row selected.

func___01___is called
Elapsed: 00:00:00.00

AINE@pdb1> select FUNC___PKG___.FUNC___02___(10) as rt from dual;

           RT
-------------
         1000

1 row selected.

func___02___is called
Elapsed: 00:00:00.00

パッケージの文言を追加しただけ。

クエリ

コード表示

create or replace function func___invocker___(p_func_type number,p_num number)
return number
as rt number;
build_sql clob := to_clob('');
begin
	build_sql := 'select '|| 'func___pkg___.' ||'func'||'___' || lpad(p_func_type,2,0) || '___' || '(' ||':p_num'|| ')' || ' from dual';
	execute IMMEDIATE build_sql into rt using p_num;
	return rt;
end;
/
select func___invocker___(1,10) as rt from dual;
select func___invocker___(2,10) as rt from dual;

実行例

コード表示

AINE@pdb1> select func___invocker___(1,10) as rt from dual;

           RT
-------------
          100

1 row selected.

func___01___is called
Elapsed: 00:00:00.00
AINE@pdb1> select func___invocker___(2,10) as rt from dual;

           RT
-------------
         1000

1 row selected.

func___02___is called
Elapsed: 00:00:00.00

Leave a Reply

Your email address will not be published. Required fields are marked *