oracle 基础

----oracle 入门
---------oracle 原理流程
                   
用户进程→服务进程(PGA) 
                        SGA
                         ↑   
                  →内存结构
                             →共享池      :用来对SQL语句,PLSQL语句进行解析,执行的内存区域,其大小直接影响数据库性能,库缓寸和数据字典缓寸组成
                             →数据缓冲区  :用于在内存中保存从磁盘中读取的数据,所有用户共享,其大小直接影响数据库读取数据的速度.
                             →日志缓冲    :在内存中用来记录数据库数据修改的记录,如果数据量过大,后台进程会将其写入日志文件当中.                       
                  →后台进程
                             →PMON   :清理故障进程,释放当前挂起的锁,释放故障进程所占用的资源.
                             →SMON   :在实列启动失败后,重新打开数据库将自动恢复,整理数据文件,将相临近的区域结合起来,释放不在使用的临时段
                             →DBWR   :管理数据缓冲区,将最近使用的的块保留到内存当中,将修改后的数据写入数据文件当中.
                             →LGWR   :将日志缓冲区的日志写入日志文件当中,多个日志文件 则以循环形式进行.
                             →其他
                       ↓         ↓          ↓
                   数据文件    控制文件    日志文件   →归档日志文件
          参数文件←
          口令文件←
         
PGA:程序全局区
→单个服务器进程所需的数据和控制信息.
→PGA是在用户进程连接到数据库并创建一个会话时自动分配的，保存每个与Oracle 数据库连接的用户进程所需的信息
→PGA为非共享区，只能单个进程使用，当一个用户会话结束，PGA释放

SGA:系统全局区
→内存结构                 

oracle 数据库服务器: oracle数据库和 oracle数据库实例 组成的.

oracle 实例 :主要指 管理数据库后台的进程 和 内存 结构 的 集合 .

oralce 启动便会 分配系统全局区(内存)和启动 后台进程.  

oralce 物理结构: 数据文件  控制文件 日志文件

oralce 逻辑结构: 段,区,数据块,模式

段:       数据段、索引段、回退段和临时段。构成表空间的逻辑存储结构，段由一组区组成.
区:       由连续的数据块组成,区不能跨数据文件存在，只能存在于一个数据文件中.
数据块:   是Oracle服务器所能分配、读取或写入的最小存储单元,Oracle服务器以数据块为单位管理数据文件的存储空间.

模式:     对用户所创建的数据库对象,包括表、视图、索引、同义词、序列、过程和程序包等.

---表空间

----uniform本地表空间
----SIZE为每个区的大小，默认为1MB。此表空间区的大小相同
create tablespace dragchong1
datafile '/oracle9i/dragchong/test1.dbf' size 10M
extend management local uniform size 256k;

----autoalllocate本地表空间
---本地表空间的默认类型，区的大小由系统自动分配
create tablespace dragchong2
datafile '/oracle9i/dragchong/test2.dbf' size 10M
extend management local authoalllocate;

---回滚表空间
create undo tablespace dragchong3
datafile '/oralce9i/dragchong/test3.dbf'  size 10M;

---临时表空间    
---一般不要使用,除了特殊需要
---由于表空间不段分配空间和 删除数据容易产生大量磁盘碎片
create temporary tablespace dragchongtemp
tempfile '/oralce9i/dragchong/test4.dbf' size 10M;

---修改表空间大小

---增加表空间大小,追加了一个数据文件
alter tablespace dragchong2
add datafile '/oralce9i/dragchong/test5.dbf' size 10M;

---改变表空间大小
alter tablespace dragchong2
modify datafile '/oralce9i/dragchong/test5.dbf' resize 15M;

 

---移动数据文件
---------数据文件脱机
alter tablepace dragchong2 offline;
---------使用以下命令
alter tablespace dragchong2 datafile '/oralce9i/dragchong/test5.dbf'
to '/oralce9i/chenchong/test5.dbf'
---------然后 在操作系统上进行手动的物理上的移动数据文件
copy===past
---------再将数据文件改为在线
alter tablespace dragchong2 online;

---给指定表空间  给定指定的用户
create user dragchong
identified by 123456
default tablespace dragchong2
--temporary tablespace dragchongtemp
--quota 15m On dragchong2
--password expire;

--修改用户密码
alter user dragchong2 identified by 111111;
--修改用户默认表空间
alter user dragchong2 default tablespace dragchong2;

--给用户 加锁 /解锁
alter user dragchong2 lock;
alter user dragchong2 unlock;

---删除用户   
---在线用户不能被删除
---如果用户创建了实列,则必须要cascade
drop user dragchong2 cascade;

 

