oracle plsql如果找不到,请重复 [英] oracle plsql if not found repeat
问题描述
set verify off
accept project prompt ' project : '
select locknr,description,couserid,ciuserid from dgdtw_lockedinfo where
description = '&project' and ciuserid is null;
accept lock prompt ' locknumber : '
update dgdtw_lockedinfo set ciuserid = couserid where locknr = &lock;
update dgdtw_topografie set locknr = '' where locknr = &lock;
update dgdtw_topografie set verval=sysdate where id= &lock;
commit;
accept var prompt 'repeat process? [Y/N] ? '
define doit = 'H:\Scripts\stop.sql'
column doit new_value doit noprint
set termout off
select 'H:\Scripts\unlock.sql' doit from dual where upper('&var') like 'Y%';
set termout on
start &doit.
我需要一个循环脚本,因此,如果项目为空或错误,则该脚本会要求重复或停止. 像这样:
I need a loop script so if project is empty or wrong, the script asks to repeat or stop. Something like:
accept var prompt 'project number is wrong try again? [Y/N] ? '
直到项目编号正确或答案为"N"
Until project number is correct or answer is "N"
推荐答案
下面的示例将在该项目没有锁的情况下立即重新启动unlock.sql,方法是将对unlock.sql的调用重新定义为对empty.sql的调用只要返回至少一行.
The example below will immediately restart unlock.sql when there are no locks for that project, by redefining the call to unlock.sql as a call to empty.sql whenever at least one row is returned.
set verify off
accept project prompt ' project : '
define doit = 'H:\Scripts\unlock.sql'
column doit new_value doit noprint
select 'H:\Scripts\empty.sql' as doit, locknr,description,couserid,ciuserid from dgdtw_lockedinfo where
description = '&project' and ciuserid is null;
start &doit.
accept lock prompt ' locknumber : '
update dgdtw_lockedinfo set ciuserid = couserid where locknr = &lock;
update dgdtw_topografie set locknr = '' where locknr = &lock;
update dgdtw_topografie set verval=sysdate where id= &lock;
commit;
accept var prompt 'repeat process? [Y/N] ? '
define doit = 'H:\Scripts\stop.sql'
column doit new_value doit noprint
set termout off
select 'H:\Scripts\unlock.sql' doit from dual where upper('&var') like 'Y%';
set termout on
start &doit.
作为一项改进,我建议将重复问题移到一个单独的SQL文件中,然后使用一个告诉它重新启动哪个脚本的参数来调用它(请参阅
As an improvement I suggest moving the repeat question into a separate SQL file then call that using an argument that tells it which script to restart (see https://docs.oracle.com/cd/B10501_01/server.920/a90842/ch13.htm#1013716)
例如,从"unlock.sql"调用"repeat.sql",如下所示:
For instance, from 'unlock.sql' call 'repeat.sql' like this:
start 'repeat.sql' unlock
使用repeat.sql就像这样:
with repeat.sql as something like this:
accept var prompt 'repeat &1 process? [Y/N] ? '
define doit = 'H:\Scripts\stop.sql'
column doit new_value doit noprint
set termout off
select 'H:\Scripts\&1.sql' doit from dual where upper('&var') like 'Y%';
set termout on
start &doit.
这篇关于oracle plsql如果找不到,请重复的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!