WHCSRL 技术网

Oracle 语法

处理数据
一)Sql类型:
         1.DML(Data Manipulation Language 数据操作语言):insert,update,delete,select
         2.DDL(Data Definition Language 数据定义语言):create,alter, drop,truncate,sequence,index
         3.DCL(Data Control Language 数据控制语言):grant(授权),revoke(撤销权限)
二)插入数据(insert)
  单条插入:        
1.INSERT INTO TABLE(COLUMN1[, COLUMN2, …]) VALUES(VALUE1[, VALUE2, …])
         2.地址符 &
           INSERT INTO TABLE(COLUMN1[, COLUMN2, …]) VALUES(&param1[, &param2, …])
  多条插入:
         1.INSERT INTO TABLE1( COLUMN1[, COLUMN2, …]) SELECT (COLUMN1[, COLUMN2, …]) FROM TABLE2 [WHERE …]
三)更新数据(update)
         UPDATE TABLE
         SET    COLUMN1 = VALUE1[, COLUMN2 = VALUE2, …]
         [WHERE conditions];
四)删除数据(delete)
         DELETE [FROM] TABLE [WHERE condition];
  删除整张表的数据:
         1.DELETE [FROM] TABLE
         2.TRUNCATE TABLE
DELETE和TRUNCATE区别:
  1.detete逐条删除数据,truncate先摧毁表,再重建表
  2.delete不会释放空间,truncate会释放空间
  3.delete是DML语句,truncate是DDL语句
  4.delete可以闪回,truncate不可以
  5.delete会产生碎片,truncate不会产生碎片
  6.delete语句效率高。
五)数据库事务
  事务组成:
         1.一个或多个DML语句;
         2.一个DDL语句;
3.一个DCL语句。
  事务标志:
         1.起始标志:事务中第一条DML语句
         2.结束标志:a.提交:显式:commit
                               隐式:正常退出(exit),DDL语句,DCL语句
b.回滚:显式:rollback
                        隐式:非正常退出,停电,宕机
  保存点:
         1.创建保存点a:savepoint a
         2.滚到保存点a:rollback to savepoint a
六)隔离级别(待补充)
 
创建和管理表
一)创建表
  必须具备:
         a. CREATE TABLE权限
         b.存储空间
  必须指定:
         a.表名
         b.列名,数据类型,数据类型的大小
CREATE TABLE TABLE1 (column datatype [DEFAULT expr][,…])
 
常用数据类型:

类型描述
VARCHAR2(size)可变长字符数据
CHAR(size)定长字符数据
NUMBER(p,s)可变长数值数据,p为有效位数,s为精确到小数点左边或右边的位数
DATE日期型数据
LONG可变长字符数据,最大可达到2G
CLOB字符数据,最大可达到4G
RAW and LONG RAW原始的二进制数据
BLOB二进制数据,最大可达到4G
BFILE存储外部文件的二进制数据,最大可达到4G
ROWID行地址(伪列)

注1:定点数的精度§和刻度(s)遵循以下规则:
1)  当一个数的整数部分的长度> p-s 时,Oracle就会报错
2)  当一个数的小数部分的长度> s 时,Oracle就会舍入。
3)  当s(scale)为负数时,Oracle就对小数点左边的s个数字进行舍入。
4)  当s > p 时, p表示小数点后第s位向左最多可以有多少位数字,如果大于p则Oracle报错,小数点后s位向右的数字被舍入
  使用自查询创建表:
         CREATE TABLE TABLE1 [(column, column…)] AS subquery;
二)修改表
  追加新的列:
         ALTER TABLE table1 ADD column datatype;
  修改现有的列:
         ALTER TABLE table1 MODIFY column datatype;
  删除一个列:
         ALTER TABLE table1 DROP COLUMN column;
  重命名列:
         ALTER TABLE table1 RENAME column1 TO column2;
  重命名表:
         RENAME table1 TO table2;
 
三)删除表
         1.数据和结构都被删除;
         2.所有正在运行的相关事务被提交;
         3.所有相关索引被删除;
         4.DROP TABLE 语句不能回滚,但是可以闪回。
         DROP TABLE table1 [PURGE];
只是将表放入到回收站中,并且重命名表。如果加上PURGE参数则不经过回收站直接删除表。
  查看回收站:
         SHOW RECYCLEBIN;
  清空回收站:
         PURGE RECYCLEBIN;
  查看回收站中的表:
         SELECT * FROM “table_recyclebin_name”;
四)约束
  约束是表一级的限制;
  如果存在依赖关系,约束可以防止错误的删除数据;
  约束的类型:

NOT NULL非空约束
UNIQUE唯一性约束,不能够重复
PRIMARY KEY主键约束
FOREIGN KEY外键约束
CHECK检查性约束

外键约束:
         1.FOREIGN KEY:在子表中,定义一个表级的约束
         2.REFERENCES:指定表和父表中的列
         3.ON DELETE CASCADE:当删除父表时,级联删除子表记录
         4.ON DELETE SET NULL:当删除父表时,将子表的相关依赖记录的外键置为null。
  使用CONSTRAINT关键字为约束命名:
         …, salary NUMBER(2) CONSTRAINT emp_salary_min CHECK (salary > 0), …
