oracle视图、存储过程、定时任务、用户授权、备份表

视图

create or replace view user_view asselect user_id,dept_id,phonenumber,name,user_name from sys_user where del_flag='0' and status='0' and sfyzh='Y';

存储过程

create or replace procedure sg_zzjgas cursor region_list is select ID_,CODE_,CREATE_TIME,CREATE_USER_ID,DATA_AREA_CODE,DEL_FLAG, ORDER_NUMBER,REMARK_,UPDATE_TIME,UPDATE_USER_ID,ADDRESS_,AREA_SITUATION, GOVERNANCE_SITUATION,KEY_WORD,LAYER_LABEL_ID,ORDER_,PARENT_ID,REGION_CODE, REGION_NAME,REGION_TYPE,LEVEL_,CENTERID,MAPLEVEL,PROVICECODE,TREE_TYPE,LONGITUDE, LATITUDE,EXTEND_ATT,LXDH from REGION@Zhzx11_35_Operention; cursor user_list is select user_id,dept_id,phonenumber,name,user_name from SYS_USER@Zhzx11_35_Operention where del_flag='0' and status='0' and sfyzh='Y'; cursor jgbm_list is select ID,BMMC,BMBM,BMJC,BGSDH,YB,DZ,SSQUY,SJBM,SSQIY,LXR, LXDH,BMMS,SFJDYBM,SFYGDZTQYQX,SFQY,SORT,CREATE_BY,CREATE_TIME, UPDATE_BY,UPDATE_TIME,DEL_FLAG,JLSJ,SFZYBM,ANCESTORS,ISBSC,SFQZ, BMLB,SFDBBM,SFBSC,ZSKBMBM,U_ID from YW_JGBMXXB@Zhzx11_35_Operentionwhere del_flag=0and sfqy=0; begin delete from sg_region; delete from sg_users; delete from sg_jgbm;for x in region_list loop insert into sg_region(ID_, CODE_, CREATE_TIME, CREATE_USER_ID, DATA_AREA_CODE, DEL_FLAG, ORDER_NUMBER, REMARK_, UPDATE_TIME, UPDATE_USER_ID, ADDRESS_, AREA_SITUATION, EXTEND_ATT, GOVERNANCE_SITUATION, KEY_WORD, LAYER_LABEL_ID, ORDER_, PARENT_ID, REGION_CODE, REGION_NAME, REGION_TYPE, LEVEL_, CENTERID, MAPLEVEL, PROVICECODE, TREE_TYPE) values(x.ID_, x.CODE_, x.CREATE_TIME, x.CREATE_USER_ID, x.DATA_AREA_CODE, x.DEL_FLAG, x.ORDER_NUMBER, x.REMARK_, x.UPDATE_TIME, x.UPDATE_USER_ID, x.ADDRESS_, x.AREA_SITUATION, x.EXTEND_ATT, x.GOVERNANCE_SITUATION, x.KEY_WORD, x.LAYER_LABEL_ID, x.ORDER_, x.PARENT_ID, x.REGION_CODE, x.REGION_NAME, x.REGION_TYPE, x.LEVEL_, x.CENTERID, x.MAPLEVEL, x.PROVICECODE, x.TREE_TYPE); end loop; for x in user_list loop insert into sg_users(user_id,dept_id,phonenumber,name,user_name) values(x.user_id,x.dept_id,x.phonenumber,x.name,x.user_name); end loop; for x in jgbm_list loop insert into sg_jgbm( ID,BMMC,BMBM,BMJC,BGSDH,YB,DZ,SSQUY,SJBM,SSQIY,LXR,LXDH,BMMS,SFJDYBM,SFYGDZTQYQX,SFQY,SORT,CREATE_BY,CREATE_TIME,UPDATE_BY,UPDATE_TIME,DEL_FLAG,JLSJ,SFZYBM,ANCESTORS,ISBSC,SFQZ,BMLB,SFDBBM,SFBSC,ZSKBMBM,U_ID) values(x.ID,x.BMMC,x.BMBM,x.BMJC,x.BGSDH,x.YB,x.DZ,x.SSQUY,x.SJBM,x.SSQIY,x.LXR,x.LXDH,x.BMMS,x.SFJDYBM,x.SFYGDZTQYQX,x.SFQY,x.SORT,x.CREATE_BY,x.CREATE_TIME,x.UPDATE_BY,x.UPDATE_TIME,x.DEL_FLAG,x.JLSJ,x.SFZYBM,x.ANCESTORS,x.ISBSC,x.SFQZ,x.BMLB,x.SFDBBM,x.SFBSC,x.ZSKBMBM,x.U_ID); end loop;commit;end sg_zzjg;

定时任务

var sg_zzjg number; begin dbms_job.submit(:sg_zzjg,'sg_zzjg;',Trunc(sysdate)+1/24 ,'Trunc(sysdate+1)+1/24'); end;/

sg_zzjg 存储过程的名字。每天凌晨一点执行。
一定要在cmd窗口执行

用户授权

create user SGidentified by SG_20240119_;#创建用户和密码GRANT CREATE SESSION TO username;#授权连接权限GRANT RESOURCE TO username;#授权资源权限grant select on JGBM_VIEW to SG;grant select on REGION_VIEW to SG;grant select on USER_VIEW to SG;grant select on JGBM_VIEW to SG;#授权视图查询权限grant select on REGION_VIEW to SG;grant select on USER_VIEW to SG;

备份表

CREATE TABLE new_table_name AS SELECT * FROM table_name;