常用Oracle语法-2022-9-05

常用函数

--upper 转大写
select upper('abcde') from dual;

--lower 转小写
select lower('abCDE') from dual;

--initcap 首字母大写
select initcap('abCDe') from dual;

--length 长度
select length('abCDe') from dual;

--replace 将a替换成S
select replace('abCDe','a','S') from dual;

--substr 字符串截取，注意无论从0还是1位开始都是从第一位开始截取（下面2个结果是相同的）
select substr('abcdefg',0,2) from dual;
select substr('abcdefg',1,2) from dual;

--instr 获取索引（'or'出现在'Hello World'的索引位置）
select instr('Hello World','or') from dual;

--lpad 左填充（总长度为10，不足的在左侧用*填充）
select lpad('abc',10,'*') from dual;

--rpad 右填充（总长度为10，不足的在右侧用*填充）
select rpad('abc',10,'*') from dual;

--trim 过滤首尾空格
select trim(' abc ') from dual;

--round 截取数字，进行四舍五入
select round(412,-2) from dual;--结果400
select round(412.3365,2) from dual;--结果412.34
select round(45.923,-1) from dual;--结果50

--trunc 截取数字，全部舍去
select trunc(462,-2) from dual;--结果400
select trunc(412.3365,2) from dual;--结果412.33
select trunc(45.923,-1) from dual;--结果40

--mod 取余，符号总是与除数相同（第一个参数）
select mod(13,5) from dual;
select mod(-13,5) from dual;
select mod(13,-5) from dual;

--months_between 返回2个日期之间的月份数，如果日期1比日期2小 ，返回值为负数。(如果不是同一天，以每天1/31月来计算月中剩余天数)
select months_between(to_date('20181023','yyyyMMdd'),to_date('20181223','yyyyMMdd')) from dual;--返回-2
select months_between(to_date('20181020','yyyyMMdd'),to_date('20181223','yyyyMMdd')) from dual;--返回-2.09677419354839
select months_between(to_date('20181223','yyyyMMdd'),to_date('20181020','yyyyMMdd')) from dual;--返回2.09677419354839

--add_months
select add_months(sysdate,2) from dual;

--next_day 当前日期的下一个'星期日'是哪一天
select next_day(sysdate,'星期日') from dual;

--last_day 当前日期所在月份的最后一天
select last_day(sysdate) from dual;

--concat 只能连接两个字符串，|| 可以连接多个
select concat('ab','cd') from dual;
select 'ab'||'cd'||'ef' from dual;

--nvl 字段'remarks'如果为空返回'空值'，否则返回字段'remarks'
select t.remarks,nvl(t.remarks,'空值') from cndba.cnprj1 t where t.project_no<>'000000'

--nvl2 字段'remarks'如果为空返回'空值'，否则返回'非空值'，永远不会返回字段'remarks'
select t.remarks,nvl2(t.remarks,'非空值','空值') from cndba.cnprj1 t where t.project_no<>'000000'

--having 用于group by后对结果进行进一步筛选
select t.project_no,sum(1) as qtys from cndba.cnprj2 t where t.project_no<>'000000' group by t.project_no having sum(1)>100;

--between and 为左右都包括
select u.project_no,u.project_name_c,u.job_qty from cndba.cnprj1 u where u.job_qty between 100 and 200;

--TopN查询
select * from (select * from cndba.cnprj1 t order by t.project_no) a where rownum<=5

--通过dbms_random.value()实现随机返回5条记录
select * from (select * from cndba.cnprj1 t order by dbms_random.value()) a where rownum<=5

--通过nulls first(last)实现空值排序
select t.contract_no from cndba.cnprj1 t order by t.contract_no nulls first
select t.contract_no from cndba.cnprj1 t order by t.contract_no desc nulls last

--通过 row_number() over (order by t.contract_no)对contract_no排序后标注行号
select row_number() over (order by t.contract_no),t.contract_no,t.project_name_c from cndba.cnprj1 t

--查询奇数行数据（先标注行号后再取奇数行）
select * from (select row_number() over (order by t.contract_no) as rn,t.contract_no,t.project_name_c from cndba.cnprj1 t) where mod(rn,2)=1

--trunc(sysdate,'y')获取当前年度的第一天
select trunc(sysdate,'y') from dual;

