DB Oracle.docx

1、DB OracleOracle SQL SQLDDLCREATE ALTER DROP TRUNCATE GRANT COMMENT REVOKEDMLSELECT INSERT UPDATE DELETE CALL EXPLAIN PLAN LOCK TABLEDCLCOMMIT SAVEPOINT ROLLBACK SET TRANSACTIONOracle的约束类型1PRIMARY KEY2FOREIGN KEY3UNIQUE Key4CHECK5NOT NULLMAN可以设定约束的范围，定义列级或表级约束。创建表同时创建约束若要建立表之间的关联，就必须先创建父表，即1：n中1的一方。在

2、删除数据时若不适用级联删除就必须先删除父表中的数据。先创建父表create table class( Class_ID varchar(10) not null, Class_Name varchar(20) not null, Class_Num INT not null, Class_Manager varchar(20) not null, primary key(Class_ID);后创建子表create table student( Stu_ID varchar(10) not null, Stu_Name varchar(20) not null, Age INT not null

3、, Height INT not null, Class_ID varchar(10) not null, constraint PK_STUDENT primary key (Stu_ID), foreign key (Class_ID) references CLASS (Class_ID) );先插入父表中的数据insert into class values(20070101,高一（1）,50,Ludahu),(20060202,高二（2）,60,HenLi),(20050303,高三（3）,30,WorkMan);后插入子表中的数据insert into student values

4、(1001,AA1,23,100,20070101),(1002,AA2,22,120,20070101),(1003,AA3,24,110,20070101),(1004,AA4,25,140,20070101);创建表然后添加创建约束ALTER TABLE TNEW ADD FOREIGN KEY(TNEW _ID) REFERENCES class(Class_ID);PL/SQL 语言(Procedural Language/SQL)PL/SQL基础PL/SQL简介1. PL/SQL是在标准SQL的基础上增加了过程化处理的语言.2. 是Oracle客户端访问Oracle服务器的操作语言

5、.3. 优点:模块化编程、良好的可移植性和可维护性、能够提升性能PL/SQL基本结构一般由声明部分、执行部分 、异常处理部分组成，只有执行部分是必须的。DECLARE -声明变量、常量、用户自定义数据类型、光标BEGIN -主程序体，可以加入各种合法语句EXCEPTION -异常处理程序，程序出错时执行此部分END;PL/SQL分界符名称符号名称符号算术运算符+ - * / *(指数操作符)单行注释-比较运算符= != = = = =多行注释/* */表达式起始分界符(DB连接指示符表达式终结分界符)属性指示符%标签起始分界符赋值操作符:=语句终结符;连接操作符=项目分割符,串连接操作符|其它

6、 范围操作符.布尔表达式AND,OR,NOT,BETWEEN . AND . ,INPL/SQL常量和变量定义常量： constant :=;如：pass_score constant INTEGER:=60;定义变量：(宽度):=; -宽度和初始值不是必须的.如：address VARCHAR2(30);提示：未初始化的变量值均为NULL.提示：PL/SQL变量和数据库变量是两个不同的概念.提示：内嵌过程可以使用外部的变量，但在外部不可以使用内嵌变量.PL/SQL数据类型分类名类型标量型数字型、字符型、布尔型BOOLEAN、日期型DATE组合型RECORD 、TABLE 、VARRAY参考型

7、REF CURSOR 、REF object_type大对象型BFILE 、BLOB 、CLOB 、NCLOB数字型(3种基本类型)NUMBER(P,S)可存储整数也存储浮点数，P(精度)和S(刻度)是可选的，若指定了S就必须也指定PNUMBER的子类型DEC,DECIMAL,DOUBLE PRECISION,INT,NUMBERIC,REAL,SMALLINTPLS_INTEGER只可存储整数BINARY_INTEGER只可存储整数字符型VARCHAR2(MaxLength)存储变长字符串，MaxLength必须指定，其最大值为32767字节LONG(MaxLength)存储变长字符串，Ma

8、xLength必须指定，其最大值为32760CHAR(MaxLength)存储定长字符串，MaxLength可不指定，其最大值为32767，默认为1，若不足补空格其它NCHAR,NVARCHAR2是PL/SQL8.0后加入提示DB中，VARCHAR2,LONG,CHAR的长度分别为4000,2GB,2000RECORD类型的使用语法：TYPE record_name IS RECORD( -定义RECORD类型 field1 type1 NOT NULL :=expr1, field1 type2 NOT NULL :=expr2, .);v_name record_name; -定义RECO

9、RD类型的变量使用：v_name. field1%TYPE可引用表中的某字段的类型：v_FirstName students.first_name%TYPE%ROWTYPE可引用基于数据库表定义的类型：v_StrRec Student%ROWTYPE;v_StrRec.student_id:=1234;v_StrRec.age:=20;TABLE类型的使用：DECLARETYPE t_StuTable IS TABLE OF Student%ROWTYPW INDEX BY BINARY_INTEGER;v_Student t_StuTable;BEGIN SELECT * INTO v_St

10、udent(1001) FROM Student WHERE id=1001;END;PL/SQL控制结构选择结构IF 条件表达式1 THEN 语句序列1; END IF;IF 条件表达式1THEN 语句序列1;ELSE 语句序列2;END IF;IF 条件表达式1THEN 语句序列1;ELSIF 条件表达式2THEN 语句序列2;ELSIF 条件表达式3THEN 语句序列3;ELSE 语句序列4;END IFCASE 检测表达式WHEN 表达式1 THEN 语句序列1;WHEN 表达式2 THEN 语句序列2;WHEN 表达式3 THEN 语句序列3;WHEN 表达式4 THEN 语句序列4

11、;ELSE 其它语句序列;END;提示：CASE中若任何一个表达式都不匹配，将产生CASE_NOT_FOUND错误号为ORA-6592提示：在条件表达式的判断中注意NULL的判断循环机结构LOOP IF条件表达式1THEN EXIT;END IF;语句序列1;END LOOPLOOP EXIT WHEN 条件表达式1语句序列1;END LOOP;WHILE 条件表达式1LOOP语句序列1;END LOOP;FOR v_name IN 0.5LOOP语句序列1;END LOOP;提示：GOTO label; 定义label:,一般不用SQL In PL/SQLSQL的类别及PL/SQL中的SQL

12、DML(Data Manipulation Language)select , insert , delete , set transaction , explain planDDL(Data Definition Language)drop , create , alter , grant , revokeTransaction Controlcommit , rollback , savepointSession Controlalert session , set roleSystem Controlalert systemESQLconnect , declare cursor , a

13、llocate提示：只有DML可以直接在PL/SQL中使用，PL/SQL可以借助内置的DBMS_SQL包来执行动态SQLPL/SQL块简介块是PL/SQL的基本结构，块可以顺序出现也可以嵌套出现。块类别简介匿名块动态生成，只可被执行一次，不能在其它块中进行调用有名块动态生成，只可被执行一次，但有名字，可以在其它块中进行调用子程序过程、包、函数，可显示的反复调用，一旦生成就不可修改，包只可以存储在数据库中，其他还可存储在本地触发器当触发事件出现时触发器便执行，是一种有名块，可反复调用，触发事件是由DML操作产生的PL/SQL编程游标简介游标用SELECT语句从表或视图中选出数据，游标指向查询结果

14、的首部。游标是一个指向上下文的句柄或指针，通过游标，PL/SQL可以对上下文进行控制。游标的种类：显示游标（由用户定义,打开,关闭）、隐式游标（自动的）。显示游标的基本操作DECLARE person_no NUMBER(5); -定义3个变量用来存放Persons表中的内容 person_name CHAR(10)； person_sex CHAR(1); result VARCHAR2(30); -记录相关的信息 CURSOR person IS -1.定义一个游标 SELECT No,Name,Sex FROM Person WHERE Nov_var1,p_InOut=10); -调用

