oracle学习

01) :

1.oracle的用户

sys （超级管理员）董事长， system（管理员）总经理 。scott（普通用户，测试） 默认锁定的状态，默认密码：tiger

角色：sysdba ， sysoper

忘记登录密码，如何操作？

a 找到密码的配置文件修改

b 以操作系统的管理身份登录

在运行中，输入 sqlplus / as sysdba

使用oracle的步骤？

a 启动服务

oracle中每个数据库对应一个服务（db实例）

b 使用第三方工具链接oracle ，需要启动监听服务

2 .常用的命令

a 解锁用户

alter user 用户名 account unlock

b 锁定用户

alter user 用户名 account lock

注意：a和b 两种语句必须管理员身份才能做到

02):

1 常用的sqlplus的命令

a 显示当前的用户

show user

b 解锁用户

alter user 账号名 account unlock

注意：执行此命令一定是 dba角色

c 切换用户

conn 账号名/密码 [as sysdba]

d 修改当前账号的密码

password

e 修改其他账号的密码

alter user 账号名 identified by 新密码

注意：此命令，一定是dba权限可以做

f 设置每行显示的字符数，默认 80个字符

set linesize 字符数

g 设置每页显示的行数

set pagesize 行数

j 清屏

clear（在sqlplus中不生效）

k 显示表的结构

desc 表名

q 显示执行sql的时间

set timing on

2 oracle数据库

物理存储和逻辑存储

逻辑存储分层管理数据库，优点：查找速度等很快的。

表空间：一个数据库可以对应多个表空间，

3 用户管理

a 创建用户

简易语法：

create user 账号名 identified by 密码

注意：创建账号，需要dba角色

b 授予“系统权限”

grant 权限/角色 to 账号 [with admin option]

注意：执行此语句，一定有dba管理员权限

c 授予“对象权限”

grant 权限 on 表名 to 账号 [width grant option]

d 权限的回收

回收对象权限

revoke 权限 on 表名 from 账号

回收系统权限

revoke 权限/角色 from 账号

问题：如果scott将emp表的select权利从 zhangsan 回收，那么lisi还具备这个查询的权利吗？ 级联

f 删除用户

drop user 账号 [cascade]

4 权限管理

画图说明

sys（超级管理员--董事长） ， system（数据库管理员--总经理）

03）：

1 表空间

在oracle中管理数据库主要使用的就是 表空间。一个数据库可以有多个表空间的，但是一个表空间只能对应一个数据库。

表空间中，有 视图，索引，表等等。

创建表空间

语法：

create tablespace 名 datafile ‘路径’ size 大小（兆）

修改表空间

删除表空间

drop 关键字开头

表空间是如何使用？创建用户时，指定表空间，创建表指定表空间等

2 oracle中的数据类型

number（N,M） 数值

N：表示的是数字的个数

M：小数的位数

如：number(5,2) 表示 范围：-999.99-------999.99

number(10) 代表的是 整数 范围：-9999999999------------9999999999

char:字符，固定长度

如：char(5) 如果插入的字符数：abc ，意味着，后面还会补充2个空格。

varchar2：字符，可变长度

如：varchar2(5) 如果插入的字符数：abc，意味着，字段中只有3位，后面不会补齐空格

注意：

char类型的字段，查询速度是非常快。

所以，一般固定长度的字段都会使用char，如：性别，电话，身份证，等等

date ：日期

long：字符，最多可以存储2G

lob：大对象。最多可以存储4G

完成：创建用户信息表，学号，姓名，性别，电话，生日 并且向其中插入一条测试数据

注意：

1 oracle数据库更新数据，那么数据的类型必须要与字段类型匹配。

2 oracle中更新操作，所有的事务默认打开，所以当更新之后必须要提交事务（commit） 才能将缓存中的数据更新到数据库中

3 同义词

给表起别名，同义词分为两种：共有（public）和私有

语法：

create [or replace][public] synonym 别名 for 表名

删除同义词

drop synonym 同义词别名

4 视图（重要）

可以将一张表或者多张表连接起来，形成一张虚拟的表。

语法：

create [or replace] view 视图名 as 查询的sql语句

创建一个视图，只显示emp表中的，编号，姓名，工作

注意：

a 视图可以做更新操作的（前提：一定要满足原来表的一些约束）

删除语法：

drop view 视图名

5 索引

表建立索引，相当于书建立了目录。 目的：提高查询效率。索引属于系统优化的一部分

语法：