其他数据库对象
一)视图
         1.视图是一种虚表,建立在已有表的基础上,视图赖以建立的这些表称为基表。
         2.向视图提供数据内容的语句为SELECT语句,可以将视图理解为存储起来的SELECT语
句。
         3.视图向用户提供基表数据的另一种表现形式。
视图优点:
         1.简化复杂查询;
         2.同样的数据,可以有不同的显示方式;
         3.限制数据访问;
         4.提供数据的相互独立。
         **但视图不能呢个提高性能。
  创建视图:
         CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view1[(alias[, alias]…)] AS subquery
         [WITH CHECK OPTION [CONSTRAINT constraint]]
         [WITH READ ONLY [CONSTRAINT constraint]];
         - FORCE:子查询不一定存在
         - NOFORCE:子查询存在(默认)
         - WITH CHECK OPTION:通过该视图只能操作看得到的数据,即该视图查到的数据
         - WITH READ ONLY:只能做查询操作
二)序列
  可供多个用户用来产生唯一数值的数据库对象。
         -自动提供唯一的数值
         -共享对象
         -主要用于提供主键值
         -将序列值装入内存可以提高访问效率
         -序列在下列情况会出现裂缝:
                   回滚,系统异常,多个表同时使用同一序列
         -如果不将序列的值装入内存(NOCACHE),可使用表USER_SEQUENCES查看序列当前的
有效值。
  定义序列:
         CREATE SEQUENCE seq [INCREMENT BY n] 步长为n
                       [START WITH n] 起始值为n
                       [{MAXVALUE n | NOMAXVALUE}] 有无最大值
                       [{MINVALUE n | NOMINVALUE}] 有无最小值
                       [{CYCLE | NOCYCLE}] 是否循环
                       [{CACHE n | NOCACHE}]序列缓存长度,默认为20,NOCACHE为1
  序列取值:
         NEXTVAL: 返回序列中下一个有效的值。
         CURRVAL:存放序列的当前值。
         NEXTVAL应在CURRVAL之前指定,二者应同时有效
  删除序列:
         DROP SEQUENCE seq;  删除之后序列不能再次被引用
三)索引
  一种独立于表的模式对象,可以存储在与表不同的磁盘或表空间中
         -索引被删除或损坏,不会对表产生影响,其影响的只是查询的速度;
         -索引一旦建立,Oracle管理系统会对其进行自动维护,而且由Oracle管理系统决定何
时使用索引;
         -在删除一个表时,所有基于该表的索引会自动被删除;
         -通过指针加速Oracle服务器的查询速度;
         -通过快速定位数据的方法,减少磁盘I/O。
  创建索引:
         CREATE INDEX index1 ON table1(column1[, column2, …]);
  以下情况可以创建索引:
         1.列中数据值分布范围很广;
         2.列经常在WHERE子句或连接条件中出现;
         3.表经常被访问而且数据量很大,访问的数据大概占数据总量的2%%到4%%。
**索引列经常被更新时不适合创建索引。
删除索引:
         DROP INDEX index1;
         只有索引的拥有者或者拥有DROP ANY INDEX权限的用户才可以删除索引。
四)同义词
  同义词就是别名:
         -方便访问其他用户的对象
         -缩短对象名字的长度
         CREATE [PUBLIC] SYNONYM synonym1 FOR object1;
PLSQL程序设计基础语法
PL/SQL(Procedure Language/SQL)是Oracle对sql语言的过程化扩展,指在SQL命令语言中增加了过程处理语句(如分支,循环等),使SQL语言具有过程处理能力。PLSQL是面向过程的语言。
结构:
DECLARE
                  --说明部分(变量说明,光标申明,例外说明)
BEGIN
         --语句序列(DML语句)
EXCEPTION
         --例外处理
         END;
  说明部分:
         -定义基本变量
         -类型:char,varchar2,date,number,Boolean,long
         -举例:
var1     char(15);
married  boolean := true;
psal    number(7,2);
- 引用型变量:
                  my_name emp.ename%%type;  引用表中字段的类型作为变量类型
- 记录型变量:
                  emp_rec emp%%rowtype; 引用表中某一行作为变量。
                  emp_rec.ename; 引用行中ename属性的值
  程序体部分:
         - IF语句:
                   1. IF 条件 THEN 语句1;
                     语句2;
                     END IF;
                   2. IF 条件 THEN 语句序列1;
                     ELSE 语句序列2;
                     END IF;
3. IF 条件 THEN 语句;
   ELSIF 条件 THEN 语句;
                     ELSE 语句;
                     END IF
  循环语句:
         1. WHILE循环语句:
                   WHILE total <= 25000 LOOP
                   …
                   Total := total + salary;
                   END LOOP;
         2.LOOP循环语句:
                   LOOP
                   EXIT[WHEN条件]; 当条件成立时退出循环体
                   ……
                   END LOOP;
         3.FOR循环:
                   FOR i IN 1…3 LOOP
                   语句序列
                   END LOOP;

推荐阅读