SQL小提琴输出误差SQL小提琴输出误差(SQL Fiddle Output Error)

其实我很新的PL / SQL

我在SQL小提琴使用Oracle PL / SQL创建以下表

create table Employee(name varchar2(100),id integer, salary integer,PRIMARY KEY(id));
insert into Employee(name,id,salary) values('sa',94,100);
insert into Employee(name,id,salary) values('pr',88,150);
insert into Employee(name,id,salary) values('ji',33,900);
insert into Employee(name,id,salary) values('na',24,880);
insert into Employee(name,id,salary) values('po',65,770);
insert into Employee(name,id,salary) values('ri',69,910);
insert into Employee(name,id,salary) values('uj',12,650);
insert into Employee(name,id,salary) values('ad',43,440);
insert into Employee(name,id,salary) values('sam',40,550);

我执行下面的查询

 DECLARE
 employee_record Employee%ROWTYPE;
 BEGIN
 select * into employee_record from Employee where id>90;
 dbms_output.put_line(employee_record.name||' '||employee_record.id||' '||employee_record.salary);
 END;
 /

我得到下面的输出

Record Count: 0; Execution Time: 2ms 

它应该打印存在于雇员记录的值，对不对？ 有什么错误的，我的SQL查询或一些问题与SQL拨弄无法显示DBMS_OUTPUT？

Answer 1:

你需要效仿dbms_output.put_line :)

架构：

create table Employee(
  name varchar2(100),
  id integer, 
  salary integer,
  PRIMARY KEY(id)
);

insert into Employee(name,id,salary) values('sa',94,100);
insert into Employee(name,id,salary) values('pr',88,150);
insert into Employee(name,id,salary) values('ji',33,900);
insert into Employee(name,id,salary) values('na',24,880);
insert into Employee(name,id,salary) values('po',65,770);
insert into Employee(name,id,salary) values('ri',69,910);
insert into Employee(name,id,salary) values('uj',12,650);
insert into Employee(name,id,salary) values('ad',43,440);
insert into Employee(name,id,salary) values('sam',40,550);

create table dbmsoutput (
  pos int,
  mes varchar2(4000)
);

---

SQL：

DECLARE
  employee_record Employee%ROWTYPE;
  procedure put_line(p_mes in varchar2) is
     v_pos int; 
  begin  
     select count(0) into v_pos from dbmsoutput;  
     insert into dbmsoutput (pos, mes) values (v_pos, p_mes);
  end;
BEGIN
 put_line('Hello!  This code is powered by dbms_output emulator :)');
 -- Your code here:
 select * into employee_record from Employee where id>90;
 put_line(employee_record.name||' '||employee_record.id||' '||employee_record.salary);
 --
 put_line('Bye!');
END;
/


SELECT mes FROM dbmsoutput order by pos

小提琴

Answer 2:

正如好奇心真的，你可以得到有限的dbms_output从SQL小提琴的结果，但你需要一个函数来提取缓冲线和可以选择的形式归还。 这采用流水线表：

create type t_lines as table of varchar2(4000)
/

create or replace function get_lines
return t_lines pipelined is
  lines dbms_output.chararr;
  numlines integer;
begin
  numlines := 999;
  dbms_output.get_lines(lines, numlines);
  if numlines > 0 then
    for i in 1..numlines loop
      pipe row (lines(i));
    end loop;
  else
    pipe row ('No data');
  end if;
end;
/

然后，无论你在发出后dbms_output.put_line电话：

select * from table(get_lines);

演示 。 而看到dbms_output文档 ，看看它有什么get_lines过程确实和它如何与您的put_lines电话。

但是，仅仅因为你可以做一些事情，并不意味着你一定应该 。 这是尴尬和不结垢，但也确实在努力学习PL / SQL通过SQL小提琴真的。

我第二次奔的建议，让您自己的数据库一起玩，但我建议你看一个预建VM图像可以在VirtualBox中运行，这样可以节省大量的时间在设置-你不不用担心如何安装Oracle软件或创建和配置数据库，它只是准备使用，你可以把它扔掉或者容易重新开始，如果出了问题。

文章来源: SQL Fiddle Output Error