oracle - 在 oracle 中查询时以数组形式回答
问题描述
我正在开发对休息请求的回复,我几乎完成了,但我无法完成数组中的对象列表。我有一个返回 json 的代码,这是我的代码:
DECLARE
v_count varchar2(2000);
v_id USERS_C.id%TYPE;
v_users_name USERS_C.name%TYPE;
v_organization_id varchar2(2000);
v_organization_name ORGANIZATIONS.NAME%TYPE;
v_role varchar2(2000);
CURSOR c_event IS SELECT
id,
users_name,
organization_id,
organization_name,
role
FROM
(SELECT
a.*,
ROWNUM rnum
FROM
(SELECT
ev.id,
ev.login users_name ,
ds.id organization_id,
ds.NAME organization_name,
cv.role_names role
FROM ORGANIZATIONS_USERS lo
LEFT JOIN users_c ev ON ev.ID = lo.USER_ID
LEFT JOIN ORGANIZATIONS ds ON lo.ORGANIZATION_ID = ds.id
LEFT JOIN APEX_APPL_ACL_USERS cv ON ev.login = cv.USER_NAME
) a
);
BEGIN
SELECT
COUNT(*)
INTO
v_count
FROM users_c ;
IF v_count = 0 THEN
apex_json.open_object;
apex_json.write('success', false);
apex_json.write('message', 'No data found');
apex_json.write('count', 0);
apex_json.close_object;
return;
END IF;
OPEN c_event;
apex_json.open_object;
apex_json.write('count',v_count);
apex_json.open_array('items');
LOOP
FETCH c_event INTO v_id,v_users_name,v_organization_id,v_organization_name,v_role;
EXIT WHEN c_event%notfound;
apex_json.open_object;
apex_json.write('id', v_id);
apex_json.write('user', v_users_name );
apex_json.open_array('datasectors');
apex_json.write('id', v_organization_id);
apex_json.write('name', v_organization_name);
apex_json.close_array;
apex_json.open_object('roles');
apex_json.write('role', v_role);
apex_json.close_object;
apex_json.close_object;
END LOOP;
apex_json.close_all;
CLOSE c_event;
End;
他返回这个答案:
"items": [
{
"id": 2,
"user": "andrii",
"datasectors": [
"id": "21",
"name": "TOW3"
],
"roles": {
"role": "Admin"
}
},
{
"id": 2,
"user": "andrii",
"datasectors": [
"id": "122",
"name": "TOW2""
],
"roles": {
"role": "Admin"
}
},
{
"id": 2,
"user": "andrii",
"datasectors": [
"id": "62",
"name": "TOW1""
],
"roles": {
"role": "Admin"
}
你看,我的答案的数量取决于我所在的数据扇区的数量。我希望每个人都在数组中,这是正确的
"items": [
{
"id": 2,
"user": "andrii",
"datasectors": [{
"id": "21",
"name": "TOW3"
},
{
"id": "122",
"name": "TOW2""
},
{
"id": "62",
"name": "TOW1"
}
],
"roles": {
"role": "Admin"
}
}
这是我所知道的所有束缚我的组织的请求
SELECT
ds.id organization_id,
ds.NAME organization_name
FROM ORGANIZATIONS_USERS lo
LEFT JOIN users_c ev ON ev.ID = lo.USER_ID
LEFT JOIN ORGANIZATIONS ds ON lo.ORGANIZATION_ID = ds.id
where df.login = 'andrii'
我必须把那个答案放进去
apex_json.open_array('datasectors');
apex_json.write('id', v_organization_id);
apex_json.write('name', v_organization_name);
apex_json.close_array;
我的桌子
Table :users_c
id login
2 Andrii
3 Ira
Table :ORGANIZATIONS_USERS
id USER_ID ORGANIZATION_ID
1 2 21
2 2 122
3 2 62
4 3 122
5 3 62
Table :ORGANIZATIONS
id Name
21 Tow1
122 Tow2
62 Tom3
Table : APEX_APPL_ACL_USERS
USER_NAME ROLE_NAMES
Andrii Admіn, Operator
Ira Admіn
解决方案
这是基本思想:
declare
v_count number;
begin
select count(*)
into v_count
from users_c;
if v_count = 0 then
apex_json.open_object;
apex_json.write('success', false);
apex_json.write('message', 'No data found');
apex_json.write('count', 0);
apex_json.close_object;
return;
end if;
apex_json.open_object;
apex_json.write('count', v_count);
apex_json.open_array('items');
for user_rec in (
select id,
login
from users_c
)
loop
apex_json.open_object;
apex_json.write('id', user_rec.id);
apex_json.write('user', user_rec.login);
apex_json.open_array('datasectors');
for org_rec in (
select id,
name
from organizations o
where id in (
select organization_id
from organization_users
where user_id = user_rec.id
)
)
loop
apex_json.open_object;
apex_json.write('id', org_rec.id);
apex_json.write('name', org_rec.name);
apex_json.close_object;
end loop;
apex_json.close_array; -- datasectors
apex_json.close_object;
end loop;
apex_json.close_array; -- items
apex_json.close_object;
end;
如果您在 18c 上使用 ORDS,则使用 SQL 可能有更好的解决方案,但上述方法通常可以工作。
我能发现的唯一问题是,对 users_c 进行计数的第一个查询可能会得到与同一张表上的第二个查询不同的数字。有不同的方法可以解决这个问题,但如果重要的话,我会把这个练习留给你。
看看这个系列的更多选项: https ://jsao.io/2015/07/relational-to-json-in-oracle-database/
推荐阅读
- php - 在laravel中将多个数组转换为字符串
- scala - 无法重定向到外部 url
- xamarin.forms - 如何在 xamarin 表单中创建带有不可点击标题的列表视图
- python - 系列。最大值和 idxmax
- python - 使用 python speech_recognition 播放和流式转录音频
- r - 使用 R 的 quanteda 情绪 wordcloud?
- ajax - Ember 适配器通过 Ajax Post 接收数据
- grafana - 如何仅在普罗米修斯的白天获得平均值
- graphql - 如何在 apollo 的 graphql-gql 中使用从 npm 包导入的自定义 graphQL 类型
- jquery - 使用 CSS 转换属性时 jQuery 动画卡顿