create [or replace] index 索引名 on 表名（字段的列表）

索引常用的根据主键 建立索引

注意：

a 建立索引有磁盘空间开销

b 建立索引的字段，尽量少的做更新操作

删除：

drop index 索引名

6 序列

在sql中 创建表，可以指定某个列自动增长，在oracle中没有此功能，所以出现 序列代替自增长。

语法：

create sequence 序列名

start with 起始值

increment by 每次增长的值

minvalue 序列的最小值

maxvalue 序列的最大值

cache 缓存序列的个数

noche 不缓存

cycle 循环开始

注意：cache 5 ，表示在内存中会先生存5个序列号。问题：如果一旦特殊原因如断电，那么在使用序列时，从第6个序列开始了、

修改序列

语法：

alter sequence 序列名

increment by 每次增长的值

minvalue 序列的最小值

maxvalue 序列的最大值

cache 缓存序列的个数

noche 不缓存

cycle 循环开始

修改的时候，不能有 start with

删除序列

drop sequence 序列名

04):

1 oracle中的常用函数

a 数值函数

abs（n） 求出n的绝对值

ceil（n） 求出比n大的最小整数

floor（n） 求出比n小的最大整数

round(n,m) 四舍五入，n表示数字，m表示n小数点保留的位数

trunc（n,m） 截取数字n，m表示截取的位数

c 字符串函数

lower（n） 将n转换成小写

upper（n） 将n转换成大写

length（n） 求出n的长度

ltrim（n） 去掉n的左边空格

rtrim（n）去掉n的右边空格

replace（n,m,h） 将n中的m，替换成h

d 转换函数

to_number(n,m) 将满足数字格式的n，按照m的格式转成数字

