Oracle

第一章 2

如何启用oracle： 2

如果不知道用户名、密码如何登录系统 2

第二章  数据类型 3

Oracle中的数据类型分类: 4

上课SQL文件： 5

检查约束check 6

主外键关联 6

第三章 运算符 6

多表连接查询: 7

子查询： 8

第四章 函数 9

日期函数: 9

字符函数: 9

数字函数 10

转换函数 10

分析函数 11

分组函数 12

第五章 数据库对象 12

同义词  synonym 12

视图:  view 13

序列  sequence 13

索引   index 14

锁  lock 14

第六章  PL/SQL 编程 15

函数 16

if 语句 16

循环结构 17

存储过程 19

带输出参数 20

带输入输出参数 21

游标  cursor 21

静态游标--->  隐式游标 22

静态游标---->显示游标   (用户自己创建) 22

动态游标: 23

包   package 24

触发器   trigger 24

行级触发器 25

表级触发器 26

模式触发器 26

第一章 

oracle是美国甲骨文公司开发的数据库产生

oracle的版本主要分为:

oracle 8i --------Internet

oracle 9i

oracle 10G--------Grid(网格)

Oracle 11G

Oracle 12C

安装oracle时，安装目录，不能包含任何中文字符

如何启用oracle：

第一步：先启动oracle的服务

OracleServiceORCL  ------------oracle实例

-----该服务启动完成以后，oracle数据库可以单独使用，但是不能用程序连接

OracleXXXXXXListener

-------该服务启动完成以后，oracle的数据库就可以使用程序连接

oracle数据库服务器包含两个部分的内容:

1、oracle数据库 (用于存储数据信息)

2、oracle实例  (相当于是一个仓库管理员,启动它以后，可以操作oracle数据库中存放的信息)

oracle数据库服务器 =  oracle数据库  +  oracle实例

oracle数据库中，主要存放三种文件:

1、数据文件 (存放数据、约束、主外键、序列、同义词、存储过程、触发器、游标....)

2、日志文件 (存放的是系统日志信息)

3、操作文件 (存放数据文件与日志文件的物理位置)

oracle的几个用户权限

sys--------------------(SYSDBA)     数据库管理员(拥有数据库的最高权限)

system----------------(SYSOPER) 数据库操作员(它的权限仅次于管理员)

-----------------------(NORMAL) 普通用户

如果不知道用户名、密码如何登录系统

1、当前的操作系统的登录用户的级别必须是:administrator

2、采用匿名登录的方式登录系统  :              sqlplus /nolog

3、切换到sysdba权限   conn /as sysdba;  

4、创建表空间:

##语法

create tablespace  表空间的名称  datafile '路径' size 10m autoextend on next  5m maxsize 100m;

例：

create tablespace  vincentSpace datafile 'f:/oracle/vincentspace.dbf' size 20m autoextend on next 10m  maxsize 100m;

5、创建临时表空间

##语法:

create  temporary tablespace  vincentTempSpace tempfile 'f:/oracle/vincentTemp.dbf'size 10m autoextend  on next 5m  maxsize 50m;

6、创建用户名以及密码，并且指定该用户的信息存到哪一个表空间，以及它的临时表空间

##语法：

create user 用户名 identified by 密码 default tablespace vincentSpace  temporary tablespace vincentTempSpace ;

例子:

create user xzc identified by xzc default tablespace vincentSpace  temporary tablespace vincentTempSpace ;

7、授予vicent登录数据库的权限

grant connect to 用户名;           grant connect to xzc;

8、授予进行数据库操作的权限

grant resource to 用户名;        grant resource to xzc;

步骤：

1、sqlplus /nolog  匿名登录

2、conn /as sysdba  切换到dba权限

3、create tablespace myspace datafile 'd:/my.dbf' size 10m autoextend on next 5m maxsize 50m;

创建表空间

4、create  temporary tablespace mytempspace tempfile 'd:/mytemp.dbf' size 10m;

创建临时表空间

5、查看有哪些表空间

select tablespace_name from dba_tablespaces;

6、删除表空间

drop tablespace 表空间名称;

7、创建用户

create user hello identified by hello default tablespace 表空间名称 temporary tablespace 临时表空间名称;

8、查看用户   select username from dba_users;

9、删除用户    drop user  xzc;

10、修改密码  alter user  用户名 identified  by  密码;

11、授权   grant connect to 用户名;        grant resource to xzc;

12、撤销权限  revoke connect from 用户名; revoke resource from xzc;

13、切换身份  conn  用户名/密码

14、查看表的结构     desc 表名;

15、在oracle数据库中，做的增、删、改操作，都必须做commit操作，才会将数据保存到数据为中

16、导出数据、导入数据

17、查询所有用户表      select table_name from user_tables;

18、把表bank 的权限给 scott用户 grant all on bank to scott;

19. 把dba权限给某个用户：grant dba to test1;

--scott用户下面包含的表,主要是一些测试数据,tiger

锁定用户:  alter user 用户名 account  lock;

解锁用户： alter user 用户名 account unlock;

oracle中导出数据:

exp

oracle中导入数据:

imp

*****************注意：导入或导出的时候，不需要登录到oracle,在控制台下操作即可

*****************注意： oracle有两个端口，用程序连接的端口号是:1521

  它还占用了8080端口

oracle的连接字符串:

mysql----url:             jdbc:mysql://localhost:3306/数据库名称

oracle---url:             jdbc:oracle:thin:@localhost:1521:实例名称

mysql---驱动字符串:       com.mysql.jdbc.Driver

oracle----                oracle.jdbc.driver.OracleDriver

创建一张表inf

   create table inf

   (

      Id  int  primary key,

      name  varchar(20),

     age int

);

第二章  数据类型

查看表空间名称

select tablespace_name from dba_tablespaces;

查看有哪些用户

select username from dba_users;

修改用户密码

alter user 用户名 identified by 新密码;

创建新用户

create user 用户名 identified by 新密码 default 表空间 临时表空间

授权用户

grant connect to 用户;

grant resource to 用户;

撤销权限

revoke connect from 用户;

