Calling PL/SQL Procedures in Python
Calling PL/SQL Procedures in PythonThe 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;
Code language: SQL (Structured Query Language) (sql)
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
The following statement creates a new procedure called get_order_count() that returns the number of sales orders by a salesman in a specific year
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;", )<div align="left"></div>
頁:
[1]