Oracle创建存储过程范例如下:
create or replace procedure Proc_DISTINCT_ZCXYXX_SZJSPX(IN_TABLE_NAME varchar2) AUTHID CURRENT_USER as
/*********************************
名称:Proc_DISTINCT_ZCXYXX_SZJSPX
功能描述:创建临时数据存储表
修订记录:
版本号 编辑时间 编辑人 修改描述
1.0.0 2015-9-20 ** 1.创建此存储过程
1.0.1 2015-9-21 ** 2.修改表名称及变量名称,增加必要注释
存储过程执行权限问题: AUTHID CURRENT_USER
入参出参描述:
IN_TABLE_NAME 要创建的临时表名
**********************************/
v_tablename varchar2(30);--表名
v_flag number(10,0);
v_sqlfalg varchar(200);
v_create_sql varchar(4000);
begin
v_flag:=0;
v_tablename:=UPPER(IN_TABLE_NAME);
v_sqlfalg:=‘select count(*) from user_TABLES where table_name=‘‘‘||v_tablename||‘‘‘‘;
v_create_sql:=‘CREATE TABLE ‘|| v_tablename ||‘ AS SELECT * from
( select DRIVER_NO,ID_NO,NAME,TRAIN_CAR_CLASS,ACCEPT_DATE,REGIST_STATUS,OPERATION, rec_update_date,MEMO,
row_number() over (partition BY ID_NO order by rec_update_date desc) RN from ZCXYXX_SZJSPX WHERE RN=1)‘ ;
-- dbms_output.put_line(v_create_sql);
execute immediate v_sqlfalg into v_flag;
if v_flag=0 then --如果没有这个表 则去创建
begin
-- DELETE DUPLICATE DATA FROM table CAR.ZCXYXX_SZJSPX,KEEP THE LATEST ONE RECORD.
execute immediate v_create_sql;
execute immediate ‘DROP TABLE ZCXYXX_SZJSPX‘;
execute immediate ‘ALTER TABLE TEMP_ZCXYXX_SZJSPX rename TO ZCXYXX_SZJSPX‘;
end;
else
execute immediate ‘DROP TABLE TEMP_ZCXYXX_SZJSPX‘;
-- DELETE DUPLICATE DATA FROM table CAR.ZCXYXX_SZJSPX,KEEP THE LATEST ONE RECORD.
execute immediate v_create_sql;
execute immediate ‘DROP TABLE ZCXYXX_SZJSPX‘;
execute immediate ‘ALTER TABLE TEMP_ZCXYXX_SZJSPX rename TO ZCXYXX_SZJSPX‘;
end if;
EXCEPTION
WHEN OTHERS THEN
--dbms_output.put_line( SQLCODE|| ‘ :‘|| SQLERRM);
RAISE_APPLICATION_ERROR(-20003,SQLCODE|| SQLERRM|| ‘存储过程[Proc_DISTINCT_ZCXYXX_SZJSPX]执行失败‘);
ROLLBACK;
end;