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',
- [salesman_id, year, 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) # 3
複製代碼 Note that the config.py is as follows:
- username = 'OT'
- password = '<password>'
- dsn = 'localhost/pdborcl'
- port = 1512
- encoding = 'UTF-8'
複製代碼In this example:
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', [salesman_id, year, 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;", [salesman_id, year, order_count])<div align="left"></div>
複製代碼
|