正文
Oracle数据库单表循环提取输出
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
现在有如下的表，名称为Test表：
ydid sws_dm sws_mc ry_dm ry_mc
1 1 第一税务所 100 张飞
2 1 第一税务所 101 赵云
3 1 第一税务所 102 马超
4 1 第一税务所 103 黄忠
5 1 第一税务所 104 关羽
6 2 第二税务所 200 程昱
7 2 第二税务所 201 贾诩
8 2 第二税务所 202 郭嘉
9 2 第二税务所 203 荀彧
10 2 第二税务所 204 荀攸
11 3 第三税务所 300 司马懿
12 3 第三税务所 301 曹洪
13 3 第三税务所 302 曹仁
14 3 第三税务所 303 夏侯渊
15 3 第三税务所 304 阿速达
16 4 第四税务所 401 黄月英
17 4 第四税务所 402 庞统
18 4 第四税务所 403 马岱
19 4 第四税务所 404 刘备
20 5 第五税务所 500 陆逊
想形成如下的表结构，就是以sws_dm字段进行过滤，得出人员总数统计和人员名称：
sws_dm sws_mc ry_mc 总
1 第一税务所 张飞；赵云；马超；黄忠；关羽 5
2 第二税务所 程昱；贾诩；郭嘉；荀彧；荀攸 5
3 第三税务所 司马懿；曹洪；曹仁；夏侯渊；阿速达 5
4 第四税务所 黄月英；庞统；马岱；刘备 4
5 五税务所 陆逊 1
从上面，我们的出两种解决方法（在Oracle 11G中）：
select sws_dm, sws_mc,
max(replace(substr(sys_connect_by_path(ry_mc, '*'), 2),'*',';')),count(*) 总人数
from(
select sws_dm, sws_mc,ry_mc,
dense_rank()over(order by sws_dm) + row_number()over(order by sws_dm) rid,
row_number() over (partition by sws_dm order by sws_dm) nid
from tab)
start with nid = 1
connect by prior rid = rid - 1
GROUP BY sws_dm,sws_mc;
在Oracle 10G中的语句如下：
select sws_dm, sws_mc, replace(wm_concat(ry_mc),',',';')ry_mc,count(*) 总人数
from tab
group by sws_dm, sws_mc;
然后另外一个方法就是利用SQL函数来解决：
具体做法如下：
create or replace FUNCTION JYH_ZHRY(BMID IN VARCHAR2)
---------------------------------------------------------------
--★ 将定位表里的数据转成单行
--★ WRITTEN BY XIASHITONG
---------------------------------------------------------------
RETURN VARCHAR2 IS
CURSOR C_ZBID IS
SELECT A.Ry_Mc || ';' Ry_Mc
FROM DB_QPGL.JYH_SYJKFK_YDQK A
WHERE A.Sws_Dm =BMID;
V_SQL VARCHAR2(5000) := '';
BEGIN
FOR C_R IN C_ZBID LOOP
V_SQL := V_SQL || C_R.Ry_Mc;
END LOOP;
IF V_SQL IS NOT NULL THEN
V_SQL := RTRIM(V_SQL, ',');
END IF;
RETURN V_SQL;
END;
然后在ORACLE中进行SQL语句的调用：
Select SWS_MC,COUNT(1),SWS_DM SL,DB_QPGL.JYH_ZHRY(SWS_DM) FROM DB_QPGL.JYH_SYJKFK_YDQK GROUP BY SWS_MC,SWS_DM
以下是转来的一片文章，大体上说明oracle的Function的运用：
方法一:帶入与表同形态的参数
CREATE OR REPLACE FUNCTION F_Get_Cust_Name (V_NO IN CUSTOMER.NO%TYPE,V_COMPNO IN CUSTOMER.COMPNO%TYPE)
RETURN CUSTOMER.NAME%TYPE
IS
V_NAME CUSTOMER.NAME%TYPE;
BEGIN
SELECT CUSTOMER.NAME INTO V_NAME
FROM CUSTOMER
WHERE
NO=V_NO AND COMPNO=V_COMPNO ;
RETURN V_NAME;
EXCEPTION WHEN NO_DATA_FOUND THEN RETURN ' '; ---錯誤回空白
END;
方法二:带入自定义类型的参数
CREATE OR REPLACE FUNCTION F_Get_Cust_Name (V_NO IN VARCHAR2,V_COMPNO IN VARCHAR2)
RETURN VARCHAR2
IS
V_NAME VARCHAR2;
BEGIN
SELECT CUSTOMER.NAME INTO V_NAME
FROM CUSTOMER
WHERE
NO=V_NO AND COMPNO=V_COMPNO ;
RETURN V_NAME;
EXCEPTION WHEN NO_DATA_FOUND THEN RETURN ' '; ---錯誤回空白
END;
例：
--1为正确登录
--2为密码不正确
--3为账户不存在
--返回数值型
create or replace function GetAccount(acc_id in MEMBERS.ACCOUNT_ID%type,acc_pwd in MEMBERS.ACCOUNT_PASSWORD%type)
return number
is
v_result number;
v_count number;
v_acc_pwd MEMBERS.ACCOUNT_PASSWORD%type;
begin
select count(*) as count, MEMBERS.ACCOUNT_PASSWORD into v_count,v_acc_pwd
from MEMBERS
where MEMBERS.ACCOUNT_ID=acc_id
group by MEMBERS.ACCOUNT_PASSWORD;
if v_count>0
then
if v_acc_pwd=acc_pwd then
v_result:=1;
else
v_result:=2;
end if;
else
v_result:=3;
end if;
return v_result;
EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 3;
end;
