oracle中的子字符串 [英] sub string in oracle
本文介绍了oracle中的子字符串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的查询如下
select dbms_metadata.get_ddl('TRIGGER', 'trigger_delete') from dual;
查询输出如下
CREATE OR REPLACE TRIGGER "owner"."trigger_delete" BEFORE DELETE ON roles FOR EACH ROW
BEGIN
DBMS_APPLICATION_INFO.read_client_info (acct_utils.trigger_enabled);
IF acct_utils.trigger_enabled <> 'F' THEN
INSERT INTO delete_table (ID, key, object, type, keytype, objecttype, created, deleted, deletedby) VALUES(
:old.id,
:old.key,
'delete',
null,
:old.keytype,
null,
:old.whencreated,
(CURRENT_TIMESTAMP AT TIME ZONE 'GMT'),
SYS_CONTEXT('TMCtx', 'USERKEY')
);
END IF;
END trigger_delete;
ALTER TRIGGER "owner"."trigger_delete" ENABLE
我希望文本(子字符串)从插入delete_table"开始直到分号(;).
I want the text(substring) starting from "insert into delete_table" till semicolon(;).
我尝试了以下查询.
select substr(dbms_metadata.get_ddl('TRIGGER', 'trigger_delete'),instr(dbms_metadata.get_ddl('TRIGGER', 'trigger_delete'),'INSERT INTO delete_table ',1),INSTR(dbms_metadata.get_ddl('TRIGGER', 'trigger_delete'),');',instr(dbms_metadata.get_ddl('TRIGGER', 'trigger_delete'),'INSERT INTO delete_table ',1),1)) from dual;
帮助我,我在哪里想念.
Help me where am I missing.
推荐答案
也许是这样的;我的CTE是您GET_DDL的结果.
Something like this, perhaps; my CTE is the result of your GET_DDL.
with test (col) as
(select q'[
CREATE OR REPLACE TRIGGER "owner"."trigger_delete" BEFORE DELETE ON roles FOR EACH ROW
BEGIN
DBMS_APPLICATION_INFO.read_client_info (acct_utils.trigger_enabled);
IF acct_utils.trigger_enabled <> 'F' THEN
INSERT INTO delete_table (ID, key, object, type, keytype, objecttype, created, deleted, deletedby) VALUES(
:old.id,
:old.key,
'delete',
null,
:old.keytype,
null,
:old.whencreated,
(CURRENT_TIMESTAMP AT TIME ZONE 'GMT'),
SYS_CONTEXT('TMCtx', 'USERKEY')
);
END IF;
END trigger_delete;]'
from dual)
select substr(col,
instr(upper(col), 'INSERT INTO DELETE_TABLE'),
--
instr(col, ';', instr(upper(col), 'INSERT INTO DELETE_TABLE'), 1) -
instr(upper(col), 'INSERT INTO DELETE_TABLE') + 1
) result
from test;
这篇关于oracle中的子字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文