to_number('23',99）

to_char(n,m) 将n按照m的格式转换成 字符串

to_char(sysdate,'yyyy-MM-dd hh24:mi:ss')

to_date(n,m); 将n，按照m的格式转换成日期

nvl（n，m） 如果n为空，则显示m

nvl2（n,m,h） 如果n不为空，则显示m，否则显示h

1 oracle中的常用函数

a 数值函数

abs（n） 求出n的绝对值

ceil（n） 求出比n大的最小整数

floor（n） 求出比n小的最大整数

round(n,m) 四舍五入，n表示数字，m表示n小数点保留的位数

trunc（n,m） 截取数字n，m表示截取的位数

c 字符串函数

lower（n） 将n转换成小写

upper（n） 将n转换成大写

length（n） 求出n的长度

ltrim（n） 去掉n的左边空格

rtrim（n）去掉n的右边空格

replace（n,m,h） 将n中的m，替换成h

d 转换函数

to_number(n,m) 将满足数字格式的n，按照m的格式转成数字

to_number('23',99）

to_char(n,m) 将n按照m的格式转换成 字符串

to_char(sysdate,'yyyy-MM-dd hh24:mi:ss')

to_date(n,m); 将n，按照m的格式转换成日期

nvl（n，m） 如果n为空，则显示m

nvl2（n,m,h） 如果n不为空，则显示m，否则显示

2 oracle中的查询

a 计算每个员工的年薪？

select ename,sal*12+nvl(comm,0) 年薪 from emp;

b 查询“SMITH” 的基本信息

select * from emp where ename='SMITH';

c 显示 工资大于等于2850 ，小于等于5000 的员工信息。

select * from emp where sal>=2850 and sal<=5000;

select * from emp where sal between 2850 and 5000

d 查询，姓名中有 M 的人的基本信息

select * from emp where ename like '%M%'

模糊查询：% 表示0到n个任意字符 _代表单个字符 [] 如果满足其中的某个字符都会查询出来

e 查询 姓名以"m"开头的人的信息

select * from emp where ename like 'M%'

链接查询（内链接，左连接，右链接）

语法：

select * from 表1 别名1 [inner/left/right] join 表2 别名2 on 别名1.相同的字段=别名2.相同的字段

inner join 两张表中有关联的数据查询出来

left join 左表是主表，左表中的所有数据都会查询出来

right join 跟左链接相反

f 查询 部门名称是“ACCOUNTING” 下面的所有员工

select * from emp e inner join dept d on e.deptno=d.deptno where d.dname='ACCOUNTING';

子查询

select * from emp where deptno=(select deptno from dept where dname='ACCOUNTING')

e 查询 部门名称是“ACCOUNTING”，“RESEARCH” 的下面所有员工

select * from emp e inner join dept d on e.deptno=d.deptno where d.dname='ACCOUNTING' or d.dname='RESEARCH'

select * from emp where deptno in(select deptno from dept where dname='ACCOUNTING' or dname='RESEARCH' )

g 查询 ‘SMITH’ 的上级姓名

select ename from emp where empno=(select mgr from emp where ename='SMITH')

h 笛卡尔积查询

select * from 表1，表2

排除笛卡尔积的结果

加上条件

select * from 表1，表2 where 表1.字段=表2.字段

j 查询 姓名是“SMITH”和"ADAMS" 并且 工资（sal）大于 1000的员工信息

select * from emp where ename in('SMITH','ADAMS') and sal >1000

select * from emp where (ename='SMITH' or ename='ADAMS') and sal>1000

3 oracle中的事务

一般批量更新时，使用较多。

oracle中的事务，自动开启的。但是没有自动提交。为了提高效率，每次更新动作完成之后，手动提交事务

提交事务：commit

回滚事务：rollback

设置保存点：savepoint 保存点名

回滚到保存点：rollback to 保存点名

if（有异常）

{

回滚

}else

{

commit

}

4 oracle中的存储过程？

存储过程类似程序中的方法，可以独立完成某个特定的操作（功能），如（订单，分页）等等

语法：

create [or replace] procedure 过程名(参数1 [模式] 数据类型，参数2 [模式] 数据类型,、、、、)

is/as

定义变量的区域

begin

具体的sql代码的区域

end;

解释：模式有两种：输入参数（in）和输出参数（out）。默认模式 输入参数in

完成：打印当前系统时间的存储过程

DBMS_OUTPUT.put_line(sysdate);

解释：DBMS_OUTPUT包名，put_line过程名

注意：如果使用此过程，需要先打开包

set serveroutput on

create or replace procedure pro_my

is

begin

DBMS_OUTPUT.put_line(to_char(sysdate,'yyyy-MM-dd'));

end;

调用存储过程：execute(exec):过程名

完成：根据员工的姓名，查出他的工资

create or replace procedure pro_my(enam varchar2)

is

--定义变量表示工资

my_sal number(5);

begin

select sal into my_sal from emp where ename=enam;

--打印工资

DBMS_OUTPUT.put_line(enam||'的工资是：'||my_sal);

end;

5 oracle中的函数

与过程非常类似，可以独立完成特定的功能

语法：

create [or replace] function 函数名(参数列表)

return 返回值的类型

is/as

begin

end;

完成：根据员工的姓名，查出他的工资

create or replace function func_my(enam varchar2)

return number

is

--定义变量表示工资

my_sal number(5);

begin

select sal into my_sal from emp where ename=enam;

return my_sal;

end;

05）：

1 pl/sql

是在标准sql的扩展，oracle中的编程，主要通过pl/sql进行

分类：

a 存储过程

b 函数

c 触发器

d 包

2 常用语法规则

注释

-- 单行注释

/**/ 多行注释

变量命名规范

v_变量名

c_常量名

游标名_cursor

e_异常变量名

3 块的结构

declare

定义部分

begin

执行具体代码

exception

异常处理

end

注意：declare和exception 都是可以省略

完成：打印“hello word”

DBMS_OUTPUT.put_line('内容')

DBMS_OUTPUT 包，put_line 过程名

前提：必须要导入包，开启包

set serveroutput on

begin

DBMS_OUTPUT.put_line('HELLO WORD');

end;

案例：根据用户输入的雇员编号，显示该雇员的名字

declare

--定义变量，保存查询出来的姓名

v_name varchar2(20);

begin

select ename into v_name from emp where empno=&empno;

DBMS_OUTPUT.put_line('雇员名是：'||v_name);

exception

when no_data_found then

DBMS_OUTPUT.put_line('查无此人');

end;

定义变量的语法

变量名 [constant] 数据类型 [not null] [:=初始值]

如：

v_name varchar2(20) not null:='abc';

v_number number(5):=23;

注意：

布尔类型,必须有初始值

v_fag boolean :=false;

v_fag boolean not null default false;

2 以输入员工号，显示雇员姓名、工资、个人所得税(税率为0.03)。

declare

--定义变量，保存查询出来的姓名

v_name emp.ename%type;

v_sal emp.sal%type;

begin

select ename,sal into v_name,v_sal from emp where empno=&empno;

DBMS_OUTPUT.put_line('雇员名是：'||v_name);

DBMS_OUTPUT.put_line('工资是：'||v_sal);

DBMS_OUTPUT.put_line('所得税：'||v_sal*0.03);

exception

when no_data_found then

DBMS_OUTPUT.put_line('查无此人');

end;

if语句的三种语法形式

if 、、、then、、、、

if、、、、then、、、、else、、、

if、、、、then、、、elsif、、、、else、、、

注意：以上三种语法结构都是以 end if结束

a 定义整形变量等123，如果变量的值大于100，则打印该变量大于100

declare

v_number number(5):=123;

begin

--判断

if v_number>100 then

DBMS_OUTPUT.put_line('变量的值大于100');

end if;

end;

3 编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000,就给该雇员工资增加10%

create or replace procedure pro_sal(enam varchar2)

is

v_sal emp.sal%type;

begin

select sal into v_sal from emp where ename=enam;

--判断工资是否小于2000

if v_sal<2000 then

--修改该员工的工资，增加10%

update emp set sal=sal+sal*0.1 where ename=enam;

commit;

end if;

end;

4 编写一个过程，可以输入一个雇员名,如果该雇员的补助不是0就在原来的基础上增加100;如果补助为0就补助设为200

create or replace procedure pro_comm(enam varchar2)

is

--定义补助

v_comm emp.comm%type;

begin

select nvl(comm,0) into v_comm from emp where ename=enam;

if v_comm!=0 then

update emp set comm=comm+100 where ename=enam;

commit;

else

update emp set comm=200 where ename=enam;

commit;

end if;

end;

5 编写一个过程，可以输入一个雇员编号,如果该雇员的职位是PRESIDENT 就给他的工资增加1000,如果该雇员的职位是MANAGER就给他的工资增加500,其它职位的雇员工资增加200

create or replace procedure pro_job(eno number)

is

--定义工作

v_job emp.job%type;

begin

select job into v_job from emp where empno=eno;

if v_job='PRESIDENT ' then

update emp set sal=sal+1000 where empno=eno;

commit;

elsif v_job='MANAGER' then

update emp set sal=sal+500 where empno=eno;

commit;

else

update emp set sal=sal+200 where empno=eno;

commit;

end if;

end;

6 循环语句

loop

exit

when 条件

end loop;

特点：至少执行一次

while 条件 loop

end loop；

特点：只有条件为真时才会执行循环体

for 循环变量 in [reverse] 范围 loop

end loop;

请，编写一个过程，循环添加10个用户到users表中，用户编号从1开始增加.

create or replace procedure pro_insert

is

v_i number(5):=1;

begin

loop

insert into userInfo values(v_i,'a'||v_i);

commit;

v_i:=v_i+1;

exit

when v_i>10;

end loop;

end;

编写一个过程，并循环添加10个用户到users表中，用户编号从11开始增加. while

is

v_i number(5):=11;

begin

while v_i<=20 loop

insert into userInfo values(v_i,'a'||v_i);

commit;

--改变条件

v_i:=v_i+1;

end loop;

end;

编写一个过程，并循环添加10个用户到users表中，用户编号从11开始增加. for

create or replace procedure pro_insert

is

begin

for v_i in 21..30 loop

insert into userInfo values(v_i,'a'||v_i);

commit;

end loop;

end;

游标

问题：

写pl/sql程序块，完成将emp表中的所有数据查询出来，如何做？

游标作用：可以存储select语句查询的多条记录

语法：

declare cursor 游标名 is 查询的sql语句

分解：

declare cursor 游标名

is

sql查询语句;

定义变量

begin

end;

注意：

a 游标的使用，必须先打开

open 游标名

b 使用完之后，关闭游标

close 游标名

declare cursor emp_cursor

is

select * from emp;

--定义变量，保存游标中的一行数据

v_cursr_emp emp_cursor%rowtype;

begin

--打开

open emp_cursor;

loop

--使用fetch 输出游标中的值

fetch emp_cursor into v_cursr_emp;

DBMS_OUTPUT.put_line(v_cursr_emp.empno||'----'||v_cursr_emp.ename);

exit

when emp_cursor%notfound;

end loop;

--关闭

close emp_cursor;

end;

declare cursor emp_cursor

is

select * from emp;

--定义变量，保存游标中的一行数据

v_cursr_emp emp_cursor%rowtype;

begin

for v_cursr_emp in emp_cursor loop

DBMS_OUTPUT.put_line(v_cursr_emp.empno||'----'||v_cursr_emp.ename);

end loop;

end;