前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Oracle存储过程(while循环、for循环、if判断、sql拼接、游标)

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

作者头像
用户10358987
发布2024-04-23 19:53:02
4720
发布2024-04-23 19:53:02
举报
文章被收录于专栏:java

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

1 写一个简单的存储过程

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

代码语言:javascript
复制
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语句,判断是否存在某张表,如果存在则删除。

代码语言:javascript
复制
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(关联条件部分)。

代码语言:javascript
复制
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循环。

在这里插入图片描述
在这里插入图片描述
代码语言:javascript
复制
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 项目实例

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

代码语言:javascript
复制
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 知识点

  1. SELECT …INTO 在数据库中进行查询,并将得到的结果赋值给变量。 要求:查询的结果集中只能有1行。
  2. := 给变量赋值。
  3. || 字符串连接符号,相当于Java重的“+”,将两个字符或字符串连接起来。
  4. DBMS_OUTPUT.PUT_LINE() 打印的语句或变量。
  5. EXECUTE IMMEDIATE 执行动态语句,可以用于执行动态拼接好的SQL。
  6. CURSOR IS 游标,配合FRO使用。
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2024-04-23,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 1 写一个简单的存储过程
  • 2 IF判断
  • 3 WHILE循环
  • 4 FOR循环
  • 5 项目实例
  • 6 知识点
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档