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/匿名块时，块结束需输入 / 执行

五实用脚本模板

最新问答