oracle10g笔记

一、oracle数据库结构

服务器参数文件oracle\product\10.1.0\Db_2\database\spfile*.ora

oracle\10.1.0\oradata\

1.参数文件

2.*.CTL 控制文件

3.*.DBF 数据文件

4.*.LOG 日志文件

c:\>sqlplus "sys/password as sysdba"登录系统

SQL>DESC v$controlfile描述控制文件

SQL>SELECT status,name FROM v$controlfile查看控制文件

SQL>DESC v$datafile描述数据文件

SQL>SELECT file#,status,name FROM v$datafile查看数据文件

SQL>DESC v$logfile描述日志文件

SQL>SELECT member FROM v$logfile查看日志文件

ORACLE内存结构

1.SGA总称

2.DB buffer数据缓存区

(默认缓存池，保持缓存池，再生缓存池)

3.Redo buffer日志缓存区

4.共享池

(库缓存区：共享SQL区、PL/SQL区，字典缓存区)

5.大共享池

6.固定SGA

ORACLE逻辑结构

表空间tablespace(块block、盘区extent、段segment)

表空间tablespace(数据文件datafile)

二、sqlplus的使用方法

isqlplus打开方式http://主机名:oracle访问端口/isqplus

isqlplus登录名:scott 密码:tiger

c:\>sqlplus "sys/password as sysdba"登录

c:\>sqlplus/nolog

SQL>connect sys/password as sysdba

SQL>startup

SQL>archive log list

SQL>startup mount正常启动

SQL>alter database archivelog

SQL>alter database open

SQL>startup nomount丢失恢复启动

SQL>shutdown immediate正常关闭

SQL>shutdown等待关闭

SQL>shutdown transactional等待立即关闭

SQL>shutdown abort强制关闭

SQL>help index查看sqlplus帮助命令

SQL>SELECT * FROM dept;

SQL>SET SQLBLANKLINES ON设置sqlplus行输入连接为开

SQL>SELECT * FROM dept WHERE deptno=&tt设置输入参数

Enter value for tt:

SQL>list查看输入缓存区的命令，或者只输入l

SQL>l2 4查看缓存区第二行到第四行的输入

SQL>change修改刚刚输入的语句，或者只输入c

SQL>c/N/M用M替换N

SQL>/执行缓存区里的命令

SQL>? CHANGE说明某个命令的用法，比如CHANGE

SQL>DEL 4删除缓存区某一行的命令，比如第四行

SQL>DEL 2 3

SQL>save c:\oracle\test.txt将缓存区中的命令保存到文件中

SQL>get c:\oracle\test.txt获取文件中的命令

SQL>edit 调用记事本编辑刚刚输入的语句

SQL>COLUMN deptno HEADING "编号"为查询的某一列别名显示，或者写成COL方法

SQL>SELECT * FROM dept;

SQL>DESCRIBE用于描述一个对象，或者写成DESC

SQL>DESC dept比如描述dept表的结构

SQL>COL dname FORMAT A10为某列格式化显示

SQL>COL dname FORMAT A1O HEADING "部门名称"

SQL>SELECT * FROM dept;

SQL>exit退出sqlplus模式，退出后缓存中的别名列自动消失

SQL>sqlplus scott/tigger，通过scott方式登录，不需要引号

SQL>COL deptno FORMAT 999,999,999格式化显示

SQL>connect "sys/test1234 as sysdba"通过sysdba查看数据文件的大小，以999,999,999格式显示

SQL>SELECT byte FORM v$datafile

SQL>SELECT byte,name FROM v$datafile

SQL>connect scott/tiger

SQL>SET LINESIZE 50一次查看多少列

以下是为查看数据设置左、中、右标题

SQL>TTITLE CENTER "我的标题" SKIP 1-

>LEFT "测试报表" RIGHT "页"-

>FOTMAT 999 SQL.PNO SKIP 2

SQL>SELECT * FROM dept;

SQL>BREAK ON pub在某一列上相同值不显示，如在pub列上

SQL>COMPUTE COUNT LABEL "计数" OF books_name ON pub

查询pub列名称相同的books_name的统计结果，显示为计数

SQL>spool c:\oracle\spool.txt将查询结果保存到文件中，表示开始点

以下是要查询保存的语句，将查询结果保存到spool.txt中

SQL>SELECT * FROM books;

SQL>spool off表示查询结果保存结束点

SQL>edit c:\oracle\spool.txt用记事本打开spool.txt文件，查看保存是否成功

二、结构化查询语句

1.DDL

CREATE

ALTER

DROP

2.DCL

GRANT

REVOKE

3.DML

SELECT

INSERT

DELETE

UPDATE

SQL>connect scott/tiger

SQL>CREATE TABLE abc(a varchar2(10), b char(10));

SQL>ALTER TABLE abc ADD c number;

SQL>ALTER TABLE abc DROP COLUMN c;

SQL>connect tt/tt11

SQL>SELECT * FROM scott.dept;

SQL>conn scott/tiger

SQL>GRANT SELECT ON dept TO tt;

SQL>conn tt/tt11

SQL>SELECT * FROM scott.dept;

SQL>conn scott/tiger

SQL>SELECT * FROM abc

SQL>INSERT INTO abc(a,b) VALUES('abc','xy')

SQL>INSERT INTO abc VALUES('bcd','123')