conn 用户名/密码

默认情况下,哪一个用户创建的数据,只能自己使用,但经过授权以后,别的用户也可以使用

授权的方式有两种:

1、以dba的身份直接授权

2、哪一个用户创建的数据，该用户也可以授权给其他用户

以dba的身份授权:      

grant insert on  vincent.test to  jor;

grant delete on  vincent.test to  jor;

grant update on  vincent.test to  jor;

grant select on  vincent.test to  jor;

grant all on  vincent.test to  jor;

以dba的身份撤销权限:

revoke insert on vincent.test from jor;

revoke all on vincent.test from jor;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

以创建者的身份来授权:

grant insert on  test to  jor;

查看删除的表：

show recyclebin;

恢复删除的表

flashback table info3 to  before drop

删除数据，不进回收站

drop table 表名 purge

查看当前表空间中，有哪些表

select table_name from user_tables;

Oracle中的数据类型分类:

1、数值类型    number

2、字符类型   char  varchar2

3、日期类型   date

4、大对象类型  clob   blob  bfile

5、特殊类型

@@@@@@@@@@@数值类型:

int表示整数

float表示小数

推荐的数值类型是:  number

id number:  表示定义一个数值类型的字段id，默认长度为38位长度

id number(11) 表示id是一个数字类型，最大长度为11位

id number(10,2)  表示id是一个数值类型，可以保留小数，小数点后最多保留两位有效位数

@@@@@@@@@@@@@@@@@@

字符类型:  char  varchar,nchar,nvarchar

oracle推荐的字符类型是:   varchar2 与 char

name varchar(20)  ---定义一个字段name为字符类型，最大长度为20个字符，如果实际存的长度不到20字符，系统将会按照实际存储的大小收缩空间

-----------优点：节约存储空间

-----------缺点: 由于每一次存储以后，系统都需要重新判断存的数据是否占满空间用于决定是否释放多于空间，所以，相对会消耗更的资源

gender char(2) ---定义一个字段gender为字符类型，占用两个字符空间,不管空间是否占满，空间大小都不再改变

       ---------缺点:相对浪费空间,空间不会根据实际存入的内容进入缩放

       --------优点：存了数据以后，它不会检查实际占用空间，所以，速度相对较快

如果可以确定存储的数据长度，应该使用char

如果不确定存储的数据长度，应该用varchar

char与varchar都需要两个字节长度才可以存储一个汉字

nchar与nvarchar这两种类型，一个长度即可存储一个汉字

varchar 与  varchar2的区别:

都表示字符类型，varchar它是所有数据库必须要有的一种类型

varchar2才是oracle自己的字符数据类型，它的兼容性会更好

@@@@@@@@@@@@@@@@

日期类型  date

bir date

insert into inf values(1,'bruce',date'2014-10-10');

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

大对象类型:  最大容量可以存储4GB容量

CLOB  文本大对象

BLOB  二进制大对象

BFILE 文件大对象

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

特殊类型

rownum与rowid (伪列)

rowid-----它用于显示当前这一行数据在oracle数据库中的物理位置

rownum----它用于给数据表中的每一行数据，生成一组连续的编号

数值类型：number

字符类型: varchar2  char

日期类型:  date

大对象类型:  clob,blob,bfile

特殊类型:  rowid  rownum

上课SQL文件：

create table inf

(

       id number(11) primary key,

       name varchar2(20) not null,

       age number(11)

)

drop table inf;   删掉表格

truncate table inf;   清空表格

--oracle中,没有自动增长列,但是通过序列,可以实现同样的效果    sequence

create sequence inf_seq start with 1 increment by 1;

--创建一个序列,名称是inf_seq,初始从1开始,每次递增1个数

drop sequence inf_seq;--删除序列

insert into inf values(inf_seq.nextval,'chris',23);

insert into inf values(inf_seq.nextval,'bruce',24);

insert into inf values(inf_seq.nextval,'jack',25);

insert into inf values(inf_seq.nextval,'tom',26);

select * from inf;

drop table inf;

create table province

(

       pid number(11) primary key,

       pname varchar2(20)

)

insert into province values(1,'湖北省');

insert into province values(2,'广东省');

检查约束check 

/*

id  主键    

name 非空  

age 必须在1-120岁之间    

gender 必须是'男'或'女'   

address  默认在武汉市

idCard 必须是18位,前17位是数字,最后一位可以是x,y,或数字      

tel  满足电话号码的基本格式       

*/

insert into myuser3 values(1,'wesley',18,'男','济南市',123456789012345678,18621624530,1);

create table inf

(

       id number(11) primary key,

       name varchar2(20) not null,

       age number check(age between 1 and 120),

       gender char(2) check(gender='男' or gender='女'),

       address varchar2(20)  default 济南市

       idCard char(18) check(regexp_like(idcard,'^[0-9]{18}$|^[0-9]{17}(x|y)$')),

       Tel varchar2(20) check (regexp_like(tel,'^1(3,5,8)[0-9]{9}$|^[0-9]{3}-[0-9]{8}$|^[0-9]{4}-[0-9]{7,8}$')),

       pid number(11) references province(pid)  

)

主外键关联        

pid number(11) references province(pid)

insert into inf values(3,'jack',23,'男',default,'4444','028-12345678',4);

--正则表达式的用法:

       字段  varchar2(20) check(regexp_like(字段名,正则表达式的规则))

在oracle中的正则表达式的每一个规则,要以 ^ 开始,以$结束                                     

^1(3,5,8)[0-9]{9}$|^[0-9]{3}-[0-9]{8}$|^[0-9]{4}-[0-9]{7,8}$

第三章 运算符

1、连接运算符  ||

2、关系运算符  >  >=  <  <=  ==  !=  <>    between   in   not in

3、算术运算符  +  -  *  /

4、逻辑运算符  and   or

5、集合运算符   union     union all    intersect

语句的分类:

ddl  数据定义语言  create   drop alter

dml  数据操作语言  insert  delete update  select

dcl  数据控制语言  grant revoke

多表连接查询:

     自连接(natural join): 两张表必须有相同的字段

     内连接(inner join):

 ---------------------

