ORACLE学习日记_oracle学习日记

ORACLE学习日记由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“oracle学习日记”。

第十八讲

Exo userid=system/manager@myral1 owner=(system,scott)file;=d;system.dmp 导出数据库

Exp userid=system/manager@myor full=y inctype=complete file=d;x.dmp 导入用imp 语句(其他用户到入表的时候不能有主外键关系)Inp userid=scott/m123@myoral1 tables=（emp）file=file=d;scott/dmp 数据字典和动态性视图

数据字典是oracle数据库中最重要的组成部分，他提供了数据的一些系统信息（静态）数据字典是存放在基本表里，存在在system里面。其中基表存放数据库的基本信息，普通用户不能直接访问数据字典的基表，数据字典视图是基于数据字典基表所建立的视图，普通用户可以通过查询数据字典视图取得系统信息，数据字典视图主要包括user_xxx,all_xxx,dba_xxx三个类型

User_table 用于显示当前用户所拥有的所有表，它只返回用户所对应反感的所有表 Select table_name from user_tables

All_TABLE 显示当前用户可以访问的所有表，她不仅会返回当前用户可以访问的其他方案表

select table_name from all_tables DBA_TABLE

他会显示所有拥有的数据库表，但是查询这种数据库字典视图，必须是DBA角色或是拥有select any table系统权限 用户名，权限，角色

在建立用户，oracle会把用户的信息存放到数据字典中，当给用户授权或是角色时，oracle会将权限和角色的信息存放到数据字典中 显示数据库用户

Select username,from DBA_users‟

DBA_SYS_PRIVS可以显示用户所具有的系统权限 DBA_TAB_PRIVS 可以显示用户所具有的对象权限 DBA_COL_PRIVS 可以显示用户具有的列权限 DBA_ROLE_PRIVS 可以显示用户所具有的角色

查看SCOTT具有的角色，可以查询DBA_ROLE_PRIVS Select * from DBA_ROLE_PRIVS WHERE =‟SCOTT 如何查询一个角色包括的权限 Select * from ‟

查询oracle中所有的系统权限，一般用DBA SELECT * FROM SYS_PRIVILEGE_MAP 查询oracle 中所有的角色，一般是DBA SELECT * FROM DBA_ROLES 查询oracle中所有对象权，一般是DBA SELECT DISTNCT PRIVILEGE FROM DBA_TAB_PRIVS 查询数据的表空间

Select tablespace_name from DBA_TABLESPACES 查询一个用户包含的系统权限

Select * from dba_sys_privs where grantee=‟DBA‟

SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE=‟DBA‟ 如何究竟有多少中角色 SELECT * FROM DBA_ROLES 显示当前用户可以访问的所有数据字典视图

SELECT * FROM DICT WHERE COMMENT LIKE „%RANT%‟ 显示当前数据全称

Select * from global_name

数据字典记录有ORACLE 数据库的所有系统信息，通过查询数据字典可以取得以下的系统信息，比如

A 对象定义情况 B 对象所占用空间大小 C 列信息 等 但因为这些个信息，可以通过PL/SQL DEVELOPER 动态性能视图记载了里程启动后的相关信息

动态性能视图用于记录当前历程的活动信息，启动oracle server时，系统会建立动态性能视图，当停止ORACLE SERVER 时，系统会删除动态性能视图 管理表空间和数据文件

介绍，表空间时数据库的逻辑组成部分，从物理上讲，数据库数据存放在数据文件中，从逻辑上讲，数据则是存放在表空间中，表空间由一个或是多个数据文件组成ORACLE的逻辑结构报考表空间，段，区，和块 表空间由段构成，段由区构成，区由块构成表空间用于从逻辑上组织数据库的数据，数据库逻辑上是由一个或是多个表空间组成的。作用

A控制数据占用的磁盘空间

B DBA可以讲不同数据类型部署到不同的位置，这样由利于提高I/O性能

