Calling PL/SQL Procedures in Python
Setting up a PL/SQL procedureThe following statement creates a new procedure called get_order_count() that returns the number of sales orders by a salesman in a specific year.CREATE OR REPLACE PROCEDURE get_order_count(
salesman_code NUMBER,
year NUMBER,
order_count OUT NUMBER)
IS
BEGIN
SELECT
COUNT(*) INTO order_count
FROM orders
WHERE salesman_id = salesman_code AND
EXTRACT(YEAR FROM order_date) = year;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END; To test the procedure, you can use the following code using SQL*Plus or SQL Developer:
SET SERVEROUTPUT ON;
DECLARE
l_order_count NUMBER;
BEGIN
get_order_count(54,2017,l_order_count);
dbms_output.put_line(l_order_count);
END; The output of the code is:
3
Calling a PL/SQL procedure in Python example
To execute a PL/SQL procedure, you use the Cursor.callproc() method.
The following code illustrates how to call the procedure get_order_count() and print out the number of orders of the salesman 54 in 2017:
import cx_Oracle
import config as cfg
def get_order_count(salesman_id, year):
"""
Get order count by salesman and year
:param salesman_id:
:param year:
:return: the number of orders by a salesman and year
"""
try:
# create a connection to the Oracle Database
with cx_Oracle.connect(cfg.username,
cfg.password,
cfg.dsn,
encoding=cfg.encoding) as connection:
# create a new cursor
with connection.cursor() as cursor:
# create a new variable to hold the value of the
# OUT parameter
order_count = cursor.var(int)
# call the stored procedure
cursor.callproc('get_order_count',
)
return order_count.getvalue()
except cx_Oracle.Error as error:
print(error)
if __name__ == '__main__':
orders = get_order_count(54, 2017)
print(orders)# 3Note that the config.py is as follows:
username = 'OT'
password = '<password>'
dsn = 'localhost/pdborcl'
port = 1512
encoding = 'UTF-8'
In this example:
First, connect to the Oracle Database by calling the cx_Oracle.connect() method with the parameters provided by the config module.
Second, create a new Cursor object by calling the Connection.cursor() method.
Third, create a new variable that will hold the returned value of the OUT parameter of the procedure:
order_count = cursor.var(int)Fourth, call the procedure get_order_count() using the Cursor.callproc() method:
cursor.callproc('get_order_count', )
Finally, call the Variable.getvalue() method to return the value of the variable.
return order_count.getvalue()
Because we used the with block, the Cursor and Connection objects were automatically released.
It is important to note that when you call the Cursor.callproc() method, cx_Oracle actually generates the following anonymous block and then executes it:
cursor.execute("begin get_order_count(:1,:2,:3); end;", )
頁:
[1]