oracle 存储过程模板

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

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_stmt和sql_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语句时，它会考虑列的数据类型和其他一些条件，以决定如何格式化数据。然后，它会先清空目标表的数据，再执行生成的插入操作。