Oracle存储过程（while循环、for循环、if判断、sql拼接、游标）-阿里云开发者社区

本篇文章将通过实例来讲解一下存储过程怎么写，知识点总结在文末。

1 写一个简单的存储过程

首先，让我们来写一个简单的存储过程，用于输出当前系统时间。

CREATE OR REPLACE PROCEDURE TEST AS 
    --声明当前时间变量
    CURRENT_TIME VARCHAR2(32);
BEGIN
    --查询当前时间赋值给变量
    SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') INTO CURRENT_TIME FROM DUAL;
    --输出
    DBMS_OUTPUT.PUT_LINE('当前时间：' || CURRENT_TIME);
END;

--输出结果
当前时间：2020-05-30 16:44:37

2 IF判断

写一个IF语句，判断是否存在某张表，如果存在则删除。

CREATE OR REPLACE PROCEDURE TEST AS 
    --表名
    N_TABLE_NAME VARCHAR2(32);
    --用于存放被查询表数量
    NUM INT;
BEGIN
    --给表名赋值
    N_TABLE_NAME := 'TEST_AAA';
    --查询库中是否有这张表
    SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERE TABLE_NAME = N_TABLE_NAME;
    DBMS_OUTPUT.PUT_LINE('被查询表数量（1为有）：' || NUM);
    --如果有 则删除
    IF NUM = 1 THEN 
        --删除表
        EXECUTE IMMEDIATE 'DROP TABLE ' || N_TABLE_NAME;
        DBMS_OUTPUT.PUT_LINE('表存在，已删除！');
    END IF; 
END;

--输出结果
被查询表数量（1为有）：1
表存在，已删除！

3 WHILE循环

写一个WHILE循环，用来动态拼接部分SQL（关联条件部分）。

CREATE OR REPLACE PROCEDURE TEST AS 
    --待拼接的字段
    STR VARCHAR2(128);
    --逗号数量，用于循环
    SIGNS INT;
    --逗号数量最大值，用于判断是否加AND
    SI_MAX INT;
    --当前循环取到的值
    CURRENT_VALUE VARCHAR2(32);
    --存放计算值，用于判断是每组字段的前后者，0为前者，1位后者
    CALCULATED INT;
    --拼接好的SQL（关联条件字符串）
    CONDITIONS VARCHAR2(128);
    --主查询别名
    ALIAS VARCHAR2(16);
    --副查询别名头
    F_ALI VARCHAR2(16);
    --临时ID
    L_ID VARCHAR2(1280);
BEGIN
    --主查询别名赋值
    ALIAS := 'Z';
    --副查询别名头赋值
    F_ALI := 'B';
    --临时ID赋值
    L_ID := '1002';
    --给待拼接的字段赋值
    STR := 'PROJECTID,ID,YEAR_DATE,TIME';
    --在末端追加逗号，否则最后一个字符无法识别
    STR := STR || ',';
    DBMS_OUTPUT.PUT_LINE('待拼接的字段：' || STR);
    --计算逗号数量
    SELECT REGEXP_COUNT(STR,',') INTO SIGNS FROM DUAL;
    DBMS_OUTPUT.PUT_LINE('逗号数量：' || SIGNS);
    --赋最大值
    SI_MAX := SIGNS;
    --循环拼接SQL
    WHILE SIGNS > 0 LOOP
        --取当前字段
        SELECT SUBSTR(STR,1,INSTR(STR,',') - 1 ) INTO CURRENT_VALUE FROM DUAL;
        --在待拼接的字段里删除当前字段
        SELECT SUBSTR(STR,INSTR(STR,',') + 1) INTO STR FROM DUAL;
        DBMS_OUTPUT.PUT_LINE('当前循环数值：' || SIGNS);
        DBMS_OUTPUT.PUT_LINE('当前取到的字段：' || CURRENT_VALUE);
        DBMS_OUTPUT.PUT_LINE('当前未取到的字段：' || STR);
        --两个字段为一组，此处用当前循环值/2，用于判断前后者
        SELECT MOD(SIGNS,2) INTO CALCULATED FROM DUAL;
        DBMS_OUTPUT.PUT_LINE('0为前者，1位后者：' || CALCULATED);
        --条件是两个一组，等于0时为第一个字符，否则为第二个
        IF CALCULATED = 0 THEN 
            --判断是否为最大值，最大值时为第一个条件，无需加AND
            IF SIGNS = SI_MAX THEN 
                CONDITIONS := CONDITIONS || F_ALI || L_ID || '.' || CURRENT_VALUE;
            ELSE
                CONDITIONS := CONDITIONS || 'AND ' || F_ALI || L_ID || '.' || CURRENT_VALUE;
            END IF; 
        ELSE
            CONDITIONS := CONDITIONS || ' = ' || ALIAS || '.' || CURRENT_VALUE || ' ';
        END IF; 
        --循环值减1
        SIGNS := SIGNS - 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('拼接好的SQL（关联条件字符串）：' || CONDITIONS);