建立表空间 create tablespace 命令完成的，建表空间一般是特权用户或是DBA执行 的 建立数据库后方便管理，最好建立自己的表空间，Create tablespace data01 datafile „d:testdata01.dbf‟ size 20m uniform size 128 使用数据表空间

Create table mypart(deptno number(4)),dname varchar2(14),loc varchar2(130)tablespace data01 查询所在表空间 改变表空间状态，表空间处于联机状态，此时表空间是可以访问的，并且空间执行各种语句，（1）使用表空间脱机

Alter tablespace biaokongjian offline B 使用表空间脱机

Alter tablespace biaokongjian o C 只读 表空间或写，用write ALTER TABLESPACE BIAOGONGJIAN READ ONLY 知道表空间名，显示该空间包括的所有的表

Select * from all_tables where tablespace_name=‟biaokongjianming‟ 知道表名，查看该表属于哪个空间

Select tablespace_name ,table_name from user_tables where table_name=‟emp‟ 删除表空间

Drop tablepace “biaokongjianming ” including contents and datafiles 扩展表空间

表空间是由数据文件组成，表空间的大小实际上就是数据文件相加的大小，那么我们可以想象

Show users Insert into mypart select * from mypart 拓展表空间由三种 增加数据文件

Alter table tablespace sp01 add datafile „d;testsp01.dbf‟ size 20m 增加数据文件大小

Alter table tablespace biaokongjianming „d;testsp01.dbf‟ size 20m 设置文件的自动增长

Alter table tablespace biaokongjianming „d;testsp01.dbf“ autoextend on next 10M maxsize 500M 移动数据文件

确定数据文件所在的表空间

Select tablespace_name from dba_data_files where file_name=‟ d;testsp01.dbf‟ 使表空间脱机

Alter tablespace sp01 offline

使用命令移动数据文件到指定位置

Host move d;testsp01.dbf c;testsp01.dbf 移动数据文件

Alter tablespace sp01 rename datafile „d;testsp01.dbf‟to “c;testsp01.dbf‟ 使空间联机

Alter tablespace sp001 online 其他表空间 索引表空间 Undo 表空间 临时表空间

非标准块的表空间 第二十一讲

数据的完整性用于确保数据遵从一定的商业和逻辑规则，数据完整性可以使用约束，触发器，应用程序，三种方法。

约束用于确保数据库数据满足特定的商业规则，约束包括

Not null 不为空

Unique 唯一 可以为空

Primary key 主键 唯一的标示表行的数据，不能重复而且不能为空 只能一个主键 Foreign key 外键

外键列数据必须在主表的主键列存在或是为NULL Check 检查 强制执行数据必须满足的条件 增加约束

ALTER TABLE 命令

在增加not null 用midify

Alter table goods modify goosname not null Alter table customer add constraint aa unique(cardID)删除约束

Alter table emp drop constraint 约束名称

删除主键约束时候，存在主从关系，那么要在删除主表的主键约束的时候 必须带上CASCADE Alter table emp drop primary key cascade 显示约束信息

通过查询user_constraints 可以显示所有约束信息

Select constraint_name,constraint_type,from user_constraints where table_name=表名 索引是用于加速数据存取的数据对象，合理使用索引可以大大的降低io的次数 创建索引

Create index suoyi on customer(列名1，列名2)使用原则 1 在大表上建立索引才有意义在WHERE 子句或是链接跳上 经常用的列

缺点，建立索引，系统要占用大约表的1.2倍的硬盘和内存空间来保存索引 更新数据的时候，系统必须要有额外的时间来同时对索引进行更新。以维持数据和索引的一致性。权限和角色 创建和授予

Create user ken identified by ken

Grant create seion,create table to ken with admin option 对象权限，常用有

Alter 修改 delete 删除 select查询 insert 添加 Update 修改 index 索引 references 引用 execute 希望monkey 授权Scott 可以查询

Grant select on emp to monkey With grant option 只能授予用户，不能授予角色 第23讲

预定义角色，是指oracle所提供的角色 1 connect角色resource 角色DBA 角色

