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-3016: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 =1THEN
--删除表
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';
--给待拼接的字段赋值oracle游标的使用
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 >0LOOP
-
-取当前字段
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 =0THEN
--判断是否为最⼤值，最⼤值时为第⼀个条件，⽆需加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
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 =1THEN
--删除表
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_DETAI L_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 EL SE 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.PERI OD_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_y ear,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.PROJE CTNAME ';
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 >0LOOP
--取当前字段
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 =0THEN
--判断是否为最⼤值，最⼤值时为第⼀个条件，⽆需加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;
由于输出结果太长了，我这⾥就附上⼀张图⽚吧。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论