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

2023-10-07 04:14| 来源: 网络整理| 查看: 265

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

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 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使用。

【本文地址】

公司简介

联系我们