C：\Adminstrator>?sqlplus "/as sysdba"?查看是否連接到數(shù)據(jù)庫

SQL> select status from?v$instance;

v$動態(tài)表開頭，查看動態(tài)實例，open為啟動。

SQL> shutdown immediate關閉數(shù)據(jù)庫

SQL>?startup?繼續(xù)啟動

SQL>?show parameter db_name?查看數(shù)據(jù)庫名稱

?

查詢數(shù)據(jù)庫SCOTT用戶默認的狀態(tài)

SQL>?select username,account_status from dba_users where username='SCOTT';

SQL>?desc?user_tables;?描述

SQL>?alter?user ~scott數(shù)據(jù)庫用戶名~ account unlock;

?

SQL> show user查看當前數(shù)據(jù)庫用戶

切換用戶 SQL>?conn?scott/dan?用戶名/密碼

?

?C：\Adminstrator> sqlplus scott/dan 進入數(shù)據(jù)庫

?

?

?

?

?

oracle 命令 操作

內(nèi)容包括三大項:
????1.Oracle基本操作語句

????2.SQLServer基本操作語句

????3.各種數(shù)據(jù)庫連接方法

**************************************************oracle基本操作語句********************************************************
打開服務器
net start oracleservicebinbo
打開監(jiān)聽器
lsnrctl start
關閉服務器
net stop oracleservicebinbo
關閉監(jiān)聽器
lsnrctl stop
===============================================================
清屏
clear screen
****************************************************************
數(shù)據(jù)字典 ===========desc user_views(關鍵詞)
****************************************************************
===============================================================
查看當前用戶的角色
SQL>select * from user_role_privs;
===============================================================
查看當前用戶的系統(tǒng)權(quán)限和表級權(quán)限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;
===============================================================
查看當前用戶的缺省表空間
SQL>select username,default_tablespace from user_users;
===============================================================
換用戶
conn as sysdba
sys
tsinghua
sqlplus "sys/tsinghua as sysdba"
conn sys/zl as sysdba
===============================================================
修改表結(jié)構(gòu)
alter table test modify(name not null);
alter table test add(name varchar2(20));
alter table test drop column sex;
alter table test set unused column sex;
alter table test drop unused columns;
===============================================================
更改用戶密碼
sql>alter user 管理員 identified by 密碼；
===============================================================
創(chuàng)建表空間的數(shù)據(jù)文件
sql>create tablespace test datafile 'd:oraclebinbo.dbf' size 10m;
===============================================================
創(chuàng)建用戶
sql>create user 用戶名 identified by 用戶名;
===============================================================
bfile類型實例
創(chuàng)建目錄
create directory tnpdir as 'c:';
刪除目錄
drop directory tnpdir
授權(quán)
grant read on directory tn pdir to scott;
建表
create table bfiletest(id number(3), fname bfile);
添加數(shù)據(jù)
insert into bfiletest values(1,bfilename('TMPDIR','tmptest.Java'));
===============================================================
查看用戶
sql>show user
===============================================================
檢查語句是否有錯
show error
===============================================================
鎖定用戶
sql>alter user 用戶名 account lock
===============================================================
解除用戶
sql>alter user 用戶名 account unlock
===============================================================
刪除用戶
sql>drop user zl;
===============================================================
給用戶創(chuàng)建表權(quán)限
sql>grant create table to 用戶名;
===============================================================
授管理員權(quán)限
sql>grant dba to 用戶名;
===============================================================
給用戶登錄權(quán)限
sql>grant connect to 用戶名
===============================================================
給用戶無限表空間權(quán)限
sql>grant unlinmited tablespace to 用戶名;
===============================================================
收回權(quán)限
sql>revoke dba from 用戶名;
===============================================================
查看用戶下所有的表
????????????SQL>select * from user_tables;
===============================================================
查看名稱包含log字符的表
????????????SQL>select object_name,object_id from user_objects
????????????????where instr(object_name,'LOG')>0;
===============================================================
查看某表的創(chuàng)建時間
????????????SQL>select object_name,created from user_objects where object_name=upper('&table_name');
===============================================================
查看某表的大小
????????????SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
????????????????where segment_name=upper('&table_name');
===============================================================
查看放在ORACLE的內(nèi)存區(qū)里的表
????????????SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;

===============================================================

再添加一個表空間的數(shù)據(jù)文件
sql>alter tablespace test add datafile 'd:oracletest1.dbf' size 10m;
===============================================================
建表????SQL>create table studen(stuno int,stuname varchar(8) not null,stubirth date default to_date('1987-5-9','YYYY-MM-DD'));
向表結(jié)構(gòu)中加入一列??SQL>alter table studen add(stuphoto varchar(9));
從表結(jié)構(gòu)中刪除一列??SQL>alter table studen drop column stuphoto;
修改表一列的長度????SQL>alter table studen modify(stuno number(4));
隱藏將要刪除的一列??SQL>alter table studen set unused column stuphoto;
刪除隱藏的列????????SQL>alter table studen drop unused columns;
向表中加入約束??????SQL>alter table studen add constraint pk primary key(stuno);
刪除約束????????????SQL>alter table studen drop constraint pk;
===============================================================
創(chuàng)建表

sql>create table 用戶名(name varchar2(20),password varchar(20)) tablespace 空間名;
===============================================================
添加字段
sql>alter table test add(column_x char(10) not null);
===============================================================
更改字段
sql>alter table emp modify(column_x char (20));
===============================================================
刪除字段
?如待刪除域?qū)儆谀硞€索引，則不允許刪除操作，必須將此域先設置為NULL。
sql>alter table emp modify(column_x null);
sql>update emp set column_x=null;
sql>commit;
sql>alter table emp drop(column_x);
===============================================================
選擇表空間
sql>alter user 用戶名 default tablespace test;
===============================================================
管理員刪除別的用戶中的表
sql>drop table 用戶名.表名;
===============================================================
退出
sql>exit;
===============================================================
默認進入
sql>sqlplus "/ as sysdba"
===============================================================
查看數(shù)據(jù)庫
sql>show parameter block;
===============================================================
寫大量語句用記事本，新建方式。
輸入"ed"回車
保存后
輸入"/"運行;
===============================================================
查詢用戶有多少表
sql>select * from tab;
===============================================================
SQLServer取時間
sql>select getdate
oracle 取時間
sql>sysdate;
===============================================================
操作表結(jié)構(gòu)數(shù)據(jù)庫定義語言命令
(不記錄在日志文件中)
create table建表
sql>create table test(name varchar2(20),age date,sex char(2));
sql>insert into test(name,age,sex) values('aa',sysdate,'男');
sql>insert into test(name,age,sex) values('bb',to_date('1888-8-8',"yyyy-aa-dd hh24:mi:ss"),'男');
sql>select * from test;
===============================================================
查詢男和女總數(shù)
sql>select sex,count(sex) from test group by sex;
---------------------------------------------------------------
test表中數(shù)據(jù)輸入test1表中
SQLSserver---select * into test1 from test;
oracle---create table test1 as select * from test;
---------------------------------------------------------------
更改會話時間
sql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
---------------------------------------------------------------
sql>show parameter block 表和視圖
sql>show parameter date 查數(shù)據(jù)結(jié)構(gòu)
---------------------------------------------------------------
SQLServer中
--刪除表中相同數(shù)據(jù)
sql>create table test1 as select distinct * from test;
--刪除表數(shù)據(jù)
sql>truncate table test;
--把test中數(shù)據(jù)輸入到test1中
sql>insert into test(select * from test1);
---------------------------------------------------------------
rowid(表中存儲地址相當表id)和rownum(表序號)稱偽列(用法)
sql>select name,age,sex,rowid,rownum from test1;
查出前三行
sql>select * from test where rownum<=3;
查出后三行
sql>select * from (select name n,age a,sex s,rownum r from test) where r>(select count(*) from test)-3;
刪除后三行
SQL> delete from test where name not in(select name from test where rownum<=(select count(*) from test)-3);
刪除相同行
sql>delete from test where rowid not in(select max(rowid) from test group by name,age,sex);
刪除所有表
sql>select??'drop table' ||tname|| ':' from tab;
sql>spool c:test.sql;
sql>select??'drop table' ||tname|| ':' from tab;
sql>spool off
sql>@c:test.sql;
---------------------------------------------------------------
alter table修改表
truncate table節(jié)段表(只刪除數(shù)據(jù))
drop table刪除表
===============================================================
查看表結(jié)構(gòu)
desc 表名;
===============================================================
查出成績的前三名
sql>select * from (select * from stu order by score desc) where rownum<=3;
===============================================================
更改字符集
SQL>startup mount
SQL>alter system enable restricted session;
SQL>alter system set job_queue_processes=0;
SQL>alter database open;
SQL>alter database character set ZHS16GBK;
SQL>shutdown
SQL>startup
===============================================================
將一張表或幾張表中的域重新組合后插入新表。
假定原先的兩張表為emp,work，現(xiàn)選擇部分數(shù)據(jù)域合并為emp_work
建立emp_work
SQL>insert into emp_new select a.no, sysdate, a.name, b.service_duration from emp a, work b where a.no=b.no;
SQL>commit;
這樣的方式仍然要使用回滾段，為加快數(shù)據(jù)遷移速度，可將insert替換成insert （大小寫不論），指示oracle以直通方式直接寫數(shù)據(jù)文件，繞過回滾空間。
SQL>insert into emp_new select a.no, sysdate, a.name, b.service_duration from emp a, work b where a.no=b.no;
SQL>commit;
===============================================================
DDL數(shù)據(jù)定義語言(create,alter,drop)
DML數(shù)據(jù)操縱語言(insert,select,delete,update)
TCL事務控制語言(commit,savepoint,rollback)
DCL數(shù)據(jù)控制語言(GRANT??REVOKE)
===============================================================

