ORACLE基础学习

1、ORACLE 基础学习基础学习 企业信息化部企业信息化部 2015-01-15 2015-01-15课程内容课程内容数据库基本概念介绍数据库基本概念介绍SQL程序语言程序语言SQL语言语言数据库基本概念数据库基本概念-（1）Oracle 服务器：Oracle 服务器中包含多种文件结构、进程结构和内存结构；但是，处理SQL 语句时，并非所有这些结构都会用到。某些结构用于提高数据库的性能，确保该数据库在遇到软件或硬件错误时可以恢复，或者执行维护该数据库所需的其它任务。Oracle 服务器包括一个Oracle 例程和一个Oracle 数据库。 Oracle 例程：Oracle 例程是后台进程和内存结

2、构的组合。只有启动例程后，才能访问数据库中的数据。每次启动例程时，会分配系统全局区(SGA) 并启动Oracle 后台进程。后台进程代表调用进程执行各种功能。它们把为每个用户运行的多个Oracle 程序所处理的功能统一起来。后台进程执行输入/输出(I/O)，并监视其它Oracle 进程来提高并行性，从而使性能和可靠性更加优越。数据库基本概念数据库基本概念-（2）Oracle 数据库：Oracle 数据库包含操作系统文件（也称为数据库文件），这些文件为数据库信息提供了实际的物理存储。数据库文件用于确保数据一致性并能在例程失败时得以恢复。表空间：表空间：是实际存储数据的地方。数据库存储分配就是表空

3、间的定义过程，因此表空间的设计对数据库物理设计极为重要，其参数设定好坏对数据库的性能及维护影响巨大。数据库基本概念数据库基本概念-（3）表：表是由确定的列数和可变的行数组成的逻辑结构。是存放数据的基本单元。数据库基本概念数据库基本概念-（4）视图：视图提供了在一个表或多个表中查看数据的替代方法，它是表上的一个动态窗口。视图并没有存储数据，它只是将一个查询结果集表示成一个虚拟表。索引：索引是为了加快查询的速度而在表上创建的对象。课程内容课程内容数据库基本概念介绍数据库基本概念介绍SQL程序语言程序语言SQL语言语言SQL数据类型数据类型数据类型描述描述VARCHAR2(SIZE)可变长字符串类型

4、。SIZE默认单位为BYTE，最大长度为4000，最小为1NUMBER(PRECISION,SCALE)可变长度的数字类型，PRECISION是数字可用的最大位数。如有不指定PRECISION,SCALE，可以提供PRECISION和SCALE为38位的数字DATE日期类型，从公元前4712年1月1日到公元9999年12月31日ROWID用一个十六进制的数来标明当前行在表内的唯一地址BLOB存储非结构化二进制文件。常用于图片，视频的存储CLOB存储单字节或者多字节字符数据。常用语文本存储SQL语言SQL语言创建表使用CREATE TABLE语句创建表，需指定表名、列名和数据类型。可通过tabl

5、espace指定表空间。SQL语言创建表CREATE建表示例：比较常用的建表方法改为“ulocal_tbs”SQL语言创建视图使用CREATE VIEW语句创建视图，以限制对表的数据访问或代替常用的SQL查询语句视图查询方式SQL语言修改表SQL语言修改表SQL语言删除表在在ORACLE10版本以后，如果版本以后，如果DROP不带不带PURGE选项，则会把表先放在回选项，则会把表先放在回收站收站DROP TABLE DEPT PURGE;SELECT * FROM USER_RECYCLEBIN;PURGE TABLE DEPT;SQL语言RENAME要更改表、视图、序列或同义词的名称，可以执

6、行RENAME语句RENAME的另一种写法。SQL语言注释可以使用COMMENT语句向表或列添加备注SQL语言约束常用的约束有四种：非空约束，主键约束，外键约束和唯一性约束。SQL语言约束非空约束：为表的列添加非空约束后，再为该表添加数据时就不可以插入空置了。主键约束：主键约束在一个表中，只能定义一个，定义为主键约束的可以是列或者列的组合，其中不能包含重复值，并且不能包含NULL值。外键约束：是让两个表通过外键建立关系。在使用外键约束时，被引用的列应该具有主键约束或者具有唯一性约束。唯一性约束：用来保证表中的某一列或者表中的某几列组合起来不重复的一种手段。SQL语言约束除了在表创建时，定义约束

7、关系，也可以在表创建后建立或取消约束关系，语句如下：SQL语言-插入数据使用INSERT语句为表添加数据SQL语言-插入数据更新数据更新数据删除数据查询语句Subselect语法：Select-cause：查询语句-选择列查询语句-选择行查询语句-选择行查询语句-选择行可以使用AND和OR来指定任意多个查询条件。查询语句-谓词查询语句-谓词查询语句-谓词查询语句-排序查询语句-去除重复行查询语句-运算次序查询语句的运算次序如下，一个子句的输出是下一个子句的输入。只有运算次序在后面的子句才能引用运算次序在前面的子句。查询语句-给表达式命名查询语句-子查询查询语句-分组函数分组函数对列中的一组值进

