Oracle语法

*****************

文件操作命令

*****************

 

===========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 connect to xiaoming

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

number(5)

-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 (selct * 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 分页）

select * from t_xiaoxi where rowid in(select rid from (select fownum rn,rid from(select rowid rid,cid from 

t_xiaoxi order by cid desc) where rownum < 10000) where rn > 9980) roder by cid desc;

 

=============条件查询（合并查询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:在指定位置显示小数点符号

<---------------------------->

 

==============sys_context('userenv','db_name')

 

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; then

goto end_loop;

end if;

i:=i+1;

end loop;

<<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();