select * from stu;

select * from score;

select * from subject;

--内连接第一种用法:

select a.stuid,a.name,a.age,c.sname,b.score  from stu a,score b,subject c  where

    a.stuid=b.stuid  and  b.subjectid=c.id;

--内连接第二种用法,连接三张表

select a.stuid,a.name,c.sname,b.score from (stu a inner join score b on a.stuid=b.stuid)

        inner join    subject c  

             on(b.subjectid=c.id);     

--要求:

            1、如果考试的科目是英语必须80以上或者考试的科目是物理70分以上

            2、按成绩排序

            3、名称 科目名称  分数            

  select a.stuid,a.name,c.sname,b.score  from stu a,score b,subject c

             where   a.stuid=b.stuid  and  b.subjectid=c.id  and

    ((c.sname='英语' and b.score>=80)or(c.sname='物理' and b.score>=70));

--内连接所连接的两张表是平级关系

--@@外连接

    #### 它所连接的两张表是主表与次表的关系,主表的数据,必须全部显示,次表的数据,只在与主表中的数据有关联的才能显示

create table stu

(

       id number(11) primary key,

       name varchar2(20)

)

insert into stu values(1,'张三');

insert into stu values(2,'李四');

insert into stu values(3,'王五');

insert into stu values(4,'孙六');

drop table score;

create table score

(

       sid number(11) primary key,

       score number(11),

       stuId number(11)

)

insert into score values(1,99,1);

insert into score values(2,78,2);

--外连接的语法:

--左外连接

select 字段..from 表1  left  outer join  表2  on (表1.字段=表2.字段);

左外连接,左边的表是主表,必须全部显示,右边的表是次表,对应上才可以显示

select a.id,a.name,b.score from stu a left outer join score b on (a.id=b.stuid);

--右外连接

select 字段..... from 表1  right  outer join  表2  on (表1.字段=表2.字段)

右外连接,右边的表是主表,必须全部显示,左边的表是次表,对应上才可以显示     

select a.id,a.name,b.score from stu a right outer join score b on (a.id=b.stuid);

--完全外连接:(两张表都是主表,不管是否对应上,都要显示)

select a.id,a.name,b.score from stu a full outer join score b on (a.id=b.stuid);

--外连接的第二种用法:  有+ 为次表

select a.id,a.name,b.score from stu a,score b where a.id(+)=b.stuid;

--查所有参加考试的学生信息

select a.name from stu a,score b where a.id = b.stuid;

--查询所有没有参加考试的学生信息

1、先找出参加了考试的学生编号

select stuid from score;

2、找出编写不在这个结果的学员信息

select * from stu where id not in (select stuid from score)

子查询：

在一条查询语句中,包含多个select子句

create table inf

(

       id number(11) primary key,

       name varchar2(20),

       regTime date

)

insert into inf values(1,'jack',date'2002-10-02');

insert into inf values(2,'chris',date'2001-12-22');

insert into inf values(3,'andy',date'1992-09-12');

insert into inf values(4,'cindy',date'2012-01-14');

insert into inf values(5,'jor',date'1998-03-22');

select * from inf where regTime > date'2000-01-01';

select * from inf where regTime between date'1995-01-01' and date'2005-01-01';

create table info2

(

name varchar2(20) not null,

gender varchar2(20) not null,

class varchar2(20),

score number,

bir date

)

insert into info2 values('余胜军','男','s1t80',92,date'1992-01-20');

insert into info2 values('黄辉','男','s1t80',91,date'1986-09-01');

select rownum,name,gender,class,score,bir from info2;

select rownum,p.* from info2 p where rownum <=3;  //查询前三条

--注意:如果在查询语句中有where与order by ,where一定要在order by 之前

select  p.* from  info2 p where rownum<=3 order by score desc;   X

select rownum,t.* from (select p.* from info2 p where p.gender='男' order by score desc) t  where  rownum<=3;      //查询男性的前三名

--查询第四到第六条数据

--假设: 每一页显示3条数据,当前第5页

第一页  rownum分别是:  1,2,3

第二页  rownum分别是:  4,5,6

                       7,8,9

--查询第二页的数据

需要知道的值:

1、第1页+第2页总共应该显示的rownum有哪些     当前页 * 每一页显示的条数  6

2、当前页之前,已经显示过哪些rownum     (当前页-1)*每一页显示的条数    3

select k.* from (select rownum r,f.* from info2 f where rownum <=6) k where k.r>3

分页查询，每页显示三条数据

第一页：select aa.* from (select rownum r,s.* from score s) aa where aa.r between 1 and 3;

第二页：select aa.* from (select rownum r,s.* from score s) aa where aa.r between 4 and 6;

第三页：select aa.* from (select rownum r,s.* from score s) aa where aa.r between 7 and 9;

第四章 函数

dual：它是oracle中非常特殊的一张表,它本身并不是真实存在的,它只是一张虚拟表

--一般在使用函数查询的时候,就可以使用虚拟,如果写查询的时候,不确定查询哪一张表,就可以写虚表名称。

日期函数:

sysdate: 获得当前系统时间

--语法：

select sysdate from dual;

add_months（）:指定时间上面加上月份,形成新的时间

select add_months(时间,加上几个月) from dual;

select add_months(date'2011-10-1',2) from dual;

select add_months(sysdate,-3) from dual;

select p.*,add_months(bir,12) 出生日期 from info2 p;

--months_between:用于计算两个时间相差的月份

语法:  select months_between(第一个时间,第二个时间) from 表名;

select floor(months_between(sysdate,date'2011-3-14')/12) 相差年份 from dual;

   floor 取整  

select name,gender,score,floor(months_between(sysdate,bir)/12)||'岁' 年龄 from info2;

select name,gender,score,floor(months_between(sysdate,bir)/12)||'岁' 年龄

  from  info2  where floor(months_between(sysdate,bir)/12) between 20 and 25

  order by bir;  //查询年龄在20--25岁之间的人

--next_day( ,)    获得下一个星期几是哪一天,

注意:       星期日  1      星期一  2       星期六  7

