Oracle 基础

目录

1、基本概念

2、实施操作

2.1、新建数据库

2.2、用户操作

2.3、授权操作

2.4、角色操作

2.5、表空间操作

3、系统操作

3.1、会话操作

3.2、删除外键 删除所有表

3.3、参数操作

4、常见报错

5、SQL语法

---

1、基本概念

• 用户：就是普通理解的用户，也叫Schema
• 对象：用户创建的表、索引、函数、过程等
• 权限：就是普通理解的权限
• 角色：角色是一组权限的集合，将角色赋给一个用户，这个用户就拥有了这个角色中的所有权限。系统角色（dba、connect，resource）
• 表空间：多个用户可以公共一个表空间，也叫tablespace
• 实例：一个数据库就是一个实例，一个实例里面可以有多个表空间，对应唯一的sid

2、实施操作

2.1、新建数据库

链接

2.2、用户操作

create user test identified by test;
alter user test identified by 123456;
drop user test;
若用户拥有对象，则不能直接删除，否则将返回一个错误值。指定关键字cascade,可删除用户所有的对象，然后再删除用户。
drop user test cascade;

查询所有用户（新建用户在上面）
select * from all_users;

2.3、授权操作

grant connect, resource to test;
revoke connect, resource from test;

2.4、角色操作

create role testrole;
grant select on class to testrole;   查询权限
grant create session to testrole;   登陆权限
grant testrole to hjq;   给用户授予testrole角色的权限
drop role testrole;

查询当前用户所拥有的角色权限
select * from role_sys_privs;

2.5、表空间操作

CREATE TABLESPACE test01
 DATAFILE 'C:\APP\ADMINISTRATOR\ORADATA\ORCL\TEST01.DBF'
 SIZE 20M
 AUTOEXTEND ON;

为用户分配表空间
create user global identified by global
default tablespace ODPS
temporary tablespace temp profile default;

查询当前用户所属表空间
select username,default_tablespace from user_users;

查询数据量
select tablespace_name ,sum(bytes) / 1024 / 1024 as MB　from dba_data_files group by tablespace_name order by MB desc;
select t.table_name,t.num_rows from all_tables t where num_rows > 0 order by num_rows desc;

表空间使用情况
select a.file_id,
       a.tablespace_name,
       round(a.bytes / 1024 / 1024, 0) || 'MB' all_size,
       round((a.bytes - sum(nvl(b.bytes, 0))) / 1024 / 1024, 0) || 'MB' used,
       round(sum(nvl(b.bytes, 0)) / 1024 / 1024, 0) || 'MB' remain,
       round(sum(nvl(b.bytes, 0)) / a.bytes * 100, 0) || '%' idle_rate
  from dba_data_files a, dba_free_space b
 where a.file_id = b.file_id(+)
 group by a.tablespace_name, a.file_id, a.bytes
 order by a.tablespace_name;

开启自动扩容是指，单个文件可以自动扩容到最大32G

临时表空间主要用途是在数据库进行排序运算[如创建索引、order by及group by、distinct、union/intersect/minus/、sort-merge及join、analyze命令]、管理索引[如创建索引、IMP进行数据导入]、访问视图等操作时提供临时的运算空间，当运算完成之后系统会自动清理。当临时表空间不足时，表现为运算速度异常的慢，并且临时表空间迅速增长到最大空间（扩展的极限），并且一般不会自动清理了。

3、系统操作

3.1、会话操作

查看锁表--指定表
SELECT sid, serial# 
FROM v$session 
WHERE sid = (SELECT distinct sid FROM v$lock 
WHERE id1 = (SELECT object_id  FROM dba_objects 
WHERE object_name = UPPER('R_D_OD_SEQTRADE')));

查看锁表--所有表
SELECT SESS.SID,
       SESS.SERIAL#,
       LO.ORACLE_USERNAME,
       LO.OS_USER_NAME,
       AO.OBJECT_NAME,
       LO.LOCKED_MODE
  FROM V$LOCKED_OBJECT LO, DBA_OBJECTS AO, V$SESSION SESS
 WHERE AO.OBJECT_ID = LO.OBJECT_ID
   AND LO.SESSION_ID = SESS.SID;

删除会话
ALTER system kill session 'sid,serial#';

3.2、删除外键 删除所有表

