- 文件操作命令
- 登录操作命令
- 表的管理
- 增删查改控制
- sql函数
- 事务管理
- 数据库管理
- 数据字典
- 表空间和数据文件
- 建表约束
- 建表索引
- 建表角色
- pl/sql
- 数据恢复
- java操作oracle数据库
- java调用存储过程
文件操作命令
*****************
===========start和@
说明：运行sql脚本
案例：sql>@ d:\a.sql 或者 sql>START d:\a.sql
===========edit
说明：该命令可以编辑指定的sql脚本
案例：sql>edit d:\a.sql
===========spool
说明：该命令可以将sql*plus屏幕上的内容输出到指定的文件中去
*****************
登录操作命令
*****************
===========清屏命令
clear;
===========设置行长
set linesize 120;
===========打开时间 如：（10:53:05 SQL>)
set time on
===========打开执行语句时间
set timing on;
===========显示用户
show user
===========连接用户(conn,connect无区别)
conn system/manager
connect system/manager
conn system/manager as sysdba
===========创建用户(必须是有创建用户权限的登陆者，才能创建)
create user xiaoming identified by m123;
===========更改密码(在oracle自带的SQL Plus下使用)--回车后，根据提示做
password xiaoming;
passw
===========删除用户
--单一删除用户
drop user xiaoming
--连带把用户建的表也删除
drop user xiaoming cascade
===========授权用户(connect普通，dba最大，resource表空间建表)
grant dba to xiaoming
grant resource to xiaoming
grant create session bo xiaoming with admin option
===========授权查询其他表(在sys,system,emp表所属人可以授权)
--普通授权
grant select on emp to xiaoming授予查询权限
..... update .. ... .. ........授予更新权限
..... all .. ... .. ........授予所有权限
--指定精细授权
grant update on emp(sal,ename) to monkey;
--授权延续
grant select on emp to blake with grant option
===========对象授权(让下一用户，可以继续传递这种权限给其他人)
grant select on emp to xiaoming with grant option
===========系统授权(对象和系统有区别一个是grant一个是admin)
system给xiaoming授权时：
grant connect to xiaoming with admin option;
grant create view to ken;
grant create session,create table to tom with admin option;
===========收回对象权限(在sys,system,emp表所属人可以授权)
revoke select on emp to xiaoming撤销查询权限
..... update .. ... .. ........撤销更新权限
..... all .. ... .. ........撤销所有权限
===========收回系统权限
revoke create session from ken;
--把对象的回收权限给用户(级联回收)
revoke select on emp from blake
<------------------------------->
create session 连接数据库
create view 键视图
create procedure 键过程，包，函数
create cluster 键簇
create table 建表
create public synonym 建同义词
create trigger 建触发器
<-------------------------------->
对象权限可以授予用户，角色，public
在授予全县时，如果带with grant option
选项，则可以将该权限授予其他用户。
但要注意with grant option选项不能被
授予的角色。
<--------------------------------> ============账户锁定
--设定文件
create profile lock_account
limit failed_login_attempts 3 password_lock_time 2;
--把锁赋给用户
alter user xiaoming profile lock_account
--把用户解锁
alter user xiaoming account unlock
===========终止口令(设定用户每10天延迟两天必须改密码)
--设定文件
create profile myprofile
limit password_life_time 10
password_grace_time 2;
--口令赋给用户
alter user xiaoming profile myprofile
===========口令历史(指定时间内必须更改密码，而且不能和以前一样)
--设定文件
create profile password_history limit
password_life_time 10 password_grace_time 2
password_reuse_time 10
--口令赋给用户
alter user xiaoming profile password_history
===========删除设定文件
drop profile filename
===========查询非本用户下的授权表
select * from scott.emp;
===========看表结构
desc test
*****************
表的管理
*****************
============字符型
char 定长 最大2000字符
char(10) 那么用不用10个都是占10个，但是查询时候快，适合做身份证
varchar2(20) 用多少空间算多少空间
============数字类型
number
-10的38次方到10的38次方
number(5,2)表示一个小数的5位整数，2位小数
-999.99---999.9
-99999 - 99999
=============日期类型
date 包括年月日时分秒
timestamp 更加精确的
=============图片、声音、视频
blob 二进制数据 可以存放图片/声音 4G
=============创建表(有resource权限才能创建表)
create table test(userId varchar2(30),userName varchar2(30))
=============添加一个字段
alter table student add(classid number(2));
=============修改字段长度
alter table student modify (xm varchar2(30));
=============修改字段类型或者名字
alter table student modify (xm char(30));
=============删除一个字段
alter table student drop column sal;
=============修改表的名字
rename student to stu;
=============删除表
drop table student;
=============插入数据（时间类型需注意）
insert into student values(1,'小明','男','11-12月-1997',2453.2,14);
=============更改日期格式
alter session set nls_date_format='yyyy-mm-dd';
--也可以插入的时候设置
('13-03月-1988')
to_date('1988-12-12','yyyy-mm-dd')
to_date('1989/12/12','yyyy/mm/dd')
=============指定字段插入
insert into student(studentNumber,studentName,sex) values(123,'红红',女);
*****************
增删查改控制
*****************
=============疯狂复制插入
insert into student(studentNumber,studentName,sex) select * from student;
=============插入多行数据
insert into kkk (Myid,myname,mydept) select empno,ename,deptno from emp where deptno = 10;
=============删除数据
--删除所有记录，表结构还在，写日志，可以回复，速度慢
delete from student;
--删除表的结构和数据
drop table student;
--删除一条记录
delete from student where xh='1001';
--删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快。
truncate table student;
==============改一个字段
update student set sex='女' where xh='1001';
==============子查询，多条件，更新
update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';
==============修改多个字段
update student set sex='男',birthday='1989-04-01'
where xh='1001';
=============查询为空与不为空
select * from student where birthday is null;
select * from student where birthday is not null;
=============查询指定列
select ename,sal,job from emp;
=============条件查询（where）
select ename,sal,job from emp where ename='SMITH';
=============条件查询（where,<）
select ename,hiredate from emp where hiredate>'1-1月-1982';
=============条件查询（where,and,< = >）
select ename,sal from emp where sal>=2000 and sal<=2500;
=============条件查询(like % _)..一个下划线代表一个字符
select ename,sal from emp where ename like '__O%';
=============条件查询（like %）
select ename,sal from emp where ename like 'S%';
=============条件查询（or in）..in可以代替or使用
select * from emp where empno=123 or empno=456 or...;
select * from emp where empno in(123,456,...);
=============条件查询（null,not null）..空查询
select * from emp where mgr is null;
select * from emp where mgr is not null;
=============条件查询（or and）
select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';
=============条件查询（order by）按照 什么什么排序 顺序排列
select * from emp order by sal;
=============条件查询（order by desc）倒叙
select * from emp order by sal desc;
=============条件查询（order by asc desc）一个升序一个降序
select * from emp order by deptno asc,sal desc;
=============条件查询（order by as）多字段 as 别名 按照别名排序
select ename,(sal+nvl(comm,0))*13 as "年薪" from emp order by "年薪"；
=============条件查询（数据分组max min avg sum count）
select max(sal),min(sal) from emp;
=============条件查询（max min 子查询）
select ename,sal from emp where sal=(select max(sal) from emp);
select ename,sal from emp where sal=(select max(sal) from emp) or sal = (select min(sal) from emp);
=============条件查询（单表 单行子查询 =）
select * from emp where deptno = (select deptno from emp where ename = 'SMITH');
=============条件查询（单表 多行子查询 in）
select * from emp where job in (select distinct job from emp where deptno = 10);
=============条件查询（单表 多行子查询 all）大于all大于全部
select ename,sal,deptno from emp where sal > all(select sal from emp where deptno = 30);
=============条件查询（单表 多行子查询any）大于any 值大于期中一个就可以了
select ename,sal,deptno from emp where sal > any(select sal from emp where deptno = 30);
=============条件查询（单表 多行 多列查询）
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
=============条件查询（单表 多行 max）
select ename,sal,deptno from emp where sal > (select max(sal) from emp where deptno = 30);
=============条件查询（from 里面使用子查询）内嵌试图
select a1.ename,a1.sal,a1.deptno,a2.mysal
from emp a1,(select deptno,avg(sal) mysql from emp) group by deptno) a2
where a1.deptno = a2.deptno and a1.sal > a2.mysal;
=============条件查询（三种分页之 rownum）
--查询视图
select * from emp;
--第一次分页（分尾）
select a1.*,rownum rn from (select * from emp) a1 where rownum <=10;
--第二次分页（分头）
select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum <= 10) where rn >= 5;
select * from (select a1.*,rownum rn from emp a1 where rownum < 10) where rn >=5;
select a2.* from (select a1.*,rownum rn from emp a1 where rownum < 10) a2 where rn >=5;
=============条件查询（三种分页之 rowid 分页）
--rowid分页，第一步
select rowid rid,sal from emp order by sal desc;
--rowid分页，第二步
select rownum rn,rid from(select rowid rid,sal from emp order by sal desc) where rownum<10;
--rowid分页，第三步
select rid from(select rownum rn,rid from(select rowid rid,sal from emp order by sal desc) where rownum<10 where="" rn="">5;
--rowid分页，第四步
select * from emp where rowid in(select rid from(select rownum rn,rid from(select rowid rid,sal from emp order by sal desc) where rownum<10 where="" rn="">5);
=============条件查询（合并查询union,union all,intersect,minus）
select ename,sal,job from emp where sal > 2500 union
select ename,sal,job from emp where job='manager';
=============条件查询(查询结果，创建表)
create table myemp2 (id,ename) as select empno,ename from emp;
=============条件查询(avg)平均查询
select avg(sal) from emp;
=============条件查询(avg 子查询)
select * from emp where sal > (select avg(sal) from emp);
=============条件查询（group by）【 group by， having， order by】使用顺序
--如果下面的deptno不是在后面group by中出现，那么就得删了它，因为它和前面的函数不属于同一列
select avg(sal),max(sal),deptno from emp group by deptno;
select avg(sal),max(sal),deptno,jop from emp group by deptno,job;
=============条件查询（having）having 对分组进行筛选
select avg(sal),max(sal),deptno from emp group by deptno having avg(avg) > 2000;
=============条件查询（多表查询）
--查询两个表 建立= 以作链接
select e.ename,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno;
=============条件查询（多表 and）
select e.ename,e.sal,d.name from emp e,dept d where e.deptno = d.deptno and d.deptno = 10;
=============条件查询（多表 between）between 在什么范围内
select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.losal and a2.hisal;
=============条件查询（多表 order by）
select a1.ename,a2.dname,a1.sal from emp a1,empt a2 where a1.deptno = a2.deptno order by a1.deptno;
=============条件查询（一表 多用 and）
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr = boss.empno;
select worker.ename,boss.ename from emp worker,emp boss where worker.mgr = boss.empno and worker.ename='FORD'
=============算数查询
select sal*13,ename from emp;
=============查询取别名
select sal*13 "年工资" ,ename from emp;
=============查询nvl处理null函数
select sal*13+nvl(comm,0)*13 "年工资",ename,comm from emp;
=============删除重复的
select distinct deptno,job from emp;
*****************
sql函数
*****************
==============字符函数
--将字符串转化为小写
lower(char)
select lower(ename) from emp;
--将字符串转换为大写
upper(char)
select upper(ename) from emp;
--首字母转换为大写
initcap
select initcap(ename) from emp;
--返回字符串的长度
length(char)
select * from emp where length(ename)=5;
--取字符串的字串(从1开始取 取3个字符)
substr(char,m,n)
select substr(ename,1,3) from emp;
--替换函数
replace(char1,search_string,replace_string)
select replace(ename,'A','老鼠') from emp;
--找字串的位置
instr(char1,char2,[,n[,m]])
--合并字符串（||合并符号）
select upper(substr(ename,1,1)) ||lower(substr(ename,2,length(ename)-1)) from emp;
==============数学函数
包括cos,cosh,exp,ln,log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round
--round(n,[m])四舍五入
select round(sal) from emp;
select round(comm,1),comm from emp;
意思：55.7 55.66
--trunc(n,[m])截取数字
select trunc(comm,1),comm from emp;
意思：55.6 55.66
--mod(m,n)取摸
select sal(10,2) from emp;
--floor(n)返回小于或是等于n的最大整数
select floor(comm),comm from emp;
--ceil(n)返回大于或是等于n的最小正数
select ceil(comm),comm from emp;
--abs(n) 返回数字n的绝对值
--select abs(-13) from dual;
--acos(n) 返回数字的反余旋
--atan(n) 返回数字反正切
--cos(n)
--exp(n) 返回e的n次幂
--log(m,n) 返回对数值
--power(m,n)返回m的n次幂
==============日期函数
--oracle默认日期格式
dd-mon-yy 既12-7月-1988
--sysdate返回系统时间
select sysdate from emp;
--hirdate(xx,8)加上8个月
select * from emp where sysdate > add_months(hiredate,8);
select trunc(sysdate-hiredate) "入职天数",ename from emp;
--last_day(hiredate)返回本月的最后一天
select hiredate,last_day(hiredate) from emp;
select hiredate,ename from emp where last_day(hiredate)-2=hiredate;
==============转换函数(类型之间自动转换)
--例子
create table t1(id int);
insert into t1 values('10')
create table t2(id varchar2(10));
insert into t2 values(1);
--to_char(hiredate,"YYYY-mm-dd hh24:mi:ss")
select ename,to_char(hiredate,'YYYY-mm-dd hh24:mi:ss') from emp;
--to_char(sal,"L99999.99")
select ename,to_char(hiredate,'YYYY-mm-dd hh24:mi:ss'),to_char(sal,'L99999.99') from emp;
找出1980入职的
select * from emp where to_char(hiredate,'YYYY') = 1980;
<---------------------------->
yy:两位数字的年份 2004--04
yyyy:四位数字的年份 2004年
mm：两位数字的年份
dd：2位数字的天 30-->30
hh24：8点 --》20
hh12：8点 --》08
mi，ss --》显示分钟\秒
<---------------------------->
9:显示数字，并忽略前面0
0:显示数字，位数不足用0补充
.:在指定位置显示小数点
,:在指定位置显示逗号
$:在数字前面加美元*to_char(sal,'$99,999.99')*
L:本地的字符集符号
C:国际货币符号
G:在指定位置显示组分隔符
D:在指定位置显示小数点符号
<---------------------------->
1) terminal:当前回话客户所对应的中断标识符
2) lanuage:语言
3) db_name:当前数据库名称
4) nls_date_format:当前回话客户所对应的日期格式
5) session_user:当前回话客户所对应的数据库用户名
6) current_schema:当前回话客户所对应默认方案名
7) host:返回数据库所在主机的名称
查询所使用的是哪个数据库
select sys_context('userenv','db_name') from dual;
*****************
事务管理
*****************
=============提交事务（让事务提交，也就是删了保存点）
commit
=============建立保存点
savepoint a1;
savepoint a2;
=============返回事务
--返回第一个保存点
rollback to a1;
--返回第二个保存点
rollback to a2;
--返回所有设置的保存点
rollback;
==============只读事务
set transaction read only;
*****************
数据库管理
*****************
=========登录方式
sys 必须以sysdba 或者 sysoper形式登录 不能以normal方式登录数据库
system 可以以normal方式登录
=========关闭数据库启动数据库
--关闭
shutdown;
--开启
startup;
=========显示参数
show parameter;
=========备份和恢复（导出导入）oracle->bin->exp.exe(并把此文件路径复制给dos下执行)
--逻辑备份（open下）
导出自己的一张表
exp userid=scott/tigger@orcl tables(emp) file=d:\e1.demp
导出自己的多张表
exp userid=scott/tigger@orcl tables(emp,dept) file=d:\e1.demp
导出其他方案的表
exp_full_database的权限，比如system就可以导出scott的表
exp userid=system/manager@orcl tables=(scott.emp) file=d:\e1.demp
导出表结构(无数据只有结构)
exp userid=scott/tigger@orcl tables=(emp) file=d:\e1.demp rows = n
直接导出方式（常用作处理大表）
exp userid=scott/tigger@orcl talbes=(emp) file=d:\e1.demp direct = y
导出方案
exp userid=scott/tigger@orcl owner=scott file=d:\e1.demp
导出其他方案
exp userid=system/manager@orcl owner(system,scott) file=d:\e1.demp
导出数据库
exp userid=system/manager@orcl full=y inctype=complete file=d:\e1.demp
--恢复导入imp
导入自己表
imp userid=scott/tigger@orcl tables=(emp) file=d:\e1.demp
导入表到其他用户
imp userid=system/manager@orcl tables
导入表的结构
只导入表的结构而不导入数据
imp userid=scott/tigger@orcl tables=(emp) file=d:\xxx.dmp e1.demp rows=n;
导入数据
如果对象（如比表） 已经存在可以只导入表的数据
imp userid=scott/tigger@orcl tables=(emp) file=d:\xxx.dmp ignor=y;
导入方案
imp userid=scott/tigger file=d:\xxx.dmp;
imp userid=system/manager file=d:\xxx.dmp fromuser=system touser=scott
导入数据库
imp userid=system/manager full=y file=d:\xxx.dmp;
<------------------------->
导出分为：导出表，导出方案，导出数据库三种方式
导出exp命令，常用选项
userid:用于指定执行导出操作的用户名，口令，连接字符串
tables:用于指定执行导出操作的表
owner: 用于指定执行导出操作的方案
full=y:用于指定执行导出操作的数据库
inctype:用于指定执行导出操作的增量类型
rows:用于指定执行导出操作是否要导出表中的数据
file:用于指定导出文件名
<------------------------->
数据字典
*****************
==============user_tables
--用于显示当前用户所拥有的所有表，它至返回用户所对应方案的所有表
select table_name from user_tables;
--用于显示当前用户可以访问的所有表，它不仅会返回当前用户方案的所有表，
还会返回当前用户可以访问的其他方案表
select table_name from all_tables;
--他会显示所有方案拥有的数据库表要求是dba权限
select table_name from dba_tables;
==============用户名，权限，角色
--dba_users
--dba_sys_privs
可以显示用户所具有的系统权限
--dba_tab_privs
可以显示用户所具有的对象权限
--dba_col_privs
可以显示用户所具有的列权限
--dba_role_privs
可以显示用户所具有的角色
--查询结构
desc dba_users;
--查询用户
select username from dba_users;
--查询密码
select password from dba_users;
--查询scott用户所具有的权限步骤
-查询dba_role_privs视图结构（必须有dba权限才能查询。比如system可以查询）
desc dba_role_privs;
-查询scott权限
select * from dba_role_privs where GRANTEE='SCOTT';
==============查询测试案例
--查询oracle中所有的系统权限，一般是dba
select * from system_privilege_map order by name
--查询oracle中所有的角色，一般是dba
select * from dba_roles
--查询oracle中所有的对象权限，一般是dba
select distinct privilege from dba_tab_privs
--查询数据库的表空间
select tablespace_name from daba_tablespaces
--包含多少系统权限
select * from dba_sys_privs where grantee='DBA'
select * from role_sys_privs where role = 'DBA'
--包含多少对象权限
select * from dba_tab_privs where grantee='角色名（connect）'
--查询某个用户具有什么样的角色
select * from dba_role_privs where grantee='SCOTT'
--显示当前用户可以访问的所有数据字典视图
select * from dict where comments like '%grant%'
--显示当前数据库的全称
select * from global_name
--约束信息查询 数据字典user_constraints可以显示约束信息
select constraint_name,constraint_type,status,validated from user_constraints where table_name='表名';
--显示约束列
select column_name,position from user_cons_columns where constraint_name='约束名';
*****************
表空间，数据文件
*****************
==============表空间包括
--索引表空间
--undo表空间
--临时表空间
--非标准块的表空间
==============建立表空间
建立表空间是使用create tablespace 命令完成的需要注意的是，一般情况下，建立表空间是特权用户或者dba来执行的，如果用其他用户来创
建表空间，否则用户要具有create tablespace的系统权限
==============建立数据表空间
在建立数据库后，为便于管理表，最好建立自己的表空间
create tablespace data01 datafile 'd:\test\data01.dbf' size 20m uniform size 128k
==============扩展表空间
1,增加数据文件
sql>alter tablespace sp001 add datafile 'd:\test\sp002.dbf' size 20m
2,增加数据文件的大小
sql>alter database 表空间名 'd:\test\sp01.dbf' resize 20m
3,设置文件的自动增长
sql>alter database 表空间名 'd:\test\sp01.dbf' autoextend on next 10m maxsize 500m;
==============移动数据文件
1,确定数据文件所在的表空间
select tablespace_name from dba_data_files where file_name='d:\test\sp01.dbf';
2,使表空间脱机(确保数据一致性)
alter tablespace sp01 offline;
3,使用命令移动数据文件到指定的目标位置
sql>host move d:\test\sp01.dbf c:\test\sp01.dbf
==============使用数据表空间
create table mypart(deptno number(4),dname,varchar2(14),loc varchar2(13) tablespace data01);
==============表空间状态控制
1,使表空间脱机
alter tablespace 表空间名 offline;
2,使表空间联机
alter tablespace 表空间名 online;
3,使表空间只读(无update,delete,insert功能)
alter tablespace 表空间名 read only;
4,使表空间可读可写
alter tablespace 表空间名 read write;
==============查询表空间下的所有表
select * from all_tables where tablespace_name='表空间名'
==============知道表名，查看该表属于哪个空间
select tablespace_name,table_name from user_tables where table_name='emp';
*****************
约束
*****************
==============约束条件（五种）
--not null
不为空
--unique
唯一但可以为空
--primary key
主键
--foreign key
外键
--check
比如年龄大于一岁小于一百岁的
==============增加约束
--增加not null
使用modify
--增加其他约束
使用add
==============建表实例
--建一个商品表
create table goods (goodsId char(8) primary key,
goodsName varchar2(30),
unitprice number(10,2) check(unitprice > 0),
category varchar2(8),
provider varchar2(30));
--建一个客户表
create table customer(customerId char(8) primary key,
name varchar2(50) not null,
address varchar2(50),
email varchar2(50) unique,
sex char(2) default '男' check(sex in('男','女')),
cardId char(18));
--建立一个购买表
create table purchase(customerId char(8) references customer(customerId),
goodsId char(8) references goods(goodsId),
nums number(10) check(nums between 1 and 30);)
--增加新约束商品名不为空
alter table goods modify goodsName not null;
--增加身份证，不允许重复cardunique 自定义名)
alter table customer add constraint cardunique unique(cardId)
--增加客户住址固定为海淀，朝阳，东城，西城(addresscheck 自定义名)
alter table customer add constraint addresscheck check(address in('东城','西城','朝阳'));
--删除约束
alter table 表明 drop constraint 约束名称
--删除主键约束的时候，可能有错误，比如
alter talbe 表名 drop primary key;
这是因为如果在两张表存在主从关系，那么在阐述主表的主键约束时，必须带上cascade 选项如
alter table 表明 drop primary key cascade;
==============表级定义，列级定义
--表级定义
建表的字段都完后，再写约束条件
create table employee2
(emp_id number(4),name varchar2(15),dept_id number(2),
constraint pk_employee primary key (emp_id),
constraint fk_department foreign key(dept_id),
rererences department4(dept_id));
--列级定义
建表的字段同时，就把约束条件写上(constraint pk_de如果不写，系统会自定义)
create table department4
(dept_id number(2) constraint pk_de primary key,
name varchar2(12),
loc varchar2(12));
*****************
索引
*****************
==============使用原则
--在达标上建立索引才有意义
--在where自居或是连接条件上经常引用的列上建立索引
--索引的层次不要超过4层
==============建立索引的缺点
--建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引
--跟新数据时候，要同意更新索引
==============单列索引
--建索引
create index 索引名 on 表名（列名）
==============复合索引
--建符合索引
create index emp_inx1 on emp(ename,job);
create index emp_idx1 on emp(job,ename);
*****************
角色
*****************
==============connect角色
包括权限
alter session
create cluster
create database link
create session
create table
create view
create sequence
===============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的
特权(启动和关闭数据库)
===============自定义角色
--建立角色（不验证）
如果角色是公用的角色，可以采用不验证的方式建立角色
create role 角色名 not identified;
--建立角色（数据库验证）
采用这样的方式时，角色名、口令存放在数据库中。
当激活该角色时，必须提供口令。在建立这种角色时
需要为其提供口令
create role 角色名 identified by xxx;
================自定义角色授权
--授权需知
给角色授权和给用户授权却别于
系统权限unlimiter tablespace
对象权限with grantoption
选项不能授权角色
--授权步骤
conn system/manger
grant create session to 角色名 with admin option
conn scott/tiger@orcl
grant select on scott.emp to 角色名
grant insert,update,delete on scott.emp to 角色名
=================权限分配给用户（由dba来完成）
conn system/manager
grant 角色名 to blake with admin option
因为给了with admin option
所以有延续分配权限的功能
=================删除角色（dba执行）
conn system/manager
drop role 角色名
=================显示角色信息
--显示所有
select * from dba_roles;
--显示系统具有的系统权限
select privilege,admin_option from role_sys_privs where role='角色名'；
--显示对象权限
通过数据字典视图dba_tab_privs可以查看角色具有的对象权限是列的权限
--显示默认角色
select granted_role,default_role from dba_role_privs where grantee='用户名';
*****************
pl/sql
*****************
=================介绍
--过程，函数触发器是pl/sql编写
--过程，函数，触发器是在oracle中
--pl/sql是非常强大的数据库过程语言
--过程，函数可以在java程序中调用
--提高应用程序的运行性能
--模块化的设计思想[分页，订单...]
--减少网络传输量
--提高安全性能
--移植性不好
=================简单测试案例
--创建表
create table mytest(name varchar2(30),passwd varchar2(30));
--创建过程
create procedure 过程名 is
begin
insert into mytest values('adminone','one');
end;
--替换方式创建
create or replace procedure sp_pro1 is
begin
insert into mytest values('adminone','one');
end;
--调用过程
exec 过程名(如果有参数，就带参数)
call 过程名(如果有参数，就带参数)
--查看错误信息
show error;
=================块hello word
--打开输出
set serveroutput on
--关闭输出
set serveroutput off
--测试
begin
dbms_output.put_line('hello,word');
end;
/
hello,word
=================接收从控制台的变量
--单个信息（declare声明）
declare
v_ename varchar2(20);
begin
select ename into v_ename varchar2(5) from emp where empno=&no;
dbms_output.put_line('雇员号'||v_ename);
end;
/
--多个信息
declare
v_ename varchar2(20);
v_sal number(7,5);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
dbms_output.put_line('雇员名：'||v_ename||'雇员薪水：'||v_sal);
end;
/
--捕获异常（当no不存在数据表中时候，抛出异常）
declare
v_ename varchar2(20);
v_sal number(7,5);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
dbms_output.put_line('雇员名：'||v_ename||'雇员薪水：'||v_sal);
exception
when no_data_found then
dbms_output.put_line('嗨，哥们，你输入有错误！');
end;
/
=================带输入参数的
--定义
create procedure test3(name varchar2,newsal number) is
begin
update emp set sal=newsal where ename=name;
end;
/
--使用
exec test3('SMITH');
=================函数
--函数用于返回特定的数据，当建立函数时，
在函数投不必须包含return子句，而在函数体内
必须包含return语句返回的数据。我们可以使用
create function来建立函数
--案例
创建
create function xf_fun2(xfname varchar2) return
number is yearsal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearsal from emp where ename=xfname;
return yearsal;
end;
使用
sqlplus中
var abc number
call xf_fun2('SCOTT') into:abc;
print abc
java中
select annual_income('SCOTT') from dual;
可以通过rs.getInt(1);得到返回结果
=================包
包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成
--创建包
create package test_package is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end;
--创建包体
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 emp
where ename=name;
return annual_salary;
end;
end;
--调用包的过程或者函数
call sp_package.update_sal('SCOTT',120);
=================pl/sql类型
--标量类型
1：定义一个变长字符串
v_ename varchar2(10)
2：定义一个小数
v_sal number(6,2)
3：定义一个小数并赋值
v_sal2 number(6,2):=5.4
4：定义一个日期类型的数据
v_hiredate date;
5：定义一个布尔变量，不为空
v_valid boolean not null default false;
定长定义案例
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=&no;
v_tax_sal:=v_sal*v_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=&no;
v_tax_sal:=v_sal*c_tax_rate;
dbms_output.put_line('姓名:'||v_ename||'工资:'||v_sal||'交税:'||v_tax_sal)
end;
--复合类型composite
1,pl/sql记录（类似高级语言的类）
declare
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type);
sp_record emp_record_type;
begin
select ename,sal,job into sp_record
from emp where empno=7788;
dbms_output.put_line('员工名：'||sp_record.name);
end;
2,pl/sql表(相当于高级语言里面的数组,但是这里面的下标可以为负数)
declare
type sp_table_type is table of emp.ename%type
index by binary_integer;
sp_table sp_table_type;
begin
select ename into sp_table(0) from emp where empno=7788;
dbms_output.put_line('员工名：'||sp_table(0));
end;
3,游标
declare
type sp_emp_cursor is ref cursor;
test_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open test_cursor for select ename,sal from emp;
loop
fetch test_cursor into v_ename,v_sal;
exit when test_cursor%notfound;
dbms_output.put_line('名字:'||v_ename||'工资:'||v_sal);
end loop;
close test_cursor;
end;
=================各种控制语句
--条件分支
if-then
if-then-else
if-then-elsif-else
--案例（if-then）
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*110% where ename=spName;
end if;
end;
--案例（if-then-else）不等于号<>
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_comm<>0 then
update emp set comm=comm+100 where ename=spName;
else
update emp set comm=comm+200 where ename=spName;
end if;
end;
--案例（if-then-elsif-else）
create or replace procedure sp_pro7(spNo number) is
v_job emp.job%type;
begin
select job into v_job from emp where empno=spNo;
if v_job='PRESIDENT' 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+200 where empno=spNo;
end if;
end;
--循环语句
loop
end loop至少循环一次
while 只有条件为true才执行
--案例（loop *users表需要创建*）
create or replace procedure sp_pro9(spName varchar2) is
v_num number:=1;
begin
loop
insert into users values(v_num,spName);
exit when v_num=10;
v_num:=v_num+1;
end loop;
end;
--案例（while）
create or replace procedure sp_pro10(spName varchar2) is
v_num number:=11;
begin
while v_num<=20 loop
insert into users(v_num,spName);
v_num:=v_num+1;
end loop;
end;
--案例（for）
for i int reverse 1..10 loop
insert into users values(i,'test');
end loop;
end;
--案例（goto，null）
declare
i int:=1;
begin
boop
dbms_output.put_line('输出i='||i);if i=10; thengoto end_loop;end if;i:=i+1;end loop;<>dbms_output.put_line('循环结束');
end;
--案例（分页过程）
-建表
create table book(bookId number,bookName varchar2(50),publishHouse varchar2(50));
-编程过程(in代表这是一个输入参数 out表示输出参数)
create or replace procedure sp_pro11(spBookId in number,spbookName in varchar2,sppublishHouse in varchar2) is
begin
insert into book values(spBookId,spbookName,sppublishHouse);
end;
-java 链接数据库
参照下面
-创建CallableStatement
CallableStatement cs = ct.prepareCall("{call ps_pro11(?,?,?)}");
cs.setInt(1,10);
cs.setString(2,"笑傲江湖");
cs.setString(3,"人民出版社");
cs.execute();
-编写过程（有返回值）
create or replace procedure sp_pro12
(spno in number,spName out varchar2) is
begin
select ename into spName from emp where empno=spno;
end;
-java创建CallableStatement（多个变量也类似）
CallableStatement cs = ct.prepareCall("{call sp_pro12(?,?)}");
cs.setInt(1,7788);
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
cs.execute();
String name=cs.getString(2);
-返回结果集,在该包中,我定义类型
-建包
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
-建存储过程
create or replace procedure testc
(myno in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno=no;
end testc;
-在java中使用
CallableStatement cs = ct.prepareCall("{call sp_pro9(?,?)}");
cs.setInt(1,10);
cs.registerOutParamenter(2,oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet)cs.getObject(2);
while(rs.next()){
System.out.println(rs.getInt(2)+""+rs.getString(2));
}
--案例分页
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
create or replace procedure fenye
(tableName in varchar2,
Pagesize in number,
PageNow in number,
myrows out number,
myPageCount out number,
p_cursor out testpackage.test_cursor
)is
v_sql varchar2(1000);
v_begin number:=(PageNow-1)*Pagesize+1;
v_end number:=PageNow*Pagesize;
begin
v_sql:='select a2.* from(select a1.*,rownum rn from (select * from '||tableName||') a2 where rownum<='||v_end||') a2 where
rn>'||v_begin;
open p_cursor for v_sql;
v_sql:='select count(*) from '||tableName;
execute immediate v_sql into myrows;
if mod(myrows,Pagesize)=0 then
myPageCount:=myrows/Pagesize;
else
myPageCount:=myrows/Pagesize+1;
end if;
end;
--java中使用
CallableStatement cs = ct.prepareCall("{call fenye(?,?,?,?,?,?)}");
cs.setString(1,"emp");
cs.setInt(2,5);
cs.setInt(3,1);
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
int rowNum = cs.getInt(4);
int pageCount = cs.getInt(5);
ResultSet rs = (ResultSet)cs.getObject(6);
System.out.println("rowNum="+rowNum);
System.out.println("总页数"+pageCount);
while(rs.next()){
System.out.println(rs.getInt(1));
}
=================例外处理
--预定义例外(提供了二十多个例外)
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno=&gno;
dbms_output.putline('名字'||v_ename);
exception
when no_data_found then
dbms_output.put_line('编号没有！');
end;
--其他预定义例外
login_denide
当非法登录时，会出发该例外
not_logged_on
如果用户没有登录就执行dml操作，会出发该例外
storage_error
如果超出了内存空间或是内存被损坏，就触发该例外
timeout_on_resource
如果oracle在等待资源时，出现了超时就触发该例外
--非预定义例外
--自定义例外
create or replace procedure ex_text(spNo number)
is
myex exception;
begin
update emp set sal=sal+1000 where empno=spNo;
if sql%notfound then
raise myex;
end if;
exception
when myex then
dbms_output.put_line('没有用户被更新');
end;
<------------------------>
case_not_found
如果when字句中没有包含必须
的条件分支，就会触发
case_not_found的例外
cursor_already_open
当重新打开已经打开的游标时，
会隐含的出发例外
dup_val_on_index
在唯一索引所对应的列上插入
重复的值时，会隐含的出发例外
invalid_cursor
当视图在不合法的游标上执行
操作时，会触发该例外
invalid_number
当输入的数据有误时，
会触发该例外
too_many_rows
当执行select into 语句时，
如果返回超过一行，就报错。
zero_divide
当执行2/0就会抛出异常
value_error
如果变量的长度不足，就会异常
<------------------------>
=================oracle视图
视图是一个虚拟的表，其内容有查询定义，同真是的表一样，视图包含一些列带有名称
的列和行数据。但是，视图并不在数据库中以数据值集形式存在。
--创建视图
create view myview as select * from emp where sal<1000;
select * from myview;
--创建多表为一个视图
create view myview2 as select emp.empno,emp.ename,dept.dname from emp,dept where emp.deptno= dept.deptno;
--删除视图
drop view 视图名
--视图只可读[with read only]
create view 视图名 as select 语句[with read only]
=================编写规范
--注释
单行注释 --
多行注释 /*...*/
--表示符号的命名规范
定义变量，用v_作为前缀 v_sal
定义常量，用c_作为前缀 c_rate
定义游标，用_cursor作为后缀emp_cursor
定义例外，用e_作为前缀 e_error
*****************
数据恢复
*****************
==============1:建立回滚点
savepoint aa;
==============2:删除数据(可回复删除)
delete from student;
==============3:恢复数据
rollback to aa;
==============自增长归零
alter table onehref auto_increment = 0;
*****************
java操作oracle数据库
*****************
==============jdbc_odbc连接
--第一步
控制面板->管理工具->数据源ODBC->添加Oracle in OraDb10_home1->完成->OK->确定
--第二部java代码
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection ct = DriverManager.getConnection("jdbc:odbc:test","scott","tigger");
==============jdbc桥链接
--第一步
加载jar包
--第二步
import java.sql.*;
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tigger")
==============java里面控制事务
--设置不能默认提交（默认不让执行一句提交一句）
ct.setAutoCommit(false);
--提交事务（执行完sql语句提交）
ct.commit();
--异常回滚(在发生异常里面写)
ct.rollback();
*****************
java调用存储过程
*****************
//连接部分在上面
CallableStatement cs = ct.prepareCall("{call plsqltest(?,?)}");
cs.setString(1,"SMITH");
cs.setInt(2,10);
--执行
cs.execute();