def execute_workflow(self, workflow, close_conn=True):
"""执行上线单，返回Review set
原来的逻辑是根据 sql_content简单来分割SQL，进而再执行这些SQL
新的逻辑变更为根据审核结果中记录的sql来执行，
如果是PLSQL存储过程等对象定义操作，还需检查确认新建对象是否编译通过!
"""
review_content = workflow.sqlworkflowcontent.review_content
review_result = json.loads(review_content)
sqlitemList = get_exec_sqlitem_list(review_result, workflow.db_name)
sql = workflow.sqlworkflowcontent.sql_content
execute_result = ReviewSet(full_sql=sql)
line = 1
statement = None
try:
conn = self.get_connection()
cursor = conn.cursor()
# 逐条执行切分语句，追加到执行结果中
for sqlitem in sqlitemList:
statement = sqlitem.statement
if sqlitem.stmt_type == "SQL":
statement = statement.rstrip(';')
with FuncTimer() as t:
cursor.execute(statement)
conn.commit()
rowcount = cursor.rowcount
stagestatus = "Execute Successfully"
if sqlitem.stmt_type == "PLSQL" and sqlitem.object_name and sqlitem.object_name != 'ANONYMOUS' and sqlitem.object_name != '':
query_obj_sql = f"""SELECT OBJECT_NAME, STATUS, TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS') FROM ALL_OBJECTS
WHERE OWNER = '{sqlitem.object_owner}'
AND OBJECT_NAME = '{sqlitem.object_name}'
"""
cursor.execute(query_obj_sql)
row = cursor.fetchone()
if row:
status = row[1]
if status and status == "INVALID":
stagestatus = "Compile Failed. Object " + sqlitem.object_owner + "." + sqlitem.object_name + " is invalid."
else:
stagestatus = "Compile Failed. Object " + sqlitem.object_owner + "." + sqlitem.object_name + " doesn't exist."
if stagestatus != "Execute Successfully":
raise Exception(stagestatus)
execute_result.rows.append(ReviewResult(
id=line,
errlevel=0,
stagestatus=stagestatus,
errormessage='None',
sql=statement,
affected_rows=rowcount,
execute_time=t.cost,
))
line += 1
except Exception as e:
logger.warning(f"Oracle命令执行报错，语句：{statement or sql}， 错误信息：{traceback.format_exc()}")
execute_result.error = str(e)
# 追加当前报错语句信息到执行结果中
execute_result.rows.append(ReviewResult(
id=line,
errlevel=2,
stagestatus='Execute Failed',
errormessage=f'异常信息：{e}',
sql=statement or sql,
affected_rows=0,
execute_time=0,
))
line += 1
# 报错语句后面的语句标记为审核通过、未执行，追加到执行结果中
for sqlitem in sqlitemList[line - 1:]:
execute_result.rows.append(ReviewResult(
id=line,
errlevel=0,
stagestatus='Audit completed',
errormessage=f'前序语句失败, 未执行',
sql=sqlitem.statement,
affected_rows=0,
execute_time=0,
))
line += 1
finally:
if close_conn:
self.close()
return execute_result
def execute_workflow(self, workflow, close_conn=True):
"""执行上线单，返回Review set
原来的逻辑是根据 sql_content简单来分割SQL，进而再执行这些SQL
新的逻辑变更为根据审核结果中记录的sql来执行，
如果是PLSQL存储过程等对象定义操作，还需检查确认新建对象是否编译通过!
"""
review_content = workflow.sqlworkflowcontent.review_content
review_result = json.loads(review_content)
sqlitemList = get_exec_sqlitem_list(review_result, workflow.db_name)
sql = workflow.sqlworkflowcontent.sql_content
execute_result = ReviewSet(full_sql=sql)
line = 1
failed_line = 0
statement = None
try:
conn = self.get_connection()
cursor = conn.cursor()
# 获取执行工单时间，用于备份SQL的日志挖掘起始时间
cursor.execute(
f"alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'")
cursor.execute(f"select sysdate from dual")
rows = cursor.fetchone()
begin_time = rows[0]
# 逐条执行切分语句，追加到执行结果中
for sqlitem in sqlitemList:
statement = sqlitem.statement
if sqlitem.stmt_type == "SQL":
statement = statement.rstrip(';')
with FuncTimer() as t:
if statement != '':
cursor.execute(statement)
#conn.commit()
rowcount = cursor.rowcount
stagestatus = "Execute Successfully"
if sqlitem.stmt_type == "PLSQL" and sqlitem.object_name and sqlitem.object_name != 'ANONYMOUS' and sqlitem.object_name != '':
query_obj_sql = f"""SELECT OBJECT_NAME, STATUS, TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS') FROM ALL_OBJECTS
WHERE OWNER = '{sqlitem.object_owner}'
AND OBJECT_NAME = '{sqlitem.object_name}'
"""
cursor.execute(query_obj_sql)
row = cursor.fetchone()
if row:
status = row[1]
if status and status == "INVALID":
stagestatus = "Compile Failed. Object " + sqlitem.object_owner + "." + sqlitem.object_name + " is invalid."
else:
stagestatus = "Compile Failed. Object " + sqlitem.object_owner + "." + sqlitem.object_name + " doesn't exist."
if stagestatus != "Execute Successfully":
raise Exception(stagestatus)
execute_result.rows.append(
ReviewResult(
id=line,
errlevel=0,
stagestatus=stagestatus,
errormessage='None',
sql=statement,
affected_rows=cursor.rowcount,
execute_time=t.cost,
))
line += 1
except Exception as e:
logger.warning(
f"Oracle命令执行报错，工单id：{workflow.id}，语句：{statement or sql}， 错误信息：{traceback.format_exc()}"
)
execute_result.error = str(e)
# 捕获异常，回滚未提交事务,标记备份结束位置
conn.rollback()
failed_line = line
# 追加当前报错语句信息到执行结果中
execute_result.rows.append(
ReviewResult(
id=line,
errlevel=2,
stagestatus='Execute Failed',
errormessage=f'异常信息：{e}',
sql=statement or sql,
affected_rows=0,
execute_time=0,
))
line += 1
# 报错语句后面的语句标记为审核通过、未执行，追加到执行结果中
for sqlitem in sqlitemList[line - 1:]:
execute_result.rows.append(
ReviewResult(
id=line,
errlevel=0,
stagestatus='Audit completed',
errormessage=f'前序语句失败, 未执行',
sql=sqlitem.statement,
affected_rows=0,
execute_time=0,
))
line += 1
finally:
# 完成会话做提交
conn.commit()
# 备份
if workflow.is_backup:
try:
cursor.execute(f"select sysdate from dual")
rows = cursor.fetchone()
end_time = rows[0]
self.backup(workflow,
cursor=cursor,
begin_time=begin_time,
end_time=end_time)
except Exception as e:
logger.warning(
f"Oracle工单备份异常，工单id：{workflow.id}， 错误信息：{traceback.format_exc()}"
)
execute_result.error = str(e)
if failed_line > 0:
while failed_line > 0:
execute_result.rows[failed_line].errlevel = 1
execute_result.rows[
failed_line].stagestatus = execute_result.rows[
failed_line].stagestatus + "\n Backup failed"
execute_result.rows[
failed_line].errormessage = f"备份失败：{str(e)}"
failed_line -= 1
else:
line = line - 2
while line > 0:
execute_result.rows[line].errlevel = 1
execute_result.rows[
line].stagestatus = execute_result.rows[
line].stagestatus + "\n Backup failed"
execute_result.rows[
line].errormessage = f"备份失败：{str(e)}"
line -= 1
else:
if failed_line > 0:
while failed_line > 0:
execute_result.rows[
line - 2].stagestatus = execute_result.rows[
line -
2].stagestatus + "\n Backup successfully"
failed_line -= 1
else:
line = line - 2
while line > 0:
execute_result.rows[
line - 2].stagestatus = execute_result.rows[
line -
2].stagestatus + "\n Backup successfully"
line -= 1
if close_conn:
self.close()
return execute_result