15、过程DROP PROCEDURE ModeTest;-删除过程函 数函数与过程很相似，有以下不同点：1 过程调用用过程名，函数调用用表达式。2 函数必须有一个返回值，过程没有。提示：函数中可以有多个返回值，但若函数结束时仍没碰到返回值将出错。提示：函数通常只有in类型的参数。提示：函数可以通过out参数返回多个值。CREATE FUNCTION count_num(in_sex in PERSONS.Sex%TYPE)RETURN NUMBERIS out_num NUMBER;BEGIN IF in_sex=M THEN SELECT count(Sex) INTO out_num FROM

16、 PERSONS WHERE Sex=M; ELSE SELECT count(Sex) INTO out_num FROM PERSONS WHERE Sex=W; END IF; RETURN(out_num);END count_num;DROP FUNCTION count_num;包包是可以将相关对象存储在一起的PL/SQL结构。包的内容：包中可以包含过程、函数、游标、变量。组成：包由包头和包体组成。包头：对包的所有部件进行一个简单的声明。包体：包含了包头中声明的所有过程和函数的代码，若包头中没有声明，包体可以不要。初始化：包在第一次被调用的时候，从数据库中调入内存并被初始化。包内变

17、量：每个会话都将拥有自己的包内变量。重载：同一个包中的过程和函数可以重载，但参数必须是不同类型族的。外部调用：包名.元素名CREATE PACKAGE my_package IS -包头部分 man_num NUMBER;-定义变量 woman_num NUMBER; COURSOR person; -定义游标 CREATE FUNCTION F_count_num(in_sex in PERSONS.Sex%TYPE) RETURN NUMBER; -定义函数CREATE PROCEDURE -定义过程P_count_num(in_sex in PERSONS.SeX%TYPE,out_nu

18、m out NUMBER);END my_package;CREATE PACKAGE BODY my_package AS 包体部分 -游标代码 CURSOR person IS SELECT No,Name FROM Person WHERE No80 THEN RAISE TOO_FAT;-引发异常 END IF；EXCEPTION WHERE TOO_FAT THEN INSERT INTO ALERT VALUES(THE PERSON IS TOO FAT.);END;视 图视图是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。视图基于的表