一個表中的某一列輸?shù)搅硪粋€表中
insert into stu1(name)(select name from stu);
===============================================================
事務
rollback;
insert into stu1(name)(select name from stu);
commit;提交
===============================================================
COMMIT - 提交并結(jié)束事務處理
ROLLBACK -??撤銷事務中已完成的工作
SAVEPOINT – 標記事務中可以回滾的點
SQL>??update order_master set del_date ='30-8月-05' WHERE orderno <= 'o002';
?
SQL>??savepoint mark1;
SQL>??delete FROM order_master WHERE orderno = 'o002';
SQL>??savepoint mark2;
SQL>??rollback TO SAVEPOINT mark1;
SQL>??COMMIT;
===============================================================
換名
set sqlprompt "scott>";
===============================================================
GRANT 授予權(quán)限
SQL> GRANT SELECT ON vendor_master TO accounts WITH GRANT OPTION;
REVOKE 撤銷已授予的權(quán)限
SQL> REVOKE SELECT, UPDATE ON order_master FROM MARTIN;
===============================================================
比較操作符
SQL> SELECT vencode,venname,tel_no
?????FROM vendor_master
?????WHERE venname LIKE 'j___s';
SQL> SELECT orderno FROM order_master
?????WHERE del_date IN (‘06-1月-05’,‘05-2月-05');
SQL> SELECT itemdesc, re_level
?????FROM??itemfile
?????WHERE qty_hand < max_level/2;
===============================================================
邏輯操作符
SQL> SELECT * FROM order_master
?????WHERE odate > ‘10-5月-05'
?????AND del_date < ‘26-5月-05’;
===============================================================
集合操作符將兩個查詢的結(jié)果組合成一個結(jié)果
SQL> SELECT orderno FROM order_master
?????MINUS
?????SELECT orderno FROM order_detail;
-----------------------------------------------------------------
select * from scott.stu
union (all)重復的去掉[intersect把相同的取出來][minus顯示不相同的數(shù)]
select * from stu
-----------------------------------------------------------------
顯示相同的數(shù)據(jù)
select name from stu intersect select name from stu1;
===============================================================
連接操作符
連接操作符用于將多個字符串或數(shù)據(jù)值合并成一個字符串
SQL> SELECT (venname|| ' 的地址是 '
?????||venadd1||' '||venadd2 ||' '||venadd3) address
?????FROM vendor_master WHERE vencode='V001';
===============================================================
操作符的優(yōu)先級
SQL 操作符的優(yōu)先級從高到低的順序是：
算術操作符???????????--------最高優(yōu)先級
連接操作符
比較操作符
NOT 邏輯操作符
AND 邏輯操作符
OR???邏輯操作符???--------最低優(yōu)先級

===============================================================
用來轉(zhuǎn)換空值的函數(shù)
NVL
NVL2
NULLIF
SELECT itemdesc, NVL(re_level,0) FROM itemfile;
SELECT itemdesc, NVL2(re_level,re_level,max_level) FROM itemfile;
SELECT itemdesc, NULLIF(re_level,max_level) FROM itemfile;
===============================================================
GROUP BY和HAVING子句

GROUP BY子句
用于將信息劃分為更小的組
每一組行返回針對該組的單個結(jié)果

HAVING子句
用于指定 GROUP BY 子句檢索行的條件

SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category;
SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category HAVING p_category NOT IN ('accessories');
===============================================================
ROW_NUMBER (row_number)返回連續(xù)的排位，不論值是否相等
RANK(rank) 具有相等值的行排位相同，序數(shù)隨后跳躍
DENSE_RANK(dense_rank) 具有相等值的行排位相同，序號是連續(xù)的
SELECT d.dname, e.ename, e.sal, DENSE_RANK()
??OVER (PARTITION BY e.deptno ORDER BY e.sal DESC)
??AS DENRANK
FROM emp e, dept d WHERE e.deptno = d.deptno;
===============================================================
日期函數(shù)
ADD_MONTHS(當前只加月)
alter session set nls_date_format='yyyymmdd hh24miss';
select add_months(sysdate,2) from dual;
----------------------------------------------------------------
MONTHS_BETWEEN(前面時間減后面時間=得之間月差)
select months_between(sysdate,to_date('2007-6-10','yyyy-mm-dd')) from dual;
----------------------------------------------------------------
LAST_DAY(求得當前月的最后一天)
select last_day(sysdate) from dual;
----------------------------------------------------------------
ROUND(round年-月-日-->四舍五入)
select round(2.3) from dual;
select round(to_date('2007-6-10','yyyy-mm-dd'),'year') from dual;
select round(to_date('2007-6-10','yyyy-mm-dd'),'month') from dual;
select round(to_date('2007-6-10','yyyy-mm-dd'),'day') from dual;
----------------------------------------------------------------
NEXT_DAY(下一星期的星期二）
select next_day(to_date('2007-6-10','yyyy-mm-dd'),'星期二') from dual;
----------------------------------------------------------------
TRUNC(trunc)
----------------------------------------------------------------
EXTRACT(extract)
select extract(year from date '1998-03-07') from dual;
select extract(month from to_date ('1998-03-07','yyyy-mm-dd')) from dual;

----------------------------------------------------------------
2008年2月有多少天
inbo---->select extract(day from last_day(to_date ('2008-02-07','yyyy-mm-dd'))) from dual;
----------------------------------------------------------------
2003-4-3與1956-3-1之間有多少天
inbo---->select round(months_between(to_date('2003-4-3','yyyy-mm-dd'),to_date('1956-3-1','yyyy-mm-dd'))/12) from dual;
===============================================================
把兩邊的9去掉
select trim('9' from '9999ddddddd99999') from dual;
去空格
select trim(' ' from '?????9999ddddddd99999') from dual;
===============================================================
???函數(shù)???????????????????輸入??????????????????????????輸出
Initcap(char) ???????????Select initcap(‘hello’) from dual;?????????????????Hello
Lower(char) ???????????Select lower(‘FUN’) from dual;?????????????????fun
Upper(char)???????????????Select upper(‘sun’) from dual;?????????????????SUN
Ltrim(char,set) ???Select ltrim( ‘xyzadams’,’xyz’) from dual;????????adams
Rtrim(char,set) ???Select rtrim(‘xyzadams’,’ams’) from dual; ?xyzad
Translate(char, from, to) Select translate(‘jack’,’j’ ,’b’) from dual;?back
Replace(char,searchstring,[rep string])???Select replace(‘jack and jue’ ,’j’,’bl’) from dual;?black and blue
Instr (char, m, n) ???Select instr (‘worldwide’,’d’) from dual; ??5
Substr (char, m, n) ???Select substr(‘a(chǎn)bcdefg’,3,2) from dual; ?????????cd
Concat (expr1, expr2)???Select concat (‘Hello’,’ world’) from dual; ?Hello world

===============================================================
數(shù)字函數(shù)接受數(shù)字輸入并返回數(shù)值結(jié)果

???函數(shù)????輸入???輸出
Abs(n) ?????????Select abs(-15) from dual; ?15

Ceil(n) ?Select ceil(44.778) from dual; ?45

Cos(n) ?????????Select cos(180) from dual; ?-.5984601

Cosh(n) ?Select cosh(0) from dual; ?1

Floor(n) ?Select floor(100.2) from dual; ?100

Power(m,n) ?Select power(4,2) from dual; ?16

Mod(m,n) ?Select mod(10,3) from dual; ?1

Round(m,n) ?Select round(100.256,2) from dual; ?100.26

Trunc(m,n) ?Select trunc(100.256,2) from dual; ?100.25

Sqrt(n) ?Select sqrt(4) from dual; ?2

Sign(n)?????????Select sign(-30) from dual;???????????-1

===============================================================
字符函數(shù)
查看有多少個字符
SQL> SELECT LENGTH('frances') FROM dual;
-----------------------------------------------------------------
SQL> SELECT vencode,
?????DECODE(venname,'frances','Francis') name
?????FROM vendor_master WHERE vencode='v001';
-----------------------------------------------------------------
查找人是否存在 加字段decode主明是否有人
select name,decode(name,'rbb','有人') from stu;

===================================================================
排續(xù)
select dense_rank() over(partition by sex order by score) from test;
select row_number() over(order by score),name,sex,score from test;
select rank() over(order by score) from test;
select dense_rank() over(order by score) from test;
==========================================================================

創(chuàng)建同義詞
SQL> create public synonym test for rbb.test;
SQL> create synonym test for mytest;
同一類的才可以替換,同義詞替換同義詞
替換
SQL> create or replace synonym emp_sysn for scott.emp;

**********************************************************************************************
創(chuàng)建序列
SQL>create sequence xule increment by 1 start with 1 maxvalue 999;
increment by??增長值
start with????起始值
maxvalue 最大值
minvalue 最小值
nocycle 不循環(huán)
chare 10緩存
xule.nextval??===========下一個序列的值
xule.currval??===========可以查詢序列當前的值
更改序列 start with 不能改
alter sequence xule maxvalue 100 [sycle nocycle]；
**********************************************************************************************
序列用法
SQL>create table xl(name varchar2(4));
SQL>insert into test values(xule.nextval);
SQL>select xl.currval from dual;
**********************************************************************************************
刪除序列
drop sequence x;
desc user_sequences
**********************************************************************************************
創(chuàng)建視圖???視圖中可以使用函數(shù)和表達式
create or replace view
**********************************************************************************************
創(chuàng)建視圖
SQL> create or replace view 視圖名 as select * from rbb union all select * from rbbb union all select * from test;
SQL> create or replace view 視圖名 as
??2??select empno as 編號,ename as 姓名 from scott.emp
??3??where deptno=10;
==========================================================================
如果在當前用戶下沒有這個視圖就創(chuàng)建此視圖
如果有此視圖就覆蓋此視圖
create or replace view view_name as select empno,ename from emp where deptno=10;
**********************************************************************************************
在創(chuàng)建視圖前要為當前用戶授權(quán)
grant resource to scott;
create or replace view v_sal as select ename,sal from emp order by sal desc;
**********************************************************************************************
使用視圖
select * from v_sal;
**********************************************************************************************
刪除一個視圖
drop view view_name;
**********************************************************************************************
重新編譯已有的視圖
alter view view_name compile;
**********************************************************************************************
數(shù)據(jù)字典 ===========desc user_views
**********************************************************************************************

常用的轉(zhuǎn)換函數(shù)有
TO_CHAR
SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS') FROM dual;　

TO_DATE
SELECT TO_DATE('2005-12-06', 'yyyy-mm-dd') FROM dual;

TO_NUMBER
SELECT TO_NUMBER('100') FROM dual;
**********************************************************************************************
集合操作符
union all??連接兩個表或者多個表為一個視圖
MINUS 操作符返回從第一個查詢結(jié)果中排除第二個查
詢中出現(xiàn)的行。
INTERSECT 操作符只返回兩個查詢的公共行。
**********************************************************************************************

鎖定的優(yōu)點

1.一致性 - 一次只允許一個用戶修改數(shù)據(jù)

2.完整性 - 為所有用戶提供正確的數(shù)據(jù)。如果一個用戶進行了修改并保存，所做的修改將反映給所有用戶

3.并行性 －允許多個用戶訪問同一數(shù)據(jù)

行級鎖和表級鎖

行級鎖:是一種排他鎖,防止其他事務修改此行.

解鎖：提交事務(commit),(rollback)
---------------------------------------------------------------
更新表數(shù)據(jù):update test set score=80 where name='xiaoli';
--------------------------------------------------------------
自動提交
set autocommit on
set sutocommit off
------------------------------------------------------------
鎖定某行更新語句
select * from scott.test where name='xiaoli' for update;
SELECT * FROM order_master WHERE vencode='V002' FOR UPDATE OF odate,del_date;
select * from scott.test where name='xiaoli' for update of score;

select * from scott.test atest,test b where a.name=b.name and b.name='bbb' for update of b.score;
--------------------------------------------------------------------
等待update
select * from scott.test where name='xiaoli' for update wait 2;
select * from scott.test where name='xiaoli' for update nowait;

-------------------------------------------------------------------
表級鎖：鎖定整個表
表級鎖語法:lock table 表名 in mode mode;
-------------------------------------------------------------------------
行共享row share--行排他row exclusive--共享share-共享行排他share row exclusive-----排他exclusive
---------------------------------------------------------------------------------
行共享(row share):lock table scott.test in (row share) mode;
[其他用戶.行共享---其他用戶.行排他---其他用戶.共享----其他用戶.共享行排他----其他用戶.不可以(排他)]

--------------------------------------------------------------------------------
行排他(row exclusive):lock table scott.test in (row exclusive) mode;

[其他用戶.行共享----其他用戶.行排他----其他用戶.不可以(共享)---其他用戶.不可以(共享行排他)--其他用戶.不可以(排他)]

---------------------------------------------------------------------------------
共享(share):lock table scott.test in (share) mode;

[其他用戶.行共享---其他用戶.不可以(行排他)---其他用戶.共享----其他用戶.不可以(共享行排他)---其他用戶.不可以(排他)]

-----------------------------------------------------------------------------------
共享行排他(share row exclusive):lock table scott.test in (share row exclusive) mode;

[其他用戶.行共享,其他用戶.不可以(行排他),其他用戶.不可以(共享),其他用戶.不可以(共享行排他),其他用戶.不可以(排他)]

--------------------------------------------------------------------------------------
排他(exclusive):lock table scott.test in (exclusive) mode;

[其他用戶.不可以(行共享),其他用戶.不可以(行排他),其他用戶.不可以(共享),其他用戶.不可以(共享行排他,)其他用戶.不可以(排他)]

----------------------------------------------------------------------------------
死鎖

當兩個事務相互等待對方釋放資源時，就會形成死鎖

Oracle會自動檢測死鎖，并通過結(jié)束其中的一個事務來解決死鎖
----------------------------------------------------------------------------------

表分區(qū)

---范圍分區(qū)
create table test(name varchar2(20),sex char(2),score number(3))
partition by range(score)
(
partition p1 values less than (50) tablespace users,
partition p2 values less than (80),
partitiom p3 values less than (maxvalue)
)
select * from test partition(p1) union select * from test partitiom(p3);
---
刪除分區(qū)
alter table test drop partition p3;
添加分區(qū)
alter table test add partition p3 values less than (maxvalue);
拆分分區(qū)
alter table test split partition p2 at(60)
into (partition p21,partition p22);
合并分區(qū)
alter table test merge partitions p21,p22 into partition p2;
截斷分區(qū)(刪除數(shù)據(jù))
alter table test truncate partition p3;

現(xiàn)有表分區(qū)
create table str as select * from student;
drop table student;
create table student(
?studentid integer not null,
?studentname varchar2(20),
?score integer
)
partition by range(score)(
?partition p1 values less than(60),
?partition p2 values less than(75),
?partition p3 values less than(85),
?partition p4 values less than(maxvalue)
)
insert into student(select * from stu);

select * from test?scott.emp@tsinghua

?

**********************************************************************************************

表分區(qū)
Oracle允許用戶對表進一步的規(guī)化，即對表進一步拆分，將表分成若干個邏輯部分，每個部分稱其為表分區(qū)
優(yōu)點：增強可用性，單個分區(qū)出現(xiàn)故障，不影響其他分區(qū)
均衡的I/O，不同的分區(qū)可以映射到不同的磁盤???改善性能
**********************************************************************************************
①范圍分區(qū)法
create table st(
?studentid integer not null,
?studentname varchar2(20),
?score integer
)
partition by range(score)(
?partition p1 values less than(60),
?partition p2 values less than(75),
?partition p3 values less than(85),
?partition p4 values less than(maxvalue)
)
========================select * from stu partition(p1)============
②散列分區(qū)
create table st(deptno int,deptname varchar(14))
partition by hash(deptno)(
partition p1,partition p2
)
組合分區(qū)
alter table test coalesce partition;
**********************************************************************************************
③復合分區(qū)
范圍分區(qū)和列表分區(qū)
create table salgrade(
grade number(2),losal number(2),hisal number(2)
)
partition by range(grade)
subpartition by list(losal)
(
partition p1 values less than(10)

??(
???subpartition p1a values('湖北'),
???subpartition p1b values(default)
??),
partition p2 values less than(20)
??(
???subpartition p1a values('河南'),
???subpartition p1b values(default)
??),
partition p3 values less than(30)
??(
???subpartition p1a values('上海'),
???subpartition p1b values(default)
??)
)

范圍分區(qū)和散列分區(qū)
create table salgrade(
grade number(2),losal number(2),hisal number(2)
)
partition by range(grade)
subpartition by hash(losal)
[subpartitions 5]
(
partition p1 values less than(10)(subpartition p1a,subpartition p1b),
partition p2 values less than(20)(subpartition p2a,subpartition p2b),
partition p3 values less than(30)(subpartition p3a,subpartition p3b)
)
--------------------------------------------
?create table salg(
?grade number(2),losal number(2),hisal number(2)
?)
?partition by range(grade)
?subpartition by hash(losal)
?subpartitions 3
?(
?partition p1 values less than(10),
?partition p2 values less than(20),
?partition p3 values less than(30)
?)
**********************************************************************************************
④列表分區(qū)
create table test stu(id int,name varchar(20),add varchar(8))
partition by list(add)
(
partition p1 values('中國'),
partition p2 values('英國'),
partition p3 values(default)
)
**********************************************************************************************
移動分區(qū)

alter table test move partition p5 tablespace users;

********************************************************************************************

修改存檔

SQL> shutdown immediate
數(shù)據(jù)庫已經(jīng)關閉。
已經(jīng)卸載數(shù)據(jù)庫。
ORACLE 例程已經(jīng)關閉。

SQL> startup mount
ORACLE 例程已經(jīng)啟動。

Total System Global Area??135338868 bytes??????????????????????????????????????
Fixed Size???????????????????453492 bytes??????????????????????????????????????
Variable Size?????????????109051904 bytes??????????????????????????????????????
Database Buffers???????????25165824 bytes??????????????????????????????????????
Redo Buffers?????????????????667648 bytes??????????????????????????????????????
數(shù)據(jù)庫裝載完畢。

SQL> alter database archivelog;

數(shù)據(jù)庫已更改。
alter database open;

SQL> archive log list;
數(shù)據(jù)庫日志模式????????????存檔模式
自動存檔?????????????禁用
存檔終點????????????d:oracleora92RDBMS
最早的概要日志序列?????1
下一個存檔日志序列???2
當前日志序列???????????2

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

系統(tǒng)已更改。

SQL> alter database open;

數(shù)據(jù)庫已更改。

SQL> spool off
********************************************************************************************

PL/SSQL(過程化語言) 聲明部分 執(zhí)行語句部分 異常處理部分

identifier constant datatype not null
[:=|default expr];

?

declare
my number(5);
begin
?select quantity into my
from products where product='wawa'
for update of quantity;
if my>0 then
update products set quantity=quantity+1
where product='wawa';
insert into purchase_record
values('wawawa',sysdate);
end if;
commit;
Exception
where others then
dbms_output.put_line('chucuo'||SQLERRM);
END;

declare icode varchar2(6)
p_catg varchar2(20);
c_catg constant datatype:=0.10

數(shù)字類型
number
??decrmdl
??int/integer
??real(實數(shù))
??binary_integer(帶符號的整數(shù))
??pls_integer(同上)
字符類型
character
??char 3276
??Raw(2000)
??long/long Raw(32760)
??Rowid/rowid()
??varchar2 (string(nchar/nvarchar)/varchar)
日期時間
date
??timeStamp(固定日期dd-mm-yy 秒6位)
??子 timestamp with time zone
??ti timestamp(9)
布爾
boolean
??true
??false
??null
打印出時間
declare
test_tz timestamp with time zone;
begin test_tz:=to_timestamp_tz('2006-6-22 09:07:11','yyyy-mm-dd hh24:mi:ss');
dbms_output.put_line(test_tz);
end;

lob類型
??BFILE
??BLOB
??CLOB
??NCLOB
屬性類型
??%type??%rowtype
===============================================================
bfile類型實例
創(chuàng)建目錄
create directory tnpdir as 'c:';
刪除目錄
drop directory tnpdir
授權(quán)
crant read on directory tnpdir to scott;
建表
create table bfiletest(id number(3), fname bfile);
添加數(shù)據(jù)
insert into bfiletest values(1,bfilename('TMPDIR','tmptest.java'));

===============================================================
向數(shù)據(jù)庫中添加圖片
create directory images as 'c:images';
crant read on directory images to scott;
create table my_diagrams(
chapter_descr varchar2(40);
diagram_no integer,
diagram blob
);

declare
??l_bfile bfile;
??l_blob blob;
begin
?insert into my_diagrams(diagram)
?values(emptv_blob())
?return diagram into l_blob;
?l_bfile:=bfilename('images','nvimage.jpg');
?dbms_lob.open(l_bfile,dbms_lob.file_readonly);
?dbms_lob.loadfromfile(l_blob,l_bfile,dbms_lob,getlength(l_bfile));
?dbms_lob.close(l_bfile);
?commit;
end;

===============================================================
%type實例 查詢
declare
dtr dept.dname%type;
begin
select dname into str from dept where deptno=30;
dbms_output.put_line(str);
end;
set serverout on

===============================================================
%rowtype實例

declare
row dept%rowtype;
begin
select * into row from dept where deptno=30;
dbms_output.put.line(row.dname||' '||row.deptno||' '||row.loc);
//異常
exception
when no_data_found then
?dbms_output.put_lin('沒有數(shù)據(jù)');
when too_many_rows(others) then
?dbms_output.put_lin('太多拉');
end;

===============================================================
格式

if 條件 then

elsif 條件 then

else

end if
===============================================================
格式

begin
?case'&grade'
??when 'a' then dbms_output.put_line('優(yōu)異');
??when 'b' then dbms_output.put_line('良好');
??else dbms_output.put_line('其它')
?end case;
end;
===============================================================
外界變量
var vnm varchar2(20);
begin
:v:='aaaaa';
end;
打印
print v
===============================================================
loop實例

begin
loop
exit when 3>4;

end loop;
end;
===============================================================
while實例
begin
while (條件)condition loop
語句體;
end loop;
end;
===============================================================
循環(huán)實例
正
begin
for c in 1..10
loop
dbms_output.put_line(c);
end loop
end;
倒
begin
for c in reverse(倒) 1..10
loop
dbms_output.put_line(c);
end loop
end;
===============================================================

declare
?num number(3):=1;
begin
?while num<10 loop
??dbms_output.put_line(num);
??num:=num+1;
??end loop;
end;

declare
?num number(3):=1;
begin
?loop
??dbms_output.put_line(num);
??exit when num>10;//退出
??num:=num+1;
??end loop;
end;
===============================================================
goto實例
DECLARE
??qtyhand itemfile.qty_hand%type;
??relevel itemfile.re_level%type;
BEGIN
??SELECT qty_hand,re_level INTO qtyhand,relevel
??FROM itemfile WHERE itemcode = 'i201';
??IF qtyhand < relevel THEN
????GOTO updation;
??ELSE
????GOTO quit;
??END IF;
??<<updation>>
??UPDATE itemfile SET qty_hand = qty_hand + re_level
??WHERE itemcode = 'i201';
??<<quit>>
??NULL;
END;
===============================================================
動態(tài)SQL 查詢
declare
cl varchar2(20);
va varchar2(20);
tb varchar2(20);
nm number(13);

begin
tb:='&table';
cl:='&aadd';
nm:=&num;
EXECUTE IMMEDIATE
'select '||cl||' from '||tb||' where '||cl||'=:1' into va using nm;
dbms_output.put_line(va);
end;

===============================================================
動態(tài)SQL

declare

sql_stmt varchar2(200);
emp_id number(4):=7566;
emp_rec emp% rowtype;

begin
Execute immedlate
'create table bonus1(id number,amt number)';

sql_stmt:='select * from emp where empno=:id';
Execute immedlate sql_stmt into emp_rec using emp_id;

end;
===============================================================
declare
aaa varchar2(20);
num number(10);
bbb varchar2(20);
begin
aaa='&aaa';
num=&kkk;
execute immedlate 'select '||aaa||' from test where age=:a'into bbb using num;(標準SQL語句)
dbms_output.put_line(bbb);
end;
into 變量(給值)
:a(外界參數(shù)) using bb(邦定常量)

===============================================================
自己定義異常
declare
invar exception;
cate varchar2(10);
begin
cate:='&cate';
if cate not in('aa','ff','dd') then
raise invar;
else
dbms_output.put_line('你輸入的類別是:'||cate);
end if;
exception
when invar then
dbms_output.put_line('無法認識這個類別!');
raise_application_error(-20200,'自己寫');
end;
讓數(shù)據(jù)庫真正出錯
raise_application_error(-20200,'自己寫');

例子2
declare
rate itemfile.itemrate%type;
ratee exception;
begin
select nvl(itemrate,0) into rate from itemfile
where itemcode='i207';
if rate=0 then
raise ratee;
else
dbms_output.put_line('項費率是:'||rate);
end if;
exception
when ratee then
RAISE_APPLICATION_ERROR(-20001, '未指定項費率');
end;
===============================================================
create procedure存儲過程
===============================================================
創(chuàng)建標準索引
SQL> CREATE INDEX item_index ON itemfile (itemcode)
?????TABLESPACE index_tbs;
重建索引
SQL> ALTER INDEX item_index REBUILD;
刪除索引
SQL> DROP INDEX item_index;
唯一索引確保在定義索引的列中沒有重復值
Oracle 自動在表的主鍵列上創(chuàng)建唯一索引
使用CREATE UNIQUE INDEX語句創(chuàng)建唯一索引
SQL> CREATE UNIQUE INDEX item_index
?????ON itemfile (itemcode);
組合索引是在表的多個列上創(chuàng)建的索引
索引中列的順序是任意的
如果 SQL 語句的 WHERE 子句中引用了組合索引的所有列或大多數(shù)列，
則可以提高檢索速度

SQL> CREATE INDEX comp_index
?????ON itemfile(p_category, itemrate);
反向鍵索引反轉(zhuǎn)索引列鍵值的每個字節(jié)
通常建立在值是連續(xù)增長的列上，使數(shù)據(jù)均勻地分布在整個索引上
創(chuàng)建索引時使用REVERSE關鍵字
SQL> CREATE INDEX rev_index
?????ON itemfile (itemcode) REVERSE;
SQL> ALTER INDEX rev_index REBUID NOREVERSE;
位圖索引適合創(chuàng)建在低基數(shù)列上
位圖索引不直接存儲ROWID，而是存儲字節(jié)位到ROWID的映射
減少響應時間
節(jié)省空間占用
SQL> CREATE BITMAP INDEX bit_index
?????ON order_master (orderno);
基于一個或多個列上的函數(shù)或表達式創(chuàng)建的索引
表達式中不能出現(xiàn)聚合函數(shù)
不能在LOB類型的列上創(chuàng)建
創(chuàng)建時必須具有 QUERY REWRITE 權(quán)限
SQL> CREATE INDEX lowercase_idx
?????ON toys (LOWER(toyname));
SQL> SELECT toyid FROM toys
?????WHERE LOWER(toyname)='doll';
與索引有關的數(shù)據(jù)字典視圖有：
USER_INDEXES － 用戶創(chuàng)建的索引的信息
USER_IND_PARTITIONS － 用戶創(chuàng)建的分區(qū)索引的信息
USER_IND_COLUMNS － 與索引相關的表列的信息
SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
?????FROM USER_IND_COLUMNS
?????ORDER BY INDEX_NAME, COLUMN_POSITION;
可以將索引存儲在不同的分區(qū)中
與分區(qū)有關的索引有三種類型：
局部分區(qū)索引 － 在分區(qū).
表上創(chuàng)建的索引，在每個表分區(qū)上創(chuàng)建獨立的索引，索引的分區(qū)范圍與表一致
全局分區(qū)索引 － 在分區(qū)表或非分區(qū)表上創(chuàng)建的索引，索引單獨指定分區(qū)的范圍，與表的分區(qū)范圍或是否分區(qū)無關
全局非分區(qū)索引 － 在分區(qū)表上創(chuàng)建的全局普通索引，索引沒有被分區(qū)

SQL> CREATE TABLE ind_org_tab (
?????vencode NUMBER(4) PRIMARY KEY,
???????venname VARCHAR2(20)
????)
????ORGANIZATION INDEX;
與索引有關的數(shù)據(jù)字典視圖有：
USER_INDEXES － 用戶創(chuàng)建的索引的信息
USER_IND_PARTITIONS － 用戶創(chuàng)建的分區(qū)索引的信息
USER_IND_COLUMNS － 與索引相關的表列的信息

SQL> SELECT INDEX_NAME, TABLE_NAME, COLUMN_NAME
?????FROM USER_IND_COLUMNS
?????ORDER BY INDEX_NAME, COLUMN_POSITION;

?

----游標簡介

逐行處理查詢結(jié)果,經(jīng)編程的方式訪問數(shù)據(jù)

---游標類型:
隱式游標:在 PL/SQL 程序中執(zhí)行DML SQL 語句時自動創(chuàng)建隱式游標。
顯式游標:顯式游標用于處理返回多行的查詢。
REF 游標:REF 游標用于處理運行時才能確定的動態(tài) SQL 查詢的結(jié)果

------隱式游標的屬性有：
%FOUND – SQL 語句影響了一行或多行時為 TRUE
%NOTFOUND – SQL 語句沒有影響任何行時為TRUE
%ROWCOUNT – SQL 語句影響的行數(shù)
%ISOPEN??- 游標是否打開，始終為FALSE

刪除游標

delete from table_name where cursor of cursor_name;
===============================================================
------隱式游標示例
------too_many_rows的用法!

??1??declare
??2??empid varchar2(20);
??3??begin
??4??select name into empid from test;
??5??exception
??6??when too_many_rows then
??7??dbms_output.put_line('該查詢多于兩行!');
??8* end;
SQL> /
該查詢多于兩行!

PL/SQL 過程已成功完成。

===============================================================

------no_data_found的用法!
SQL> set serverout on
SQL> ed
已寫入文件 afiedt.buf

??1??declare
??2??empid varchar2(20);
??3??desig varchar2(20);
??4??begin
??5??empid:='&emp';
??6??select name into desig from test where name=empid;
??7??dbms_output.put_line('你查詢的名字是:'||desig);
??8??exception
??9??when no_data_found then
?10??dbms_output.put_line('沒有時間！');
?11* end;
SQL> /
輸入 emp 的值:??xiaoli
原值????5: empid:='&emp';
新值????5: empid:='xiaoli';
你查詢的名字是:xiaoli

PL/SQL 過程已成功完成。

SQL> /
輸入 emp 的值:??ss
原值????5: empid:='&emp';
新值????5: empid:='ss';
沒有時間！

PL/SQL 過程已成功完成。

===============================================================
SQL> set serveroutput on
SQL> begin
??2??update test set name='renbinbo' where name='binbo';
??3??if sql%found then
??4??dbms_output.put_line('表已經(jīng)更新!');
??5??end if;
??6??end;
??7??/
test_t表中name也已經(jīng)更新!
表已經(jīng)更新!

===============================================================
SQL>
??declare
???aa varchar2(20);
??bb varchar2(20);
???begin
??bb:='&bb';
???select score into aa from test where name=bb;
???if sql%found then
???dbms_output.put_line(bb||'的分數(shù)為:'||aa);
???end if;
??end;
SQL> /
輸入 bb 的值:??renbinbo
原值????5: bb:='&bb';
新值????5: bb:='renbinbo';
renbinbo的分數(shù)為:100

PL/SQL 過程已成功完成。
===============================================================
SQL> ed
已寫入文件 afiedt.buf

??1??declare
??2??my_toy rbb.test.name%type;
??3??cursor toy_cur is
??4??select name from test where name='xiaoli';
??5??begin
??6??open toy_cur;
??7??loop
??8??fetch toy_cur into my_toy;
??9??exit when toy_cur%notfound;
?10??dbms_output.put_line('你查詢?nèi)说男彰?'||my_toy);
?11??end loop;
?12??close toy_cur;
?13* end;
SQL> /
你查詢?nèi)说男彰?xiaoli

PL/SQL 過程已成功完成。

SQL> ed
昨天我說現(xiàn)在的想法那些確實的真的 也是事實 那是
已寫入文件 afiedt.buf

??1??declare
??2??name_n rbb.test.name%type;
??3??sex_s rbb.test.name%type;
??4??sex_t rbb.test.name%type;
??5??cursor test_t is
??6??select name,sex,score from test;
??7??begin
??8??open test_t;
??9??dbms_output.put_line('你所查資料列表:');
?10??loop
?11??fetch test_t into name_n,sex_s,sex_t;
?12??exit when test_t%notfound;
?13??dbms_output.put_line(name_n||'??'||sex_s||'??'||sex_t);
?14??end loop;
?15??close test_t;
?16* end;
?17??/
你所查資料列表:
xiaoli??女???90
renbinbo??男???100
xiaoming??男???89
xiaowang??男???91
xiaohua??女???98
yunfeng??男???88
wangming??男???78
wuming??男???98
xiaobin??男???68
binbin??男???44
tianhua??女???55
liyun??女???65

PL/SQL 過程已成功完成。

===============================================================

bibno-->ed
已寫入文件 afiedt.buf

??1??declare
??2??cursor test_cur is
??3??select name,sex,score from test;
??4??begin
??5??dbms_output.put_line('用戶資料列表:');
??6??for namet in test_cur
??7??loop
??8??dbms_output.put_line(namet.name||' '||namet.sex||' '||namet.score);
??9??end loop;
?10* end;
?11??/
用戶資料列表:
xiaoli 女??90
renbinbo 男??100
xiaoming 男??89
xiaowang 男??91
xiaohua 女??98
yunfeng 男??88
wangming 男??78
wuming 男??98
xiaobin 男??68
binbin 男??44
tianhua 女??55
liyun 女??65

PL/SQL 過程已成功完成。

===============================================================

帶參數(shù)的顯式游標
SET SERVEROUTPUT ON
SQL> DECLARE
??desig????VARCHAR2(20);
??emp_code VARCHAR2(5);
??empnm????VARCHAR2(20);
??CURSOR emp_cur(desigparam VARCHAR2) IS
???SELECT empno, ename FROM employee
???WHERE designation=desig;
?????BEGIN
??desig:= '&desig';
??OPEN emp_cur(desig);
??LOOP
???FETCH emp_cur INTO emp_code,empnm;
???EXIT WHEN emp_cur%NOTFOUND;
???DBMS_OUTPUT.PUT_LINE(emp_code||' '||empnm);
????END LOOP;
??CLOSE emp_cur;
?????END;
===============================================================
SET SERVEROUTPUT ON
SQL> DECLARE
??new_price NUMBER;
??CURSOR cur_toy IS
????SELECT toyprice FROM toys WHERE toyprice<100
????FOR UPDATE OF toyprice;
BEGIN
??OPEN cur_toy;
??LOOP
????FETCH cur_toy INTO new_price;
????EXIT WHEN cur_toy%NOTFOUND;
????UPDATE toys
????SET toyprice = 1.1*new_price
????WHERE CURRENT OF cur_toy;
??END LOOP;
??CLOSE cur_toy;
??COMMIT;
END;

===============================================================
游標變量的功能強大，可以簡化數(shù)據(jù)處理

游標變量的優(yōu)點有:
1.可從不同的 SELECT 語句中提取結(jié)果集
2.可以作為過程的參數(shù)進行傳遞
3.可以引用游標的所有屬性
4.可以進行賦值運算

使用游標變量的限制：
1.不能在程序包中聲明游標變量
2.FOR UPDATE子句不能與游標變量一起使用
3.不能使用比較運算符
===============================================================
===============================================================

?

創(chuàng)建過程
create procedure test_b(test varchar2,test1 number)
as
begin

dbms_output.put_line(test);
dbms_output.put_line(test1);
end;

?

create procedure test_c(test varchar2,test1 char)
as
aa varchar2(20);
bb char(10);
begin
select name into aa from test where name=test;
dbms_output.put_line(aa);
?select age into bb from test where age=test1;
dbms_output.put_line(bb);
end;
===============================================================
創(chuàng)建函數(shù)
create or replace function test_binbo return varchar2
as
begin
return '我愛你!';
end
執(zhí)行:
select test_binbo from dual;

?create or replace function test_binbo return varchar2
?as
?aa varchar2(20);
?bb char(3);
?begin
?bb:='&bb';
?select name into aa from test where sex=bb;
?return 'name';
?end;
執(zhí)行:
select test_binbo from dual;

create or replace function item_price_range(price number)
return varchar2 as
min_price number;
max_price number;
begin
select max(itemrate),min(temrate) into max_price,min_price
from test;
if price>=min_price and price<=max_price then
return '將計就計機';
else
return '哩哩啦啦理論';
end if;
end;
執(zhí)行:
select test_binbo from dual;
===============================================================
自主事務處理
CREATE OR REPLACE PROCEDURE p1 AS
??b VARCHAR2(50);
BEGIN
??UPDATE vendor_master SET venadd1='10 Walls Street'
??WHERE vencode='V002';
??P2();
??SELECT venadd1 INTO b
??FROM vendor_master WHERE vencode='V002';
??DBMS_OUTPUT.PUT_LINE(b);
END;
/
執(zhí)行
EXECUTE p1;

CREATE OR REPLACE PROCEDURE p2 AS
??a VARCHAR2(50);
??PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
??SELECT venadd1 INTO a
??FROM vendor_master WHERE vencode='V002';
??DBMS_OUTPUT.PUT_LINE(a);
??ROLLBACK;
END;
/

===============================================================
創(chuàng)建程序包

SQL> ed
已寫入文件 afiedt.buf

??1??create or replace package package_me as
??2??procedure proc_test(test varchar2);
??3??function fun_test(funt varchar2) return varchar2;
??4* end;
??5??/

程序包已創(chuàng)建。

已寫入文件 afiedt.buf

??1??create or replace package body package_me as
??2??procedure proc_test(test varchar2) as
??3??nam varchar2(20);
??4??begin
??5??select name into nam from test where name=test;
??6??dbms_output.put_line('你所查的人的姓名是:'||nam);
??7??end;
??8??function fun_test(funt varchar2) return varchar2 as
??9??funn varchar2(20);
?10??begin
?11??select next_day(funt,'星期六')-7 into funn from dual;
?12??return funn;
?13??end;
?14* end package_me;
SQL> /

程序包主體已創(chuàng)建。
SQL> select package_me.fun_test('2008-10-16') from dual;

PACKAGE_ME.FUN_TEST('2008-10-16')
---------------------------------------------------------

2008-10-11

SQL> exec package_me.proc_test('xiaoli');
你所查的人的姓名是:xiaoli
PL/SQL 過程已成功完成。
===============================================================
create or replace package pack_me as
procedure order_pr(orn varchar2);
function order_fu(onr varchar2) return varchar2;
end pack_me;
/

CREATE OR REPLACE PACKAGE BODY pack_me AS
??PROCEDURE order_proc (orno VARCHAR2) IS
????stat CHAR(1);
??BEGIN
????SELECT ostatus INTO stat FROM order_master
????WHERE orderno = orno;
????IF stat = 'p' THEN
??????DBMS_OUTPUT.PUT_LINE('暫掛的訂單');
????ELSE
??????DBMS_OUTPUT.PUT_LINE('已完成的訂單');
????END IF;
??END order_proc;
?
??FUNCTION order_fun(ornos VARCHAR2)
??RETURN VARCHAR2
??IS
????icode???VARCHAR2(5);
????ocode???VARCHAR2(5);
????qtyord??NUMBER;
????qtydeld NUMBER;
??BEGIN
????SELECT qty_ord, qty_deld, itemcode, orderno
????INTO???qtyord, qtydeld, icode, ocode
????FROM order_detail
????WHERE orderno = ornos;
????IF qtyord < qtydeld THEN
??????RETURN ocode;
????ELSE
??????RETURN icode;
????END IF;
??END order_fun;
END pack_me;
/

===============================================================

執(zhí)行
EXECUTE pack_me.order_proc('o002');

DECLARE
??msg VARCHAR2(10);
BEGIN
??msg := pack_me.order_fun('o002');
??DBMS_OUTPUT.PUT_LINE('值是 ' || msg);
END;

/

CREATE OR REPLACE PACKAGE BODY cur_pack AS
?CURSOR ord_cur(vcode VARCHAR2)
?RETURN order_master%ROWTYPE IS
?SELECT * FROM order_master
?WHERE VENCODE=vcode;
?PROCEDURE ord_pro(vcode VARCHAR2) IS
???or_rec order_master%ROWTYPE;
?BEGIN
??OPEN ord_cur(vcode);
??LOOP
????FETCH ord_cur INTO or_rec;
????EXIT WHEN ord_cur%NOTFOUND;
????DBMS_OUTPUT.PUT_LIne('返回的值為' || or_rec.orderno);
??END LOOP;
?END ord_pro;
END cur_pack;

EXEC cur_pack.ord_pro('V001');
===============================================================

COLUMN OBJECT_NAME FORMAT A18

SELECT object_name, object_type
FROM USER_OBJECTS
WHERE object_type IN ('PROCEDURE', 'FUNCTION',
?'PACKAGE', 'PACKAGE BODY');

DESC USER_SOURCE

COLUMN LINE FORMAT 9999
COLUMN TEXT FORMAT A50

SELECT line, text FROM USER_SOURCE
WHERE NAME='TEST';

DESC pack_me;

===============================================================

數(shù)據(jù)庫級觸發(fā)器
CREATE TABLE system.session_info (
??username???VARCHAR2(30),
??logontime??DATE,
??session_id VARCHAR2(30),
??ip_addr????VARCHAR2(30),
??hostname???VARCHAR2(30),
??auth_type??VARCHAR2(30)
);
顯示
set serverout on

create or replace trigger trg_session_info defore logoff on database
declare
session_id varchar2(30);
ip_addr????varchar2(30);
hostname???varchar2(30);
auth_type??varchar2(30);
logontime??date;

begin
?select sys_context('userenv','sessionid') -- 會話編號
??--??用戶登錄的客戶端IP地址
?select sys_context('userenv','ip_address') into ip_addr from dual;
??--??用戶登錄的客戶端主機名?
?select sys_context('usernv','host') into hostname from dual;
??--??登錄認證方式，數(shù)據(jù)庫認證或外部認證
?select sys_context('usernv','authentication_type') into auth_type from dual;
?insert into system.session_info values (user,sysdate,session_id,ip_addr,hostname,auth_type);
end;
SELECT * FROM system.session_info;
===============================================================
對表employees創(chuàng)建觸發(fā)器
create or replace trigger tr_employee after update on employees
for each row
begin
?if(:new.salary>40000) then
?raise_application_error(-20002,'職員工資不能超過 40000');
?end if;
end;
===============================================================

create or replace procedure demo(salary in number) as
??cursor_name integer;
??rows_processed interger;
begin
??cursor_name:=dbms_sql.open_cursor;
??dbms_sql.parse(cursor_name,'delete from salary_records where empsal>:temp_sal',dbms_sql.native);
??dbms_sql.bind_variable(cursor_name,':temp_sal',salay);
??rows_processed:=dbms_sql.execute(cursor_name);
??dbms_sql.close_cursor(crusor_name);
exception
??when others then
????dbms_sql.close_cursor(cursor_name);
end;
===============================================================
1.寫一個帶程序包的函數(shù)，只要傳入文件名和地址就可以把這個文件的內(nèi)容存到BLOB類型的字段中。
binbo>create directory tnpdir as 'c:bfile';
binbo>grant read on directory tnpdir to scott;
binbo>CREATE TABLE my_dia
(
??chapter_descr VARCHAR2(40),
??diagram_no INTEGER,
??diagram BLOB
);

DECLARE
??l_bf??BFILE;
??l_bl BLOB;
BEGIN
??INSERT INTO my_dia (diagram)
??VALUES (EMPTY_BLOB())
??RETURN diagram INTO l_bl;
??l_bf := BFILENAME('jsp', 'test.jsp');
??DBMS_LOB.OPEN(l_bf, DBMS_LOB.FILE_READONLY);
??DBMS_LOB.LOADFROMFILE(l_bl, l_bf, DBMS_LOB.GETLENGTH(l_bf));
??DBMS_LOB.CLOSE(l_bf);
??COMMIT;
END;
===============================================================
2.有一張表，字段的值是這樣的：name varchar2(20),sex char(2),score number(3)。其中的SCORE字段為分數(shù)字段。請用一條SQL語句把九十分以上的顯示為A。九十到七十分的為B。七十分以下的為C。

binbo>create table test(name varchar2(20),sex char(2),score number(3));
binbo>select name,sex,case when score<=70 then 'C'
when score<=90 and score>70 when 'B'
when score>90 when 'A'
end case from test;
===============================================================
3.有一個表，其中有一個字段為自動增長的數(shù)據(jù)類型。請在ORACLE中實現(xiàn)。
binbo>create table test(id number,name varchar2(20));
create sequence seq_test increment by 1 start with 1 maxvalue 999;
create or replace trigger tr_test before insert or update of id on test
for each row
begin
if insert into then
select seq_test.nextval into :new.id from dual;
else
raise_application_error(-20002,'不允許更新ID序列!');
end if;
end;
===============================================================
4.如何刪除一個用戶下的所有表。

binbo>spool c:test.sql
binbo>select 'drop table '||tname||';'from tab;
binbo>spool off
binbo>@c:test;
===============================================================
5。如何把數(shù)據(jù)庫的日志模式從歸檔模式變?yōu)榉菤w檔模式

