centos下sqlplus存储过程使用教程

CentOS 下 SQL*Plus 存储过程使用教程

一 环境准备与连接数据库

• 安装 Oracle Instant Client 与 SQL*Plus（示例为 21.1.0.0.0 x86_64）：
  • 下载并安装 RPM 包（basic、sqlplus、devel），示例：
    • oracle-instantclient-basic-21.1.0.0.0-1.x86_64.rpm
    • oracle-instantclient-sqlplus-21.1.0.0.0-1.x86_64.rpm
    • oracle-instantclient-devel-21.1.0.0.0-1.x86_64.rpm
  • 安装命令：rpm -ivh 包名
• 配置环境变量（写入 ~/.bashrc 或 /etc/profile 并 source 生效）：
  • export ORACLE_HOME=/usr/lib/oracle/21/client64
  • export TNS_ADMIN=$ORACLE_HOME/network/admin
  • export LD_LIBRARY_PATH=$ORACLE_HOME/lib
  • 可选：export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
• 创建网络配置（示例）：
  • mkdir -p $TNS_ADMIN
  • 编辑 $TNS_ADMIN/tnsnames.ora，加入：
    • ORCL =
      • (DESCRIPTION =
        • (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
        • (CONNECT_DATA =
          • (SERVICE_NAME = ORCL)
• 连接数据库：
  • sqlplus username/password@//192.168.1.10:1521/ORCL
  • 或 sqlplus username/password@ORCL（使用 tnsnames.ora）
  • 连接成功后提示：Connected to: Oracle Database …

二 创建与编译存储过程

• 基本语法模板：
  • CREATE [OR REPLACE] PROCEDURE 过程名 (
    • 参数名1 [IN | OUT | IN OUT] 数据类型 [DEFAULT 值],
    • …
    • )
    • IS | AS
    • 声明部分;
    • BEGIN
      • 执行部分;
    • EXCEPTION
      • 异常处理;
    • END;
    • /
• 示例 1（无参过程，打印信息）：
  • CREATE OR REPLACE PROCEDURE hello_proc AS
    • BEGIN
      • DBMS_OUTPUT.PUT_LINE(‘Hello, SQL*Plus on CentOS’);
    • END;
    • /
• 示例 2（带 IN/OUT 参数，查询员工姓名与工资）：
  • CREATE OR REPLACE PROCEDURE get_emp_info(
    • p_empno IN emp.empno%TYPE,
    • p_ename OUT emp.ename%TYPE,
    • p_sal OUT emp.sal%TYPE
    • )
    • IS
    • BEGIN
      • SELECT ename, sal INTO p_ename, p_sal
        • FROM emp WHERE empno = p_empno;
    • EXCEPTION
      • WHEN NO_DATA_FOUND THEN
        • p_ename := NULL;
        • p_sal := NULL;
    • END;
    • /
• 编译与错误处理：
  • 在 SQL*Plus 中，块或过程体输入完成后以斜杠 / 执行创建/编译
  • 查看最近编译错误：SHOW ERRORS
  • 重新编译已有过程：ALTER PROCEDURE 过程名 COMPILE
  • 脚本化创建：将过程写入文件（如 proc.sql），在 SQL*Plus 中执行：@proc.sql

三 调用与输出查看

• 常用调用方式：
  • EXEC/EXECUTE 过程名(参数列表)：适合快速调用
  • 匿名块调用：BEGIN 过程名(参数); END; /
  • CALL 语句：支持 INTO 绑定变量接收返回值
• 示例 1（无参）：
  • SET SERVEROUTPUT ON
  • EXEC hello_proc
• 示例 2（IN/OUT，使用绑定变量）：
  • VARIABLE v_ename VARCHAR2(30)
  • VARIABLE v_sal NUMBER
  • EXEC get_emp_info(p_empno => 7369, p_ename => :v_ename, p_sal => :v_sal)
  • PRINT v_ename v_sal
• 示例 3（使用 CALL + INTO）：
  • VARIABLE v_out NUMBER
  • CALL some_func(10) INTO :v_out
  • PRINT v_out
• 输出说明：
  • 使用 DBMS_OUTPUT 输出前需执行：SET SERVEROUTPUT ON
  • 在 SQL*Plus 中，CALL 语句的宿主变量（绑定变量）必须以冒号 : 前缀，且同一 CALL 语句中同一宿主变量不能出现两次
  • 无参过程调用也必须带括号：CALL proc();

四 查看源码 修改与删除

• 查看已存在过程源码：
  • SELECT text FROM all_source
    • WHERE name = UPPER(‘过程名’) AND type = ‘PROCEDURE’
    • ORDER BY line;
• 修改：
  • 直接 CREATE OR REPLACE PROCEDURE … 覆盖原过程
  • 或 ALTER PROCEDURE 过程名 COMPILE 重新编译
• 删除：
  • DROP PROCEDURE 过程名
• 常见排错要点：
  • 编译失败用 SHOW ERRORS 定位行号与错误
  • 调用带 OUT 参数时，优先使用绑定变量（VARIABLE …）与 PRINT 查看结果
  • 使用 EXEC/匿名块时，块结束需输入 / 执行

五 实用脚本模板

• 文件：proc_demo.sql
  • SET SERVEROUTPUT ON
  • CREATE OR REPLACE PROCEDURE hello_proc AS
    • BEGIN
      • DBMS_OUTPUT.PUT_LINE(‘Hello, SQL*Plus on CentOS’);
    • END;
    • /
  • CREATE OR REPLACE PROCEDURE get_emp_info(
    • p_empno IN emp.empno%TYPE,
    • p_ename OUT emp.ename%TYPE,
    • p_sal OUT emp.sal%TYPE
    • )
    • IS
    • BEGIN
      • SELECT ename, sal INTO p_ename, p_sal
        • FROM emp WHERE empno = p_empno;
    • EXCEPTION
      • WHEN NO_DATA_FOUND THEN
        • p_ename := NULL;
        • p_sal := NULL;
    • END;
    • /
  • – 调用演示
  • VARIABLE v_ename VARCHAR2(30)
  • VARIABLE v_sal NUMBER
  • EXEC hello_proc
  • EXEC get_emp_info(p_empno => 7369, p_ename => :v_ename, p_sal => :v_sal)
  • PRINT v_ename v_sal
• 在 SQL*Plus 中执行：@proc_demo.sql
• 说明：
  • 以上脚本演示了创建、调用、绑定变量与输出查看的完整流程，适合在 CentOS + SQL*Plus 环境中快速复用