玩转oracle笔记Word文件下载.docx
《玩转oracle笔记Word文件下载.docx》由会员分享，可在线阅读，更多相关《玩转oracle笔记Word文件下载.docx（9页珍藏版）》请在冰豆网上搜索。
alteruserteaaccountunlock;
定期更新密码（强制）:
createprofilemyprofilelimitpassword_life_time
10password_grace_time2;
alteruserteaprofilemyprofile;
口令历史:
禁止使用以前使用过的密码.
createprofilepassword_historylimitpassword_life_time
10password_grace_time2password_reuse_time10;
删除profile:
dropprofilepassword_history[cascade];
表名和列名的命名规则:
必须以字母开头
长度不能超过30字符
不能使用Oracle保留字.
只能使用如下字符a-z,0-9,$,#等.
数据类型
字符型:
char,varchar2,clob
char的查询速度极快.
varchar最长为4000
数字型:
number
number（5,2）:
一共五位,有两位小数
number（5）:
五位整数
日期类型:
date,timestamp
图片类型:
blob,二进制数据,可以存放图片/声音4G.
altertablestudentadd（classidnumber
（2））;
altertablestudentmodify（xmvarchar2（3））;
altertablestudentmodify（xmchar（30））;
altertablestudentdropcolumnsal;
renamestudenttostu;
droptablestudent;
修改日期格式:
altersessionsetnls_date_format='
yy-mm-dd'
;
删除数据
deletefromstudent;
deletefromstudentwherexx=xx
truncatetablestudent;
savepointa;
rollback;
/rollbacktoa;
=====================
查询
查看表结构:
descdept;
select*fromxx;
对速度影响很大.
所以在查询时最好写出列名.
settimingon;
begin
foriin1..100000loop
insertintousers（userid,username,password）
select*fromusers;
endloop;
end;
如何处理NULl值:
nvl（xx,xx）
使用LIKE操作符
%表示任意多个字符
_代表任意单个字符.
--------------
对数据分组的总结
1,分组函数只能出现在选择列表,having,orderby子句中.
2,顺序:
groupby,having,orderby.
多表查询
避免笛卡尔积
规定:
多表查询的条件至少不能少于表的个数-1
子查询
单行子查询,多行子查询.
数据库在执行sql是从左到右,所以将条件强的写到最右边.
selectenamefromempwherejobin（
selectdistinctjobfromemp
wheredeptno=10）;
selectename,salfromempwheresal>
all（selectsalfromempwheredeptno=30）;
的执行效率不如
下面的高:
selectename,salfromempwheresal>
（selectmax（sal）fromempwheredeptno=30）;
在多行子查询中使用all,any
*子查询中返回多列
selectename,sal,jobfromemp
where（deptno,job）=
（selectdeptno,jobfromempwhereename='
SMITH'
）
selectename,sal,mysalfromempe,
（selectdeptno,avg（sal）mysalfromempgroupbydeptno）a
wheree.deptno=a.deptnoande.sal>
a.mysalorderbye.sal
子查询被看作一个视图来对待,也叫内嵌视图,因此必须给内嵌视图
起一个别名,不然是没法用的.并且起别名时,不能加as,为表起别名
不加as,列可以加as.
---------------------
分页查询
共有三种方式:
1,rownum分页
select*from（selecta1.*,rownumrnfrom（select*fromemp）a1
whererownum<
=10）wherern>
=6;
2,根据rowid来分
select*fromxxwhererowidin（
selectridfrom（selectrownumrn,ridfrom（
selectrowidrid,cidfromxxorderbyciddesc）
10000）wherern>
9980orderbyciddesc;
3,根据分析函数,效率最低
createtablemyemp（id,ename,sal）
asselectempno,ename,salfromemp
-------------------
Oracle合并查询
union并集,intersect交集,minus差集
updateempset（job,sal,comm）=（select
job,sal,commfromempwhereename='
whereename='
SCOTT'
=======================
Oracle事务
只读事务:
只允许执行查询的操作.只会取到特定点的数据信息.
settransactionreadonly;
设置之后,将不再看然新的事务产生的效果,比如说新插入的数据.
-----------
字符函数:
lower（char）,upper（char）,length（char）,substr（char,m,n）,
replace（char1,search_string,replace_string）,instr（str,char）;
selectlower（ename）fromemp;
selectupper（substr（ename,1,1））||lower（substr（ename,2,length（ename）））fromemp
selectsubstr（ename,1,3）fromemp;
selectreplace（ename,'
A'
'
我是老鼠'
）fromemp;
数学函数:
round（n,[m]）,trunc（n,[m]）,mod（m,n）,floor（n）,ceil（n）;
日期函数
sysdate,add_months（hire_date,8）;
select*fromempwheresysdate>
add_months（hiredate,8）;
selectename,trunc（sysdate-hiredate）"
入职天数"
fromemp;
SELECTHIREDATE,ENAMEFROMEMPWHERELAST_DAY（HIREDATE）-2=HIREDATE
转换函数
TO_CHAR
SQL>
SELECTENAME,TO_CHAR（HIREDATE,'
YYYY/MM/DDhh24:
mi:
ss'
TO_CHAR（SAL,'
L99999.99'
）FROMEMP;
SELECTENAME,HIREDATEFROMEMPWHERETO_CHAR（HIREDATE,'
YYYY'
）=1988;
YYYY-MM'
）='
1988-12'
系统函数:
TERMINAL:
LANGUAGE:
DB_NAME:
NLS_DATE_FORMAT:
SESSION_USER:
SELECTSYS_CONTEXT（'
USERENV'
LANGUAGE'
）FROMDUAL;
SESSION_USER'
PL/SQL编程
1,过程,函数,触发器是PL/SQL编写的.
2,它们存在Oracle中
3,pl/sql非常强大
4,可以在Java中调用.
学习必要性
1,提高应用程序运行性能.
传统操作数据库的方法是基于网络连接,接收SQL语句,编译再执行
2,模块化的设计思想（分页过程）
3,减少网络传输量
4,提高安全性.
缺点
移植性不好.
如何查看错误信息:
SHOWERROR;
如何调用该过程:
1,EXEC过程名（参数..）
2,CALL过程名（参数..）
createorreplaceprocedurepr01is
insertintomytestvalues（'
xxxx'
）;
----------------------------
pl/sql基础
分类:
过程（存储过程）,函数,触发器,包
编写规范
常量:
c_xx
变量:
v_xx
游标:
xxx_cursor
例外:
e_error
1createorreplaceprocedurepr03（namevarchar2,new_sal
number）is
2begin
3updateempsetsal=new_salwhereename=name;
4*end;
========================
在Java中调用存储过程
//调用存储过程
cs=conn.prepareCall（"
{callpr03（?
?
）}"
cs.setString（1,"
SMITH"
cs.setInt（2,100）;
cs.execute（）;
PL/SQL控制结构
条件分支
if-thenendif
if--then--else-endif
CREATEORREPLACEPROCEDUREPR06（NONUMBER）IS
--定义部分
V_JOBEMP.JOB%TYPE;
BEGIN
SELECTJOBINTOV_JOBFROMEMPWHEREEMPNO=NO;
IFV_JOB='
PRESIDENT'
THEN
UPDATEEMPSETSAL=SAL+1000WHEREEMPNO=NO;
ELSIFV_JOB='
MANAGER'
UPDATEEMPSETSAL=SAL+500WHEREEMPNO=NO;
ELSE
UPDATEEMPSETSAL=SAL+200WHEREEMPNO=NO;
ENDIF;
END;
-------------------------
CREATEORREPLACEPROCEDUREPR6（NAMEVARCHAR2）IS
V_NUMNUMBER:
=1;
LOOP
INSERTINTOUSERS1VALUES（V_NUM,NAME）;
EXITWHENV_NUM=10;
V_NUM:
=V_NUM+1;
ENDLOOP;
------------------------------
分页
CREATEORREPLACEPROCEDUREPR7
（BOOK_IDINNUMBER,BOOK_NAMEINVARCHAR2,PUBLISHINVARCHAR2）IS
INSERTINTOBOOKVALUES（BOOK_ID,BOOK_NAME,PUBLISH）;
CREATEORREPLACEPROCEDUREPR8
（SPNOINNUMBER,SPNAMEOUTVARCHAR2）IS
SELECTENAMEINTOSPNAMEFROMEMPWHEREEMPNO=SPNO;
-------------
建包
CREATEORREPLACEPACKAGETESTPACKAGEAS
TYPETEST_CURSORISREFCURSOR;
ENDTESTPACKAGE;
建过程
CREATEORREPLACEPROCEDUREPR9
（SPNOINNUMBER,P_CURSOROUTTESTPACKAGE.TEST_CURSOR）IS
OPENP_CURSORFORSELECT*FROMEMPWHEREDEPTNO=SPNO;
JAVA代码
{callpr9（?
cs.setInt（1,10）;
cs.registerOutParameter（2,oracle.jdbc.OracleTypes.CURSOR）;
rs=（ResultSet）cs.getObject
（2）;
while（rs.next（））{
System.out.println（rs.getInt
（1）+"
"
+rs.getString
（2））;
}
====================================
分页完整过程
--包
--CREATEORREPLACEPACKAGE
CREATEORREPLACEPROCEDUREPAGER
（TABLENAMEINVARCHAR2,
PAGESIZEINNUMBER,
PAGENOWINNUMBER,
MYROWSOUTNUMBER,--总记录数
MYPAGECOUNTOUTNUMBER,--总页数
P_CURSOROUTTESTPACKAGE.TEST_CURSOR--返回一个记录集
）IS
--定义部分
--定义SQL语句
V_SQLVARCHAR2（1000）;
V_BEGINNUMBER:
=（PAGENOW-1）*PAGESIZE+1;
V_ENDNUMBER:
=PAGENOW*PAGESIZE;
V_SQL:
='
SELECT*FROM（SELECTT1.*,ROWNUMRNFROM（SELECT*FROM'
||TABLENAME||'
）T1WHEREROWNUM<
='
||V_END||'
）WHERERN>
||V_BEGIN;
OPENP_CURSORFORV_SQL;
SELECTCOUNT（*）FROM'
||TABLENAME;
EXECUTEIMMEDIATEV_SQLINTOMYROWS;
IFMOD（MYROWS,PAGESIZE）=0THEN
MYPAGECOUNT:
=MYROWS/PAGESIZE;
ELSE
ENDIF;
CLOSEP_CURSOR;
/
------------------
COREJAVACODE...
{callpager（?
EMP"
cs.setInt（2,5）;
cs.setInt（3,1）;
cs.registerOutParameter（4,oracle.jdbc.OracleTypes.INTEGER）;
cs.registerOutParameter（5,oracle.jdbc.OracleTypes.INTEGER）;
cs.registerOutParameter（6,oracle.jdbc.OracleTypes.CURSOR）;
cs.execute（）;
introwNum=cs.getInt（4）;
intpageCount=cs.getInt（5）;
rs=（ResultSet）cs.getObject（6）;
System.out.println（"
总记录条数为:
"
+rowNum）;
总页数为:
+pageCount）;
System.out.println（rs.getString
（1）+"
====================
异常处理
DECLARE
V_ENAMEEMP.ENAME%TYPE;
SELECTENAMEINTOV_ENAMEFROMEMPWHEREEMPNO=&
GNO;
DBMS_OUTPUT.PUT_LINE（'
名字:
'
||V_ENAME）;
EXCEPTION
WHENOTHERSTHEN
DBMS_OUTPUT.PUT_LINE（'
EXCEPTIONACCURED!
'
常见预定义例外
CASE_NOT_FOUND,CURSOR_ALREADY_OPEN,DUL_VAL_ON_INDEX,INVALID_CURSOR
INVALID_NUMBER,NO_DATA_FOUND,TO_MANY_ROWS,ZERO_DIVIDE,VALUE_ERROR.
其它预定义例外
LOGON_DENIED,NOT_LOGGED_ON,STORAGE_ERROR,TIMEOUT_ON_RESOURCE.
自定义例外
CREATEORREPLACEPROCUDUREEX_TEST（NONUMBER）IS
MYEXEXCEPTION;
UPDATEEMPSETSAL:
=SAL+1000WHEREEMPNO=NO;
IFSQL%NOTFOUNDTHEN
--%NOTFOUND表示没有UPDATE
--RAISEMYEX;
触发MYEX这个例外
RAISEMYEX;
EXCEPTIN
WHENMYEXTHEN
DBMS_OUTPUT.PUT_LINE（"
没有更新任何用户"
ORACLE视图
视图是一个虚拟表,其内容由查询定义.同真实的表一样,视图包含一系列带有名称
的列和行数据.但是,视图并不在数据库中以存储的数据值集形式存在.行和列数据
来自由定义视图的查询所引用的表,并且在引用视图时动态生成.
视图与表的区别:
视图不要空间,表要
视图不能添加索引
提高安全性.
创建视图:
CREATE[ORREPLACE]VIEWXXASSELECTXXX[WITHREADONLY];
DROPVIEWXX;
20:
592009-9-19
升级会员 