binbo>shutdown immediate
binbo>startup mount
binbo>alter database archivelog;
binbo>archive log list;
binbo>alter system set log_archive_dest=false scope=spfile;
binbo>alter database open;
===============================================================

6。建立一個用戶和表空間，在這個用戶和表空間下建立一張表。并授予SCOTT用戶查詢權(quán)利。
binbo>create user binbo identified by binbo;
binbo>create tablespace test datafile 'e:test.dbf' size 10m;
binbo>GRANT SELECT ON scott.test to scott;
===============================================================
7。寫一個過程，計算某個月有多少天。
?create or replace procedure dept(test in varchar2)
?as
?aa varchar2(20);
??begin
?select extract(day from last_day(to_date (test,'yyyy-mm'))) into aa from dual;
??dbms_output.put_line(aa);
??end;
===============================================================
8。有一章表，字段為name,sex,score，score字段為分數(shù)字段，查詢出這個班的第五名到第七名的人的姓名。
binbo>create table test(name varchar2(20),sex char(3),score number(3));
binbo>select * from (select name n,score sc,rownum r from (select name,score,rownum from test order by score desc)) where r between 5 and 7;
===============================================================
9。查詢出當前這個星期的星期六是幾號。

binbo>select next_day(sysdate,'星期六') from dual;
===============================================================
10。做一個外鍵關聯(lián)的兩個表。然后用觸發(fā)器做級聯(lián)更新。
create table test(name varchar2(20),sex char(3),score number(3));
?create table test_t(name varchar2(20));