8、行运算以得到单个结果值。函数名称函数名称功能功能AVG返回某一组中的值除以该组中值得个数的和COUNT返回一组行或值的个数MAX返回一组值中的最大值MIN返回一组值中的最小值SUM返回一组值的和查询语句-数学函数数学函数函数名称函数名称功能功能ABS(N)返回数字N的绝对值CEIL(N)返回大于等于数字N的最小整数FLOOR(N)返回小于等于数字N的最大整数MOD(M,N)返回两个数字相除后的余数。如果N为0，则返回MROUND(N,M)执行四舍五入运算SIGN(N)N大于0，返回1，N等于0，返回0，N小于0，返回-1TRUNC(N,M)对数字进行截取操作查询语句-字符函数字符函数函数名称函

9、数名称功能功能CONCAT(S1,S2)连接两个字符串INSTR(S1,S2,n,m)取得子串S2在字符串S1中的位置，N为起始搜索位置，M为子串出现次数，N,M默认为1LENGTH(S)返回字符串的长度LOWER返回字符串,并将所有的字符小写UPPER返回字符串,并将所有的字符大写SUBSTR (S,N,M)取子字符串,从N开始,取 M个REPLACE (S,S1,S2)将字符串S中的子串S1用S2替换查询语句-日期函数日期函数函数名称函数名称功能功能ADD_MONTHS (D,N)返回指定日期时间D之前或者之后的N个月所对应的时间LAST_DAY (D)返回指定日期所在月份的最后一天MON

10、THS_BETWEEN (D1,D2)返回日期D1和D2之间相差的月数NEXT_DAY (D,day)返回指定日期D后的第一个工作日（由day指定）所对应的日期SYSDATE用来得到系统的当前日期TRUNC(D,FMT)按指定格式格式化日期查询语句-转换函数转换函数函数名称函数名称功能功能TO_CHAR(D,FMT)按指定格式将日期转变为字符串TO_CHAR(N,FMT)按指定格式将数字转变为字符串TO_DATE(S,FMT)按指定格式将符合特定日期格式的字符串转变为DATE类型的值TO_NUMBER (S,FMT) 按指定格式将符合特定数字格式的字符串转变为数字值查询语句字符串使用单引号,如

11、:ClerkORDER BY 指定多列时,先按第一列排序,对于第一列相同的行,再按第二列排序.不同列可以指定不同的排序,如:SELECT DISTINCT DEPT, JOBFROM STAFFWHERE DEPT SET SERVEROUTPUT ONSQL BEGINUPDATE prod SET price=270WHERE pid= 0005;IF SQL%FOUND THENDBMS_OUTPUT.PUT_LINE(数据已修改);END IF; END;/游标简介游标简介隐式游标 循环游标用于简化游标处理代码当用户需要从游标中提取所有记录时使用循环游标的语法如下：FOR IN LOO

12、PEND LOOP;游标简介显式游标在 PL/SQL 块的声明部分定义查询，该查询可以返回多行显式游标的操作过程： 数据库打开游标30George344Roger245James1Stud_mrksStud_namestud_no提取行变量 关闭游标 显式游标 游标简介带参数的显式游标声明显式游标时可以带参数以提高灵活性声明带参数的显式游标的语法如下：CURSOR ( ) IS select_statement;游标简介使用显式游标更新行 2-1允许使用游标删除或更新活动集中的行声明游标时必须使用 SELECT FOR UPDATE语句 CURSOR IS SELECT statement F

13、OR UPDATE;UPDATE SET WHERE CURRENT OF 程序子程序命名的 PL/SQL 块，编译并存储在数据库中。子程序的各个部分：声明部分可执行部分异常处理部分(可选)子程序的分类：过程 执行某些操作函数 执行操作并返回值过程创建过程的语法：CREATE OR REPLACE PROCEDURE ()IS|AS BEGIN EXCEPTION END;程序程序过程 过程参数的三种模式：IN用于接受调用程序的值默认的参数模式OUT用于向调用程序返回值 IN OUT用于接受调用程序的值，并向调用程序返回更新的值程序函数函数是可以返回值的命名的 PL/SQL 子程序。 创建函数

14、的语法： CREATE OR REPLACE FUNCTION (param1,param2)RETURN IS|AS local declarationsBEGIN Executable Statements; RETURN result;EXCEPTION Exception handlers;END;过程和函数的比较过过 程程函函 数数作为 PL/SQL 语句执行作为表达式的一部分调用在规格说明中不包含 RETURN 子句必须在规格说明中包含 RETURN 子句不返回任何值必须返回单个值可以包含 RETURN 语句，但是与函数不同，它不能用于返回值必须包含至少一条 RETURN 语句程序

15、程序程序包程序包是对相关过程、函数、变量、游标和异常等对象的封装程序包由规范和主体两部分组成声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等声明程序包私有对象和实现在包规范中声明的子程序和游标程序包规范主体程序创建程序包程序包规范CREATE OR REPLACE PACKAGE package_name IS|ASPublic item declarationsSubprogram specificationEND package_name;程序包主体CREATE OR REPLACE PACKAGE BODY package_name IS|ASPrivate item declarationsSubprogram bodiesBEGINInitializationEND package_name;系统视图V$SESSIONV$VERSIO