SELECT 'ALTER TABLE ' || table_name || ' disable CONSTRAINT ' || constraint_name || ';' FROM user_constraints where CONSTRAINT_TYPE = 'R';
 拿到的结果全部执行一遍，然后当前用户下所有表
begin
  for t in (select table_name from user_tables) loop
   execute immediate 'drop table '||t.table_name;
  end loop;
end;

3.3、参数操作

查询字符集
select * from nls_database_parameters

版本信息
select * from v$version

4、常见报错

ORA-00600：internal error code, arguments:[kkqtSetOp.1],[],[],[],[],[],[],[],[],[],[],[],[]

解决：/*+OPT_PARAM('_optimizer_cost_based_transformation' 'off')*/

5、SQL语法

1、分区表

create table T_S_USER (C_IDEN varchar2(25) NOT NULL PRIMARY KEY, C_USER_CODE varchar2(20) not null, c_user_name varchar2(50) not null, T_CREATE_TIME timestamp(9) default systimestamp not null)
PARTITION BY RANGE (T_CREATE_TIME)
(PARTITION TUSER20191231 VALUES LESS THAN (TIMESTAMP' 2019-12-31 23:59:59'),
 PARTITION TUSER20201231 VALUES LESS THAN (TIMESTAMP' 2020-12-31 23:59:59'),
 PARTITION TUSERMAX VALUES LESS THAN (MAXVALUE));

--分区查询
select * from t_s_user PARTITION (TUSER20221231);

--新增分区
alter table t_s_user rename partition TUSERMAX to TUSER20221231;

alter table t_s_user
split partition TUSER20221231 at (TO_TIMESTAMP('2022-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss', 'NLS_CALENDAR=GREGORIAN'))
into (partition TUSER20221231, partition TUSERMAX);

2、insert

INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 值1, 值2, ……);
INSERT INTO 表名(字段名1, 字段名2, ……) SELECT (字段名1, 字段名2, ……) FROM 另外的表名;
INSERT INTO 表名 SELECT * FROM 另外的表名;

3、delete/truncate

DELETE FROM 表名 WHERE 条件;
TRUNCATE 表名;

注意：删除记录并不能释放ORACLE里被占用的数据块表空间。它只把那些被删除的数据块标成unused，如果确实要删除一个大表里的全部记录，可以用 TRUNCATE 命令，它可以释放占用的数据块表空间，TRUNCATE TABLE 表名。此操作不可回退.

4、update

UPDATE 表名 SET 字段名1 = ?, 字段名2 = ? WHERE 条件;

如果修改的值N没有赋值或定义时，将把原来的记录内容清为NULL，最好在修改前进行非空校验，值N超过定义的长度会出错, 最好在插入前进行长度校验。
注意事项：在运行INSERT、DELETE 和 UPDATE 语句前最好估算一下可能操作的记录范围，应该把它限定在较小 (一万条记录) 范围内,。否则ORACLE处理这个事物用到很大的回退段，程序响应慢甚至失去响应. 如果记录数上十万以上这些操作, 可以把这些SQL语句分段分次完成，其间加上COMMIT 确认事物处理

5、alter

ALTER TABLE 表名1 TO 表名2;
ALTER TABLE 表名 ADD (字段名1 字段名描述1, 字段名2 字段名描述2);
ALTER TABLE 表名 MODIFY 字段名 字段名描述;

6、drop

(删除表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)

 删除表和它所有的约束条件
DROP TABLE 表名 CASCADE CONSTRAINTS;

8、group by

select c_plan_code, count(c_view_user) from t_s_Wf_Rela t where c_plan_code='11111' group by c_plan_code;

9、序列

create sequence SEQU_S_MT_SQL minvalue 1 maxvalue 9999999999999999999999999999 start
  with 1 increment by 1 cache 20;

 获取序列
SELECT NCME_QUESTION_SEQ.nextval FROM dual 
SELECT NCME_QUESTION_SEQ.CURRVAL FROM dual

create table T_S_USER (C_IDEN NOT NULL PRIMARY KEY C_USER_CODE varchar2(20) not null, c_user_name varchar2(50) not null, T_CREATE_TIME timestamp(9) default systimestamp not null);

10、注释

comment on table T_S_MT_SQL is 'SQL监控管理';
comment on column T_S_MT_SQL.c_iden is '系统流水号';

