Oracle人门学习笔记

1.如何显示当前用户是谁?
show user;
2.进入sql*plus管理工具，运行里面输入
sqlplusw就可以了。
3.disc断开当前数据库的连接
4.passw修改密码命令
示例：passw 回车
更改snapall的口令
旧口令：*******
新口令：******
重新键入新口令：******
口令已更改
5.如何在忘掉用户密码的情况下修改密码。
alter user sys identified by snapall;
6.ping命令的其他技巧：在一般情况下还可以通过

ping对方让对方返回给你的TTL值大小，粗略的判断

目标主机的系统类型是Windows系列还是UNIX/Linux

系列，一般情况下Windows系列的系统返回的TTL值

在100-130之间，而UNIX/Linux系列的系统返回的

TTL值在240-255之间，当然TTL的值在对方的主机里

是可以修改的，Windows系列的系统可以通过修改注

册表以下键值实现。
7.文件操作命令
a.start和@打开
sql>@ d:/1.sql
sql>start d:/1.sql
b.edit编辑
sql>edit d:/1.sql
c.spool将sql*plus上的内容输出到指定文件中去
sql>spool d:/1.sql并输入sql>spool off
8.linesize设置显示行的宽度，默认是80
show linesize;
set linesize 90;

pagesize设置每页显示行的数目，默认是14
用法与linesize一样

9.授权和收回权限(系统权限是不会级联回收的，对

象权限会级联回收的)
grant connect to user;
grant select on emp to xiaoming;给小明赋予

查询当前用户的下的emp表的权限。
revoke connect to user;
revoke select on emp from xiaoming
如果有其它权限，收回select不会对其它权限由

影响。
10.resource这个权限可以在任意表空间下面建表。
11.如果是对象权限：
如果想让用户有赋予权限的权限，可以在后面添加

with grant option;
例如:grant select on emp to xiaoming with

grant option;

如果是系统权限
添加with admin option;

12.用户登陆次数太多会被限制。
创建profile文件
create profile lock_account limit
failed_login_attempts 3 password_lock_time 1;
alter user xiaoming lock_account;

13.要求用户定期更改密码
create profile myprofile limit

password_life_time 10 password_grace_time 2;
aler user xiaoming profile myprofile;
要求用户每10天去修改密码，宽限期为2天。
14.当不需要profile时，可以去删除。
drop profile myprofile cascade;
15.如何查看一个表的结构
desc 表名；
改一个表的名字
rename 旧表名 to 新表名；

16.oracle默认的日期格式是'09-6月-99'
表示是1999年6月9号。
可以更改数据库日期的默认格式。
alter session set nls_date_fromat='yyyy-

mm-dd';
17.delete 语句删除后可以恢复；
先savepoint aa;
然后delete；
然后rollback to aa;
18.清频命令
clear
19.使用列的别名排序
select ename,sal*12 "年薪" from emp order by

"年薪" asc;
列名需要使用双引号圈中
20.如何取消重复行
select distinct deptno,job from demp;
21.打开显示操作时间的开关
set timing on;
22.疯狂复制法
insert into users (userid,username) select

* from users;
然后不断执行；
23.oracle对字段里面的内容是区分的，对字段本身

不区分。
24.如何处理NULL值
使用nvl函数
select sal*13+nvl(comm,0)*13 from emp;
25.用查询结果创建表
create tabel mytable1(id,name,sal) as select

empno,ename,sal form emp;
26.union命令
将两个查询出来的集合并，重复的只保留一条数据

。
union all 命令

将两个查询出来的集合并，重复的也都保留原有数

目。
intersect 命令
取交集
minus 命令
取差集，用前面那个查询结果减去后面那个查询合

