Oracle将一个过程重写为泛型

Question

我有一个程序，它运行良好，其他应用程序想使用

可以看到，表和列名被硬编码到过程中，这使得很难共享代码。有没有办法重写一下，让大家分享。如果可能的话，我希望避免传入更多的值，因为这会使代码变得笨拙和笨拙。

如有任何建议，不胜感激。

SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';
CREATE table t(
seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
c CLOB,
create_date DATE DEFAULT SYSDATE
);
/
insert into t (c) values (
rpad('X',20,'X')
);
/
create or replace procedure lob_append( p_id in number, p_text in varchar2 )
as
l_clob clob;
l_text varchar2(32760);
l_system_date_time VARCHAR2(50); 
begin
select c into l_clob from t where seq_num = p_id for update;
SELECT TO_CHAR (SYSDATE, 'MMDDYYYY HH24:MI:SS') into l_system_date_time from dual;
-- newline each time code is appended for clarity.
l_text := chr(10) || p_text || chr(10) || '['||l_system_date_time||']'||chr(10);
dbms_lob.writeappend( l_clob, length(l_text), l_text );
end;
/
exec lob_append(1, rpad('Z',20,'Z'));
exec lob_append(1, rpad('Y',10,'Y'));

select * from t;
/

Answer 1

在过程中不从表中获取SELECT，而是将CLOB传递到过程中;这样，您就不需要在过程中使用动态SQL:

CREATE PROCEDURE lob_append(
p_clob IN OUT CLOB,
p_text IN     VARCHAR2
)
AS
l_text varchar2(32760);
BEGIN
-- newline each time code is appended for clarity.
l_text := chr(10)
|| p_text || chr(10)
|| '['||TO_CHAR (SYSDATE, 'MMDDYYYY HH24:MI:SS')||']'||chr(10);
dbms_lob.writeappend(p_clob, length(l_text), l_text );
END;
/

然后，当你想调用它时:

DECLARE
l_clob CLOB;
BEGIN
SELECT c INTO l_clob FROM t WHERE seq_num = 1 FOR UPDATE;
lob_append(l_clob, rpad('Z',20,'Z'));
END;
/

DECLARE
l_clob CLOB;
BEGIN
SELECT c INTO l_clob FROM t WHERE seq_num = 1 FOR UPDATE;
lob_append(l_clob, rpad('Y',10,'Y'));
END;
/

db<此处小提琴>

Answer 2

我是这样理解这个问题的

---

是否有一种方法可以重写以使其可以共享。

是的，通过使用动态SQL． 您将将所需的所有语句组合到varchar2局部变量中，然后使用execute immediate运行它。这意味着，实际上，你必须将表/列名传递到过程中，以便你能够在"通用"中使用它们。方式。

注意SQL注入，即坏人可能会试图滥用该代码。阅读更多关于DBMS_ASSERT包的信息。

---

我希望尽可能避免传递更多的值，因为这会使代码变得笨拙和笨拙。

对，就是和相对你要做的事。如果过程必须是"泛型"的，则必须传递表/列名(正如我已经说过的)，因此这意味着更多参数

---

值得吗?我不喜欢动态SQL。尽管它似乎"解决"了问题。一些问题，会带来另一些问题。代码很难维护和调试。基本上，天下没有免费的午餐。有好处，也有缺点。