SQL>UPDATE abc SET b='ttt'

SQL>UPDATE abc b='YYY' WHERE a='abc';

SQL>DELETE FROM abc WHERE a='abc';

SQL>SELECT * FROM abc;

常用系统函数

1.字符

SQL>SELECT LENGTH('ABCDEF') FROM dual;

SQL>SELECT LTRIM(' ABCDEF') FROM dual;

SQL>SELECT RTRIM('ABCDEF ') FROM dual;

SQL>SELECT TRIM(' ABCDEF ') FROM dual;

SQL>UPDATE aa SET a3='aa';

SQL>SELECT LENGTH(A2),LENCTH(A3) FROM aa;

SQL>SELECT SUBSTR('abcdefg',2,3) FROM dual;

SQL>SELECT SUBSTR('sbcdefg',LENGTH('qbcdefg')-3+1,3) FROM dual;

REPLACE

2.日期

SQL>SELECT sysdate FROM dual;

SQL>SELECT current_date FROM dual;

SQL>ALTER SESSION SET NLS_DATE_FORMAT='dd-mon-yyyy hh:m1:ss';

SQL>SELECT NEXT_DAY(sysdate,'星期三') FROM dual;

3.转换

SQL>SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') FROM dual;

SQL>SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM dual;

SQL>SELECT TO_DATE('12-3月-04') FROM dual;

SQL>SELECT TO_NUMBER('00333') FROM dual;

4.聚集函数

SQL>SELECT * FROM books

SQL>SELECT max(price) FROM books;

SQL>SELECT min(price) FROM books;

SQL>SELECT sum(price) FROM books;

SQL>SELECT avg(price) FROM books;

SQL>SELECT count(price) FORM books;

SQL>SELECT count(*) FROM books;

SQL>SELECT * FROM books WHERE price>20;

5.其他

SQL>SELECT user FROM dual;

SQL>connect tt/tt11;

SQL>connect scott/tiger;

SQL>SELECT * FROM e;

