ORACLEsql
来源：互联网 发布：qq管家下不了软件, 编辑：程序博客网 时间：2024/06/01 08:38
------------------------------------------------------第1讲oracle---------------------------------
oracle管理介绍
如何使用
1)在开始------>程序-->Oracle-oraDb11g_home1--->应用程序开发------>SQL_plus
2)在运行栏中输入sqlplus即可
3)在开始------>程序-->Oracle-oraDb11g_home1--->应用程序开发------>SQL_plus worksheet
4)oracle的企业管理器(oem oracle enterpise manager)
在开始------>程序-->Oracle-oraDb11g_home1--->enterpise manager console即可启动oracle的企业管理器
5)pl/sql developer属于第三方软件优化oracle pl/sql的存储过程方案等
关于卸载oracle
实现方法：
1、 services.msc 停止所有Oracle服务。
2、开始－＞程序－＞Oracle - OraHome81－＞Oracle Installation Products－＞ Universal Installer 卸装所有Oracle产品，但Universal Installer本身不能被删除
3、运行regedit，选择HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE，按del键删除这个入口。
4、运行regedit，选择HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services，滚动这个列表，删除所有Oracle入口。
5、运行reedit，选择
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application，删除所有Oracle入口。
6、开始－＞设置－＞控制面板－＞系统－＞高级－＞环境变量删除环境变量CLASSPATH和PATH中有关Oracle的设定
7、删除\Program Files\Oracle目录
8、重新启动计算机，重起后才能完全删除Oracle所在目录
9、到事件查看器中，删除Oracle相关的日志
app: 这样删除后其实注册表中还会有记录，一般用ctrl+f来去掉。
---------------------------第2讲oracle------------------------------------------------------------
连接命令
1.conn[ect]
说明:该命令是用来连接数据库的
用法:conn用户名/密码，当特权用户(sys,syystem)身份登陆的时候，必须带有as sysdba或者 as sysoper
例如：
sql>conn scott/wxh;
sql>conn sys/ych as sysdba;
sql>conn system/ych sysoper;
------------------------------第3讲oracle基础使用 -------------------------------------------
连接命令
2.disconn[ect]
说明:该命令是用来断开数据库的
用法:disconn;
sql>disconn;
3.passw[ord]
说明:该命令使用与修改用户的密码的，如果要修改其他用户的密码，则必须是dba(sys,system)用户或是具有dba权限的用户
sql>conn system/ych;
sql> passw scott;
更改scott的口令
新口令:whx
重新键入新口令:whx
口令已更改
sql>conn scott/whx;
sql>passw;
更改scott的口令
新口令:whx
重新键入新口令:whx
口令已更改
4.show user
说明:显示当前用户名
用法: show user;
sql>conn scott/whx;
sql>show user;
USER 为"SCOTT"
5.exit
说明:该命令会断卡与数据库的连接同时退出来
sql>exit;
6.文件操作命令
1.start和@
说明:运行sql脚本
案例:
sql>@ d:\a.sql;
或者是
sql>start d:\a.sql;
2.edit
说明:该命令可以编辑指定的sql脚本
案例:
sql>edit d:\a.sql;
3.spool
说明:该命令可以将sql_plus荧屏上的内容输入到指定文件夹去
案例:
sql>conn scott/whx;
sql>spool d:\b.sql;
sql>select * from emp;
sql>spool off;
交互式命令
1.&
说明:可以替代变量，而该变量在执行的时候，需要用户输入，只针对pl/sql
sql>conn scott/whx;
sql>select * from emp where job='&job';
显示和设置环境变量
概述:可以用来控制输出的各种格式，setshow
如果希望永久的保存相关的设置，可以去修改glogin.sql脚本
(1).linesize
说明:设置显示行的宽度，默认为80个字符
Sql>show linesize;;
Sql>set linesize 90;
(2).pagesize
说明:设置每页显示的行数目，默认是14
Sql>show pagesize;;
Sql>set pagesize 18;
至于其他的环境变量参数的使用也是大同小异
----------------第4讲oracle--------------------------------------------------------------------------------------
1.创建用户
概述:在oracle中要创建一个新的用户使用create user语句，一般是具有dba(数据库管理员)的权限才能使用
用法:create user用户名 identified by 密码;----注意在oracle中密码必须以字母开头
Sql>conn system/ych;
Sql>create user xiaoming identified by m123;
2.给用户修改密码
概述:如果给自己修改密码可以直接使用
用法:
Sql>password
….
特别说明:如果是给其他用户修改密码，则要求用户必须具有dba权限或是拥有alter user的系统权限
Sql>alter user 用户名 identified by 新密码;
Sql>alter user scott identified by m123;
3.删除用户
概述:一般删除用户要以dba的身份去删除一个用户,如果用其他用户去删除用户，则需要具有drop user的权限。
例如:drop user用户名 [cascade];
Cascade 如果删除用户已经创建了表、视图、存储过程等，那么就要再删除的时候带上参数cascade;
用户管理的综合案例
概述:创建的用户是没有任何权限的，甚至连登陆到数据库的权限都没有，需要指定相应的权限，给用户赋权限使用命令grant,回收权限使用revoke
用法:
Sql>grant 操作权限名 on表名 to 用户名;
Sql>revoke 操作权限名 on表名 from 用户名;
Oracle中有140多种权限
大体分为两类
(1)系统权限
系统权限是指用户对数据库的相关操作的权限(比如建立表、视图、存储过程、函数等操作)
(2)对象权限
对象权限是指用户对其他用户的数据对象的操作
数据对象包括:表(table)、视图(view)、触发器(tiggers)、存储过程(proceduct)、函数(function)、类型(types)、工作(job)等等
Create session 用户具有登陆到数据库中的权限
角色
角色可以大体上分为两类
(1)自定义角色
自定义角色需要自己定义相关的权限的角色
(2)预定义角色
自定义角色是oracle已经定义好了的角色
讲connect角色赋予给xiaoming
Sql>conn system/whx;
Sql>Grant connect to xiaoming;
只希望xiaoming允许查询不允许修改
Sql>conn scott/whx;
Sql>Grant select on emp to xiaoming;
或者是
Sql>conn system/whx;
Sql>grant select on scott.emp to xiaoming;
特别注意:oracle中可以存在两张相同的表，但是必须存在不同的方案(用户)中
回收权限
Scott希望收回xiaoming对emp表的查询权限
Sql>revoke select on emp from xiaoming;
权限的传递问题-------------------------------------对象权限问题
希望xiaoming用户可以查询scott用户的emp表，还希望xiaoming把这样的权限传递给其他的用户
Sql>conn scott/whx;
Sql>grant select on emp to xiaoming with grant option;
Sql>conn xiaoming/m123;
Sql>conn system.ych;
Sql>create user xiaohong identified by m123;
Sql>grant connection to xiaohong;
Sql>conn xiaoming/m123;
Sql>grant select on scott.emp to xiaohong;
权限传递方向
Scott----------àxiaoming-------------àxiaohong;
如果将xiaoming对scott用户emp表的权限收回时，xiaohong是否还能对scott的emp表进行操作呢?------------对象权限采用联级回收
Sql>conn scott/whx;
Sql>revke select on emp from xiaoming;
Sql>conn xiaohong/m123;
Sql>select * from scott.emp;------将会出错，对象权限采用联级回收机制
------------------------------第5讲oracle-----------------------------------------------------------------
使用profile管理用户口令
概述:profile是口令限制，资源限制的命令的集合，当建立数据库的时候，oracle会自动建立名称为default的profile文件，当建立用户没有指定profile选项，那么oracle就会将default分配给用户。
(1) 账户锁定
(2) 指定该用户登录时最多可以输入密码的次数，也可以指定用户所定的时间(以天为单位计算),一般要用dba的身份去执行该命令。
Sql>conn system/ych;
Sql>create profile aaa limit failed_login_attempt 3 password_lock_time 2;
Sql>alter user xiaoming profile aaa;
excise---指定xiaoming这个用户最多能尝试3次登陆，锁定2天
sql>conn system/ych;
sql>create profile lock_account limit failed_login_attempt 3 password_lock_time 2;
sql>alter user xiaoming profile lock_account;
缺陷是，如果系改系统时间，就可以不被锁定了。
2).给用户解锁，需要dba来操作
用法:
Sql>conn system/ych;
Sql>alter user 用户名 account unlock;
例如:
Sql>conn system/ych;
Sql>alter user xiaoming account unlock;
3).终止口令
为了让用户定期修改密码，可以使用终止命令来完成，需要dba用户来操作
Sql>conn system/ych;
Sql>create profile bbbb limit password_life_time 10 password_grance_time 2;
Sql>alter user 用户名 profile bbbb;
要求xiaoming用户每隔10天要修改自己的密码，宽限期为2天;
Sql>conn system/ych;
Sql>create profile aaa limit password_life_time 10 password_grance_time 2;
Sql>alter user xiaoming profile aaa;
4).口令历史
如果希望用户在修改密码的时候，不能再使用以前使用过的密码，可以使用口令历史，oracle就会将口令修改的信息存放在数据库中
步骤如下
---1建立profile
Sql>conn system/ych;
Sql>create profile bac limit password_life_time 10 passoworf_grance_time 2 password_reuse_time 10;
---2.分配给用户
Sql>alter user xiaoming profile bac;
5).删除profile
当不需要某个profile文件时，可以删除该文件
Sql>conn system/ych;
Sql>drop profile bac [cascade];-----带有参数cascade,则相关所有的文件全部删除
--------------------第6讲oracle-------------------------------------------------------------------
1.上节回顾
2.oracle的表的管理
3.基本查询
4.复杂查询
5.oracle数据库的创建
期望目标
表名和别的命名规则
--1必须以字母开头
--2长度不能能超过30个字符
--3不能使用oracle的保留字符
--4只能使用A~Z、a~z、0~9，#,$等
Oracle支持的数据类型
--1.字符型
char 定长最大长度为2000个字符
eg: char(10) ‘小王’,前面四个字符存储‘小王’,后面添加6个空格补全
varchar2() 变长最大长度为4000字符
eg: varchar2(10) ‘小寒’,oracle只分配四个字符，这样节省空间
clob(character large object)字符型对象，最大为4G
--2.数字类型
number
范围为-10的38次方<------à10的38次方，可以表示整数，也可以表示小数
number(5,2);表示一个小数有5为整数，2位小数，
-99999.99~99999.99
number(5);表示一个5为整数-99999~99999
---------------第7讲oracle------------------------------------------------------------------------
--3.日期类型
date------------------包含年月日时分秒
timestamp------------oracle9i对date数据类型的扩展
--4.图片/声音/视频
blob 二进制数据可以存放图片/声音 4G
建表
Sql>create table student(xh number(4),xm varchar2(20),sex cahr(2),birthday date,sal number(7,2));
添加字段
Sql>alter table student add(classId number);
修改字段
Sql>alter table student modify (xm varchar2(40));
特别注意，修改字段的类型或是名字，不能有数据
删除字段
Sql>alter table student drop column sal;
修改表的名字
Sql>rename student to stud;
删除表
Sql>drop table student;
添加数据
--添加(插入)全部字段数据
sql>insert into student values (‘A00001’,’张三’,’男’,’09-1月-94’,10);
特别注意:
Oracle默认的日期格式’DD-MM月--YY’;
sql>alter session set nls_date_format=’yyyy-mm-dd’ ;
sql>insert into student values(‘A00001’,’张三’,’男’,’09-1月-94’,10) ;
--添加(插入)部分字段数据
sql>insert into student (xh,xm,sex) values(‘A0003’,’小王’,’男’);
--插入空值
sql>insert into student(xh,xm,sex,birthday) values(‘A00001’,’张三’,’男’,null);
--访问空值
sql>select * from student where birthday is null;
sql>select * from student where birthday is not null;
--修改一个字段的数据
sql>update student set sex=’女’ where xh=’A0001’;
--修改多个字段的数据
sql>update student set sex=’男’,birthday=’1980-04-01’ where xh=’A0001’;
--修改含有null值的数据
sql> update student set sex=’男’,birthday=’1980-04-01’ where xh is null;
sql> update student set sex=’女’ where xh is not null;
sql> update student set sex=’女’ where xh is null;
--删除数据
delete from student;--删除所有记录、表结构、可以恢复。但是速度慢
--回滚命令
sql>rollback aa;
--保存节点命令
sql>savepotin aa;
--删除表的结构和数据，无法找回
sql>drop tbale student;
--删除表的所有的记录，表的结构还在，不写日志，无法找回删除的记录，但是速度快
sql>truncate table student;
-------------------第8讲oracle---------------------------------------------------------------
oracle基本查询
--1.查看表的结构
sql>desc dept;
--2.查看所有列
sql>select * from emp;
--3.查看指定列
sql>select ename,sal,job from emp;
--4.查看指定行
sql>select ename,sal,job from emp where ename=’SCOTT’;
--5.取消重复行
sql>select distinct deptno,job from emp;
--建立数据库
sql>create database spdb1
--建立表dept
sql>create table dept
(deptno int primary key,
dname nvarchar2(30),
loc nvarchar2(30)
)
--创建表emp
sql>create table emp
(
empno int primary key,
ename nvarchar2(30),
job nvarchar2(30),
mgr int,
hiredate date,
sal number(7,2),
coma number(7,2),
deptno int foreign key references dept(deptno)--因为deptno我们根据需要做成外键
)
--针对外键，请注意
--1，只能够指向一个主键 primary key
--2,外键和主键的数据类型要一致
sql>insert into emp (empno,ename,job,mgr,hiredate,sal,deptno)
values(7369,'smith','clerk',7902,'1980-12-17',800.00,20)
sql>insert into emp values(7492,'allen','salesman',7698,'1981-2-20',1600.00,300.00,30)
sql>insert into emp values(7621,'ward','salesman',7698,'1981-2-22',1250.00,500.00,30)
sql>insert into emp (empno,ename,job,mgr,hiredate,sal,deptno)
values(7506,'jones','manager',7839,'1981-4-2',2975.00,20)
sql>insert into emp values(7654,'martin','salesman',7699,'1981-9-28',1250.00,1400.00,30)
sql>insert into emp (empno,ename,job,mgr,hiredate,sal,deptno)
values(7699,'blake','manager',7839,'1981-5-1',2850.00,30)
sql>insert into emp (empno,ename,job,mgr,hiredate,sal,deptno)
values(7782,'slark','manager',7839,'1981-6-9',2450.00,10)
sql>insert into emp (empno,ename,job,mgr,hiredate,sal,deptno)
values(7788,'scott','analyst',7566,'1987-4-19',3000.00,20)
sql>insert into emp (empno,ename,job,hiredate,sal,deptno)
values(7839,'king','president','1981-4-17',5000.00,10)
sql>insert into emp values(7844,'turner','slesman',7698,'1981-9-8',1500.00,0.00,30)
sql>insert into emp (empno,ename,job,mgr,hiredate,sal,deptno)
values(7876,'adams','clerk',7788,'1982-5-23',1100.00,20)
sql>insert into emp (empno,ename,job,mgr,hiredate,sal,deptno)
values(7900,'james','clerk',7698,'1982-12-3',950.00,30)
sql>insert into emp (empno,ename,job,mgr,hiredate,sal,deptno)
values(7902,'ford','analyst',7566,'1982-12-3',3000.00,20)
sql>insert into emp (empno,ename,job,mgr,hiredate,sal,deptno)
values(7934,'miller','clerk',7782,'1982-1-23',1300.00,10)
sql>insert into emp values(9999,'shunping','clerk',7782,'1988-5-5',2456.34,55.66,10)
sql>insert into dept values(10,'accounting','new york')
sql>insert into dept values(20,'reseach','dallas')
sql>insert into dept values(30,'rales','chicage')
sql>insert into dept values(40,'operations','boston')
sql>select * from dept
sql>select * from emp
--查询SMITH的薪水，工作，所在部门
--SQL server不区分大小写，但是oracle区分大小写
sql>select sal,job,deptno from emp where ename='smith'
sql>select sal,job,deptno from emp where ename='SMITH'
--
sql>select deptno from emp
--distinct只取消重复的记录，并且完全相同
sql>select deptno from emp
sql>select deptno ,ename from emp
sql>select distinct deptno from emp
sql>select distinct deptno,ename from emp
--显示每个雇员的年工资
sql>select ename,sal*13年工资 from emp
sql>select ename,sal*13 '年工资' from emp
sql>select ename,sal*13 "年工资" from emp
--年工资就是别名+奖金,出现情况如何处理空NULL的问题
sql>select ename,sal*13+coma*13年工资 from emp
------------------------第9讲oracle-------------------------------------------------------
--SQL server处理NULL的问题 isnull(comm,0)
--oracle处理NULL的问题 nvl(coma,0)
sql>select ename,sal*13+isnull(coma,0)*13年工资 from emp;
sql>select ename.sal*13+nvl(comm.,0)*13年工资 from emp;
--如何处理连接字符串(||)
sql>select ename ||’ is a ’ || job from emp;
--如何显示工资高于3000的员工
sql>select * from emp where sal>3000;
--如何查询1982.1.1以后入职的员工
sql>select *from emp where hiredate>'1982-1-1';
--如何显示工资2000到2500的员工情况(2000=<sal<=2500);
sql>select * from emp where sal>2000 and sal<2500
sql>select *from emp where sal between 2000 and 2500;
--如何使用like操作符
--如何显示首字母为S的员工姓名和工资
%:表示任意0到多个字符
_:表示任意单个字符
sql>select ename,sal from emp where ename like 'S%'
--如何显示第三个字符为大写O的所有员工的姓名和工资
sql>select ename,sal from emp where ename like '__O%';
sql>select ename,sal from emp where ename like '__o%';
--如何显示empno为123,345,800..的情况
sql>select * from emp where empno=123 or empno=345 or empno= 800;
--一般我们使用in这个关键字，效率高
sql>select * from emp where empno in(123,345,800);
sql>select * from emp;
--如何显示没有上级的雇员的情况
--使用is null操作符
sql>select * from emp where mgr is null
--查询工资高于500或是岗位为MANAGER的雇员，
--同时还要满足他们的姓名首写字母为j
sql>select * from emp where (sal>500 or job=manager ) and ename like'j%'
--注意有括号与没有括号的区别
sql>select * from emp where sal>500 or job=manager and ename like'j%'
sql>select * from emp where job=manager
----------第10讲oracle-------------------------------------------------------------------
--使用 order by子句
--如何按照工资的从低到高的顺序显示雇员
--order by默认是asc(升序排列),
--desc(降序排列)
sql>select * from emp order by sal asc;
--desc
sql>select * from emp order by sal desc;
--按照入职的先后顺序排列
sql>select * from emp order by hiredate asc;
--按照名字排序
sql>select * from emp order by ename asc;
--按照部门号升序而雇员的工资降序排列
-- order by 可以根据不同字段排序，可以多个字段一起
sql>select * from emp order by deptno asc,sal desc;
sql>select * from emp order by deptno,sal desc;
--统计每个人的年薪并按照从低到高的顺序，使用别名排序
----在SQL server中
sql>select ename,sal+isnull(comm,0)*13 from emp order by (sal*isnull(coma,0)*13)
--在oracle中
sql>select ename,,sal_nvl(comm.,0)*13 “年薪” from emp order by ‘年薪’ asc;
----在SQL server中
sql>select ename,sal+isnull(coma,0)*13 '年薪' from emp order by '年薪';
----oracle表复杂查询
数组分组---max,min,avg,sum,count;
--如何显示所有员工中的最高工资
--如何显示最低工资和该雇员的名字
sql>select min(sal) from emp;
sql>select min(sal),ename from emp;--------出现错误ORA—00937
sql>select ename,sal from emp where sal=min(sal)
sql>select ename,sal from emp where sal=(select min(sal) from emp);
sql>select ename,sal from emp where sal=(select max(sal) from emp);
--oracle优化的原则是将减少结果条件放在最右边
sql>select ename,sal from emp where sal=(select min(sal) from emp);
sql>select ename,sal from emp where sal=(select max(sal) from emp);
--显示所有员工的平均工资
sql>select avg(sal) '平均工资',sum(sal) '总工资' from emp;
--请显示高于平均工资的雇员的名字和工资
select ename,sal from emp where sal> (select avg(sal) from emp;
--请显示高于平均工资的雇员的名字和工资并显示平均工资
sql>select * from emp where sal> (select avg(sal) from emp);
--计算有多少员工
select count(*) '总人数' from emp
group by和having子句
group by 用于对查询的结果分组统计
having子句用于限制分组显示结果
--如何显示每个部门的平均工资和最高工资
sql>select avg(sal) “平均工资”,deptno “部门编号”,max(sal) from emp group by deptno;
--并显示部门名称，涉及多表查询
--sql>select avg(sal) “平均工资”,deptno “部门编号”,max(sal) from emp group by deptno;
--显示每个部门的各种岗位的平均工资
sql>select avg(sal) “平均工资”,min(sal) “最低工资”,deptno,job from emp group by deptno ,job order by deptno;
--显示平均工资低于2000的部门号和他的平均工资
--having往往和group by结合使用，可以对分组查询结果进行筛选
--SQL执行的顺序是从左到右
sql>select avg(sal),deptno from emp group by deptno;
sql>select avg(sal)“平均工资”,deptno“部门编号”from emp group by deptno having avg(sal)<2000;
sql>select avg(sal) “平均工资”,deptno “部门编号”from emp group by deptno having ‘平均工资’<2000;
--显示平均工资高于2000的部门号和他的平均工资，并按照从低到高
sql>select avg(sal) “平均工资”,deptno “部门编号”from emp group by deptno having avg(sal)>2000 order by avg(sal) asc;
--显示每个部门的每种岗位的平均工资和最低工资
sql>select avg(sal) “平均工资”,max(sal) “最低工资” ,deptno ,job from emp group by deptno,job order by deptno;
1.分组函数值额能出现在选择列表having,order by子句中.
2.如果在select语句中同时包含group by,having,order by,那么他们的顺序是group by,having,order by;
3.在选择列中如果有一个出现在group by子句中，否则就会出现错误
例如:
sql>select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;
这里deptno就一定要出现在group by中
--多表查询
多表查询是指基于两个或是两个以上的表达式或是视图的查询
笛卡尔集规定:多表查询的条件是至少不能少于表的个数-1
sql>select * from emp;
sql>select * from dept
--显示sales部门的位置
--显示雇员号，雇员工资涉及所有部门的名字
sql>select * from emp , dept where dept.dname='sales' and emp.deptno=dept.deptno;
sql>select ename,sal,loc from emp,dept where emp.deptno= dept.deptno;
--列名 'deptno'不明确。如果两张表有相同名字的字段，则需要带表名（别名）
sql>select ename,sal,loc, deptno from emp,dept where emp.deptno= dept.deptno;
sql>select ename,sal,loc, emp.deptno from emp,dept where emp.deptno= dept.deptno;
--用别名,用了别名以后，就不能用表名
sql>select ename,sal,loc, emp.deptno from emp as e,dept as d where emp.deptno= dept.deptno
select ename,sal,loc, e.deptno from emp as e,dept as d where e.deptno= d.deptno;
--显示部门号为10的部门号，员工名。和工资
sql>select d.dname,e.ename,sal from emp e,dept d where e.deptno =10 and e.deptno = d.deptno;
sql>select d.dname,e.ename,e.sal from emp e,dept d where e.deptno =10 and e.deptno =d.deptno;
--显示各个员工的姓名、工资、以及工资的级别
sql>select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;
--显示雇员名，雇员编号以及所在部门的名字，并按部门排序
sql>select e.ename ,d.dname,e.sal from emp e,dept d where e.deptno=d.deptno order by d.dname
--显示某个员工的上级领导的姓名，譬如‘FORD’
--1.知道fond的上级编号
sql>select * from emp order by ename asc;
sql>select ename from emp order by ename asc;
sql>select mgr from emp where ename='ford'
sql>select ename,mgr from emp where ename='ford'
sqlo>select ename from emp where empno=(select mgr from emp where ename='FORD');
--select ename, 'ford' from emp where empno=(select mgr from emp where ename='ford');
select * from emp order by emp.deptno asc;
--显示公司每个员工的名字和他的上级的名字
sql>select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno;
--自连接
--分析，把emp表看做是两张表，分别是worker boss
--把没有上级领导的员工也显示
--解决之道-----外连接（左外连接，右外连接）
--内连接
sql>select worker.ename,boss.ename from emp worker,emp boss where worker.mgr=boss.empno;
什么是子查询
子查询是指嵌套在其他sql语句中的select语句,也叫嵌套查询
--单行子查询,数据库在执行SQL语句时是从左到右开始扫描的。
--如何显示与smith同一部门的所有员工
第一步:
sql>select deptno from emp where ename=’SMITH’;
sql>select * from emp where deptno=(select deptno from emp where ename='SMITH');
sql>select * from emp where deptno in (select deptno from emp where ename='SMITH');
--多行子查询,是指返回多行数据的子查询
--如何查询和部门10的工作相同的雇员的名字，岗位，工资，部门号
sql>select distinct job from emp where deptno=10;
sql>select * from emp where job in(select distinct job from emp where deptno=10);
--如何排除10部门？？
sql>select * from emp where job in(select distinct job from emp where deptno=10) and deptno<>10
;
--在多行子查询中使用all字符
--显示工资比部门30的所有的员工的工资(最高的工资)都要高的员工的信息
sql>select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);
sql>select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);
--在多行子查询中使用any字符
--显示工资比部门30的任意一个员工的工资高的员工的信息
select ename,sal,deptno from emp where sal>any(select sal from emp where deptno=30);
select ename,sal,deptno from emp where sal>(select min(sal) from emp where deptno=30);
--多列子查询
--当行子查询是侄子查询只返回单列、单行数据
--多行字查询时只返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多列数据的子查询语句
--如何查询与smith的部门和岗位完全相同的所有的员工的信息
第一步: sql>select deptno ,job from emp where ename=’SMITH’;
第二步:sql>select * from emp where (deptno,job)=( select deptno ,job from emp where ename=’SMITH’);
-----------第11讲oracle-------------------------------------------------------------------------
--在from子句中使用子查询
--这里需要说明的当在from子句中使用子查询的时候，
--该子查询会被当作为一个视图来对待，
--因此叫做内嵌视图，当在from子句中使用子查询的时候，
--必须给子查询指定别名
--如何显示高于部门平均工资的员工的信息（名字,薪水，部门的平均工资等）
--分析
--1.首先要知道各个部门的平均工资
sql>select avg(sal),deptno from emp group by deptno
sql>select * from emp
--2.把上面查询的结果当做成临时表
sql>select ?,?,? from emp,(select avg(sal) 'myavg',deptno from emp group by deptno) ten where emp.deptno=ten.deptno and emp.sal>ten.myavg;
sql>select emp.ename,emp.sal,ten.myavg from emp,(select avg(sal) 'myavg',deptno from emp group by deptno) ten where emp.deptno=ten.deptno and emp.sal>ten.myavg;
sql>select emp.ename,emp.sal,ten.myavg, emp.deptno from emp ,(select avg(sal) 'myavg',deptno from emp group by deptno) ten where emp.deptno=ten.deptno and emp.sal>ten.myavg;
sql>select emp.ename,emp.sal,ten.myavg, emp.deptno from emp ,(select avg(sal) 'myavg',deptno from emp group by deptno) as ten where emp.deptno=ten.deptno and emp.sal>ten.myavg;
sql>select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2,(select avg(sal) mysal,deptno from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;
---分页查询
//Oracle分页一共有三种方式
1.rownum分页
sql>select * from emp;
--显示rownum[oracle已经分配的]
sql>select a1.*,rownum rn from (select * from emp) a1;
sql>
select * from
(select a1.*,rownum rn from
(select * from emp) a1 where rownum<=10) where rn>=5;
SQL>select * from emp where rownum<=5;--查询前5个人的信息;
--2.几个查询变化
--a.指定查询列，只需修改最内的子查询
select * from (select a1.*,rownum rn from (select ename,sal from emp) a1 where rownum<=10) where rn>=5;
2.rowid分页
3.根据分析函数分页
--用查询结果创建新表
sql>create table mytable(id,ename,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;
在SQL Server中
--按雇员的ID号查询
--??请显示第6个到第10个雇员（按照入职的时间的先后顺序）
--分析一下问题
--显示第1个到第4个入职的雇员
sql>select top 4 * from emp order by hiredate;
sql>select * from emp order by hiredate;
--请显示第6个到第10个雇员（按照入职的时间的先后顺序）
--（select top 4 empno from emp order by hiredate）排序最先入职的雇员
--要排序，否则就不会挑选出按顺序来的
--top 后面的数字表示要取出几条记录
sql>select top 6 * from emp where empno not in (select top 4 empno from emp order by hiredate) order by hiredate;
--？？请显示第11个到第13个入职的雇员
sql>select top 3 * from emp where empno not in (select top 10 empno from emp order by hiredate) order by hiredate;
--???请显示第5个到第9个入职的雇员(按薪水的高低（低到高）)
sql>select top 5 * from emp where empno not in (select top 4 empno from emp order by sal desc) order by sal desc;
----testId int identity<n,m>表示该testId字段自增，从n，每次加m
---testId int identity<1,1>表示该testId字段自增，从1，每次加1
sql>create table test
(
testId int primary key identity(1,1),
testName varchar2(30),
testPass varchar2(30)
)
sql>insert into test (testName,testPass) values('WangHong','shunping');
--疯狂复制
sql>insert into test (testName,testPass)
sql>select testName,testPass from test;
--统计查询
sql>select count(*) from test
--查询所有信息
sql>select * from test
--查询一个
sql>select testName from test
--test表按照id排序100 -105
sql>select top 6 * from test where testId not in (select top 99 testId from test)
--test表按照id排序100000 -100005
sql>select top 6 * from test where testId not in (select top 99999 testId from test)
--合并查询
在实际应用中，为了合并多个select语句的结果，可以使用操作符
union,union all,intersect,minus;
--1.union
--该操作符用于取得两个结果集的并集，当使用该操作符的时候会自动去掉结果集中重复的行
sql>select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job='MANAGER';
--2.union all
--该操作符用于取得两个结果集的并集，当使用该操作符的时候会并不会去掉结果集中重复的行，而且不会排序
sql>select ename,sal,job from emp where sal>2500 union all select ename,sal,job from emp where job='MANAGER';
--3.intersect
--该操作符用于取得两个结果集的交集，
sql>select ename,sal,job from emp where sal>2500 intersect select ename,sal,job from emp where job='MANAGER';
--4.minus
--该操作符用于取得两个结果集的交集，他只会显示存在第一个集合中的，而不存在第二个集合中的数据
sql>select ename,sal,job from emp where sal>2500 minus select ename,sal,job from emp where job='MANAGER';
--创建新的数据库
两种方式
1.通过oracle提供的工具向导
dbca[数据库配置助手]Datebase Configuration Assitant
2.我们可以手工步骤直接创建
------------------第12讲oracle-------------------------------------------------------------------
1.上节回顾
2.java程序如何操作oracle
3.如何在oracle中操作数据
4.oracle的事务处理
5.sql函数的使用
期望目标
1.oracle表对数据操作技巧
2.在java程序中操作oracle
3.oracl事务概念
4.oracle各种sql函数
--b.排序，只需修改最内的子查询
sql>select * from (select a1.*,rownum rn from (select ename,sal from emp order by sal) a1 where rownum<=10) where rn>=5;
使用to_date函数
如何插入到带有日期的表，并按照年-月-日的格式插入
sql>insert into emp values(9997,'小红','manager',7882,'12-12月-1984',78.9,55.33,10);
sql>insert into emp values(‘9997,'小红','manager',7882,to_date('1996-23-23''yyyy-mm-dd',78.9,55.33,10);
sql>insert into emp values(9997,'小红
','manager',7882,to_date('1996/23/23''yyyy/mm/dd',78.9,55.33,10);
----------------------第13讲oracle---------------------------------------------------
--当使用values子句的时候，一次只能插入一行数据，
--当使用子查询数据的时候，一条insert语句可以插入大量的数据，
--当处理行迁移或者装在外部表的数据到数据库的时候，
--可以使用子查询来插入数据,可以插入多条数据
sql>create table kkk(myId number(4),myname varchar2(50),myDept number(5));
sql>insert into kkk valuse(myId,myname,myDept) select empno,ename,deptno from emp where deptno=10;
--使用update语句跟新数据的时候,
--即可以使用表达式或者数值直接修改数据,
--也可以使用子查询修改数据
---？希望员工scott的job,sal,comm与smith员工一样
sql>update emp set (job,sal,comm)=select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';
--什么是事务
事务用于保证数据的一致性，
--它由一组相关的dml语句组成，
--该组的语句要么全成功，要么全部失败
---如网上转账就是典型的事务处理，用以保证数据的一致性
---事务和锁
当执行事务操作的时候(dml语句),oracle会在被作用的表上加锁,
---防止其他用户修改表的结构，这里对我们用户来讲就是非常重要的
--提交事务
当执行commit语句的时候可以提交事务，当执行了commit语句之后
--，会确认事务的变化,结束事务，删除保存点，释放锁，
--当执行commit语句结束之后，其他会话将可以查看到事务的变化后的数据
sql>commit;
--回退事务
--在介绍回退事务之前，先知道保存点(savepoint)的概念和作用，
--保存点是事务中的一点，用于取消部分事务，当结束事务的时候，
--会自动的删除该事务所定义的所有保存点，当执行rollback语句时候，
--通过指定保存点可以回退到指定的点
sql>rollback aa;
--事务的几个重要操作
1).设置保存点
savepoint a;
2).取消部分事务
sql>rollback to a;
3).取消全部事务
sql>rollback;
sql>savepoint a1;
sql>delete from emp where empno=9996;
……….
sql>savepoint a2;
sql>delete from emp where empno=9994;
.........
sql>rollback to a1;//可以回退到任何一个保存点，
--特别注意：
----当执行commit语句的时候可以提交事务，当执行了commit语句之后
----，会确认事务的变化,结束事务，删除保存点，
示例如下
sql>rollback to a2;
SQL> savepoint a1;
Savepoint created
SQL> delete from emp where empno=9996;
0 rows deleted
SQL> select * from emp;
14 rows selected
SQL> savepoint a2;
Savepoint created
SQL> delete from emp where empno=9999;
0 rows deleted
SQL> ollback a2;
ollback a2
ORA-00900: 无效 SQL语句
SQL> rollback a2;
rollback a2
ORA-02181: 无效的 ROLLBAC WORK选项
SQL> rollback a1;
rollback a1
ORA-02181: 无效的 ROLLBAC WORK选项
SQL> rollback to a1;
Rollback complete
SQL> commit;
Commit complete
SQL> rollback to a1;
rollback to a1
ORA-01086: 从未创建保存点 'A1'
--java程序如何使用事务
----在java操作数据库的时候，为了保证数据的一致性比如转账
1。从一个账户减掉10￥
2.从一个账户加入10￥
package com;
import java.sql.*;
public class TestTrans {
public static void main(String[] args) {
// TODO Auto-generated method stub
//事务案例
Connection ct=null;
Statement sm=null;
try {
//1.加载驱动
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//2.得到连接,必须要配置数据源(jdbc:odbc不允许远程连接，但是jdbc可以远程连接)
ct=DriverManager.getConnection("jdbc:odbc:textshun","scott","whx");
//加入事务处理
ct.setAutoCommit(false);//设置不能默认提交
sm=ct.createStatement();
//从scott员工中sal-100
sm.executeUpdate("update emp set sal=sal-100 where ename='SCOTT'");
int i=7/0;//意外终止，抛异常
//给SMITH+100
sm.executeUpdate("update emp set sal=sal+100 where ename='SMTIH'");
//提交事务
ct.commit();
sm.close();
ct.close();
} catch (Exception e) {
//如果发生异常，就回滚
try {
ct.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
}
}
}
--只读事务
只读事务是指只允许执行查询的操作，而不允许执行任何其他dml操作的
--事务，使用只读事务可以确保用户只能取得某时间点的数据，假定机票代售点每天
--18点钟开始统计今天的销售情况，这是可以使用只读事务，在设置了只读事务后
--，尽管其他回话可能会提交新的事务，但是只读事务将不会取得最新数据的变化，
--从而可以确保取得特定时间点的数据信息
--设置只读事务
sql>set transaction read only;
---------第14讲oracle---------------------------------------------------------------
sql函数的使用-----字符函数
字符函数是oracle中最常用的函数
lower(char);将字符串转化成小写的格式
upper(char);将字符串转化成大写的格式
length(char);返回字符串的长度
substr(char,m,n);取字符串的子串,m是开始取的位置，n取的字符的个数
1.将所有员工的姓名按小写的方式显示
sql>select lower(ename) from emp;
2.将所有员工的姓名按大写的方式显示
sql>select upper(ename),sal,job from emp;
3.显示正好为5个字符的员工的姓名
sql>select * from emp where length(ename)=5;
4.显示所有员工姓名的前三个字符
sql>select substr(ename,2,3),sal,job from emp;
sql>select substr(ename,1,3),sal,job from emp;
5.以首字母大写的方式显示所有员工的姓名
--分析一下
1.完成首字母大写
sql>select upper(substr(ename,1,1)) from emp;
2.完成后面字母小写
sql>select lower(substr(ename,2,length(ename)-1)) from emp;
3.合并
sql>select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp;
6.以首字母小写的方式显示所有员工的姓名
--分析一下
1.完成首字母小写
sql>select lower(substr(ename,1,1)) from emp;
2.完成后面字母大写写
sql>select upper(substr(ename,2,length(ename)-1)) from emp;
3.合并
sql>select lower(substr(ename,1,1)) || upper(substr(ename,2,length(ename)-1)) from emp;
replace(char1,search_string,replace_string);//替代函数
instr(char1,char2,[,n[,m]]);//取子串在字符串的位置
1.显示所有员工的姓名，用"我是A"替换所有的"A";
select replace (ename,'A','我是大人物') from emp;
sql函数的使用---数学函数
--数学函数的输入参数和返回值的数据类型都是数字类型的，
--数学函数包括cos,sin,tan,sinh,cosh,sqrt,tanh,acos,asin,atan,round
最常用的
round(n,[m])
trunc(n,[m])
mod(m,n)取模
floor(n)
ceil(n)
--对数字的处理，在财务系统或者银行系统中使用的最多不同的是处理方法
--对财务报表有不同的结果
round(n,[m])该函数用于执行四舍五入，如果省掉m，则四舍五入到整数；
sql>select (round(sal)+round(comm)*13) from emp;
sql>select (round(sal)+round(comm))*13 from emp where ename='xiaowang';
sql>select ename,round(sal),sal from emp where ename='xiaowang';
ENAME ROUND(SAL) SAL
---------- ---------- ---------
xiaowang 34 34.34
如果m是整数，则四舍五入到小数点的m位后，
select ename,round(sal,1),sal from emp where ename='xiaowang';
ENAME ROUND(SAL,1) SAL
---------- ------------ ---------
xiaowang 34.3 34.34
如果m是负数，则四舍五入到小数点的m位前
select ename,round(sal,-2),sal from emp where ename='xiaowang';
ENAME ROUND(SAL,-2) SAL
---------- ------------- ---------
xiaowang 0 34.34
select ename,round(sal,-1),sal from emp where ename='xiaowang';
ENAME ROUND(SAL,-1) SAL
---------- ------------- ---------
xiaowang 30 34.34
trunc(n,[m])该函数用于截取数字，如果省掉m，就截去小数部分
如果m是正数就就截取到小数点的m位后，
如果m是负数就截取到小数点的前m位
SQL> select ename,trunc(comm,-1),comm from emp where ename='xiaowang';
ENAME TRUNC(COMM,-1) COMM
---------- -------------- ---------
xiaowang 20 23.23
如果m是正数就就截取到小数点的m位后，
SQL> select ename,trunc(comm,1),comm from emp where ename='xiaowang';
ENAME TRUNC(COMM,1) COMM
---------- ------------- ---------
xiaowang 23.2 23.23
如果省掉m，就截去小数部分
SQL> select ename,trunc(comm),comm from emp where ename='xiaowang';
ENAME TRUNC(COMM) COMM
---------- ----------- ---------
xiaowang 23 23.23
如果m是负数就截取到小数点的前m位
SQL> select ename,trunc(comm,-2),comm from emp where ename='xiaowang';
ENAME TRUNC(COMM,-2) COMM
---------- -------------- ---------
xiaowang 0 23.23
SQL> select ename,trunc(comm,2),comm from emp where ename='xiaowang';
ENAME TRUNC(COMM,2) COMM
---------- ------------- ---------
xiaowang 23.23 23.23
mod(m,n)取模
select mod(10,2) from dual;
MOD(10,2)
----------
0
SQL> select mod(10,3) from dual;
MOD(10,3)
----------
1
SQL> select mod(10,6) from dual;
MOD(10,6)
----------
4
dual表在oracle中做测试
floor(n)返回小于或者等于n的最大整数
select ename,floor(comm),comm from emp where ename='xiaowang';
ENAME FLOOR(COMM) COMM
---------- ----------- ---------
xiaowang 23 23.23
ceil(n)返回大于或者等于n的最小整数
select ename,ceil(comm),comm from emp where ename='xiaowang';
ENAME CEIL(COMM) COMM
---------- ---------- ---------
xiaowang 24 23.23
显示在一个月为30天的情况所有员工的日薪金，忽略余数
select ename,floor(sal/30) from emp;
select ename,trunc(sal/30) from emp;
abs(n)返回数字n的绝对值
select abs(-3) from dual;
acos(n)返回数字的反余弦值
select acos(0.534) from dual;
asin(n)返回数字的反正弦值
select asin(0.76) from dual;
atan(n)返回数字的反正切值
select atan(3) from dual;
cos(n)
select cos(3) from dual;
sin(n)
select sin(3) from dual;
exp(n)返回e的n次幂
select exp(3) from dual;
log(m,n)返回对数值
SQL> select log(12,32) from dual;
LOG(12,32)
----------
1.39471472
SQL> select log(32,12) from dual;
LOG(32,12)
----------
0.71699250
power(m,n)返回m的n次幂
SQL> select power(23,1) from dual;
POWER(23,1)
-----------
23
SQL> select power(23,3) from dual;
POWER(23,3)
-----------
12167
----------------第15讲oracle----------------------------------------------------------------
sql函数的使用--日期函数
日期函数用于处理date类型的数据
默认情况下日期格式是dd-mon-yy即24-9月-92
(1)sysdate:该函数返回系统时间
SQL> select sysdate from dual;
SYSDATE
-----------
2012-12-24
(2)add_months(d,n)://d+n
(3)last_day(d):返回指定日其所在月份的最后一天
查找已经入职8个月多的员工
select * from emp where sysdate>add_months(hiredate,8);
查找已经入职360个月多的员工
select * from emp where sysdate>add_months(hiredate,360);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
12 rows selected
显示满10年服务年限的员工的姓名和受雇日期
SQL> select * from emp where sysdate>add_months(hiredate,12*10);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
8088 xiaowang MANAGER 7782 1988-12-12 34.34 23.23 10
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT CLERK 7566 1987-4-19 700.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
15 rows selected
对于每一个员工，显示其加入公示的天数
select ename,trunc(sysdate-hiredate) "入职天数" from emp;
select ename,floor(sysdate-hiredate) "入职天数" from emp;
select ename,(sysdate-hiredate) "入职天数" from emp;
SQL> select ename,floor(sysdate-hiredate) "入职天数" from emp;
ENAME 入职天数
---------- ----------
xiaowang 8778
SMITH 11695
ALLEN 11630
WARD 11628
JONES 11589
MARTIN 11410
BLAKE 11560
CLARK 11521
SCOTT 9381
KING 11360
TURNER 11430
ADAMS 9347
JAMES 11344
FORD 11344
MILLER 11293
15 rows selected
SQL> select ename,trunc(sysdate-hiredate) "入职天数" from emp;
ENAME 入职天数
---------- ----------
xiaowang 8778
SMITH 11695
ALLEN 11630
WARD 11628
JONES 11589
MARTIN 11410
BLAKE 11560
CLARK 11521
SCOTT 9381
KING 11360
TURNER 11430
ADAMS 9347
JAMES 11344
FORD 11344
MILLER 11293
15 rows selected
SQL> select ename,sysdate-hiredate "入职天数" from emp;
ENAME 入职天数
---------- ----------
xiaowang 8778.46850
SMITH 11695.4685
ALLEN 11630.4685
WARD 11628.4685
JONES 11589.4685
MARTIN 11410.4685
BLAKE 11560.4685
CLARK 11521.4685
SCOTT 9381.46850
KING 11360.4685
TURNER 11430.4685
ADAMS 9347.46850
JAMES 11344.4685
FORD 11344.4685
MILLER 11293.4685
15 rows selected
找出个月倒数第3天受雇的所有的员工
select ename,hiredate from emp where last_day(hiredate)-2=hiredate;
select hiredate,last_day(hiredate) from emp;
SQL> select ename,hiredate from emp where last_day(hiredate)-2=hiredate;
ENAME HIREDATE
---------- -----------
MARTIN 1981-9-28
sql函数的使用---转换函数
转换函数用于将数据类型从一种转为另一种，在某些情况下，
oracle server允许值的数据类型和实际的不一样，这是oracle server
会隐含的转化数据类型
比如：
create table t1(id int)
insert into t1 values('10');
这样oracle会自动地将'10'转化成10
create table t2(id varchar2(10));
insert into t2 values(1);
这样oracle会自动地将1转换成----'1';
我们要说的是尽管oracle可以隐函数的数据类型的转换，
但是他并不适应所有的情况，为了提高程序的可靠性，我们应该使用转换函数进行转换
to_char
可以使用select ename,hiredate,sal,from emp where deptno=10;
显示信息，可是，在某些情况下，这个病不能满足你的要求
yy:两位数字的年份2004--->04 9：显示数字，并忽略前面的0
yyyy:四位数字的年份 2004年 0：显示数字，如果为数不足，则用0补齐
mm:2为数值的月份 8月--08 .：在指定位置显示小数点
dd:2位数字的天 30号 -30 ,:在指定位置显示逗号
hh24:8点--------20 $:在数字前面加美元符
hh12:8点--------08 L：在数字面前加本地货币符号
mi:显示分 C：在职数字前面加国际货币符号
ss:显示秒 G：在指定位置显示组合分隔符
D：在指定位置显示小数点符号(.)
薪水是否可以显示指定的货币符号
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'L99999.99') from emp;
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'$99999.99') from emp;
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'L99,999.99') from emp;
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'L99999,99') from emp;
？日期是否可以显示时/分/秒-----如果在插入数据的时候，不指定时分秒，则默认全部为0
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
8088 xiaowang MANAGER 7782 1988-12-12 34.34 23.23 10
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT CLERK 7566 1987-4-19 700.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
15 rows selected
SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH
---------- ---------------------------------------------------------------------------------------------
xiaowang 1988-12-12 00:00:00
SMITH 1980-12-17 00:00:00
ALLEN 1981-02-20 00:00:00
WARD 1981-02-22 00:00:00
JONES 1981-04-02 00:00:00
MARTIN 1981-09-28 00:00:00
BLAKE 1981-05-01 00:00:00
CLARK 1981-06-09 00:00:00
SCOTT 1987-04-19 00:00:00
KING 1981-11-17 00:00:00
TURNER 1981-09-08 00:00:00
ADAMS 1987-05-23 00:00:00
JAMES 1981-12-03 00:00:00
FORD 1981-12-03 00:00:00
MILLER 1982-01-23 00:00:00
15 rows selected
SQL> inset into emp values(8881,'测试人员','MANAGER',7782,sysdate,23,23,10);
inset into emp values(8881,'测试人员','MANAGER',7782,sysdate,23,23,10)
ORA-00900: 无效 SQL语句
SQL> insert into emp values(8881,'测试人员','MANAGER',7782,sysdate,23,23,10);
1 row inserted
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- -----------------------------------------------
8088 xiaowang MANAGER 7782 1988-12-12 34.34 23.23 10
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT CLERK 7566 1987-4-19 700.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
8881 测试人员 MANAGER 7782 2012-12-24 23.00 23.00 10
16 rows selected
SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH
---------- ------------------------------------------------------------------------------------------------------------
xiaowang 1988-12-12 00:00:00
SMITH 1980-12-17 00:00:00
ALLEN 1981-02-20 00:00:00
WARD 1981-02-22 00:00:00
JONES 1981-04-02 00:00:00
MARTIN 1981-09-28 00:00:00
BLAKE 1981-05-01 00:00:00
CLARK 1981-06-09 00:00:00
SCOTT 1987-04-19 00:00:00
KING 1981-11-17 00:00:00
TURNER 1981-09-08 00:00:00
ADAMS 1987-05-23 00:00:00
JAMES 1981-12-03 00:00:00
FORD 1981-12-03 00:00:00
MILLER 1982-01-23 00:00:00
测试人员 2012-12-24 16:21:54
16 rows selected
SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),sal from emp;
ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH SAL
---------- ------------------------------ ------------------------------------------------------------------
xiaowang 1988-12-12 00:00:00 34.34
SMITH 1980-12-17 00:00:00 800.00
ALLEN 1981-02-20 00:00:00 1600.00
WARD 1981-02-22 00:00:00 1250.00
JONES 1981-04-02 00:00:00 2975.00
MARTIN 1981-09-28 00:00:00 1250.00
BLAKE 1981-05-01 00:00:00 2850.00
CLARK 1981-06-09 00:00:00 2450.00
SCOTT 1987-04-19 00:00:00 700.00
KING 1981-11-17 00:00:00 5000.00
TURNER 1981-09-08 00:00:00 1500.00
ADAMS 1987-05-23 00:00:00 1100.00
JAMES 1981-12-03 00:00:00 950.00
FORD 1981-12-03 00:00:00 3000.00
MILLER 1982-01-23 00:00:00 1300.00
测试人员 2012-12-24 16:21:54 23.00
16 rows selected
SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'L99999.99') from emp;
ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH TO_CHAR(SAL,'L99999.99')
---------- ------------------------------ ----------------------------------------------------------------------------
xiaowang 1988-12-12 00:00:00 ￥34.34
SMITH 1980-12-17 00:00:00 ￥800.00
ALLEN 1981-02-20 00:00:00 ￥1600.00
WARD 1981-02-22 00:00:00 ￥1250.00
JONES 1981-04-02 00:00:00 ￥2975.00
MARTIN 1981-09-28 00:00:00 ￥1250.00
BLAKE 1981-05-01 00:00:00 ￥2850.00
CLARK 1981-06-09 00:00:00 ￥2450.00
SCOTT 1987-04-19 00:00:00 ￥700.00
KING 1981-11-17 00:00:00 ￥5000.00
TURNER 1981-09-08 00:00:00 ￥1500.00
ADAMS 1987-05-23 00:00:00 ￥1100.00
JAMES 1981-12-03 00:00:00 ￥950.00
FORD 1981-12-03 00:00:00 ￥3000.00
MILLER 1982-01-23 00:00:00 ￥1300.00
测试人员 2012-12-24 16:21:54 ￥23.00
16 rows selected
SQL> desc emp;
Name Type Nullable Default Comments
-------- ------------ -------- ------- -------- -----------------------------------------------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'L99999,99') from emp;
ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH TO_CHAR(SAL,'L99999,99')
---------- ------------------------------ -------------------------------------------------------------------------
xiaowang 1988-12-12 00:00:00 ￥34
SMITH 1980-12-17 00:00:00 ￥8,00
ALLEN 1981-02-20 00:00:00 ￥16,00
WARD 1981-02-22 00:00:00 ￥12,50
JONES 1981-04-02 00:00:00 ￥29,75
MARTIN 1981-09-28 00:00:00 ￥12,50
BLAKE 1981-05-01 00:00:00 ￥28,50
CLARK 1981-06-09 00:00:00 ￥24,50
SCOTT 1987-04-19 00:00:00 ￥7,00
KING 1981-11-17 00:00:00 ￥50,00
TURNER 1981-09-08 00:00:00 ￥15,00
ADAMS 1987-05-23 00:00:00 ￥11,00
JAMES 1981-12-03 00:00:00 ￥9,50
FORD 1981-12-03 00:00:00 ￥30,00
MILLER 1982-01-23 00:00:00 ￥13,00
测试人员 2012-12-24 16:21:54 ￥23
16 rows selected
SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'L99,999.99') from emp;
ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH TO_CHAR(SAL,'L99,999.99')
---------- ------------------------------ -----------------------------------------------------------------------------
xiaowang 1988-12-12 00:00:00 ￥34.34
SMITH 1980-12-17 00:00:00 ￥800.00
ALLEN 1981-02-20 00:00:00 ￥1,600.00
WARD 1981-02-22 00:00:00 ￥1,250.00
JONES 1981-04-02 00:00:00 ￥2,975.00
MARTIN 1981-09-28 00:00:00 ￥1,250.00
BLAKE 1981-05-01 00:00:00 ￥2,850.00
CLARK 1981-06-09 00:00:00 ￥2,450.00
SCOTT 1987-04-19 00:00:00 ￥700.00
KING 1981-11-17 00:00:00 ￥5,000.00
TURNER 1981-09-08 00:00:00 ￥1,500.00
ADAMS 1987-05-23 00:00:00 ￥1,100.00
JAMES 1981-12-03 00:00:00 ￥950.00
FORD 1981-12-03 00:00:00 ￥3,000.00
MILLER 1982-01-23 00:00:00 ￥1,300.00
测试人员 2012-12-24 16:21:54 ￥23.00
16 rows selected
SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'L 99,999.99') from emp;
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'L 99,999.99') from emp
ORA-01481: 无效的数字格式模型
SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'L099,999.99') from emp;
ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH TO_CHAR(SAL,'L099,999.99')
---------- ------------------------------ -----------------------------------------------------------------------------
xiaowang 1988-12-12 00:00:00 ￥000,034.34
SMITH 1980-12-17 00:00:00 ￥000,800.00
ALLEN 1981-02-20 00:00:00 ￥001,600.00
WARD 1981-02-22 00:00:00 ￥001,250.00
JONES 1981-04-02 00:00:00 ￥002,975.00
MARTIN 1981-09-28 00:00:00 ￥001,250.00
BLAKE 1981-05-01 00:00:00 ￥002,850.00
CLARK 1981-06-09 00:00:00 ￥002,450.00
SCOTT 1987-04-19 00:00:00 ￥000,700.00
KING 1981-11-17 00:00:00 ￥005,000.00
TURNER 1981-09-08 00:00:00 ￥001,500.00
ADAMS 1987-05-23 00:00:00 ￥001,100.00
JAMES 1981-12-03 00:00:00 ￥000,950.00
FORD 1981-12-03 00:00:00 ￥003,000.00
MILLER 1982-01-23 00:00:00 ￥001,300.00
测试人员 2012-12-24 16:21:54 ￥000,023.00
16 rows selected
SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'C99,999.99') from emp;
ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH TO_CHAR(SAL,'C99,999.99')
---------- ------------------------------ ------------------------------------------------------------------------
xiaowang 1988-12-12 00:00:00 CNY34.34
SMITH 1980-12-17 00:00:00 CNY800.00
ALLEN 1981-02-20 00:00:00 CNY1,600.00
WARD 1981-02-22 00:00:00 CNY1,250.00
JONES 1981-04-02 00:00:00 CNY2,975.00
MARTIN 1981-09-28 00:00:00 CNY1,250.00
BLAKE 1981-05-01 00:00:00 CNY2,850.00
CLARK 1981-06-09 00:00:00 CNY2,450.00
SCOTT 1987-04-19 00:00:00 CNY700.00
KING 1981-11-17 00:00:00 CNY5,000.00
TURNER 1981-09-08 00:00:00 CNY1,500.00
ADAMS 1987-05-23 00:00:00 CNY1,100.00
JAMES 1981-12-03 00:00:00 CNY950.00
FORD 1981-12-03 00:00:00 CNY3,000.00
MILLER 1982-01-23 00:00:00 CNY1,300.00
测试人员 2012-12-24 16:21:54 CNY23.00
16 rows selected
显示1980年入职的所有的员工
select * from emp where to_char(hiredate,'yyyy')=1980;
显示所有在12月份入职的员工
select * from emp where to_char(hiredate,'mm')=12;
sql函数使用-----系统函数
sys_context
1).terminal:当前会话客户所对应的终端的标示符
2).language:语言
3).db_name:当前数据库的名称
4).nls_date_format:当前会话客户所对应的日期格式
5).session_user：当前会话客户所对应的数据库用户名
6).current_schema：当前会话客户所对应的默认方案名
7).host:返回数据库所在主机的名称
通过该函数，可以查询一些重要的信息，比如你正在使用
--那个数据库
select sys_context('userenv','db_name') from dual;
select sys_context('userenv','******') from dual;
SQL> select sys_context('userenv','db_name') from dual;
SYS_CONTEXT('USERENV','DB_NAME
--------------------------------------------------------------------------------
orcl
SQL> select sys_context('userenv','lanuage') from dual;
select sys_context('userenv','lanuage') from dual
ORA-02003: 无效的 USERENV参数
SQL> select sys_context('userenv','language') from dual;
SYS_CONTEXT('USERENV','LANGUAG
--------------------------------------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
SQL> select sys_context('userenv','session_user') from dual;
SYS_CONTEXT('USERENV','SESSION
--------------------------------------------------------------------------------
SCOTT
SQL> select sys_context('userenv','current_schema') from dual;
SYS_CONTEXT('USERENV','CURRENT
--------------------------------------------------------------------------------
SCOTT
SQL> select sys_context('userenv','nls_date_formal') from dual;
select sys_context('userenv','nls_date_formal') from dual
ORA-02003: 无效的 USERENV参数
SQL> select sys_context('userenv','nls_date_format') from dual;
SYS_CONTEXT('USERENV','NLS_DAT
--------------------------------------------------------------------------------
DD-MON-RR
---------第16讲oracle------------------------------------------------------------------
1.数据库管理员
2.数据库表的逻辑备份和恢复
3.数据字典和动态性能视图
4.管理表空间和数据文件
------期望目标
1.了解oracle管理员的基本职责
2.掌握备份和恢复数据库、表的方法
3.理解表空间，数据字典，性能视图
数据库管理员
每个oracle数据库应该至少有一名数据库管理员(dba),
对于一个小的数据库，一个dba就够了，但是对于一个大的数据库可能需要多个
dba分别担负不同的管理职责，那么一个数据库管理员的主要的工作是什么呢
1.安装和升级oracle数据库
2.坚苦，表空间，表，视图，索引...
3.制定并实时备份和恢复计划
4.数据库权限管理，调优，故障排除
5.对于高级dba，要求能参与项目开发，会编写sql语句，存储过程、触发器、规则、约束、包
管理数据库的用户主要有sys和system(sys董事长，system总经理)
在前面我们已经提到了这两个用户，区别主要是:
(1).最重要的区别，存储的数据的重要性不同
sys:所有oracle的数据字典的基本表和视图都存放在sys用户中，这些基表和视图对于oracle的运行是至关重要的，
----有数据库自己维护，任何用户都不能手动修改，sys用户拥有dba、sysdba、sysoper角色或权限，视oracle权限最高的用户
system:用于存放次一级的内部数据，如oracle的一些特性或工具的管理信息，system用户拥有dba，sysdba角色或系统权限。没有sysoper角色或权限
(2).其次的区别，权限的不同
sys用户必须以 as sysdba或as sysoper形式登陆，不能以normal方式登录到数据库中
system如果正常登陆，他其实就是一个普通的dba用户，但是如果以as sysdba登陆，其结果实际上它是作为sys用户登录，从登录信息里面我们可以看出来
错误的登录方式
sql>conn sys/ych;
ERROR:connect to sys should be as sysdba or sysoper;
正确的登陆方式
sql>conn sys/ych as sysdba;
sql>conn sys/ych as sysoper;
sql>conn system/ych as sysdba;
sql>conn system/ych as sysoper;
sql.>conn system/ych;
bda权限的用户
---dba用户是指具有dba角色的数据库用户，特权用户可以执行启动实例，关闭实例等特殊操作，
----而dba用户只有启动数据库之后才能执行各种管理工作
管理初始化参数
初始化参数用于设置实例或是数据库的特征，oracle9i提供了200多个初始化参数，并且每个初始化参数都有默认值
--显示初始化参数
sql>show parameter;
--如何修改参数
需要说明的是如果你希望修改这些初始化的参数，可以到文件.....\pfile\init.ora文件中去修改，比如要修改实例的名字
数据库(表)的逻辑备份与恢复----介绍
逻辑备份是指使用工具export将数据对象的结构和数据导出到文件的过程，
逻辑恢复是指当数据库对象被误操作而损坏后使用工具import利用备份的文件把数据对象导入到数据库的过程，
物理备份即可在数据open的状态下进行也可关闭数据库后进行，但是逻辑备份和恢复只能在open的状态下进行。
最常用的方式
---------导出
---导出表
---导出方案
---当初数据库
导出具体的分为:导出表、导出方案、导出数据库三种方式
到处使用exp命令来完成的，该命令常用的选项有:
userid:用于指定执行导出操作的用户名、口令、连接字符串
tables:用于指定执行导出操作的表
owner:用于指定执行导出操作的方案
full=y:用于指定执行导出操作的数据库
inctype:用于指定执行导出操作的增量类型
rows:用于指定执行导出操作是否要导出表中的数据
file:用于指定导出文件名
特别说明:
在导入和导出的时候，要切换到oracle目录的bin目录下。
D:\ych\app\product\11.1.0\db_1\BIN\exp.exe
导出表
---(1)导出自己的表---单张表
exp useid=scott/whx@oracl tables=(emp) file=d:\e1.dmp;
导出多张表
exp useid=scott/whx@oracl tables=(emp,dept) file=d:\e1.dmp;
---(2)导出其他方案(用户)的表
如果用户要导出其他方案的表,咋需要dba的权限或是exp_full_database的权限，比如system就可以导出scott的表
exp userid=system/ych@oracl tables=(scott.emp) file=d:\e2.dmp;
--(3)导出表的结构
sql>exp userid=scott/whx@oracl tables=(emp) file=d:\e2.dmp rows=n;
--(4)使用直接导出方式
exp userid=scott/whx@oracl tables=(emp) file=d:\e2.dmp direct=y;
exp userid=scott/whx@oracl tables=(emp,dept) file=d:\e2.dmp direct=y;
这种方式比默认的常规方式速度要快，当数据量大的时候，可以考虑使用这种方法
这时候需要数据库的字符既要与客户端字符集完全一致，否则会报错.....
------导出方案
导出方案是指使用export工具导出一个方案或是多个方案中的所有对象(表、索引、约束....)和数据，并存放在文件中
(1)导出自己的方案
exp scott/whx@oracl owner=scott file d:\scott.dep;
(2)导出其他方案
如果用户要导出其他方案，则需要dba的权限或是exp_full_database的权限
例如system用户就可以到处任何方案
exp system/ych@oracl owner=(system,scott) file d:\system.dep;
----------导出数据库
导出数据库是指利用export导出所有数据库中的对象以及数据，要求该用户具有dba权限或是exp_full_database权限
exp userid=system/ych@oracl full=y inctype=complete file=d:\\x.dmp;
因为数据量大，所以耗用的时间会较长
-----------第17讲oracle----------------------------------------------------
-----------导入---------------------------
假设要将SQL Server中的Northwind数据库中的Products表导出到Oracle的Scott用户,首先需要有安装SQL Server企业版
1.打开工具：
开始->程序->Microsoft SQL Server->导入和导出数据
2.下一步，选择数据源
[数据源]选择“用于SQL Server的Microsoft OLE DB提供程序”，这应该是缺省值。
[服务器]选择要导出数据的服务器，如果是本机，选择(local)
[数据库]选择要导出的数据所在的库，这里选择Northwind
3.下一步，选择目的
[目的]选择Microsoft ODBC for Oracle
[DSN]选择用户/系统DSN一项，然后在下拉列表框中找一个已经连接到了Scott用户的DSN名称。
如果下拉列表中没有，点下拉列表框右侧的[新建]，出现创建新数据源界面。
接下来选择系统数据源，下一步，在驱动程序列表中选择Microsoft ODBC for Oracle
下一步，完成，出现Microsoft ODBC for Oracle安装界面
[数据源名称]随便输入，比如sss
[说明]可以不填
[用户名称]填入要导出到的Oracle用户名称，这里是scott
[服务器]填入要连接到Oracle服务器所使用的服务名，比方说使用sqlplus scott/tiger@server1可以连接到数据库，那么这里就填入server1。如果本机就是服务器，使用sqlplus scott/tiger即可连接到数据库，这里空着不填即可。
这样就创建好了一个数据源sss，保证下拉列表框中选择了sss一项。
[用户名]填入要导出到的Oracle用户名称，这里是scott
[密码]填入要导出到的Oracle用户使用的密码，这里是tiger
4.下一步，指定表复制或查询
如果要导出的内容是整个表，选择[从源数据库复制表和视图]
如果要导出的内容是表的一部分列，行，可以选择[用一条查询指定要传输的数据]，这时需要写出一个查询语句
这里选择第一项[从源数据库复制表和视图]
5.下一步，选择源表和视图
点中要导出的表Products一行的复选框，目的、转换两栏就会出现内容。
目的可以用来选择表名。转换可以用来修改列的数据类型或是整个建表的SQL语句
6.下一步，保存、调度和复制包
选中立即运行
7.下一步，正在完成DTS导入/导出向导
8.完成
提示“成功地将n个表从Microsoft SQL Server复制到了Oracle”
9.完成，关闭导入导出程序
注意:因为oracle的表名都是大写的，而sql的表可能是大小混写的，所以导入后在oracle里查不到该表，比如这个Products表在oracle里查询就应该写成select * from scott."Products";
导入就是使用工具import将文件中的对象和数据导入到数据库中，但是到如要使用的文件必须export所导出的文件，与导出相似，
导入也分导入表、导入方案、导入数据库三种方式。
--imp常用的选项有
--userid:用于指定执行导入操作的用户名、口令、连接字符串
--tables:用于指定执行导入操作的表
--formuser:用于指定源用户
--touser:用于指定目标用户
owner:用于指定执行导入操作的方案
full=y:用于指定执行导入整个文件
inctype:用于指定执行导入操作的增量类型
rows:用于指定是否要导入表中(行)的数据
file:用于指定导入文件名
ignore:如果表存在，则只导入数据
------导入表
(1)导入自己的表(表的结构和数据)(因为在导入emp表的时候，有emp和dept有主外键的关系(deotno)，就不能导入到其他的用户下面，但是如果没有主外键的话，可以导入到其他用户下面)
imp userid=scott/whx@oracl tables=(emp) file=d:\xx.dmp;
(2)导入表到其他用户
imp userid=system/ych@oracl tables=(emp) file=d:\xx.dmpm touser=scott;
(3)导入表的结构(表的定义)
之导入表的结构而不导入表数据
imp userid=scott/whx@oracl tables=(emp) file=d:\xx.dmp rows=n;
(4)导入数据(不管建表的问题)
如果对象已经存在可以直接导入表的数据
imp userid=scott/whx@oracl tables=(emp) file=d:\xx.dmp ignore=y;
------导入方案
导入方案是指使用import工具将文件中的对象和数据导入到一个或多个方案中如果要导入其他方案，要求用户具有dba的权限或是imp_full_database权限
(1)导入自己的方案
imp userid=scott/whx file=d:\xx.dmp;
(2)导入其他的方案
要求该用户具有dba权限
imp userid=system/ych file=d:\xx.dmp fromuser=system touser=scott;
-----------------第18讲Oracle------------------------------------------------------
----导入数据库
在默认默认情况下，导入数据库时，会导入所有对象结构和数据
imp userid=system/ych full=y file=d:\xxx.dmp;
数据字典和动态性能视图
数据字典是数据库中最重要的组成部分，它提供了数据库的一些系统信息
动态性能视图记载了例程启动后的相关信息
数据字典
数据字典记录了数据库的系统信息，它是只读表和视图的集合、数据字典的所有者位sys用户
用户只能在数据字典上执行查询操作(select)语句，而其维护和修改是由系统自动完成的
这里我们谈谈数据字典的组成
数据字典包括数据字典基表和数据字典动态视图，其中基表存储数据库的基本信息，
普通用户不能直接访问数据字典的基表，数据字典视图基于数据字典基表所建立的视图，
普通用户可以通过查询数据字典视图取得系统信息，
数据字典视图主要包括user_xxx,all_xxx,dba_xxx三种类型。xxx表示范围
user_tables:
用于显示当前用户所拥有的所有表，他只返回用户所对应方案的所有表
比如:select table_name from user_tables;
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
CLASSES
STUDENT
USERS
MYTABLE
KKK
9 rows selected
all_tables;
用于显示当前用户可以访问的所有表，它不仅会返回当前用户方案的所有表，还会返回当前用户可以访问的其他方案的表
比如:select table_name from all_tables;
SQL> show user;
User is "system"
SQL> select table_name from all_tables;
2530 rows selected
dba_tables;
他只显示所有方案拥有的数据库表，但是查询这种数据库字典视图，要求用户必须是dba角色或是有select any table系统权限。
例如:当用system用户查询数据字典视图dba.tables时，会返回system，sys，scott.....方案所对应的数据库表
SQL> select table_name from dba_tables;
---用户名、权限、角色
在建立用户的时候，oracle会把用户的信息存放在数据字典中，当给用户授予权限或是角色时候，oracle会将权限和角色的信息存放在数据字典中，
通过查询dba.users可以显示所有数据库用户的详细信息
sql>desc users;
sql>select username from dba_users;
sql>select username,password from dba_users;
通过查询数据字典视图dba.sys.privs，可以显示用户所具有的系统权限
通过查询数据字典视图dba.tab.prives,可以显示用户具有的对象权限
通过查询数据字典dba.col.prives,可以显示用户具有的列权限；
sql>desc dba_col_privs;
通过查询数据字典视图dba.role.prives，可以显示用户所具有的角色
sql>desc dba_role_privs;
select * from dba_role_privs where GRANTEE='SCOTT';
GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
------------------------------ ------------------------------ ------------ ------------
SCOTT RESOURCE NO YES
SCOTT CONNECT NO YES
//查询数据库的表空间
select tablespace_name from dba_tablespaces;
//查询oracle中所有的角色，一般是dba
select * from dba_roles;
也个角色可以包含一个或者多个
1.如何查询一个角色包含的权限?
a.一个角色包含多少系统权限
select * from dba_sys_privs where grantee='DBA';//DBA具有的角色
select * from dba_sys_privs where prantee='CONNECT';
另外也可以用这样查看
select * from role_sys_privs where role='DBA';
select * from role_sys_privs where role='CONNECT';
b.一个角色包含多少对象权限
select * from dba_tab_privs where grantee='角色名';
1.查看所有用户：
select * from dba_users;
select * from all_users;
select * from user_users;
2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs;
3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select * from role_sys_privs;
4.查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
5.查看所有角色：
select * from dba_roles;
6.查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;
7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select * from V$PWFILE_USERS
注意：
1、以下语句可以查看Oracle提供的系统权限
select name from sys.system_privilege_map
2、查看一个用户的所有系统权限(包含角色的系统权限)
sql>select privilege from dba_sys_privs where grantee='DATAUSER' union select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='DATAUSER' );
-------------第19讲oracle-------------------------------------------------------------------
2.oracle究竟有多少种角色?//查询oracle中所有对象权限，一般是dba
select * from dba_roles;
角色和权限的关系
要查看scott具有的角色，可以查询dba.role.prives;
//查询oracle中所有的系统权限，一般是dba
select * from system_privilege_map order by name;
206 rows selected
//查询oracle中所有的对象权限，一般是dba
select distinct privilege from dba_tab_privs;
18 rows selected
//查看某个用户具有怎样的角色
select * from dba_role_privs where grantee='用户名';
//当然某个角色具有什么样的系统权限和对象权限，也可以通过pl/sql developer工具直接查看即可。
3.如何查看用户具有什么样的角色?
select * from dba_role_privs where grantee='用户名';
select * from dba_role_privs where grantee='SCOTT';
select * from dba_role_privs where grantee='SYS';
显示当前用户可以访问的所有的数据字典视图
select * from dict where comments liek '%grant%';
显示当前数据库的全称
select * from global_name;
其他说明
数据字典记录有oracle数据库的所有系统信息，通过查询数据字典可以取得以下系统信息
比如:
(1)对象定义情况
(2)对象占用空间大小
(3)列信息
(4)约束信息
......
但是因为这些信息，可以通过pl/sql developer工具查询到
--动态性能视图用于记录当前例程的活动信息，当启动oracle server时，系统会建立动态性能视图，
--当停止oracle server时，系统会删除动态性能视图，oracle的所有动态性能视图都是以v_$开始，
--并且oracle为每一个动态性能视图提供了相应的同义词
，并且其同义词是以V$开始的，例如v_$datafile的同义词v$datafile；动态性能视图的所有者位sys,一般情况下，由dba或是特权用户来查询动态性能视图
-------------第20讲oracle-------------------------------------------------------
-----管理表空间和数据文件-----数据库的逻辑结构
--表空间是数据库的逻辑组成部分，从物理上讲，数据库数据存放在数据文件中，在逻辑上讲，数据库则是存放在表空间中，表空间是有一个或多个数据文件组成的
oracle中逻辑结构包括表空间、段、区和块;
说明一下数据库由表空间构成，而表空间又是有段构成，而段又是由区构成，而区又是由oracle块构成的这样一种构成，可以提高数据库的效率
----------表空间
表空间用于从逻辑上组织数据库的数据，数据库逻辑结构上是一个或是多个表空间组成的，通过表空间可以达到以下作用
(1)(减少)控制数据库占用的磁盘空间
(2)dba可以将不同的数据类型部署到不同的位置，这样有利于提高i/o性能，同事里与备份和恢复等管理操作
建立表空间
建立表空间是使用create tablespace命令完成的，需要注意是，一般情况下，建立表空间是特权用户或是dba来执行的，如果用其他用户来创建表空间，则该用户必须具有create tablespace的系统权限。
建立数据表空间
在建立数据库后，为了便于管理表，
最好建立自己的表空间create tablespace data01 datafile 'd:\test\data01.dbf' size 20m uniform size 128k;
说明:在执行完成上述命令后，会建立名称为data01表空间，并为该表空间建立名称为data01.dbf的数据文件，区的大小为128k
使用数据表空间---建表
create table mypart(deptno number(4),dname varchar2(14),loc varchar2(13)) tablespace data01;//此时创建的表存在于data01的表空间里面
create table mypart(deptno number(4),dname varchar2(14),loc varchar2(13));//此时创建的表存在于当前用户的表空间
改变表空间的状态
当建立表空间的时候，表空间处于联机(online)状态，此时该表空间是可以访问的，并且该表空间是可以读写的，即可以查询该表空间的数据，而且还可以在表空间执行各种语句。
--但是在进行系统维护或是数据维护时，可能需要改变表空间的状态，一边情况下，有特权用户或是dba来操作
(1)使表空间脱机(对系统维护时，让表空间不能用)
alter tablespace users offline;
alter tablespace 表空间名 offline;
(2)使表空间联机
alter tablespace users online;
alter tablespace 表空间名 offline;
(3)只读表空间
当建立表空间时，表空间可以读写，如果不希望在该表空间上执行update,delete,insert操作，
那么可以将表空间修改为只读
alter tablespace query_data read only;
alter tablespace 表空间名 read only;
修改回来
alter tablespace 表空间名 read write;
1).知道表空间名，显示该表空间包括的所有的表
select * from all_tables where tablespace_name='表空间';
s
2).知道表名，查看该表属于那个表空间
select tablespace_name,table_name from user_tables where table_name='emp';
select tablespace_name,table_name from user_tables where table_name='EMP';
select tablespace_name,table_name from user_tables where table_name='MYPART';
通过2)我们可以知道scott.emp是在system这个表空间上，现在我们可以将system修改为只读的但是我们不会成功，因为system是系统表空间，如果是普通表空间，那么我们就可以将其设置为只读。
删除表空间
一般情况下，有特权用户或是dba来操作，如果是其他用户操作，那么要求用户具有drop tablespace系统权限
drop tablespace '表空间' including contents amd datafiles;
drop tablespace '表空间'
drop tablespace '表空间' including contents;
drop tablespace '表空间' including datafile;
说明:including contents表示删除表空间时，删除该控件的所有的数据库对象，而datafiles表示将数据库文件也删除
扩展表空间
表空间是有数据文件组成的，表空间的大小实际上就是数据文件相加后的大小
，那么我们可以想象，假定表employee存放到data01表空间上，初始大小是2m，当数据满2m空间后，如果再向employee表插入数据，这样就会显示表空间不足的错误
案例说明
1.建立表空间 sp01;
create tablespace sp01 datafile 'd:\test\data01.dbf' size 2m uniform size 128k;
2.在该表空间上建立一个普通表myment其结构和dept一样;
create table mypart(deptno number(4),dname varchar2(14),loc varchar2(13)) tablespace data01;//此时创建的表存在于data01的表空间里面
3.向该表中加入数据
insert into mydment select * from dept;
4.当一定时候就会出现无法扩展的问题
5.开展该表空间，为其增加更多的存储空间，有三种方法
SQL> create tablespace sp01 datafile 'd:\test\data01.dbf' size 2m uniform size 128k;
create tablespace sp01 datafile 'd:\test\data01.dbf' size 2m uniform size 128k
ORA-01119: 创建数据库文件 'd:\test\data01.dbf'时出错
ORA-27040: 文件创建错误,无法创建文件
OSD-04002: 无法打开文件
O/S-Error: (OS 3) 系统找不到指定的路径。
开展该表空间，为其增加更多的存储空间，有三种方法
--1增加数据文件
sql>alter tablespace sp01 add datafile 'd:\test\sp01.dbf' size 20m;
--2增加数据文件的大小
sql>alter tablespace表空间名 'd:\test\sp01.dbf' size 20m;
这里需要注意的是数据文件的大小不要超过50m
--3设置文件的自动增长
sql>alter tablespace表空间名 'd:\test\sp01.dbf' autoextend on next 10m maxsize 500m;
--------移动数据文件
有些时候，如果你的数据文件所在的磁盘损坏时，该数据文件不能再使用了，为了能够重新使用，需要将这些文件的副本移动到其他的磁盘、然后再恢复
下面以移动文件sp01.dbf为列来说明
按以下步骤进行
---1)确定数据文件所在的表空
----select tablespace_name from dba_files where file_name='d:\test\sp01.dbf';
---2)使用表空间脱机
---确保数据文件的一致性，将表空间转变为offline的状态
---alter tablespace sp01 offline;
---3)使用命令移动数据文件到指定的目标位置
---sql>host move d:\test\sp01.dbf c:\test\sp01.dbf;
4)执行alter tablespace命令
在物理上移动了数据后，还必须执行alter tablespace命令对数据库文件进行逻辑修改
sql>alter tablespace sp01 rename datafile 'd:\test\sp01.dbf' to 'c:\test\sp01.db';
5)使表空间联机
在移动了数据文件之后，为了使用户可以访问该表空间，必须将其转变为online状态
sql>alter tablespace sp01 online;
sql>alter tablespace表空间名 online;
显示表空间信息
查询数据字典视图dba_tablespace,显示表空间的信息;
select tablespace_name from dba_tablespaces;
显示表空间所包含的数据文件
查询数据字典视图dba_data_files，可以显示表空间所包含数据文件
sql>select file_name.bytes from dba_data_files where tablespace_name='表空间名';
表空间小结
1)了解表空间和数据文件的作用
2)掌握常用表空间，undo表空间和临时表空间的建立
3)了解表空间的各种状态(online,offline,read write,read only)的作用，以及修改表空间的状态的方法
4)了解移动数据文件的原因--一个磁盘损坏或是部分损坏造成的，移植到另一个磁盘继续使用，以及alter tablespace和alter datatable命令移动数据文件的方法
其他表空间
除了最常用的数据表空间外，还有其他类型表空间
(1)索引表空间,
(2)undo表空间
(3)临时表空间
(4)非标准块的表空间
......
----------------第21讲oracle------------------------------------------------------------
----1.上节回顾
----2.维护数据的完整性
----3.管理索引
----4.管理权限和角色
目标
1.掌握维护oracle数据完整性的技巧
2.理解索引概念，会建立索引
3.管理oracle的权限和角色
数据的完整性用语却表数据库数据遵从一定的商业和逻辑规则，在oracle中，数据完整性可以使用约束、触发器、应用程序(过程、函数)三种方法来实现，在这三种方法中，因为约束以维护，并且具有最好的性能，所以作为维护数据完整性的首选
约束
约束用于确保数据满足特定的商业规则
在oracle中，约束包括以下五种：
--not null:(比如在emp表中的雇员的姓名)
如果在列上定义了not null，那么当插入数据的时候，必须为列提供的数据，否则加不进去
--unique:(比如订单号)
当定义为唯一约束后，该列值是不允许出现重复的，但是可以为null
--primary key:
用于唯一的标示表行的数据，当定义主键约束后，该列不但不允许出现重复的而且不能为空(null)
需要说明的是:一张表最多只能有一个主键，但是可以有多个unique约束
--foreign key:
用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必须具备主键约束或是unique约束，当定义外键约束以后，要求外键列数据必须在主表的主键列存在或是为null
--check:
用于强制行数据必须满足的条件，假定在sal列定义了check约束，并要求sal列值在1000-2000之间如果不在1000-2000之间就会提示错误
商店售系统表的设计案例
有三个表组成
商品goods表
(商品号goodId,商品名goodsName,单价unitprice,商品类型category,供应商privider);
客户表customer表
(客户号customerId,姓名name,地址address,电邮email,性别sex,身份证carId);
购买purchase(客户号customerId,商品号goodsId,购买数量nums);
请用SQL语言完成以下功能
1.建表
(1)每个表的主外键;
(2)客户的姓名不能为空值;
(3)单价必须大于0,构面数量必须在1到30之间;
(4)电邮不能重复
(5)客户的性别必须是男或者女,默认是男.
create table goods(goodsId char(8) primary key,goodsName varchar2(20),
unitprice number(10,2) check(unitprice>0),
category varchar2(8),
privider varchar2(30));
create table customer(customerId char(8) primary key,
name varchar2(30) not null,
address varchar2(50),
email varchar2(50) unique,
sex char(2) default '男' check(sex in('男','女')));
create table purchase(customerId char(8) references customer(customerId),
goodsId char(8) references goods(goodsId),
nums number(10) check(nums between 1 and 30));
Table created
SQL> desc purchase;
Name Type Nullable Default Comments
---------- ---------- -------- ------- -------- ------------------------
CUSTOMERID CHAR(8) Y
GOODSID CHAR(8) Y
NUMS NUMBER(10) Y
SQL> desc desc customer;
Object desc customer does not exist.
SQL> desc customer;
Name Type Nullable Default Comments
---------- ------------ -------- ------- -------- -------------------------
CUSTOMERID CHAR(8)
NAME VARCHAR2(30)
ADDRESS VARCHAR2(50) Y
EMAIL VARCHAR2(50) Y
SEX CHAR(2) Y '男'
SQL> desc goods;
Name Type Nullable Default Comments
--------- ------------ -------- ------- -------- ----------------------
GOODSID CHAR(8)
GOODSNAME VARCHAR2(20) Y
UNITPRICE NUMBER(10,2) Y
CATEGORY VARCHAR2(8) Y
PRIVIDER VARCHAR2(30) Y
如果再见表的时候忘记了建立必要的约束,则可以再见标识后使用alter table命令为表增加约束，但是要注意,增加not null约束时，需要使用modify选项，而增加其他四种约束使用add选项。
(1)每个表的主外键;
(2)客户的姓名不能为空值;--增加商品也不能为null
alter table goods modify goodsName not null;
(3)单价必须大于0,构面数量必须在1到30之间;
(4)电邮不能重复---增加身份证也不重复
sql> alter table customer add constraint carunique unique(carId);
sql> alter table customer add constraint '任意字符串' unique(carId);
(5)客户的性别必须是男或者女,默认是男.
(6)增加客户的主治只能为'海淀'，'朝阳'，'东城'，'西城'，'通川'，'荣文';
alter table customer add constraint addresscheck check(address in('海淀','朝阳','东城','西城','通川','荣文'));
删除约束
当不需要约束的时候，可以删除
alter table 表名 drop constraint约束名称;
特别说明一下
在删除主键约束的时候，可能会出现错误，比如:
alter table 表名 drop primary key;
alter table goods drop primary key;
这是因为如果在两张表存在主从关系，那么在删除主表的主键约束时，必15:31 2012-12-25须带上cascade选项
alter table 表名 drop primary key cascade;
alter table goods drop primary key cascade;
显示约束信息
1.显示约束信息
通过查询数据字典视图user_constraints可以显示当前用户所有的约束信息
select constraint_name,constraint_type,status,validated from user_constraints where table_name='表名';
2).显示约束列
通过查询数据字典视图user_cons_columns,可以显示约束所对应的表列的信。
select column_name,position from user_cons_columns where constraint_name='约束名';
3).当然也有更容易的方法，直接用pl/sql developer查看即可。
列级定义
列级定义是在定义列的同时定义约束
如在department表定义主键约束
create table department(dept_id number(4) constraint pk_department primary key,name varchar2(12),loc varchar2(12)):
create table department(dept_id number(4) primary key,name varchar2(12),loc varchar2(12)):
列级定义与表级定义的唯一区别是:如果不加constraint pk_department，则主键的名字就会使其他的
表级定义
表级定义是指在定义了所有列后，再定义约束，这里需要注意:
not nul约束只能在列级上定义
以在建立enployee表示定义主键约束和外键约束为例:
create table employee(emp_id number(4) ,name varchar2(12),dept_id12 varchar2(12)
,constraint pk_employee primary key (emp_id),
constraint pk_department froeign key(dept_id12)
,references department(dept_id)):
------------------第22讲oracle-------------------------------------------------------------------
管理索引
索引是用于加速数据存取的数据对象，合理的使用索引可以大大降低i/o次数,从而提高数据访问性能，索引有很多种我们主要介绍常用的几种
为什么添加索引以后，我们查询数据会变快
如图书馆书籍
需要管理和维护索引(原则:)建立合理的索引
管理索引----------创建索引
1.单列索引
单列索引是基于单个列所建立的索引，比如:
create index 索引名 on 表名(列名)
SQL> create index nameindex on customer(name);
Index created
SQL> create index idx1 on emp(ename);
Index created
create index idx1 on emp(job);
2.复合索引
复合索引是基于两列或是多列的索引，在同一张表上可以有多个索引，但是要求的组合必须不同，比如:
create index emp.idex1 on emp(ename,job);
create index emp.idex1 on emp(job,ename);
select * from customer where name='sp' and carid='aaa';//把能够筛选很多的条件放在后面
建立索引的使用的原则
(1)在大表上建立索引才有意义
(2)在where子句或是连接条件上经常饮用的列上建立索引
(3)索引的层次不要超过4层
索引缺点分析
索引有些先天不足
1:建立索引、系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引，
2:更新数据的时候，系统必须要额外的时间来同时对索引进行更新，以维持数据和索引的一致性
实践证明，不恰当的索引不但于事无补，反而会降低系统的性能，因为大量的索引在进行插入的时候，修改和删除操作时比没有索引花费更多的系统时间
比如在如下字段建立索引应该是不恰当的
1.很少或是从来不引用的字段
2.逻辑型的字段，如男或女(是与否)等，综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目建立。
管理索引-------其他索引
按照数据存储方式，可以分为B+树、反向索引、位图索引;
按照索引列的个数分类，可以分为单列索引、复合索引;
按照索引列值的唯一性，可以分为唯一索引和非唯一索引;
此外还有函数索引、全局索引、分区索引.......
对于索引
在不同的情况下我们会在不同的列上建立索引，甚至建立不同种类的索引
B+树索引建立在重复值很少的列的情况下，而位图索引则建立在重复值很多、不同值相对固定的列上
显示表的所有索引
在同一张表上可以有多个索引，通过查询数据字典视图dba_indexes和user_indexes,可以显示索引信息，其中dba_indexs用于显示数据库所有的索引信息，而user_indexs用于显示当前用户的索引信息:
sql>select index_name,index_type from user_indexes where table_name='表名';
sql>select index_name,index_type from dba_indexes where table_name='scott.customer';
sql>select index_name,index_type from user_indexes where table_name='scott.customer';
显示索引列
通过查询数据字典视图user_ind_columns,可以显示索引对应的列的信息
sql>select table_name,column_name from user_ind_columns where index_name='IND_ENAME';
我们也可以通过pl/sql developer工具查看索引
---------------------第23讲oracle------------------------------------------------
------------管理权限和角色
当新建用户时，用户没有任何权限，也不能执行任何操作，如果要执行某种特定的数据库操作，则有必要为其授予系统权限;
如果用户要访问其他方案的对象数据的时候，则必须为其授予对象权限，为了简化权限的管理，可以使用角色。
--查看当前用户的缺省表空间
SQL>select username,default_tablespacefrom user_users;
---查看当前用户的角色
SQL>select*from user_role_privs;
---查看当前用户的系统权限和表级权限
SQL>select*from user_sys_privs;
SQL>select*from user_tab_privs;
---查看用户下所有的表
SQL>select*from user_tables;
--1、用户
----查看当前用户的缺省表空间
SQL>select username,default_tablespacefrom user_users;
----查看当前用户的角色
SQL>select*from user_role_privs;
----查看当前用户的系统权限和表级权限
SQL>select*from user_sys_privs;
SQL>select*from user_tab_privs;
----显示当前会话所具有的权限
SQL>select*from session_privs;
----显示指定用户所具有的系统权限
SQL>select*from dba_sys_privs where grantee='GAME';
--2、表
----查看用户下所有的表
SQL>select*from user_tables;
----查看名称包含log字符的表
SQL>select object_name,object_idfrom user_objects
whereinstr(object_name,'LOG')>0;
----查看某表的创建时间
SQL>select object_name,createdfrom user_objectswhere object_name
=upper('&table_name');
----查看某表的大小
SQL>selectsum(bytes)/(1024*1024)as "size(M)" from user_segments
where segment_name=upper('&table_name');
----查看放在ORACLE的内存区里的表
SQL>select table_name,cachefrom user_tableswhereinstr(cache,'Y')>0;
--3、索引
----查看索引个数和类别
SQL>select index_name,index_type,table_namefrom user_indexesorder
by table_name;
----查看索引被索引的字段
SQL>select*from user_ind_columns where index_name=
upper('&index_name');
----查看索引的大小
SQL>selectsum(bytes)/(1024*1024)as "size(M)" from user_segments
where segment_name=upper('&index_name');
--4、序列号
查看序列号，last_number是当前值
SQL>select*from user_sequences;
5、视图
----查看视图的名称
SQL>select view_name from user_views;
----查看创建视图的select语句
SQL>set view_name,text_lengthfrom user_views;
SQL>setlong2000;说明：可以根据视图的text_length值设定setlong的大小
SQL>select textfrom user_viewswhere view_name=
upper('&view_name');
--6、同义词
----查看同义词的名称
SQL>select*from user_synonyms;
--7、约束条件
----查看某表的约束条件
SQL>selectconstraint_name, constraint_type,search_condition,
r_constraint_namefrom user_constraintswhere table_name
=upper('&table_name');
SQL>select c.constraint_name,c.constraint_type,cc.column_namefrom
user_constraints c,user_cons_columns cc
where c.owner=upper('&table_owner')and c.table_name
=upper('&table_name')
and c.owner= cc.ownerand c.constraint_name= cc.constraint_name
orderby cc.position;
--8、存储函数和过程
----查看函数和过程以及包的状态
SQL>select object_name,statusfrom user_objectswhere
object_type='FUNCTION';
SQL>select object_name,statusfrom user_objectswhere
object_type='PROCEDURE';
SQL>select object_name,statusfrom user_objectswhere
object_type='PACKAGE';
----查看函数和过程以及包的源代码
SQL>select textfrom all_sourcewhere owner=userand
name=upper('&plsql_name');
系统权限
a.什么是系统权限
b.系统权限有哪些
查看权限
sql>select * from system_privilege_map order by name;
c.怎样给用户赋给系统权限
对象权限
a.什么是对象权限
访问别的用户的对象数据的能力
b.对象权限有哪些
c.怎样给用户赋给对象权限
方案
当创建用户的时候，就会创建方案--包括数据对象，表，视图，触发器等
方案与系统权限和对象权限无关
角色
为了简化权限的管理，可以使用角色，使得一个角色包含多种系统权限和对象权限
查看角色
sql>select * from dba_roles;
权限
权限是指执行特定类型sql命令或是访问其他方案对象的权利，
包括系统权限和对象权限两种:
系统权限
系统权限是指执行特定类型sql命令的权利，它用于控制用户可以执行的一个或是一组数据库操作
比如当用户具有create table权限时，可以在其他方案中建表，当用户具有create any table权限时，可以在任何方案中建立表，oracle提供了100多种系统权限
常用的有:
create session 连接数据库
create table 建表
create view 建立视图
create public synomym建立同义词
create procedure 建立过程、函数、包
create trigger 建立触发器
create cluster
显示系统权限
oracle提供了100多种的系统权限，而且oracle的版本越高，提供系统权限就越多，我们可以查询数据字典视图system_privilege_map
sql>select * from system_privilege_map order by name;
授予权限
一般情况下，授予系统权限是有dba完成的，如果其他用户来授予系统权限，则要求该用户必须具有grant any privilege的系统权限在授予权限时，可以带有with admin option选项，这样被授予权限用户或是角色还可以将该系统权限授予其他的用户或是角色
1.创建两个用户ken,tom
sql>create user ken identified by ken
2.给用户ken授权
sql>grant create session,create table to ken with admin option;
sql>grant create view to ken
3.给用户tom授权
回收系统权限
一般情况下，回收系统权限时有dba来完成的，如果其他的用户来回收系统权限，则要求该用户必须具有相应的系统权限及转授权限的选项(with admin option) ,回收系统权限使用rvoke来完成
当回收了系统权限后，用户就不能执行相应的操作了，但需要注意系统权限级联回收问题
不是级联回收
----------system-------------->ken------------>tom
当system执行如下操作
sql>revoke create session from ken;请问tom还能登陆吗?
tom还能登陆
对象权限
指访问其他方案对象的权利，用户可以直接访问自己的方案的对象，但是如果要访问其他的方案的对象，则必须具有对象权限
比如smith用户要访问scoot.emp表(scoot:方案,emp:表)
则必须在scott.emp表上具有对象权限
常用的有
alter 修改
delete 删除
select 查询
insert 插入
update 更新
index 索引
references 引用
execute 执行
显示对象权限
通过数据字典视图可以显示用户或是角色所具有的对象权限，视图为dba_tab_privs
sql>conn systemych;
sql>select distinct privilege from dba_tab_privs;
sql>select grantor.owner,table_name,privilege from dba_tab_privs where grantee='BLAKC';
授予对象权限
在oracle9i之前，授予对象权限是有对象的所有者来完成的，如果用其他的用户来操作，则需要该用户具有相应的(with grant option)权限,从oracle9i开始。dba用户(sys,system)可以将任何对象上的对象权限授予其他用户，授予对象权限用grant命令来完成的。
对象权限可以授予用户、角色、和public，在授予权限的时候，如果带有with grant option选项，则可以将该权限转授给其它用户，但是需要注意，with grant option选项不能被授予角色。
sql>create user monkey identified by m123;
1.monkey用户要操作scott.emp表，则必须收于相应的对应权限
1.希望monkey可以查询scott.emp的数据
sql>grant select on emp to monkey;
sql>select * from scott.emp;
2.希望monkey可以修改scott.emp的数据
sql>grant update on emp to monkey;
3.希望monkey可以删除scott.emp的数据
sql>grant delete on emp to monkey;
4.没有更简单的方法吗?一次性把所有的权限给monkey?
sql>grant all on emp to monkey;
5.能否对monkey访问权限更加精细控制呢？
1.希望monkey只可以修改scott.emp的表中的sal字段
sql>grant update on emp (sal) to monkey;
2.希望monkey只可以查询scott.emp的表中的enamel,sal字段
sql>grant select on emp (ename,sal) to monkey;
6.授予alter权限
如果black用户修还scott.emp表的结构，则必须需要授予alter对象权限
sql>conn scott/whx;
sql>grant alter on emp to black;
当然也可以用system,sys来完成这件事
7.授予execute权限
如果用户想要执行其他方案的包/过程/函数,则需要有execute权限
比如为了让ken可以执行包dbms_transaction,可以授予execute权限
sql>conn system/ych;
sql>grant execute on dbms_transaction to ken;
8.授予index权限
如果想在别的方案的表上建立索引，则必须要具有index权限
sql>conn scott/whx;
sql>grant index on scott.emp to black;
9.使用with grant option选项
该选项用于转授对象权限，但是该选项只能被授予用户，而不能授予角色
sql>conn scott/whx;
sql>grant select on emp to black with grant option;
sql>conn black/whx;
sql>grant select on scott.emp to jones;
回收对象的权限
在oracle9i中，回收对象的权限可以有对象的所有者来完成，也可以用dba用户(sys,system)来完成
当回收了对象权限后，用户就不能执行相应的操作了，但需要注意对象权限级联回收问题
是级联回收(这与系统权限级联回收问题有很大的区别)
----------scott-------------->black------------>jones;
当scott执行如下操作
sql>conn scott/whx;
sql>revoke select on emp from black;请问jones还能登陆吗?
jones不能查询scott.emp表的数据
角色
角色就是相关权限的命令集合，使用角色的主要目的是为了简化权限管理，假定有用户a,b,c为了让他们都拥有权限
1.连接数据库
2.在scott.emp表上select,insert,update
如果采用直接授权操作，则需要进行12次授权
首先将create sesion ,select on scott.emp,insert on scott.emp,update on scott.emp授予角色，然后将该角色授予a,b,c用户，这样就可以三次授权搞定
角色分为预定义和自定义角色两类
预定义角色
预定义角色是指oracle所提供的角色，每种角色都用于执行一些特定的管理任务
预定义角色connect角色
connect角色具有一般应用开发人员需要的大部分权限，当建立了一个用户后，多数情况下，只要给用户授予connect和resource角色就足够了，那connect角色具有的系统权限有以下几种
alter session;
creaet cluster;
create database link;
create sesssion;
create table;
create view;
create sequence;
resource角色具有应用开发人员所需要的其他权限，比如建立存储过程、触发器等，这里需要注意的是resource角色隐含了unlimited tablespace系统权限
resource角色包含以下系统权限
create cluster;
create indextype;
create table;
create sequence;
create type;
create procedure;
create trigger;
dba角色
dba角色具有所有的系统权限，及with admin option选项，默认的dba用户为sys和system，他们可以将任何系统权限授予其他用户，但是要注意的是dba角色不具备sysdba和sysoper的特权(启动和关闭数据库)
自定义角色
顾名思义就是自己定义角色，根据自己的需要来定义，一般是dba来建立，如果用的别的用户来建立，则需要具有create role的系统权限，在建立角色的时候，可以指定验证方式(不验证，数据库验证等)
(1)建立角色(不验证)
如果角色是公用的角色，可以采用不验证的方式建立角色
sql>create role 角色名 not identified;
sql>create role myrole not identified;
(2)建立角色(数据可验证)
采用这样的方式，角色名，口令存放在数据库中，当激活该角色的时候，必须提供口令，建立这种角色时，需要为其提供口令
sql>create role 角色名 identified by m123;
角色授权
当建立角色时，角色没有任何权限，为了使的角色完成特定的任务，必须为其授予相应的系统权限和对象权限。
(1)给角色授权
给角色授予权限和给用户授予权限没有多大的区别，但是需要注意的是，系统权限的unlimited tablespace和对象权限的with grant option选项是不能授予角色的;
通过以下步骤就可以完成了
sql>conn system/ych;
sql>grant create session to角色名 woth admin option;
sql>conn scott/whx;
sql>grant select on scott.emp to角色名//用system用户grant select on emp to角色名
sql>grant select,insert,update,detele on emp to角色名
sql>conn system/ych;
sql>grant create session to myrole woth admin option;
sql>conn scott/whx;
sql>grant select on emp to myrole//用system用户grant select on scott.emp to myrole;
sql>grant select,insert,update,detele on emp to myrole;
分配角色给某个用户
一般分配角色是由dba来完成的，如果要以其他用户身份分配角色，则要求用户必须具有grant any role的系统权限
sql>conn system/ych;
sql>grant 角色名 to black with admin option;
sql>conn system/ych;
sql>grant myrole to black with admin option;
因为我给了with admin option选项，所以black可以把system分配给其它角色分配给别的用户;
删除角色
使用drop role ,一般是dba来执行，如用其他用户删除角色，则必须该用户具有drop any role的系统权限
sql>conn system/ych;
sql>drop role 角色名;
sql>conn system/ych;
sql>drop role myrole;
如果角色被删除，那么black是否还能登陆?
1.删除myrole角色
drop role myrole;
black不能登录了。
显示角色信息
(1)显示所有角色
sql>sql>conn system/ych;
sql>drop role 角色名;
(2)显示角色具有的系统权限
sql>select privilege,admin_option from role_sys_privs where role='用户名';
(3)显示角色具有的对象权限
通过查询数据字典视图dba_tab_privs可以查看角色具有对象权限或是列的权限。
(4)显示用户具有的角色，以默认角色
当以用户的身份连接到数据库，oracle会自动的激活默认的角色，通过查询数据字典视图dba_role_privs可以显示某个用户具有的所有角色及当前默认角色
sql>select granted_role,default_role from dba_role_privs where grantee='角色名';
sql>select granted_role,defaul_role from dba_role_privs where grantee='SYSTEM';
sql>select granted_role,defaul_role from dba_role_privs where grantee='SYS';
sql>select granted_role,defaul_role from dba_role_privs where grantee='SCOTT';
精细访问控制
是指用会使用函数、策略实现更加细微的安全访问控制，
--如果使用精细访问控制，则当客服端发出sql语句(select,insert,update,delete)时，
--oracle会自动在sql语句后面加上谓词(where子句),并且执行新的sql语句，
--通过这样的控制，可以使得不同的数据库用户在访问相同表时，返回不同的数据信息，
----------第6天
----------第24讲oracle---------------------------------------------------------------------
---内容介绍
1.上节回顾
2.pl/sql的介绍
3.pl/sql的基础
---期望目标
1.理解oracle的pl/sql概念
2.掌握oracle编程技术(包括编写过程、函数、包、触发器.....)
pl/sql的介绍
pl/sql(procedural language/sql)是oracle在标准的sql语言上扩展，pl/sql不仅允许嵌入sql语句，还可定义变量和常量，允许使用条件语句和循环语句，允许使用例处处理各种错误，这样使得它的功能变得更加强大。
1.过程、函数、触发器是pl/sql编写的
2.过程、函数、触发器是在oracle中
3.pl/sql是非常强大的数据库过程语言
4.过程、函数可以再java程序中调用
学习pl/sql的必要性------------为什么要学
1.提高应用程序的运行性能[分页的过程、转账的过程、订单的过程......]，减少项目人员的管理
2.模块化的设计思想
3.减少网络传输量
4.提高安全性
缺点
一个技术不是完美的
1.移植性不好(比如oracle----->SQL server等)
sqlplus开发工具
两种工具
1.sqlplus是oracle公司提供的一种工具，这是因为我们在以前介绍过的
举例
编写一个过程，该过程可以向某个表添加记录
创建一张简单表
create table mytest(name varchar2(30),passwd varchar2(30));
2.创建过程
create procedure sp_prol is begin
--执行部分
insert into mytest('王红祥','m123');
/
SQL> create table mytest(name varchar2(30),passwd varchar2(30));
Table created
SQL> create procedure sp_prol is begin insert into mytest('王红祥','m123');end;
2 /
Warning: Procedure created with compilation errors
SQL> create or replaceprocedure sp_prol is begin insert into mytest('王红祥','m123');end;
create or replaceprocedure sp_prol is begin insert into mytest('王红祥','m123')
ORA-00905: 缺失关键字
SQL> create or replace procedure sp_prol is begin insert into mytest('王红祥','m123');end;
2 /
Warning: Procedure created with compilation errors
create procedure sp_prol is begin insert into mytest('王红祥','m123');end;
如何查看错误
show error;
SQL> show error;
Errors for PROCEDURE SCOTT.SP_PROL:
LINE/COL ERROR
-------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1/47 PL/SQL: ORA-00928:缺失 SELECT 关键字
1/28 PL/SQL: SQL Statement ignored
3/0 PLS-00103: 出现符号 "end-of-file"在需要下列之一时： ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge
SQL> create or replace procedure sp_prol is begin insert into mytest values('王红祥','m123');
2 /
Warning: Procedure created with compilation errors
SQL> create or replace procedure sp_prol is begin insert into mytest values('王红祥','m123') end;
2 /
Warning: Procedure created with compilation errors
SQL> create or replace procedure sp_prol is begin insert into mytest values('王红祥','m123');end;
2 /
Procedure created
3).如何调用该过程
有两种方式
--------第一种
exec 过程名(参数值1,参数2,....)
exec sp_prol;
--------第二种
call 过程名(参数值1,参数2,....)
call sp_prol;
SQL> exec sp_prol;
PL/SQL procedure successfully completed
SQL> select * from mytest;
NAME PASSWD
------------------------------ ------------------------------
王红祥 m123
SQL> exec sp_prol;
PL/SQL procedure successfully completed
SQL> select * from mytest;
NAME PASSWD
------------------------------ ------------------------------
王红祥 m123
王红祥 m123
2.pl/sql developer开发工具
pl/sql developer开发工具
pl/sql developer是用于开发pl/sql块的集成开发环境(ide)，它是一个独立的产品，而不是oracle的一部分。
举例
编写一个过程，该过程可以向某个表删除记录举例
-案例
create or replace procedure sp_pro2 is begin
--执行部分
delete from mytest where name='王红祥';
end;
SQL>
SQL> create or replace procedure sp_pro2 is begin
2 --执行部分
3 delete from mytest where name='王红祥';
4 end;
5 /
Procedure created
SQL> exec sp-pro2;
begin sp-pro2; end;
ORA-06550: 第 2行, 第 9列:
PLS-00103: 出现符号 "-"在需要下列之一时：
:= . ( @ % ;
符号 ":="被替换为 "-" 后继续。
SQL> exec sp_pro2;
PL/SQL procedure successfully completed
SQL> select * from mytest;
NAME PASSWD
------------------------------ ------------------------------
---------pl/sql基础
开发人员使用pl/sql编写应用模块时，不仅要求掌握sql语句的编写方法，还要掌握pl/sql语句及语法规则。pl/sql变成可以使用变量和逻辑控制语句，从而可以编写非常有用的功能模块，
比如:分页存储过程模块、订单处理过程模块、转账存储过程模块......。
而且如果使用pl/sql编程，我们可以轻松的完成非常复杂的查询要求
简单分类
|-----------过程(存储过程)
|
|
|-----------函数
----块(编程)------- |
|
|-----------触发器
|
|-----------包
pl/sql基础-----------------编写规范
编写规范
1).注释
单行注释 --
select * from emp where empno=7788;--取得员工信息
多行注释
/*..........*/来划分
2).表示符号的命名规范
--1).当定义变量时，建议用v_作为前缀 v_sal;
--2).当定义常量时，建议用c_作为前缀 c_rate;
--3).当定义游标时，建议用_cursor作为后缀 emp_cursor;
--4).当定义例外时，建议用e_作为前缀e_error;
pl/sql基础------pl/sql块介绍
块(block)是pl/sql的基本程序单元，编写pl/sql程序实际上就是pl/sql块。要完成相对简单的应用功能，可能只需要编写一个pl/sql块;但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其他的pl/sql块
块结构示意图
pl/sql块由三部分构成:定义部分、执行部分、例外处理。
如下所示
declear
/*定义部分---------------定义常量、变量、游标、例外、复杂数据类型*/
begin
/*执行部分---------------要执行的pl/sql语句和sql语句*/
exception
/*例外处理部分-----------处理运行的各种错误*/
end;
特别说明
定义部分是从declear开始的
该部分可选的
执行部分是从begin开始的
该部分是必须有的
例外处理部分是从exception开始的
该部分是可以选的
可以和java编成结构做一个简单的比较
----------第25讲oracle---------------------------------------------------------------------------
java程序
public static void main(String [] args)
{
//定义变量
int a=1;
try{
a++;
}catch(Exception e)
{
//得到异常
}
}
set serveroutput off --关闭输出选项
pl/sql基础--------pl/sql块的实例(1)
set serveroutput on --打开输出选项
begin
dbms_output.put_line('hello');
SQL> begin
2 dbms_output.put_line('hello');
3 end;
4 /
PL/SQL procedure successfully completed
SQL> set serveroutput on;
SQL>
SQL> begin
2 dbms_output.put_line('hello');
3 end;
4 /
hello
PL/SQL procedure successfully completed
pl/sql基础--------pl/sql块的实例(2)
declare
v_ename varchar2(5);--定义字符串变量
begin
select ename into v_ename from emp where empno=&no;
dbms_output.put_line('雇员名:'||v_ename);---||是连接符
end;
/
相关说明:
&表示要接受从控制台的变量
SQL> declare
2 --定义变量
3 v_ename varchar2(5);
4 begin
5 --执行部分
6 select ename into v_ename from emp where empno=&aa;
7 --在控制台显示用户名
8 dbms_output.put_line('用户名:'||v_ename);
9 end;
10 /
用户名:SCOTT
PL/SQL procedure successfully completed
--有定义和执行部分的块
--把用户的工资也显示出来
declare
--定义变量
v_ename varchar2(5);
v_sal number(7,2);
begin
--执行部分
select ename into v_ename from emp where empno=&aa;
select sal into v_sal from emp where emp empno=&aa;
--在控制台显示用户名
dbms_output.put_line('用户名:'||v_ename);
end;
--有定义和执行部分的块
--把用户的工资也显示出来
declare
--定义变量
v_ename varchar2(5);
v_sal number(7,2);
begin
--执行部分
select ename,sal into v_ename,v_sal from emp where empno=&aa;
--在控制台显示用户名
dbms_output.put_line('用户名:'||v_ename);
dbms_output.put_line('薪金:'||v_sal);
end;
declare
--定义变量
v_ename varchar2(5);
v_sal number(7,2);
begin
--执行部分
select ename,sal into v_ename,v_sal from emp where empno=&aa;
--在控制台显示用户名
dbms_output.put_line('用户名:'||v_ename);
dbms_output.put_line('薪金:'||v_sal);
end;
declare
--定义变量
v_ename varchar2(5);
v_sal number(7,2);
begin
--执行部分
select ename,sal into v_ename,v_sal from emp where empno=&aa;
--在控制台显示用户名
dbms_output.put_line('用户名:'||v_ename||'薪金:'||v_sal);
end;
pl/sql基础--------pl/sql块的实例(3)
为了避免pl/sql程序运行的错误，提高pl/sql的使用健壮性，应该对可能的错误进行处理，这个很有必要;
(1)比如在实例2中，如果输入了不存在的雇员号，应该当做例外处理。
(2)有时出现异常，希望用另外的逻辑处理
相关说明:oracle事先预定义了一些例外,no_data_found就是找不到数据的例外
SQL> declare
2 --定义变量
3 v_ename varchar2(5);
4 v_sal number(7,2);
5 begin
6 --执行部分
7 select ename,sal into v_ename,v_sal from emp where empno=&aa;
8 --在控制台显示
9 dbms_output.put_line('用户名:'||v_ename||'工资'||v_sal);
10 --异常处理
11 exception
12 when no_data_found then
13 dbms_output.put_line('出现错误，请重新输入');
14 end;
15 /
用户名:SCOTT工资700
PL/SQL procedure successfully completed
过程(存储过程)
过程用于执行特定的操作，当建立过程时，既可以执行输入参数(in)，也可以指定输出参数(out),通过在过程中使用输入参数，可以将执行部分的数据传递到应用环境，在sqlplus中可以使用create procedure命令来建立过程。
(1)请考虑编写一个过程，可以输入雇员名、新工资，可以修改雇员的工资
create procedure sp_pro3(spName varchar2,newSal number) is
bgein
--执行部分
update emp set sal=newSal where ename=spName;
end;
/
--演示4
create procedure sp_pro4(spName varchar2,neSal number) is
begin
--执行部分---根据用户名修改工资
update emp set sal=newSal where ename spName;
end;
(2)如何调用过程有两种方法;
exec sp_pro3('SCOTT',65544);
call sp_pro3('SCOTT',65544);
SQL> exec sp_pro3('SCOTT',65544);
PL/SQL procedure successfully completed
SQL> select * from emp;
SQL> call sp_pro3('SCOTT',65544);
Method called
(3)如何在java/jsp程序中调用一个存储过程呢?
package com;
import java.sql.*;
//演示java程序调用oracle的存储过程
public class TestOracle {
public static void main(String[] args) {
//1.加载驱动
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
//2.得到连接
Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORACL","SCOTT","whx");
//创建一个CallableStatement
CallableStatement cs=ct.prepareCall("sp_pro3(?,?)");
//4.给问号赋值
cs.setString(1, "SMTIH");
cs.setInt(2, 10);
//5.执行
cs.execute();
//6.关闭资源
ct.close();
cs.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
(4)如何使用过程返回值
--------------------第26讲oracle----------------------------------------------
---函数
函数用于返回特定的数据，当建立函数时，
在函数头部必须包含return语句，
而在函数体内必须包含return语句返回的数据，
我们可以使用create function来建立函数
create function sp_fun2(spName varchar2) return number is
yearSal number(7,2);
begin
--执行部分
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;
/
在sqlplus中调用函数
sql>var income number;
sql>call annual_income('SCOTT') into:income;
sql>print income;
同样我们可以在java程序中调用该函数
select annual_income('SCOTT') from dual;//这样可以通过rs.getInt(1)得到返回的结果
SQL> create function sp_fun5(spName varchar2) return number is
2 yearSal number(7,2);
3 begin
4 --执行部分
5 select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
6 return yearSal;
7 end;
8 /
Function created
SQL> var abc number;
SQL> call sp_fun5('SCOTT') into:abc;
包
包用于在逻辑上组合过程和函数，它是由包规范和包体两部分组成的
(1)我们可以使用create package命令来创建
实例
create package sp_package is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end;
报的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码，包体用于实现报的规范中的过程和函数
(2)建立包体可以使用create package body命令
create or replace package body sp_package is
procedure update_sal(name varchar2,newsal number) is
begin
update emp set sal=newsal where ename=name;
end;
function annual_income(name varchar2) return number is annual_salary number;
begin
select sal*12+nvl(comm,0) into annual_salary from where ename=name;
return annual_salary;
end;
end;
(3)如何调用包的过程或是函数
当调用包的过程或是函数时，在过程和函数前需要带上包名，如果要访问其它方案的包，还需要在包名前加上方案名.
如:
sql>call sp_package.update_sal('SCOTT',1500);
特别说明:
包是pl/sql中非常重要的组成部分，我们在使用过程分页的时候，将会再次体验它的威力
触发器(用户登录等.....)
触发器是指隐含的执行的存储过程，当定义触发器时，必须指定触发的事件和触发的操作，常用的触发事件包括insert,update,delte语句，而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。
因为出发其实非常有用的，可维护数据库的安全和一致性
pl/sql基础--定义并使用变量
在编写pl/sql程序时，可以定义变量和常量;在pl/sql程序中包括有:
(1)标量类型(scalar)
(2)复合类型(composite)
(3)参照类型(referenece)
(4)lob(large object)
(1)标量类型(scalar)---常用类型
在编写pl/sql块时，如果要使用变量，需在定义部分定义变量.pl/sql中的定义变量和常量的语法如下:
identifier [constant] datatype [not null] [:=| default expr]
idenifier:名称
constantL:指定常量.需要指定它的初始值,并且其值是不能改变的
datatype:数据类型
not null:指定变量值不能为空(null)
:= 给变量或是常量指定初始值
default 用于指定初始值
expr:指定初始值的pl/sql表达式，可以是文本值、其他变量、函数等。
标量定义的案例
(1)定义一个变长字符串
v_ename varchar2(10);
(2)定义一个小数，范围-9999.99~9999.99
v_sal number(6,2);
(3)定义一个小数并给一个初始值为5.4:=是pl/sql的赋值号
v_sal2 number(6,2):=5.4;
(4)定义一个日期类型的数据
v_hiredate date;
(5)定义一个布尔变量，不能为空，初始值为false;
v_valid boolean not null default false;
(6)
标量(scalar)---使用标量
在定义好变量后，就可以使用这些变量，这里需要说明的是pl/sql块为变量赋值不同于其他的编程语言，需要在等号前面加上冒号(:=)
案例
下面已输入员工号，显示员工姓名、工资、个人所得税(税率为0.03)为例,说明变量的使用
declare
c_tax_rate number(3,2):=0.03;
--用户名,工资，税收
v_ename varchar2(5);
v_sal number(7,2);
v_tax_sal number(7,2);
begin
--执行部分
select ename,sal into v_ename,v_sal from emp where empno=&em;
---计算所得税
v_tax_sal:=v_sal*c_tax_rate;
--输出
dbms_output.put_line('姓名是:'||v_ename||'工资'||v_sal||'所得税'||v_tax_sal);
end;
SQL> declare
2 c_tax_rate number(3,2):=0.03;
3 --用户名,工资，税收
4 v_ename varchar2(5);--有漏洞
5 v_sal number(7,2);
6 v_tax_sal number(7,2);
7 begin
8 --执行部分
9 select ename,sal into v_ename,v_sal from emp where empno=&em;
10 ---计算所得税
11 v_tax_sal:=v_sal*c_tax_rate;
12 --输出
13 dbms_output.put_line('姓名是:'||v_ename||'工资'||v_sal||'所得税'||v_tax_sal);
14 end;
15 /
PL/SQL procedure successfully completed
标量(scalar)---使用type类型
对于上面的pl/sql块有一个问题:
就是如果员工的姓名超过了5个字符，就会有错误，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。
用法:标示符名表名.列名%type;
v_ename emp.ename%type;
declare
c_tax_rate number(3,2):=0.03;
--用户名,工资，税收
v_ename emp.ename%type;
v_sal number(7,2);
v_tax_sal number(7,2);
begin
--执行部分
select ename,sal into v_ename,v_sal from emp where empno=&em;
---计算所得税
v_tax_sal:=v_sal*c_tax_rate;
--输出
dbms_output.put_line('姓名是:'||v_ename||'工资'||v_sal||'所得税'||v_tax_sal);
end;
declare
c_tax_rate number(3,2):=0.03;
--用户名,工资，税收
v_ename emp.ename%type;
v_sal emp.sal%type;
v_tax_sal number(7,2);
begin
--执行部分
select ename,sal into v_ename,v_sal from emp where empno=&em;
---计算所得税
v_tax_sal:=v_sal*c_tax_rate;
--输出
dbms_output.put_line('姓名是:'||v_ename||'工资'||v_sal||'所得税'||v_tax_sal);
end;
(2)复合变量(composite)--介绍
用于存放多个值的变量，主要包括这几种
(1)pl/sql记录
(2)pl/sql表
(3)嵌套表
(4)varray
---pl/sql记录实例
复合类型--pl/sql记录
类似于高级语言中的结构体，需要注意的是,当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量，记录成员)如下
declare
--定义一个pl/sql记录类型emp_record_type,类型包含三个数据name,salary,title;
type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);
--定义了一个sp_record变量，这个变量的类型是emp_record_type
sp_record emp_record_type;
begin
select ename,sal,job into sp_record from emp where empno=7788;
dbms_output.put_line('员工名'||emp_record.name);
end;
SQL> declare
2 --定义一个pl/sql记录类型emp_record_type,类型包含三个数据name,salary,title;
3 type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);
4 --定义了一个sp_record变量，这个变量的类型是emp_record_type
5 sp_record emp_record_type;
6 begin
7 select ename,sal,job into sp_record from emp where empno=7788;
8 dbms_output.put_line('员工名'||emp_record.name);
9 end;
10 /
SQL> declare
2 --定义一个pl/sql记录类型emp_record_type,类型包含三个数据name,salary,title;
3 type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);
4 --定义了一个sp_record变量，这个变量的类型是emp_record_type
5 sp_record emp_record_type;
6 begin
7 select ename,sal,job into sp_record from emp where empno=7788;
8 dbms_output.put_line('员工名'||emp_record.name||'工资'||sp_record.salary);
9 end;
10 /
复合类型---pl/sql表
相当于高级语言中的数组，但是需要注意的是高级语言中数组的下标不能为负数，而pl/sql是可以为负数的、并且表元素的下表没有限制。
实例如下
declare
type sp_table_type is table of emp.ename%type index by binary_integer;
sp_table sp_table_type;
begin
selectename into sp_table(0) from emp where empno=778;
dbms_output.putline('员工名:'||sp_table(0));
end;
说明:
sp_table_type 是pl/sql表类型;
emp.ename%type 指定了表的元素的类型和长度;
sp_table 为pl/sql表变量
sp_table(0)则表示下标为0的元素
如果把where条件去掉了，怎样呢?
---pl/sql记录实例
declare
--定义一个pl/sql记录类型emp_record_type,类型包含三个数据name,salary,title;
type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);
--定义了一个sp_record变量，这个变量的类型是emp_record_type
sp_record emp_record_type;
begin
select ename,sal,job into sp_record from emp where empno=7788;
dbms_output.put_line('员工名'||emp_record.name);
end;
--复合类型----pl/sql表的实例
declare
--定义了一个pl/sql表类型sp_table_type(是一个类型,不是一个变量)，该类型是用于存放emp.ename%type类型的数据
--index by binary_integer;表示下表是整数
type sp_table_type is table of emp.ename%type index by binary_integer;
--定义了一个sp_table变量,变量的类型是sp_table_type;pl/sql中定义时变量在前，类型在后面
sp_table sp_table_type;
begin
select ename into sp_table(0) from emp where empno=778;
dbms_output.putline('员工名:'||sp_table(0));
end;
--出错，下标出现错误
declare
--定义了一个pl/sql表类型sp_table_type(是一个类型,不是一个变量)，该类型是用于存放emp.ename%type类型的数据
--index by binary_integer;表示下表是整数
type sp_table_type is table of emp.ename%type index by binary_integer;
--定义了一个sp_table变量,变量的类型是sp_table_type;pl/sql中定义时变量在前，类型在后面
sp_table sp_table_type;
begin
select ename into sp_table(-1) from emp where empno=778;
dbms_output.putline('员工名:'||sp_table(0));
end;
--出错，实际返回的行数超出了请求的行数
declare
--定义了一个pl/sql表类型sp_table_type(是一个类型,不是一个变量)，该类型是用于存放emp.ename%type类型的数据
--index by binary_integer;表示下表是整数
type sp_table_type is table of emp.ename%type index by binary_integer;
--定义了一个sp_table变量,变量的类型是sp_table_type;pl/sql中定义时变量在前，类型在后面
sp_table sp_table_type;
begin
select ename into sp_table(0) from emp;
dbms_output.putline('员工名:'||sp_table(0));
end;
复合变量--嵌套表(nested table)
复合变量--变长数组(varray)
参照变量
参照变量是指用于存放数值指针的变量，通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间，
在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型
1.游标变量(ref cursosor)
2.对象类型变量(ref obj_type)
参照变量--ref cursor游标变量，
使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时(open)需要指定select语句,这样一个有标语一个select语句的结合
(1)请使用pl/sql编写一个块,可以输入部门号,并显示该部门所有员工的姓名和他的工资。
(2)在(1)的基础上，如果某个员工的工资低于200元，就增加100元。
--参照变量-ref cursor游标变量
--(1)请使用pl/sql编写一个块,可以输入部门号,并显示该部门所有员工的姓名和他的工资。
declare
--定义游标类型sp_emp_cursor
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--执行部分
--把test_cursor和一个select结合
open test_cursor for select ename,sal from emp where deptno=&no;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--判断退出条件，是否test_cursor是否为空
exit when test_cursor%notfound
dbns_output.put_line('名字为'||v_ename||'工资'||v_sal);
end loop;
end;
--(2)在(1)的基础上，如果某个员工的工资低于200元，就增加100元。
declare
--定义游标类型sp_emp_cursor
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--执行部分
--把test_cursor和一个select结合
open test_cursor for select ename,sal from emp where deptno=&no;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--判断工资高低，决定是否更新
if(v_sal<200) v_sal=v_sal+100;
--判断退出条件，是否test_cursor是否为空
exit when test_cursor%notfound
dbns_output.put_line('名字为'||v_ename||'工资'||v_sal);
end loop;
end;
---------------第27讲oracle----------------------------------------------------------------------
--内容介绍
1.上节回顾
2.pl/sql的进阶
3.oracle的视图
4.oracle的触发器
--期望目标
1.掌握pl/sql的高级用法(能够编写分页过程模块，下订单过程模块....)
2.会处理oracle常见的例外
3.会编写oracle各种触发器
4.理解视图的概念并能灵活使用视图
pl/sql的进阶----控制结构
1)使用各种if语句
2)使用循环语句
3)使用控制语句----goto和null
条件分支语句
pl/sql中提供了三种条件语句
(1)if--then
(2)if---then---else
(3)if----then-----else if----else
编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该雇员工资增加10%
create or replace procedure sp_pro6(spName varchar2) is
--定义
v_sal emp.sal%type;
begin
--执行
select sal into v_sal from emp where ename=spName;
--判断
if v_sal<2000 then
update emp set sal=sal+sal*0.1 where ename=spName;
end if;
end;
sql>select * from emp;
sql>exec sp_pro6('SCOTT');
spl>select * from emp;
二重条件分支if---then--else
编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0，就给该雇员补助在原来的基础上增加100;如果补助为0就把补助设置为200;
create or replace procedure sp_pro7(spName varchar2) is
--定义
v_comm emp.comm%type;
begin
--执行
select comm into v_comm from emp where ename=spName;
--判断
if v_sal<>0 then
update emp set comm=comm+100 where ename=spName;
else
update emp set comm=comm+200 where ename=spName;
end if;
end;
sql>select * from emp;
sql>exec sp_pro7('SCOTT');
spl>select * from emp;
多重条件分支if--then--else if---else
编写一个过程，可以输入一个雇员名，如果该雇员的职位是PRESIDENT，就给该他工资增加1000;如果该雇员是MANAGER,就给他的工资增加500;其他职位的雇员工资增加200;
create or replace procedure sp_pro8(spNO number) is
--定义
v_job emp.job%type;
begin
--执行部分
select job into v_job from emp where empno=spNO;
if v_job='PERSIDENT' then
update emp set sal=sal+1000 where empno=spNO;
elsif v_job='MANAGER' then
update emp set sal=sal+500 where empno=spNO;
else
update emp set sal=sal+100 where empno=spNO;
end if;
end;
sql>select * from emp;
sql>exec sp_pro8(7839);
spl>select * from emp;
循环语句----loop
loop是pl/sql中最简单的循环语句，这种循环语句以loop开头，以end loop结尾，这种循环至少执行一次。
案例:现有一张表users
请编写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从1开始增加。
---循环语句loop,新建一张表
create table usersq(num1 number，Name1 varchar2(40));
create or replace procedure sp_prol(spName varchar2) is
--定义:=表示赋值
v_num number:=1;
begin
loop
insert into usersq values(v_num,spName);
--判断是否要退出循环
exit when v_num=10;
--自增
v_num:=v_num+1;
end loop;
end;
SQL> create table usersq(num1 number，Name1 varchar2(40));
Table created
SQL>
SQL> create or replace procedure sp_prol(spName varchar2) is
2 --定义:=表示赋值
3 v_num number:=1;
4 begin
5 loop
6 insert into usersq values(v_num,spName);
7 --判断是否要退出循环
8 exit when v_num=10;
9 --自增
10 v_num:=v_num+1;
11 end loop;
12 end;
13 /
Procedure created
SQL> exec sp_prol('你好');
PL/SQL procedure successfully completed
SQL> select * from usersq;
NUM1 NAME1
---------- ----------------------------------------
1 你好
2 你好
3 你好
4 你好
5 你好
6 你好
7 你好
8 你好
9 你好
10 你好
10 rows selected
循环语句---while循环
基本循环loop至少执行循环体一次,而对于while循环来说，只有条件为true时，才会执行循环体语句，while循环以while.loop开始,以end loop结束
请编写一个过程，可输入用户名，并循环添加10个用户到users表中，用户编号从11开始增加。
create or replace procedure sp_pro2(spName varchar2) is
--定义:=表示赋值
v_num number:=11;
begin
while v_num<=20 loop
--执行部分
insert into usersq values(v_num,spName);
--自增
v_num:=v_num+1;
end loop;
end;
SQL> create or replace procedure sp_pro2(spName varchar2) is
2 --定义:=表示赋值
3 v_num number:=11;
4 begin
5 while v_num<=20 loop
6 --执行部分
7 insert into usersq values(v_num,spName);
8 --自增
9 v_num:=v_num+1;
10 end loop;
11 end;
12 /
Procedure created
SQL> exec sp_pro2('wo');
PL/SQL procedure successfully completed
SQL> select * from userq;
select * from userq
ORA-00942: 表或视图不存在
SQL> select * from usersq;
NUM1 NAME1
---------- ----------------------------------------
1 你好
2 你好
3 你好
4 你好
5 你好
6 你好
7 你好
8 你好
9 你好
10 你好
11 wo
12 wo
13 wo
14 wo
15 wo
16 wo
17 wo
18 wo
19 wo
20 wo
20 rows selected
循环语句---for循环
基本for循环的基本结构如下
create or replace procedure sp_pro3(spName varchar2) is
--定义:=表示赋值
v_num number:=11;
begin
for i in reverse 1..10 loop
insert into usersq values(i,'小王');
end loop;
end;
我们可以看到控制变量i，再引隐含就在不停的增加
顺序控制语句---goto,null
(1)goto语句
goto语句用于跳转到特定标号去执行语句，注意由于使用goto语句会增加程序的复杂性，并使得应用程序可读性变差，所以在做一般应用开发时，建议大家不要使用goto语句
基本语法如下
goto table,其中table是已经定义好的标号名
SQL> declare
2 i int:=1;
3 begin
4 loop
5 dbms_output.put_line('输出i='||i);
6 if i=10 then
7 goto end_loop;
8 end if;
9 i:=i+1;
10 end loop;
11 <<end_loop>>
12 dbms_output.put_line('循环结束');
13 end;
14 /
输出i=1
输出i=2
输出i=3
输出i=4
输出i=5
输出i=6
输出i=7
输出i=8
输出i=9
输出i=10
循环结束
PL/SQL procedure successfully completed
declare
i int:=1;
begin
loop
dbms_output.put_line('输出i='||i);
if i=10 then
goto end_loop;
end if;
i:=i+1;
end loop;
dbms_output.put_line('循环结束');
<<end_loop>>
end;
顺寻控制语句--null
(1)null
null语句不会执行任何操作，并且会直接将控制传递到下一条语句，
使用null语句的主要好处是可以提高pl/sql的可读性
declare
v_sal emp.sal%type;
v_ename emp.ename%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
if v_sal<3000 then
update emp set sal=sal+0.1*sal where ename=v_ename;
else
null;
end if;
end;
pl/sql的进阶---编写分页过程
分也是任何一个网站(bbs,网上商城、blog)都会使用到的技术
因此学习pl/sql编程开发就一定要掌握该技术
---无返回值的存储过程
案例:
请编写一个过程，可以像book表添加书,要求通过java程序调用该过程
1.首先调用一张表
create table book (bookId number,bookName varchar2(50),publishHouse varchar2(50));
---编写过程
---spBookId (in) number,
--in代表往存储储过程输入，默认为in
--out代表一个输出参数
create or replace procedure sp_pro4(
spBookId in number,spbookName in varchar2,sppulishHouse varchar2) is
begin
insert into book values(spBookId,spbookName,sppulishHouse);
end;
在java中如何调用
package com;
//调用一个无返回值的过程
import java.sql.*;
public class TESTfenye {
public static void main(String[] args) {
//1.加载驱动
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到连接
Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL","SCOTT","whx");
//3.创建一个CallableStatement
CallableStatement cs=ct.prepareCall("{call sp_pro4(?,?,?)}");
//给?赋值
cs.setInt(1, 10);
cs.setString(2, "笑傲江湖");
cs.setString(3, "人民出版社");
//执行
cs.execute();
//关闭资源
cs.close();
ct.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
有返回值的存储过程(非列表)
再看如何处理有返回值的存储过程
案例:编写一个过程，可以输入雇员的编号，返回该雇员的姓名
package com;
//调用一个有返回值的过程
import java.sql.*;
public class TESTfenye {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
//1.加载驱动
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到连接
Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL","SCOTT","whx");
//3.创建一个CallableStatement
//CallableStatement cs=ct.prepareCall("{call sp_pro4(?,?,?)}");
//给?赋值
// cs.setInt(1, 10);
// cs.setString(2, "笑傲江湖");
// cs.setString(3, "人民出版社");
//
//看看如何调用有返回值的过程
CallableStatement cs=ct.prepareCall("{call sp_pro8(?,?)}");
//给?赋值
cs.setInt(1, 7788);
//给第二个问号赋值
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
//执行
cs.execute();
//取出返回值
String name=cs.getString(2);//因为返回值在第二个问号
System.out.println(" 7788"+name);
//关闭资源
cs.close();
ct.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
案例扩展:编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资、和岗位
package com;
//调用一个有返回值的过程
create or replace procedure sp_pro9(spno in number,spName out varchar2,spSal out number,spJob out varchar2) is
begin
select ename,sal,job into spName,spSal,spJob from emp where empno=spno;
end;
import java.sql.*;
public class TESTfenye {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
//1.加载驱动
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到连接
Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL","SCOTT","whx");
//3.创建一个CallableStatement
//CallableStatement cs=ct.prepareCall("{call sp_pro4(?,?,?)}");
//给?赋值
// cs.setInt(1, 10);
// cs.setString(2, "笑傲江湖");
// cs.setString(3, "人民出版社");
//
//看看如何调用有返回值的过程
CallableStatement cs=ct.prepareCall("{call sp_pro9(?,?,?,?)}");
//给?赋值
cs.setInt(1, 7788);
//给第二个问号赋值
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.DOUBLE);
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.VARCHAR);
//执行
cs.execute();
//取出返回值
String name=cs.getString(2);//因为返回值在第二个问号
System.out.println(" 7788"+name);
String job=cs.getString(4);
System.out.println(job);
//关闭资源
cs.close();
ct.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
有返回值的存储过程(列表[结果集])
案例:编写一个过程，输入部门号，返回该部门所有的雇员信息，
分析
由于oracle存储过程没有返回值，他所有返回值都是通过out参数来替代的，列表同样也不例外，
但由于是集合，所以不能用一般的参数，必须用package，所以要分两部分
--1.创建包,在该包中定义了一个test_cursor类型
create or replace package testpackage as type test_cursor
is ref cursor;
end testpackage;
--2.创建存储过程
create or replace procedure sp_pro10(spNo in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno=spNo;
end;
--3.如何在JAVA中调用该存储过程
package com;
//调用一个无返回值的过程
import java.sql.*;
public class TESTfenye {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
//1.加载驱动
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
//得到连接
Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL","SCOTT","whx");
//3.创建一个CallableStatement
//CallableStatement cs=ct.prepareCall("{call sp_pro4(?,?,?)}");
//给?赋值
// cs.setInt(1, 10);
// cs.setString(2, "笑傲江湖");
// cs.setString(3, "人民出版社");
//
//看看如何调用有返回值的过程
// CallableStatement cs=ct.prepareCall("{call sp_pro9(?,?,?,?)}");
//
// //给?赋值
// cs.setInt(1, 7788);
// //第2问题.给第二个问号赋值
// cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
// cs.registerOutParameter(3, oracle.jdbc.OracleTypes.DOUBLE);
// cs.registerOutParameter(4, oracle.jdbc.OracleTypes.VARCHAR);
//1.创建CallableStatement
CallableStatement cs=ct.prepareCall("{call sp_pro10(?,?)}");
//给问号赋值
cs.setInt(1, 10);
//给第二个问号注册
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
//执行
cs.execute();
//取出返回值
// String name=cs.getString(2);//因为返回值在第二个问号
// System.out.println(" 7788"+name);
// String job=cs.getString(4);
// System.out.println(job);
//得到结果集
ResultSet rs=(ResultSet)cs.getObject(2);
while(rs.next())
{
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
//关闭资源
cs.close();
ct.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
--------第29讲oracle
编写分页过程
由上面的基础，
要求，编写一个存储过程，要求输入表名、每页显示记录数、当前页，返回总记录、总页数，和返回的结果集
oracle分页
--开发一个包
--使用上面的包
create or replace package testpackage as type test_cursor
is ref cursor;
end testpackage;
--开始编写分页的过程
create or replace procedure fenye
(tablename in varchar2,
Psize in number,--3、4、5、一页显示记录
PNow in number,--1、2、3
myrows out number,--总记录数
myPageCount out number,--总页数
p_cursor out testpackage.test_cursor---返回的记录
) is
--定义部分
--定义sql语句字符串
v_sql varchar2(1000);
--
v_begin number:=(PNow-1)*psize+1;
v_end number:=PNow*psize;
begin
--执行部分
v_sql:='select * from (select t1.*,rownum rn from (select * from '||tablename ||') t1 where rownum<='||v_end||') where rn>='||v_begin;
--把游标和sql语句关联
open p_cursor for v_sql;
--关闭游标
--计算myrows和myPageCount
--组织了一个sql语句
v_sql:='select count(*) from'||tablename;
--执行sql语句，并把返回值赋值给myrows;
execute immediate v_sql into myrows;
--计算myPageCount
if mod(myrows,psize)=0 then
myPageCount=myrows/psize;
else
myPageCount=myrows/psize+1;
end if;
--关闭游标
close p_cursor;
end;
//测试分页
import java.sql.*;
public class FenYe {
/**
* @param args
* @throws ClassNotFoundException
*/
public static void main(String[] args){
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL","SCOTT","whx");
CallableStatement cs=ct.prepareCall("{call fenye(?,?,?,?,?,?)}");
cs.setString(1, "emp");
cs.setInt(2, 5);
cs.setInt(3, 1);//第一页的记录
//cs.setInt(3, 2);第二页的记录
//cs.setInt(3, 3);第三页的记录
//注册总记录数
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
//注册总页数
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
//注册返回结果集
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
//取出总记录数
//执行
cs.execute();
//这里需要注意，getInt(4)中的4，是参数的位置决定的
int rowNum=cs.getInt(4);
int pageCount=cs.getInt(5);
ResultSet rs=(ResultSet)cs.getObject(6);
System.out.println(""+rowNum);
System.out.println(""+pageCount);
while(rs.next())
{
System.out.println(" "+rs.getInt(1)+ ""+rs.getString(2)+""+rs.getFloat(6));
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
---问题---排序需求
create or replace procedure fenye
(tablename in varchar2,
Psize in number,--3、4、5、一页显示记录
PNow in number,--1、2、3
myrows out number,--总记录数
myPageCount out number,--总页数
p_cursor out testpackage.test_cursor---返回的记录
) is
--定义部分
--定义sql语句字符串
v_sql varchar2(1000);
--
v_begin number:=(PNow-1)*psize+1;
v_end number:=PNow*psize;
begin
--执行部分
v_sql:='select * from (select t1.*,rownum rn from (select * from '||tablename ||'order by sal) t1 where rownum<='||v_end||') where rn>='||v_begin;
--把游标和sql语句关联
open p_cursor for v_sql;
--关闭游标
--计算myrows和myPageCount
--组织了一个sql语句
v_sql:='select count(*) from'||tablename;
--执行sql语句，并把返回值赋值给myrows;
execute immediate v_sql into myrows;
--计算myPageCount
if mod(myrows,psize)=0 then
myPageCount:=myrows/psize;
else
myPageCount:=myrows/psize+1;
end if;
--关闭游标
close p_cursor;
end;
在执行一下，在最后用JAVA调用
----------第30讲oracle
pl/sql的进阶----例外处理
oracle将例外分为预定义例外、非预定义例外和自定义例外三种
预定义例外用于处理常见的oracle错误
非预定义例外用于处理预定义例外不能处理的例外
自定义例外用于处理与oracle错误无关的其他情况
例外传递
如果不处理里外我们看看出现什么情况
案例:编写一个过程，可接受雇员的编号，并显示该雇员的名字
问题是，如果输入的雇员的编号不存在，怎样去处理。
---------例外处理
declare
v_ename emp.ename%type;
--定义
begin
select ename into v_ename from emp where empno=&no;
dbms_output.put_line('姓名为:'||v_ename);
exception
when no_data_found then
dbms_output.put_line('不存在');
end;
----预定义例外case_not_found
在开发pl/sql块中编写case语句时，如果在when子句中没有包含必要的条件分支，就会触发case_not_found例外。
create or replace procedure sp_pro6(spno number) is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=spno;
case
when v_sal<1000 then
update emp set sal=sal+100 where empno=spno;
when v_sal<2000 then
update emp set sal=sal+200 where empno=spno;
end case;
exception
when case_not_found then
dbms_output.put_line('case语句没有与'||v_sal||'相匹配的条件');
end;
---如果查出来的薪水为3000
---预定义例外cursor_already_open
declare
cursor emp_cursor is ref ename,sal from emp;
begin
open emp_cursor;
for emp_record in emp_cursor loop
dbms_output_putline(emp_record.ename);
end loop;
exception
when cursor_alreadly_open then
dbms_output.putline('游标已经打开');
end;
--预定义例外dup_val_on_index
在唯一索引对应的列上插入重复的值时，会隐含的触发例外dup_val_on_index例外
declare
begin
insert into dept values(10,'公安部','北京');
exception
when dup_val_on_index then
dbms_output.putline('在dept列上不能重复值');
end;
--预定义例外invalid_cirsor
当试图在不合法的游标上执行操作时，会触发该例外
例如:试图从没有打开的游标提取数据，或是关闭没有打开的游标，则会触发该例外
declare
cursor emp_cursor is select ename,sal from emp;
emp_reocrd emp_crusor%type;
begin
--open emp_cursor;
fetch emp_cursor into emp_record;
dbms_output.putline(emp_record.ename);
close emp_cursor;
exception
when invalid_cursor then
dbms_output.putline('请检测游标是否打开');
end;
--预定义例外invalid_number
当桑栓的数据有误时，会触发该例外
比如:数字100写成loo就会触发该例外
begin
update emp set sal=sal+'loo';
exception
when invalid_number then
dbms_output.putline('输入的数字不正确');
end;
--预定义例外no_data_found
下面是一个pl/sql块，当执行select into没有返回行，则会触发该例外
declare
v_sal emp.sal%type;
begin
select sal into v_sal emp where ename='&ename';
exception
when no_data_found then
dbms_output.putline('不存在该员工');
end;
--预定义例外too_many_rows
当执行select into语句时，如果返回超过了一行，则会触发该例外
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp;
exception
when too_many_rows then
dbms_output.putline('返回了多行');
end;
预定义例外zero_divide
当执行2/0语句时，则会触发该例外
--预定义例外value_error
当在执行赋值操作时，如果变量的长度不足以容纳实际数据
，则会触发该例外value_error;
比如:
declare
v_ename varchar2(5);--ename varchar2(8)
begin
select ename into v_ename from emp where empno=&no;
dbms_output.putline(v_ename);
exception
when value_error then
dbms_output.putline('变量长度不够');
end;
其他预定义例外
(1)login_denide
当用户非法登陆时，会触发该例外
(2)not_logged_on
如果用户没有登陆就执行dml操作，就会触发该例外
(3)storage_error
如果超出了内存空间或是内存被损坏，就会触发该例外
(4)timeout_on_resource
如果oracle在等待资源时，出现了超市就会触发该例外
非预定义例外
非预定义例外用于处理与预定义例外无关的oracle错误，
使用预定义例外只能处理oracle错误，而当使用pl/sql开发应用程序时，可能会遇见其他的一些oracle错误，比如在pl/sql块中执行dml语句，违反了约束规定等等
在这样的情况下，可以处理oracle的各种例外，因为非预定义例外用的不多
处理自定义例外
预定义例外和自定义例外都是与oracle错误相关的，并且出现的oracle错误会隐含的触发相应的例外
;而自定义例外与oracle错误没有任何关系，它是由开发人员为特定情况所定义的例外
编写一个pl/sql块,接受一个雇员的编号，并给该雇员的工资增加1000元，
如果该雇员不存在，请提示
SQL> create or replace procedure ex_test(spNo number)
2 is
3 --定义一个例外
4 myex exception;
5 begin
6 --更新用户sal
7 update emp set sal=sal+1000 where empno=spNo;
8 --sql%notfound这里表示没有uodate成功
9 --raise myex触发例外
10 if sql%notfound then
11 raise myex;
12 end if;
13 exception
14 when myex then
15 dbms_output.put_line('没有任何用户更新');
16 end;
17 /
Procedure created
SQL> exec ex_test(56);
没有任何用户更新
PL/SQL procedure successfully completed
oracle视图--介绍
试图使一个虚拟表，
其内容有查询定义，同真实的表一样，
视图包含一系列带有名称的列和行数据。
但是。视图并不在数据库中以存储的数据值形式存在，
行和列数据来自由定义视图的查询所引用的表，
并且在引用视图是动态生成的。
1.如果要显示个雇员的名字和他所在部门的名称，必须用两张表
2.假设管理员创建了一个用户xiaoming,现在希望xiaoming只可以查询sal<1000的哪些雇员
视图的必要性(视图并不是真实存在的)
视图与表的区别
(1)表需要占用存储空间，视图不需要占用空间
(2)视图不能添加索引
(3)使用视图可以简化复杂查询
比如:学生选课系统
(4)视图用利于提高安全性
比如:不同用户查看不同视图
创建视图
create view 试图名 as select语句 [with read only]
创建或修改视图
create or replace view视图名 as select语句[with read only]
删除视图
drop view 视图名
---创建视图，把emp表sal<1000的雇员映射到该视图(view)
--视图创建以后，可以将视图当成一张普通的表
create view myview as select * from emp where sal<1000;
sql>select * from myview;
--为了简化操作，用一个视图解决，显示雇员编号，姓名和部门名称
sql>create view myview2 as select emp.ename,emp.sal,dept.deptno from emp,dept where emp.deptno=dept.deptno;
sql>select * from myview2;
--视图和视图之间可以进行联合查询
--？？？如何删除表里面的重复的记录
create table cat
(
catId int,
catName varchar(40)
)
insert into cat values (1,'aa');
insert into cat values (2,'bb');
select * from cat;
--？？？如何删除表里面的重复的记录
--1.把cat表没有重复distinct的记录结果，放入到#temp_1表中
select distinct * into #temp_1 from cat
--把cat表中的数据清空
delete from cat
--把#temp_1表中的数据(没有重复的数据),插入到cat表中
insert into cat select * from #temp_1
--删除#temp_1,oracle可以一句话搞定，SQL必须这4句话同时运行
drop table #temp_1
--将左外连接和右外链
--内连接
---显示公司每个员工和他的上级
select w.ename,b.ename from emp w,emp b where w.mgr=b.empno
--显示公司每个员工和他的上级(没有上级的也要显示)
--左外连接:指左边的表的记录显示全部，如果没有匹配的记录就用null表示
select w.ename,b.ename from emp w left join emp b on w.mgr=b.empno
--右外连接:指右边的表的记录显示全部，如果没有匹配的记录就用null表示
select w.ename,b.ename from emp w right join emp b on w.mgr=b.empno
--第63讲--SQL-server约束
--1.not null
create table test1
(
testId int primary key identity(1,1),
testname varchar(30) not null ,--不为空
testpass varchar(39) not null,
testage int --可以为空
)
insert into test1(testage) values(3)
insert into test1(testname,testpass,testage) values ('','',5)
select * from test1
--unique
create table test2
(
testId int primary key identity(1,1),--行级定义
testname varchar(30) unique,--不为空 行级定义
testpass varchar(39) ,
testage int --可以为空
)
insert into test2 (testname,testpass,testage) values ('aa','123',45)
insert into test2 (testpass,testage) values ('123',45)
select * from test2
--复合主键
create table test3
(
testId int,
testname varchar(30),--不为空
testpass varchar(39) ,
testage int --可以为空
primary key (testId,testname)--复合主键 --表级定义
)
--行级定义和表级定义
--
create table test4
(
testId int,
testname varchar(30),--不为空
testpass varchar(39) ,
sal int check(sal>=1000 and sal<=2000)--规定sal的值1000-2000
)
insert into test4 values (4,'aa','aa',2000)
insert into test4 values (4,'aa','aa',2030)
--default 使用
create table nes
(
nesId int primary key identity(1,1),
mescon varchar(2000) not null,
nesDate datetime default getdate()
)
insert into nes (mescon) values ('你好吗');--默认时间
insert into nes (mescon,nesDate) values ('你好吗','2000-1-1');--自定义时间
select * from nes
--维护数据的完整性
--每张表的主外健
--客户的名字不能为空
--单价必须大于0，购买数量必须在1到30之间
--电邮不能够重复
--客户的性别必须是男或者是女，默认是男
--商品的类别是‘食物’ ‘日用品’
--good表
create table goods
(
goodId nvarchar(50) primary key,
goodName nvarchar(80) not null,--商品名字不能为空
unitprice numeric(10,2) check(unitprice >0),
category nvarchar(3) check (category in('食物','日用品')),
provider nvarchar(50)
)
--customer
create table customer
(
customerId nvarchar(50) primary key,
cusname nvarchar(50) not null,
address nvarchar(100),
email nvarchar(100) unique,
sex nchar(1) check(sex in('男','女')) default '男',
carId nvarchar(18)
)
--purchase
create table purchase
(
customer nvarchar(50) foreign key references customer(customerId),
goodId nvarchar(50) foreign key references goods(goodId),
nums int check(nums>0)
)
--------------------------------------------------------
--
-----------第64讲-sql-server备份和恢复
----1.sql-server数据库的备份和恢复
----2.java程序如何操作sql-server
---掌握sql-server数据库的备份和恢复技巧
---掌握java对表的crud的操作（增删改查）
---
---
---
---使用企业管理器完成备份与恢复
---1.分离/附加
---分离完成后，请到sql-server安装的目录下去找两个文件 数据库名.mdf
---和数据库名.
---备份与恢复
---如何备份数据库
---backup database 表名 to disk='d:/sp.bak'
---语句：backup database表名 to disk='路径'
backup database aaa to disk='d:/sp.bak'
--删除数据库
drop database aaa
--恢复数据库
restore database 你的数据库名 from disk='备份文件路径'
restore database aaa from disk='d:/sp.bak'
--有时只需要对表的备份和恢复
--java程序操作sql-server
--crud
---JDBC
---解释
---sql注入漏洞
select * from dept;
create table users
(username varchar(30),
passwd varchar(30)
)
insert into users values('shunping','shunping');
select * from users where username='shunping' and passed ='shunping';
select * from users where username='shunping' and passed ='shunping' or 1='1';
select * from users where username='shunping' and passed ='shaf' or 1='1';
select * from users where username='shun' and passed ='shu' or 1='1';
-------------------------------------------------------------
--------第67讲
---1.选择部门30中所有的员工
select * from emp where deptno=30
---2.列出所有办事员
select * from emp where job='clerk'
--3.找出佣金高于薪金的员工
select * from emp where isnull(coma,0)>sal
---4.找出佣金高于薪金的60%的员工
select * from emp where coma>sal*0.6
---5.找出部门10中所有经理和部门20中所有的办事员
select * from emp where ( deptno=10 and job='manager' )or (deptno=20 and job='clerk')
---6.找出部门10中所有经理和部门20中所有的办事人员
--既不是经理又不是办事人员但其薪金大于等于2000的所有员工
select * from emp where (deptno=10 and job='manager') or (deptno=20 and job='clerk')
or (job<>'manager 'and job<>'clerk'and sal>=2000)
---7.找出收取佣金的员工的不同工作
select distinct job from emp where coma>0
--8.找出不收取佣金或收取佣金小于100的员工
select * from emp where coma<100 or is null
--9.找出各月倒数第三天受雇的所有员工
--//oracle有专门的函数
select * from emp
where
(
datepart(mm.hiredate) is(1,3,5,7,8,10,12)
and datepart (dd.hiredate)<29
)
or
(
datepart(mm.hiredate) is(4,6,9,11)
and datepart (dd.hiredate)=28
)
or
(
(
datepart(yyyy.hiredate) <4
and datepart(yyyy.hiredate)%100<>0
or datepart (dd.hiredate)%400=0
)
and datepart (mm.hiredate)=2
and datepart(dd.hiredate0=27
)
or
(
datepart (mm.hiredate)=2
and datepart (dd.hiredate)=26
)
--10.找出早于12年前受雇的员工
select * from emp where datediff(year,hiredate.getdate())>12
select * from emp where datediff(year,hiredate.getdate())>23
--11.以首字母大写的方式显示所有员工的信息
upper len substring
select upper(substring(ename,1,1)+lower(substring(ename,2,len(ename)))) from emp;
--12.显示正好为5个字符的员工的信息
select * from emp where ename like '_____'
slect * from emp where len(ename)=5
--14.显示所有员工的姓名的前三个字符
select substring(ename,3) from emp;
--15.显示所有员工的名字,用a替换所有的A
select replace(ename,'A','a') from emp
--16显示服务超过10年的员工的名字和受雇日期
select ename,hiredate from emp where datediff(year,hiredate.getdate())>10
--17.显示所有员工的详细信息，按照名字排序
select * from emp order by ename
--18.显示员工的名字和受雇的日期，根据其服务年限，将最老的员工排在最前面
select ename,hiredate from emp where order by hiredate;
--19.显示所有的名字，工作和薪金，按照工作的降序排序，如果工作相同则按照薪金升序
select ename,job,sal from emp where order by job desc,sal asc;
--20.显示所有员工的名字，加入公司的年月份，按照受雇日期所在月份排序
--如月份相同则将最早年月份的员工牌在最前面
--datepart()函数
select ename,datepart(year,hiredate) y,datepart(month,hiredate) m from emp order by m,y
----------2---------
--1.列出至少有一个员工的所有的部门
----1.显示每个部门的员工
select count(*),deptno from emp group by deptno
select count(*),deptno from emp group by deptno having count(*)>1
select count(*),deptno from emp group by deptno having count(*)>5
--2.列出薪金比smith多的所有员工
select * from emp where sal>(select sal from emp where ename='smith')
--3.列出所有的员工的名字以及其直接上级的名字
--自连接
select e.ename,b.ename from emp e,emp b where e.mgr=b.empno
--4.列出受雇日期晚于其直接上级的所有员工
select e.ename,b.ename from emp e,emp b where
e.mgr=b.empno and e.hiredate>b.hiredate;
--5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
-----列出部门名称和这些部门的员工信息
select d.dname,e.ename.e.job from emp e,dept d where e.deptno=d.deptno
---列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select d.dname,e.ename.e.job from emp e right join dept d on e.deptno=d.deptno
select d.dname,e.ename.e.job from dept d left join emp e on e.deptno=d.deptno
--6.列出所有的clerck的名字
--7.列出最低薪金大于1500的各种工作
select min(sal),job from emp group by job having min(sal)>1500
--8.列出在部门sales工作的员工的名字，不知道销售部的编号
select ename,'sales' from emp where deptno=(select deptno from where dname='sales')
--9.列出薪金高于公司平均薪金的所有的员工
select * from emp where sal>(select avg(sal) from emp)
--10.列出与scott从实现工工作的所有的员工
--11.列出薪金等于部门30中员工的薪金的所有的员工的名字和薪金
select ename,sal from emp where sal in(select sal from emp where deptno=30)
--12.列出薪金高于在部门30工作的所有员工的薪金的员工的名字和薪金
select ename,sal from emp where sal>(select max(sal) from emp where deptno=30)
--13.列出在每个部门工作的员工的数量，平均工资和平均服务期限
select count(*) '总人数',avg(sal) '平均工资',avg(datediff(year,hiredate.getdate())) '平均服务年限',deptno from emp group by deptno
--14.列出所有员工的名字。部门名称、工资
select ename,deptno,sal from emp
--15.列出从事同一种工作但不属于不同部门的员工的一种组合
select w1.ename,w1.job,w1.deptno,w2.ename,s2.job,w2.deptno from
emp w1,emp w2 where w1.job=w2.job and w1.deptno<>s2.deptno
select ename,job,deptno from where job='clerk'
--16.列出所有部门的详细信息和部门人数
select count(*) ,dname from dept group by deptno
select d2.dname,d2.loc,d.c from dept d2,
(select count(*) c,deptno de from emp group by deptno) d
where d2.deptno=d.de
--左外连接
select d2.dname,d2.loc,d.c from dept d2 left join
(select count(*) c,deptno de from emp group by deptno) d
on d2.deptno=d.de
select d2.dname,d2.loc,isnull(d.c,0) from dept d2 left join
(select count(*) c,deptno de from emp group by deptno) d
on d2.deptno=d.de
--17.列出各种工作的最低工资
select*
--18.列出manager的最低薪金
select min(sal),job from emp group by job
select min(sal),ename from emp where job='manager'
--19.列出所有员工的年工资，按照年薪从低到高排序
select (sal+isnull(coma,0))*12 '年工资',ename from emp order by '年工资'
关系模式：
sb(sn，sname，city)
其中，s表示供应商，sn为供应商代号，sname为供应商名字，city为供应商所在城市，主关键字为sn。
pb(pn，pname，color，weight)
其中p表示零件，pn为零件代号，pname为零件名字，color为零件颜色，weight为零件重量，主关键字为pn。
jb(jn，jname， city)
其中，j表示工程，jn为工程编号，jname为工程名字，city为工程所在城市，主关键字为jn。
spjb(sn，pn，jn，qty)
其中，spj表示供应关系，sn是为指定工程提供零件的供应商代号，pn为所提供的零件代号，jn为工程编号，qty表示提供的零件数量，主关键字为(sn，pn，jn)，外部关键字为sn，pn，jn。
写出实现以下各题功能的sql语句：
(1).取出所有工程的全部细节；
select * from jb;
(2).取出所在城市为上海的所有工程的全部细节；
select * from jb where city='上海';
(3).取出重量最轻的零件代号；
select pn from pb where weight=(select min(weight) from pb)
(4).取出为工程j1提供零件的供应商代号；
select sn from spjb where jn＝'j1';
(5).取出为工程j1提供零件p1的供应商代号；
select sn from spjb where jn=‘jL’and pn＝'pL';
(6).取出由供应商s1提供零件的工程名称；
select jb.jname from jb，spjb where jb.jn＝spjb.jn and spjb.sn=‘s1’;
(7).取出供应商s1提供的零件的颜色；
select distinct pb.color from pb，spjb
where pb.pn＝spjb.pn and spjb．sn='s1';
(8).取出为工程j1或j2提供零件的供应商代号；
select distinct sn from spjb where jn='j1'or jn='j2';
(9).取出为工程j1提供红色零件的供应商代号；
select distinct spjb.sn from spjb，pb
where pb.pn=spjb.pn and spjb.jn=‘j1’and pb.color='红';
(10).取出为所在城市为上海的工程提供零件的供应商代号；
select distinct spjb.sn from spjb, jb
where spjb.jn=jb.jn and jb.city='上海';
(11).取出为所在城市为上海或北京的工程提供红色零件的供应商代号；
select spjb.sn from pb,jb spjb
where spjb.pn=pb.pn and jb.jn=spjb.jn and pb.color=’红’ and jb.city=’上海’;
(12).取出供应商与工程所在城市相同的供应商提供的零件代号；
select distinct spjb.pn from sb,jb,spjb
where sb.sn=spjb.sn and jb.jn=spjb.jn and sb.city=jb.city ;
(13).取出上海的供应商提供给上海的任一工程的零件的代号；
select spjb.pn from sb,jb,spjb
where sb.sn=spjb.sn and jb.jn=spjb.jn and sb.city=’上海’
(14).取出至少由一个和工程不在同一城市的供应商提供零件的工程代号；
select distinct spjb.jn from sb,jb,spjb
where sb.sn=spjb.sn and jb.jn=spjb.jn and sb.city＜＞jb.city;
(15).取出上海供应商不提供任何零件的工程的代号；
select distinct jn from spjb where jn not in
(select distinct spjb.jn from sb,spjb
where sb.sn=spjb.sn and sb.city=’上海’);
(16).取出这样一些供应商代号，它们能够提供至少一种由红色零件的供应商提供的零件；
select distinct spjb.sn from pb,spjb where spjb.pn in
(select spjb.pn from spjb,sb,pb
where sb.sn=spjb.sn and pb.pn=spjb.pn and pb.color=’红’)
(17).取出由供应商s1提供零件的工程的代号；
select distinct spjb.jn from sb,pb,spjb
where sb.sn=spjb.sn and pb.pn=spjb.pn and sb.sn=’s1’
(18).取出所有这样的一些〈city，city〉二元组，使得第1个城市的供应商为第2个
城市的工程提供零件；
select distinct sb.city, jb.city from sb,jb,spjb
where sb.sn=spjb.sn and jb.jn=spjb.jn ;
(19).取出所有这样的三元组〈city，pn city〉，使得第1个城市的供应商为第2个城市的工程提供指定的零件；
select distinct sb.city, spjb.pn, jb.city from sb,jb,spjb
where sb.sn=spjb.sn and jb.jn=spjb.jn;
(20).重复(19)题，但不检索两个city值相同的三元组。
select distinct sb.city, spjb.pn, jb.city from sb, jb, spjb
where sb.sn=spjb.sn and jb.jn=spjb.jn and sb.city＜＞jb.city
12．以下面的数据库为例，用sql完成以下检索。关系模式如下：
仓库（仓库号，城市，面积）←→ warehouse（wmno，city，size）
职工（仓库号，职工号，工资）←→ employee（whno，eno，salary）
订购单（职工号，供应商号，订购单号，订购日期）←→ order（sno，sno，ono，date）
供应商（供应商号，供应商名，地址）←→ supplier（sno，sname，addr）
(1).检索在北京的供应商的名称。
select sname from supplier where addr='北京';
(2).检索发给供应商s6的订购单号。
select ono from order where sno='s6';
(3).检索出职工e6发给供应商s6的订购单号。
select ono from order where sno='s6' and eno='e6';
(4).检索出向供应商s3发过订购单的职工的职工号和仓库号。
select eno，whno from employee
where eno in
（select eno from order where sno=“s3”）；
或：select eno，whno from employee，order
where employee.eno=order.eno and order.sno=“s3”；
(5).检索出目前与s3供应商没有联系的职工信息。
select eno，whno from employee
where eno not in (select eno from order where sno=“s3”）；
(6).检索出目前没有任何订购单的供应商信息。
select * from supplier where sno not in
（select sno from order）;
(7).检索出和职工e1、e3都有联系的北京的供应商信息。
select *
from supplier
where addr=“北京” and
(exists (select * from order where sno=suppier.sno and
eno=“e3”))
and
(exists (select * from order where sno=suppier.sno and eno=
“e6”))；
(8).检索出目前和华通电子公司有业务联系的每个职工的工资。
select eno，salary
from employee
where eno in
（select eno from order where sno in
（select sno from supplier where addr=“华通电子公司”））；
或：select eno，salary
from employee，order，supplier
where employee.eno=order.eno and order.sno=supplier.sno
and supplier.addr=“华通电子公司”；
(9).检索出与工资在1220元以下的职工没有联系的供应商的名称。
select sname
from supplier
where sno not in
（select sno from order where eno in
（select eno from employee where salary<1220））；
(10).检索出向s4供应商发出订购单的仓库所在的城市。
select city
from warehouse
where whno in (select whno from employee
where eno in (select eno from order where sno=“s4”))；
或：select city
from warehouse，employee，order
where warehouse.whno=employee.whno
and employee.eno=order.eno and order.sno=“s4”；
(11).检索出在上海工作并且向s6供应商发出了订购单的职工号。
select eno
from employee
where whno in
(select whno from warehouse where city=“上海”)
and (eno in select eno from order where sno=“s6”)；
或：select eno
from employee，warehouse，order
where employee.whno=wrehouse.whno
and wrehouse.city=“上海”
and employee.eno=order.eno and order.sno=“s6”；
(12).检索出在广州工作并且只向s6供应商发出了订购单的职工号。
select eno
from employee
where (whno in select whno from warehouse where city=“广州”)
and (eno in select eno from order where sno=“s6”)
and (not exists (select * from order
where sno<>“s6” and eno=employee.eno))；
(13).检索出由工资多于1230元的职工向北京的供应商发出的订购单号。
select ono
from order
where （eno in select eno from employee where salary>1230）
and （sno in select sno from supplier where addr=“北京”）；
或：select ono
from order，employee，supplier
where order.eno= employee.eno and employee.salary>1230
and order.sno= supplier.sno and supplier. addr=“北京”；
(14).检索出仓库的个数。
select count（*） from warehouse；
(15).检索出有最大面积的仓库信息。
select *
from warehouse outer
where outer.size=（select max（size） from warehouse inner）；
(16).检索出所有仓库的平均面积。
select avg(size) from warehouse；
(17).检索出向s4供应商发出订购单的那些仓库的平均面积。
select avg(size)
from warehouse
where whno in （select whno from employee
where eno in （select eno from order where sno=“s4”））；
(18).检索出每个城市的供应商个数。
select city，count(sno)
from supplier
group by city；
(19).检索出每个仓库中工资多于1220元的职工个数。
select whno，count(eno)
from employee
where salary>1220
group by whno；
或：select whno，count(eno)
from employee
group by whno
having salary>1220；
(20).检索出和面积最小的仓库有联系的供应商的个数。
select count(distinct sno)
from order
where eno in
select eno from employee
where whno in
select whno from warehouse outer
where outer.size=select min(size)
from warehouse inner；
(21).检索出工资低于本仓库平均工资的职工信息。
select * from employee outer
where outer.salary<(select avg(salary) from employee inner
where inner.whno=outer.whno group by whno)；
13．以下面的数据库为例，用sql完成以下更新操作。关系模式如下：
仓库（仓库号，城市，面积）←→ warehouse（wmno，city，size）
职工（仓库号，职工号，工资）←→ employee（whno，eno，salary）
订购单（职工号，供应商号，订购单号，订购日期）←→ order（sno，sno，ono，date）
供应商（供应商号，供应商名，地址）←→ supplier（sno，sname，addr）
(1).插入一个新的供应商元组（s9，智通公司，沈阳）。
insert into supplier values(s9，智通公司，沈阳)；
(2).删除目前没有任何订购单的供应商。
delete from supplier
where not exists （select * from order where order.sno=supplier.sno）；
或：delete from supplier
where sno not in （select sno from order）；
(3).删除由在上海仓库工作的职工发出的所有订购单。
delete from order
where eno in （select eno from employee
where whno in {select whno from warehouse where city=“上海”}}；
(4).北京的所有仓库增加100M2的面积。
update warehouse
sET size=size+100 where city=“北京”；
(5).给低于所有职工平均工资的职工提高5%的工资。
update employee outer
set outer.salary=outer.salary*1.05
where outer.salary<select avg（salary） from employee inner；
----------------------------------------------------------------------------------------------------------------------
Dos环境下使用SQl*Loader命令加载使用其它数据库的数据转移工具Oracle企业管理器中的数据加载功能
具体的技术实现
Dos 环境下加载
1、首先，服务器端的侦听服务必须已经开启。
测试方法：Dos下输入
C:\>sqlplus username/password@serviceName
2、然后使用 Oracle的 sqlldr 命令进行数据的导入
前期条件
1)Oracle数据库端必须已经建好了需要导入的数据表的结构
2)一个数据源文件下面例子中为制表符分隔的文本文件 model.txt ,为Excel表中导出的
3)手工编辑一个XXX.CTL的控制文件
4)命令行加载数据
如下实例：
以下文件缺省放到C:\下，如果不是，就需要指明全路径
1.命令控制文件 input.ctl内容
如何把文本数据导入Oracle中
控制文件中指定插入数据的方式关键字
insert，为缺省方式，在数据装载开始时要求表为空
append，在表中追加新记录
replace，删除旧记录，替换成新装载的记录
truncate，同上
在 Dos窗口下使用 SQl*Loader 命令实现数据的导入
C:\>sqlldr userid=system/manager@ serviceName control=input.ctl
默认日志文件名为：input.log
默认坏记录文件为：input.bad
create table testone
(
id number, --序号
username varchar2(50),
password varchar2(50),
sj varchar2(20)
);
--导入命令
load data infile 'd:\whx\testoracle.txt' append into table testone fields terminated by
X'09'(id,username,password,sj);
--oracle日志文件
Oracle日志文件管理与查看
--1.查询系统使用的是哪一组日志文件：
select * from v$log;
--2.查询正在使用的组所对应的日志文件：
select * from v$logfile;
--3.强制日志切换：
alter system switch logfile;
--4.查询历史日志：
select * from v$log_history;
--5.查询日志的归档模式：
select dbid,name,created,log_mode from v$database;
--6.查询归档日志的信息：
select recid,stamp,thread#,sequence#,name from v$archived_log;
--7.增加与删除日志文件组
alter database add logfile group 1
('/home1/oracle/oradata/ora8i/log1a.log'),'/home2/oracle/oradata/ora8i/log1b.log') size 100M;
alter database drop logfile group 1;
--8.增加与删除日志成员
alter database add logfile member '/home1/oracle/oradata/ora8i/log1a.log' to
group 1,'/home1/oracle/oradata/ora8i/log2a.log' to group 2;
alter database drop logfile member '/home1/oracle/oradata/ora8i/log1a.log' ;
--9.日志文件移动
alter database rename file '/home1/oracle/oradata/ora8i/log1a.log'
to '/home2/oracle/oradata/ora8i/log1a.log';
--执行该命令之前必须保证该日志文件物理上已经移动到新目录
--10.清除日志文件
alter database clear logfile '/home1/oracle/oradata/ora8i/log1a.log';
--该命令用于不能用删除组及组成员命令删除日志时使用
---将文本文件/(excel表中)的内容导入到oracle数据库中---可以利用PL/SQL developer
--首先查看oracle数据库的
例子
C:\>sqlldr userid=scott/whx@ORCL control=input.ctl
SQL*Loader: Release 11.1.0.6.0 - Production on星期三 5月 29 17:10:58 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
达到提交点 -逻辑记录计数 8
C:\>
在C盘根目录下面创建一个文件input.ctl
内容如下：
load data infile 'd:\whx\testoracle.txt' append into table testone fields terminated by X'09'(id)
在D盘whx目录下面创建testoracle.txt文件
内容如下：
1
2
3
4
5
6
7
8
- OracleSQL
- ORACLEsql
- oraclesql优化
- oracleSQL优化
- oracleSQL编辑语句
- 000015.OracleSQL执行计划
- ORACLESQL语句分类
- 常用的OracleSQL
- oracleSQL基本查询
- OracleSQL运算符优先级
- Oraclesql数据库连接笔录
- ORA 各种oraclesql错误
- oracleSQL优化方案
- OracleSQL内置函数大全之一
- OracleSQL常用(以及经典)语句
- OracleSQL Trace 和 10046 事件
- oraclesql分页最高效率写法
- OracleSQL 插入和查询数据
- MySQL命令行导出数据库
- openCV中的Mat、IplImage数据的转换
- 如何让您的PC 保持顺畅运行
- C++基础学习系列第一部分——再议数组与指针
- 博客迁移
- ORACLEsql
- [菜鸟每天来段CUDA_C]CppIntegration在C++程序中引用CUDA程序
- C与汇编（转）
- 突然不做开发了，蛮意外的
- CentOS下用Tomcat+Zookeeper+Nginx+Solr完美搭建SolrCloud平台（四）
- ios下使用rsa算法
- Linux用户管理&文件权限&常用管理命令
- SSH项目中遇到的不细心错误!
- Kindle Fire 刷机至Android 4.2.2