11、timestamp

create table T_S_MT_SQL(c_iden VARCHAR2(30) not null, d_create_time timestamp(9) default systimestamp not null);
D_CREATE_TIME >= to_timestamp(?, 'yyyy-mm-dd hh24:mi:ss.ff9')

Java 代码后台直接setObject   java.util.Date类型即可

12、constraint

建表后可以增加约束：
ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... , column_n);

建表语句中可以增加字段约束：
create table table_name
(  
  column1      number not null,  
  column2      number not null,  
  column3      varchar2(50),  
  CONSTRAINT constraint_name UNIQUE (column2) --定义唯一性约束  
);

13、NVL

select NVL(c_iden, c_user_code) as ABC from t_s_user;

返回其中不为null的字段，若都为null则返回null，若都不为null则返回第一个字段

14、union

select * from t_test1
union
select * from t_test2;

如果我们需要将两个select语句的结果作为一个整体显示出来，我们就需要用到union或者union all关键字。union(或称为联合)的作用是将多个结果合并在一起显示出来。union和union all的区别是,union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。联合在Java代码中来拼接常量数据的办法（多字段的联合查询只能先建立临时表操作）：

15、start with connect by

select level, * from table  ##  level
start with org_id = ‘HBHqfWGWPy’  ## 条件1
connect by prior org_id = parent_id;  ## 条件2
where ...  ##  条件3

• Level：代表第N级节点

• 条件1：是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。

• 条件2：是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR org_id = parent_id；就是说上一条记录的org_id 是本条记录的parent_id，即本记录的父亲是上一条记录。运算符PRIOR被放置于等号前后的位置，决定着查询时的检索顺序

• 条件3：是过滤条件，用于对返回的所有记录进行过滤。

16、exists

select * from ttt t where not exists (select 1 from qqq q where t.ids = q.ids);
跟下面的等价
select * from ttt t where not exists (select * from qqq q where t.ids = q.ids);

 理解：先通过条件建立连接，再将连接部分从主表剔除

17、可重复执行SQL

--1、创建表
DECLARE
  VC_STR           VARCHAR2(5000);
  VN_COUNT         NUMBER;
BEGIN
  SELECT COUNT(*) INTO VN_COUNT
    FROM USER_TABLES
   WHERE TABLE_NAME = 'TB_BALANCE';
  --如果没有则新增表，如果有就不处理
  IF VN_COUNT < 1 THEN
    VC_STR := 'create table TB_BALANCE (
          ID              NUMBER not null,
          BILL_TYPE       CHAR(1),
          CORP_SCALE_CODE CHAR(4),
          INDUSTRY_CODE   VARCHAR2(8),     
          BALANCE         NUMBER(18,2),
          END_DATE        DATE
        )';
    EXECUTE IMMEDIATE VC_STR;
  END IF;
END;


-- 2增加表字段
DECLARE
  VN_COUNT     NUMBER;
  V_STR        VARCHAR2(1000);
BEGIN
select COUNT(*) 
  INTO VN_COUNT
  from USER_TAB_COLUMNS 
 where table_name = 'TB_BALANCE' AND COLUMN_NAME = 'CUR_CODE';
IF VN_COUNT < 1 THEN
  V_STR := ' alter table TB_BALANCEadd(CUR_CODE VARCHAR2(50) default ''01'' not null)';
  EXECUTE IMMEDIATE V_STR;
END IF;
END;


--3、判断表bis_acc_his_bal是否已经建有名为PK_BIS_ACC_HIS_BAL的主键，没有则新增
DECLARE
  VN_COUNT    NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO VN_COUNT
    FROM user_constraints WHERE constraint_type = 'P' and constraint_name = 'PK_BIS_ACC_HIS_BAL' and table_name = 'BIS_ACC_HIS_BAL';
  IF VN_COUNT < 1 THEN
   EXECUTE IMMEDIATE 'alter table bis_acc_his_bal add constraint PK_BIS_ACC_HIS_BAL primary key(BANK_ACC, BAL_DATE)';
  END IF;
  COMMIT; 
END;


