我想在与 shell 脚本中提到的条件匹配的文本后面添加一条语句,
下面是我的示例文件(SQL 文件):
begin
AFFECTED_ROWS := 0;
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
DELETE FROM table_name
WHERE condition;
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
MERGE INTO employees e
USING hr_records h
ON (e.id = h.emp_id)
WHEN MATCHED THEN
UPDATE SET e.address = h.address
WHEN NOT MATCHED THEN
INSERT (id, address)
VALUES (h.emp_id, h.address);
end;
一旦我按顺序看到以下任何文本,我将获取此文件并执行以下转换
1: "UPDATE ... SET ...;"
2: "DELETE ... FROM ...;"
3: "INSERT ... INTO ...;"
4: "MERGE ... INTO ... [WHEN MATCHED THEN | WHEN NOT MATCHED] ... [UPDATE|INSERT|DELETE] ... ;"
我需要在分号后面添加 1 行
AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;
所以我的新文件将如下所示
begin
AFFECTED_ROWS := 0;
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;
DELETE FROM table_name
WHERE condition;
AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;
MERGE INTO employees e
USING hr_records h
ON (e.id = h.emp_id)
WHEN MATCHED THEN
UPDATE SET e.address = h.address
WHEN NOT MATCHED THEN
INSERT (id, address)
VALUES (h.emp_id, h.address);
AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;
end;
我尝试在 PLSQL 范围内找到并实现该方法,但没有找到任何可能使行不受影响的通用方法,因此我想到了文本解析,但我对 awk 或 sed 不太了解。
现在我正在尝试做如下的事情:
sed '/Patterns Go Here/a AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;'临时sql
所以模式可能有上面提到的条件
答案1
我通过使用 perl 而不是 sed 并使用 IRS(输入记录分隔符 - $/)特殊变量来通过分号而不是换行符处理行来解决它。
任何支持更改 IRS 的语言都应该可以工作(可能可以使用纯 bash using IFS=';'
,但我还没有探索过这一点)。
在这个例子中,perl 将加载它的缓冲区,直到输入记录分隔符,然后将所有内容作为一行处理 - 通过更改分隔符的内容,您可以一次处理一个语句,而不是一次处理一行。然后,我可以匹配我感兴趣的语句的模式,并在打印之前将受影响的行逻辑附加到该行。
$ cat test.sql
SELECT non-matching-statement FROM table;
MERGE INTO employees e
USING hr_records h
ON (e.id = h.emp_id)
WHEN MATCHED THEN
UPDATE SET e.address = h.address
WHEN NOT MATCHED THEN
INSERT (id, address)
VALUES (h.emp_id, h.address);
SELECT another-non-matching-statement FROM table;
end;
$ cat test.sql | perl -pe 'BEGIN{$/=";"} m/MERGE INTO .*/ && ($_ .= "\nAFFECTED_ROWS := AFFECTED_ROWS + SQL %ROWCOUNT;")'
SELECT non-matching-statement FROM table;
MERGE INTO employees e
USING hr_records h
ON (e.id = h.emp_id)
WHEN MATCHED THEN
UPDATE SET e.address = h.address
WHEN NOT MATCHED THEN
INSERT (id, address)
VALUES (h.emp_id, h.address);
AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;
SELECT another-non-matching-statement FROM table;
end;
$
使用 less perl 简写,你可以这样做:
perl -e '$/=";"; foreach $statement (<>){ print $statement; $statement =~ m/(MERGE INTO .*)|(DELETE FROM)/i && print "\nAFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;" }'
例如:
cat test.sql | perl -e '$/=";"; foreach $statement (<>){ print $statement; $statement =~ m/(MERGE INTO .*)|(DELETE FROM)/i && print "\nAFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;" }'
SELECT non-matching-statement FROM table;
MERGE INTO employees e
USING hr_records h
ON (e.id = h.emp_id)
WHEN MATCHED THEN
UPDATE SET e.address = h.address
WHEN NOT MATCHED THEN
INSERT (id, address)
VALUES (h.emp_id, h.address);
AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;
SELECT another-non-matching-statement FROM table;
DELETE FROM truncate_me;
AFFECTED_ROWS := AFFECTED_ROWS + SQL%ROWCOUNT;
end;
$