艾歐踢論壇

標題: Calling PL/SQL Procedures in Python [打印本頁]
作者: admin    時間: 2024-4-27 14:56
標題: 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.
  1. CREATE OR REPLACE PROCEDURE get_order_count(
  2.     salesman_code NUMBER,
  3.     year NUMBER,
  4.     order_count OUT NUMBER)
  5. IS     
  6. BEGIN     
  7.     SELECT
  8.         COUNT(*) INTO order_count  
  9.     FROM orders
  10.     WHERE salesman_id = salesman_code AND
  11.         EXTRACT(YEAR FROM order_date) = year;
  12. EXCEPTION
  13.     WHEN OTHERS THEN
  14.         dbms_output.put_line(sqlerrm);
  15. END;        
  16. Code language: SQL (Structured Query Language) (sql)
  17. To test the procedure, you can use the following code using SQL*Plus or SQL Developer:

  19. SET SERVEROUTPUT ON;

  21. DECLARE
  22.     l_order_count NUMBER;
  23. BEGIN
  24.     get_order_count(54,2017,l_order_count);
  25.     dbms_output.put_line(l_order_count);
  26. 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
  1. import cx_Oracle
  2. import config as cfg


  5. def get_order_count(salesman_id, year):
  6.     """
  7.     Get order count by salesman and year
  8.     :param salesman_id:
  9.     :param year:
  10.     :return: the number of orders by a salesman and year
  11.     """
  12.     try:
  13.         # create a connection to the Oracle Database
  14.         with cx_Oracle.connect(cfg.username,
  15.                             cfg.password,
  16.                             cfg.dsn,
  17.                             encoding=cfg.encoding) as connection:
  18.             # create a new cursor
  19.             with connection.cursor() as cursor:
  20.                 # create a new variable to hold the value of the
  21.                 # OUT parameter
  22.                 order_count = cursor.var(int)
  23.                 # call the stored procedure
  24.                 cursor.callproc('get_order_count',
  25.                                 [salesman_id, year, order_count])
  26.                 return order_count.getvalue()
  27.     except cx_Oracle.Error as error:
  28.         print(error)


  31. if __name__ == '__main__':
  32.     orders = get_order_count(54, 2017)
  33.     print(orders)  # 3
複製代碼
Note that the config.py is as follows:
  1. username = 'OT'
  2. password = '<password>'
  3. dsn = 'localhost/pdborcl'
  4. port = 1512
  5. 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:
  1. order_count = cursor.var(int)
複製代碼

Fourth, call the procedure get_order_count() using the Cursor.callproc() method:
  1. cursor.callproc('get_order_count', [salesman_id, year, order_count])
複製代碼

Finally, call the Variable.getvalue() method to return the value of the variable.
  1. 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:
  1. cursor.execute("begin get_order_count(:1,:2,:3); end;", [salesman_id, year, order_count])<div align="left"></div>
複製代碼









歡迎光臨 艾歐踢論壇 (http://www.iot.idv.tw/ucenter/) Powered by Discuz! X3.2