| 零基础学习Oracle 这一篇就够了 | 您所在的位置：网站首页 › 零基础学马头琴容易吗推荐 › 零基础学习Oracle 这一篇就够了 |
零基础学习Oracle 这一篇就够了
|
目录
第一章 数据库概述
1.1、数据库的好处1.2、数据库的常见概念1.3、数据库的存储特点1.4、数据库的常见分类1.5、SQL语言的分类第二章 Oracle概述
2.1、Oracle的概述2.2、Oracle的下载2.3、Oracle的安装2.4、Oracle的目录2.5、Oracle的配置2.6、Oracle的命令行连接2.7、Oracle的窗口化连接2.8、Oracle的体系结构2.9、Oracle的测试用户第三章 DQL语言
3.1、完整语法3.2、简单查询3.3、别名查询3.4、去重查询3.5、条件查询3.6、分组查询3.7、分组过滤3.8、排序查询3.9、分页查询3.10、多表查询3.11、联合查询3.12、子查询第四章 函数大全
4.1、数值型函数4.2、字符型函数4.3、日期函数4.4、转换函数4.5、聚组函数4.6、分析函数4.7、其它函数第五章 DCL语言
5.1、创建表空间5.2、删除表空间5.3、创建用户5.4、删除用户5.5、授权用户5.6、取消授权5.7、修改密码第六章 DDL语言
6.1、数据类型6.2、创建表6.3、复制表6.4、删除表6.5、修改表6.6、表约束第七章 DML语言
7.1、插入语句7.2、修改语句7.3、删除语句第八章 TCL语言
8.1、事务8.2、事务并发（读问题）8.3、丢失更新（写问题）第九章 高级部分
9.1、序列9.2、PLSQL编程
9.2.1、格式9.2.2、变量9.2.3、if判断9.2.4、while循环9.2.5、for循环9.2.6、loop循环9.2.7、意外9.3、索引9.4、视图9.5、同义词9.6、游标9.7、存储过程9.8、函数9.9、触发器第十章 数据备份与恢复
10.1、全部导出10.2、全部导入10.3、按用户导出10.4、按用户导入第十一章 安装Oracle单实例
11.1、环境准备11.2、下载程序11.3、解压程序11.4、移动软件11.5、域名映射11.6、关闭selinux11.7、关闭防火墙11.8、安装依赖包11.9、添加新用户11.10、修改内核文件11.11、修改用户限制11.12、创建安装目录11.13、设置环境变量11.14、安装应用程序11.15、使用sqlplus连接
配套资料，免费下载 https://gitee.com/biwangsheng/mxq 第一章 数据库概述 1.1、数据库的好处 将数据持久化到本地提供结构化查询功能 1.2、数据库的常见概念 DB：数据库，存储数据的仓库DBMS：数据库管理系统，又称为数据库软件或者数据库产品，用于创建和管理数据库，常见的有MySQL、Oracle、SQL ServerDBS：数据库系统，数据库系统是一个通称，包括数据库、数据库管理系统、数据库管理人员等，是最大的范畴SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言 1.3、数据库的存储特点 数据存放到表中，然后表再放到库中一个库中可以有多张表，每张表具有唯一的表名用来标识自己表中有一个或多个列，列又称为“字段”，相当于Java中“属性”表中的每一行数据，相当于Java中“对象” 1.4、数据库的常见分类 关系型数据库：MySQL、Oracle、DB2、SQL Server非关系型数据库： 键值存储数据库：Redis、Memcached、MemcacheDB列存储数据库：HBase、Cassandra面向文档的数据库：MongDB、CouchDB图形数据库：Neo4J 1.5、SQL语言的分类 DQL：数据查询语言：select、from、whereDCL：数据控制语言：grant、revokeDDL：数据定义语言：create、alter、drop、truncateDML：数据操作语言：insert、update、deleteTCL：事务控制语言：commit、rollback 第二章 Oracle概述 2.1、Oracle的概述Oracle Database，又名Oracle RDBMS，或简称Oracle。是甲骨文公司的一款关系数据库管理系统。它是在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的、适应高吞吐量的数据库方案 2.2、Oracle的下载课程使用：Windows 10 官网地址：Oracle 11gR2 admin目录 描述：记录Oracle实例的配置，运行日志等文件，每一个实例一个目录，SID：SystemIDentifier的缩写，是Oracle实例的唯一标记，在Oracle中一个实例只能操作一个数据库，如果安装多个库那么就会有多个实例，我们可以通过实例SID来区分。 cfgtoollogs目录 描述：下面子目录分别存放当运行dbca，emca，netca等图形化配置程序时的log。 checkpoints目录 描述：存放检查点文件。 diag目录 描述：Oracle11g添加的一个重组目录，其中的子目录，基本上Oracle每个组件都有了自己的单独目录，在Oracle10g中我们诟病的log文件散放在四处的问题终于得到解决，无论是asm还是crs还是rdbms，所有组件需要被用来诊断的log文件都存放在这个新的目录下。 flash_recovery_area(闪回区)目录 描述：分配一个特定的目录位置来存放一些特定的恢复文件，用于集中和简化管理数据库恢复工作。闪回区可存储完全的数据文件备份，增量备份、数据文件副本、当前控制文件、备份的控制文件、spfile文件、快照控制文件、联机日志文件、归档日志、块跟踪文件、闪回日志。 oradata目录 描述：存放数据文件。 CONTROL01.CTL 描述：Oracle数据库的控制文件EXAMPLE01.DBF 描述：Oracle数据库表空间文件REDO01.LOG 描述：Oracle数据库的重做日志文件，此文件有三个SYSAUX01.DBF 描述：11g新增加的表空间，主要存储除数据字典以外的其他数据对象，由系统内部自动维护SYSTEM01.DBF 描述：用于存放Oracle系统内部表和数据字典的数据。比如：表名、列名、用户名等TEMP01.DBF 描述：临时表空间文件UNDOTBS01.DBF 描述：撤销表空间文件，用来保存回滚数据USERS01.DBF 描述：用户表空间product目录 描述：数据库实例存放文件。 2.5、Oracle的配置前方高能：如果你是把Oracle 11gR2直接安装到本地电脑上的，这一章，你就直接跳过吧！ 注意事项：如果你是把Oracle 11gR2安装到了服务器中，那么你要想客户端远程访问，接下来的这些步骤，请你仔细阅读！ 客户端为了和服务器连接，必须先和服务器上的监听进程联络。ORACLE通过tnsnames.ora文件中的连接描述符来说明连接信息。一般tnsnames.ora 是建立在客户端上的。如果是客户端/服务器结构，整个网络上只有一台机器安装了ORACLE数据库服务器，那么只需在每个要访问ORACLE服务器的客户端上定义该文件，在服务器上无需定义。也就是说，如果客户端想连接到服务器上的Oracle数据库实例，客户端就必须有tnsnames.ora这个文件。一般在Oracle数据库安装的时候，它就会生成一个listener.ora（监听器配置文件）、tnsnames.ora（网络服务名配置文件），因为我们现在已经将Oracle安装到本地了，并没有安装到服务器上，为了能够测试连接服务器是否需要tnsnames.ora文件，我们假设D:\Oracle是服务器上的一个目录，D:\Oracle以外都是客户端的目录。 第一步：打开文件夹（假设当前电脑是服务器）：D:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN 第二步：获取服务器IP地址（假设当前电脑是服务器）：192.168.1.3 第三步：修改listener.ora文件（假设当前电脑是服务器）： 第四步：修改tnsnames.ora文件（假设当前电脑是服务器）： 第五步：重启服务器Oracle的服务（假设当前电脑是服务器）： 第六步：复制tnsnames.ora文件到客户端（假设当前电脑是客户端）：D:\Config 第七步：配置tnsnames.ora文件到环境变量（假设当前电脑是客户端）：TNS_ADMIN = D:\Config Oracle数据库的连接不像MySQL那样可以直接使用CMD窗口就能连接，它需要一些辅助软件，也就是下边的sqlplus。 既然是使用窗口化连接，那我们就需要一个窗口化连接的工具，这里我们使用的是代码补全提示比较好的PLSQL Developer。 但是，如果你使用带有中文的别名查询会乱码，这是因为PLSQL Developer的字符集编码和Oracle不一样，那如何设置呢？首先查询Oracle数据库的字符集编码：select userenv(‘language’) from dual; scott测试用户下的表： hr测试用户下的表： 一、语法 select [TOP|DISTINCT] [选择列表]|[*] from 数据源 [where 查询条件] [group by 分组条件] [having 过滤条件] [order by 排序条件 asc|desc nulls first|last]; 123456二、执行顺序 （5）select [（5-3）TOP|（5-2）DISTINCT] （5-1）[选择列表]|[*] （1）from 数据源 （2）[where 查询条件] （3）[group by 分组条件] （4）[having 过滤条件] （6）[order by asc|desc nulls first|last]; 123456 3.2、简单查询 --查询所有员工的信息 select * from emp; 12 3.3、别名查询 --查询所有员工的姓名 select e.ename from emp e; 12 3.4、去重查询 --查询所有部门的编号 select distinct e.deptno from emp e; 12 3.5、条件查询一、运算符 条件运算符：>、>=、 3000; 12 3.6、分组查询 --统计每个部门有多少个人 select deptno as "部门",count(*) as "人数" from emp group by deptno; 12 3.7、分组过滤 --统计部门人数>5人的部门的编号 select deptno as "部门", count(*) as "人数" from emp group by deptno having count(*) > 5; 12345 3.8、排序查询 --按照员工主管编号由高到低进行排序，NULL值放到最后边 select * from emp order by mgr desc nulls last; 12 3.9、分页查询 --查询前10条员工的信息 --注意：Oracle中不支持limit，需要在原始表加上一列：行号，然后使用子查询来实现分页 select * from (select rownum hanghao,e.* from emp e) t where t.hanghao >=1 and t.hanghao 1500 union select * from emp where deptno = 20; --工资大于1500或者20号部门下的员工 select * from emp where sal > 1500 union all select * from emp where deptno = 20; 1234567891011121314 交集运算：找两个查询结果的交集 --工资大于1500并且20号部门下的员工 select * from emp where sal > 1500 intersect select * from emp where deptno = 20; 1234 差集运算：找两个查询结果的差集 --1981年入职员工(不包括总裁和经理) select * from emp where to_char(hiredate,'yyyy') = '1981' minus select * from emp where job = 'PRESIDENT' or job = 'MANAGER'; 1234 注意事项： 列的类型要一致列的顺序要一致列的数量要一致，如果不够，可以使用null填充 3.12、子查询 单行子查询：>、>=、 some (select sal from emp where deptno = 10); 125、all的使用 --查询出比20号部门所有员工薪资高的员工信息 select * from emp where sal > all (select sal from emp where deptno = 20); 126、exits的使用 --查询有员工的部门的信息 select * from dept d1 where exists (select * from emp e1 where e1.deptno = d1.deptno); 12 第四章 函数大全具体用法，请参考资料中的Oracle函数大全.chm 4.1、数值型函数一、语法 create tablespace 表空间的名称 datafile '文件的路径' size 初始化大小 autoextend on next 每次扩展的大小; 12345二、演示 create tablespace mytest datafile 'd:/mytest.dbf' size 100m autoextend on next 10m; 12345 5.2、删除表空间一、语法 drop tablespace 表空间的名称; 1二、演示 drop tablespace mytest; 1 5.3、创建用户一、语法 create user 用户名 identified by 密码 default tablespace 表空间的名称; 123二、演示 create user zhangsan identified by 123456 default tablespace mytest; 123 5.4、删除用户 5.5、授权用户一、语法 grant 系统权限列表 to 用户名; 或者 grant 实体权限列表 on 表名称 to 用户名; 123二、权限列表 系统权限分类：（系统权限只能由DBA用户授出） DBA：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。RESOURCE：拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。CONNECT：拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。实体权限分类：select、update、insert、alter、index、delete、all 三、演示 grant CONNECT to zhangsan; 或者 grant CONNECT,RESOURCE to zhangsan; 或者 grant CONNECT,RESOURCE,DBA to zhangsan; 或者 grant DBA to zhangsan; 或者 grant all on emp to zhangsan; 123456789 5.6、取消授权一、语法 revoke 系统权限列表 from 用户名; 或者 revoke 实体权限列表 on 表名称 from 用户名; 123二、注意事项 系统权限只能由DBA用户回收 二、演示 revoke CONNECT from zhangsan; 或者 revoke CONNECT,RESOURCE from zhangsan; 或者 revoke CONNECT,RESOURCE,DBA from zhangsan; 或者 revoke DBA from zhangsan; 或者 revoke all on emp from zhangsan; 123456789 5.7、修改密码一、语法 alter user 用户名 identified by "密码"; 1二、演示 alter user zhangsan identified by "123456"; 1 第六章 DDL语言 6.1、数据类型注意：这里整理的都是常用的并没有全部整理出所有类型 字符串类型 CHAR：定长字符串，它会用空格填充来达到其最大长度，最多可以存储2000字节的信息NCHAR：这是一个包含UNICODE格式数据的定长字符串，最多可以存储2000字节的信息VARCHAR2：变长字符串，它不会用空格填充来达到其最大长度，最多可以存储4000字节的信息NVARCHAR2：这是一个包含UNICODE格式数据的变长字符串，最多可以存储4000字节的信息数值类型 NUMBER：NUMBER(p,s)是最常见的数字类型，关于NUMBER的有效位§和精确位(s)遵循以下规则： p：是有效数据总位数，取值范围为【1-38】，默认值是38s：表示精确到多少位，取值范围为【-84-127】，默认值是0INTEGER：INTEGER是NUMBER的子类型，它等同于NUMBER(38,0)，用来存储整数FLOAT：Float(n)是NUMBER的子类型，数 n 指示位的精度，n值的范围可以从1到126日期类型 DATE：DATE是最常用的日期数据类型，它可以存储日期和时间信息，虽然可以用字符或数字类型表示日期和时间信息，但是日期数据类型具有特殊关联的属性。Oracle 为每个日期值存储以下信息： 世纪、 年、 月、 日期、 小时、 分钟和秒，一般占用7个字节的存储空间TIMESTAMP：这是一个7字节或12字节的定宽日期/时间数据类型。它与DATE数据类型不同，因为TIMESTAMP可以包含小数秒，带小数秒的TIMESTAMP在小数点右边最多可以保留9位 6.2、创建表一、语法 create table 表名( 列名 列的类型 [列的约束], 列名 列的类型 [列的约束] ); 1234二、演示 create table users( id number, username varchar2(20), password varchar2(20) ); 12345 6.3、复制表一、语法 create table 表名 as 查询语句; 1二、演示 create table emp_copy as select * from emp ; 123 6.4、删除表一、语法 方式一：drop table 表名; 方式二：truncate table 表名; 12二、演示 方式一：drop table emp_copy; 方式二：truncate table emp_copy; 12 6.5、修改表1、添加一列 格式：alter table 表名 add 列名 列的类型; 演示：alter table users add phone varchar2(11); 122、修改列名 格式：alter table 表名 rename column 旧列名 to 新列名; 演示：alter table users rename column phone to mobile; 123、修改类型 格式：alter table 表名 modify 列名 列的类型; 演示：alter table users modify mobile char(11); 124、删除一列 格式：alter table 表名 drop column 列名; 演示：alter table users drop column mobile; 125、修改表名 格式：rename 旧表名 to 新表名; 演示：rename users to myusers; 12 6.6、表约束一、语法 CREATE TABLE 表名( 列名 列的类型 primary key,--主键约束 列名 列的类型 not null,--非空约束 列名 列的类型 unique,--唯一约束 列名 列的类型 check(列名 in (检查列表)),--检查约束 constraint 约束名 foreign key(字段名) references 主表(被引用列)--外键约束 ) ; 1234567二、演示 --商品分类表 create table category( cid number primary key, cname varchar2(20) ); --商品详情表 create table product( pid number primary key,--主键约束 pname varchar2(50) not null,--非空约束 pimg varchar2(50) unique,--唯一约束 pflag varchar2(10) check(pflag in ('上架','下架')),--检查约束 cid number, constraint FK_CATEGORY_ID foreign key(cid) references category(cid)--外键约束 ); 123456789101112131415三、修改 1、主键约束 添加 alter table product add constraint PK_PRODUCT_PID primary key(pid); 删除 alter table product drop constraint PK_PRODUCT_PID; 或者 alter table product drop primary key; 1234562、非空约束 添加 alter table product modify pname not null; 删除 alter table product modify pname null; 12343、唯一约束 添加 alter table product add constraint UK_PRODUCT_PIMG unique(pimg); 删除 alter table product drop constraint UK_PRODUCT_PIMG; 或者 alter table product drop unique(pimg); 1234564、检查约束 添加 alter table product add constraint CK_PRODUCT_PFLAG check(pflag in ('上架','下架')); 删除 alter table product drop constraint CK_PRODUCT_PFLAG; 12345、外键约束 添加 alter table product add constraint FK_PRODUCT_ID foreign key(cid) references category(cid); 删除 alter table product drop constraint FK_PRODUCT_ID; 1234 第七章 DML语言 7.1、插入语句 格式：insert into 表名(列名1,列名2,...) values(值1,值2,...); 演示：insert into category(cid,cname) values(1,'电视'); 注意：commit; 123 7.2、修改语句 格式：update 表名 set 列名1=值1,列名2=值2,... where 查询条件; 演示：update category set cname='汽车' where cid = 1; 注意：commit; 123 7.3、删除语句 格式：delete from 表名 where 查询条件; 演示：delete from category where cid = 1; 注意：commit; 123 第八章 TCL语言 8.1、事务一、含义 一条或多条sql语句组成一个执行单位，一组sql语句要么都执行要么都不执行 二、特点（ACID） 原子性：一个事务是不可再分割的整体，要么都执行要么都不执行一致性：一个事务的执行不能破坏数据库数据的完整性和一致性隔离性：一个事务不受其它事务的干扰，多个事务是互相隔离的持久性：一个事务一旦提交了，则永久的持久化到本地三、分类 1、开启事务 Oracle 11g中事务是隐式自动开始的，它不需要用户显示的执行开始事务语句 2、编写一组逻辑sql语句 注意：sql语句支持的是insert、update、delete 【设置回滚点】 savepoint 回滚点名; 3、结束事务 提交：commit; 回滚：rollback; 回滚到指定的地方： rollback to 回滚点名; 12345678910111213 8.2、事务并发（读问题）一、事物的并发问题如何发生？ 多个事务同时操作同一个数据库的相同数据时 二、事务的并发问题都有哪些？ 脏读：一个事务读到了另一个事务还未提交的update数据，导致多次查询的结果不一样（Oracle中不会产生）不可重复读：一个事务读到了另一个事务已经提交的update数据，导致多次查询结果不一致幻读：一个事务读到了另一个事务已经提交的insert数据，导致多次查询的结果不一样三、事物的并发问题如何解决？ 通过设置隔离级别来解决并发问题 四、隔离级别 Oracle中只支持READ COMMITTED、SERIALIZABLE、READ ONLY、READ WRITE，这些语句是互斥的，不能同时设置两个或两个以上的选项，一般默认即可。 8.3、丢失更新（写问题）一、定义 在事务的隔离级别内容中，能够了解到两个不同的事务在并发的时候可能会发生数据的影响。细心的话可以发现事务隔离级别章节中，脏读、不可重复读、幻读三个问题都是由事务A对数据进行修改、增加，事务B总是在做读操作。如果两事务都在对数据进行修改则会导致另外的问题：丢失更新。 二、解决 悲观锁：认为两个事务更新操作一定会发生丢失更新 解决：通过在语句后边添加for update来实现行级上锁，所以又称为“行级锁”，例如：select * from t_account t wheret.id=‘1’ for update;乐观锁：认为事务不一定会产生丢失更新，让事务进行并发修改，不对事务进行锁定 解决：由程序员自己解决，可以通过给数据表添加自增的version字段或时间戳timestamp，进行数据修改时，数据库会检测version字段或者时间戳是否与原来的一致，若不一致，抛出异常或者重新查询三、注意 对于账户交易建议直接使用悲观锁，数据库的性能很高，并发度不是很高的场景两者性能没有太大差别。如果是交易减库存的操作可以考虑乐观锁，保证并发度。 第九章 高级部分 9.1、序列一、含义 序列是Oracle数据库中特有的，使用序列可以生成类似于 auto_increment 这种ID自动增长 1,2,3,4,5… 的效果 二、语法 create sequence 序列名称 start with 从几开始 increment by 每次增长多少 [maxvalue 最大值] | nomaxvalue [minvalue 最小值] | nominvalue cycle | nocycle --是否自动循环 [cache 缓存数量] | nocache; 1234567三、演示 --创建序列 create sequence auto_increment_seq start with 1 increment by 1 nomaxvalue minvalue 1 nocycle cache 10000; --调用序列 select auto_increment_seq.nextval from dual; select auto_increment_seq.currval from dual; 123456789101112 9.2、PLSQL编程9.2.1、格式 declare --声明变量 begin --业务逻辑 end; 12345679.2.2、变量 declare --声明变量 -- 格式一：变量名 变量类型; -- 格式二：变量名 变量类型 := 初始值; -- 格式三：变量名 变量类型 := &文本框名; -- 格式四：变量名 表名.字段名%type; -- 格式五：变量名 表名%rowtype; vnum number; vage number := 28; vabc number := &abc;--输入一个数值，从一个文本框输入 vsal emp.sal%type; --引用型的变量，代表emp.sal的类型 vrow emp%rowtype; --记录型的变量，代表emp一行的类型 begin --业务逻辑 dbms_output.put_line(vnum); --输出一个未赋值的变量 dbms_output.put_line(vage); --输出一个已赋值的变量 dbms_output.put_line(vabc); --输出一个文本框输入的变量 select sal into vsal from emp where empno = 7654; --将查询到的sal内容存入vsal并输出 dbms_output.put_line(vsal); select * into vrow from emp where empno = 7654; --将查询到的一行内容存入vrow并输出 dbms_output.put_line(vrow.sal); dbms_output.put_line(123); --输出一个整数 dbms_output.put_line(123.456); --输出一个小数 dbms_output.put_line('Hello,World'); --输出一个字符串 dbms_output.put_line('Hello'||',World'); --输出一个拼接的字符串，||拼接符Oracle特有 dbms_output.put_line(concat('Hello',',World')); --输出一个拼接的字符串，concat函数比较通用 end; 1234567891011121314151617181920212223242526279.2.3、if判断 一、语法 if 条件1 then elsif 条件2 then else end if; 1234567二、演示 declare age number := &age; begin if age < 18 then dbms_output.put_line('小屁孩'); elsif age >= 18 and age 24 and age < 40 then dbms_output.put_line('老司机'); else dbms_output.put_line('老年人'); end if; end; 123456789101112139.2.4、while循环 一、语法 while 条件 loop end loop; 123二、演示 --输出1~10 declare i number := 1; begin while i 10; dbms_output.put_line(i); i := i + 1; end loop; end; 123456789109.2.7、意外 一、含义 意外是程序运行的过程发生的异常，相当于是Java中的异常 二、语法 declare --声明变量 begin --业务逻辑 exception --处理异常 when 异常1 then ... when 异常2 then ... when others then ...处理其它异常 end; 12345678910111213三、分类 系统异常 zero_divide ：除数为零异常value_error ：类型转换异常no_data_found : 没有找到数据too_many_rows : 查询出多行记录，但是赋值给了%rowtype一行数据变量自定义异常 declare --声明变量 异常名称 exception; begin --业务逻辑 if 触发条件 then raise 异常名称; --抛出自定义的异常 exception --处理异常 when 异常名称 then dbms_output.put_line('输出了自定义异常'); when others then dbms_output.put_line('输出了其它的异常'); end; 1234567891011121314四、演示 1、内置系统异常 vi number; vrow emp%rowtype; begin --以下四行对应四个异常，测试请依次放开 vi := 8/0; --vi := 'aaa'; --select * into vrow from emp where empno = 1234567; --select * into vrow from emp; exception when zero_divide then dbms_output.put_line('发生除数为零异常'); when value_error then dbms_output.put_line('发生类型转换异常'); when no_data_found then dbms_output.put_line('没有找到数据异常'); when too_many_rows then dbms_output.put_line('查询出多行记录，但是赋值给了%rowtype一行数据变量'); when others then dbms_output.put_line('发生了其它的异常' || sqlerrm); end; 12345678910111213141516171819202、抛出系统异常 --查询指定编号的员工，如果没有找到，则抛出系统异常 declare --1.声明一个变量 %rowtype vrow emp%rowtype; begin --查询员工信息，保存起来 select * into vrow from emp where empno = 8000; --判断是否触发异常的条件 if vrow.sal is null then --抛出系统异常 raise_application_error(-20001,'员工工资为空'); end if; exception when others then dbms_output.put_line('输出了其它的异常' || sqlerrm); end; 123456789101112131415163、抛出自定义异常 --查询指定编号的员工，如果没有找到，则抛出自定义异常 declare --1.声明一个变量 %rowtype vrow emp%rowtype; --2.声明一个自定义的异常 no_emp exception; begin --查询员工信息，保存起来 select * into vrow from emp where empno = 8000; --判断是否触发异常的条件 if vrow.sal is null then raise no_emp; --抛出自定义的异常 end if; exception when no_emp then dbms_output.put_line('输出了自定义异常'); when others then dbms_output.put_line('输出了其它的异常' || sqlerrm); end; 12345678910111213141516171819 9.3、索引一、含义 索引相当于是一本书的目录，能够提高我们的查询效率 二、语法 1、创建索引 create [UNIQUE]|[BITMAP] index 索引名 on 表名(列名1,列名2,...); 12、修改索引 --重命名索引 alter index 索引名称 rename to 新的名称; --合并索引 alter index 索引名称 coalesce; --重建索引 alter index 索引名称 rebuild; --修改某列 先删除，在创建 12345678910113、删除索引 drop index 索引名称; 1三、演示 1、创建索引 create index INX_CATEGORY_CNAME on category(cname); 12、修改索引 --重命名索引 alter index INX_CATEGORY_CNAME rename to INX_CATEGORY_CNAME_NEW; --合并索引 alter index INX_CATEGORY_CNAME_NEW coalesce; --重建索引 alter index INX_CATEGORY_CNAME_NEW rebuild; --修改某列 先删除，在创建 12345678910113、删除索引 drop index INX_CATEGORY_CNAME; 1 9.4、视图一、含义 视图是对查询结果的一个封装，视图里面所有的数据，都是来自于它查询的那张表，视图本身不存储任何数据，但是可以修改原数据，但是不建议这样使用 二、语法 1、创建视图 create view 视图名称 as 查询语句 [with read only]; 1232、修改视图 create or replace view 视图名称 as 查询语句 [with read only]; 1233、删除视图 drop view 视图名称; 1三、演示 1、创建视图 create view view_emp as select ename,job,mgr from emp; 122、修改视图 create or replace view view_emp as select ename,job,mgr,deptno from emp; 123、删除视图 drop view view_emp; 1 9.5、同义词一、含义 同义词就是别名的意思和视图的功能类似，就是一种映射关系 二、语法 1、创建同义词 create [public] synonym 同义词名称 for 对象的名称; 12、修改同义词 create or replace [public] synonym 同义词名称 for 对象的名称; 13、删除同义词 drop [public] synonym 同义词名称; 1三、演示 1、创建同义词 --创建 create synonym syno_emp for emp; --调用 select * from syno_emp; 12342、修改同义词 --创建 create or replace synonym syno_emp_update for emp; --调用 select * from syno_emp_update; 12343、删除同义词 drop synonym syno_emp_update; 1 9.6、游标一、含义 游标是用来操作查询结果集，相当于是JDBC中ResultSet，它可以对查询的结果一行一行的获取 二、语法 --第一步：定义游标 --第一种：普通游标 cursor 游标名[(参数 参数类型)] is 查询语句; --第二种：系统引用游标 游标名 sys_refcursor; --第二步：打开游标 --第一种：普通游标 open 游标名[(参数 参数类型)]; --第二种：系统引用游标 open 游标名 for 查询语句; --第三步：获取一行 fetch 游标名 into 变量; --第四步：关闭游标 close 游标名; 1234567891011121314151617三、演示 1、普通游标使用 --输出指定部门下的员工姓名和工资 declare --1.声明游标 cursor vrows(dno number) is select * from emp where deptno = dno; --声明变量 vrow emp%rowtype; begin --2.打开游标 open vrows(10); --3.循环遍历 loop fetch vrows into vrow; exit when vrows%notfound; dbms_output.put_line('姓名:' || vrow.ename || ' 工资: ' || vrow.sal); end loop; --4.关闭游标 close vrows; end; 1234567891011121314151617182、系统引用游标使用 --输出员工表中所有的员工姓名和工资 declare --1.声明系统引用游标 vrows sys_refcursor; --声明变量 vrow emp%rowtype; begin --2.打开游标 open vrows for select * from emp; --3.循环遍历 loop fetch vrows into vrow; exit when vrows%notfound; dbms_output.put_line('姓名:' || vrow.ename || ' 工资: ' || vrow.sal); end loop; --4.关闭游标 close vrows; end; 1234567891011121314151617183、使用for循环输出 --输出员工表中所有的员工姓名和工资 declare cursor vrows is select * from emp; begin --自动定义变量vrow，自动打开游标，自动关闭游标 for vrow in vrows loop dbms_output.put_line('姓名:' || vrow.ename || ' 工资: ' || vrow.sal || '工作:' || vrow.job); end loop; end; 123456789 9.7、存储过程一、含义 存储过程实际上是封装在服务器上一段PLSQL代码片断，它已经编译好了，如果客户端调用存储过程，执行效率就会非常高效 二、语法 1、创建存储过程 create procedure 存储过程名称(参数名 in|out 参数类型,参数名 in|out 参数类型,...) is|as --声明部分 begin --业务逻辑 end; 1234562、修改存储过程 create [or replace] procedure 存储过程名称(参数名 in|out 参数类型,参数名 in|out 参数类型,...) is|as --声明部分 begin --业务逻辑 end; 1234563、删除存储过程 drop procedure 存储过程名称; 14、调用存储过程 --方式一： call 存储过程名称(...); --方式二： declare begin 存储过程名称(...); end; 123456789三、演示 1、创建存储过程 --给指定员工涨薪并打印涨薪前和涨薪后的工资 create procedure proc_update_sal(vempno in number,vnum in number) is --声明变量 vsal number; begin --查询当前的工资 select sal into vsal from emp where empno = vempno; --输出涨薪前的工资 dbms_output.put_line('涨薪前:' || vsal); --更新工资 update emp set sal = vsal + vnum where empno = vempno; --输出涨薪后的工资 dbms_output.put_line('涨薪后:' || (vsal + vnum)); --提交事物 commit; end; --给员工编号为7521的员工涨工资10元 call proc_update_sal(7521, 10); 12345678910111213141516171819202、修改存储过程 --给指定员工涨薪并打印涨薪前和涨薪后的工资 create or replace procedure proc_update_sal(vempno in number,vnum in number) is --声明变量 vsal number; begin --查询当前的工资 select sal into vsal from emp where empno = vempno; --输出涨薪前的工资 dbms_output.put_line('涨薪前:' || vsal); --更新工资 update emp set sal = vsal + vnum where empno = vempno; --输出涨薪后的工资 dbms_output.put_line('涨薪后:' || (vsal + vnum)); --提交事物 commit; end; --给员工编号为7521的员工涨工资10元 call proc_update_sal(7521, 10); 12345678910111213141516171819203、删除存储过程 drop procedure proc_update_sal; 1 9.8、函数一、含义 函数实际上是封装在服务器上一段PLSQL代码片断，它已经编译好了，如果客户端调用存储过程，执行效率就会非常高效，它跟存储过程没有什么本质区别，存储过程能做的函数也能做，只不过函数有返回值 二、语法 1、创建函数 create function 函数名称(参数名 in|out 参数类型,参数名 in|out 参数类型,...) return 返回的参数类型 is|as --声明部分 begin --业务逻辑 end; 1234562、修改函数 create [or replace] function 函数名称(参数名 in|out 参数类型,参数名 in|out 参数类型,...) return 返回的参数类型 is|as --声明部分 begin --业务逻辑 end; 1234563、删除函数 drop function 函数名称; 14、调用函数 --方式一： select 函数名称(...) from dual; --方式二： declare 变量名 变量类型; begin 变量名 = 函数名称(...); end; 123456789三、演示 1、创建函数 --查询指定员工的年薪 /* 参数 : 员工的编号 返回 : 员工的年薪 */ create function func_getsal(vempno number) return number is vtotalsal number; begin select sal * 12 + nvl(comm, 0) into vtotalsal from emp where empno = vempno; return vtotalsal; end; --查询员工编号为7788的年薪 declare vsal number; begin vsal := func_getsal(7788); dbms_output.put_line(vsal); end; 12345678910111213141516171819202、修改函数 --查询指定员工的年薪 /* 参数 : 员工的编号 返回 : 员工的年薪 */ create or replace function func_getsal(vempno number) return number is vtotalsal number; begin select sal * 12 + nvl(comm, 0) into vtotalsal from emp where empno = vempno; return vtotalsal; end; --查询员工编号为7788的年薪 declare vsal number; begin vsal := func_getsal(7788); dbms_output.put_line(vsal); end; 12345678910111213141516171819203、删除函数 drop function func_getsal; 1 9.9、触发器一、含义 当用户执行了 insert | update | delete 这些操作之后，可以触发一系列其它的动作、业务逻辑，使用触发器可以协助应用在数据库端确保数据的完整性、日志记录 、数据校验等操作。使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在Oracle触发器不仅支持行级触发，还支持语句级触发 二、分类 触发器类型NEW 和 OLD的使用INSERT 型触发器NEW 表示将要或者已经新增的数据UPDATE 型触发器OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据DELETE 型触发器OLD 表示将要或者已经删除的数据三、语法 1、创建触发器 create trigger 触发器名称 before|after insert|update|delete on 表名称 [for each row]--行级触发器 declare --声明部分 begin --业务逻辑 end; 123456789102、修改触发器 create [or replace] trigger 触发器名称 before|after insert|update|delete on 表名称 [for each row]--行级触发器 declare --声明部分 begin --业务逻辑 end; 123456789103、删除触发器 drop trigger 触发器名称; 1四、演示 1、INSERT 型触发器 --新员工入职之后，输出一句话: 欢迎加入我们 create or replace trigger tri_emp_insert after insert on emp declare begin dbms_output.put_line('欢迎加入我们'); end; --插入数据就可以自动触发触发器 insert into emp(empno, ename) values(9527, '马哈哈'); 123456789101112132、UPDATE 型触发器 --判断员工涨工资后的工资一定要大于涨工资前的工资 create or replace trigger tri_emp_update_sal before update on emp for each row declare begin if :old.sal > :new.sal then raise_application_error(-20002,'旧的工资不能大于新的工资'); end if; end; --更新数据就可以自动触发触发器（无异常） update emp set sal = sal + 10; select * from emp; --更新数据就可以自动触发触发器（有异常） update emp set sal = sal - 100; select * from emp; 1234567891011121314151617181920213、DELETE 型触发器 --老员工离职之后，输出一句话: 有员工离职了 create or replace trigger tri_emp_delete after delete on emp declare begin dbms_output.put_line('有员工离职了'); end; --删除数据就可以自动触发触发器 delete from emp where empno = 9527; 123456789101112134、删除触发器 drop trigger tri_emp_insert; drop trigger tri_emp_update_sal; drop trigger tri_emp_delete; 123 第十章 数据备份与恢复 10.1、全部导出 --注意：以下操作为cmd命令行操作 全部导出: exp 管理员帐号/密码 file='d:\beifen.dmp' full=y 12 10.2、全部导入 --注意：以下操作为cmd命令行操作 全部导入: imp 管理员帐号/密码 file='d:\beifen.dmp' full=y 12 10.3、按用户导出 --注意：以下操作为cmd命令行操作 按用户导出: exp 管理员帐号/密码 file='d:\beifen.dmp' owner=帐号 12 10.4、按用户导入 --注意：以下操作为cmd命令行操作 按用户导入: imp 管理员帐号/密码 file='d:\beifen.dmp' fromuser=帐号 12 第十一章 安装Oracle单实例 11.1、环境准备注意：当前Linux为CentOS-6.10-x86_64-bin-DVD1.iso 界面：使用图形界面 内存：4GB 硬盘：20GB 连接：SecureCRT，上传文件（rz命令）需要安装：yum -y install lrzsz 11.2、下载程序32位需要下载（需要登录账号）： http://download.oracle.com/otn/linux/oracle11g/R2/linux_11gR2_database_1of2.zip http://download.oracle.com/otn/linux/oracle11g/R2/linux_11gR2_database_2of2.zip 64位需要下载（需要登录账号）： http://download.oracle.com/otn/linux/oracle11g/R2/linux.x64_11gR2_database_1of2.zip http://download.oracle.com/otn/linux/oracle11g/R2/linux.x64_11gR2_database_2of2.zip 查看当前系统主机名： [root@caochenlei ~]# hostname caochenlei 12查看当前系统的IP地址： [root@caochenlei ~]# ifconfig 1修改系统的映射文件： [root@caochenlei ~]# vi /etc/hosts 1在末尾添加以下内容： 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.112.129 caochenlei 1234 11.6、关闭selinux [root@caochenlei ~]# vi /etc/selinux/config 1设置SELINUX=disabled 临时关闭一下selinux： [root@caochenlei ~]# setenforce 0 1 11.7、关闭防火墙 [root@caochenlei ~]# service iptables stop [root@caochenlei ~]# chkconfig iptables off 12 11.8、安装依赖包 [root@caochenlei ~]# yum install -y binutils compat-libcap1 compat-libstdc++-33 gcc gcc-c++ glibc glibc-devel ksh libgcc libstdc++ libstdc++-devel libaio libaio-devel make sysstat unixODBC-devel pdksh java-1.8.0-openjdk* 1 11.9、添加新用户 [root@caochenlei ~]# groupadd oinstall [root@caochenlei ~]# groupadd dba [root@caochenlei ~]# useradd -g oinstall -G dba oracle [root@caochenlei ~]# passwd oracle 1234 11.10、修改内核文件 [root@caochenlei ~]# vi /etc/sysctl.conf 1在末尾添加以下内容： fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmall = 2097152 kernel.shmmax = 536870912 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 1234567891011更新一下文件以生效： [root@caochenlei ~]# sysctl -p 1 11.11、修改用户限制 [root@caochenlei ~]# vi /etc/security/limits.conf 1在末尾添加以下内容： oracle soft nproc 4096 oracle hard nproc 65536 oracle soft nofile 4096 oracle hard nofile 65536 1234修改完成后重启服务器： [root@caochenlei ~]# reboot 1 11.12、创建安装目录 [root@caochenlei ~]# mkdir -p /data/oracle/product/11.2.0 [root@caochenlei ~]# mkdir -p /data/oracle/oradata [root@caochenlei ~]# mkdir -p /data/oracle/inventory [root@caochenlei ~]# chown -R oracle:oinstall /data/oracle [root@caochenlei ~]# chmod -R 775 /data/oracle 12345 11.13、设置环境变量切换到oracle用户下： [root@caochenlei ~]# su - oracle [oracle@caochenlei ~]$ vi .bash_profile 12在末尾添加以下内容： ORACLE_BASE=/data/oracle ORACLE_HOME=$ORACLE_BASE/product/11.2.0 ORACLE_SID=orcl PATH=$PATH:$ORACLE_HOME/bin export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH 12345更新一下文件以生效： [oracle@caochenlei ~]$ source .bash_profile 1 11.14、安装应用程序这一步需要在虚拟机里进行，因为会弹出窗体程序 [oracle@caochenlei response]$ cd /usr/local/oracle/database/ [oracle@caochenlei database]$ LANG=en_US ./runInstaller 12用户安装截图： 然后提示要执行两个脚本，使用root执行，可以使用SecureCRT重新新建一个连接进行 [root@caochenlei ~]# sh /data/oracle/inventory/orainstRoot.sh Changing permissions of /data/oracle/inventory. Adding read,write permissions for group. Removing read,write,execute permissions for world. Changing groupname of /data/oracle/inventory to oinstall. The execution of the script is complete. [root@caochenlei ~]# sh /data/oracle/product/11.2.0/dbhome_1/root.sh Running Oracle 11g root.sh script... The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= /data/oracle/product/11.2.0/dbhome_1 Enter the full pathname of the local bin directory: [/usr/local/bin]: Copying dbhome to /usr/local/bin ... Copying oraenv to /usr/local/bin ... Copying coraenv to /usr/local/bin ... Creating /etc/oratab file... Entries will be added to the /etc/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root.sh script. Now product-specific root actions will be performed. Finished product-specific root actions. 1234567891011121314151617181920212223242526执行完成，点ok，安装成功 配套资料，免费下载 链接:https://pan.baidu.com/s/1yjJY3IstA8aMQBPYhWNlCw 提取码:qmpd 复制这段内容后打开百度网盘手机App，操作更方便哦 新建一个系统环境变量，变量名为TNS_ADMIN，变量值为tnsnames.ora所在文件夹的全路径 |
【本文地址】
公司简介
联系我们