如何在Shell脚本中读取文件中某些文本后的某些行

Question

我有SQL文件，我需要从特定的SQL查询(即用户传递的SQL查询(中获取SQL查询到SQL文件的末尾。

让我们举一个例子：

下面是SQL文件：

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Thomas', 'Tom B. James', 'Skagen 22', 'Stavanger', '4007', 'Norway');
Update Customers set CustomerName = 'ABC' where PostalCode = '4006'
Delete from Customers where PostalCode = '4007'
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Rose', 'Tom B. Smith', 'Skagen 23', 'Stavanger', '4008', 'Norway');
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Jim', 'Tom B. Jonny', 'Skagen 24', 'Stavanger', '4009', 'Norway');

这里用户通过查询Update Customers set CustomerName = 'ABC' where PostalCode = '4006'，我将用户通过的SQL查询存储在变量$sql中

现在我需要得到如下的输出

Update Customers set CustomerName = 'ABC' where PostalCode = '4006'
Delete from Customers where PostalCode = '4007'
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Rose', 'Tom B. Smith', 'Skagen 23', 'Stavanger', '4008', 'Norway');
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Jim', 'Tom B. Jonny', 'Skagen 24', 'Stavanger', '4009', 'Norway');

为了得到上面的输出，我使用下面的代码

awk -v var="$sql" '$0 == "var" {i=1;next};i && i++' test.sql

在这里，我将sql变量内容存储在var变量中，并在文件中搜索特定的sql查询，在获得特定的sql询问(由用户传递(后，我将从用户传递到文件末尾的sql查询中打印sql查询。

再举一个例子：

如果用户通过INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Rose', 'Tom B. Smith', 'Skagen 23', 'Stavanger', '4008', 'Norway');，我应该得到以下输出

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Rose', 'Tom B. Smith', 'Skagen 23', 'Stavanger', '4008', 'Norway');
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Jim', 'Tom B. Jonny', 'Skagen 24', 'Stavanger', '4009', 'Norway');

我没有得到输出。

有人能帮我解决上述问题吗。这将对他很有帮助。

注意：我的SQL查询包含所有DML语句，而且我的一个SQL查询不是单行查询。它将占用超过4-5条线路。示例目的我在这里使用了单行查询

有人能帮我使用通用方法吗？这样它对文件中的所有SQL查询都有用吗？

Answer 1

对于您显示的示例，您可以尝试以下操作吗。用GNUawk编写和测试。

##Creating shell variables named insert and value here before passing them to awk code.
insert="INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)"
value="VALUES ('Rose', 'Tom B. Smith', 'Skagen 23', 'Stavanger', '4008', 'Norway');"
##Starting awk code from here and passing shell variables to it.
awk -v insert="$insert" -v value="$value" '
/^INSERT/ && foundInsertcouple{
print
next
}
/^VALUES/ && foundInsertcouple{
print
foundInsertcouple=""
next
}
/^INSERT/ && $0==insert{
foundInsert=1
val=$0
next
}
/^VALUES/ && foundInsert && $0==value{
print val ORS $0
val=foundInsert=""
foundInsertcouple=1
next
}'  Input_file

Answer 2

在SQL中：select id, firstname, lastname from authors

如果提供：Firstname: evil'ex和Lastname: Newman

查询字符串变为：

select id, firstname, lastname from authors where firstname = 'evil'ex' and lastname ='newman'

数据库尝试运行为：

由于数据库试图执行邪恶，il'附近的语法不正确。

Answer 3

以下是的正确答案

file='read_file.txt'  

i=1  
while read line; do  

#Reading each line  
echo "Line No. $i : $line"  
i=$((i+1))  
done < $file