?create or replace trigger test_test before insert or update of name on test
?for each row
?begin
?if inserting then
??insert into test_t(name) values (:new.name);
??dbms_output.put_line('test_t表中name也已經(jīng)插入!');
?elseif updating then
??update test_t set name=:new.name where name=old.name;
??dbms_output.put_line('test_t表中name也已經(jīng)更新!');
?elseif deleting then
??delete from test_t where name=:old.name;
??dbms_output.put_line('test_t表中name也已經(jīng)刪除!');
?else
??raise_application_error(-20002,'不允許更新test表中的name字段');
?end if;
?end;

===============================================================
---從外界向數(shù)據(jù)庫中插入數(shù)據(jù)
SQL> create table test_file(name varchar(30),shell varchar2(30));
表已創(chuàng)建。

G:盤data.ctl:(tab鍵隔開時間用x'09')
load data into table test_file fields terminated by '=='(name,shell);

G:盤data.txt:
aaaaaaaa==11111111
bbbbbbbb==22222222
cccccccc==33333333
binbo==hehehehe

C:Documents and SettingsAdministrator>sqlldr rbb/rbb control=G:data.ctl data=G:data.txt

SQL*Loader: Release 9.2.0.1.0 - Production on 星期二 7月 10 20:37:47 2007

Copyright (c) 1982, 2002, Oracle Corporation.??All rights reserved.

