Oracle 在存储过程中使用 LIKE 子句立即执行

Question

我正在尝试编写一个过程，该过程基于模式名称和表名称参数返回序列的名称。

该过程将查看all_sequences，并根据给定的模式和表名找到正确的序列。它将通过EXECUTE IMMEDIATE命令执行此操作，并将序列名插入变量中。

所有序列都被命名为s_[table_name]、seq_[table_name]或[table_name]_seq。

这是我写的代码：

PROCEDURE proc_find_sequence(  
shema_name VARCHAR2,
table_name VARCHAR2,
) 
IS

sequence_name1      VARCHAR2(30);    

BEGIN


EXECUTE IMMEDIATE 
'SELECT s.sequence_name FROM all_sequences s 
WHERE 
s.SEQUENCE_OWNER = UPPER('||shema_name||')
AND
(
s.sequence_name LIKE UPPER(''''S_%'''||table_name||')
OR 
s.sequence_name LIKE UPPER(''''SEQ_%'''||table_name||')
OR
s.sequence_name LIKE UPPER('||table_name||'''%_SEQ'''')
)'
INTO sequence_name1;


DBMS_OUTPUT.PUT_LINE(sequence_name1); 
END;

我得到的错误是"；ORA-00907：缺少右括号"；，尽管我确信问题出在我用括号之间的参数实现LIKE子句的方式上。

任何关于如何解决这个问题的帮助或想法都将受到赞扬。

Answer 1

您不需要动态SQL。简单地使用这个：

SELECT s.sequence_name 
FROM all_sequences s 
INTO sequence_name1
WHERE 
s.SEQUENCE_OWNER = UPPER(shema_name)
AND
(
s.sequence_name = UPPER('S_'||table_name)
OR 
s.sequence_name = UPPER('SEQ_'||table_name)
OR
s.sequence_name = UPPER(table_name||'_SEQ')
)

或者只是

s.sequence_name IN (
UPPER('S_'||table_name), 
UPPER('SEQ_'||table_name), 
UPPER(table_name||'_SEQ'))

正则表达式也应该工作

REGEXP_LIKE(s.sequence_name, '^S(EQ)?_'||table_name||'$', 'i')
or REGEXP_LIKE(s.sequence_name, '^'||table_name||'_SEQ$', 'i')

Answer 2

如果你坚持使用PL/SQL，我相信你不需要它(正如@Wemfried提供的答案(，你可以试试这个。考虑

-您自己说序列只能取s_[table_name]、seq_[table_name]或[table_name]_seq中的一个名称。

-因此，您可以使用一个类似的表达式，如S%TABLE_NAME%，它涵盖了所有场景。

set serveroutput on echo on
declare
shema_name varchar2(40)  := 'SYS' ;
table_name  varchar2(128) := 'MY_TABLE' ;
begin
dbms_output.put_line ( 'SELECT s.sequence_name FROM all_sequences s  
where
s.SEQUENCE_OWNER = UPPER('''||shema_name||''')
AND
s.sequence_name like UPPER(''%'||table_name||'%'')
' );
end;
/

如果我执行这个：

SQL> create table my_table ( c1 number ) ;
Table created.
SQL> create sequence s_my_table start with 1 increment by 1 ;
Sequence created.
SQL> set serveroutput on echo on
declare
shema_name varchar2(40)  := 'SYS' ;
table_name  varchar2(128) := 'MY_TABLE' ;
begin
dbms_output.put_line ( 'SELECT s.sequence_name FROM all_sequences s
where
s.SEQUENCE_OWNER = UPPER('''||shema_name||''')
AND
s.sequence_name like UPPER(''%'||table_name||'%'')
' );
end;
/
SELECT s.sequence_name FROM all_sequences s
where
s.SEQUENCE_OWNER = UPPER('SYS')
AND
s.sequence_name like UPPER('%MY_TABLE%')

PL/SQL procedure successfully completed.
SQL> SELECT s.sequence_name FROM all_sequences s
where
s.SEQUENCE_OWNER = UPPER('SYS')
AND
s.sequence_name like UPPER('%MY_TABLE%')
;
SEQUENCE_NAME
--------------------------------------------------------------------------------------------------------------------------------
S_MY_TABLE

Answer 3

另一个观点，不是你的代码出了什么问题，而是在处理这类问题时如何自己发现问题。

不应将查询构造为EXECUTE IMMEDIATE语句的一部分，而应将其分配给变量。然后，出于调试目的，显示您构建的内容。然后，您可以通过引用变量来实际执行它：

PROCEDURE proc_find_sequence(  
shema_name VARCHAR2,
table_name VARCHAR2,
) 
IS

sequence_name1      VARCHAR2(30); 
v_sql               varchar2(2048);   

BEGIN


v_sql := 
'SELECT s.sequence_name FROM all_sequences s 
WHERE 
s.SEQUENCE_OWNER = UPPER('||shema_name||')
AND
(
s.sequence_name LIKE UPPER(''''S_%'''||table_name||')
OR 
s.sequence_name LIKE UPPER(''''SEQ_%'''||table_name||')
OR
s.sequence_name LIKE UPPER('||table_name||'''%_SEQ'''')
)'
INTO sequence_name1;

dbms_output_put_line(v_sql);
execute immediate v_sql;

DBMS_OUTPUT.PUT_LINE(sequence_name1); 
END;