oracle sql制作程序以显示每周排名 [英] oracle sql making procedure to show weekly ranking

问题描述

我制定了根据喜欢和观看次数选择每周排名的程序. 我的sql查询如下:

I made procedure to select weekly rank based on likes and views. my sql query is like this below:

create or replace procedure select_rank
is
begin
  select count(billboard_user.user_idx) as likes, music.hit
  from billboard_user, user_like_music, music
  where billboard_user.user_idx = user_like_music.user_idx
    and user_like_music.music_idx = music.music_idx
  group by  music.hit
  order by count(billboard_user.user_idx) desc, music.hit desc;

end select_rank;
/

我能够执行程序，但是oracle显示错误

I was able to make procedure but oracle shows error

PLS-00428:此SELECT语句中应包含一个INTO子句

所以我在下面尝试了这个

So i tried this below:

create or replace procedure select_rank
  (v_rank number,
  v_title music.title%type,
  v_cnt number,
  v_release_date music.release_date%type,
  v_hit music.hit%type) is
begin
  select rank() over (order by count(billboard_user.user_idx) desc) as rank,
  music.title, music.release_date, count(billboard_user.user_idx) as likes, music.hit
  into v_rank, v_title, v_cnt, v_release_date, v_hit
  from billboard_user, user_like_music, music
  where billboard_user.user_idx = user_like_music.user_idx
    and user_like_music.music_idx = music.music_idx
  group by music.title, music.hit, music.release_date
  order by count(billboard_user.user_idx) desc, music.hit desc;

  DBMS_OUTPUT.PUT_LINE('v_rank: ' || v_rank);
  DBMS_OUTPUT.PUT_LINE('v_title: ' || v_title);
  DBMS_OUTPUT.PUT_LINE('v_cnt: ' || v_cnt);
  DBMS_OUTPUT.PUT_LINE('v_release_date: ' || v_release_date);
  DBMS_OUTPUT.PUT_LINE('v_hit: ' || v_hit);
end select_rank;
/

但是它也不能正常工作.我该如何解决?

But it doesn't work as well. How do I fix it?

推荐答案

对于每个被选择的变量，您都需要一个into:

You need an into for each variable being selected:

declare
  v_title MUSIC.TITLE%type;
  v_cnt number;
  v_hit music.hit%type;
begin
  select music.title, count(melon_user.user_idx) as likes, music.hit
  into v_title, v_cnt, v_hit
  . . .

注意:

• 从不在FROM子句中使用逗号. 始终使用正确的显式JOIN语法.
• 通常，这会将变量存储到存储过程中.
• 您可以只使用视图而不是存储过程来执行所需的操作.

• Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.
• Normally, this would go into out variables for the stored procedure.
• You can possibly do what you want just using a view, rather than a stored procedure.

这篇关于oracle sql制作程序以显示每周排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！