（课堂笔记）PL/SQL 循环 自定义函数 存储过程

本文系统梳理了PL/SQL三大核心内容：

1. 循环结构：包括FOR循环（固定次数）、WHILE循环（条件判断）及BREAK/CONTINUE控制语句；
2. 自定义函数：强调必须返回值的特性，演示了数值计算和业务查询两种应用场景；
3. 存储过程：侧重数据处理流程封装，对比了与函数的关键差异（无返回值、不可SQL调用）。

---

特别指出存储过程中IS/AS关键字不可省略的语法要求，并提供了典型练习案例（质数判断、字符串处理等）。

---

适用于数据库开发人员快速掌握PL/SQL编程要点。

PL/SQL 循环、函数、存储过程 的核心知识点

📘 一、循环结构

1. FOR 循环（最常用）

sql

FOR I IN 起始值..结束值 LOOP
  循环体；
END LOOP;

• I 自动从起始值递增到结束值，步长为 1

• 适合已知循环次数的场景

for 循环的步长固定为1，不能更改。

---

在 PL/SQL 的 FOR 循环中，起始值..结束值 是一个闭区间，循环变量 I 会从起始值一直遍历到结束值，包括结束值本身。

示例：判断质数

sql

DECLARE
  X NUMBER := &请输入数值;
  V_FLAG NUMBER := 0;
BEGIN
  FOR I IN 2..X-1 LOOP
    IF MOD(X, I) = 0 THEN
      V_FLAG := 1;
    END IF;
  END LOOP;
  ...
END;

示例：99 乘法表

sql

DECLARE
  V_STR VARCHAR2(100);
BEGIN
  FOR I IN 1..9 LOOP
    V_STR := '';
    FOR J IN 1..I LOOP
      V_STR := V_STR || I || ' * ' || J || ' = ' || I*J || ' ';
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(V_STR);
  END LOOP;
END;

2. WHILE 循环

sql

WHILE 条件 LOOP
  循环体；
  变量自增/自减；
END LOOP;

• 适合未知循环次数，但知道结束条件的场景

示例：输出 1~100 奇数

sql

DECLARE
  V_NUM NUMBER := 1;
BEGIN
  WHILE V_NUM <= 100 LOOP
    IF MOD(V_NUM,2) = 1 THEN
      DBMS_OUTPUT.PUT_LINE(V_NUM);
    END IF;
    V_NUM := V_NUM + 1;
  END LOOP;
END;

3. 循环控制语句（重要！）

📘 二、自定义函数（FUNCTION）

语法结构

sql

CREATE OR REPLACE FUNCTION 函数名(入参名 参数类型)
RETURN 返回值类型
IS
  变量声明；
BEGIN
  逻辑体；
  RETURN 结果；
END;

特点

• 必须有返回值

• 入参和返回值类型不能定义长度

• 适合封装常用计算逻辑

示例：模拟 ABS 函数

sql

CREATE OR REPLACE FUNCTION ABS_BAK(P_NUM NUMBER)
RETURN NUMBER
IS
BEGIN
  RETURN REPLACE(P_NUM, '-');
END;

示例：根据员工编号返回姓名（带错误处理）

sql

CREATE OR REPLACE FUNCTION F_NAME(P_EMPNO NUMBER)
RETURN VARCHAR2
IS
  V_ENAME VARCHAR2(100);
  V_CNT NUMBER;
BEGIN
  SELECT COUNT(1) INTO V_CNT FROM EMP WHERE EMPNO = P_EMPNO;
  IF V_CNT = 0 THEN
    RETURN '该员工不存在！';
  ELSE
    SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = P_EMPNO;
    RETURN V_ENAME;
  END IF;
END;

📘 三、存储过程（PROCEDURE）

语法结构

sql

CREATE OR REPLACE PROCEDURE 过程名(入参名 参数类型)
IS
  变量声明；
BEGIN
  -- 数据同步、清洗、处理流程
END;

特点

• 没有返回值

• 适合封装数据处理流程（如 ETL）

示例：同步部门最高薪资

sql

CREATE OR REPLACE PROCEDURE P_001
IS
BEGIN
  DELETE FROM H_SAL_EMP;
  INSERT INTO H_SAL_EMP
  SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO;
  COMMIT;
END;

调用方式

sql

BEGIN
  P_001;
END;

示例：带参数的过程

sql

CREATE OR REPLACE PROCEDURE P_002(C_JOB VARCHAR2)
IS
BEGIN
  DELETE FROM EMP_J WHERE JOB = C_JOB;
  INSERT INTO EMP_J SELECT * FROM EMP WHERE JOB = C_JOB;
  COMMIT;
END;

📘 四、函数 vs 存储过程（面试/考试重点）

Oracle 存储过程 procedure 不允许使用 return 语句

✅ 典型练习题（建议手动敲一遍）

1. 循环练习

• 使用 FOR / WHILE 计算 1~200 之间所有偶数的和

• 打印 1~X 之间所有的质数

2. 函数练习

sql

-- 实现 ELIM 函数：删除字符串中第一次出现的指定字符
-- 示例：ELIM('A-B-C', '-') → 'AB-C'

3. 存储过程练习

sql

-- 创建表 T_NUMBER(ID NUMBER)
-- 创建过程 P_004(X)，将比 X 小的所有奇数插入 T_NUMBER

create TABLE T_NUMBER(
ID NUMBER
);
commit;

CREATE OR REPLACE PROCEDURE p_004(x NUMBER)
      IS
      BEGIN
        -- 清空表格
        DELETE FROM T_NUMBER;
        COMMIT;
        -- 或者使用 TRUNCATE（更高效，但无法回滚）
        -- EXECUTE IMMEDIATE 'TRUNCATE TABLE T_NUMBER';
        for i in 1..x-1 loop
            if mod(i,2)=1
            then
            INSERT INTO T_NUMBER(ID) VALUES (i);
            end if;
        end loop;
        COMMIT;
      END;

-- 调用存储过程
BEGIN
p_004(100);
END;

select * from T_NUMBER;

补充：IS可以省略不写吗

---

在 Oracle 存储过程中，IS（或 AS）是必须的，不能省略。

---

存储过程的基本语法结构：

sql

CREATE OR REPLACE PROCEDURE 过程名(参数)
IS      -- 或 AS，必须存在，不能省略
BEGIN
    -- 过程体
END;
/

IS 和 AS 的区别：

在存储过程中，IS 和 AS 完全等价，可以互换使用：

sql

-- 使用 IS
CREATE OR REPLACE PROCEDURE p_004(x NUMBER) 
IS
BEGIN
    -- 逻辑
END;

-- 使用 AS（同样正确）
CREATE OR REPLACE PROCEDURE p_004(x NUMBER) 
AS
BEGIN
    -- 逻辑
END;

为什么不能省略？

IS/AS 的作用是：

1. 标记声明部分的开始（用于声明变量、游标等）

2. 分隔过程头部和主体

3. 语法规定的必需关键字

sql

-- 错误示范（缺少 IS/AS）
CREATE OR REPLACE PROCEDURE p_004(x NUMBER)  -- 编译错误！
BEGIN
    NULL;
END;

对比其他数据库：

数据库	语法	IS/AS 是否必需
Oracle	IS 或 AS	必需
SQL Server	AS	必需
PostgreSQL	AS	必需（但写法不同）
MySQL	不需要	不需要

总结：在 Oracle 中创建存储过程，IS 或 AS 是必需的关键字，不能省略。如果没有任何变量声明，可以在 IS 后直接写 BEGIN。