集。
27.to_date函数
插入时间格式，to_date('1988-12-2','yyyy-mm-

dd');
默认格式为2-12月-1988,不要用函数来表示。
28.update 语句跟新数据技巧
update emp set (job,sal,comm)=(select

job,sal,comm from emp where ename='SMITH')

where ename='SCOTT';
29.创建保存点命令
savepoint a1;
回退命令；
rollback to a1;
回退全部事务
rollback
注意：一旦提交commit；原来的保存点就没有了

，也就无法回退。
30.只读事务：只是允许执行查询，不能执行其他

dml操作。设置了只读事务后，其它会话更改信息，

也不会对当前的只读事务由影响。
命令如下：set transaction read only;

31.字符函数
lower(char):将字符串转化为小写的格式。
upper(char):将字符串转化为大写的格式。
length(char):返回字符串的长度。
substr(char,m,n):取字符串的字串。m代表从第几

个开始取，n代表取的字符个数。
select upper(substr(ename,1,1))||lower

(substr(ename,2,length(ename)-1)) from emp;

replace(char1,search_string,replace_string)
替换函数

32.数字函数
round(n,[m])该函数用于执行四舍五入，如果省掉

mround(n)，则四舍五入到整数；如果m是正数，则

四舍五入到小数点的m位后，如果m是负数，则四舍

五入到小数点的m位前。
trunc(n,[m])该函数用于截取数字，如果省掉m，就

截取小数部分，如果m是正数就截取到小数点的m位

后，如果m是负数，则截取到小数点的前m位。
mod(m,n)
floor(n)返回小于或是等于n的最大整数。
ceil(n)返回大于或是等于n的最小整数。

dual表是一个测试表，用了做测试的。

33. 日期函数
sysdate()------返回系统时间
add_months(d,n)
last_day(d): 返回d日期的月的最后一天

34.转换函数
to_char()
to_char(sal,'L99999.99');
to_char(hiredate,'yyyy-mm-dd hh24:mi:ss')

35.系统函数
sys_context
teminal:当前会话客户所对应的终端的标示符
lanuage:语言
db_name:当前数据库的名称
nls_date_format:当前会话客户所对应的日期格式
session_user:当前会话客户所对应的数据库用户名
current_schema:当前会话客户所对应的默认的方案

名

例如：想查询当前用的是哪个数据库
select sys_context('USERENV','db_name') from

dual;
host:返回数据库所在主机的名称

36.普通dba没有startup 和shutdown权限。

37.用户名，权限，角色
查询dba_user可以显示所有数据库用户的详细信息
查询数据字典视图dba_sys_privs,可以显示用户所

具有的系统权限；
查询数据字典视图dba_tab_privs可以显示用户具有

的对象权限；
查询数据字典视图dba_col_privs可以显示用户具有

的列权限；
查询数据字典视图dba_role_privs可以显示用户具

有的角色；

38.
查询数据库有多少种角色？
select * from dba_roles;
一般预先定义为25个

查询数据库中所有的系统权限，一般是dba
select * from system_privilege_map order by

name;

查询数据库中所有的对象权限，一般是dba
select distinct privilege from dba_tab_privs;

查询数据库的表空间
select tablespace_name form dba_tablespace;

如何查询一个角色包括的权限？
a.一个角色包含的系统权限
select * from dba_sys_privs where

grantee='CONNECT';
或者
select * from role_sys_privs where role='';
b.一个角色包含的对象权限
select * from dba_tab_privs where

grantee='CONNECT';

如何查看某个用户具有什么用的角色？
select * from dba_role_privs where

grantee='';

显示当前用户可以访问的所有数据字典视图
select * from dict where comments like '%

grant%';

显示当前数据库的全称
select * from global_name;

39.改变表空间的状态
1.使表空间脱机
alter tablespace 表空间名字|offline;
2.使表空间联机
alter tablespace 表空间名字 online;
3.只读表空间
当设置为只读是，就不能进行修改操作（但是系统

表空间无法改为只读状态）
alter tablespace 表空间名字 read only;
4.设置表空间为可读可写
alter tablespace 表空间名字 read write;

40.
1.如果知道表空间名字，想显示该表空间的的所有

表
select * from all_tables where

tablespace_name='表空间名字';
2.如果知道表名，想查看该表属于哪个表空间
select tablespace_name,table_name from

user_tables where table_naem='表名';
3.删除表空间
drop tablespace '表空间' including contents

and datafiles;

41.移动数据文件
假如说一块磁盘的一部分磁道被损坏了，改数据文

件不能使用了，为了能够重新使用，需要将这些文

件的副本移动到其他磁盘，然后恢复。
1.确定数据文件所在的表空间
select tablespace_name from dba_data_files

where file_name='数据文件的路径';

2.使表空间脱机
确保数据文件的一致性，将表空间转变为offline的

状态。
alter tabespace 表空间名字 offline;

3.使用命令移动数据文件到指定的目标位置
host move 损坏数据文件的路径 新的数据文件的路

径；

4.执行alter tablespace命令
在物理上移动了数据文件后，还必须一致性命令对

数据文件作逻辑的修改；
alter tablespace 表空间名 rename datafile
'原来的数据文件路径' to '新的数据文件路径';

5.是表空间联机
alter tablespace 表空间名 online;

42.
1.显示表空间的信息
select tablespace_name from dba_tablespaces;
2.显示表空间所包含的数据文件
select file_name,bytes form dba_date_files

where tablespace_name=='表空间名';

43.约束：约束用于确保数据库数据满足特定的商业

规则。在oracle中，约束包括：not null（非

空）,unique（唯一，该列不能重复，但是可以为空

）,primary key（主键，该列不能重复，而且不能

为null）,foreign key（外键）和check（限制条件

）五种。

如果在建表时忘记建立必要的约束，可以在建表后

用alter table命令来为表增加约束，但是要注意，

增加not null约束时，需要 使用modify选项，而增

加其他四种约束使用add选项。

例如：alter table jacky modify 字段名 not

null;
alter table jacky add constraint 约束名

unique(字段名)；
alter table jacky add constraint 约束名

check(字段名 in ('zon','df','dfs'));

如何删除约束？
alter table 表名 drop constraint 约束名；
有些主键要删除，可能有外键的关系，无法删除，

要用如下命令：
alter table 表名 drop primary key cascade;

如何显示约束信息
1.显示约束信息
select

constraint_name,constraint_type,status,valida

ted from user_constraints where table_name='

表名';
2.显示约束列
select column_name,position form

user_cons_columns where constraint_name='约束

名';

44.索引
create index emp_index on emp(ename,job);

create index emp_index on emp(job,ename);
这两个索引时不同的。

45.权限
系统权限不是级联回收的。
对象权限是级联回收的。

46.角色
创建角色。
create role myrole1 not identified;
给空的角色赋予权限
grant create session to 角色名 with admin

option;
分配角色给用户
grant 角色名 to 用户名 with admin option;
删除角色
drop role 角色名；
问：如果一个角色被删除，那被分配这个角色的用

户还有权限吗？
答：用户权限没有了。
精细访问控制

47.PLSQL编程
PLSQL是oralce在标准的sql语言上的额扩展。
1.创建一个存储过程
create table mytest(name varchar2(30),passwd

varchar2(30));

create or replace procedure jacky219 is
begin
--执行部分
insert into mytest values('殷志刚','123');
commit;
end;
/

如何查看错误信息
show error;

如何调用过程

1.exec 过程名（参数1，参数2...）

2.call 过程名（参数1，参数2...）

编写规范
1.注释
单行注释 --
select * from emp;--取得员工信息
多行注释
/*...*/来划分

2.标示符号的命名规范
1）当定义变量时，建议用v_作为前缀，例如v_sal
2)当定义常量时，建议用c_作为前缀
3）当定义游标时，建议用_cursor作为后缀；
4)当定义例外时，建议用e_作为前缀；