自定义角色 一般是DBA设定

建立角色

create role jueseming not identified(不验证)Create role 角色名 identity by SHUNP 赋予权限

Grant create seion to jueseming with admin option Conn scott/tiger@myoral Grant select on scott,emp to jueseming Grant insert,update,delete on scott.emp 分配角色给某个用户 Conn system/manager Grant jueseming to blake with admin option 删除 角色

Drop role juese 第25讲

PL/SQL是oracle在标准语言上的扩展，不仅允许嵌入SQL语言还可以定义变量和常量。过程，函数，触发器是PL/sql编写，过程，函数，触发器是在oracle中，PL/QSL是非常强大的数据库语言，过程函数，可以在JAVA程序中可以调用。编写一个存储过程，该过程可以插入 Create or replace procedure so_spro1 is Begin--执行部分

Insert into mytest values(„寒暑平‟,‟M123‟);End.;/ 查看错误

SHOW ERROR 如何调用该过程exec 过程名（参数值1，参数值2。。）2 call 过程名（参数值1，参数值2）删除某表记录

Create or replace sp_pro2 is Begin Delete from myte where name=‟hanshupin‟ End.分类，块 A 过程

B函数

C 触发器

D 包 注释

单行注释

--多行注释

/*…*/划分 标识符号的命名的规范定义变量时 建议用V_前缀 2 定义常量时，建议用C_前缀定义游标时候，建议用—cursor作为后缀 4定义例外时

Pl/sql 由三个部分构成，定义部分，执行部分，例外处理部分 如下所示 Declear(可选)定义部分—定义变量，常量，游标，例外，复杂数据类型 Begin(必选)执行部分----要执行的语句 Exception（可选）

例外处理部分----处理运行的各种错误 End.最简单的块 Begin Dbms_output.put_line(„‟hello world”)End

Declare

V_ename varchar2(4)Begin

Select ename into v_name from emp where empno=&no;End

有定义和执行部分的块 Declare ==定义变量

V_ename varchar2(4)V_sal number(7.2)Begin---执行部分

Select ename,sal into v_name,v_sal from emp where empno=&aa---在控制台显示用户名 Dbms_output.put_line(t….)Exception When no_data_found then Dbms_output.put_line(有误)End;过程用于执行特定的操作，当建立过程时，既可以制定输入参数in ,可以指定输出参数。可以将数据传递到执行部分，通过使用输出参数，可以将执行部分的数据传递到应用环境 1 请考虑编写一个过程，可以输入雇员名，新工资，可修改雇员的工资 2 如何调用两种方法

答 1 create procedure sp_pro3(spname varchar2,newsal number(3,2))is Begin Update emp set sal=newsal where ename=spname End 如何使用过程返回值？

函数用于返回特定的数据，当建立函数时，在函数头部必须包含return 显示雇员的年薪

Create function sp_fun2(spname varchar2)return Number is yearsal Begin

Select sal*12+nvl(comm.,0)*12 into yearsal from emp where ename=spname Return yearsal End 包是用于逻辑上组合过程和函数，它由包规范和包体两部分 实例

Create package sp_package is

Procedure update_sal(name varchar2，newsal number)Is Begin Update emp set sal=newsal where ename=name end

Function annual_incom(name varchar)return number Return number is Annuan_salary number Begin End 变量类型 标量类型

定义可变长字符串 V_ENAME VARCHAR2(10)定义一个小数 v_sal number(6.2)定义一个小数和初始值 v_sal2 number(9,2)=6.4 定义一个日期类型的数据 v_hiredate date 定义一个布尔变量，不能为空，初始值为false v_valid Boolean not null default false Declare

C_tax_rate number(3,2):=0.03 V_ename varchar2(7,2)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 End 标量%tyoe 符合类型

用于存放多个值的变量，主要包括 记录 表 嵌套表 嵌套表 varry 记录 类似与高级语言中的结构体

PL.SQL记录实例 Declare--定义一个PLSQL记录类型