達到提交點，邏輯記錄計數(shù)3
達到提交點，邏輯記錄計數(shù)4

SQL> select * from test_file;

NAME???????????????????????????SHELL
------------------------------ ------------------------------
aaaaaaaa???????????????????????11111111
bbbbbbbb???????????????????????22222222
cccccccc???????????????????????33333333
binbo??????????????????????????hehehehe

?

===============================================================
?declare
???result clob;
???xmlstr varchar2(32767);
???line varchar2(2000);
???line_no integer:=1;
???begin
???result:=dbms_xmlquery.getxml('select * from test');
???xmlstr:=dbms_lob.substr(result,32767);
???loop
??exit when xmlstr is null;
?line:=substr(xmlstr,1,instr(xmlstr,chr(10))-1);
??dbms_output.put_line(line_no||':'||line);
??xmlstr:=substr(xmlstr,instr(xmlstr,chr(10))+1);
??line_no:=line_no+1;
?end loop;
?end;
SQL> /

PL/SQL 過程已成功完成。

?

SQL> select instr('abcdefsfssdfabcabcsdfs','bc',2,2) "instring" from dual;

??instring
----------
????????14

===============================================================
---創(chuàng)建表中自動增長列(利用觸發(fā)器)
//創(chuàng)建表
create table test_bin(id number(3),name varchar2(20));
//創(chuàng)建序列
create sequence test_sq increment by 1 start with 1 maxvalue 1000;
//創(chuàng)建觸發(fā)器
行級觸發(fā)器
create or replace trigger test_test before insert or update of id on test_bin
for each row
begin
if inserting then
select test_sq.nextval into :new.id from dual;
else
raise_application_error(-20002,'不允許更新id');
end if;
end;
===============================================================
語句級觸發(fā)器

create or replace trigger trgdemo after insert or update or delete on order_master
begin
?if updating then
??dbms_output.put_line('已更新order_master中的數(shù)據(jù)');
?elseif deleting then
??dbms_output.put_line('已刪除order_master中的數(shù)據(jù)');
?elseif inserting then
??dbms_output.put_line('已在order_master中插入數(shù)據(jù)');
?end if;
end;
===============================================================
instead of 觸發(fā)器(主要用在視圖中[視圖中只能有for each row])

create or replace trigger upda_ord instead of update on ord_view
for each row
begin
??update order_master set vencode=:new.vencode where orderno=:new.orderno;
??dbms_output.put_line('已激活觸發(fā)器');
end;
===============================================================
觸發(fā)器由三部分組成：
觸發(fā)器語句（事件）
定義激活觸發(fā)器的 DML 事件和 DDL 事件
觸發(fā)器限制
執(zhí)行觸發(fā)器的條件，該條件必須為真才能激活觸發(fā)器
觸發(fā)器操作（主體）
包含一些 SQL 語句和代碼，它們在發(fā)出了觸發(fā)器語句且觸發(fā)限制的值為真時運行

===============================================================
觸發(fā)器類型

DDL 觸發(fā)器
數(shù)據(jù)庫級觸發(fā)器
DML 觸發(fā)器
語句級觸發(fā)器
行級觸發(fā)器
INSTEAD OF 觸發(fā)器
===============================================================
模式觸發(fā)器
create table dropped_obj(obj_name varchar2(30),obj_type varchar2(20),drop_date date);

create or replace trigger log_drop_obj after drop on schema
begin
?insert into dropped_obj values(ora_dict_obj_name,ora_dict_obj_type,sysdate);
end;
===============================================================
啟用和禁用觸發(fā)器
alter trigger aiu_name disable;
alter trigger aiu_name enable;
===============================================================
刪除觸發(fā)器
drop trigger aiu_name;
===============================================================
user_triggers數(shù)據(jù)字典視圖包含有關觸發(fā)器的信息
select trigger_name from user_triggers where table_name='emp';

select trigger_type,triggering_event,when_clause from user_triggers
?where trigger_name='biu_emp_deptno';
===============================================================
dbms_output包顯示pl/sql塊和子程序的調(diào)試信息
set serveroutput on
BEGIN
??DBMS_OUTPUT.PUT_LINE('打印三角形');
??FOR i IN 1..9 LOOP
????FOR j IN 1..i LOOP
??????DBMS_OUTPUT.PUT('*');
????END LOOP for_j;
????DBMS_OUTPUT.NEW_LINE;
??END LOOP for_i;
END;
打印三角形
*
**
***
****
*****
******
*******
********
*********

PL/SQL 過程已成功完成。
===============================================================
1.DBMS_LOB 包提供用于處理大型對象的過程和函數(shù)
2.DBMS_XMLQUERY 包用于將查詢結(jié)果轉(zhuǎn)換為 XML 格式
DECLARE??result CLOB;
??xmlstr VARCHAR2(32767);
??line???VARCHAR2(2000);
??line_no INTEGER := 1;
BEGIN
??result := DBMS_XMLQuery.getXml('SELECT * FROM test');
??xmlstr := DBMS_LOB.SUBSTR(result,32767);
?LOOP
??EXIT WHEN xmlstr IS NULL;
??line := SUBSTR(xmlstr,1,INSTR(xmlstr,CHR(10))-1);
??DBMS_OUTPUT.PUT_LINE(line_no || ':' || line);
??xmlstr := SUBSTR(xmlstr,INSTR(xmlstr,CHR(10))+1);
??line_no := line_no + 1;
?END LOOP;
END;

===============================================================
一些常用的內(nèi)置程序包：
DBMS_OUTPUT 包輸出 PL/SQL 程序的調(diào)試信息
DBMS_LOB 包提供操作 LOB 數(shù)據(jù)的子程序
DBMS_XMLQUERY 將查詢結(jié)果轉(zhuǎn)換為 XML 格式
DBMS_RANDOM 提供隨機數(shù)生成器
UTL_FILE 用于讀寫操作系統(tǒng)文本文件
===============================================================
觸發(fā)器

CREATE OR REPLACE TRIGGER biu_emp_deptno
BEFORE INSERT OR UPDATE OF deptno
ON emp
FOR EACH ROW
WHEN (New.deptno <> 40)
BEGIN
??:New.comm := 0;
END;

/
觸發(fā)器已創(chuàng)建
===============================================================
----沒有表還不能測試

CREATE VIEW ord_view AS
SELECT order_master.orderno, order_master.ostatus,
???????order_detail.qty_deld, order_detail.qty_ord???
FROM order_master, order_detail
WHERE order_master.orderno = order_detail.orderno;

CREATE OR REPLACE TRIGGER order_mast_insert
INSTEAD OF INSERT ON ord_view
REFERENCING NEW AS n
FOR EACH ROW
DECLARE
??CURSOR ecur IS SELECT *??FROM order_master
????WHERE order_master.orderno = :n.orderno;
??CURSOR dcur IS SELECT * FROM order_detail
????WHERE order_detail.orderno = :n.orderno;
??a ecur%rowtype;
??b dcur%rowtype;
BEGIN
??OPEN ecur;
??OPEN dcur;
??FETCH ecur into a;
??FETCH dcur into b;
??IF dcur%notfound THEN
????INSERT INTO order_master(orderno,ostatus)
????VALUES(:n.orderno, :n.ostatus);
??ELSE
????UPDATE order_master SET order_master.ostatus = :n.ostatus
????WHERE order_master.orderno = :n.orderno;
??END IF;
??IF ecur%notfound THEN
????INSERT INTO order_detail(qty_ord,qty_deld,orderno)
????VALUES(:n.qty_ord, :n.qty_deld, :n.orderno);
??ELSE
????UPDATE order_detail
????SET order_detail.qty_ord??= :n.qty_ord,
????????order_detail.qty_deld = :n.qty_deld
????WHERE order_detail.orderno = :n.orderno;
??END IF;
??CLOSE ecur;
??CLOSE dcur;
END;
/
===============================================================
CREATE TABLE dropped_obj
(
??obj_name VARCHAR2(30),
??obj_type VARCHAR2(20),
??drop_date DATE
);

CREATE OR REPLACE TRIGGER log_drop_obj
AFTER DROP ON SCHEMA
BEGIN
??INSERT INTO dropped_obj
??VALUES (ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);
END;
/

ALTER TRIGGER biu_emp_deptno DISABLE;

ALTER TRIGGER biu_emp_deptno ENABLE;

DROP TRIGGER biu_emp_deptno;

DESC USER_TRIGGERS;

===============================================================
DECLARE
??l_num???NUMBER;
??counter NUMBER;
BEGIN
??counter:=1;
??WHILE counter <= 10
??LOOP
????l_num := ABS((DBMS_RANDOM.RANDOM MOD 100)) + 1;
????DBMS_OUTPUT.PUT_LINE(l_num);
????counter := counter + 1;
??END LOOP;
END;
/
40
4
35
52
68
5
94
38
49
51

PL/SQL 過程已成功完成。
===============================================================
===============================================================
---查詢出表中數(shù)據(jù)轉(zhuǎn)換為xml格式
-- 以 SYSTEM 用戶登錄執(zhí)行命令
CREATE DIRECTORY TEST_DIR AS 'C:DEVELOP';
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO SCOTT;

-- 以 SCOTT 用戶登錄
DECLARE
??src CLOB;
??xmlfile UTL_FILE.FILE_TYPE;
??length INTEGER;
??buffer VARCHAR2(16384);
BEGIN
??src := DBMS_XMLQuery.getXml('select * from emp');
??length := DBMS_LOB.GETLENGTH(src);
??DBMS_LOB.READ(src, length, 1, buffer);
??xmlfile := UTL_FILE.FOPEN('TEST_DIR', 'employees.xml', 'w');
??UTL_FILE.PUT(xmlfile, buffer);
??UTL_FILE.FCLOSE(xmlfile);
END;
/
===============================================================
-------讀取xml格式的文件
UTL_FILE 包用于讀寫操作系統(tǒng)文本文件
操作文件的一般過程是打開、讀或?qū)憽㈥P閉
UTL_FILE 包指定文件路徑依賴于 DIRECTORY 對象
1.CREATE DIRECTORY TEST_DIR AS 'C:DEVELOP';
2.GRANT READ, WRITE ON DIRECTORY TEST_DIR TO SCOTT