19、称为基表。通过创建视图可以提取数据的逻辑上的集合或组合。视图的查询可以使用复杂的SELECT语法，包括连接/分组查询和子查询；语法CREATE OR REPLACE FORCE|NOFORCE VIEW view_name(alias, alias.)AS subquery WITH CHECK OPTION CONSTRAINT constraint WITH READ ONLY 视图的分类简单视图：只从单表里获取数据，不包含函数和数据组，可以实现DML操作复杂视图：从多表里获取数据，包含函数和数据组，对DML有限制视图的作用隔离性：对数据库的访问，因为视图可以有选择性的选取数据库里的一部分

20、。简化查询：用户通过简单的查询可以从复杂查询中得到结果。维护数据的独立性，试图可从多个表检索数据。丰富逻辑模型：对于相同的数据可产生不同的视图。视图的定义规则在没有WITH CHECK OPTION和 READ ONLY 的情况下，查询中不能使用ORDER BY 子句。如果没有为CHECK OPTION约束命名，系统会自动为之命名，形式为SYS_Cn。在视图包含GROUP 函数，GROUP BY子句，DISTINCT关键字时不能删除数据行。索 引索引的作用1 加快查询速度。2 减少I/O操。使用语法索引的分类按存储方法分类 B*树索引 B树索引中不存在非唯一的条目。有分支和叶两种类型的存储数据块，一般索引及唯一约束索引都使用B*树索引。适用于：访问表中占很小比例的行。适用于：根本不访问表，所需查询的数据全部在索引中。注意：如果列值为null,在索引中就没有相应的条目。 位图索引 主要用来节省空间，采用位图索引一般是重复值太多的表字段。按功能分类 唯一约束索引个是数据约束（保证数据的完整性），一个