select next_day(date'2011-02-12',2) from dual;

--last_day  用于得到本月的最后一天

select last_day(date'2000-02-22') from dual;

--truncate:  用于截断日期格式,只保留年-月-日

select sysdate from dual;

select trunc(sysdate) from dual;

--extract  :用于获得日期中,指定部份的值  year,month,day

select extract(year from sysdate) from dual;

字符函数:

length()---计算字符的长度

select length('abc') from dual;

查询名字长度为2 且为男生的

select * from info2 where length(name)=2 and gender='男';

select * from info2 where name like '__'

--upper(),lower()

upper:将字符转换大写

lower:将字符转换成小写

select upper('abc233FFs经在') from dual;

select upper(class) from info2;

--ltrim 去掉左侧的空格

--rtrim 去掉右侧的空格

select ltrim(' 123') from dual;

--substr:截取字符串的指定内容

substr(字段串,开始的位置,要截取的长度)   开始下标为1

select substr('abcd123kkk',5,3) from dual;

--replace():替换函数

select replace('abcKKK123','KKK','yyy') from dual;

insert into info3 values(1,'jack','13986181999');

select substr(tel,4,5) from info3;

select replace(tel,substr(tel,4,5),'*****') from info3;

输出 13*****81999

--concat(第一个字符,第二个字符)   将第二个字符追加到第一个字符的尾部

select concat('hello','world') from dual;

数学函数

abs()----求绝对值

select abs(23) from dual;

select power(m,n);---计算m的n次方

select power(2,16) from dual;

select mod(10,3) from dual;--取模

select round(1234.56789,2) from dual;--四舍五入,保留小数字之后的几位

select sqrt(2) from dual;--开根

select sign(0) from dual;--用于判断是正数,负数,还是零      1,-1,0

select decode(gender,'男','男同学','女同学') from info2;

create table info4

(

       id number(11) primary key,

       name varchar2(20),

       age number(20)

);

insert into info4 values(1,'jack',16);

insert into info4 values(2,'chris',19);

insert into info4 values(3,'bruce',18);

insert into info4 values(4,'andy',22);

insert into info4 values(5,'mariah',15);

decode(表达式,值,结果1,结果2) --如果表达式与值相等,显示结果1,否则显示结果2

decode(表达式,值1,结果1,值2,结果2,值3,结果3......)

select k.*,decode(sign(age-18),-1,'未成年人','成年人') 是否成年 from info4 k;

select k.*,decode(sign(age-18),-1,'未成年',0,'刚刚成年',1,'已成年') 是否成年 from info4 k;

转换函数

to_date()  --将字符类型,转换成日期类型

to_char() --将数据转换成字符类型

create table info5

(

       name varchar2(20),

       bir date

)

--默认日期格式:        日-月-年     日/月/年   '12-2月-2014'

insert into info5 values('aa','19/3月/2014');

insert into info5 values('bb','19-4月-2014');

insert into info5 values('cc',to_date('2013-12-23','yyyy-MM-dd'));

insert into info5 values('dd',to_date('2011/10/11','yyyy/MM/dd'));

insert into info5 values('kk',date'2011-11-11');

--to_char()

select to_char(12345) from dual;

select to_char(date'2011-02-02','yyyy-MM-dd') from dual;

select  p.*,rownum 收入名次  from emp p order by p.salary desc;

----oracle先查询数据生成rownum,然后再对生成的结果进行排序

select  p.*,rownum 名次 from (select * from emp order by salary desc) p;

分析函数

--分析函数主要有四个:

    row_number() over(order by 字段)--先排序,再生成连续的序号       

    row_number() over(partition by 字段 order by 字段)

       --先按照某一字段分组,然后再对每一组里面的某一个字段进行排序生成序号       

    rank() over(partition by 字段 order by 字段)

       --先按照某一字段分组,然后再对每一组里面的某一个字段进行排序生成序号,

       --如果有并列值,就产生一组相同的序号

    dense_rank() over(partition by 字段 order by 字段)

       --先按照某一字段分组,然后再对每一组里面的某一个字段进行排序生成序号,

       --如果有并列值,就产生一组相同的序号

       --即使有并列值,序号依然是连续的

row_number() over()--该分析函数可以排序以后再生成一个连续的序号

select p.*,row_number() over(order by salary desc) 名次 from emp p;

--查看工资最高的三个人员信息

select * from (select p.empname,p.emplocation,p.salary,d.deptname,

   row_number() over(order by salary desc) 名次

   from emp p,dept d where p.deptid=d.deptid) where rownum<=3;

--查看每一个部门工资收入高低情况,并且在部门内按照工资收入高低序排序

select e.empname,e.emplocation,d.deptname,e.salary,

       dense_rank() over(partition by d.deptid order by salary desc) 名次

                    from emp e,dept d where e.deptid=d.deptid ;

--3、查看每个部门工资总额,按总额排序

select d.deptname,sum(e.salary)

       from emp e,dept d

            where  e.deptid=d.deptid

                   group by d.deptname order by sum(e.salary) desc;

--4、查看部门总工资高于18000的部门信息，降序

select d.deptname,sum(e.salary)

    from emp e,dept d

      where  e.deptid=d.deptid

        group by d.deptname having sum(e.salary) >18000 order by sum(e.salary) desc;

--5、查看每个部门工资最高的人员信息

select * from (select e.empname,e.emplocation,d.deptname,e.salary ,

       row_number() over(partition by d.deptid order by e.salary desc) 名次

       from emp e,dept d where e.deptid=d.deptid) k where k.名次=1;

--6、查看每个部门工资最高的人员前三名信息

select * from (select e.empname,e.emplocation,d.deptname,e.salary ,

       row_number() over(partition by d.deptid order by e.salary desc) 名次

       from emp e,dept d where e.deptid=d.deptid) k where k.名次 in (1,2,3);

----------------------------------------------

info2表

--7、查询info2表，查询生日是本月的学生信息

select * from info2 where extract(month from bir)=extract(month from sysdate);

9、查询:所有1990年以后出生学生信息

     Select * from info2 where bir > date’1990-01-01’;