END;

--输出结果
待拼接的字段：PROJECTID,ID,YEAR_DATE,TIME,
逗号数量：4

当前循环数值：4
当前取到的字段：PROJECTID
当前未取到的字段：ID,YEAR_DATE,TIME,
0为前者，1位后者：0

当前循环数值：3
当前取到的字段：ID
当前未取到的字段：YEAR_DATE,TIME,
0为前者，1位后者：1

当前循环数值：2
当前取到的字段：YEAR_DATE
当前未取到的字段：TIME,
0为前者，1位后者：0

当前循环数值：1
当前取到的字段：TIME
当前未取到的字段：
0为前者，1位后者：1

拼接好的SQL（关联条件字符串）：B1002.PROJECTID = Z.ID AND B1002.YEAR_DATE = Z.TIME

4 FOR循环

通过游标写一个简单的FOR循环。
在这里插入图片描述

CREATE OR REPLACE PROCEDURE TEST AS 
    --定义游标，取该表的前10条记录（通过ID排序）
    CURSOR DATA IS SELECT * FROM (SELECT a.*,ROWNUM RN FROM IEW_INDEX a ORDER BY ID) WHERE RN <= 10;
BEGIN
    --循环10次，输出ID、INDEX_NAME
    FOR TEMP IN DATA LOOP
        DBMS_OUTPUT.PUT_LINE(TEMP.ID || '，' || TEMP.INDEX_NAME);
    END LOOP;
END;

--输出结果
1001，本年收入增长
1002，累计收入增长
1003，本年成本增长
1004，累计成本增长
1005，预测投资总额
1013，实际投资总额
1014，预测完工时间
1015，实际完工时间
1016，预测开工时间
1017，实际开工时间

5 项目实例

前边铺垫了那么多，现在附上项目实例，把各种操作整合到一起看一下效果。

CREATE OR REPLACE PROCEDURE TEST AS 
    --表名
    N_TABLE_NAME VARCHAR2(32);
    --逗号数量，用于循环
    SIGNS INT;
    --逗号数量最大值，用于判断是否加AND
    SI_MAX INT;
    --主查询别名
    ALIAS VARCHAR2(16);
    --副查询别名头
    F_ALI VARCHAR2(16);
    --用于存放被查询表数量
    NUM INT;
    --当前循环取到的值
    CURRENT_VALUE VARCHAR2(32);
    --拼接好的目标结果SQL
    TARGET_RESULTS VARCHAR2(25600);
    --待拼接的字段
    STR VARCHAR2(128);
    --关联条件字符串
    CONDITIONS VARCHAR2(128);
    --查询项目字符串
    QUERY_ITEMS VARCHAR2(128);
    --存放计算值，用于判断是每组字段的前后者，0为前者，1位后者
    CALCULATED INT;
    --定义游标，取该表的前50条记录（通过ID排序）
    CURSOR DATA IS SELECT * FROM (SELECT a.*,ROWNUM RN FROM IEW_INDEX a ORDER BY ID) WHERE RN <= 50;