--4、插入数据
DECLARE
VN_COUNT   NUMBER;
BEGIN
SELECT COUNT(*) INTO VN_COUNT FROM TB_BALANCE WHERE  FORMULA_CODE = 'GETTOPDKYE';
IF VN_COUNT <1 THEN
INSERT INTO TB_BALANCE(ID, FORMULA_NAME, HELP, FORMULA_CODE)VALUES
(NVL((SELECT MAX(ID) + 1 FROM RPT1104_REPORT_FORMULA),1),
   'GETTOPDKYE（****）',
   'GETTOPDKYE（*****）:<br>
    参数1:贷款科目的代码<br>
    参数2:数据项，1-10<br>
    参数3:第几列；包括：客户名称，客户代码，最高风险额，贷款余额<br>
    示例:GETTOPDKYE(1303,1,1)<br*********<br>',
   'GETTOPDKYE'
 );
END IF;
   COMMIT;
END;


--5、判断表bis_acc_his_bal是否已经建有名为PK_BIS_ACC_HIS_BAL的索引，有则删除
DECLARE
  VN_COUNT    NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO VN_COUNT
    FROM user_indexes WHERE index_name = 'PK_BIS_ACC_HIS_BAL' and table_name = 'BIS_ACC_HIS_BAL';
  IF VN_COUNT >= 1 THEN
   EXECUTE IMMEDIATE 'drop index PK_BIS_ACC_HIS_BAL';
  END IF;
  COMMIT; 
END;

18、索引

Create Index Index_Name On Table_Name(Column_Name);
Create Index i_deptno_job on emp(deptno,job);
--全文索引
CREATE INDEX myindex ON mytable(mycolumn) indextype is ctxsys.context parameters('lexer my_lexer');
--函数索引
create index indexname on table(substr(fileld,0,2)) 

19、merge into

qqq：ids, names
1    qqq1
2    qqq2
3    qqq3   
4    qqq4
5    qqq5
6    qqq6
7    qqq7
8    qqq8

www: ids, names
1    www1
4    www4
6    www6
9    www9
10   www10

MERGE INTO qqq t1
USING (select * from qqq where ids in ('1','4','9','10')) t2
ON (t1.ids = t2.ids)
WHEN MATCHED THEN
  UPDATE SET t1.names = t2.names
WHEN NOT MATCHED THEN
  INSERT (t1.ids, t1.names) VALUES (t2.ids, t2.names);

结果：qqq
1    www1
2    qqq2
3    qqq3   
4    www4
5    qqq5
6    qqq6
7    qqq7
8    qqq8
9    www9
10   www10

20、for

declare
 cursor my_cursors is 
  select distinct c_fun_code from t_s_fun_rights where rownum < 3680;
begin
  for my_cursor in my_cursors loop
    insert into t_s_fun_rights values(my_cursor.c_fun_code, 'SealRelaInfo', '');
  end loop;
end;

21、decode

DECODE(value,if1,value1,if2,value2,if3,value3,...,else)
 含义为
IF 条件=值1 THEN
　　　　RETURN(value 1)
ELSIF 条件=值2 THEN
　　　　RETURN(value 2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(value 3)
ELSE
　　　　RETURN(default)
END IF

22、partition by

select row_number() OVER (PARTITION BY 分组字段1,分组字段2 ORDER BY 排序字段1) from table;

取分组排序后的第一条

select * from 
(select row_number() OVER (PARTITION BY 分组字段1,分组字段2 ORDER BY 排序字段1) as rid, t.* from table t)
where rid = 1;

23、clob字段模糊查询

select * from table t where dbms_lob.instr(t.name,'xxx',1,1)>0;

24、package

包
CREATE OR REPLACE PACKAGE vastcm_utils IS
  FUNCTION is_table_exists( table_name_to_check  VARCHAR2) RETURN BOOLEAN;
END;

包体
CREATE OR REPLACE PACKAGE BODY vastcm_utils IS
  FUNCTION is_table_exists( table_name_to_check  VARCHAR2)
  RETURN BOOLEAN IS
      row_count  NUMBER;
      is_exists  BOOLEAN;
  BEGIN
      SELECT count(*) INTO row_count
      FROM dual
      WHERE EXISTS
            ( SELECT * FROM user_tables WHERE table_name = upper(table_name_to_check) );
      IF row_count = 0 THEN
        is_exists := FALSE;
      ELSE
        is_exists := TRUE;
      END IF;
      RETURN is_exists;
  END;
END;