分组函数     

--注意:如果在查询语句中,使用到了分组函数group by 那么就只能查询   用于分组的字段或者聚合函数

select class,count(name) from info2 group by class;

--查询班级总人数大于5个人的班级信息

--having是用于在分组以后设置查询条件的关键字,分组以后设置条件必须用到having

--having 不能单独出现,它只能做为group by 的子句出现

where >  group by  > having >order by

select class,count(name) from info2 group by class having count(name)>5 order by count(name);

第五章 数据库对象

       1、同义词    synonym

       2、视图      view

       3、序列      sequence

       4、索引      index                 

同义词  synonym

同义词: 它是一个数据库对象,一般用于关联到某一张表,操作同义词其实就在操作这张表

--使用同义词的好处

       1、可以隐藏用户信息

       2、可以隐藏真实的表名称

--同义词的分类:

       1、私有同义词:只能自己用,如果其他用户要使用,需要经过授权

       2、公有同义词:大家都可以用

--查看当前用户有哪些私有同义词

select synonym_name from user_synonyms;

--创建私有同义词的语法:

create synonym 私有同义词名称  for 表;

create synonym syn_info for info2;

select * from syn_info;

delete from syn_info where name='余胜军';--对同义词的操作,其实就是在对表操作

--删除同义词

drop synonym syn_info;

select * from scott.syn_dept;

--用户可以授权其他用户访问该私有同义词的权限

grant insert on 私有同义词名称 to 用户;

grant all on 私有同义词名称 to 用户;

-- 撤销访问私有同义词的权限

revoke all on 同义词 from 用户;

--两种身份都可以授权

dba 或者是同义词的创建者

---授予创建私有同义词的权限---只有dba才有权限执行该操作

grant create synonym to 用户名;

grant create public synonym to 用户名;

----------------------------------------------------------------

创建公共同义词

create public synonym syn_info_pb for info5;

--查看有哪些公共同义词

select synonym_name from all_synonyms where owner='PUBLIC';

--如果要访问公共同义词,用户必须要拥有对公共同义词所关联表有访问权限

grant all on info5 to scott;

视图:  view

视图它是一数据库对象,一般用于关联到一张或者多张表的查询结果,操作视图就在操作表中的数据

--查看有哪些视图

select view_name from user_views;

--授权创建视图 dba

grant create view to 用户名;

--创建视图 (基于单表创建)

create or replace view 视图名称 as 查询语句;

create or replace view info_vw as select * from info2;

create or replace view info_vw as select rownum id,name,gender,class from info2;

create or replace view info_vw as select name,gender from info2 where gender='女';

create or replace view info_vw as select * from info2 with read only;

--授权scott访问该视图的权限

grant all on info_vw to scott

insert into info_vw values('张飞','男','s1t82',90,date'2012-01-22');

--基于多表的视图 (如果视图是基于多表的,一般不允许做增删改)

create or replace view my_vw as

select * from (select e.empname,e.emplocation,d.deptname,e.salary ,

       row_number() over(partition by d.deptid order by e.salary desc) 名次

       from emp e,dept d where e.deptid=d.deptid) k where k.名次=1;

select * from my_vw;

grant all on my_vw to vincent;

drop view 视图名称

序列

--查看序列

select sequence_name  sequence

from user_sequences;

--删除序列

drop sequence inf_seq;

--创建序列

create sequence info_seq

                start with 1---初值

                increment by 1--递增量

                minvalue 1  --最小值

                maxvalue 10 --最大值--默认没有最大值

                nocycle --到达最大值,重新循环生在新的序列  nocycle

                nocache --不缓存   --cache 10

--查看当前序列的值

select inf_seq.currval    from dual;

--初始化序列,产生一个序列号

select inf_seq.nextval from dual;

--简化的方式创建序列

create sequence inf_seq;

--默认初值为1,递增量为1,最小值为1,没有最大值,不循环,不缓存

create table inf

(

       id number(11) primary key,

       name varchar(20),

       age number(11)

)

begin

       for a in 1..2000000

           loop

                insert into inf values(inf_seq.nextval,'username'||a,23);

           end loop;

end;

--查询姓名为  username198

select * from inf where name='username198';   --没有索引  2.297秒

                                              --有索引  0.375

索引   index

--查看当前表有哪些索引

select index_name from user_indexes where table_name ='INF';

--删除索引

drop index 索引名;

--创建索引

create index inf_index on inf(name);--对inf表中的name字段创建索引

索引可以提高检索效率,但是,并不是所有情况下,都适合用索引,在有情况下,建立索引反而会降低速度

1、索引主要用于海量数据查询,如果数据量很少就不适合建立索引

2、如果会经常对数据表做增、删、改的操作也不适合建立索引,因为内容一改变,索引会重新排列

--注意:对表的某一个字段建立索引,在查询的时候,只有把建立了索引的字段作为查询条件,才能提高查询效率

  select * from inf where name=’’

 

grant create session to test;--赋予create session的权限

grant create table,create view,create trigger, create sequence,create procedure to test;--分配创建表，视图，触发器，序列，过程 权限

grant unlimited tablespace to test; --授权使用表空间

;

锁  lock

create table bank

(

       id number(11) primary key,--编号

       name varchar2(20) not null,--帐号名称

       salary number(11)--帐户余额

)

insert into bank values(1,'jack',1000);

insert into bank values(2,'chris',5000);

grant all on bank to scott;

---多个用户,如果同时去访问同一数据,就会产生并发问题

通过锁的机制,就可以解决这一类问题

oracle的锁有两种分类方式:

      第一类:按照锁的范围大小,可以分为:   行级锁 与  表级锁

      第二类:按照锁的力度可以分为:   共享锁 与 排它锁

--------------------------------------------------------------

commit ---提交

rollback ---回滚(撤销之前的操作,但是已提交的数据无法撤销)

表级锁:如果对表加了表级锁,在解锁之前,其他不能对该表进行任何修改操作(可以查询)

      lock table  表名  in exclusive mode; ---表级锁的语法

      lock table  表名  in row exclusive mode; ---行级锁的语法      

      如果对表进行修改操作,系统会对表自动加上一个行级锁      

