oracle call vs exec[ute]

最新推荐文章于 2020-05-09 16:42:43 发布

Jalen备忘录

最新推荐文章于 2020-05-09 16:42:43 发布

阅读量156

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Database

原文链接：https://stackoverflow.com/questions/20571647/oracle-sql-stored-procedures-call-vs-execute

Database 专栏收录该内容

32 篇文章

订阅专栏

本文探讨了在Oracle SQL*Plus环境中使用CALL与EXEC执行存储过程（SP）的区别。CALL作为标准的Oracle SQL语法，在多种客户端中普遍支持；而EXEC则允许执行任意SQL语句，但在某些客户端如Liquibase中可能不受支持。此外，CALL要求参数类型为SQL数据类型，不能使用如BOOLEAN等PL/SQL特有类型。当SP无参数时，CALL需使用空括号。文章还提供了一个Python示例，展示如何通过SQLAlchemy连接Oracle数据库并调用存储过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Both EXEC[ute] SP() and CALL SP() could be used in SQL*Plus to execute an SP. BTW, you can also use BEGIN SP(); END;

But there are some differences.

CALL is Oracle SQL and should work everywhere. Other DB clients that can talk to Oracle may or may not support SQL*Plus EXEC. Many do (for example, Oracle SQL Developer, SQLWorkbench/J), but some don't (Liquibase).
The data types of the parameters passed by the CALL statement must be SQL data types. They cannot be PL/SQL-only data types such as BOOLEAN.
EXEC could be used to execute not only an SP, but an arbitrary statement.
If an SP does not have parameters, you can use EXEC SP; syntax, but CALL requires empty parentheses: CALL SP();

python:

def get_checked_data(param1, param2, param3):
    from sqlalchemy import create_engine
    import pandas as pd
    import logging
    engine = create_engine("oracle://user:pass@ip:port/?service_name=?", echo=True)
    
    logging.info('Run procedure and get data... ...')
    connection = engine.raw_connection()
    try:
        cursor = connection.cursor()
        cursor.callproc("procedure_name", [param1, param2])
        sql = "select * from table_name where username=:param3"
        df = pd.read_sql_query(sql, engine, params=[param3])
        cursor.close()
        connection.commit()
        return df.to_dict(orient='records')
    except Exception as e:
        logging.error('Get checked data for bov error:' + str(e.message))
        logging.error(traceback.format_exc())
    finally:
        connection.close()