awk :尝试使用awk生成查询

Question

我试图编写一个awk程序来生成sql查询，使用管道命令的输出。命令的输出如下所示

Service name: APP1  
Service name: APP2  
Service name: APP3  
Service name: APP4  
Service name: APP5  
Service name: APP6  
Service name: APP7  
Service name: APP8  
Service name: APP9  

我需要的结果是：

select 'APP1' x from dual union all  
select 'APP2' from dual union all  
select 'APP3' from dual union all  
select 'APP4' from dual union all  
select 'APP5' from dual union all  
select 'APP6' from dual union all  
select 'APP7' from dual union all  
select 'APP8' from dual union all  
select 'APP9' from dual  

我需要在“服务名称：”字符串之后获取字符串，将其放在引号之间，并将其放入select中。第一行必须在字符串后面有"x“，最后一行不能包含所有的并。字符串上不能有空格。由于我对awk没有太多的经验，所以到目前为止我还没有办法做到这一点。到目前为止，我有这样的看法：

srvctl config service -db database | grep 'Service name' | awk 'BEGIN {FS = "[:]"}  
{ gsub(/^[ \t]+|[ \t]+$/, "", $2)  
    if ( NR == 1 )  
    {  
         printf "'select\ \''" $2 "'\'\ x\ from\ dual\ union\ all\ '\n"  
    }  
    else  
    {  
        printf "'select\ \''" $2 "'\'\ from\ dual\ union\ all\ '\n"  
    }  
}'  

它将产生以下输出：

select 'APP1' x from dual union all  
select 'APP2' from dual union all  
select 'APP3' from dual union all  
select 'APP4' from dual union all  
select 'APP5' from dual union all  
select 'APP6' from dual union all  
select 'APP7' from dual union all  
select 'APP8' from dual union all  
select 'APP9' from dual union all 

任何帮助都是非常感谢的。

谢谢

Answer 1

使用awk，您可以：

awk 'NR==1{printf "select \047%s\047 x from dual union all\n", $NF; next}
      s{print s, "union all"}
      {s=sprintf("select \047%s\047 from dual", $NF)} END{print s}' file

select 'APP1' x from dual union all
select 'APP2' from dual union all
select 'APP3' from dual union all
select 'APP4' from dual union all
select 'APP5' from dual union all
select 'APP6' from dual union all
select 'APP7' from dual union all
select 'APP8' from dual union all
select 'APP9' from dual

Answer 2

也许这样更简单

$ awk -v q="'" '{print "select " q$3q (NR==1?" x":"") " from dual union all"}' file | 
  sed '$s/\w* \w*$//'

select 'APP1' x from dual union all
select 'APP2' from dual union all
select 'APP3' from dual union all
select 'APP4' from dual union all
select 'APP5' from dual union all
select 'APP6' from dual union all
select 'APP7' from dual union all
select 'APP8' from dual union all
select 'APP9' from dual

或

$ awk -v q="'" -v x=" x" '{print "select " q$3q x " from dual union all"; x=""}' file | 
  sed '$s/\w* \w*$//'

Answer 3

$ awk 'BEGIN{x=" x"} NR>1{print prev " union all"; x=""} {prev="select \047" $NF "\047" x " from dual"} END{print prev}' file
select 'APP1' x from dual union all
select 'APP2' from dual union all
select 'APP3' from dual union all
select 'APP4' from dual union all
select 'APP5' from dual union all
select 'APP6' from dual union all
select 'APP7' from dual union all
select 'APP8' from dual union all
select 'APP9' from dual