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

# 連載目次
[連載：cx_Oracle入門　目次](https://qiita.com/nakaie/items/df170c3fa6895cf1b44d)

# 検証環境
- 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()メソッドで実行可能です。以下、サンプルです。サンプルの様に、バインド変数も利用可能です。

```python: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()メソッドを使用

```python: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文を実行

```python: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()メソッドを使用

```python: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文を実行

```python: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()が存在しない点です。