希望这个简单的查询循环 [英] Want this simple query to loop
问题描述
我希望此查询循环,但是在获得结果后添加此问题:您要搜索另一列吗?"
I want this query to loop, but add this question after getting the results, "Would you like to search for another column?"
ACCEPT column_name CHAR
PROMPT 'Please insert column name'
SELECT DISTINCT owner, table_name
FROM all_tab_columns
WHERE column_name IN ('&column_name');
如果用户键入"no",则程序以祝您有美好的一天"结尾.如果用户键入是",则程序将重新开始,要求用户输入另一个列名.
If the user types in 'no' program ends with "Have a nice day". If user types in 'yes', the program starts over again requesting the user to input another column name.
我知道这很简单.我主要在SQL Server中工作,但是Oracle对我来说是新手
I know it's simple. I mainly work in SQL Server, but Oracle is new to me
推荐答案
您可以使用2个这样的SQL脚本文件来做到这一点:
You can do this with 2 SQL script files like this:
1)文件myscript.sql:
1) File myscript.sql:
ACCEPT column_name CHAR PROMPT 'Please insert column name: '
SELECT DISTINCT owner, table_name
FROM all_tab_columns
WHERE column_name IN ('&column_name');
-- Ask the user if the want to search again
ACCEPT response CHAR PROMPT 'Would you like to search for another column? '
-- Use a SELECT statement to look at their response and set variable "script" as appropriate
COLUMN script new_value script
-- Stop this select's results being displayed (in SQL Plus)
SET TERM OFF
SELECT CASE LOWER('&response.') WHEN 'yes' THEN 'myscript' ELSE 'stop' END AS script
FROM DUAL;
-- Switch terminal output back on
SET TERM ON
-- Run whichever script the user chose i.e. "myscript.sql" if they said "yes", "stop.sql" if they said "no".
@&script.
2)文件stop.sql:
2) File stop.sql:
-- This script just says "Have a nice day" then ends.
PROMPT Have a nice day
取决于用户响应的是是"还是否",脚本要么再次运行自身(循环),要么运行stop.sql脚本,该脚本只说祝你有美好的一天".
Depending on whether the user responds 'yes' or 'no', the script either runs itself again (loops) or runs the stop.sql script, which just says "Have a nice day".
这篇关于希望这个简单的查询循环的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!