SET SERVEROUT ON FORMAT WRAPPED
DECLARE
??input_file???UTL_FILE.FILE_TYPE;
??input_buffer VARCHAR2(4000);
BEGIN
??input_file := UTL_FILE.FOPEN('TEST_DIR', 'employees.xml', 'r');
??FOR I IN 1..11 LOOP
????UTL_FILE.GET_LINE(input_file, input_buffer);
????DBMS_OUTPUT.PUT_LINE(input_buffer);
??END LOOP;
??UTL_FILE.FCLOSE(input_file);
EXCEPTION
??WHEN NO_DATA_FOUND THEN
????DBMS_OUTPUT.PUT_LINE('------------------');
END;
===============================================================

oracle 數(shù)據(jù)庫重生$rman
oracle 數(shù)據(jù)庫備份兩種類型:物理備份和邏輯備份
===============================================================
oracle 故障有4種類型
?1.語句故障
在執(zhí)行 SQL 語句過程中發(fā)生的邏輯故障可導致語句故障。如果用戶編寫的 SQL 語句無效，就會發(fā)生邏輯故障
2.用戶進程故障
當用戶程序出錯而無法訪問數(shù)據(jù)庫時發(fā)生用戶進程故障。導致用戶進程故障的原因是異常斷開連接或異常終止進程
3. 實例故障
當 Oracle 的數(shù)據(jù)庫實例由于硬件或軟件問題而無法
繼續(xù)運行時，就會發(fā)生實例故障

4.介質(zhì)故障
在數(shù)據(jù)庫無法正確讀取或?qū)懭肽硞€數(shù)據(jù)庫文件時，
會發(fā)生介質(zhì)故障?
===============================================================
oracle 導出導入模式

1.完全數(shù)據(jù)庫
導出和導入整個數(shù)據(jù)庫中的所有對象
2.表
導出和導入一個或多個指定的表或表分區(qū)
3.用戶
導出和導入一個用戶模式中的所有對象
4.表空間
導出和導入一個或多個指定的表空間中的所有對象

===============================================================
導出實用程序有以下常用命令參數(shù)

USERID?確定執(zhí)行導出實用程序的用戶名和口令
BUFFER?確定導出數(shù)據(jù)時所使用的緩沖區(qū)大小，其大小用字節(jié)表示
FILE?指定導出的二進制文件名稱，默認的擴展名是.dmp
FULL?指定是否以全部數(shù)據(jù)庫方式導出，只有授權(quán)用戶才可使用此參數(shù)
OWNER?要導出的數(shù)據(jù)庫用戶列表
HELP?指定是否顯示幫助消息和參數(shù)說明
ROWS?確定是否要導出表中的數(shù)據(jù)
TABLES?按表方式導出時，指定需導出的表和分區(qū)的名稱
PARFILE?指定傳遞給導出實用程序的參數(shù)文件名
TABLESPACES?按表空間方式導出時，指定要導出的表空間名

按用戶方式導出數(shù)據(jù)
exp?scott/tiger@tsinghua?file=scott_back owner=scott

按表方式導出數(shù)據(jù)
exp?scott/tiger@tsinghua?tables=(emp, dept) file=scott_back_tab

按表空間方式導出數(shù)據(jù)
exp?system/zl@tsinghua?tablespaces=(users) file=tbs_users

使用參數(shù)文件導出數(shù)據(jù)
exp system/zl parfile='C:parameters.txt'

===============================================================
導入實用程序有如下常用命令參數(shù)

USERID?指定執(zhí)行導入的用戶名和密碼
BUFFER?指定用來讀取數(shù)據(jù)的緩沖區(qū)大小，以字節(jié)為單位
COMMIT?指定是否在每個數(shù)組（其大小由BUFFER參數(shù)設置）插入后進行提交
FILE?指定要導入的二進制文件名
FROMUSER?指定要從導出轉(zhuǎn)儲文件中導入的用戶模式
TOUSER?指定要將對象導入的用戶名。FROMUSER與TOUSER可以不同
FULL?指定是否要導入整個導出轉(zhuǎn)儲文件
TABLES?指定要導入的表的列表
ROWS?指定是否要導入表中的行
PARFILE?指定傳遞給導入實用程序的參數(shù)文件名，此文件可以包含這里列出的所有參數(shù)
IGNORE?導入時是否忽略遇到的錯誤，默認為N
TABLESPACES?按表空間方式導入，列出要導入的表空間名

將整個文件導入數(shù)據(jù)庫
imp?tsinghua/tsinghua@tsinghua?file=item_back.dmp ignore=y full=y

將scott用戶的表導入到martin用戶
imp?system/zl@tsinghua?file=scott_back fromuser=scott touser=martin tables=(emp,dept)

使用參數(shù)文件導入數(shù)據(jù)
imp system/oracle parfile='C:parameters.txt'
===============================================================
歸檔日志方式下的數(shù)據(jù)庫:自動歸檔和手動歸檔

手動歸檔允許用戶手動歸檔非活動日志文件文件的已填充組
自動歸檔對非活動日志文件文件進行自動歸檔
===============================================================
數(shù)據(jù)庫可在兩種方式下運行：非歸檔日志方式和歸檔日志方式
非歸檔日志方式可以避免實例故障，但無法避免介質(zhì)故障。在此方式下，數(shù)據(jù)庫只能實施冷備份.
歸檔日志方式產(chǎn)生歸檔日志，用戶可以使用歸檔日志完全恢復數(shù)據(jù)庫.
===============================================================

導出和導入實用程序的特點有：
可以按時間保存表結(jié)構(gòu)和數(shù)據(jù)
1.允許導出指定的表，并重新導入到新的數(shù)據(jù)庫中
2.可以把數(shù)據(jù)庫遷移到另外一臺異構(gòu)服務器上
3.在兩個不同版本的Oracle數(shù)據(jù)庫之間傳輸數(shù)據(jù)
4.在聯(lián)機狀態(tài)下進行備份和恢復
5.可以重新組織表的存儲結(jié)構(gòu)，減少鏈接及磁盤碎片
===============================================================

oracle表輸格式為xml全過程.txt

SQL> conn sys/sys as sysdba
已連接。
SQL> drop directory test_dir
??2??/

目錄已丟棄。

SQL> CREATE DIRECTORY TEST_DIR AS 'C:';

目錄已創(chuàng)建。

SQL> GRANT READ, WRITE ON DIRECTORY TEST_DIR TO rbb;

授權(quán)成功。

SQL> conn rbb/rbb
已連接。
SQL> ed
已寫入文件 afiedt.buf

??1??DECLARE
??2????src CLOB;
??3????xmlfile UTL_FILE.FILE_TYPE;
??4????length INTEGER;
??5????buffer VARCHAR2(16384);
??6??BEGIN
??7????src := DBMS_XMLQuery.getXml('select * from liuxing');
??8????length := DBMS_LOB.GETLENGTH(src);
??9????DBMS_LOB.READ(src, length, 1, buffer);
?10????xmlfile := UTL_FILE.FOPEN('TEST_DIR', 'emp.xml', 'w');
?11????UTL_FILE.PUT(xmlfile, buffer);
?12????UTL_FILE.FCLOSE(xmlfile);
?13* END;
SQL> /

PL/SQL 過程已成功完成。
===============================================================

SQL> ed
已寫入文件 afiedt.buf

??1??declare
??2??lname number;
??3??counter number;
??4??begin
??5??counter:=1;
??6??while counter<=10
??7??loop
??8??lname:=dbms_random.random;
??9??dbms_output.put_line(lname);
?10??counter:=counter+1;
?11??end loop;
?12* end;
SQL> /
277652640
-479979827
-1049652647
-1006595853
1252280346
196435204
466478280
-85782435
-1489036577
-927786638

PL/SQL 過程已成功完成。

已用時間:??00: 00: 00.00
===============================================================

修改表名

alter table old_table_name rename to new_table_name;
===============================================================
估算SQL執(zhí)行的I/O數(shù)

SQL>SET AUTOTRACE ON ;

SQL>SELECT * FROM TABLE;

OR

SQL>SELECT * FROM v$filestat ;
===============================================================

?如何查有多少個數(shù)據(jù)庫實例

SQL>SELECT * FROM V$INSTANCE;
===============================================================

查詢數(shù)據(jù)庫有多少表

SQL>select * from all_tables;

===============================================================

顯示測試SQL語句執(zhí)行所用的時間

SQL>set timing on ;
===============================================================

監(jiān)控事例的等待
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4;
===============================================================
回滾段的爭用情況
select name, waits, gets, waits/gets "Ratio"
from v$rollstat C, v$rollname D
where C.usn = D.usn;
===============================================================
監(jiān)控表空間的 I/O 比例
select B.tablespace_name name,B.file_name "file",A.phyrds pyr,
A.phyblkrd pbr,A.phywrts pyw, A.phyblkwrt pbw
from v$filestat A, dba_data_files B
where A.file# = B.file_id
order by B.tablespace_name;
===============================================================
監(jiān)控文件系統(tǒng)的 I/O 比例
select substr(C.file#,1,2) "#", substr(C.name,1,30) "Name",
C.status, C.bytes, D.phyrds, D.phywrts
from v$datafile C, v$filestat D
where C.file# = D.file#;
===============================================================
在某個用戶下找所有的索引
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
===============================================================
監(jiān)控 SGA 的命中率
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
===============================================================
監(jiān)控 SGA 中字典緩沖區(qū)的命中率
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
===============================================================
監(jiān)控 SGA 中共享緩存區(qū)的命中率，應該小于1%?
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;

select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
===============================================================
顯示所有數(shù)據(jù)庫對象的類別和大小
select count(name) num_instances ,type ,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type order by 2;
===============================================================
監(jiān)控 SGA 中重做日志緩存區(qū)的命中率，應該小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
===============================================================
監(jiān)控內(nèi)存和硬盤的排序比率，最好使它小于 .10，增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');

===============================================================
監(jiān)控當前數(shù)據(jù)庫誰在運行什么SQL語句
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
===============================================================
監(jiān)控字典緩沖區(qū)
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
===============================================================
后者除以前者,此比率小于1%,接近0%為好。

SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE
===============================================================

監(jiān)控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大于0.5時，參數(shù)需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers時，參數(shù)需加大
===============================================================
知道當前用戶的ID號
SQL>SHOW USER;
OR
SQL>select user from dual;
===============================================================
查看碎片程度高的表
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
===============================================================
知道表在表空間中的存儲情況
select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;
===============================================================
知道索引在表空間中的存儲情況
select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'
group by segment_name;
===============================================================
知道使用CPU多的用戶session 11是cpu used by this session

select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by value desc;
===============================================================

?

?

?

?

?

?

?

?

?

?

?

?

?

?

**************************************************SQLServer語言基本語句***********************************************************

SQL分類：

　　DDL—數(shù)據(jù)定義語言(CREATE，ALTER，DROP，DECLARE)
　　DML—數(shù)據(jù)操縱語言(SELECT，DELETE，UPDATE，INSERT)
　　DCL—數(shù)據(jù)控制語言(GRANT，REVOKE，COMMIT，ROLLBACK)

　　首先,簡要介紹基礎語句：
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　1、說明：創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE database-name
create database database
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　2、說明：刪除數(shù)據(jù)庫
drop database database
drop database dbname
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　3、說明：備份sql server

　　--- 創(chuàng)建 備份數(shù)據(jù)的 device
user pubs
exec database 'disk'
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'

　　--- 開始 備份
backup database pubs to testback
BACKUP DATABASE pubs TO testBack
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　4、說明：創(chuàng)建新表
create table tabname(name nvchar(20) primary key,password nvchar(16))
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

　　根據(jù)已有的表創(chuàng)建新表：

A：create table tab_new like tab_old (使用舊表創(chuàng)建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　5、說明：

　　刪除新表：drop table tabname
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　6、說明：

　　增加一個列：Alter table tabname add column col type
alter table tabanme add column col

　　注：列增加后將不能刪除。DB2中列加上后數(shù)據(jù)類型也不能改變，唯一能改變的是增加varchar類型的長度。
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　7、說明：

　　添加主鍵：Alter table tabname add primary key(col)

　　說明：

　　刪除主鍵：Alter table tabname drop primary key(col)
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　8、說明：

　　創(chuàng)建索引：create [unique] index idxname on tabname(col….)

　　刪除索引：drop index idxname

　　注：索引是不可更改的，想更改必須刪除重新建。
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　9、說明：

　　創(chuàng)建視圖：create view viewname as select statement

　　刪除視圖：drop view viewname
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　10、說明：幾個簡單的基本的sql語句

　　選擇：select * from table1 where 范圍

　　插入：insert into table1(field1,field2) values(value1,value2)

　　刪除：delete from table1 where 范圍

　　更新：update table1 set field1=value1 where 范圍

　　查找：select * from table1 where field1 like ’%value1%’ ---like的語法很精妙，查資料!

　　排序：select * from table1 order by field1,field2 [desc]

　　總數(shù)：select count * as totalcount from table1

　　求和：select sum(field1) as sumvalue from table1

　　平均：select avg(field1) as avgvalue from table1

　　最大：select max(field1) as maxvalue from table1

　　最小：select min(field1) as minvalue from table1
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　11、說明：幾個高級查詢運算詞
下列語句創(chuàng)建 STAFF 表 中 20 部門的非經(jīng)理人員視圖，其中薪水和傭金不通過基表顯示。

　　CREATE VIEW STAFF_ONLY

　　AS SELECT ID, NAME, DEPT, JOB, YEARS

　　FROM STAFF

　　WHERE JOB <> 'Mgr' AND DEPT=20

　　在創(chuàng)建視圖之后，下列語句顯示視圖的內(nèi)容:

　　SELECT *　FROM STAFF_ONLY

　　A： UNION union 運算符

　　UNION 運算符通過組合其他兩個結(jié)果表（例如 TABLE1 和 TABLE2）并消去表中任何重復行而派生出一個結(jié)果表。當 ALL 隨 UNION 一起使用時（即 UNION ALL），不消除重復行。兩種情況下，派生表的每一行不是來自 TABLE1 就是來自 TABLE2。

　　B： EXCEPT except運算符

　　EXCEPT 運算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重復行而派生出一個結(jié)果表。當 ALL 隨 EXCEPT 一起使用時 (EXCEPT ALL)，不消除重復行。

　　C： INTERSECT intersect運算符

　　INTERSECT 運算符通過只包括 TABLE1 和 TABLE2 中都有的行并消除所有重復行而派生出一個結(jié)果表。當 ALL 隨 INTERSECT 一起使用時 (INTERSECT ALL)，不消除重復行。

　　注：使用運算詞的幾個查詢結(jié)果行必須是一致的。
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
　　12、說明：使用外連接

　　A、left outer join：

　　左外連接（左連接）：結(jié)果集幾包括連接表的匹配行，也包括左連接表的所有行。

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

　　B：right outer join:

　　右外連接(右連接)：結(jié)果集既包括連接表的匹配連接行，也包括右連接表的所有行。

　　C：full outer join：

　　全外連接：不僅包括符號連接表的匹配行，還包括兩個連接表中的所有記錄。

　　其次，大家來看一些不錯的sql語句

　　1、說明：復制表(只復制結(jié)構(gòu),源表名：a 新表名：b) (Access可用)

　　法一：select * into b from a where 1<>1

　　法二：select top 0 * into b from a

　　2、說明：拷貝表(拷貝數(shù)據(jù),源表名：a 目標表名：b) (Access可用)

insert into b(a, b, c) select d,e,f from b;

　　3、說明：跨數(shù)據(jù)庫之間表的拷貝(具體數(shù)據(jù)使用絕對路徑) (Access可用)

insert into b(a, b, c) select d,e,f from b in ‘具體數(shù)據(jù)庫’ where 條件

　　例子：..from b in '"&Server.MapPath(".")&"data.mdb" &"' where..

　　4、說明：子查詢(表名1：a 表名2：b)

select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)

　　5、說明：顯示文章、提交人和最后回復時間

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

　　6、說明：外連接查詢(表名1：a 表名2：b)

select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

　　7、說明：在線視圖查詢(表名1：a )

select * from (SELECT a,b,c FROM a) T where t.a > 1;

　　8、說明：between的用法,between限制查詢數(shù)據(jù)范圍時包括了邊界值,not between不包括

select * from table1 where time between time1 and time2

select a,b,c, from table1 where a not between 數(shù)值1 and 數(shù)值2

　　9、說明：in 的使用方法

select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)

