oracle 存储过程模板

发布时间:2024年01月11日

--------------------------------------------------------

DROP TABLE "LOCAL_RESP";

CREATE TABLE "LOCAL_RESP"( "RES_REPORT" VARCHAR2(1000) ) ;

GRANT INSERT ON LOCAL_RESP TO C##TEST06;

--------------------------------------------------------

这段代码是用于在Oracle数据库中执行一系列操作的SQL脚本。以下是每行代码的解释:

  1. DROP TABLE "LOCAL_RESP";:这行代码用于删除名为"LOCAL_RESP"的表。如果该表存在,它将被永久删除。
  2. CREATE TABLE "LOCAL_RESP"( "RES_REPORT" VARCHAR2(1000));:这行代码用于创建一个新的表,名为"LOCAL_RESP"。该表有一个列,名为"RES_REPORT",数据类型为VARCHAR2,最大长度为1000。
  3. GRANT INSERT ON LOCAL_RESP TO C##TEST06;:这行代码授予用户C##TEST06向"LOCAL_RESP"表插入数据的权限。

注意:在执行这段代码之前,请确保您已经备份了任何重要数据,因为DROP TABLE命令会永久删除表及其所有数据。另外,执行GRANT命令需要相应的权限。

为了下面的存储过程 的?EXECUTE IMMEDIATE sql_stmt; 语句能成功,最好写上GRANT INSERT ON LOCAL_RESP TO C##TEST06;


CREATE OR REPLACE PROCEDURE PRO_FIX1010_TEMP (t_name VARCHAR2) AS ?

del_sql_stmt varchar2(1000);
sql_stmt varchar2(1000);

CURSOR OUT_RESULT IS?

WITH ?
?ee as ( SELECT column_name, data_type, data_precision, data_scale ?
FROM user_tab_columns ?
WHERE table_name = t_name ?)

select * from ee;?


begin ?
? sql_stmt := 'insert into LOCAL_RESP (RES_REPORT) ? SELECT ';

for obj in OUT_RESULT


LOOP

IF obj.data_type = 'NUMBER' THEN
sql_stmt := sql_stmt || 'LPAD(TO_NUMBER(' || obj.column_name || '), ' || obj.data_precision || ', 0)' ? || '||''&*&''||' ;

ELSIF obj.column_name = 'INSERT_SYS_DATE' OR obj.column_name = 'UPD_SYS_DATE' THEN

sql_stmt := sql_stmt || '''1900/01/01 01:01:01''' ? || '||''&*&''||' ;

ELSIF obj.column_name = 'INSERT_USER_ID' OR obj.column_name = 'INSERT_JOB_ID' ?OR obj.column_name = 'INSERT_PRO_ID' OR obj.column_name = 'UPD_USER_ID' OR obj.column_name = 'UPD_JOB_ID' OR obj.column_name = 'UPD_PRO_ID' THEN

sql_stmt := sql_stmt || '''IKOU''' ? || '||''&*&''||' ;

ELSE

sql_stmt := sql_stmt || obj.column_name ? || '||''&*&''||' ;

END IF;


end LOOP;

sql_stmt := SUBSTR(sql_stmt, 1, LENGTH(sql_stmt) - 9); ?
sql_stmt := sql_stmt || ' AS RESU FROM ' || t_name ?|| ' ORDER BY SEQ_NO';

del_sql_stmt := 'truncate table LOCAL_RESP' ;

EXECUTE IMMEDIATE del_sql_stmt;
commit;

EXECUTE IMMEDIATE sql_stmt;
commit;


end PRO_FIX1010_TEMP;

这段代码是一个Oracle PL/SQL存储过程,名为PRO_FIX1009_TEMP。这个过程接受一个参数t_name,该参数代表要从中提取数据的表名。该过程的主要目的是生成一个SQL语句,用于从指定表中提取数据并插入到LOCAL_RESP表中。以下是详细的代码解析:

  1. 声明两个字符串变量del_sql_stmtsql_stmt,用于存储SQL语句。
  2. 定义一个游标OUT_RESULT,它通过一个公用表表达式(CTE)ee从用户表中选择列名、数据类型、数据精度和数据刻度。
  3. 初始化sql_stmt为插入语句的开头部分,并从CTE中选择数据。
  4. 使用循环遍历游标中的每一行数据(即每一列)。根据列的数据类型和其他条件,构建SQL语句。
  5. 如果列的数据类型是'NUMBER',则使用LPAD函数格式化数字并添加到SQL语句中。
  6. 如果列名为'INSERT_SYS_DATE'或'UPD_SYS_DATE',则将固定的日期字符串'1900/01/01 01:01:01'添加到SQL语句中。
  7. 如果列名为'INSERT_USER_ID'、'INSERT_JOB_ID'、'INSERT_PRO_ID'、'UPD_USER_ID'、'UPD_JOB_ID'或'UPD_PRO_ID',则将固定的字符串'IKOU'添加到SQL语句中。
  8. 对于其他列,直接添加列名到SQL语句中。
  9. 删除SQL语句末尾的连续的'||''&*&''||',因为它们在生成的SQL语句中没有实际意义。
  10. 执行一个立即执行的动态SQL语句(EXECUTE IMMEDIATE),删除LOCAL_RESP表中的所有数据。
  11. 提交事务。
  12. 执行另一个立即执行的动态SQL语句,执行生成的插入操作。
  13. 提交事务。
  14. 结束过程。

这个过程主要用于生成一个特定的SQL插入语句,用于将指定表的数据插入到另一个表中。在生成SQL语句时,它会考虑列的数据类型和其他一些条件,以决定如何格式化数据。然后,它会先清空目标表的数据,再执行生成的插入操作。

文章来源:https://blog.csdn.net/qq_34967644/article/details/135534900
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。