什么情况会自动解锁:

      1、执行  commit;

      2、执行  rollback;

      3、执行  DDL语句或  DCL语句      

                    DDL语句(数据定义语言)  create drop

                    DCL语句(数据控制语言)  grant revoke

行级锁:如果对表加了行级锁,在解锁之前,其他用户不能对当前用户正在编辑的行进行修改操作,但其他的行可以

按照锁的力度分为:  共享锁  与  排它锁

对一张表加了排它锁,其他用户在该表锁解锁之前  不能对表加任何锁

对一张表加了共享锁,其他用户还可以对表加锁,但只能加共享锁                         

--排它锁:   lock table 表名  in  exclusive mode;

--共享锁:   lock table 表名 in  share  mode;

如果两边都把表锁住,如果做同时做修改操作,就会造成“死锁”,系统侦测到"死锁",就会自动解锁

 ALTER USER user1 ACCOUNT LOCK --给用户加锁

  ALTER USER user1 ACCOUNT UNLOCK --解锁用户

第六章  PL/SQL 编程

Procedure Language /  Structured Query  Language

过程化语言 /  结构化查询语言

PL/SQL语句可以用于编写:  函数\存储过程\触发器\游标

--PL/SQL语句的基本语法:

declare

    --此处用于声明,定义变量

begin

    --此处写代码块

end;

例:

declare

    name varchar2(20):='jack';--声明变量

    age number(11):=23;

begin

     dbms_output.put_line('姓名是:'||name);

     dbms_output.put_line('年龄是:'||age);

end;

------------------------------------------

declare

     name varchar2(20);

     age number(11);

begin

     name:='&请输入你的姓名';          //动态输入，字符串要加’’,数字不用

     age:=&请输入你的年龄;     

     dbms_output.put_line('姓名是'||name);

     dbms_output.put_line('姓名是'||age);

end;

-------------------------------------------

declare

     i number(11):=1;

begin

     while(i<100)

         loop

                    dbms_output.put_line('i的值为:'||i);

                    i:=i+1;

         end loop;

end;

-------------------------------

declare

       name constant varchar2(20):='bruce';     //是常量，不能变

begin

       name:='lee';

       dbms_output.put_line('姓名是:'||name);

end;

函数

if 语句

----@@   if 与 end if需要成对出现       if...then....end if

declare

       age number(11):=18;

begin

       if(age>17)

           then

                dbms_output.put_line('成年人');

       end if;       

end;

----@@  if...then   else   end if

declare

        age number(11):=11;

begin

        if age>17

            then

                    dbms_output.put_line('已是成年人');

        else

                    dbms_output.put_line('未成年人');    

        end if;                    

end;

例：--判断是否可以参加决赛（嵌套语句）

declare

      speed number(11):=17;

      gender char(2):='女';

begin

      if(speed<=15)

             then

                    if(gender='男')

                           then

                           dbms_output.put_line('你可以参加男子决赛');

                    else

                            dbms_output.put_line('你可以参加女子决赛');

                    end if;       

      else       

             dbms_output.put_line('你不能参加决赛');

      end if;

end;

---------------------------------------------------

接收一个成绩,如果在90以上,显示优秀，80以上显示良好,60以上显示及格,否则,显示不及格

--需要一种类似于switch来完成

case   

      when  条件1    then 代码1;

      when  条件2    then 代码2;

end case;

--如果有任一条件匹配成功,后续代码就不再运行

declare

    score number(11);

begin

    score:=&请输入你的成绩;

    case

          when score>=90 then dbms_output.put_line('优秀');

          when score>=80 then dbms_output.put_line('良好');

          when score>=60 then dbms_output.put_line('及格');

          when score<60 then dbms_output.put_line('不及格');

    end case;

end;

循环结构

while循环

declare

      i number(11):=1;

      k number(11);

begin

      k:=&请输入循环次数;

      while(i<=k)

          loop

               dbms_output.put_line(i);

               i:=i+1;

          end loop;

end;

------------------------------

for循环

begin

   for i in 1..10

       loop

            dbms_output.put_line(i);

       end loop;

end;

--------------三角形-----------------

循环结构的嵌套:

declare

    i number(11);

    j number(11);   

begin

    for i in 1..10 --控制行

        loop

             for j in 1..i

                 loop

                    dbms_output.put('*');

                 end loop;

             dbms_output.put_line('');   

        end loop;

end;

--------------等边三角形-----------------

declare

    i number(11);

    j number(11);   

    k number(11);

begin

    for i in 1..10 --控制行

        loop

             for j in 1..11-i

                 loop

                    dbms_output.put(' ');

                 end loop;             

             for k in 1..2*i-1

                 loop

                   dbms_output.put('*');

                 end loop;  

             dbms_output.put_line('');   

        end loop;

end;

create table inf

(

       id number primary key,

       name varchar2(20),

       age number

)

select * from inf;

create sequence inf_seq;

添加语句

begin

   for i in 1..10

       loop

         insert into inf values(inf_seq.nextval,'name'||i,23);

       end loop;     

       commit;

end;

修改语句

begin

       update inf set name='bruceLee' where id=1;

       commit;

end;

删除语句

begin

       delete from inf where id=1;

       commit;

end;

动态输入编号,根据输入的编号查询数据（select 必须要 into）

declare

       myname varchar2(20);

       myage number(11);

       myid number(11);

begin

       select id,name,age into myid,myname,myage from inf where id=2;

       dbms_output.put_line('编号:'||myid);

       dbms_output.put_line('姓名:'||myname);

       dbms_output.put_line('年龄:'||myage);

end;

--------------------------------

declare

       myname inf.name%type;    //inf表name字段的类型

       myage inf.age%type;

       myid inf.id%type;

begin

       select id,name,age into myid,myname,myage from inf where id=3;

       dbms_output.put_line('编号:'||myid);

       dbms_output.put_line('姓名:'||myname);

       dbms_output.put_line('年龄:'||myage);

end;

---------------------------------------------------------------------------

存储过程

