使用 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!

答

IN SQL 如果要从表、视图等中检索数据，则需要使用 SELECT 语句.在您的匿名块中，您没有使用选择，而是在循环空变量.所以你需要像这样在你的代码中添加选择(你也可以用声明的游标来做，但在这种情况下，我在 FOR 上做了它):

IN SQL if you want to retrieve data from a table, view, etc. You need to use the SELECT statement. In your anonymous block you aren't using the select, and you're looping the empty variables. So you need to add the select in your code like this (You can do it with a declared cursor too, but in this case i made it in line on the FOR):

set serveroutput on;
begin
dbms_output.put_line('Department Details are :');
for c in (select department_id, department_name, location_id from department) loop
  dbms_output.put_line(c.department_id|| ', ' || c.department_name|| ', ' || 
  c.location_id);
end loop;
commit;
end;

由于您使用的是游标，因此根本不需要声明和使用变量.

Since you are using a cursor, there is no need to declare and use variables at all.

使用 PL/SQL 从表中检索数据

相关推荐