--add_months(trunc(sysdate,'y'),12)获取下一年度的第一天
select add_months(trunc(sysdate,'y'),12) from dual;

--确定一年内的天数
select add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y') from dual;

--wm_concat实现多个字段逗号分隔
select t.project_no,wm_concat(t.job_type_a||t.job_type_b||t.job_seq_no) from cndba.cnprj2 t where t.project_no<>'000000' group by t.project_no

--decode实现条件过滤
select v.job_type_a||v.job_type_b||v.job_seq_no as job_no,decode(l.flag_install,'N',v.shp_act,v.adjust_act) from cndba.cnprj1 l,cndba.cnprj2 v where l.project_no=v.project_no and l.project_no='004848'

--oracle数字变为千分位显示
SELECT TO_CHAR('123120.4599999','FM999,999,999,999,990.00') FROM DUAL;

--结果包括汇总
select u.contract_office,u.project_no,count(1) as qty from cndba.cnprj2 u where u.project_no<>'000000' and u.l_contract_date>='20200101' group by rollup(u.contract_office,u.project_no)
order by u.contract_office,u.project_no

--update select
update test1 a set a.name=(select b.name from test2 b where a.id=b.id) where a.id in (select id from test2);
两种方法:
-- 方法1.
UPDATE 表2 SET 表2.C = (SELECT B FROM 表1 WHERE 表1.A = 表2.A) WHERE EXISTS ( SELECT 1 FROM 表1 WHERE 表1.A = 表2.A)
-- 方法2
MERGE INTO 表2
USING 表1
ON ( 表2.A = 表1.A ) -- 条件是 A 相同
WHEN MATCHED THEN UPDATE SET 表2.C = 表1.B -- 匹配的时候，更新
数据字典

--查询CNDBA用户下所有的表
select * from all_tables a where a.owner='CNDBA'

--查询CNPRJ2表中所有字段（列）
select * from all_tab_columns t where t.table_name='CNPRJ2'

--列出表的索引列
select * from sys.all_ind_columns b where b.table_name='CNPRJ2'

--列出表中约束
select * from all_constraints c where c.table_name='CNPRJ2'

--在oracle中描述数据字典视图
select * from dictionary d where d.table_name like '%TABLE%'

--查询现有索引
select * from user_indexes t

--可以获知索引建立在哪些字段上
select * from user_ind_columns k
DDL数据定义语言、DML数据操纵语言

--创建表
--tablespace不是必须的，默认不写，则创建再登录的用户所在的表空间上
create table test(
Sid number(10),
Sname varchar2(20)
) tablespace oa

--添加字段
alter table test add age number(5);

--修改字段
alter table test modify age number(10);

--删除字段
alter table test drop column age;

--清空表数据，注意删除后数据不可回滚（用delete删除数据后，可以rollback回滚）
truncate table test;

--删除表
drop table test;

--重命名
rename test to test2;

--merge使用
merge into 目标表 a
using 源表 b
on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)
when matched then update set a.更新字段=b.字段
when not macthed then insert into a(字段1,字段2……)values(值1,值2……)

create table test1(eid number(10), name varchar2(20),birth  date,salary number(8,2));
insert into test1 values (1001, '张三', '20-5月-70', 2300);
insert into test1 values (1002, '李四', '16-4月-73', 6600);

create table test2(eid number(10), name varchar2(20),birth  date,salary number(8,2)); 

merge into test2  
using test1  

on(test1.eid = test2.eid )
when matched then 

update set name = test1.name, birth = test1.birth, salary = test1.salary
when not matched then
insert (eid, name, birth, salary) values(test1.eid, test1.name, test1.birth, test1.salary);

Oracle中的5种约束类型
primary key PK 含义：主键约束，非空唯一(表中最多只能含有一个主键约束)
not null NN 含义：非空
unique UK 含义：唯一
check CK 含义：自定义约束（往往用来定义业务的约束，如分数不能为负）
foreign key FK 含义：外键约束（表示此列的值是引用自己的或其他表的主键）
注意：能够被外键所引用的列，其本身也必须是主键约束或唯一性约束

--添加主键约束
alter table test add constraint test_Sid_pk primary key(Sid);

--添加唯一性约束
alter table test add constraint test_Sname_uk unique(Sname);

--添加非空约束
alter table test modify Sname not null;

