玩转oracle笔记.docx
- 文档编号:3287656
- 上传时间:2022-11-21
- 格式:DOCX
- 页数:9
- 大小:19.71KB
玩转oracle笔记.docx
《玩转oracle笔记.docx》由会员分享,可在线阅读,更多相关《玩转oracle笔记.docx(9页珍藏版)》请在冰豆网上搜索。
玩转oracle笔记
小型数据库:
access,foxbase
你该用什么数据库?
1.项目规模:
a,负载量多大,用户多大
b,成本
c,安全性
成本在千元内
负载量小,100人内,比如留言板,信息系统
以成本在千元内,对安全性要求不高.
中型数据库:
mysql,sqlserver,informix
比如在负载日访问量5000-15000,成本在万元内
比如商务网站.
大型数据库:
sybase,oracle,db2
负载可以处理海量数据库.
sybase 这几个数据库安全性很高,相对贵. ------------------------------- 修改密码: passw createuserxiaomingidentifiedbym123; dropuserxxcascade 权限: 系统权限: 用户对数据库的相关权限. 对象权限: 用户对其他用户的数据对象操作的权限. grantselectonemptoxiaoming; grantallonemptoxx; revokeselectonempfromxx; grantallonemptoxxwithgrantoption; grantconnecttoxxwithadminoption; Oracle用户管理: 创建profile文件 createprofilelock_accountlimit failed_login_attempts3password_lock_time2; alteruserteaprofilelock_account; 解锁: 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; droptablestudent; 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) whererownum<10000)wherern>9980orderbyciddesc; 3,根据分析函数,效率最低 createtablemyemp(id,ename,sal) asselectempno,ename,salfromemp ------------------- Oracle合并查询 union并集,intersect交集,minus差集 updateempset(job,sal,comm)=(select job,sal,commfromempwhereename='SMITH') 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; SQL>SELECTENAME,HIREDATEFROMEMPWHERETO_CHAR(HIREDATE,' YYYY')=1988; SQL>SELECTENAME,HIREDATEFROMEMPWHERETO_CHAR(HIREDATE,' YYYY-MM')='1988-12'; 系统函数: TERMINAL: LANGUAGE: DB_NAME: NLS_DATE_FORMAT: SESSION_USER: SELECTSYS_CONTEXT('USERENV','LANGUAGE')FROMDUAL; SELECTSYS_CONTEXT('USERENV','SESSION_USER')FROMDUAL; PL/SQL编程 1,过程,函数,触发器是PL/SQL编写的. 2,它们存在Oracle中 3,pl/sql非常强大 4,可以在Java中调用. 学习必要性 1,提高应用程序运行性能. 传统操作数据库的方法是基于网络连接,接收SQL语句,编译再执行 2,模块化的设计思想(分页过程) 3,减少网络传输量 4,提高安全性. 缺点 移植性不好. 如何查看错误信息: SHOWERROR; 如何调用该过程: 1,EXEC过程名(参数..) 2,CALL过程名(参数..) createorreplaceprocedurepr01is begin insertintomytestvalues('xxxx','xxxx'); end; ---------------------------- 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'THEN UPDATEEMPSETSAL=SAL+500WHEREEMPNO=NO; ELSE UPDATEEMPSETSAL=SAL+200WHEREEMPNO=NO; ENDIF; END; ------------------------- CREATEORREPLACEPROCEDUREPR6(NAMEVARCHAR2)IS V_NUMNUMBER: =1; BEGIN LOOP INSERTINTOUSERS1VALUES(V_NUM,NAME); EXITWHENV_NUM=10; V_NUM: =V_NUM+1; ENDLOOP; END; ------------------------------ 分页 CREATEORREPLACEPROCEDUREPR7 (BOOK_IDINNUMBER,BOOK_NAMEINVARCHAR2,PUBLISHINVARCHAR2)IS BEGIN INSERTINTOBOOKVALUES(BOOK_ID,BOOK_NAME,PUBLISH); END; CREATEORREPLACEPROCEDUREPR8 (SPNOINNUMBER,SPNAMEOUTVARCHAR2)IS BEGIN SELECTENAMEINTOSPNAMEFROMEMPWHEREEMPNO=SPNO; END; ------------- 建包 CREATEORREPLACEPACKAGETESTPACKAGEAS TYPETEST_CURSORISREFCURSOR; ENDTESTPACKAGE; 建过程 CREATEORREPLACEPROCEDUREPR9 (SPNOINNUMBER,P_CURSOROUTTESTPACKAGE.TEST_CURSOR)IS BEGIN OPENP_CURSORFORSELECT*FROMEMPWHEREDEPTNO=SPNO; END; JAVA代码 cs=conn.prepareCall("{callpr9(? ? )}"); cs.setInt(1,10); cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR); cs.execute(); 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; BEGIN V_SQL: ='SELECT*FROM(SELECTT1.*,ROWNUMRNFROM(SELECT*FROM'||TABLENAME||')T1WHEREROWNUM<='||V_END||')WHERERN>='||V_BEGIN; OPENP_CURSORFORV_SQL; V_SQL: ='SELECTCOUNT(*)FROM'||TABLENAME; EXECUTEIMMEDIATEV_SQLINTOMYROWS; IFMOD(MYROWS,PAGESIZE)=0THEN MYPAGECOUNT: =MYROWS/PAGESIZE; ELSE MYPAGECOUNT: =MYROWS/PAGESIZE; ENDIF; CLOSEP_CURSOR; END; / ------------------ COREJAVACODE... cs=conn.prepareCall("{callpager(? ? ? ? ? ? )}"); cs.setString(1,"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); System.out.println("总页数为: "+pageCount); while(rs.next()){ System.out.println(rs.getString (1)+","+rs.getString (2)); ==================== 异常处理 DECLARE V_ENAMEEMP.ENAME%TYPE; BEGIN SELECTENAMEINTOV_ENAMEFROMEMPWHEREEMPNO=&GNO; DBMS_OUTPUT.PUT_LINE('名字: '||V_ENAME); EXCEPTION WHENOTHERSTHEN DBMS_OUTPUT.PUT_LINE('EXCEPTIONACCURED! '); END; 常见预定义例外 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; BEGIN UPDATEEMPSETSAL: =SAL+1000WHEREEMPNO=NO; IFSQL%NOTFOUNDTHEN --%NOTFOUND表示没有UPDATE --RAISEMYEX;触发MYEX这个例外 RAISEMYEX; ENDIF; EXCEPTIN WHENMYEXTHEN DBMS_OUTPUT.PUT_LINE("没有更新任何用户"); END; ======================== ORACLE视图 视图是一个虚拟表,其内容由查询定义.同真实的表一样,视图包含一系列带有名称 的列和行数据.但是,视图并不在数据库中以存储的数据值集形式存在.行和列数据 来自由定义视图的查询所引用的表,并且在引用视图时动态生成. 视图与表的区别: 视图不要空间,表要 视图不能添加索引 提高安全性. 创建视图: CREATE[ORREPLACE]VIEWXXASSELECTXXX[WITHREADONLY]; DROPVIEWXX; 20: 592009-9-19
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 笔记