BEGIN
    --主查询别名赋值
    ALIAS := 'Z';
    --副查询别名头赋值
    F_ALI := 'B';
    --表名赋值
    N_TABLE_NAME := 'BBB';
    --查询库中是否有这张表
    SELECT COUNT(1) INTO NUM FROM ALL_TABLES WHERE TABLE_NAME = N_TABLE_NAME;
    --如果有 则删除
    IF NUM = 1 THEN 
        --删除表
        EXECUTE IMMEDIATE 'DROP TABLE ' || N_TABLE_NAME;
    END IF; 
    --拼接建表语句
    TARGET_RESULTS := 'CREATE TABLE ' || N_TABLE_NAME || ' AS ';
    --拼接查询项
    TARGET_RESULTS := TARGET_RESULTS || 'SELECT ' || ALIAS || '.* ';
    --循环动态生成查询项
    FOR TEMP IN DATA LOOP
        TARGET_RESULTS := TARGET_RESULTS || ',B' || TEMP.ID || '.A' || TEMP.ID || ' ';
    END LOOP;
    --拼接主SQL
    TARGET_RESULTS := TARGET_RESULTS || 'FROM (SELECT a.PRO_NAME,a.CLASS_NAME,a.ID,a.MAIN_DATA_PRO_CODE,b.TIME ';
    TARGET_RESULTS := TARGET_RESULTS || 'FROM IEW_V_PRO_USER a LEFT JOIN (SELECT DISTINCT PROJECTID,TIME from V_IEW_ACC_DETAIL_DATA WHERE TIME <= '''||2020||''') b ON a.ID = b.PROJECTID ';
    TARGET_RESULTS := TARGET_RESULTS || 'LEFT JOIN (SELECT T.*,(CASE WHEN t.START_YEAR IS NULL THEN bussiness_year+ORIGIN_YEAR ELSE START_YEAR END) begin_year,'''||2020||''' END_YEAR  ';
    TARGET_RESULTS := TARGET_RESULTS || 'FROM (SELECT A.PROJECTNAME,B.ID,B.MAIN_DATA_PRO_CODE,A.PERIOD,A.ORIGIN_YEAR,A.PERIOD_TIME,';
    TARGET_RESULTS := TARGET_RESULTS || '(SELECT TO_NUMBER(SUBSTR(NVL(VALUE,'''||1900||'''),0,4)) bussiness_year ';
    TARGET_RESULTS := TARGET_RESULTS || 'FROM IEW_ACTUAL_DATA WHERE ACC_CODE = '''||'C000005'||''' AND PROJECTID = B.ID) bussiness_year,A.START_YEAR,D.TIME,D.VALUE,C.RESULT ISWARN ';
    TARGET_RESULTS := TARGET_RESULTS || 'FROM IEW_MODPRICE_PERIOD A LEFT JOIN IEW_V_PRO_USER B ON A.PROJECTNAME = B.PROJECTNAME ';
    TARGET_RESULTS := TARGET_RESULTS || 'LEFT JOIN (SELECT * FROM IEW_ACTUAL_DATA D WHERE  D.TIME < '''||2020||''' AND D.ACC_CODE = '''||'C000114'||''')D ';
    TARGET_RESULTS := TARGET_RESULTS || 'ON D.PROJECTID = B.ID LEFT JOIN (SELECT * FROM IEW_ACC_WARN_DATA_FINAL  WHERE  DATA_VER_ID = 1 AND RES_ACC_CODE = '''||'income002'||''' ) C ';
    TARGET_RESULTS := TARGET_RESULTS || 'ON B.ID = C.PROJECTID AND C.YEAR_DATE = D.TIME) T  ORDER BY ID,PROJECTNAME,TIME )C ON A.ID = C.ID AND B.TIME = C.TIME ORDER BY a.ID,b.TIME';
    TARGET_RESULTS := TARGET_RESULTS || ') ' || ALIAS || ' ';
    --循环拼接关联SQL
    FOR TEMP IN DATA LOOP
        --给待拼接的字段赋值
        STR := TEMP.ASSOCIATION;
        --在末端追加逗号，否则最后一个字符无法识别
        STR := STR || ',';
        --计算逗号数量
        SELECT REGEXP_COUNT(STR,',') INTO SIGNS FROM DUAL;
        --赋最大值
        SI_MAX := SIGNS;
        --清空关联条件字符串
        CONDITIONS := '';
        --清空查询字符串
        QUERY_ITEMS := '';
        --循环拼接SQL
        WHILE SIGNS > 0 LOOP
            --取当前字段
            SELECT SUBSTR(STR,1,INSTR(STR,',') - 1 ) INTO CURRENT_VALUE FROM DUAL;
            --在待拼接的字段里删除当前字段
            SELECT SUBSTR(STR,INSTR(STR,',') + 1) INTO STR FROM DUAL;
            --两个字段为一组，此处用当前循环值/2，用于判断前后者
            SELECT MOD(SIGNS,2) INTO CALCULATED FROM DUAL;
            --条件是两个一组，等于0时为第一个字符，否则为第二个
            IF CALCULATED = 0 THEN 
                --判断是否为最大值，最大值时为第一个条件，无需加AND
                IF SIGNS = SI_MAX THEN 
                    --拼接关联条件字符串
                    CONDITIONS := CONDITIONS || F_ALI || TEMP.ID || '.' || CURRENT_VALUE;
                    --拼接查询字符串
                    QUERY_ITEMS := QUERY_ITEMS || CURRENT_VALUE;
                ELSE
                    --拼接关联条件字符串
                    CONDITIONS := CONDITIONS || 'AND ' || F_ALI || TEMP.ID || '.' || CURRENT_VALUE;
                    --拼接查询字符串
                    QUERY_ITEMS := QUERY_ITEMS || ','  || CURRENT_VALUE;
                END IF; 
            ELSE
                CONDITIONS := CONDITIONS || ' = ' || ALIAS || '.' || CURRENT_VALUE || ' ';
            END IF; 
            --循环值减1
            SIGNS := SIGNS - 1;
        END LOOP;
        --拼接关联SQL
        TARGET_RESULTS := TARGET_RESULTS || 'LEFT JOIN (SELECT DISTINCT ' || QUERY_ITEMS || ',' || TEMP.QUERY_CONTENT || ' AS A' || TEMP.ID || ' FROM ' || TEMP.TABLE_NAME || ' WHERE ' || TEMP.PARAMETER || ') B' || TEMP.ID || ' ON ' || CONDITIONS;
    END LOOP;
    --执行建表语句
    EXECUTE IMMEDIATE TARGET_RESULTS;
    DBMS_OUTPUT.PUT_LINE('目标结果SQL：' || chr(13) || TARGET_RESULTS);
END;

由于输出结果太长了，我这里就附上一张图片吧。
在这里插入图片描述

6 知识点

SELECT …INTO
在数据库中进行查询，并将得到的结果赋值给变量。
要求：查询的结果集中==只能有1行==。
:=
给变量赋值。
||
字符串连接符号，相当于Java重的“+”，将两个字符或字符串连接起来。
DBMS_OUTPUT.PUT_LINE()
打印的语句或变量。
EXECUTE IMMEDIATE
执行动态语句，可以用于执行动态拼接好的SQL。
CURSOR IS
游标，配合FRO使用。

Oracle存储过程（while循环、for循环、if判断、sql拼接、游标）

1 写一个简单的存储过程

2 IF判断

3 WHILE循环

4 FOR循环

5 项目实例

6 知识点

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

Oracle存储过程（while循环、for循环、if判断、sql拼接、游标）

1 写一个简单的存储过程

2 IF判断

3 WHILE循环

4 FOR循环

5 项目实例

6 知识点

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像