浏览 2683 次
锁定老帖子 主题:玩转Oracle
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
|
|
---|---|
作者 | 正文 |
发表时间:2009-09-21
你该用什么数据库? 1.项目规模: a,负载量多大,用户多大 b,成本 c,安全性 成本在千元内 负载量小,100人内,比如留言板,信息系统 以成本在千元内,对安全性要求不高. 中型数据库:mysql,sql server,informix 比如在负载 日访问量5000-15000,成本在万元内 比如商务网站. 大型数据库:sybase,oracle,db2 负载可以处理海量数据库. sybase<oracle<db2 这几个数据库安全性很高,相对贵. ------------------------------- 修改密码: passw create user xiaoming identified by m123; drop user xx cascade 权限: 系统权限:用户对数据库的相关权限. 对象权限:用户对其他用户的数据对象操作的权限. grant select on emp to xiaoming; grant all on emp to xx; revoke select on emp from xx; grant all on emp to xx with grant option; grant connect to xx with admin option; Oracle用户管理: 创建profile文件 create profile lock_account limit failed_login_attempts 3 password_lock_time 2; alter user tea profile lock_account; 解锁:alter user tea account unlock; 定期更新密码(强制): create profile myprofile limit password_life_time 10 password_grace_time 2; alter user tea profile myprofile; 口令历史:禁止使用以前使用过的密码. create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10; 删除profile: drop profile password_history [cascade]; 表名和列名的命名规则: 必须以字母开头 长度不能超过30字符 不能使用Oracle保留字. 只能使用如下字符a-z,0-9,$,#等. 数据类型 字符型:char,varchar2,clob char的查询速度极快. varchar 最长为4000 数字型:number number(5,2):一共五位,有两位小数 number(5):五位整数 日期类型:date,timestamp 图片类型:blob,二进制数据,可以存放图片/声音 4G. alter table student add(classid number(2)); alter table student modify(xm varchar2(3)); alter table student modify(xm char(30)); alter table student drop column sal; rename student to stu; drop table student; 修改日期格式: alter session set nls_date_format = 'yy-mm-dd'; 删除数据 delete from student; drop table student; delete from student where xx=xx truncate table student; savepoint a; rollback; / rollback to a; ===================== 查询 查看表结构:desc dept; select * from xx ; 对速度影响很大. 所以在查询时最好写出列名. set timing on; begin for i in 1 .. 100000 loop insert into users(userid,username,password) select * from users; end loop; end; 如何处理NULl值:nvl(xx,xx) 使用LIKE操作符 % 表示任意多个字符 _ 代表任意单个字符. -------------- 对数据分组的总结 1,分组函数只能出现在选择列表,having,order by 子句中. 2,顺序:group by , having, order by. 多表查询 避免笛卡尔积 规定:多表查询的条件至少不能少于表的个数-1 子查询 单行子查询,多行子查询. 数据库在执行sql是从左到右,所以将条件强的写到最右边. select ename from emp where job in( select distinct job from emp where deptno=10); select ename,sal from emp where sal> all(select sal from emp where deptno=30);的执行效率不如 下面的高: select ename,sal from emp where sal > (select max(sal) from emp where deptno=30); 在多行子查询中使用all,any -------------- * 子查询中返回多列 select ename,sal,job from emp where(deptno,job)= (select deptno,job from emp where ename='SMITH') select ename,sal,mysal from emp e, (select deptno,avg(sal) mysal from emp group by deptno) a where e.deptno=a.deptno and e.sal>a.mysal order by e.sal 子查询被看作一个视图来对待,也叫内嵌视图,因此必须给内嵌视图 起一个别名,不然是没法用的.并且起别名时,不能加as,为表起别名 不加as,列可以加as. --------------------- 分页查询 共有三种方式: 1,rownum分页 select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6; 2,根据rowid来分 select * from xx where rowid in( select rid from (select rownum rn,rid from( select rowid rid,cid from xx order by cid desc) where rownum<10000)where rn>9980 order by cid desc; 3,根据分析函数,效率最低 create table myemp (id,ename,sal) as select empno,ename,sal from emp ------------------- Oracle合并查询 union 并集, intersect 交集, minus 差集 update emp set(job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename ='SCOTT'; ======================= Oracle 事务 只读事务:只允许执行查询的操作.只会取到特定点的数据信息. set transaction read only; 设置之后,将不再看然新的事务产生的效果,比如说新插入的数据. ----------- 字符函数: lower(char),upper(char),length(char),substr(char,m,n), replace(char1,search_string,replace_string),instr(str,char); select lower(ename) from emp; select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename))) from emp select substr(ename,1,3) from emp; select replace(ename,'A','我是老鼠') from emp; 数学函数: round(n,[m]),trunc(n,[m]),mod(m,n),floor(n),ceil(n); 日期函数 sysdate,add_months(hire_date,8); select * from emp where sysdate>add_months(hiredate,8); select ename,trunc(sysdate-hiredate) "入职天数" from emp; SELECT HIREDATE, ENAME FROM EMP WHERE LAST_DAY(HIREDATE)-2=HIREDATE 转换函数 TO_CHAR SQL> SELECT ENAME, TO_CHAR(HIREDATE,'YYYY/MM/DD hh24:mi:ss') , TO_CHAR(SAL,'L99999.99') FROM EMP; SQL> SELECT ENAME,HIREDATE FROM EMP WHERE TO_CHAR(HIREDATE,' YYYY')=1988; SQL> SELECT ENAME,HIREDATE FROM EMP WHERE TO_CHAR(HIREDATE,' YYYY-MM')='1988-12'; 系统函数: TERMINAL: LANGUAGE: DB_NAME: NLS_DATE_FORMAT: SESSION_USER: SELECT SYS_CONTEXT('USERENV','LANGUAGE') FROM DUAL; SELECT SYS_CONTEXT('USERENV','SESSION_USER') FROM DUAL; PL/SQL编程 1,过程,函数,触发器是PL/SQL编写的. 2,它们存在Oracle中 3,pl/sql非常强大 4,可以在Java中调用. 学习必要性 1,提高应用程序运行性能. 传统操作数据库的方法是基于网络连接,接收SQL语句,编译再执行 2,模块化的设计思想(分页过程) 3,减少网络传输量 4,提高安全性. 缺点 移植性不好. 如何查看错误信息: SHOW ERROR; 如何调用该过程: 1,EXEC 过程名(参数 .. ) 2,CALL 过程名(参数 .. ) create or replace procedure pr01 is begin insert into mytest values('xxxx','xxxx'); end; ---------------------------- pl/sql基础 分类:过程(存储过程),函数,触发器,包 编写规范 常量:c_xx 变量:v_xx 游标:xxx_cursor 例外:e_error 1 create or replace procedure pr03(name varchar2, new_sal number) is 2 begin 3 update emp set sal=new_sal where ename=name; 4* end; ======================== 在Java中调用存储过程 // 调用存储过程 cs = conn.prepareCall("{call pr03(?,?)}"); cs.setString(1, "SMITH"); cs.setInt(2, 100); cs.execute(); PL/SQL 控制结构 条件分支 if - then end if if -- then -- else - end if CREATE OR REPLACE PROCEDURE PR06(NO NUMBER) IS --定义部分 V_JOB EMP.JOB%TYPE; BEGIN SELECT JOB INTO V_JOB FROM EMP WHERE EMPNO=NO; IF V_JOB='PRESIDENT' THEN UPDATE EMP SET SAL=SAL+1000 WHERE EMPNO=NO; ELSIF V_JOB='MANAGER' THEN UPDATE EMP SET SAL=SAL+500 WHERE EMPNO=NO; ELSE UPDATE EMP SET SAL=SAL+200 WHERE EMPNO=NO; END IF; END; ------------------------- CREATE OR REPLACE PROCEDURE PR6(NAME VARCHAR2) IS V_NUM NUMBER := 1; BEGIN LOOP INSERT INTO USERS1 VALUES(V_NUM, NAME); EXIT WHEN V_NUM = 10; V_NUM := V_NUM+1; END LOOP; END; ------------------------------ 分页 CREATE OR REPLACE PROCEDURE PR7 (BOOK_ID IN NUMBER, BOOK_NAME IN VARCHAR2, PUBLISH IN VARCHAR2) IS BEGIN INSERT INTO BOOK VALUES(BOOK_ID,BOOK_NAME,PUBLISH); END; CREATE OR REPLACE PROCEDURE PR8 (SPNO IN NUMBER,SPNAME OUT VARCHAR2) IS BEGIN SELECT ENAME INTO SPNAME FROM EMP WHERE EMPNO=SPNO; END; ------------- 建包 CREATE OR REPLACE PACKAGE TESTPACKAGE AS TYPE TEST_CURSOR IS REF CURSOR; END TESTPACKAGE; 建过程 CREATE OR REPLACE PROCEDURE PR9 (SPNO IN NUMBER,P_CURSOR OUT TESTPACKAGE.TEST_CURSOR) IS BEGIN OPEN P_CURSOR FOR SELECT * FROM EMP WHERE DEPTNO=SPNO; END; JAVA代码 cs = conn.prepareCall("{call pr9(?,?)}"); 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)); } ==================================== 分页完整过程 -- 包 -- CREATE OR REPLACE PACKAGE CREATE OR REPLACE PROCEDURE PAGER (TABLENAME IN VARCHAR2, PAGESIZE IN NUMBER, PAGENOW IN NUMBER, MYROWS OUT NUMBER,--总记录数 MYPAGECOUNT OUT NUMBER,--总页数 P_CURSOR OUT TESTPACKAGE.TEST_CURSOR -- 返回一个记录集 ) IS -- 定义部分 -- 定义SQL语句 V_SQL VARCHAR2(1000); V_BEGIN NUMBER := (PAGENOW-1)*PAGESIZE+1; V_END NUMBER := PAGENOW*PAGESIZE; BEGIN V_SQL := 'SELECT * FROM (SELECT T1.*,ROWNUM RN FROM (SELECT * FROM '|| TABLENAME ||') T1 WHERE ROWNUM<='|| V_END ||') WHERE RN>='|| V_BEGIN; OPEN P_CURSOR FOR V_SQL; V_SQL := 'SELECT COUNT(*) FROM '||TABLENAME; EXECUTE IMMEDIATE V_SQL INTO MYROWS; IF MOD(MYROWS,PAGESIZE)=0 THEN MYPAGECOUNT := MYROWS/PAGESIZE; ELSE MYPAGECOUNT := MYROWS/PAGESIZE; END IF; CLOSE P_CURSOR; END; / ------------------ CORE JAVA CODE ... cs = conn.prepareCall("{call pager(?,?,?,?,?,?)}"); 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(); int rowNum = cs.getInt(4); int pageCount = 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_ENAME EMP.ENAME%TYPE; BEGIN SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=&GNO; DBMS_OUTPUT.PUT_LINE('名字: ' || V_ENAME); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('EXCEPTION ACCURED!'); 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. 自定义例外 CREATE OR REPLACE PROCUDURE EX_TEST(NO NUMBER) IS MYEX EXCEPTION; BEGIN UPDATE EMP SET SAL := SAL+1000 WHERE EMPNO = NO; IF SQL%NOTFOUND THEN -- %NOTFOUND 表示没有UPDATE -- RAISE MYEX; 触发MYEX这个例外 RAISE MYEX; END IF; EXCEPTIN WHEN MYEX THEN DBMS_OUTPUT.PUT_LINE("没有更新任何用户"); END; ======================== ORACLE视图 视图是一个虚拟表,其内容由查询定义.同真实的表一样,视图包含一系列带有名称 的列和行数据.但是, 视图并不在数据库中以存储的数据值集形式存在.行和列数据 来自由定义视图的查询所引用的表,并且在引用视图时动态生成. 视图与表的区别: 视图不要空间,表要 视图不能添加索引 提高安全性. 创建视图: CREATE [OR REPLACE] VIEW XX AS SELECT XXX [WITH READ ONLY]; DROP VIEW XX; 20:59 2009-9-19 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |