玩转Oracle

小型数据库:access,foxbase
你该用什么数据库?
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