emp_record_typ,类型包括三个数据name ,salary,title Type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type)--定义一个变量emp_record_typ, Sp_record emp_record_type Begin Select ename,sal,job into sp_record from emp where empno=7788 End PLSQL表

Declare

定义了一个表类型sp-table-type 该类型用于存放EMP,ENAME%TYPE, index by binary_integer标示下标是整数

Tysp sp_table_type is table of emp,ename%type index by binary_integer Sp_table sp_table_type/ Begin

Select ename into sp_tabel(0)from emp where empno=7788 End

参照类型 参照变量是指用于存放数值指针的变量，可以使用游标标量（ref cursor）参照变量-ref cursor 使用游标时，当定义游标时不需要制定相应的select语句 但使用游标时OPEN时需要指定SELECT 语句，这样一个游标就与一个SELECT 语句结合了 编写使用PLSQL编写一个存储过程，可以输入部门号，并显示 Declare 定义游标类型sp_emp_cirsor Type sp_emp_cirsor is ref cursor Test_cursor sp_emp_cirsor:-定义变量

V-ename emp.empno&pyte V_sal emp.sal&type Begin-执行

-把 test_cursor和一个select结合Open test_cursor for select ename,sal,from emp where deptno=&no =循环取出 Loop

Fet test_cursor into v_ename,v_sal‟-判断是否test_cursor为空

Exit when test_cursor ¬found End loop End 第二十八讲 循环FOR BEGIN

FOR I IN REVERSE 1..10 LOOP INSERT INTO USERS VALUSE END

GOTO 语句 最好不要用 Null 语句 Declare

V_sal emp.sal%type;V_ename emp.enmae%type Begin Select ename.sal into v_ename ,v_sal From emp where empno=&no If v_sal

---in 代表这是一个输入参数（默认）---out 输出参数

Create or replace sp_pro7(spbookid in number, spbook in varchar2, sppublishhouse in varchar2)iis Begin Insert into book values(spbookid,spbookname,sppublishhouse)End 编写一个过程，输入雇员编号，返回雇员的姓名 Create orproceduresp_pro8(spname in number,spname out varchar, spsal out number,spjob out varchar2)is Begin

Select ename into spname from emp where empno=spno‟ End

有返回值的存储过程 列表 结果集 返回的结果集需要包 package Create or replace package testpackage as Type test_cursor is ref cursor End testpackage Create or replace procedure sp_pro9(spno in number, p_cursor out tepackage.test_cursor)is Begin

Oper p_cursor for select * from emp where deptno=spno;end 编写一个过程，输入部门号，返回该部门所有雇员信息

Oracle 的分页 Select * from(Select t1.*, rownum rn from(select * from emp)ti where rn6 开始编写分页的过程

Create or replace procedure fenye(table_name in varchar2, Pagesize in number, Pagenow in number, Myrows out number, Mypagecount out number P_cursor out tespackage.test_cursor)is

定义部分

定义SQL语句

V_sql varchar2(1000)V_begin number;=(pagenow-1)*pagesize+1

V_end;=(pagenow*pagesize Begin V_sql;=” Select * from(Select t1.*, rownum rn from(select * from‘||?||’)ti where rn=‟|| ”

Loop 循环

Create or replace procedure sp_pro6(spname varchar2)is V_num number:=1 Begin While v_num

exit when v_num=10 end loop

while循环

while v_table_name2 v_table_name3 loop select TABLE_NAME into v_table_name2 from

(select A1.*,rownum rn from(SELECT * FROM user_tab_columns_new)A1 where rownum=v_number1)

while v_column_name1=v_column_name2 loop

select v_table_name,v_column_name,v_data_type,v_data_length from

select distinct ljs.column_name, ljs.comments, lm.DATA_TYPE, lm.DATA_LENGTH from user_col_comments ljs inner join user_tab_columns lm on ljs.table_name = lm.TABLE_NAME and ljs.column_name = lm.COLUMN_NAME order by ljs.column_name