存储过程是提前创建并且编译好的sql语句集,它存储在数据库中,如果要执行这些代码,只需要调用存储过程即可

传统方式:

1、先在程序中编写好SQL语句

2、把语句传输到数据库系统中

3、数据库系统编译这些sql语句

4、数据库系统执行这些SQL语句

创建存储过程:

1、编写语句

2、编译这些sql语句

3、存储起来

使用这些存储过程:

1、调用已经编译好的存储过程

--优点

       1   速度更快

       2   安全性更好              

什么是存储过程?

答:提前编写并且编译好的存储在数据库中的语句块就称为 "存储过程"  

-- procedure 过程

基本语法:

--不带参数的存储过程

create or replace  procedure 存储过程名称

as

begin

       语句块;

end;

--带参数的存储过程

create or replace procedure 存储过程名称(参数....)  

as

begin

       语句块;

end;

-------------------------------

存储过程参数的分类:

       输入参数:  只能把外面的数据带入到存储过程里面 in

       输出参数:  只能把存储过程得到数据,带到存储过程之外(专用于返回结果的参数)  out

       输入输出参数:  既能将参数带入到存储过程,也能将返回值带出去  in out

--带参数的存储过程

create or replace procedure my_pc(in_id in number,in_name in varchar2)

as

begin

       dbms_output.put_line('编号是'||in_id);

       dbms_output.put_line('姓名是'||in_name);

end;

begin

        my_pc(in_name =>'aa',in_id => 12);    ？

end;

带输出参数

create or replace procedure my_pc(out_name out varchar2,out_age out number)

as

begin

       out_name:='bruceLee';

       out_age:=12345;

end;

调用:

declare

       myname varchar2(20);

       myage number(11);

begin

       --my_pc(out_name => myname,out_age => myage);       

       my_pc(myname,myage);       

       dbms_output.put_line('返回的结果值是:'||myname);

       dbms_output.put_line('返回的结果值是:'||myage);

end;

带输入输出参数

create or replace procedure my_pc(in_out_id in out number,out_name out varchar2,out_tel out varchar2)

as

begin

       select id,name,tel into in_out_id,out_name,out_tel  from info3 where id=in_out_id;

end;

调用：

declare

       id number(11);

       name varchar2(20);

       tel varchar2(20);

begin

       id:=&请输入你要查询的编号;

       my_pc(id,name,tel);       

       dbms_output.put_line('编号:'||id);

       dbms_output.put_line('姓名:'||name);

       dbms_output.put_line('电话:'||tel);

end;

call my_pc();

--

begin

    my_pc()

end;

truncate table inf;   清空表格

select * from inf;

游标  cursor

Oracle中的游标:它是用于指数据集中的某一行记录的一个标识符

Oracle中的游标分类:

       1、静态游标 (声明游标时,必须指定,该游标指向的是哪一张表,指定以后就不允许再改变)           

              A、隐式游标 (由系统提供)

              B、显示游标 (由用户创建)       

       2、动态游标 (声明游标时,不必指定指向的是哪一张表,在打开游开游标的时候再决定)

静态游标--->  隐式游标

          1   sql%found         ---执行成功返回true,执行失败返回false

          2   sql%notfound      ---执行成功返回false,执行失败返回true

          3   sql%rowcount      ---执行语句以后,有几行语句操作成功

begin

          update inf set name='abc' where id>2;

          dbms_output.put_line('修改的行数是:'||sql%rowcount);

end;

begin

          update inf set name='bruceLee' where id=5678;         

          if(sql%found)

                 then dbms_output.put_line('修改数据成功!');

          else

                      dbms_output.put_line('修改数据成失败!');          

          end if;    

end;          

静态游标---->显示游标   (用户自己创建)

使用显示游标基本步骤:

                --1       声明游标

                --2       打开游标

                --3       利用游标提取数据

                --4       关闭游标

declare

       cursor my_cur is select id,name,age from inf where id=3;  

       mid number;

       mname varchar2(20);

       mage number;

begin

       open my_cur;       

       fetch my_cur into mid,mname,mage;

       dbms_output.put_line('编号'||mid);

       dbms_output.put_line('姓名'||mname);

       dbms_output.put_line('年龄'||mage);       

       close my_cur;

end;

------------------------------------------------------------------

declare           //读出一行的数据

       cursor my_cur is select * from inf where id=5;  

       r inf%rowtype;  --声明变量r,它的类型为inf表中的行的类型

begin

       open my_cur;       

       fetch my_cur into r;       

       dbms_output.put_line('编号'||r.id);

       dbms_output.put_line('姓名'||r.name);

       dbms_output.put_line('年龄'||r.age);       

       close my_cur;

end;

----------------------------------------------------------------

declare       //取得整个表的数据

       cursor my_cur is select * from inf ;  

       r inf%rowtype;  --声明变量r,它的类型为inf表中的行的类型

begin

       open my_cur;     

       fetch my_cur into r;            

       while(my_cur%found)

             loop

                    dbms_output.put_line(r.id||'   '||r.name||'  '||r.age);

                    fetch my_cur into r;

             end loop;

       close my_cur;

end;

--------------------------------------------------------------------

动态游标:  

在声明的时候不需要关联到表,在打开的时候才指定,与哪一张表建立关联

---动态游标的语法:

declare

       type my_cur is ref cursor;   --声明一种动态游标类型,名称为 :   my_cur

       abc  my_cur; --声明一个动态游标类型的变量,名称为: abc

       r inf%rowtype; --声明一个变量r,它代表inf表中的一行数据

begin

       open abc for select * from inf;

       fetch abc into r;       

            while(abc%found)

            loop

                   dbms_output.put_line(r.id||'   '||r.name||'  '||r.age);

                   fetch abc into r;

            end loop;

       close abc;

end;

--------------找出年龄大于指定值的行，放在储存过程里面---------------

create or replace procedure my_pc(in_age in number)

as

