Oracle总结实例.docx
《Oracle总结实例.docx》由会员分享，可在线阅读，更多相关《Oracle总结实例.docx（32页珍藏版）》请在冰点文库上搜索。
Oracle总结实例
第一章Oracle简介1
创建用户/分配权限/回收权限1
拷贝表及数据/只拷贝表结构并把原表数据加入到新表中2
只拷贝表结构并把原表数据加入到新表中3
加入新雇员/更新薪水为10000/雇佣日期为当天或2000-10-01日4
删除该雇员5
新表与原表5
交集5
并集5
差集7
第二章Oracle数据类型8
字符函数8
时间函数10
转换函数11
分析函数13
第三章数据库对象的管理14
创建同义词与用户，使该用户可以访问scott用户的emp表14
创建一个序列，起始值为100，步长100，最大值1000，使用该序列向emp表插入数据15
创建带错误的/只读的/带check约束的视图16
创建emp表中ename列上的唯一索引17
PL/SQL17
用PL/SQL过程打印特定雇员的名字和工资17
依据工资范围显示不同结果18
在pl/sql块中使用绑定变量查询特定名字的雇员的工资19
自定义异常，当余额不够时抛出并处理19
第四章游标20
写一个PL/SQL块，使用隐式游标的属性20
用显式游标吧所有雇员的名字和薪水及雇员总数显示出来20
改上题为使用for循环游标21
写动态ref游标，输入参数显示emp/dept的名字21
第五章子程序和程序包22
写过程，使用in、out、inout参数22
将上题改为函数22
把过程/函数放到包中23
第六章触发器24
写一个行级触发器24
写一个替代触发器的例子25
造一个触发器递归25
第一章Oracle简介
创建用户/分配权限/回收权限
ConnectedtoOracle9iEnterpriseEditionRelease9.2.0.1.0
ConnectedasSYS
SQL>createuserjohnnyidentifiedbyjohnny;
Usercreated
SQL>grantdbatojohnny;
Grantsucceeded
SQL>revokedbafromjohnny;
Revokesucceeded
SQL>grantcreatesessiontojohnny;
Grantsucceeded
SQL>connjohnny/johnny;
ConnectedtoOracle9iEnterpriseEditionRelease9.2.0.1.0
Connectedasjohnny
SQL>connsys/sys@accpassysdba;
ConnectedtoOracle9iEnterpriseEditionRelease9.2.0.1.0
ConnectedasSYS
SQL>revokecreatesessionfromjohnny;
Revokesucceeded
SQL>grantallonscott.emptojohnny;
Grantsucceeded
SQL>revokeallonscott.empfromjohnny;
Revokesucceeded
拷贝表及数据/只拷贝表结构并把原表数据加入到新表中
SQL>createtablenew_Emptablespaceusersasselect*fromscott.emp;
Tablecreated
SQL>select*fromnew_Emp;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
----------------------------------------------------------------
7369SMITHCLERK79021980-12-17800.0020
7499ALLENSALESMAN76981981-2-201600.00300.0030
7521WARDSALESMAN76981981-2-221250.00500.0030
7566JONESMANAGER78391981-4-22975.0020
7654MARTINSALESMAN76981981-9-281250.001400.0030
7698BLAKEMANAGER78391981-5-12850.0030
7782CLARKMANAGER78391981-6-92450.0010
7788SCOTTANALYST75661987-4-193000.0020
7839KINGPRESIDENT1981-11-175000.0010
7844TURNERSALESMAN76981981-9-81500.000.0030
7876ADAMSCLERK77881987-5-231100.0020
7900JAMESCLERK76981981-12-3950.0030
7902FORDANALYST75661981-12-33000.0020
7934MILLERCLERK77821982-1-231300.0010
14rowsselected
只拷贝表结构并把原表数据加入到新表中
SQL>createtablenew_Emptablespaceusersasselect*fromscott.empwhere1=2;
Tablecreated
SQL>select*fromnew_Emp;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
----------------------------------------------------------------
SQL>insertintonew_Empselect*fromscott.emp;
14rowsinserted
SQL>select*fromnew_Emp;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
----------------------------------------------------------------
7369SMITHCLERK79021980-12-17800.0020
7499ALLENSALESMAN76981981-2-201600.00300.0030
7521WARDSALESMAN76981981-2-221250.00500.0030
7566JONESMANAGER78391981-4-22975.0020
7654MARTINSALESMAN76981981-9-281250.001400.0030
7698BLAKEMANAGER78391981-5-12850.0030
7782CLARKMANAGER78391981-6-92450.0010
7788SCOTTANALYST75661987-4-193000.0020
7839KINGPRESIDENT1981-11-175000.0010
7844TURNERSALESMAN76981981-9-81500.000.0030
7876ADAMSCLERK77881987-5-231100.0020
7900JAMESCLERK76981981-12-3950.0030
7902FORDANALYST75661981-12-33000.0020
7934MILLERCLERK77821982-1-231300.0010
14rowsselected
加入新雇员/更新薪水为10000/雇佣日期为当天或2000-10-01日
SQL>insertintonew_Empvalues（'7777','johnny','MANAGER','7839',sysdate,'5000','200','20'）;
1rowinserted
SQL>updatenew_Empsetsal='10000'whereEMPNO='7777';
1rowupdated
SQL>updatenew_EmpsetHIREDATE=to_date（'2000-10-01','yyyy-mm-dd'）whereEMPNO='7777';
1rowupdated
SQL>select*fromnew_Emp;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
----------------------------------------------------------------
7369SMITHCLERK79021980-12-17800.0020
7499ALLENSALESMAN76981981-2-201600.00300.0030
7521WARDSALESMAN76981981-2-221250.00500.0030
7566JONESMANAGER78391981-4-22975.0020
7654MARTINSALESMAN76981981-9-281250.001400.0030
7698BLAKEMANAGER78391981-5-12850.0030
7782CLARKMANAGER78391981-6-92450.0010
7788SCOTTANALYST75661987-4-193000.0020
7839KINGPRESIDENT1981-11-175000.0010
7844TURNERSALESMAN76981981-9-81500.000.0030
7876ADAMSCLERK77881987-5-231100.0020
7900JAMESCLERK76981981-12-3950.0030
7902FORDANALYST75661981-12-33000.0020
7934MILLERCLERK77821982-1-231300.0010
7777johnnyMANAGER78392000-10-110000.00200.0020
15rowsselected
删除该雇员
SQL>deletenew_EmpwhereEMPNO='7777';
1rowdeleted
新表与原表
交集
SQL>select*fromscott.empintersectselect*fromnew_Emp;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
--------------------------------------------------------------------------------
7369SMITHCLERK79021980-12-1780020
7499ALLENSALESMAN76981981-2-20160030030
7521WARDSALESMAN76981981-2-22125050030
7566JONESMANAGER78391981-4-2297520
7654MARTINSALESMAN76981981-9-281250140030
7698BLAKEMANAGER78391981-5-1285030
7782CLARKMANAGER78391981-6-9245010
7788SCOTTANALYST75661987-4-19300020
7839KINGPRESIDENT1981-11-17500010
7844TURNERSALESMAN76981981-9-81500030
7876ADAMSCLERK77881987-5-23110020
7900JAMESCLERK76981981-12-395030
7902FORDANALYST75661981-12-3300020
7934MILLERCLERK77821982-1-23130010
14rowsselected
并集
SQL>select*fromscott.empunionselect*fromnew_Emp;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
--------------------------------------------------------------------------------
7369SMITHCLERK79021980-12-1780020
7499ALLENSALESMAN76981981-2-20160030030
7521WARDSALESMAN76981981-2-22125050030
7566JONESMANAGER78391981-4-2297520
7654MARTINSALESMAN76981981-9-281250140030
7698BLAKEMANAGER78391981-5-1285030
7777johnnyMANAGER78392010-3-517500020020
7782CLARKMANAGER78391981-6-9245010
7788SCOTTANALYST75661987-4-19300020
7839KINGPRESIDENT1981-11-17500010
7844TURNERSALESMAN76981981-9-81500030
7876ADAMSCLERK77881987-5-23110020
7900JAMESCLERK76981981-12-395030
7902FORDANALYST75661981-12-3300020
7934MILLERCLERK77821982-1-23130010
8888yellowMANAGER78392001-10-11500020020
16rowsselected
所有all
SQL>select*fromscott.empunionallselect*fromnew_Emp;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
--------------------------------------------------------------------------------
7369SMITHCLERK79021980-12-1780020
7499ALLENSALESMAN76981981-2-20160030030
7521WARDSALESMAN76981981-2-22125050030
7566JONESMANAGER78391981-4-2297520
7654MARTINSALESMAN76981981-9-281250140030
7698BLAKEMANAGER78391981-5-1285030
7782CLARKMANAGER78391981-6-9245010
7788SCOTTANALYST75661987-4-19300020
7839KINGPRESIDENT1981-11-17500010
7844TURNERSALESMAN76981981-9-81500030
7876ADAMSCLERK77881987-5-23110020
7900JAMESCLERK76981981-12-395030
7902FORDANALYST75661981-12-3300020
7934MILLERCLERK77821982-1-23130010
7369SMITHCLERK79021980-12-1780020
7499ALLENSALESMAN76981981-2-20160030030
7521WARDSALESMAN76981981-2-22125050030
7566JONESMANAGER78391981-4-2297520
7654MARTINSALESMAN76981981-9-281250140030
7698BLAKEMANAGER78391981-5-1285030
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
--------------------------------------------------------------------------------
7782CLARKMANAGER78391981-6-9245010
7788SCOTTANALYST75661987-4-19300020
7839KINGPRESIDENT1981-11-17500010
7844TURNERSALESMAN76981981-9-81500030
7876ADAMSCLERK77881987-5-23110020
7900JAMESCLERK76981981-12-395030
7902FORDANALYST75661981-12-3300020
7934MILLERCLERK77821982-1-23130010
7777johnnyMANAGER78392010-3-517500020020
8888yellowMANAGER78392001-10-11500020020
30rowsselected
差集
SQL>select*fromnew_Empminusselect*fromscott.emp;
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
--------------------------------------------------------------------------------
7777johnnyMANAGER78392010-3-517500020020
8888yellowMANAGER78392001-10-11500020020
Commit
SQL>commit;
Commitcomplete
Savepoint/rollback
SQL>savepoints2;
Savepointcreated
SQL>rollbacktosavepoints2;
Rollbackcomplete
第二章Oracle数据类型
字符函数
将字符串的第一个字母转换为大写
selectinitcap（'hello'）fromdual;
INITCAP（'HELLO'）
----------------
Hello
去除字符串左边的指定字符
SQL>selectLtrim（'aaahellobbb','aaa'）fromdual;
LTRIM（'AAAHELLOBBB','AAA'）
--------------------------
Hellobbb
去除字符串右边的指定字符
SQL>selectrtrim（'aaahellobbb','b'）fromdual;
RTRIM（'AAAHELLOBBB','B'）
------------------------
Aaahello
替换字符串中的字符为指定字符
SQL>selecttranslate（'aaahellobbb','b','c'）fromdual;
TRANSLATE（'AAAHELLOBBB','B','C
------------------------------
Aaahelloccc
替换字符串中的字符串为指定字符串
SQL>selectreplace（'abchelloabc','abc','***'）fromdual;
REPLACE（'ABCHELLOABC','ABC','*
------------------------------
***hello***
找出指定字符串在原字符串中的位置
SQL>selectinstr（'abchelloabc','abc'）fromdual;
INSTR（'ABCHELLOABC','ABC'）
--------------------------
1
从索引为2的位置开始找出指定字符串在原字符串中的位置
SQL>selectinstr（'abchelloabc','abc',2）fromdual;
INSTR（'ABCHELLOABC','ABC'）
--------------------------
9
从索引为2的位置开始找出指定字符串在原字符串中的第2个出现位置
SQL>selectinstr（'abchelloabchelloabc','abc',2,2）fromdual;
INSTR（'ABCHE
升级会员 