----权限
--------赋予权限
grant create any table to dragchong2;
--------回收权限
revoke create any table from dragchong2;

----角色
connection ,resource
----操作权限
select, update ,all

 

---------------------------------------------SQL--语法----------------------------------------------------------------
----创建表
create table emp(userid varchar2(10));
create table smp(userid varchar2(10),constraint O_USERID primary key (USERID));

create table smp as select *from smp;
                
alter table emp add(username varchar2(10));          ---增加字段
alter table emp add constraint K_USERID primary key (USERID)   ---增加主键
alter table emp modify(username varchar2(20));        ---修改字段  大小
alter table emp add constraint f_USERID foreign key (USERID) reference smp(userid);   ---增加主键

----给表分区  缺

create table T_PBH_PROBLEMPROCESSHIS
(
  SERIALNO                 VARCHAR2(20) not null,
  SRID                     VARCHAR2(20),
  SRTYPEID                 VARCHAR2(128),
  ORIGINALSRTYPEID         VARCHAR2(128),
  ADVISEDSRTYPEID          VARCHAR2(128),

)
TABLESPACE TBS_CSP_WF_HISDAT
PARTITION BY RANGE (SERIALNO)
(
  PARTITION P_07_09_1 VALUES LESS THAN ('20070911'),
  PARTITION P_MAX_VALUE VALUES LESS THAN ( MAXVALUE )
);

----对表的基本操作   

----查
select *from emp;

----改
update emp set userid='1' where userid is null;

----增
insert into  emp(userid) values ('2');

----删
delete from emp;

delete *from emp where emp.userid='1';

truncate  table  emp;

-----创建索引

索引:对存储在磁盘上的数据进行映射,将路径记录下来,用户进行查询操作 会根据索引去找数据,速度很快的
     → 一般上在海量的数据才建立索引的,没有海量的数据,也没必要建立索引,性能消耗
     → 表更新比较频繁，那么在索引中删除标示会越来越多，这时索引的查询效率必然降低，所以应该定期重建索引，来消除索引中这些删除标记
    
    
建立索引原则一:
     →  表的主键、外键必须有索引，当PRIMARY KEY和UNIQUE  KEY的约束有效时，Oracle自动创建此索引。
     →  经常与其他表进行连接的表，在连接字段上应该建立索引。
     →  经常出现在Where子句中的字段，特别是大表的字段，应该建立索引。
     →  索引应该建在选择性高的字段上，否则即使建了索引也无法被利用。
     →  索引应该建在小字段上，对于大的文本字段甚至超长字段，如LOB、LONG、LONG RAW等类型字段不能建索引。
     →  复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替。
     →  频繁进行数据操作的表，不要建立太多的索引。
     →  避免使用位图索引，特别是频繁进行数据操作的表，禁止使用位图索引

建立索引原则二:
      → 对于分区表，除有唯一性要求的字段建全局索引外，其它字段一律建成局部索引。
      → 索引对应SELECT语句来说，是加快了检索的速度，但对于UPDATE、INSERT和DELETE来说，则是减慢了速度。因此对于临时性的大批量数据操作，尽可能先删除索引，事务完成后再重建。
      → 删除无用的索引，避免对执行计划造成负面影响。
      → 频繁进行数据操作的表，需对索引进行周期性重建。
      → SQL语句的WHERE子句中应尽可能将操作移至等式的右边，特别是索引字段的操作，必要时可考虑建函数索引。
      → SQL语句的WHERE子句中操作符两边类型应相同，避免潜在的数据类型转换
   
自动索引:     表的主键, 会自动产生索引.
手动索引:     create index indext_emp on emp(userid)
重建立索引:   alter index indext_emp on emp(userid)

-----创建序列
CREATE SEQUENCE toys_seq
 START WITH 10
 INCREMENT BY 10
 MAXVALUE 2000
 MINVALUE 10
 NOCYCLE
 CACHE 10;
 
-----创建触发器
CREATE OR REPLACE TRIGGER del_emp_deptno
  BEFORE DELETE ON dept
  FOR EACH ROW
BEGIN
  DELETE FROM emp WHERE deptno = :OLD.deptno;
END;

------删除触发器
DROP TRIGGER del_emp_deptno on dept;

---------------------------------------------PLSQL--语法----------------------------------------------------------------
基本语法:
----------------------------

--循环语句

loop
x:=x+1;

if(x>10)then
exit;

end loop;
-----------------------------
loop
x:=x+1;

exit when x>10;

end loop;
-----------------------------
X:=100;
FOR v_Counter IN 1..10 LOOP
X := X + 10;
END LOOP;
Y := X;

-----------------------------
--判断语句