--添加外键约束
第一种方式简单粗暴，删除的时候，级联删除掉子表中的所有匹配行，在创建外键时，通过 on delete cascade 子句指定该外键列可级联删除：
alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept (deptno) on delete cascade;

第二种方式，删除父表中的对应行，会将对应子表中的所有匹配行的外键约束列置为NULL，通过 on delete set null 子句实施：
alter table emp add constraint emp_deptno_fk foreign key(deptno) references dept(deptno) on delete set null;

--添加检查约束
alter table test add constraint test_sex_ck check(sex in('男','女'));

--删除约束
alter table test drop constraint test_sex_ck;

--启用约束
alter table test enable constraint test_Sname_uq;

--禁用约束
alter table test disable constraint test_Sname_uq;

--创建视图
create or replace view testv010 as (select * from test);

--查询视图
select * from testv010

--删除视图
drop view testv010;

--创建索引，这两种索引方式是不一样的，一般情况下，需要经常访问的字段放在组合字段的前面
create index textid01 on test(Sname,Sex);
create index textid02 on test(Sex,Sname);

--删除索引
drop index textid01;
数据库维护

Oracle的几种登录方式：
1.以操作系统权限认证的oracle sys管理员登录
C:>sqlplus / as sysdba
或
C:>sqlplus "/as sysdba"
2.不在cmd或者terminal当中暴露密码的登录方式
C:>sqlplus /nolog
SQL>conn /as sysdba
或者
SQL>conn sys/password as sysdba

关闭数据库（首选这种方式）
shutdown immediate
1、shutdown normal
正常方式关闭数据库。
2、shutdown immediate
立即方式关闭数据库。
在SVRMGRL中执行shutdown immediate，数据库并不立即关闭，
而是在Oracle执行某些清除工作后才关闭（终止会话、释放会话资源），
当使用shutdown不能关闭数据库时，shutdown immediate可以完成数据库关闭的操作。
3、shutdown abort
直接关闭数据库，正在访问数据库的会话会被突然终止，
如果数据库中有大量操作正在执行，这时执行shutdown abort后，重新启动数据库需要很长时间

启动数据库
startup

创建用户
create user 用户名 identified by 密码
create user oa identified by oa default tablespace ecology temporary tablespace temp;

删除用户（cascade表示级联删除）
drop user 用户名
drop user oa cascade;

用户授权
角色是权限的集合
grant 权限（角色） to 用户
grant connect,resource,dba to oa;
UNLIMITED TABLESPACE 权限，也就是这个用户可以在其他表空间里随意建表
grant unlimited tablespace to oa;

锁住一个用户
ALTER USER 用户名 ACCOUNT LOCK|UNLOCK
alter user oa account lock;
alter user oa account unlock;

密码失效（提示用户第一次链接的时候需要修改密码，让用户的密码到期）
alter user oa password expire;

对象授权
GRANT 权限（all、select、update、insert、delete） ON schema.table TO 用户

对象权限收回
REVOKE 权限 ON schema.table FROM 用户

查看权限
select * from user_sys_privs t;

角色操作
create role myrole;
grant create table to myrole;
drop role myrole;

显示当前登录用户
SQL>show user;

创建database link
create public database link mydblink6 connect to "System"
identified by "manager"
using '( DESCRIPTION =
( ADDRESS_LIST =
( ADDRESS = (PROTOCOL = TCP)
(HOST = 10.105.10.77)
(PORT = 1521)
)
)
(CONNECT_DATA = (SERVICE_NAME = HS01)
)
)';

使用database link进行查询
select * from sydba.syuserms@hs03;

删除database link
drop public database link mydblink6;

存储函数权限授予
grant execute on get_working_date to sddba;
grant execute on get_working_num TO PUBLIC;

 

//--------------

查询表锁定 2022-7-28

https://www.php.cn/oracle/488267.html
怎样查询oracle被锁的表
//------
select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;

Oracle数据库利用闪回时间恢复删除数据
select * from emdba.empmms as of timestamp to_timestamp('2022-08-04 09:07:01','yyyy-mm-dd hh24:mi:ss')
where part_no='0100ADJC1'

 

//----------------

 

Analyze table dcdba.dcjbsc_21 compute statistics;
数据库调优命令

 

//---数据库自增序号

CREATE SEQUENCE XXXXX
INCREMENT BY 1
START WITH 1001--此为获取的初始值
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER

select XXXXX.nextval as get_id from dual