英文:
Accepting conditional input for PLSQL
问题
使用Oracle SQL在SQL Developer中：
我试图将我的团队用于查找信息的几个查询打包在一起以进行验证。我有一个查询，返回给定客户的大量信息。返回的列将始终相同（cust_id，name，zip_cd），但用户搜索客户的方式会发生变化。有时我们可以直接搜索客户ID，有时只有姓名和zip_cd。
是否有一种方法可以创建一个单一的查询，其中提示用户他们想要如何搜索（即通过cust_id还是通过name和zip_cd），然后提示用户根据他们先前的输入输入cust_id或name和zip_cd？
此外，最好的情况是我希望这可以显示在结果窗格中，就像正常查询一样，但到目前为止，我想到的最好的解决方案是使用游标。如果您对如何做到这一点有任何建议，我将非常感激！
这类似于我的第一个查询：
SET SERVEROUTPUT ON
ACCEPT choice_prompt PROMPT 'Enter seach criteria: (1) cust_id (2) name and zip_cd: ';
ACCEPT cust_id_prompt PROMPT 'Enter cust_id: ';
ACCEPT cust_name_prompt PROMPT 'Enter customer name: ';
ACCEPT cust_zip_prompt PROMPT 'Enter customer zip cd: ';
BEGIN
IF &choice_prompt = '1' THEN
dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
FOR r_product IN (
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE a.cust_id = &cust_id_prompt
)
LOOP
dbms_output.put_line( a.cust_id ||chr(9)|| c.cust_nm || chr(9) || b.cust_zip );
END LOOP;
ELSIF &choice_prompt = '2' THEN
dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
FOR r_product IN (
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE b.cust_zip = &cust_zip_prompt AND c.cust_nm = &cust_name_prompt
)
LOOP
dbms_output.put_line( a.cust_id ||chr(9)|| c.cust_nm || chr(9) || b.cust_zip );
END LOOP;
ELSE
dbms_output.put_line('There were errors.');
END IF;
END;
但它一直提示我输入所有内容，而不仅仅是所需的内容。然后我尝试嵌入接受提示到相关选择中，但无法使其工作：
SET SERVEROUTPUT ON
ACCEPT choice_prompt PROMPT 'Enter seach criteria: (1) cust_id (2) name and zip_cd: ';
BEGIN
IF &choice_prompt = '1' THEN
ACCEPT cust_id_prompt PROMPT 'Enter cust_id: ';
dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
FOR r_product IN (
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE a.cust_id = &cust_id_prompt
)
LOOP
dbms_output.put_line( a.cust_id ||chr(9)|| c.cust_nm || chr(9) || b.cust_zip );
END LOOP;
ELSIF &choice_prompt = '2' THEN
ACCEPT cust_name_prompt PROMPT 'Enter customer name: ';
ACCEPT cust_zip_prompt PROMPT 'Enter customer zip cd: ';
dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
FOR r_product IN (
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE b.cust_zip = &cust_zip_prompt AND c.cust_nm = &cust_name_prompt
)
LOOP
dbms_output.put_line( a.cust_id ||chr(9)|| c.cust_nm || chr(9) || b.cust_zip );
END LOOP;
ELSE
dbms_output.put_line('There were errors.');
END IF;
END;
然后我尝试只是使用输入变量，但它仍然提示输入所有内容，而不仅仅是所需的内容：
SET SERVEROUTPUT ON
ACCEPT choice_prompt PROMPT 'Enter seach criteria: (1) cust_id (2) name and zip_cd: ';
DECLARE
enter_cust_id number;
enter_cust_name varchar2(20);
enter_cust_zip_cd varchar2(5)
BEGIN
IF &choice_prompt = '1' THEN
dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
FOR r_product IN (
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE a.cust_id = &enter_cust_id
)
LOOP
dbms_output.put_line( a.cust_id ||chr(9)|| c.cust_nm || chr(9) || b.cust_zip );
END LOOP;
ELSIF &choice_prompt = '2' THEN
dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
FOR r_product IN (
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE b.cust_zip = &enter_cust_zip_cd AND c.cust_nm = &enter_cust_name
)
LOOP
dbms_output.put
<details>
<summary>英文:</summary>
Using Oracle SQL in SQL Developer:
I am trying to package together a couple queries my team uses to lookup information to verify with. I have a query that returns a bunch of information on a given customer. The returned columns will always be the same (cust_id, name, zip_cd) but what changes is how the user searches for the customer. Sometimes we have a customer ID we can search on directly, sometimes it's just name and zip_cd.
Is there a way I can create a single query where it prompts the user how they want to search (ie by cust_id OR by name and zip_cd) and then prompts the user to enter the cust_id OR name and zip_cd depending on their previous input?
Also, ideally I would LOVE for this to be displayed in the results pane like a normal query, but the best solution I could come up with so far was to use a cursor. If you have any input on how I can do that it would be greatly appreciated!
This was something like my first query:
SET SERVEROUTPUT ON
ACCEPT choice_prompt PROMPT 'Enter seach criteria: (1) cust_id (2) name and zip_cd: ';
ACCEPT cust_id_prompt PROMPT 'Enter cust_id: '
ACCEPT cust_name_prompt PROMPT 'Enter customer name: '
ACCEPT cust_zip_prompt PROMPT 'Enter customer zip cd: '
BEGIN
IF &choice_prompt = '1' THEN
dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
FOR r_product IN (
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE a.cust_id = &cust_id_prompt
)
LOOP
dbms_output.put_line( a.cust_id ||chr(9)|| c.cust_nm || chr(9) || b.cust_zip );
END LOOP;
ELSIF &choice_prompt = '2' THEN
dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
FOR r_product IN (
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE b.cust_zip = &cust_zip_prompt AND c.cust_nm = &cust_name_prompt
)
LOOP
dbms_output.put_line( a.cust_id ||chr(9)|| c.cust_nm || chr(9) || b.cust_zip );
END LOOP;
ELSE
dbms_output.put_line('There were errors.');
END IF;
END;
But it kept prompting me for everything instead of just what was needed. So then I tried embedding the ACCEPT PROMPT into the relevant choices like so, but couldn't get it to work:
SET SERVEROUTPUT ON
ACCEPT choice_prompt PROMPT 'Enter seach criteria: (1) cust_id (2) name and zip_cd: ';
BEGIN
IF &choice_prompt = '1' THEN
ACCEPT cust_id_prompt PROMPT 'Enter cust_id: '
dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
FOR r_product IN (
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE a.cust_id = &cust_id_prompt
)
LOOP
dbms_output.put_line( a.cust_id ||chr(9)|| c.cust_nm || chr(9) || b.cust_zip );
END LOOP;
ELSIF &choice_prompt = '2' THEN
ACCEPT cust_name_prompt PROMPT 'Enter customer name: '
ACCEPT cust_zip_prompt PROMPT 'Enter customer zip cd: '
dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
FOR r_product IN (
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE b.cust_zip = &cust_zip_prompt AND c.cust_nm = &cust_name_prompt
)
LOOP
dbms_output.put_line( a.cust_id ||chr(9)|| c.cust_nm || chr(9) || b.cust_zip );
END LOOP;
ELSE
dbms_output.put_line('There were errors.');
END IF;
END;
So then I tried just using input variables and that executed, but it still prompted for everything instead of just what was needed:
SET SERVEROUTPUT ON
ACCEPT choice_prompt PROMPT 'Enter seach criteria: (1) cust_id (2) name and zip_cd: ';
DECLARE
enter_cust_id number;
enter_cust_name varchar2(20);
enter_cust_zip_cd varchar2(5)
BEGIN
IF &choice_prompt = '1' THEN
dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
FOR r_product IN (
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE a.cust_id = &enter_cust_id
)
LOOP
dbms_output.put_line( a.cust_id ||chr(9)|| c.cust_nm || chr(9) || b.cust_zip );
END LOOP;
ELSIF &choice_prompt = '2' THEN
dbms_output.put_line('cust_id'||chr(9)||'cust_nm'||chr(9)||'cust_zip');
FOR r_product IN (
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE b.cust_zip = &enter_cust_zip_cd AND c.cust_nm = &enter_cust_name
)
LOOP
dbms_output.put_line( a.cust_id ||chr(9)|| c.cust_nm || chr(9) || b.cust_zip );
END LOOP;
ELSE
dbms_output.put_line('There were errors.');
END IF;
END;
</details>
# 答案1
**得分**: 1
以下是代码部分的中文翻译：
如果您只是按照原样运行带有替代变量的查询在SQL Developer中，它会自动提示您填写它们。根本不需要使用像 `ACCEPT ... PROMPT` 这样的脚本命令。一旦您这样做，您就会受限于来自脚本的文本输出，无法使用正常的结果工作表或其他结果集工具。
```sql
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE a.cust_id = &enter_cust_id;
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE b.cust_zip = &enter_cust_zip_cd AND c.cust_nm = &enter_cust_name;
您可以将查询保留在同一个文件中，只需选择并运行您想要的查询。您将在正常的结果窗格中按预期获得结果，而无需处理文本输出（再次强调，DBMS_OUTPUT 实际上并不适用于您所使用的用途）。只需确保使用 "运行语句" 按钮（绿色箭头）来运行特定命令（因此不需要注释掉任何内容），而不是 "运行脚本（F5）" 按钮。
也许这已经是您正在做的事情，如果是这样，那么在SQL Developer中真的没有更多要做的事情。正如已经指出的，PL/SQL 实际上并不是构建用户界面的语言。它更多地用于构建支持用其他语言编码的用户界面的API。如果您需要一个真正的用户界面，请考虑使用Python或Oracle Application Express（APEX），后者是包含在您的Oracle数据库中的低/无代码解决方案。
英文:
If you simply run the queries as is - with the substitution variables - in SQL Developer, it will prompt you automatically to fill them. There is no need to use scripting commands like ACCEPT ... PROMPT at all. As soon as you do that you are limited to text output from the script and have no ability to use the normal results worksheet or other result set tools.
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE a.cust_id = &enter_cust_id;
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE b.cust_zip = &enter_cust_zip_cd AND c.cust_nm = &enter_cust_name;
You can keep the queries in the same file, and just select and run the query you want specifically. You would get your results in the normal results pane as desired and not have to deal with text output (again, DBMS_OUTPUT really isn't intended for the purpose you have used it). Just be sure to use the "Run Statement" button (green arrow) to run a specific command (so no need to comment things out) and not the "Run Script (F5)" button.
Maybe this is what you are already doing, in which case there really isn't much more to be done within SQL Developer. As has been pointed out, PL/SQL isn't really the sort of language you can build a user interface with. It's more about building an API to support a UI coded in some other language. If you need a real user interface, look at Python or Oracle Application Express (APEX), a low/no-code solution which is included with your Oracle database.
答案2
得分: 0
通常，PL/SQL 不太适合需要与用户进行交互的任务 - 像 Python 这样的语言会更容易处理。不过，在这种情况下，你也许不需要这两者。你可以尝试像下面这样做：
ACCEPT cust_id_prompt PROMPT '输入客户 ID： '
ACCEPT cust_name_prompt PROMPT '输入客户姓名： '
ACCEPT cust_zip_prompt PROMPT '输入客户邮编： '
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE (a.cust_id = &cust_id_prompt or '&cust_id_prompt' is null)
AND ((b.cust_zip = &enter_cust_zip_cd and c.cust_nm = &enter_cust_name) or '&enter_cust_zip_cd' is null)
AND ('&cust_id_prompt' is not null or '&enter_cust_zip_cd' is not null);
基本上，如果我提供输入，它将用于搜索。最后一行只是为了确保你不会输入空值并得到整个表格。
使用 Python 等其他语言可能会提供其他优势（更容易编写单元测试、更好的用户界面等），但如果你希望在 SQL*PLUS 中快速实现解决方案，这个方法可能会对你有所帮助。
英文:
Generally PL/SQL is not great for things that need to be interactive with the user - something like Python will be much easier to work with. However for this case, you may not need either.. could you do something like the following?
ACCEPT cust_id_prompt PROMPT 'Enter cust_id: '
ACCEPT cust_name_prompt PROMPT 'Enter customer name: '
ACCEPT cust_zip_prompt PROMPT 'Enter customer zip cd: '
SELECT a.cust_id, c.cust_nm, b.cust_zip
FROM customers.cust_id a
JOIN customers.cust_addr b ON a.xref_id = b.xref_id
JOIN customers.cust_nm c ON a.xref_id = c.xref_id
WHERE (a.cust_id = &cust_id_prompt or '&cust_id_prompt' is null)
AND ((b.cust_zip = &enter_cust_zip_cd and c.cust_nm = &enter_cust_name) or '&enter_cust_zip_cd' is null)
AND ('&cust_id_prompt' is not null or '&enter_cust_zip_cd' is not null);
Basically, if I provide input use it to search. The last line is just in there to ensure you don't enter blanks for everything and get back the whole table.
There would be other benefits that using Python, etc will provide (easier to have unit tests around your code, a better user interface, etc) - but if you are looking for a quick and dirty solution staying in SQL*PLUS, this may get you there.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论