调用数据库存储过程及其他感兴趣的高级 Python 编程功能。
2010 年 3 月发布
对于涉及数据库的软件开发来说,有两种主流开发方法:一种是在应用程序中(对于三层体系结构,也可以是在中间件中)实现所有业务逻辑,另一种是在数据库内部实现所有业务逻辑。本教程不讨论这两种解决方案的优缺点;不过,使用 Oracle 数据库方法还是会为面向数据库的应用程序带来某些好处。
用 PL/SQL 嵌入所有业务逻辑可大大减少应用程序与数据库之间的往返次数,从而此使处理都在服务器端进行。PL/SQL 与 SQL 紧密集成,并与 Python 类似,提供了大量的标准程序包库:从安排数据库作业时间 (DBMS_SCHEDULER),到自动查询调优 (DBMS_SQLTUNE) 和闪回 (DBMS_FLASHBACK),再到线性代数 (UTL_NLA) 和 LDAP 访问 (DBMS_LDAP)。
本教程介绍使用 cx_Oracle 模块在 Python 中调用 Oracle 数据库内部的 PL/SQL 存储过程和函数的各种方法,同时还介绍一些使用 PL/SQL 无法实现或者实现起来非常复杂的编程功能。在本文的最后,我们将简单介绍 Oracle Berkeley DB,它作为即取即用插件内置在 Python 中。
IN-OUT 方式
Oracle 过程和函数是将 SQL 功能与编程语言功能相结合一些数据库对象。过程(从现在开始也称其为函数)的参数可以是以下三种类型之一:
IN:传递给过程,但不能写入到过程内部
OUT:从过程返回,在过程主体内部可写
IN OUT:传递给过程,在过程内部完全可写
默认情况下,参数都是 IN 类型。
为了说明 Python 和 Oracle 过程之间的交互,我们考虑将以下程序包安装在 Oracle Database XE 实例的 HR 模式中。
CREATE OR REPLACE PACKAGE pkg_hr AS
PROCEDURE add_department(
p_department_id OUT NUMBER,
p_department_name IN VARCHAR2,
p_manager_id IN NUMBER,
p_location_id IN NUMBER
);
FUNCTION get_employee_count(
p_department_id IN NUMBER
) RETURN NUMBER;
PROCEDURE find_employees(
p_query IN VARCHAR2,
p_results OUT SYS_REFCURSOR
);
END pkg_hr;
/
CREATE OR REPLACE PACKAGE BODY pkg_hr AS
PROCEDURE add_department(
p_department_id OUT NUMBER,
p_department_name IN VARCHAR2,
p_manager_id IN NUMBER,
p_location_id IN NUMBER
) AS
BEGIN
INSERT INTO departments(department_id, department_name, manager_id, location_id)
VALUES (departments_seq.nextval, p_department_name, p_manager_id, p_location_id)
RETURNING department_id
INTO p_department_id;
COMMIT;
END add_department;
FUNCTION get_employee_count(
p_department_id IN NUMBER
) RETURN NUMBER AS
l_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO l_count
FROM employees
WHERE department_id= p_department_id;
RETURN l_count;
END get_employee_count;
PROCEDURE find_employees(
p_query IN VARCHAR2,
p_results OUT SYS_REFCURSOR
) AS
BEGIN
OPEN p_results FOR
SELECT *
FROM employees
WHERE UPPER(first_name||'