SQL查询中的游标值

发布于 2024-10-03 18:06:04 字数 832 浏览 4 评论 0原文

I have two tables  
1.
shiv_tab_col

TABLENAME     | COLUMNNAME
--------------------------  
 SER_SHIV_SYN | TABLENAME    
 SER_SHIV_SYN | COLUMNNAME


AND SER_SHIV_SYN

TABLENAME   | COLUMNNAME
--------------------------  
 A          |  B  
 E          | NULL  
NULL        | NULL  
NULL        | NULL  
NULL        | NULL  
NULL        | NULL  

BUT WHEN I M TRYING TO RUN THE BELOW CODE IT IS GIVING ME '0' AS VALUE OF TNAME

DECLARE
tname varchar(20):=null;

 CURSOR C1 IS
    SELECT  *
    FROM SHIV_TAB_COL;


BEGIN
    for rec in C1

loop
    select count(*) into tname from (select nvl(rec.columnname,1)b from ser_shiv_syn) where b ='1';
    dbms_output.put_line(tname);
    dbms_output.put_line(rec.columnname);
END LOOP;
END;

其实我想数数。每列中的空值将存储在变量 TNAME
对于第一列，它应该给出 4
对于 2 列，它应该给出 5

原文

I have two tables  
1.
shiv_tab_col

TABLENAME     | COLUMNNAME
--------------------------  
 SER_SHIV_SYN | TABLENAME    
 SER_SHIV_SYN | COLUMNNAME


AND SER_SHIV_SYN

TABLENAME   | COLUMNNAME
--------------------------  
 A          |  B  
 E          | NULL  
NULL        | NULL  
NULL        | NULL  
NULL        | NULL  
NULL        | NULL  

BUT WHEN I M TRYING TO RUN THE BELOW CODE IT IS GIVING ME '0' AS VALUE OF TNAME

DECLARE
tname varchar(20):=null;

 CURSOR C1 IS
    SELECT  *
    FROM SHIV_TAB_COL;


BEGIN
    for rec in C1

loop
    select count(*) into tname from (select nvl(rec.columnname,1)b from ser_shiv_syn) where b ='1';
    dbms_output.put_line(tname);
    dbms_output.put_line(rec.columnname);
END LOOP;
END;

Actually i m looking to count the no. of null values in each column which will be stored in variable TNAME
for first column it should give 4
for 2 column it should give 5

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

凉城 2024-10-10 18:06:05

只是一个小提示：对于您的光标，请使用此：

SELECT  column_name 
FROM    all_tab_columns
WHERE   table_name = 'SER_SHIV_SYN'

而不是 shiv_tab_col。

该表会自动更新并且始终存在。
它还具有字段类型、大小等。一切皆为 0 成本。双赢。

Just a small hint: For your cursor use this:

SELECT  column_name 
FROM    all_tab_columns
WHERE   table_name = 'SER_SHIV_SYN'

Instead of shiv_tab_col.

This table is automatically updated and it's always in there.
It also has field type, size, etc. Everything and more for 0 cost. Win-Win.

回复收藏 0 原文

紫﹏色ふ单纯 2024-10-10 18:06:04

您现在要做的是计算表 shiv_tab_col 中列 COLUMNNAME 的所有空出现次数。哪个是 0（你已经发现了）。

解决方案有点复杂，你需要动态SQL来完成你想做的事情：

declare
  l_count pls_integer;
begin
  for r_cur in (
    select columnname
    from shiv_tab_col
  )
  loop
    execute immediate 
      'select count(1) from ser_shiv_syn where '||r_cur.columnname||' is null' 
    into l_count;
    dbms_output.put_line(l_count);
    dbms_output.put_line(r_cur.columnname);
  end loop;
end;
/

What you are doing now is counting all empty occurrences of the column COLUMNNAME in the table shiv_tab_col. Which is 0 (you already found that out).

The solution is a bit more complicated, you need dynamic SQL to accomplish what you want to do:

declare
  l_count pls_integer;
begin
  for r_cur in (
    select columnname
    from shiv_tab_col
  )
  loop
    execute immediate 
      'select count(1) from ser_shiv_syn where '||r_cur.columnname||' is null' 
    into l_count;
    dbms_output.put_line(l_count);
    dbms_output.put_line(r_cur.columnname);
  end loop;
end;
/

回复收藏 0 原文

~没有更多了~