在 Oracle 中查看带有参数的查询结果

发布于 2024-10-18 04:02:22 字数 400 浏览 4 评论 0原文

我需要运行大查询（这是 SP 的一部分）并查看它们的结果（只是试图在具有许多联合的大 SP 中找到错误。我想将其分成几个部分并单独运行它们）。如果这个 SP 的参数很少，我该怎么做？我不想在代码中替换它们，最好在标头中添加声明并对此参数进行硬编码。

我尝试过这样的事情：

DECLARE

p_asOfDate DATE :=  '22-Feb-2011';

BEGIN

SELECT * from myTable where dateInTable < p_asOfDate;

END

但它说我应该使用 INTO 关键字。如何在 IDE 中查看此结果？（我正在使用 Aqua data studio）

我需要经常这样做，所以如果能找到一个简单的解决方案，我会非常高兴

原文

I need to run big queries (that was a part of SP) and look at their results (just trying to find a bug in a big SP with many unions. I want to break it into parts and run them separately).
How can I do that if this SP have few parameters? I don't want to replace them in code, it would be great just to add declare in a header with a hardcode for this parameter.

I've tried something like this:

DECLARE

p_asOfDate DATE :=  '22-Feb-2011';

BEGIN

SELECT * from myTable where dateInTable < p_asOfDate;

END

But it says that I should use INTO keyword. How can I view this results in my IDE? (I'm using Aqua data studio)

I need to do that very often, so will be very happy if will find a simple solution

分享到QQ

分享到微博

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

发布评论

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

心作怪 2024-10-25 04:02:22

您正在使用匿名的 pl/sql 代码块。
在 pl/sql 过程中，您需要为结果指定目标变量。

所以你首先需要定义一个变量来保存声明部分的结果
然后将结果数据插入其中。

DECLARE
  p_asOfDate DATE :=  '22-Feb-2011';
  p_result myTable%ROWTYPE;
BEGIN
  select * into p_result from myTable where dateInTable < p_asOfDate;
END

也就是说，您可能会返回不止一行，所以我会使用
单独获取行的游标。

DECLARE
  CURSOR c_cursor (asOfDate IN DATE) is 
    select * from myTable where dateInTable < asOfDate;
  p_asOfDate DATE :=  '22-Feb-2011';
  p_result myTable%ROWTYPE;
BEGIN
  OPEN c_cursor(p_asOfDate);
    loop
      FETCH c_cursor into p_result;
      exit when c_cursor%NOTFOUND;
      /* do something with the result row here */
    end loop;
  CLOSE c_cursor;
END

要输出结果，您可以使用类似这样的内容：

dbms_output.put_line('some text' || p_result.someColumn);

或者您可以在 sql 命令行（如 sqlplus）上执行查询
并立即以表格形式获取结果。

我希望我正确理解你的问题...

更新

这是注入测试数据的不同方法：

使用你的工具sql执行环境直接提交sql语句，而不需要pl/sql块。

使用“&”在变量部分前面触发变量提示。

select * from myTable where dateInTable < &p_asOfDate;

结果应该由您的工具以这种方式以格式化的方式显示。
我不知道 Aqua，但有些工具具有在 sql 代码之外定义这些参数的功能。

You are using an anonymous block of pl/sql code.
In pl/sql procedures you need to specify a target variable for the result.

So you first need to define a variable to hold the result in the declare section
and then insert the result data into it.

DECLARE
  p_asOfDate DATE :=  '22-Feb-2011';
  p_result myTable%ROWTYPE;
BEGIN
  select * into p_result from myTable where dateInTable < p_asOfDate;
END

That said you will probaply get more than one row returned, so I would use
a cursor to get the rows separately.

DECLARE
  CURSOR c_cursor (asOfDate IN DATE) is 
    select * from myTable where dateInTable < asOfDate;
  p_asOfDate DATE :=  '22-Feb-2011';
  p_result myTable%ROWTYPE;
BEGIN
  OPEN c_cursor(p_asOfDate);
    loop
      FETCH c_cursor into p_result;
      exit when c_cursor%NOTFOUND;
      /* do something with the result row here */
    end loop;
  CLOSE c_cursor;
END

To output the results you can use something like this for example:

dbms_output.put_line('some text' || p_result.someColumn);

Alternatively you can execute the query on an sql command-line (like sqlplus)
and get the result as a table immediately.

I hope I understood your question correctly...

update

Here is a different way to inject your test data:

Use your tools sql execution environemnt to submit your sql statement directly without a pl/sql block.

Use a "&" in front of the variable part to trigger a prompt for the variable.

select * from myTable where dateInTable < &p_asOfDate;

The Result should be displayed in a formatted way by your tool this way.
I do not know about Aqua, but some tools have functions to define those parameters outside the sql code.

回复收藏 0 原文

~没有更多了~