oracle学习小结Word格式.docx
《oracle学习小结Word格式.docx》由会员分享，可在线阅读，更多相关《oracle学习小结Word格式.docx（17页珍藏版）》请在冰豆网上搜索。
相应的数据文件oracle\product\10.2.0\oradata\orcl\jysoft.dbf手动删除
4.用户
创建用户：
Createuserjysoftidentifiedbypass
Defaulttablespacejysoft;
删除用户：
Dropuserjysoftcascade;
Alteruserjysoftidentifiedbypass;
5.权限控制：
Grantresource,dbatojysoft;
Grantselectonemptojysoft;
Revokeselectallempfromjysoft;
Revokedbafromjysoft;
6.表
创建表：
createtablestudent
（
idnumber（4）notnull,
stunonumber（4）notnullunique,
namevarchar2（50）,
scorenumber（8,2）,
birthdatedefaultsysdate,
picblob,
remarkclob
）
复制表：
createtablejysoft.empasselect*fromscott.emp;
插入：
insertintoempselect*fromscott.emp;
insertintostudent（id,stuno）values（2,2）;
altertablestudentadd（testnumber（4））;
altertablestudentmodify（testdefault5555）;
altertablestudentdropcolumntest;
altertablestudentdropconstraintpk_stu;
altertablestudentadd（constraintstuno_ukunique（stuno））;
altertablestudentadd（constraintpk_stuprimarykey（ID））;
altertablestudentadd（constraintscore_checkcheck（score>
=0andscore<
=100））;
droptablejysoft.emp;
7.创建索引
createindexbirth_idxonstudent（birth）;
dropindexbirth_idx;
8.同义词：
createpublicsynonymstudentforjysoft.student;
select*fromstudent;
droppublicsynonymstudent;
9.数据库链接：
createpublicdatabaselinkjysoft_linkconnecttojysoftidentifiedbypassusing'
orcl'
;
select*fromstudent@jysoft_link;
10.创建序列器
createsequencestudent_id_s
minvalue1
maxvalue999999999999999999999999999
startwith1
incrementby1
cache20;
selectstudent_id_s.nextvalfromdual;
dropsequencestudent_id_s;
11.创建触发器（主键自增）
createorreplacetriggerstudent_id_t
beforeinsertonstudentforeachrow
begin
selectstudent_id_s.nextvalinto:
new.idfromdual;
end;
12.创建视图
createviewv_studentasselectstuno,namefromstudent;
13.系统命令
selectrowid,rownum,t.*fromstudentt;
SQL>
variablesrowidvarchar2（20）;
execselectrowidinto:
srowidfromscott.emptwhereempno=7369;
PL/SQLproceduresuccessfullycompleted
srowid
---------
AAAMfPAAEAAAAAgAAA
selectempno,enamefromscott.emptwhererowid=:
srowid;
EMPNOENAME
---------------
7369SMITH
AAAMfPAAEAAAAAgAAA
insertintostudent（stuno,birth）values（2,TO_DATE（'
2010-01-01'
'
YYYY-MM-DD'
））;
selectstuno,to_char（birth,'
yyyy-mm-ddhh:
mm:
ss'
）fromstudent;
savepointupdate1;
Savepointcreated
updatestudentsetname='
张三'
wherestuno=1;
1rowupdated
savepointupdate2;
李四'
wherestuno=2;
rollbacktoupdate2;
Rollbackcomplete
commit;
Commitcomplete
张三没有更新，李四更新
SELECTordernofromorder_master
Union/unionall/intersect/minus
Selectordernofromorder_detail;
Intersect操作符只返回两个查询的公共行
Minus操作符返回从第一个积善余庆结果中排除第二个查询中出现的行
连接操作符：
select‘oracle’||’程序员’fromdual;
结果返回：
oracle程序员
函数
输入
输出
Initcap（char）
Selectinitcap（‘hello’）fromdual;
Hello
Lower（char）
Selectlower（‘FUN’）fromdual;
fun
Upper（char）
Selectupper（‘sun’）fromdual;
SUN
Ltrim（char,set）
Selectltrim（‘xyzadams’,’xyz’）fromdual;
adams
Rtrim（char,set）
Selectrtrim（‘xyzadams’,’ams’）fromdual;
xyzad
Translate（char,from,to）
Selecttranslate（‘jack’,’j’,’b’）fromdual;
back
Replace（char,searchstring,[repstring]）
Selectreplace（‘jackandjue’,’j’,’bl’）fromdual;
blackandblue
Instr（char,m,n）
Selectinstr（‘worldwide’,’d’）fromdual;
5
Substr（char,m,n）
Selectsubstr（‘abcdefg’,3,2）fromdual;
cd
Concat（expr1,expr2）
Selectconcat（‘Hello’,’world’）fromdual;
Helloworld
函数名
说明
ADD_MONTHS（d,x）
返回日期d的月份加上x个月后的日期
SYSDATE
返回当前系统日期和时间
GREATEST（d1,d2）
比较两个日期d1和d2，返回其中较大的日期
LEAST（d1,d2）
比较两个日期d1和d2，返回其中较小的日期
LAST_DAY（d）
返回日期d所在月的最后一天的日期
MONTHS_BETWEEN（d1,d2）
返回两个日期d1和d2之间相差的月数
NEXT_DAY（d,day）
返回日期d后day所在的日期，day是指星期几
TO_DATE（d，’format’）
将日期型数据d,转换成以format指定形式的字符型数据
TO_CHAR（string,’format’）
将字符串string转换成以format指定形式的日期型数据
例在指定日期上增加月份。
SQL>
SELECTADD_MONTHS（’12-APR-07’,4）FROMDUAL;
例比较两个日期，显示其中较大者的日期。
SELECTGREATEST（‘15-APR-07’，‘16-MAY-07’）FROMDUAL;
例求某月的最后一天的日期。
SELECTLAST_DAY（’10-AUG-07’）FROMDUAL;
例求两个日期相差的月份数。
SELECTMONTHS_BETWEEN（’20-JAN-07’，’13-MAY-07’）FROMDUAL;
例将日期型数据转换为字符型数据。
SELECTTO_CHAR（sysdate，‘DD-MONTH-YYYY’）FROMDUAL;
例字符型数据转换为日期型数据。
SELECTTO_DATE（’14-AUG-07’，‘DD-MONTH-YYYY’）FROMDUAL;
SELECTADD_MONTHS（to_date（'
'
yyyy-mm-dd'
）,4）FROMDUAL;
DBA用户权限操作内容：
select*fromdba_tableswhereowner='
SCOTT'
//区别大小定
❑常用的主要参数
❑LINESIZE和PAGESIZE
例设置行宽为60，设置页的长度为30。
SETLINESIZE60
SETPAGESIZE30
❑ECHO设置在SQL*Plus的环境下执行命令文件时，命令是否显示有屏幕上
SETECHOON命令本身显示在屏幕上。
SETECHOOFF命令本身不显示在屏幕上。
❑PAUSE设置在每页输出的开始处是否停止。
SETPAUSEON每页输出的开始处停止，按回车键后继续滚动
❑TIME
SETTIMEON表示在每个命令提示前显示当前时间。
❑NUMFORMAT
SETNUMFORMAT设置查询结果中显示数字的缺省格式。
select*fromemptwheret.hiredate>
to_date（'
&
hdate'
）;
例替换变量用于列名。
SELECT&
Col_NameFROMEmployees;
例替换变量用于表达式。
SELECTNameFROMEmployeesWHERE&
var;
❑双&
符号替换变量
重新使用某个变量并且不希望重新提示输入该值，可以使用双&
符号变量（&
）
SELECTEmployeeID，Name，Address,&
Column
FROMEmployees
ORDERBY&
ColumnDESC;
❑VERIFY命令
SETVERIFYON/OFF命令设置是否显示执行替换的值，可以观察替换变量值前后的SQL语句。
设置值为ON，此功能可用，可以用来验证输入的值是否正确。
若设置值为OFF，该功能禁用。
默认值为ON。
SETVERIFYON
SELECTNameFROMEmployeesWHEREEmployeeID=&
EMP_ID;
输入了EMP_ID的值后，系统显示该变量的新旧值。
如输入值000002,其输出结果如下：
Old1：
SELECTNameFROMEmployeesWHEREEmployeeCode=&
New1：
SELECTNameFROMEmployeesWHEREEmployeeCode=000002;
前缀
范围
USER
用户拥有的视图
ALL
用户可访问的部分
DBA
数据库管理员视图
V$
数据库运行参数
❑*_TABLES:
用户创建的数据表
❑*_INDEXES:
用户创建的索引
❑*_OBJECTS:
用户创建的对象
❑*_TAB_COLUMNS:
数据表的列信息
selectt.tablespace_name,t.status,t.contentsfromdba_tablespacest;
//表空间
selectt.file_name,t.tablespace_name,t.bytesfromdba_data_filest;
//数据文件
selectt.username,t.password,t.createdfromdba_userst;
//用户
selectt.NAME,t.CREATEDfromV$databaset;
//数据库信息
selectt.INSTANCE_NAME,t.HOST_NAME,t.VERSIONfromv$instancet;
//实例信息
select*fromv$version;
select*fromv$controlfile;
PL/SQL查询数据库对象：
正规表达式为：
%通配符
V_$v$?
select*fromv_$version;
select*fromv$version;
DICTIONARY（DICT）中可查到名称//应如何使用？
列出DICT的结构：
DESCDICT
查找控制文件的数据字典：
select*fromdictwheretable_namelike'
%CONTROL%'
droptablespacedmusertbsincludingcontentsanddatafiles;
❑命令行方式查看有关表空间信息借助数据字典视图或动态性能视图。
如：
V$TABLESPACE、DBA_TABLESPACES，USER_TABLESPACES,DBA_DATA_FILES等。
❑作为system用户或一些其他有特权的用户登录，查询V$DATAFILE动态性能视图：
添加和移动控制文件
1）修改参数文件initsid.ora的control_file参数
2）正常关闭数据库
3）将控制文件从当前位置移到新的位置
4）启动数据库
5）检查是否正确
6）如正确，删除无用的旧控制文件
初始化参数文件：
❑initsid.ora：
初始化参数文件是一个ASCII文本文件，记录Oracle数据库运行时的一些重要参数，决定着数据库和实例的特性，如：
共享池、高速缓存、重做日志缓存分配、后台进程的自动启动、控制文件的读取、为数据库指出归档日志的目标，自动联机回滚段等。
❑服务器端二进制参数文件（SPFILE），默认情况下使用服务器端参数文件启动实例，在Oracle9i中，初始化参数文件不仅可以在运行时修改，还可以通过scope选项决定修改过的参数值是只在本次运行中有效。
查看同义词：
DBA_SYNONYMS，ALL_SYNONYMS，USER_SYNONYMS视图
查看序列：
USER_SEQUENCES数据字典视图可查询序列的设置。
14.PL/SQL
[DECLARE]
--declarationstatements声明部分
BEGIN
---executablestatements可执行部分
[EXCEPTION]
--exceptionstatements异常处理部分
END
例1用一个完整的PL/SQL块实现查询雇员号为7369的雇员信息。
declare
p_salnumber（7,0）;
p_namevarchar2（10）;
selectsal,enameintop_sal,p_name
fromscott.emp
whereempno=7369;
dbms_output.put_line（'
员工姓名:
'
||p_name||'
员工工资'
||p_sal）;
Exception
whenno_data_foundthen
Dbms_Output.put_line（'
员工号不存在!
PL/SQL语言的复合类型是用户定义的，常用的复合类型有属性、记录、表和数组。
复合类型是标量类型的组合，使用这些数据类型可以拓宽应用范围。
（1）属性类型
属性用于引用数据库列的数据类型，以及表示表中一行的记录类型。
属性类型有两种：
%TYPE-引用变量和数据库列的数据类型。
例:
使用了％TYPE声明变量
p_namescott.emp.ename%TYPE;
%ROWTYPE-提供表示表中一行的记录类型
使用％ROWTYPE声明变量
emp_exemp%ROWTYPE;
该段代码声明了变量emp_ex,它可以用于存储从emp中提取的记录。
--列类型
emp_exscott.emp%ROWTYPE;
--行记录
select*intoemp_ex
fromscott.emp
员工信息:
||emp_ex.ename）;
（2）记录类型
PL/SQL记录是由一组相关的记录成员组成的，通常用来表示对应数据库表中的一行。
使用PL/SQL记录时应自定义记录类型和记录变量，也可以使用%ROWTYPE属性定义记录变量。
引用记录成员时，必须要记录变量作为前缀。
⏹自定义记录类型和记录变量的语法：
⏹TYPE<
记录类型名>
ISRECORD（
⏹<
数据项1>
<
数据类型>
[NOTNULL[:
=<
表达式1>
]],
⏹<
数据项2>
表达式2>
⏹……
数据项n>
表达式n>
]]）;
记录变量名>
例6-2将雇员信息定义为记录类型如下：
declare
--定义记录类型
typeemp_record_typeisrecord
（
v_enamescott.emp.ename%TYPE,
v_jobscott.emp.job%TYPE,
v_salscott.emp.sal%TYPE
）;
-
升级会员 