Oracle存储过程和游标 [英] Oracle Stored Procedure and Cursor
问题描述
我正在尝试编写一个存储过程来对此进行转换:
I'm trying to write a stored procedure to transform this:
|----------|----------|----------|----------|----------|
| ID | ESD | TD | IS_DB | TEST_SET |
|----------|----------|----------|----------|----------|
| 1 | 10 | 20 | 1 | 2 |
| 2 | 30 | (null) | 1 | 2 |
| 3 | 40 | (null) | 1 | 2 |
| 4 | 50 | 60 | 0 | 2 |
| 5 | (null) | 70 | 1 | 2 |
| 6 | 75 | 100 | 1 | 2 |
| 7 | (null) | 80 | 1 | 2 |
|----------|----------|----------|----------|----------|
对此:
|----------|----------|
| DT | FLAG |
|----------|----------|
| 10 | E |
| 20 | H |
| 30 | E |
| 40 | E |
| 50 | E |
| 60 | S |
| 70 | H |
| 75 | E |
| 80 | H |
| 100 | H |
|----------|----------|
业务规则为:
对于TEST_DATA_SOVLP中的每一行:
-
如果当前
ESD的值不是null，则:
- 在TEMP中插入具有以下值的行:
<ESD value>，E
如果当前TD的值不是null，则:
If the current TD value is not null then:
- 如果
IS_DB=0=>在TEMP中插入以下值:<TD value>，S - 如果
IS_DB=1=>在TEMP中插入以下值:<TD value>，H
- If
IS_DB=0=> insert in TEMP the values:<TD value>,S - If
IS_DB=1=> insert in TEMP the values:<TD value>,H
但是我没有达到我期望的水平:
but I'm not getting anything close to what I'm expecting:
- 在TEMP表中什么也没写.
-
控制台显示的值对我来说没有意义:
- Nothing get written in the TEMP table.
The console display values that make no sense to me:
20小时 10 E 20小时 10 E 20小时 ...
20 H 10 E 20 H 10 E 20 H ...
问题:
-
有人可以告诉我我在proc中做错了什么吗?
Could someone tell me what I'm doing wrong in my proc ?
为什么没有数据存储在TEMP中?
Why no data are stored in TEMP ?
有什么更干净的方法可以解决此问题? 我尝试使用SQL查询(请参见此处)，但未成功. 注意:我要编写多个类似的proc，然后从主" proc调用该proc，以概述逻辑.
What could be a cleaner way to solve this problem ? I tried using a SQL query (see here) without success. Note: I have multiple similar proc to write that I would like then to call from a 'master' proc that will summarize the logic.
谢谢
1.表格中存储了一些数据
CREATE TABLE "TEST_DATA_SOVLP"
( "ID" NUMBER,
"ESD" NUMBER,
"TD" NUMBER,
"IS_DB" NUMBER(1,0) DEFAULT 0,
"TEST_SET" NUMBER
)
某些数据
INSERT INTO "TEST_DATA_SOVLP" (ID, ESD, TD, IS_DB, TEST_SET) VALUES ('1', '10', '20', '1', '2');
INSERT INTO "TEST_DATA_SOVLP" (ID, ESD, IS_DB, TEST_SET) VALUES ('2', '30', '1', '2');
INSERT INTO "TEST_DATA_SOVLP" (ID, ESD, IS_DB, TEST_SET) VALUES ('3', '40', '1', '2');
INSERT INTO "TEST_DATA_SOVLP" (ID, ESD, TD, IS_DB, TEST_SET) VALUES ('4', '50', '60', '1', '2');
INSERT INTO "TEST_DATA_SOVLP" (ID, TD, IS_DB, TEST_SET) VALUES ('5', '70', '1', '2');
INSERT INTO "TEST_DATA_SOVLP" (ID, ESD, TD, IS_DB,TEST_SET) VALUES ('6', '75', '100', '1', '2');
INSERT INTO "TEST_DATA_SOVLP" (ID, TD, IS_DB, TEST_SET) VALUES ('7', '80', '1', '2');
2.用于存储结果的表格
CREATE TABLE "TEMP"
( "DT" NUMBER,
"FLAG" VARCHAR2(1 BYTE)
)
3. PL/SQL处理数据并存储结果
CREATE OR REPLACE PROCEDURE S_OVLP
AS
CURSOR cSH IS
SELECT ID, ESD, TD, IS_DB, TEST_SET
FROM TEST_DATA_SOVLP
WHERE TEST_SET = 2;
rec_csh cSH%ROWTYPE;
BEGIN
-- DBMS_UTILITY.EXEC_DDL_STATEMENT('TRUNCATE TABLE TEMP');
OPEN cSH;
LOOP
FETCH cSH INTO rec_csh;
EXIT WHEN cSH%NOTFOUND;
IF rec_csh.esd IS NOT NULL THEN
INSERT INTO TEMP VALUES (rec_csh.esd, 'E');
dbms_output.put_line(rec_csh.esd || ' E');
END IF;
IF rec_csh.td IS NOT NULL THEN
IF rec_csh.is_db = 1 THEN
INSERT INTO TEMP VALUES (rec_csh.td, 'H');
dbms_output.put_line(rec_csh.td || ' H');
ELSE
INSERT INTO TEMP VALUES (rec_csh.td, 'S');
dbms_output.put_line(rec_csh.td || ' S');
END IF;
END IF;
END LOOP;
CLOSE cSH;
END S_OVLP;
推荐答案
只需运行此INSERT INTO，就不需要过程.
Just run this INSERT INTO , there is no need of procedure.
INSERT INTO temp
SELECT esd,
'E'
FROM test_data_sovlp
WHERE esd IS NOT NULL
UNION ALL
SELECT td,
CASE is_db
WHEN 0 THEN 'S'
WHEN 1 THEN 'H'
END AS FLAG
FROM test_data_sovlp
WHERE td IS NOT NULL
这篇关于Oracle存储过程和游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