　　10、說明：兩張關聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息

delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

　　11、說明：四表聯(lián)查問題：

select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

　　12、說明：日程安排提前五分鐘提醒

SQL: select * from 日程安排 where datediff('minute',f開始時間,getdate())>5

　　13、說明：一條sql 語句搞定數(shù)據(jù)庫分頁

select top 10 b.* from (select top 20 主鍵字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主鍵字段 = a.主鍵字段 order by a.排序字段

　　14、說明：前10條記錄

select top 10 * form table1 where 范圍

　　15、說明：選擇在每一組b值相同的數(shù)據(jù)中對應的a最大的記錄的所有信息(類似這樣的用法可以用于論壇每月排行榜,每月熱銷產(chǎn)品分析,按科目成績排名,等等.)

select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)

　　16、說明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重復行而派生出一個結(jié)果表

(select a from tableA ) except (select a from tableB) except (select a from tableC)

　　17、說明：隨機取出10條數(shù)據(jù)

select top 10 * from tablename order by newid()

　　18、說明：隨機選擇記錄

select newid()

　　19、說明：刪除重復記錄

Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

　　20、說明：列出數(shù)據(jù)庫里所有的表名

select name from sysobjects where type='U'

　　21、說明：列出表里的所有的

select name from syscolumns where id=object_id('TableName')

　　22、說明：列示type、vender、pcs字段，以type字段排列，case可以方便地實現(xiàn)多重選擇，類似select 中的case。

select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type

　　顯示結(jié)果：

type????vender pcs
電腦 A 1
電腦 A 1
光盤 B 2
光盤 A 2
手機 B 3
手機 C 3

　　23、說明：初始化表table1

TRUNCATE TABLE table1

　　24、說明：選擇從10到15的記錄

select top 5 * from (select top 15 * from table order by id asc) table_別名 order by id desc

　　隨機選擇數(shù)據(jù)庫記錄的方法（使用Randomize函數(shù)，通過SQL語句實現(xiàn)）

　　對存儲在數(shù)據(jù)庫中的數(shù)據(jù)來說，隨機數(shù)特性能給出上面的效果，但它們可能太慢了些。你不能要求ASP“找個隨機數(shù)”然后打印出來。實際上常見的解決方案是建立如下所示的循環(huán)：

Randomize
RNumber = Int(Rnd*499) +1
　
While Not objRec.EOF
If objRec("ID") = RNumber THEN
... 這里是執(zhí)行腳本 ...
end if
objRec.MoveNext
Wend

　　這很容易理解。首先，你取出1到500范圍之內(nèi)的一個隨機數(shù)（假設500就是數(shù)據(jù)庫內(nèi)記錄的總數(shù)）。然后，你遍歷每一記錄來測試ID 的值、檢查其是否匹配RNumber。滿足條件的話就執(zhí)行由THEN 關鍵字開始的那一塊代碼。假如你的RNumber 等于495，那么要循環(huán)一遍數(shù)據(jù)庫花的時間可就長了。雖然500這個數(shù)字看起來大了些，但相比更為穩(wěn)固的企業(yè)解決方案這還是個小型數(shù)據(jù)庫了，后者通常在一個數(shù)據(jù)庫內(nèi)就包含了成千上萬條記錄。這時候不就死定了？

　　采用SQL，你就可以很快地找出準確的記錄并且打開一個只包含該記錄的recordset，如下所示：

Randomize
RNumber = Int(Rnd*499) + 1
　
SQL = "SELECT * FROM Customers WHERE ID = " & RNumber
　
set objRec = ObjConn.Execute(SQL)
Response.WriteRNumber & " = " & objRec("ID") & " " & objRec("c_email")

　　不必寫出RNumber 和ID，你只需要檢查匹配情況即可。只要你對以上代碼的工作滿意，你自可按需操作“隨機”記錄。Recordset沒有包含其他內(nèi)容，因此你很快就能找到你需要的記錄這樣就大大降低了處理時間。

　　再談隨機數(shù)

　　現(xiàn)在你下定決心要榨干Random 函數(shù)的最后一滴油，那么你可能會一次取出多條隨機記錄或者想采用一定隨機范圍內(nèi)的記錄。把上面的標準Random 示例擴展一下就可以用SQL應對上面兩種情況了。

　　為了取出幾條隨機選擇的記錄并存放在同一recordset內(nèi)，你可以存儲三個隨機數(shù)，然后查詢數(shù)據(jù)庫獲得匹配這些數(shù)字的記錄：

　　SQL = "SELECT * FROM Customers WHERE ID = " & RNumber & " OR ID = " & RNumber2 & " OR ID = " & RNumber3

　　假如你想選出10條記錄（也許是每次頁面裝載時的10條鏈接的列表），你可以用BETWEEN 或者數(shù)學等式選出第一條記錄和適當數(shù)量的遞增記錄。這一操作可以通過好幾種方式來完成，但是 SELECT 語句只顯示一種可能（這里的ID 是自動生成的號碼）：
SQL = "SELECT * FROM Customers WHERE ID BETWEEN " & RNumber & " AND " & RNumber & "+ 9"

　　注意：以上代碼的執(zhí)行目的不是檢查數(shù)據(jù)庫內(nèi)是否有9條并發(fā)記錄。

　　隨機讀取若干條記錄，測試過

Access語法：SELECT top 10 * From 表名 ORDER BY Rnd(id)
Sql server:select top n * from 表名 order by newid()
MySQL?select * From 表名 Order By rand() Limit n

　　Access左連接語法(最近開發(fā)要用左連接,Access幫助什么都沒有,網(wǎng)上沒有Access的SQL說明,只有自己測試, 現(xiàn)在記下以備后查)

　　語法 select table1.fd1,table1,fd2,table2.fd2 From table1 left join table2 on table1.fd1,table2.fd1 where ...

　　使用SQL語句 用...代替過長的字符串顯示

　　語法：

　　SQL數(shù)據(jù)庫：select case when len(field)>10 then left(field,10)+'...' else field end as news_name,news_id from tablename
　　Access數(shù)據(jù)庫：SELECT iif(len(field)>2,left(field,2)+'...',field) FROM tablename;

　　Conn.Execute說明

　　Execute方法

　　該方法用于執(zhí)行SQL語句。根據(jù)SQL語句執(zhí)行后是否返回記錄集，該方法的使用格式分為以下兩種：

　　1．執(zhí)行SQL查詢語句時，將返回查詢得到的記錄集。用法為：

　　Set 對象變量名=連接對象.Execute("SQL 查詢語言")

　　Execute方法調(diào)用后，會自動創(chuàng)建記錄集對象，并將查詢結(jié)果存儲在該記錄對象中，通過Set方法，將記錄集賦給指定的對象保存，以后對象變量就代表了該記錄集對象。

　　2．執(zhí)行SQL的操作性語言時，沒有記錄集的返回。此時用法為：

　　連接對象.Execute "SQL 操作性語句" [, RecordAffected][, Option]

　　·RecordAffected 為可選項，此出可放置一個變量，SQL語句執(zhí)行后，所生效的記錄數(shù)會自動保存到該變量中。通過訪問該變量，就可知道SQL語句隊多少條記錄進行了操作。

　　·Option 可選項，該參數(shù)的取值通常為adCMDText，它用于告訴ADO，應該將Execute方法之后的第一個字符解釋為命令文本。通過指定該參數(shù)，可使執(zhí)行更高效。

　　·BeginTrans、RollbackTrans、CommitTrans方法

　　這三個方法是連接對象提供的用于事務處理的方法。BeginTrans用于開始一個事物；RollbackTrans用于回滾事務；CommitTrans用于提交所有的事務處理結(jié)果，即確認事務的處理。

　　事務處理可以將一組操作視為一個整體，只有全部語句都成功執(zhí)行后，事務處理才算成功；若其中有一個語句執(zhí)行失敗，則整個處理就算失敗，并恢復到處里前的狀態(tài)。

　　BeginTrans和CommitTrans用于標記事務的開始和結(jié)束，在這兩個之間的語句，就是作為事務處理的語句。判斷事務處理是否成功，可通過連接對象的Error集合來實現(xiàn)，若Error集合的成員個數(shù)不為0，則說明有錯誤發(fā)生，事務處理失敗。Error集合中的每一個Error對象，代表一個錯誤信息。

?

?

?

?

?

?

?

?

?

?

?

?

***************************************各種數(shù)據(jù)庫連接方法**********************************************************************

==================TestSQLServer.java連接SQLServer====================================

import java.sql.*;
import java.util.*;
public class TestSQLServer{?
???public static void main (String args[]){?????????
??????try{?
??Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
??String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=testdb";
??String user ="sa";
??String password = "sa";??
??Connection conn = DriverManager.getConnection(url,user,password);??????
?????????Statement stat = conn.createStatement();
?????????ResultSet result = stat.executeQuery("SELECT * FROM users");????????
?????????result.next();????????
?????????System.out.println(result.getString(1));
?????????System.out.println(result.getString(2));
?????????System.out.println(result.getString(3));????????
?????????result.close();?????
?????????stat.close();
?????????conn.close();
????????
??????}catch(ClassNotFoundException en){
????????System.out.println("數(shù)據(jù)庫驅(qū)動找不到！");
????????en.printStackTrace();?????
??????}catch(SQLException ex) {???????
?????????while (ex != null) {?
????????????ex.printStackTrace();
????????????ex = ex.getNextException();
?????????}????????
??????}catch(Exception e){
??????????System.out.println("其他未知異常！");
??????????e.printStackTrace();
??????}
???}
}

=================TestMysql.java連接mysql數(shù)據(jù)庫=======================================

package org.binbo.dom;
import java.sql.*;
public class TestMysql{?
???public static void main (String args[]){?????????
??????try{?
??Class.forName("com.mysql.jdbc.Driver").newInstance();??
??String url = "jdbc:mysql://localhost:3306/binbo";
??String user ="root";
??String password = "binbo";??
??Connection conn = DriverManager.getConnection(url,user,password);
??
????????Statement stat = conn.createStatement();
?????????ResultSet result = stat.executeQuery("SELECT * FROM testxml");????????
?????????result.next();????????
?????????System.out.println(result.getString(1));
?????????System.out.println(result.getString(2));
?????????System.out.println(result.getString(3));????????
?????????result.close();?????
?????????stat.close();
?????????conn.close();????????
??????}catch(ClassNotFoundException en){
????????System.out.println("數(shù)據(jù)庫驅(qū)動找不到！");
????????en.printStackTrace();?????
??????}catch(SQLException ex) {???????
?????????while (ex != null) {?
????????????ex.printStackTrace();
????????????ex = ex.getNextException();
?????????}????????
??????}catch(Exception e){
??????????System.out.println("其他未知異常！");
??????????e.printStackTrace();
??????}
???}

}
=================TestOracle.java連接oracle數(shù)據(jù)庫==================================

