使用 PL/SQL 从表中检索数据 [英] Retrieving the data from the table using the PL/SQL

问题描述

我想从 DEPARTMENT 表中检索每个部门的所有信息，并将这些信息显示在屏幕上.

I want to retrieve all the information about each department from the DEPARTMENT table and display the information on the screen.

Column name      Data type     Constraints

DEPARTMENT_ID    NUMBER(5)     PK

DEPARTMENT_NAME  VARCHAR2(25)  NOT NULL

LOCATION_ID      VARCHAR2(15)

Sample Output:

Department Details are :
1000, ADMIN, HQ-101

1010, DEVELOPMENT, CBE-103

1020, TESTING, CHN-102

我有一个代码如下-

set serveroutput on;
declare
v_dno department.department_id%type;
v_dname department.department_name%type;
v_loc department.location_id%type;
begin
dbms_output.put_line('Department Details are :');
loop
dbms_output.put_line(v_dno || ', ' || v_dname || ', ' || v_loc);
end loop;
commit;
end;
/

但这不会产生任何输出，请帮忙.提前致谢！

But this isn't producing any output, please help. Thanks in advance!

推荐答案

是的，一个简单的方法是使用循环.但是，您正在循环nothing(永远不要将任何内容提取到这些变量中)并且永​​远不会退出循环.此外，你到底在做什么?

Yes, a simple way to do that is to use a loop. But, you're looping through nothing (never fetch anything into those variables) and never exit the loop. Besides, what exactly are you committing?

以下是您可能的做法(基于 Scott 的 DEPT 表，该表与您的表类似):

Here's how you might have done it (based on Scott's DEPT table which is similar to yours):

SQL> set serveroutput on
SQL> begin
  2    for cur_r in (select deptno, dname, loc from dept) loop
  3      dbms_output.put_line(cur_r.deptno ||' '|| cur_r.dname ||' '|| cur_r.loc);
  4    end loop;
  5  end;
  6  /
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

PL/SQL procedure successfully completed.

SQL>

这篇关于使用 PL/SQL 从表中检索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！