Oracle 数据库基础

简介

Oracle 是金融行业核心系统的主流数据库，银行核心系统、风控平台通常使用 Oracle。

pip install cx_Oracle oracledb

连接

import oracledb  # 新版驱动（推荐）

# 连接
connection = oracledb.connect(
    user="finance_user",
    password="password",
    dsn="localhost:1521/ORCL"
)

# 连接池
pool = oracledb.create_pool(
    user="finance_user",
    password="password",
    dsn="localhost:1521/ORCL",
    min=2,
    max=10,
    increment=1
)

基础查询

with pool.acquire() as connection:
    with connection.cursor() as cursor:
        # 参数化查询（防 SQL 注入）
        cursor.execute(
            "SELECT * FROM LOAN_APPLICATIONS WHERE STATUS = :status AND AMOUNT > :amount",
            status="PENDING",
            amount=100000
        )
        
        columns = [col[0] for col in cursor.description]
        rows = cursor.fetchall()
        
        results = [dict(zip(columns, row)) for row in rows]

# 批量插入
with pool.acquire() as connection:
    with connection.cursor() as cursor:
        data = [
            ("L001", "企业A", 500000, "PENDING"),
            ("L002", "企业B", 200000, "APPROVED"),
        ]
        cursor.executemany(
            "INSERT INTO LOAN_APPLICATIONS VALUES (:1, :2, :3, :4)",
            data
        )
        connection.commit()

SQLAlchemy 集成

from sqlalchemy import create_engine

# Oracle 连接字符串
engine = create_engine(
    "oracle+oracledb://user:password@localhost:1521/?service_name=ORCL",
    pool_size=5,
    max_overflow=10
)

# 之后与 MySQL 使用方式完全相同

金融系统对接

• Oracle 存储过程调用：cursor.callproc("RISK_CALC_PKG.CALC_SCORE", [customer_id])
• 大字段处理：使用 CLOB 存储 LLM 生成的长文本报告
• 事务管理：金融操作必须显式 commit/rollback