package org.binbo.dom;
import java.sql.*;
public class TestOracle{?
???public static void main (String args[]){?????????
??????try{?
??????Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();??
??????String url = "jdbc:oracle:thin:@localhost:1521:binbo";
??????String user ="scott";
??????String password = "tiger";???
??Connection conn = DriverManager.getConnection(url,user,password);
??
????????Statement stat = conn.createStatement();
?????????ResultSet result = stat.executeQuery("SELECT * FROM test");????????
?????????result.next();????????
?????????System.out.println(result.getString(1));
?????????System.out.println(result.getString(2));
?????????System.out.println(result.getString(3));????????
?????????result.close();?????
?????????stat.close();
?????????conn.close();????????
??????}catch(ClassNotFoundException en){
????????System.out.println("數(shù)據(jù)庫驅(qū)動找不到！");
????????en.printStackTrace();?????
??????}catch(SQLException ex) {???????
?????????while (ex != null) {?
????????????ex.printStackTrace();
????????????ex = ex.getNextException();
?????????}????????
??????}catch(Exception e){
??????????System.out.println("其他未知異常！");
??????????e.printStackTrace();
??????}
???}
}
===============XML連接數(shù)據(jù)庫=====================================================
da.xml
<?xml version="1.0" encoding="UTF-8"?>
<PEOPLE><!--
??<PERSON PERSONID="E01">
????<className>com.microsoft.jdbc.sqlserver.SQLServerDriver</className>
????<url>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=binbo</url>
????<user>sa</user>
????<password>sa</password>
??</PERSON>
??--><PERSON PERSONID="E02">
?????<className>com.mysql.jdbc.Driver</className>
????<url>jdbc:mysql://localhost:3306/binbo</url>
????<user>root</user>
????<password>binbo</password>
??</PERSON><!--
?<PERSON PERSONID="E03">
?????<className>oracle.jdbc.driver.OracleDriver</className>
????<url>jdbc:oracle:thin:@localhost:1521:binbo</url>
????<user>scott</user>
????<password>tiger</password>
??</PERSON>
--></PEOPLE>
--------------------------------------------
content.java
package org.binbo.dom;
import java.sql.Connection;
import java.sql.DriverManager;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
public class Content {
?public static Connection getConnection(){
??Connection conn =null;
??try {
???DocumentBuilderFactory factory=DocumentBuilderFactory.newInstance();
???DocumentBuilder builder=factory.newDocumentBuilder();
???Document doc=builder.parse("da.xml");
???NodeList nl=doc.getElementsByTagName_r("PERSON");
???Element node=(Element) nl.item(0);
???
?String className =node.getElementsByTagName_r("className").item(0).getFirstChild().getNodeValue();
????????
?String url =node.getElementsByTagName_r("url").item(0).getFirstChild().getNodeValue();
????????
?String user =node.getElementsByTagName_r("user").item(0).getFirstChild().getNodeValue();
???????
?String pwd =node.getElementsByTagName_r("password").item(0).getFirstChild().getNodeValue();???????
????????Class.forName(className);
????????conn =DriverManager.getConnection(url,user,pwd);?????
??
??} catch (Exception e) {
???e.printStackTrace();
??}
??return conn;
?}
}
--------------------------------------------
Domtest.java
package org.binbo.dom;
import java.io.FileOutputStream;
import java.sql.*;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;
import org.w3c.dom.Element;

public class Domtest {
?public static void main(String[] args){
??try{
???Connection conn =Content.getConnection();
???PreparedStatement ps=conn.prepareStatement("select * from testxml");
???ResultSet rs =ps.executeQuery();
???DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
???DocumentBuilder builder = factory.newDocumentBuilder();
???Document doc = builder.newDocument();
???Element Stu = doc.createElement_x("binboxml");
???Element uname = doc.createElement_x("username");
???Element nameid = doc.createElement_x("userid");
???Element pass = doc.createElement_x("password");
???while(rs.next()){????
????String id=rs.getString(1);
????String name=rs.getString(2);
????String pwd=rs.getString(3);
????System.out.print(rs.getString(1));
????System.out.print(rs.getString(2));
????System.out.print(rs.getString(3));
???????System.out.print("寫入成功！");
????nameid.appendChild(doc.createTextNode(id));
????uname.appendChild(doc.createTextNode(name));
????pass.appendChild(doc.createTextNode(pwd));
???}
???
???Stu.appendChild(nameid);???
???Stu.appendChild(uname);
???Stu.appendChild(pass);
???doc.appendChild(Stu);
???TransformerFactory tf = TransformerFactory.newInstance();
???Transformer tr = tf.newTransformer();
???tr.transform(new DOMSource(doc), new StreamResult(
?????new FileOutputStream("Binbo.xml")));
???rs.close();
??} catch(Exception e){
???e.printStackTrace();
??}

?}

}

=====================數(shù)據(jù)庫連接池=====================================

加到tomcat中的server.xml

<Logger className="org.apache.catalina.logger.FileLogger"
?????????????????directory="logs"??prefix="localhost_log." suffix=".txt"
????????????timestamp="true"/>

這個后面
<Context path="/myjsp" docBase="myjsp"
????????debug="5" reloadable="true" crossContext="true">
???????<Resource name="jdbc/myjsp"
???????????????auth="Container"
???????????????type="javax.sql.DataSource"/>
??<ResourceParams name="jdbc/myjsp">
????<parameter>
??????<name>factory</name>
??????<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
????</parameter>
????<parameter>
??????<name>maxActive</name>
??????<value>100</value>
????</parameter>
????<parameter>
??????<name>maxIdle</name>
??????<value>30</value>
????</parameter>
????<parameter>
??????<name>maxWait</name>
??????<value>10000</value>
????</parameter>
?
????<parameter>
?????<name>username</name>
?????<value>sa</value>
????</parameter>
????<parameter>
?????<name>password</name>
?????<value>sa</value>
????</parameter>
????<parameter>
???????<name>driverClassName</name>
???????<value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
????</parameter>
????<parameter>
??????<name>url</name>
??????<value>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jsp</value>
????</parameter>
??</ResourceParams>
</Context>
-----------------------------------------------------------------------
用DBPool.java獲得數(shù)據(jù)庫連接池
package com.binbo.dbo;
import java.sql.Connection;
import java.sql.DriverManager;
import javax.naming.Context;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class DBPool {
?public static synchronized Connection getConnection()throws Exception{
??DataSource ds = null;
??try{
??Context ininCtx??= new javax.naming.InitialContext();
??
??Context envCtx = (Context)ininCtx.lookup("java:comp/env");
????ds = (DataSource)envCtx.lookup("jdbc/myjsp");
??}catch(NamingException e){
???e.printStackTrace();???
??}??
??Connection conn = ds.getConnection();
??return conn;??
?}
}
-----------------------------------------------------------
操作數(shù)據(jù)庫
package com.binbo.dbo;
import java.sql.*;
import com.binbo.javabean.BreakBean;
import com.binbo.javabean.OpenBean;
public class DataBaseClass {
?private Statement sta = null;
?private ResultSet rs = null;
?Connection conn = null;
?private int count;
?public DataBaseClass() throws Exception {
??// 取得數(shù)據(jù)庫的連接
??conn = DBPool.getConnection();
??sta = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
????ResultSet.CONCUR_UPDATABLE);
?}
?public void getExecute(String sql) {
??try {
???System.out.println(sql);
???sta.executeUpdate(sql);
??} catch (SQLException e) {
???e.printStackTrace();
??}
?}

?
?public ResultSet getQuery(String sql) {
??try {
???System.out.println(sql);
???rs = sta.executeQuery(sql);
??} catch (SQLException e) {
???e.printStackTrace();
??}
??return rs;
?}

?
??* 取得結(jié)果集的行數(shù)
?
?public int getCount(ResultSet rs2) {
??try {
???rs2.next();
???count = rs2.getRow();
??} catch (SQLException e) {
???e.printStackTrace();
??}
??return count;
?}
??//添加！?
?public void getRegister(OpenBean open) {
??String sql = "insert into f_info(nam,email,titl,content,tem,mid)values(?,?,?,?,?,?)";
??try {
???PreparedStatement ps = conn.prepareStatement(sql);
???ps.setString(1, open.getNam());
???ps.setString(2, open.getEmail());
???ps.setString(3, open.getTitl());
???ps.setString(4, open.getContent());
???ps.setString(5, open.getTem());
???ps.setString(6, open.getDepa());
???ps.execute();
??} catch (SQLException e) {
???e.printStackTrace();
??}
?}
????????// 刪除
?public void getRealys(OpenBean real) {
??try {
???String sql = "delete from departments where id=?";
???PreparedStatement ppt = conn.prepareStatement(sql);
???ppt.setString(1, real.getUnam());
???ppt.execute();

??} catch (SQLException e) {
???e.printStackTrace();
??}

?}

?// 查詢
?public void getRealy(OpenBean hg) {

??try {
???String sql = "select * from departments where id=?";
???PreparedStatement ppg = conn.prepareStatement(sql);
???ppg.setString(1, hg.getUnam());
???ppg.execute();
??} catch (SQLException e) {
???e.printStackTrace();
??}
?}
}
============================hibernate操作數(shù)據(jù)庫====================================
package com.binbo.hibernate.xml;
import java.util.Iterator;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
public class PersonOperate {
?private Session session = null ;
?public PersonOperate()
?{
??Configuration config = new Configuration().configure() ;
??SessionFactory factory = config.buildSessionFactory() ;
??this.session = factory.openSession() ;
?}?
?// 增加數(shù)據(jù)
?public void insert(Person p)
?{
??Transaction tran = this.session.beginTransaction() ;
??this.session.save(p) ;
??tran.commit() ;
??this.session.close() ;
?}
------------------------------------------
增加數(shù)據(jù)調(diào)用
????????????????LinOperate po = new LinOperate();
????????????????Lin p = new Lin();
??p.setLname(lname);
??p.setLmonery(lmonery);
??p.setLmain(lmain);
??p.setLtime(ltime);
??LinOperate po = new LinOperate();
??po.insert(p);
??errors.add("success", new ActionMessage("xiangxi"));
??request.setAttribute("org.apache.struts.action.ERROR", errors);
??return mapping.findForward("jinru");
------------------------------------------
?
?// 修改
?public void update(Person p)
?{
??Transaction tran = this.session.beginTransaction() ;
??this.session.update(p) ;
??tran.commit() ;
??this.session.close() ;
?}?
?// 用戶登錄
?public boolean queryById(Person person)
?{
??boolean flag = false;
??String hql = "from Person as p where p.id=? and p.password=?" ;
??Query q = this.session.createQuery(hql) ;
??q.setString(0,person.getId()) ;
??q.setString(1,person.getPassword());
??Iterator iter = q.list().iterator();
??if (iter.hasNext()) {
???flag = true;
???person.setName(((Person) iter.next()).getName());
???
??}
??this.session.close() ;
??return flag;
?}
?//驗證用戶存不存在
?public boolean queryC(Person person)
?{
??boolean flag = false;
??String hql = "from Person as p where p.name=?" ;
??Query q = this.session.createQuery(hql) ;
??q.setString(0,person.getName()) ;
??Iterator iter = q.list().iterator();
??if (iter.hasNext()) {
???flag = true;???
??}
??this.session.close() ;
??return flag;
?}
?//查看個人資料
?public Person queryZliao(String id)
?{
??Person p = null ;
??String hql = "from Person as p where p.id=?" ;
??Query q = this.session.createQuery(hql) ;
??q.setString(0,id) ;
??List l = q.list() ;
??Iterator iter = l.iterator() ;
??if(iter.hasNext())
??{
???p = (Person)iter.next() ;
??}
??return p ;
?}?
?// 刪除數(shù)據(jù)
?public void delete(Person p)
?{
??Transaction tran = this.session.beginTransaction() ;
??this.session.delete(p) ;
??tran.commit() ;
?}?
?// 修改
?public void delete(String name)
?{
??String hql = "delete Person where name=?" ;
??Query q = this.session.createQuery(hql) ;
??q.setString(0,name) ;
??q.executeUpdate() ;
??this.session.beginTransaction().commit() ;
?}
?
?// 查詢?nèi)繑?shù)據(jù)
?public List queryAll()
?{
??List l = null ;
??String hql = "from Person as p" ;
??Query q = this.session.createQuery(hql) ;
??l = q.list() ;
??return l ;
?}
-------------------------------
(
查詢?nèi)繑?shù)據(jù)調(diào)用
????????????????LinOperate po = new LinOperate();
??List l = po.queryAll();
??Iterator iter = l.iterator();
??ArrayList lus = new ArrayList();
??while (iter.hasNext()) {
???Lin p = (Lin) iter.next();
???Luser lu = new Luser();
???lu.setName(p.getLname());
???lu.setChange(p.getLmonery());
???lu.setMainn(p.getLmain());
???lu.setLtime(p.getLtime());
???lus.add(lu);
???System.out.print(p.getLname() + " ");
???request.setAttribute("lus", lus);

)
------------------------?
?// 模糊查詢
?public List queryByLike(String cond)
?{
??List l = null ;
??String hql = "from Person as p where p.name like ?" ;
??Query q = this.session.createQuery(hql) ;
??q.setString(0,"%"+cond+"%") ;
??l = q.list() ;
??return l ;
?}
}

來源：http://blog.csdn.net/dongdekun369/article/details/50588551

轉(zhuǎn)載于:https://www.cnblogs.com/wanghongyun/p/6337143.html

總結(jié)

以上是生活随笔為你收集整理的dos命令行连接操作ORACLE数据库的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。