包含定义部分和执行部分的pl/sql快
declare
v_ename varchar2(5);--定义字符串变量
begin
select ename into v_ename from emp where

empno=&no;
dbms_output.put_line('雇员

名：'||v_ename);
end;
/

重要：其中into代表将查询出来的值插入到v_ename

中去，&代表是要我们要从控制台输入值进去。
||代表是连接符。no只是随便是个变量名，可以用

aaa,bbb都可以。

48.函数
函数用于返回特定的数据，一般返回一个值。
例：
输入雇员的姓名，返回该雇员的年薪

create function sp_fun2(spName varchar2)

return number is yearSal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearSal

from emp where ename=spName;
return yearSal;
end;

在sqlplus中调用函数的方法
var income number
call sp_fun2('殷志刚') into:income;
print income

1.包
包用于逻辑上组合过程和函数。
创建一个包，声明了该包有一个过程和一个函数
create package sp_package is
procedure update_sal(name

varchar2,newsal number);
function annual_income(name varchar2)

returen number;
end;

2.给包 sp_package实现包体
create 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;

3.如何调用包的过程或是函数
exec sp_package.update_sal('SCOTT','120');

49.触发器

触发器是指隐含的执行的存储过程。当定义触发器

时，必须要指定触发的事件和触发的操作，常用的

触发事件包括insert,update,delete语句，而触发

操作实际就是一个pl/sql快。可以用create

trigger来建立触发器。

50.变量
1.定义标量

1.1定义一个变长的字符串
v_ename varchar2(10);
1.2定义一个小数 范围-9999.99-9999.99
v_sal number(6,2);
1.3定义一个小数并给一个初始值为5.4:=是pl/sql

的赋值号
v_sal2 number(6,2):=5.4
1.4定义一个日期类型的数据
v_hiredate date;
1.5定义一个布尔变量，不能为空，初始值为false
v_valid boolean not null default false;

%type类型
val_jacky emp.ename%type;表示变量val_jacky和

表emp中的字段ename,的长度为标准，这样就不会出

现长度不够的情况。

2.符合变量
用于存放多个值的变量，主要包括
pl/sl记录，pl/sql表，嵌套表和varray（动态表）

3.复合类型的pl/sql表
相当于高级语言的数组。

4.参照变量-ref cursor游标变量
例如：使用pl/sql编写一个快，可以输入部门号，

并显示该部门所有员工的姓名和他的工资。

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 where deptno=&no;
loop
fetch test_cursor into v_ename,v_sal;
//update emp set sal:=sal+100 where

sal<200 and ename=v_ename;
exit when test——cursor%notfound;
dbms_output.put_line('名字：'||v_ename||'

工资：'||v_sa;);
end loop;
end;

51.控制结构

1.if then endif;

2.if then else endif;

3.if then elsif then else endif;

4.循环语句
4.1 loop end loop;
4.2 while..loop end loop;
4.3基本for循环
4.4goto语句，跳转；跳转的语句必须加《》
4.5输出语句开关：set serveroutput on;打开
set serveroutput off;关闭

52.编写分页过程
1.将一个表中的记录编号，
select t1.*,rownum rn from (select * fromo

emp) t1;

2.可以按照每10条来取。
select t1.*,rownum rn from (select * fromo

emp) t1 where rownum<1=0;

3.取第6条到第10条的语法
select * from
(select t1.*,rownum rn from (select * fromo

emp) t1 where rownum<10;)
where rn>=6;

4.分页的过程代码
create or replace procedure fenye
(tableName in varchar2,
Pagesize in number,
pageNow in number,
myrows out number,
mypageCount out number,
p_cursor out tespackage.test_cursor
) is
v_sql varchar2(1000);
v_begin number:=(pageNow-1)*Pagesize+1;
v_end number:=pageNow*Pagesize;
begin
v_sql:='select * from (select t1.*,rownum rn

from(select * from '|| tableName||') t1 where

rownum<='||v_end||' ) 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;

53.视图
视图是一个虚拟表，其内容友查询定义。
1.视图不占用磁盘空间
2.视图不能建索引
3.视图可以简化 复杂查询
4.视图可以提高安全性

创建视图
create view 视图名 as select语句 [with read

only(假如添加了这个，那这个视图就不能被修改)]

创建或者修改视图
create or replace 视图名 as select语句[with

read only];

删除视图
drop view 视图名；