[cx_Oracle入門](第11回) PL/SQLの実行の基本

連載目次

連載：cx_Oracle入門　目次

検証環境

• Oracle Cloud利用
• Oracle Linux 7.7 (VM.Standard2.1)
• Python 3.6
• cx_Oracle 8.0
• Oracle Database 19.5 (ATP, 1OCPU)
• Oracle Instant Client 18.5

無名PL/SQLの実行

無名PL/SQLの実行は、他のSQL文と同様、Cursorオブジェクトのexecute()メソッドで実行可能です。以下、サンプルです。サンプルの様に、バインド変数も利用可能です。

sample11a.py

import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
begin
  :out_value := :in_value * 2;
end;
"""

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
        with connection.cursor() as cursor:
                outValue = cursor.var(int)
                cursor.execute(SQL, [outValue, 111])
                print(outValue.getvalue())

ストアドプロシージャの実行

ストアドプロシージャの実行は、下記2種類の実行方法があります。

Cursor.callproc()メソッドを使用

sample11b.py

import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
create or replace procedure sample11b(in_value in number, out_value out number) is
begin
  out_value := in_value * 2;
end;
"""

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
        with connection.cursor() as cursor:
                outValue = cursor.var(int)
                cursor.execute(SQL)
                cursor.callproc("sample11b", [222, outValue])
                print(outValue.getvalue())

下から2行目のように、最初の引数で呼び出したいストアドプロシージャの名前をstr型で指定してください。2番目の引数にはストアドプロシージャへの引数を、プロシージャの引数仕様に合わせて指定します。

Cursor.execute()メソッドを使用してSQLのCALL文を実行

sample11c.py

import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
create or replace procedure sample11b(in_value in number, out_value out number) is
begin
  out_value := in_value * 2;
end;
"""

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
        with connection.cursor() as cursor:
                outValue = cursor.var(int)
                cursor.execute(SQL)
                cursor.execute("call sample11b(:a, :b)", [333, outValue])
                print(outValue.getvalue())

プロシージャの引数をバインド変数として指定する必要があります。

callproc()とexecute()+CALLのどちらがよいかというと、callproc()の場合、DB APIのcallproc()は引数名を指定した引数の指定に対応していません。ただしcx_OracleではDB APIを拡張して対応しています。DB APIに厳密に対応したコーディングがしたい場合はexecute()+CALLとなります。とはいえ、cx_OracleはDB APIにない、独自拡張した仕様が結構ありますので、厳密に守るのも難しそうです。
逆に、コーディングのわかりやすさはcallproc()だと思います。また、CALL文でのストアドプロシージャやストアドファンクションの呼び出しは、callproc()と比べて微妙に仕様が異なる場合があるようです。こういった点が気になる場合はcallproc()の利用をお勧めいたします。

ストアドファンクションの実行

ストアドプロシージャの実行は、下記2種類の実行方法があります。

Cursor.callfunc()メソッドを使用

sample11d.py

import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
create or replace function sample11d(in_value in number)
  return number is
begin
  return in_value * 2;
end;
"""

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
        with connection.cursor() as cursor:
                cursor.execute(SQL)
                returnValue = cursor.callfunc("sample11d", int, [111])
                print(returnValue)

下から2行目のように、関数を演算結果を戻すので、変数(サンプルのケースだとreturnValue)で受けています。
callfunc()メソッドの引数は、1つ目はファンクション名で、str型で渡す必要があります。2つ目は関数の戻り値のデータ型を指定します。3つ目は、関数の引数です。

Cursor.execute()メソッドを使用してSQLのCALL文を実行

sample11e.py

import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
create or replace function sample11d(in_value in number)
  return number is
begin
  return in_value * 2;
end;
"""

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
        with connection.cursor() as cursor:
                outValue = cursor.var(int)
                cursor.execute(SQL)
                cursor.execute("call sample11d(:inValue) into :outValue", [222, outValue])
                print(outValue.getvalue())

基本的にはストアドプロシージャの実行と同様です。ストアドファンクションの場合は、戻り値を受けるためにINTO句が必要になる点、ご注意ください。

上記二つ実行方法ののどちらがよいかというと、基本的にはストアドファンクションと同様の考え方となります。違いは、DB APIにはcallfunc()が存在しない点です。