SQL>SELECT SUM(DECODE(SEX,'男',1,0)) 男人数,SUM(DECODE(SEX,'女',1,0) 女人数 FROM e;判断计数，为真加1，否则加零

SQL>SELECT a1,nvl(a2,'未输入') a2 FROM aa;对空值处理，填充'未输入'

SQL>SELECT * FROM aa WHERE a2=NULL;

SQL>SELECT * FROM aa ORDER BY a1 ASC;

SQL>SELECT * FROM aa ORDER BY a1 DESC;

SQL>SELECT DISTINCT a1 FROM aa;

SQL>SELECT ALL a1 FROM aa;

分组语句

SQL>SELECT * FROM books;

SQL>SELECT sum(price*qty) FROM books

SQL>SELECT sum(price*qty) FROM books GROUP BY pubs;

SQL>SELECT pub,books_name,sum(price*qty) FROM books GROUP BY pubs,books_name;

SQL>SELECT pub,sum(price*qty) FROM books GROUP BY pub,books_name;

SQL>SELECT pub,sum(price*qty) FROM books WHERE price>30 GROUP BY pubs;

SQL>SELECT pub,sum(price*qty) FROM books GROUP BY pub HAVING sum(price)>60;

SQL>SELECT a1,count(a1) FROM aa GROUP BY a1 HAVING COUNT(a1)>1;

模糊查询

SQL>SELECT * FROM aa;

SQL>SELECT * FROM aa WHERE a1 LIKE 'a_';

SQL>SELECT * FROM aa WHERE a1 LIKE 'a%';

SQL>SELECT * FROM aa WHERE a1 LIKE '_a';

SQL>SELECT * FROM aa WHERE a1 LIKE '%a';

SQL>SELECT * FROM aa WHERE a1 LIKE '%a%';

连接查询

SQL>SELECT * FROM e;

SQL>SELECT * FROM d;

SQL>SELECT eid 编号,ename 姓名,sex 性别,d.name 所在部门 FROM employee e,department d WHERE e.id=d.id;

内连接

SQL>SELECT eid 编号,ename 姓名,sex 性别,d.name 所在部门 FROM employee e join department d on e.id=d.id;

以下为外连接

SQL>SELECT eid 编号,ename 姓名,sex 性别,d.name 所在部门 FROM employee e join department d on e.id=d.id(+);

左连接，以左边为主，右边为辅

SQL>SELECT eid 编号,ename 姓名,sex 性别,d.name 所在部门 FROM employee e join department d on e.id(+)=d.id;

右连接，以右边为主，左边为辅

子查询

无关子查询

SQL>SELECT * FROM e WHERE id IN (SELECT id FROM d);

相关子查询，与父表有关

SQL>SELECT * FROM e WHERE id IN (SELECT id FROM d WHERE id=e.id AND id='03');

用IN的方式，子查询的字段要与父查询的条件匹配

判断子查询结果是否存在，存在就执行父查询

SQL>SELECT * FROM e WHERE EXISIS (SELECT id FROM d)

SQL>SELECT * FROM e WHERE NOT EXISIS (SELECT * FROM d WHERE id=e.id);

联合查询

SQL>SELECT e.id,ename FROM e

UNION

SELECT id,name FROM d;

SQL>SELECT id FROM e

UNION

SELECT id FROM d;

返回两个SQL语句中都出现的行,ORACLE数据库特有

SQL>INTERSECT

SQL>SELECT id FROM e

INTERSECT

SELECT id FROM d;

将一个表的查询结果，插入到另外一张表中

SQL>INSERT INTO e(eid,ename) SELECT id,name FROM d;

将一个表的查询结果，插入到新建的一张表中

SQL>CREATE TABLE ttt AS (SELECT * FROM e);

SQL>CREATE TABEL t AS SELECT eid,ename FROM e WHERE eid='001';

SQL>SELECT * FROM t;

四 PL/SQL

PL/SQL块结构

DECLARE

BEGIN

EXCEPTION 异常处理

END

变量声明，命名规则

变量由字符开头，可以包含：数字，下划线，$，#

不能是系统关键字

SQL>DECLARE

2 x varchar2(10);

3 BEGIN

4 x:='This is..'; 赋值的写法

5 DBMS_OUTPUT.PUT_LINE('x的值为：'||x) ||连接加号

6 END;

7 /

SQL> SET SERVEROUTPUT ON SIZE 10000 设置服务器输出大小

SQL> L 显示缓存中的语句

SQL> / PL/SQL执行语句

SQL>DECLARE

2 x varchar2(10);

3 BEGIN

4 x:='This is..'; 赋值的写法

5 DBMS_OUTPUT.PUT_LINE('x的值为：'||x) ||连接加号

6 END;

7 四 PL/SQL

PL/SQL块结构

DECLARE

BEGIN

EXCEPTION 异常处理

END

变量声明，命名规则

变量由字符开头，可以包含：数字，下划线，$，#

不能是系统关键字

SQL>DECLARE

2 x varchar2(10);

3 BEGIN

4 x:='This is..'; 赋值的写法

5 DBMS_OUTPUT.PUT_LINE('x的值为：'||x) ||连接加号

6 END;

7 /

SQL> SET SERVEROUTPUT ON SIZE 10000 设置PL/SQL服务器输出大小

SQL> L 显示缓存中的语句

SQL> / PL/SQL执行语句

SQL>DECLARE

2 x varchar2(10);

3 BEGIN

4 x:='This is..'; 赋值的写法

5 --DBMS_OUTPUT.PUT_LINE('x的值为：'||x); 注释掉了

  DBMS_OUTPUT.PUT('x的值为：'||x);

  DBMS_OUTPUT.NEW_LINE; 在一个新行显示

6 END;

7 /

多行注释用/* */

SQL>DECLARE

2 x varchar2(10);

  y INTEGER:=123;

3 BEGIN

4 x:='This is..'; 赋值的写法

5 --DBMS_OUTPUT.PUT_LINE('x的值为：'||x); 注释掉了

  DBMS_OUTPUT.PUT('x的值为：'||x||y);

  DBMS_OUTPUT.NEW_LINE; 在一个新行显示

6 END;

7 /

SQL>save c:/plsql_01.txt 保存到文件

SQL>@ c:/plsql_01.txt 调用文件

SQL>DECLARE

2 x varchar2(10);

  y string(10):=123; 必须给定长度string

3 BEGIN

4 x:='This is..'; 赋值的写法

5 --DBMS_OUTPUT.PUT_LINE('x的值为：'||x); 注释掉了

  DBMS_OUTPUT.PUT('x的值为：'||x||y);

  DBMS_OUTPUT.NEW_LINE; 在一个新行显示

6 END;

7 /

SQL>DECLARE

2 a number;

3 b varchar2(10);

4 BEGIN

5 a:=2;

6 IF a=1 THEN

7 b:='A';

8 ELSEIF a=2 THEN

9 b:='B';

10 ELSE

11 b:='C'

12 END IF;

13 DBMS_OUTPUT.PUT_LINE('b的值是：'||b);

14 END;

15 /

SQL>save c:/plsql_02.txt 保存到文件

SQL>@ c:/plsql_02.txt 调用文件

DECLARE

a number;

b varchar2(10);

BEGIN

a:=2;

CASE

WHEN a=1 THEN b:='A'

WHEN a=2 THEN b:='B'

WHEN a=3 THEN b:='C'

ELSE

b:='Others';

END CASE;

DBMS_OUTPUT.PUT_LINE('b的值是：'||b);

END;

/

循环语句

基本循环

SQL>DECLARE

x number;

BEGIN

x:=0;

LOOP

x:=0

LOOP

x:=x+1

IF x>3 THEN

EXIT;

END IF;

DBMS_OUTPUT.PUT_LINE('内：x='||x);

END LOOP;

DBMS_OUTPUT.PUT_LINE('外：x='||x);

END;

/

内：x=1

内：x=2

外：x=3

SQL>save c:\plsql_loop01.txt

created file c:\plsql_loop01.txt

SQL>edit c:\plsql_loop01.txt

SQL>@ c:\plsql_loop01.txt

WHILE循环

SQL>DECLARE

x number;

BEGIN

x:=0;

LOOP

x:=0

LOOP

x:=x+1

EXIT WHEN x>3;

END IF;

DBMS_OUTPUT.PUT_LINE('内：x='||x);

END LOOP;

DBMS_OUTPUT.PUT_LINE('外：x='||x);

END;

SQL>DECLARE

x number;

BEGIN;

x:=0;

WHILE x<3 LOOP;

x:=x+1;

DBMS_OUTPUT.PUT_LINE('内：x='||x);

END LOOP;

DBMS_OUTPUT.PUT_LINE('外：x='||x);

/

内：x=1

内：x=2

内：x=3

内：x=4

外：x=4

SQL>save c:\plsql_loop03.txt

FOR循环

SQL>BEGIN

FOR i in 1..5 LOOP

DBMS_OUTPUT.PUT_LINE('i='||i);

END LOOP;

DBMS_OUTPUT.PUT_LINE('END OF FOR LOOP');

END;

/

SQL>BEGIN

FOR i in REVERSE 1..5 LOOP从大到小循环

DBMS_OUTPUT.PUT_LINE('i='||i);

END LOOP;

DBMS_OUTPUT.PUT_LINE('END OF FOR LOOP');

END;

/

SQL>save c:\plsql_loop04.txt

GOTO语句，不建议用

DECLARE

x number;

BEGIN

x:=0;

<<repeat_loop>>

x:=x+1

DBMS_OUTPUT.PUT_LINE(x)

IF x<3 THEN

GOTO repeat_loop;

END IF;

SQL>save c:\plsql_loop05.txt

异常处理

DUP_VAL_ON_INDEX 向有唯一约束的表中插入重复行

NO_DATA_FOUND 在一个SELECT INTO语句中无返回值

TOO_MANY_ROWS SELECT INTO 语句返回多行

VALUE_ERROR 一个算法、转换、截断或大小约束发生错误

ZERO_DIVIDE 发生被零除

SQL>DECLARE

test varchar2(10)

BEGIN

SELECT name INTO test FROM deptment WHERE id='tt';

DBMS_OUTPUT.PUT_LINE(test);

END;

/

未查找到数据，系统异常

SQL>DECLARE

test varchar2(10)

BEGIN

SELECT name INTO test FROM deptment WHERE id='tt';

DBMS_OUTPUT.PUT_LINE(test);

EXCEPTION

WHEN NO_DATA_FOUND THEN 给定异常处理

DBMS_OUTPUT.PUTLINE('没有找到数据!')

END;

/

SQL>DECLARE

tname varchar2(10);

e exception;

BEGIN

SELECT name INTO tname FROM deptment WHERE id='01'

IF tname<>'B部门' THEN

RAISE e;

END IF;

DBMS_OUTPUT.PUT_LINE(tname);

EXCEPTION

WHEN e THEN

DBMS_OUTPUT.PUT_LINE('错误，不是需要的B部门!');

END;

/

复合变量

SQL>DECLARE

TYPE myrecord is RECORD(

id varchar2(10),

name varchar2(10));

real_record myrecord;

BEGIN

SELECT emp_id,emp_name INTO real_record FROM emp WHERE emp_id='001';

DBMS_OUTPUT.PUT_LINE(real_record.id||','||real_record.name);

END;

/

SQL>DESC emp;

SQL>EDIT

SQL>DECLARE

TYPE myrecord is RECORD(

id emp.eid%TYPE,与表中的字段类型一致

name varchar2(10));

real_record myrecord;

BEGIN

SELECT emp_id,emp_name INTO real_record FROM emp WHERE emp_id='001';

DBMS_OUTPUT.PUT_LINE(real_record.id||','||real_record.name);

END;

/

SQL>DECLARE

myrec emp%ROWTYPE;复合类型的子类型与表中字段相同

BEGIN

SELECT * INTO myrec FROM emp WHERE emp_id='001';

DBMS_OUTPUT.PUT_LINE(myrec.eid||','||myrec.ename||','||myrec.sex);

END;

/

 

五 游标

SQL>DECLARE

CURSOR mycur IS

SELECT * FROM books;

myrecord books%ROWTYPE;

BEGIN

OPEN mycur;打开游标

FETCH mycur INTO myrecord;把游标记录放到结果集中

WHILE mycur%FOUND LOOP 如果游标记录不为空，将记录循环显示出

DBMS_OUTPUT.PUT_LINE(myrecord.books_id||','||myrecord.books_name);

FETCH mycur INTO myrecords;把游标记录放到结果集中，游标进了一步

END LOOP;

CLOSE mycur;

END;

/

SQL>DECLARE

CURSOR cur_para(id varchar2) IS

SELECT books_name FROM books WHERE books_id=id;

t_name books.books_name%TYPE;

BEGIN

OPEN cur_para('0001');

LOOP

FETCH cur_para INTO t_name;

EXIT WHEN cur_para%NOTFOUND;当游标中的记录不存在时

END LOOP;

CLOSE cur_para;

END;

/

SQL>DECLARE

CURSOR cur_para(id varchar2) IS

SELECT books_name FROM books WHERE books_id=id;

BEGIN

DBMS_OUTPUT.PUT_LINE('******结果集为：******')

FOR cur IN cur_para('0001') LOOP 显示books_id等于'0001'的记录

DBMS_OUTPUT.PUT_LINE(cur.books_name);

END LOOP;

END;

/

SQL>DECLARE

t_name books.books_name%TYPE;

CURSOR cur(id varchar2) IS

SELECT books_name FROM books WHERE books_id=id;

BEGIN

IF cur%ISOPEN THEN 判断游标是否打开了

DBMS_OUTPUT.PUT_LINE('游标已经被打开!');

ELSE

OPEN cur('0003'); 如果没有打开，打开book_id等于'0003'的记录

END IF;

FETCH cur INTO t_name;

CLOSE cur;

DBMS_OUTPUT.PUT_LINE(t_name);

END;

/

 

SQL>DECLARE

t_name varchar2(10);

CURSOR mycur IS

SELECT name FROM deptment;

BEGIN

OPEN mycur;

LOOP

FETCH mycur INTO t_name;

EXIT WHEN mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;

DBMS_OUTPUT.PUT_LINE('游标mycur的ROWCOUNT是：'||mycur%ROWCOUNT);用于游标的统计

END LOOP;

CLOSE mycur;

END;

/

游标参数

%FOUND

%ISOPEN

%NOTFOUND

%ROWCOUNT

利用游标来修改数据

SQL>DECLARE

CURSOR cur IS

SELECT name FROM deptment FOR UPDATE;

text varchar2(10);

BEGIN

OPEN cur;

FETCH cur INTO text;

WHILE cur%FOUND LOOP

UPDATE deptment SET name=name||'_t' WHERE CURRENT OF cur;

FETCH cur INTO text;

END LOOP;

CLOSE cur;

END;

/

隐式游标声明

SQL>BEGIN

FOR cur IN(SELECT name FROM deptment) LOOP

DBMS_OUTPUT.PUT_LINE(cur.name);

END LOOP;

END;

/

数据量大的时候不建议使用游标

 

PL/SQL高级应用存储过程

SQL>CREATE OR REPLACE PROCEDURE myproc (id IN varchar2)

IS

name varchar2(10);

BEGIN

SELECT books_name INTO name FROM books WHERE books_id=id;

DBMS_OUTPUT.PUT_LINE(name);

END myproc;

/

IN 表示是输入参数

OUT 表示是输出参数

查询创建的过程编译错误

SQL>SHOW ERRORS PROCEDURE myproc;

用存储过程执行

SQL>DECLARE

tid varchar2(10);

BEGIN

tid:='0001';

myproc(tid);

END;

/

用执行语句执行

SQL>EXECUTE myproc('0001');

SQL>CREATE OR REPLACE PROCEDURE myproc2(id IN varchar2,

name OUT varchar2)

IS

BEGIN

SELECT books_name INTO name FROM books WHERE books_id=id;

END;

/

SQL>DECLARE

tid varchar2(10);

tname varchar2(10);

BEGIN

tid:='0001';

myproc2(tid,tname);

 

六 视图

SQL>CREATE OR REPLACE VIEW myview

AS

SELECT * FROM books;

SQL>SELECT * FROM myview;

SQL>INSERT INTO myview(books_id) VALUES('0008')

SQL>CREATE OR REPLACE VIEW myview

AS

SELECT * FROM books WHERE price>30

SQL>INSERT INTO myview VALUES('0009','ABCDE',23.5,'AA')

SQL>CREATE OR REPLACE VIEW myview

AS

SELECT * FROM books WHERE price>30

WITH CHECK OPTION 检查在插入时price>30不能插入数据，只对视图起作用

SQL>SELECT * FROM deptment;

SQL>SELECT eid,ename,sex,d.id,d.name FROM emp e,deptment d

WHERE

e.id=d.id;

SQL>CREATE OR REPLACE VIEW v_emp_dept

AS

SELECT eid,ename,sex,d.id,d.name FROM emp e,deptment d

WHERE

e.id=d.id;

SQL>INSERT INTO v_emp_dept VALUES('007','ABC','33','TT');

如果组成视图的基表两个，两个以上，不允许同时对两个和两个以上的表更新

SQL>CREATE OR REPLACE VIEW v_read

AS

SELECT eid,ename FROM emp

WITH READ ONLY; 设置视图为只读

DISTINCT情况下也是不能更新的，因为是多条，这样的视图也是不能更新的

SQL>DESC dba_views 查看所有视图

SQL>DESC user_views 查看当前用户的视图

SQL>SELECT text FROM user_views WHERE view_name='V_READ';

用数据字典来查看视图的语法，语句

 

同义词 DBA创建公用同义词 一般用户创建专有同义词

SQL>SELECT user FROM dual;

SQL>SELECT * FROM dept;

SQL>SELECT * FROM scott.dept;

创建同义词

SQL>CREATE SYNONYM dept FOR scott.dept;

SQL>connect tt/tt11

SQL>SELECT * FROM dept;

SQL>connect sys/test1234 as sysdba

SQL>DROP SYNONYM dept

创建公有同义词

SQL>CREATE PUBLIC SYNONYM dept FOR scott.dept;

SQL>SELECT * FROM dept;

SQL>connect tt/tt11

SQL>SELECT * FROM dept;

SQL>DESC dba_synonyms查看同义词的数据字典

SQL>CONNECT tt/tt11

SQL>CREATE SYNONYM dep FOR scott.dept;

SQL>SELECT * FROM dept;

SQL>desc user_synonyms

SQL>SELECT synonym_name,table_name,table_owner FROM

user_synonyms;

 

序列

创建序列

SQL>CREATE SEQUENCE myseq

START WITH 1 开始处为1

INCREMENT BY 1 增长为1

ORDER 默认排序 从小到大

NOCYCLE; 不循环

查询下一个序列的值

SQL>SELECT myseq.NEXTVAL FROM dual;序列为1

SQL>SELECT myseq.NEXTVAL FROM dual;序列为2

SQL>SELECT myseq.NEXTVAL FROM dual;序列为3

查看当前序列的值

SQL>SELECT myseq.CURRVAL FROM dual;必须写完NEXTVAL的查询，

才能查询CURRVAL

创建一个表

SQL>CREATE TABLE auto(a number,varchar2(10));

执行三条查询，当前插入的序列就从4开始

SQL>INSERT INTO auto VALUES(myseq.NEXTVAL,'dfd');序列为4

SQL>INSERT INTO auto VALUES(myseq.NEXTVAL,'dfd');序列为5

SQL>INSERT INTO auto VALUES(myseq.NEXTVAL,'dfd');序列为6

SQL>SELECT * FROM auto;

SQL>DESC dba_sequences 查看的序列的数据字典

SQL>SELECT sequence_name,sequence_owner FROM dba_sequence

WHERE sequence_owner='TT'

SQL>SELECT user FROM dual;查看当前登录用户名称

SQL>ALTER SEQUENCE myseq INCREMENT BY 3;更改序列增长为3

 

七 触发器

SQL>SELECT * FROM books;

SQL>DELETE FROM books WHERE books_id='0011'

提交

SQL>commit;

SQL>DELETE FROM books WHERE books_id='0010'

回滚

SQL>rollback;

SQL>DELETE FROM books WHERE books_id='00d10';

事务的特性：原子性、一致性、隔离性、永久性

SQL>SELECT * FROM emp;

SQL>SELECT * FROM deptment;

SQL>CREATE OR REPLACE TRIGGER del_deptid

AFTER DELETE ON deptment

FOR EACH ROW 行级触发器

BEGIN

DELETE FROM emp WHERE id=:old.id;一种特殊的方式，强记，逻辑表

END del_deptid;

/

SQL>DELETE FROM deptment WHERE id='01'

SQL>SELECT * FROM emp;

SQL>SELECT * FROM deptment;

SQL>CREATE OR REPLACE TRIGGER insert_dept

AFTER INSERT ON department

FOR EACH ROW

BEGIN

INSERT INTO emp(eid,ename,id) VALUES ('121','QWERT','new.id');一种特殊的方式，强记，逻辑表

END;

/

SQL>INSERT INTO deptment VALUES('77','GHJ');

SQL>SELECT * FROM emp;

 OLd New

INSERT  无      有

DELETE  有      无

UPDATE  有      有

SQL>CREATE OR REPLACE TRIGGER update_dept

AFTER UPDATE ON deptment

FOR EACH ROW

BEGIN

UPDATE emp SET id=:new.id WHERE id=:old.id

END;

/

SQL>UPDATE department SET id='yy' WHERE id='01';

SQL>SELECT * FROM emp;

SQL>CREATE OR REPLACE TRIGGER books_delete

AFTER DELETE ON books

FOR EACH ROW

BEGIN

IF:old.book_id='0001' THEN

RAISE_APPLICATION_ERROR(-20000,'不允许删除！');

END IF;

END;

/

SQL>SELECT * FROM books;

SQL>DELETE FROM books WHERE books_id='0009'

SQL>DELETE FROM books WHERE books_id='0001'

触发器里不能写rollback,DBMS.OUTPUT_LINE

SQL>SELECT * FROM aa;

SQL>CREATE TABLE mylog(curr_user varchar2(100),curr_date,act char(1));

SQL>CREATE OR REPLACE TRIGGER dm1_aa

AFTER INSERT OR DELETE OR UPDATE ON aa

BEGIN

IF INSERTING THEN 语句级的触发器

INSERT INTO mylog VALUES(user,sysdate,'I')

ELSEIF DELETING THEN

INSERT INTO mylog VALUES(user,sysdate,'D')

ELSE

INSERT INTO mylog VALUES(user,sysdate,'U')

END IF;

END;

/

SQL>SELECT * FROM aa;

SQL>UPDATE aa SET a2='test' WHERE a1='cha';

SQL>SELECT * FROM aa;

SQL>DELETE FROM aa WHERE a2='dee';

SQL>INSERT INTO aa VALUES('tt','yy','uu')

SQL>SELECT * FROM mylog;

触发器日期

SQL>SELECT curr_user,TO_CHAR(curr_date,'yyyy-mm-dd' hh24:mi:ss')

D FROM mylog;

SQL>SELECT * FROM auto;

SQL>SELECT myseq.NEXTVAL FROM dual;

SQL>SELECT myseq.NEXTVAL FROM dual;

SQL>INSERT INTO auto VALUES(myseq.nextval,'SD');

SQL>SELECT * FROM auto;

SQL>CREATE OR REPLACE TRIGGER set_no

BEFORE INSERT ON auto

FOR EACH ROW

DECLARE

sn number(5);

BEGIN

SELECT myseq.nextval INTO sn FROM dual;

:NEW.a:=sn;用序列来代替插入的NEW.a的值

END;

/

SQL>INSERT INTO auto VALUE(21,'df');

SQL>INSERT INTO auto VALUE(2321,'ddtt');

SQL>SELECT * FROM auto;

替换触发器

SQL>INSERT INTO v_emp_dept VALUE('456','test','f','33','hg');

在ORACLE中替换触发器只能用在视图上，用于解决更新两个和两个以上的基表

SQL>CREATE OR REPLACE TRIGGER tr_v_e_d

INSTEAD OF INSERT ON v_emp_dept

FOR EACH ROW

BEGIN

INSERT INTO deptment VALUES(:new.id,:new.name);

INSERT INTO emp(eid,ename,sex,id) VALUES (:new.eid,:new.ename,

:new.sex,:new.id)

END;

/

SQL>INSERT INTO v_emp_dept VALUE('456','test','f','33','hg');

SQL>SELECT * FROM v_emp_dept;

八 安全管理

ORA_DBA Administrator

http://localhost:5500/em Oracle Manager System

创建用户 显示SQL

SQL> connect test/test1234

SQL> SELECT * FROM scott.dept;

SQL> connect sys/test1234 as sysdba

设置权限

SQL> GRANT SELECT ON scott.dept TO test;

改变连接方式

SQL> conn / as sysdba

改变默认表空间

SQL> ALTER USER test DEFAULT TABLESPACE tt;

SQL> ALTER USER test DEFAULT TABLESPACE users;

更改密码

SQL> ALTER USER test IDENFIFED BY test11;

SQL> conn test/test11

SQL> conn / as sysdba

锁定帐号

SQL> ALTER USER test ACCOUNT LOCK;

解除锁定

SQL> ALTER USER test ACCOUNT UNLOCK;

SQL> CREATE USER qqq IDENTIFIED BY qqq123

DEFAULT TABLESPACE tt;

SQL> connect qqq/qqq123

SQL> conn / as sysdba

SQL> GRAINT CONNECT TO qqq;

SQL> SELECT * FROM scott.dept;

SQL> GRANT SELECT ON scott.dept TO qqq;

SQL> conn / as sysdba

给予授权的权限

SQL> GRANT SELECT ON scott.dept TO test WITH GRANT OPTION

SQL> connect test/test11

SQL> GRANT SELECT ON scott.dept TO qqq;

SQL> GRANT INSERT ON scott.dept TO test WITH GRANT OPTION

包括INSERT、DELETE、UPDATE

SQL> GRANT ALL ON scott.dept TO test WITH GRANT OPTION

对过程进行授权

SQL> GRANT EXECUTE ON tt.proc01 TO test

SQL> conn test/test11

SQL> CREATE USER abc IDENTIFIED BY abc;

SQL> conn / as sysdba

创建用户的权限

SQL> GRANT CREATE USER TO test;

SQL> conn test/test11

SQL> DROP USER abc

SQL> conn / as sysdba

删除用户的权限

SQL> GRANT DROP USER TO test;

SQL> conn test/test11

SQL> DROP USER abc;

SQL> conn / as sysdba

SQL> GRANT CREATE USER TO test;

SQL> GRANT CREATE USER TO qqq;

对象授权

SQL> GRANT CREATE USER TO test WITH GRANT OPTION;

系统授权

SQL> GRANT CREATE USER TO test WITH ADMIN OPTION;

SQL> GRANT CREATE USER TO qqq;

SQL> conn qqq/qqq123;

SQL> CREATE USER ty IDENTFIED BY ty;

取消授权

SQL> REVOKE SELECT ON scott.dept FROM test;

SQL> REVOKE CREATE USER FROM test;

角色授权

创建角色

SQL> CREATE ROLE myrole;

分配角色

SQL> GRANT SELECT ON tt.emp TO myrole;

SQL> GRANT SELECT ON tt.deptment TO myrole;

SQL> connect test/test11

SQL> SELECT * FROM tt.emp;

SQL> conn / as sysdba

将角色给用户，或者说添加用户到角色

SQL> GRANT myrole TO test;

SQL> connect test/test11

SQL> SELECT * FROM tt.emp;

SQL> conn / as sysdba

SQL> GRANT myrole TO qqq;

SQL> connect qqq/qqq123

SQL> SELECT * FROM tt.emp;

ORACLE Enterprise Manager 概要文件 创建概要文件，授权给用户

 

九 表空间的管理

SQL> SELECT user FROM dual;

新建一个表空间

SQL> CREATE TABLESPACE tabs

DATAFILE 'C:\oracle\product\10.1.0\oradata\test\tabs.dbf'

SIZE 10M;

为用户设置默认表空间

SQL> ALTER USER test DEFAULT TABLESPACE tabs;

SQL> connect test/test11

SQL> CREATE TABLE tre(t char(10),re number);

SQL> conn / as sysdba

授予创建表空间的权限

SQL> GRANT UNLIMITED TABLESPACE,DBA TO test;

SQL> connect test/test11

SQL> CREATE TABLE tre(t char(10),re number);

创建一个表放到默认表空间中

SQL> CREATE TABLE ty(t char(10),y varchar2(10)) TABLESPACE TT;

 

十 表的管理

实体完整性，主键约束，域完整性是指插入数据不能大于类型的约束

SQL> SELECT * FROM emp;

SQL> INSERT INTO emp(eid,ename) VALUE ('001','test22');

建立主键约束

SQL> ALTER TABLE nn ADD CONSTRAINT pk_nn PRIMARY KEY(n1);

参照完整性,参照表需要被参照表的字段

SQL> SELECT * FROM nn;

SQL> CREATE TABLE mm(m1 char(10),m2 varchar2(10),n1 varchar2(10)).

SQL> INSERT INTO mm VALUE('avd','dd','gg');

SQL> SELECT * FROM mm;

SQL> ALTER TABLE mm ADD CONSTRAINT fk_mm FOREGIN KEY(n1)

REFERENCE nn(n1);

check约束

SQL> DESC emp

SQL> UPDATE emp SET sex='男' WHERE eid='001'

SQL> ALTER TABLE emp ADD CONSTRAINT ck_emp_sex CHECK(sex='男'

or sex='女');

SQL> DESC dba_constraints查看一个表的约束情况

查看一个表的具体的约束

SQL> SELECT constraint_name,constraint_type FROM user_constraints

WHERE table_name='EMP';

SQL> desc emp

索引，索引是建立在表字段上的

在表上的字段上建立索引

SQL> CREATE INDEX my_mm_idx ON mm(m1);

位图索引

SQL> SELECT * FROM emp;

SQL> CREATE bitmap INDEX bit_emp ON emp(sex);

唯一约束的索引

SQL> CREATE UNIQUE INDEX myidx ON mm(m2);

 

十一 SQL LOAD

SQL>exit

SQL>sqlldr

SQL>

记事本创建两个文件

1.数据文件

abc,xyz

def,qqq

fff,ggg

save as c:\loader.txt

2.控制文件

load data

infile 'c:\loader.txt'

append

into table mm(

m1 position(1:3) char,

m2 position(5:7) char)

save as c:\cont.ctl

SQL>sqlldr tt/tt11 control=c:\cont.ctldata=c:\loader.txt通过控制文件写入数据

SQL>sqlplus tt/tt11

SQL>SELECT * FROM mm;

load data

infile 'c:\loader.txt'

append

into table mm(

m1 lterminated by ",",

m2 char terminated by ",")如果不是固定长度，用","区分

SQL>sqlplus tt/tt11

SQL>SELECT * FROM mm;

十二 OEM的配置

Enterprise Manger 企业管理工具(IE登录方式)

sys SYSDBA方式登录

性能、管理、维护

十三 监听程序及服务的配置

TCP/IP原理 1521端口

windows管理 服务 ORACLE Listener

c:\>lsnrctl status 状态

c:\>lsnrctl start 打开

c:\>lsnrctl stop 关闭

c:\>lsnrctl start lisnt_name 指定名称打开

Net Manager ORACLE网络管理程序

添加 监听程序 如listener1 指定监听端口号如1522

新加一个数据库名称

c:\>lsnrctl start listener1

c:\oracle\product\10.1.0\Db_2\NETWORK\ADMIN\listener.ora 监听程序的配置文件

Net Configuration Assistant 用数据库管理助手配置监听程序

c:\oracle\product\10.1.0\Db_2\NETWORK\ADMIN\lnsnames.ora

SQL>connect tt/tt11@ora ora为刚刚配置的监听服务

SQL>SELECT * FROM emp

十四 数据库备份与恢复

C:\>exp tt/tt11@test

Enter array fetch buffer size:4096>5000

Export file:EXPDAT.DMP>c:\mybak.dmp

<1>E<ntire database>, <2>U<sers>, or <3>T<ables>: <2>U >deptment dept

EXP-00012:deptme?...is not a valid export mode

<1>E<ntire database>, <2>U<sers>, or <3>T<ables>: <2>U >t

Export table data <yes/no>: yes > yes

Compress extents <yes/no>: yes > yes

Export done in ZHS16GBK charater set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path...

Table<T> or Partition<T:P> to be exported: <RETURN to quit> >

Export terminated successfully with warnings

详细导出

c:\>exp tt/tt11@test

>5000

>c:mybak.dmp

>yes

>

>t

>emp

恢复导入

c:\>sqlplus "tt/tt11";

SQL>SELECT * FROM CMP;

SQL>DELECT FROM emp;

SQL>COMMIT;

SQL>SELECT * FROM emp;

c:\>imp tt/tt11

>c:mybak.dmp

输入缓存区的大小>

依次选择yes or no

冷备份

SQL>shutdown immediate

选择oradata中的文件拷贝到备份目录下

SQL>startup mount

SQL>archive log list;查看归档日志

将数据库设置为日志归档方式

SQL>alter system set log_archive_start=true scope=spfile;

SQL>shutdown immediate;

评论

1 楼 jiaguwen123 2010-08-03  

接博文:
将数据库设置为日志归档方式

SQL>alter system set log_archive_start=true scope=spfile;

SQL>shutdown immediate;

SQL>startup mount;

SQL>alter database archivelog;

SQL>alter database open;

SQL>archive log list;

SQL>alter tablespace tt begin backup;

SQL>alter tablespace tt end backup;

SQL>alter system archivelog current;

SQL>alter system switch logfile;

SQL>alter system switch logfile;

SQL>shutdown immediate;

SQL>startup

SQL>select * from v$recover_file;

恢复

SQL>alter database datafile 6 offline drop;

SQL>alter database open;

SQL>SELECT * FROM tt.deptment;

SQL>RECOVER DATAFILE 6;

auto

SQL>ALTER DATABASE DATAFILE 6 ONLINEL;

SQL>SELECT * FROM tt.deptment

备份控制文件

SQL>shutdown immediate

备份oradata下的控制文件

SQL>@c:\create_ctl.txt用控制文件的脚本重新创建控制文件

SQL>select user from dual;

备份日志文件

SQL>recover database until cancel

SQL>alter database open resetlogs