begin

       declare

       type my_cur is ref cursor;   --声明一种动态游标类型,名称为 :   my_cur

       abc my_cur; --声明一个动态游标类型的变量,名称为: abc

       r inf%rowtype; --声明一个变量r,它代表inf表中的一行数据

      begin

             open abc for select * from inf where age >in_age ;

             fetch abc into r;

                  while(abc%found)

                  loop

                         dbms_output.put_line(r.id||'   '||r.name||'  '||r.age);

                         fetch abc into r;

                  end loop;

             close abc;

      end;       

end;

---------

begin

      my_pc(in_age => 29);   调用存储过程

end;

包   package

 作用:创建程序包以后,可以把定义好的游标类型,放到程序包中,要使用这种游标的时候,只需要从程序包中取得该游标

 类型即可使用

-----------------------------------

创建程序包的语法:

--创建一个程序包,程序包中包含一个动态游标

create or replace package my_pk   

as

       type r1 is ref cursor;

end;

----------------------------------------------------------------------

--创建一个存储过程,返回游标

create or replace procedure my_pc(out_cur out my_pk.r1)

as

begin

       open out_cur for select * from info2;

end;

-----------------------------------------------------------

      String sql = "{call my_pc(?)}";

      cst = getConn().prepareCall(sql);   

      cst.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);

cst.execute();

rs = (ResultSet) cst.getObject(1);

while(rs.next()){

String name = rs.getString("name");

触发器   trigger         

什么是触发器？

答：触发器是数据库中的一个代码块，当满足一定条件时，触发器中的代码将会自动运行

触发器只能被系统调用，用户不能调用

触发器的类型：

      1、行级触发器

      2、表级触发器

      3、视图触发器（替换触发器）

      4、模式触发器               

  创建触发器的语法:  

  create or  replace  trigger    触发器名称 

  在什么时候做什么事情的时候

  begin

         要触发的代码;

  end;

  --如果有for each row就表示当前是一个行级触发器

  -- :new    :old

行级触发器

  create or replace trigger tr_info3_insert

  before insert on info3 for each row

  begin

         dbms_output.put_line('operation info3......');

  end;

--------------------------

表级触发器 ,在表级触发器中 :new 与 :old 这两属性都不可用

  create or replace trigger tr_info3_insert

  before insert on info3

  begin

         dbms_output.put_line('operation info3......');

  end;

--create or replace trigger tr_info3_insert   创建触发器

--  before insert on info3  对info3表进行添加数据的时候触发

    在把数据添加到info3表之前，先触发，然后再向表中添加数据

------------部门表，新员工工作年限永远是0-----

  create table empInfo

  (

         eid number(11) primary key,

         ename varchar2(20),

         workYears number(11) --如果是新员工，工作年限应该为0

  );  

  create or replace trigger tr_insert_empInfo

  before insert on empInfo for each row

  begin

         :new.workYears:=0;  --自动将要赋给工作年限的值改为0

  end;     

  insert into empInfo values(1,'chris',999);

  update empInfo set workYears=10 where id=1

  select * from empInfo;     

------------主外键关联时，删除主键的值------------------

 create table myprovince

 (

        pid number(11) primary key,

        pname varchar2(20)

 )     

 insert into myprovince values(1,'湖北省');  

 create table mycity

 (

        cid number(11) primary key,

        cname varchar2(20),

        pid number(11) references myprovince(pid)

 )     

 insert into mycity values(1,'武汉市',1);

 insert into mycity values(2,'襄阳市',1);

 insert into mycity values(3,'宜昌市',1);          

 -----

两张表如果有主外键约束，如果外键表有数据，

主键表与之有关联的主键值不允许不删除      

delete from myprovince where pid=1;      

   --如果要删除主键表的数据，就必须先把外键表与之有关联的数据先删除

   --触发器可以在删除主键表中的数据之前，先删除外键表中有关联的数据

create or replace trigger tr_del_province

before delete on myprovince for each row

begin

       delete from mycity where pid = :old.pid;

end;   

 --------------------------------

表级触发器 

 create or replace trigger tr_op_empInfo

 before insert or update or delete on empInfo

 begin

        if(user!='FJ')

          then

               raise_application_error(-20001,'该用户无限执行这些操作');

        end if;

 end;      

 /*

        错误编号必须在  -20001            ----------    -20999

 */     

insert into empInfo values(1,'tomcat',23);

update empInfo set ename='abc' where eid=1;

delete from empInfo where eid=1;

--------------通过表级触发器，实现日志记录------------------------

create table empLog

(

       eid number(11) primary key,--日志编号

       ename varchar2(20),--操作者名称

       opDate date,--操作时间

       opDetails varchar2(200)--做了什么操作

)

create sequence emplog_seq;

select * from emplog;

---------

create or replace trigger tr_op_empInfo

before insert or update or delete on empinfo

begin

       case

              when inserting  

                    then

                        insert into empLog values(emplog_seq.nextval,user,sysdate,'向empInfo表中添加了数据')

              when updating  

                    then

                        insert into empLog values(emplog_seq.nextval,user,sysdate,'向empInfo表中修改了数据');

               when deleting  

                    then

                        insert into empLog values(emplog_seq.nextval,user,sysdate,'向empInfo表中删除了数据');               

       end case;

end;

模式触发器  

对schema这张表操作的时候触发

--创建对象，或者，删除对象的时候会触发

create table syslog

(

       sid number(11) primary key,--系统日志编号

       uname varchar2(20) ,--用户名

       opTime date,--操作时间

       objName varchar2(20),--创建或删除的对象名称

       objType varchar2(20),--创建或删除的对象类型

       opType varchar2(20)--做了什么操作：删除或创建

)

select * from syslog;

create sequence syslog_seq;

create or replace trigger tr_create_schema

after create on schema

begin

       insert into syslog values(syslog_seq.nextval,user,sysdate,ora_dict_obj_name,ora_dict_obj_type,'创建');

end;

-----------

create or replace trigger tr_drop

after drop on schema

begin

      insert into syslog values(syslog_seq.nextval,user,sysdate,ora_dict_obj_name,ora_dict_obj_type,'删除');

end;

--   ora_dict_obj_name   创建或删除的对象名称

--   ora_dict_obj_type  创建或删除的对象的类型