这两周,项目上需要在Oracle上处理数据,整理了部分用到的sql写成模板,以便快速应用 -- 20210228
存储过程模板:

--编写存储过程方法:新建一个sql窗口,直接写即可
create or replace procedure 用户名.存储过程名 as
begin
--execute immediate 'truncate table sg_test_sm_0225'; --执行特殊sql
insert into sg_test_sm_0225
   select * from sjhpt.lgqsghjhchwzydsdgl_2021 where xh=27;
commit;
end; 

call 存储过程名(参数) --运行存储过程

查看建表语句:

SELECT DBMS_METADATA.GET_DDL('TABLE','表名大写','用户名大写') FROM DUAL; --务必记得大写
select upper('driver_record_detail103987') from dual; --转大写

快速复制表结构:

CREATE TABLE 新表 as select * from 旧表 where 1=2;  --oracle复制不了注释
comment on table is '注释信息'; --添加表注释
comment on  column 表名.字段名  is '注释信息'; --添加字段注释

去除干扰符号:

select  
 regexp_replace('测《试=”字段（'
 ,'['''' 　''-''=+|\“”"":：（）( )<>《》{}【】.．。、;，,?？一_!！`~!@#$%^&*￥′……「]' --可自行添加干扰字符,''两个单引号表转义
 ,'',1,0,'i')
from  dual;
--仅保留数字字母中文
select regexp_replace('~@》"}测*！！(试#','[[:punct:]|[:blank:]|[:space:]|[:cntrl:]]','') from dual;

创建简单索引:

CREATE INDEX 索引名 ON 表名(字段); --增加索引
alter table 表名 add constraint 索引名 unique(字段); --增加唯一约束
select * from all_indexes where table_name=upper('表名'); --查看索引
alter table TRUCK_DRIVER_RECORD104093_new add constraint pk_card_number_timeflag primary key (card_number,timeflag); --主键索引
drop index 索引名; --删除索引

获取当前时间:

select CURRENT_TIMESTAMP AS db_time from dual; --数据库时间
select sysdate AS sys_time from dual; --数据库时间

当前时间加减:

select sysdate+N from dual; --当前时间加N天
select sysdate+N/24 from dual; --当前时间加N小时
select sysdate+N/(24*60) from dual; --当前时间加N分钟
--减同理

添加删除字段:

alter table 表名 add 字段名 字段类型; --添加字段
alter table 表名 drop 字段名; --删除字段

MD5:

CREATE OR REPLACE FUNCTION MD5(passwd IN VARCHAR2) RETURN VARCHAR2 IS
  retval varchar2(32);
BEGIN
  retval := utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => passwd));
  RETURN retval;
END;

select MD5(concat('sasa','2021-02-25 18:19:53')) from dual -- 创建好存储过程后可以直接select调用

查询结果插入多表:

--简单插入
insert all
 into test1(id,name,sex)
 into test2(id,name,sex)
select id,name,sex from test;

--条件插入
insert all
 when id <10 then
  into test1
 when id >10 and id<50  then
  into test2
 when id >50 and id<1000  then
  into test3
select id,name,sex from test;

merge插入:

MERGE INTO username.SM_KJYSXXB t1 --更新插入目的表
USING (
    select * from SM_KJYSXXB_NEW --来源更新数据
)t2
ON t1.id=t2.id
WHEN MATCHED THEN --主键匹配用最新数据更新
    update set
        t1.cp=t2.cp
        ,t1.sjmc=t2.sjmc
WHEN NOT MATCHED THEN --主键不存在重复，插入数据
    insert values(
        t2.id
        ,t2.cp
        ,t2.sjmc
    );

用户操作:

drop user ryd_interface cascade;

create user ryd_interface identified by ryd_interface;

grant 权限 on 表或视图 to 用户; --简单授权

grant connect,create synonym to ryd_interface; --允许登录与创建同义词

conn ryd_interface/ryd_interface --以ryd_interface登录

create synonym  run_views for  ryd_interface_src.run_views; --创建同义词

--基本授权列表
GRANT
　　CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE,
　　ALTER ANY TABLE, ALTER ANY PROCEDURE,
　　DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE,
　　SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
TO username;

--创建用户并设置其表空间
CREATE USER usertest IDENTIFIED BY userpwd
DEFAULT TABLESPACE TEST_DATA
TEMPORARY TABLESPACE TEST_TEMP;

--查看所有用户
SELECT * FROM DBA_USERS;
SELECT * FROM ALL_USERS;
SELECT * FROM USER_USERS;

--查看用户系统权限
SELECT * FROM DBA_SYS_PRIVS;
SELECT * FROM USER_SYS_PRIVS;

--查看用户对象或角色权限
SELECT * FROM DBA_TAB_PRIVS;
SELECT * FROM ALL_TAB_PRIVS;
SELECT * FROM USER_TAB_PRIVS;

--查看所有角色
SELECT * FROM DBA_ROLES;

--查看用户或角色所拥有的角色
SELECT * FROM DBA_ROLE_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

--遇到no privileges on tablespace ‘tablespace ‘
alter user userquota 10M[unlimited] on tablespace;