if(x>y)then
   dbms_output.put_line(x>y);
   if                      
elsif(x<y)then
   dbms_output.put_line(x<y);
else
   dbms_output.put_line(x=y);
}
----------------------------
----跳转
DECLARE
  X         NUMBER(3);
  Y         NUMBER(3);
  V_COUNTER NUMBER(2);
BEGIN
  X := 100;
  FOR V_COUNTER IN 1 .. 10 LOOP
    IF V_COUNTER = 4 THEN
      GOTO end_of_loop;
    END IF;
    X := X + 10;
  END LOOP;
  <<end_of_loop>>
  Y := X;
  dbms_output.put_line('Y:'||Y);
END;
----------------------------
-----创建存储过程
create or replace procedure testOne
(
 userid in varchar2;
 username out varchar2
)
as--is
v_sql varchar2(1000);
v_username varchar2(10);
begin
  v_sql='select emp.username into '||v_username || 'from emp where emp.id =' '||v_username||';
  execute immediate v_sql;
  username :=v_username; 
end 
-----创建函数

-----异常
----------异常处理
begin
x:=x+1
exception
when NO_DATA_FOUND then
dbms_output.put_line(x);
when OTHERS then
dbms_output.put_line(x);
end;
----------自定义异常
declare
salary varchar2(10)
text Exception;
begin
loop
salary :=salary+1;
if salary in('6','7','8')then
raise text;
end if;
end loop;
exception
when text then
dbms_output.put_line('出错了');
end;

-----创建游标
DECLARE
CURSOR
c_emp IS SELECT?ename, salary FROM emp;
R_emp c_emp%ROWTYPE;
[R_emp EMP%ROWTYPE; ]
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUT.PUT.PUT_LINE('Salary of Employee'||r_emp.ename||                 
                          'is'||r_emp.salary);
END  LOOP;
CLOSE c_emp;
END;

-----包
------------------创建包头
CREATE PACKAGE test_package IS
  --定义变量
  man_num   NUMBER;
  woman_num NUMBER;
  --定义游标
  CURSOR学生;

  --定义函数
  CREATE FUNCTION f_count(in sex IN 学生.sex%TYPE)
  --定义返回值类型
  RETURN NUMBER;

  --定义过程
  CREATE PROCEDURE p_count(in_sex IN 学生.sex%TYPE, out_num OUT NUMBER);

--包头结束
END test_package;

 

---------------创建包体
CREATE PACKAGE BODY test_package AS
  --游标具体定义
  CURSOR 学生IS
    SELECT 学号，姓名 FROM 学生 WHERE 学号 < 50;

  --函数具体定义
  FUNCTION f_count(in_sex IN学生.sex%TYPE)
  --定义返回值类型
   RETURN NUMBER IS
    out_num NUMBER;
    --函数体
  BEGIN
    IF in_sex = '男' THEN
      SELECT count(sex) INTO out_num FROM 学生 WHERE性别＝'男';
    ELSE
      SELECT count(sex) INTO out_num FROM 学生 WHERE 性别＝'女';
    END IF;
    --返回函数值
    RETURN(out_num);
    --函数定义结束
  END f_count;

  --过程具体定义
  PROCEDURE p_count(in_sex IN学生.sex%TYPE， out_num OUT NUMBER) AS
    --过程体
  BEGIN
    IF in_sex = '男' THEN
      SELECT count(sex) INTO out_num FROM 学生 WHERE性别 = '男';
    ELSE
      SELECT count(sex) INTO out_num FROM 学生 WHERE 性别＝ '女';
    END IF;
    --过程定义结束
  END P_count;

--包体定义结束
END test_package;

 

---------------导入  导出

----按用户方式导出数据
exp scott/tiger@accp file=scott_back owner=scott

----按表方式导出数据
exp scott/tiger@accp tables=(emp, dept) file=scott_back_tab

----按表空间方式导出数据
exp system/aptech@accp tablespaces=(users) file=tbs_users

----使用参数文件导出数据
exp system/aptech parfile='C:\parameters.txt'

----将整个文件导入数据库
imp accp/accp@accp file=item_back.dmp ignore=y full=y

----将scott用户的表导入到martin用户
imp system/aptech@accp file=scott_back fromuser=scott touser=martin tables=(emp,dept)

----使用参数文件导入数据
imp system/oracle parfile='C:\parameters.txt'

 

---常用操作   实战
如果你知道字段的名称,但不知道在哪个表中,可以使用以下语句:

select * from  user_tab_cols where COLUMN_NAME like '%STAFF%';
 
如果你知道表的大概的名称,但又不知道具体的表的名称.可以使用以下语句:

select * from tab where TNAME like '%T_UCP_ME%'