------------------------------------Oracle 常用命令------------------------------------
-------------一般命令----------------
1、清屏
clear screen ;
2、導入sql腳本
@d:/sqlCreate.sql
3、設置回滾點(diǎn):
savepoint aa;
4、回滾數據:
rollback to aa;
5、提交事務(wù)：
commit;
6、設置只讀事務(wù)： --設置只讀后，之后其他用戶(hù)加入的數據不會(huì )影響到本用戶(hù)的查詢(xún)結果。
set transaction read only;
6、修改數據庫的默認時(shí)間類(lèi)型（默認為："dd-mm-yyyy"）：
alter session set nls_date_format='yyyy-mm-dd';
7、打開(kāi)操作時(shí)間開(kāi)關(guān)：
set timing on ;
8、控制臺中輸入語(yǔ)句：
dbms_output.put_line('雇員名：'||v_ename); --dbms_output ：是包名
9、聲明一個(gè)變量
declare v_ename varchar2 ;
10、給變量賦值
v_ename:='賦值' ;
-----------用戶(hù)管理------------
1、創(chuàng )建一個(gè)用戶(hù)：
create user zou identified by mima;
2、修改用戶(hù)的密碼：
password newMima;
3、連接一個(gè)新用戶(hù)：
conn sys/accpsys as sysdba;
4、刪除用戶(hù):
drop user zou;
5、查詢(xún)所有用戶(hù)：
select * from dba_users; desc dba_users;
6、給用戶(hù)賦角色權限：
grant connect to zou ;
7、給某個(gè)用戶(hù)授予某張表的訪(fǎng)問(wèn)權限：
grant select on emp to zou ;
或者
grant add on emp to zou;
8、同時(shí)允許該用戶(hù)繼續授權給其他用戶(hù)方式：
grant add on emp to zou with grant option;
9、回收權限：
revoke select on emp from zou;
10、查詢(xún)用戶(hù)具有權限：
select * from dba_tab_privs ;
11、查詢(xún)用戶(hù)具有的角色:
select * from dba_role_privs ;
12、查詢(xún)Oracle中所有角色：
select * from dba_roles ;
-----------權限表------------
connect：一般開(kāi)發(fā)人員就足夠了
alter session
create cluster
create database link
create sequence
create session
create table
create view
resource ：具有應用開(kāi)發(fā)人員所需要的其他權限，比如存儲過(guò)程、觸發(fā)器等。
create cluster
create indextype
create table
create sequence
create type
create procedure
create trigger。
dba角色：具有所有的系統權限，不具有啟動(dòng)和關(guān)閉數據庫的權限。
-------------表的基本操作----------------
1、新建一張表：
create table users
(
userId int not null primary key,
userName varchar2(20),
birthday date
);
----添
2、添加一條數據方法：
insert into users(1,'aaa',to_date('2009-10-13','yyyy-mm-dd'));
3、一次性插入多條數據：
insert into kkk(myid,myname,mydept) select empno,ename,deptno from emp where deptno=10
----修
3、修改一條數據：
update users set userName='ccc' where userId=1;
4、一次性修改多條數據：
update emp set(job,sal,comm)==(select job,sal,comm from emp where ename='smtth') where ename='scott' ;
----查
4、查詢(xún)一條數據：
select * from users;
5、查詢(xún)某列是空值的方式：
select * from student where birthday is null;
6、查詢(xún)表結構：
desc users;
7、多表連接查詢(xún)：
select a1.ename,a1.sal,s2.dname from emp a1,poed a2 where a1.ename=a2.pname ;
8、用查詢(xún)結果建立一張新表：
create table mytable(id,name,sal,job,deptno) as select empno,ename,sal from emp ;
9、查詢(xún)當前用戶(hù)可以訪(fǎng)問(wèn)的所有表:
select * from all_tables; --all_tables :能訪(fǎng)問(wèn)到的表 。user_tables ：查詢(xún)該用戶(hù)的所有表。
--dba_tables :查詢(xún)所有解決方案的表。
9、表的聯(lián)合查詢(xún):
select ~~~~~ union
select ~~~~~
--關(guān)鍵字：union ：兩張表中的數據，并保證無(wú)重復數據。
-- union all ：不保證無(wú)重復數據。
-- intersect ：取出兩張表中公共部分。
-- minus a表中已涵蓋了b表，取出來(lái)的就是a表減b表的數據。
8、子查詢(xún)語(yǔ)句：
select * from emp where sal in(select sal from emp where sal=30) ; --in 是指所有的意思，=指任何一個(gè)
或者
select * from emp where (deptno,job)=(select deptno,job from emp where lie=30) ;
9、Oracle的分頁(yè)寫(xiě)法：
select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=10) where rn>=6 ;
----刪
7、刪除表中的數據：
delete from users;
8、刪除表結構，即，表。
drop table users;
或者
truncate table student; --此語(yǔ)句更快
----修改列、及按照固定的格式顯示數據。
9、按照固定的時(shí)間格式顯示時(shí)間：
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
10、薪水顯示方式：
to_char(sal,'L99.999.99')
11、添加一個(gè)字段：
alter table student add(classId number(2));
12、刪除字段（慎用）
alter table student drop column sal;
13、修改表字段的長(cháng)度、類(lèi)型：
alter table student modify(xm varchar2(30));
14、給列起別名：
select sal*12 '年薪' from emp ;
-------------Oracle存儲過(guò)程、視圖----------------
1、創(chuàng )建存儲過(guò)程
create or replace procedure sp_proc1
is
begin
insert into my tes values('zou','aaa') ;
end ;
/
-----顯示錯誤信息
show error ;
2、調用存儲過(guò)程：
exec 過(guò)程名(參數值1，參數值2...)
或者
call 過(guò)程名(參數值1,參數值2...)
3、帶輸出、輸入參數的存儲過(guò)程：
create or replace procedure sp_pro(spNo in number,spName out varchar2,spSal out varchar2)
is
begin
select ename,sal into spName,spSal from emp where empno=spno;
end;
/
在Java中調用：
CallableStatement cs=ct.prepareCall("{call sp_pro(?,?,?)}") ;
cs.setInt(1,7788) ;
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(3,oracle.jdbc.OracleTypes.VARCHAR);
cs.execute();
String name=cs.getString(2);
String sal=cs.getString(3);
4、返回集合列表：
1)、創(chuàng )建一個(gè)包
create or replace package testpackage as
type test_cursor is ref cursor ;
end testpackage ;
2)、創(chuàng )建存儲過(guò)程：
create or replace procedure sp_pro(spNo in numbar,p_cursor out tespackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno=spNo ;
end ;
/
3)、Java中讀取集合：
......
cs.execute();
ResultSet rs=(ResultSet)cs.getObject(2);
While(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
--關(guān)閉連接
....
5、分頁(yè)存儲過(guò)程：
--開(kāi)發(fā)一個(gè)包
create or replace package testpackage AS
type test_cursor is ref cursor;
end testpackage;
--存儲過(guò)程
create or replace procedure fenye
(tableName in varchar2,
Pagesize in number, --一頁(yè)顯示數量
pageNo in number, --頁(yè)碼
myrows out number, --總記錄數
myPageCount out number,--總頁(yè)數
P_cursor out tespackage.test_cursor --返回的記錄集
) is
v_sql varchar2(1000) ；
v_begin number:=(pageNow-1)*Pagesize+1;
v_end number:=pageNow*Pagesize;
begin
--執行部分
v_sql:= ' select * from (select t1.* rownum rn from (select * from '|| tableName | |') t1 where rownum<='|| v_end ||') where rn>='|| begin ；
--把游標和sql語(yǔ)句關(guān)聯(lián)起來(lái)
open p_cursor for v_sql;
--要計算myrows和myPageCount
--組織一個(gè)sql語(yǔ)句
v_sql:=select count(*) from '|| tableName;
--執行sql，并把返回的值，賦給myrows
execute immediate v_sql into myrows;
--計算my
if mod(myrows,Pagesize)=0 then
myPageCount:=myrows/Pagesize;
else
myPageCount:=myrows/Pagesize+1;
end if ;
--關(guān)閉游標
close p_cursor;
end ;
/
6、創(chuàng )建Oracle視圖
create or replace view myView as select * from emp where no<1000
-------------Oracle的異常處理----------------
1、異常的定義：
1)、case_not_found ：
2)、cursor_already_open ：游標已經(jīng)打開(kāi)，再次打開(kāi)時(shí)則會(huì )拋出異常。
3)、dup_val_on_index ：在唯一索引添加重復值時(shí)，拋出的異常。
4)、invaild_cursor ：當試圖在不合法的游標上執行操作時(shí)，會(huì )觸發(fā)該例外。
5)、invalid_number ：比如應該輸入數字輸入的是字符串。
6)、too_many_rows ：返回多行時(shí)，用一個(gè)接收就會(huì )拋出該異常。
7)、zero_divide ：當執行2/0語(yǔ)句時(shí)，則會(huì )出觸發(fā)異常。
8)、value_error ：當得到的數據比變量大，則會(huì )拋出該異常。
9)、login_denide ：用戶(hù)登陸異常則是該異常。
10)、not_logged_on ：如果用戶(hù)沒(méi)登陸就執行dml操作，就會(huì )觸發(fā)。
11)、storage_error ：如果超出了內存空間或是內存被損壞，就觸發(fā)該例外。
12)、timeout_on_resource ：如果oracle在等待資源時(shí)，出現了超時(shí)就觸發(fā)該例外。
create or replace procedure sp_pro6(spno number) is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=spno;
case
when v_sal<1000 then
update emp set sal=sal+100 where empno=spno;
when v_sal<2000 then
update emp set sal=sal+200 where empno=spno
end case;
exception
when case_not_found then
dbms_output.put_line('case語(yǔ)句沒(méi)有與'|| v_sal ||'相匹配的條件');
end ;
/
-------------數據備份操作操作----------------
----前提條件在Oracle的主目錄下找到/bin/exp.exe，并運行
1、導出自己的表：
exp userid=system/accpsystem@accp tables=(users,table2) file=d:/users.dmp
2、導出其他用戶(hù)的表：
exp userid=system/accpsystem@accp tables=(scott.emp) file=d:/emp.dmp
3、導出表結構：
exp userid=system/accpsystem@accp tables=(users) file=d:/users.dmp rows=n
4、直接導出方式：
exp userid=scott/accpscott@accp tables=(emp) file=d:/emp.dmp direct=y
5、導出自己的方案：
exp scott/accpscott@accp owner=scott file=d:/scott.dmp
6、導出數據庫：
exp userid=system/accpsystem@accp full=y inctype=complete file=d:/accp.dmp
7、導入自己的表：
imp userid=scott/accpscott@accp tables=(users,emp) file=d:/users.dmp
----導入時(shí)該表不能有主外鍵關(guān)系。
8、導入表到其他用戶(hù)內：
imp userid=scott/accpscott@accp tables=(emp) file=d:/emp.dmp touser=system
9、導入表結構(導入表結構而不導入數據)：
imp userid=scott/accpscott@accp tables=(emp) file=d:/emp.dmp rows=n
10、導入表結構(導入數據而不導入表結構)：
imp userid=scott/accpscott@accp tables=(emp) file=d:/emp.dmp ignore=y
11、導入方案：
imp userid=scott/accpscott file=d:/scoot.dmp
12、導入其他解決方案（必須具有dba的權限）：
imp userid=system/accpsystem file=d:/system.dmp fromuser=system touser=scott
13、導入數據庫：
imp userid=system/accpsystem full=y file=d:/accp.dmp
-----------------表空間的管理-----------------
1、創(chuàng )建表空間：
create tablespace spoo1 datafile 'd:/test/data01.dbf' size 20m uniform size 128k;
2、使用表空間：
create table myPart(deptno number(4),dname varchar2(10),loc varchar2(13)) tablespace sp001 ;
3、使表空間脫機：
alter tablespace 表空間名 offline;
4、使表空間聯(lián)機：
alter tablespace 表空間名 online ;
5、只讀表空間：
alter tablespace 表空間名 read only ;
6、可讀寫(xiě)表空間：
alter tablespace 表空間名 read write;
7、查詢(xún)表空間中的表：
select * from all_tables where tablespace_name='表空間名' ;
8、知道表名，查詢(xún)所屬表空間：
select tablespace_name,table_name from user_tables where table_name='emp' ;
9、刪除表空間：
drop tablespace '表空間' including contents and datafiles ;
10、增加數據文件：
alter tablespace sp001 add datafile 'd:/test/sp001.dbf' size 20m
11、增加數據文件的大?。?br> alter tablespace sp001 'd:/test/sp001.dbf' resize 200m ;
12、設置文件的自動(dòng)增長(cháng)：
alter tablespace sp001 'd:/test/sp001.dbf' autoextend on next 10m maxsize 500m ;
----故障處理，將表移動(dòng)到其他表空間：
1)、確定數據文件所在的表空間：
select tablespace_name from dba_data_files where file_name='d:/sp001.dbf' ;
2)、使表空間脫機：
alter tablespace sp001 offline ;
3)、使用命令移動(dòng)數據文件到指定的目標位置：
host move d:/sp001.dbf c:/sp001.dbf ;
4）、執行alter tablespace 命令：
alter tablespace sp001 rename datafile 'd:/sp001.dbf' to 'c:/sp001.dbf' ;
5)、使表空間聯(lián)機：
alter tablespace sp001 online ;
6)、顯示表空間信息：
select tablespace_name from dba_tablespaces ;
7、顯示表空間所有包含的數據文件：
select file_name,bytes from dba_data_files where tablespace_name='表空間名' ;
-------------Oracle基本類(lèi)型----------------
number（5,2） ：表示一個(gè)小數有5位有效數，2位小數范圍：-999,99-999,99.
number(5) ：表示一個(gè)5位的整數 -99999-99999
number范圍 -10的38次方-10的38次方可以表示整數，也可以表示小數。
char(10) ：字符型，使用它比varchar的讀取效益要強很多，常用屬性要是用char類(lèi)型，最大2000.
varchar ：最大4000
clob ： 字符型的大對象4G。
date ：精確到秒鐘。比較常用。
timestamp ：精度很高，精確到毫秒。
blob ：二進(jìn)制數據，可以存放圖片/聲音 4G。（很少使用，如果安全性很高的話(huà)可以使用）。
-------------Oracle約束、角色----------------
1、添加約束：
alter table customer add constraint card_uniquer unique(cardId) ;
2、刪除約束：
alter table customer drop constraint 約束名 ;
3、刪除主鍵：
alter table 表名 drop primary key cascade ;
4、查詢(xún)約束信息：
select * from user_constraints where table_name='約束名' ;
5、創(chuàng )建索引：
create index on '索引名' on '表名'(列名,列名....) ;
-------------Oracle的內置函數----------------
lower() ：將字符串轉換成小寫(xiě)。
upper() ：轉換大寫(xiě)。
length(char) ：返回字符的長(cháng)達。
substr(char,m,n) ：截取字符串。
--第一個(gè)參數列名，第二參數是從幾個(gè)開(kāi)始取，第三個(gè)參數是取幾個(gè)。
--示例：select upper(substr(ename,1,1)) || lower(substr(ename,2, length(ename)-1)) from emp;
replace(char1,search_string,replace_string) ：替換方法。
instr(char1,char2,[,n[,m]]) ：查找位置。
round(n,[m]) ：該函數用于執行四舍五入，如果省掉m，則四舍五入到整數；如果m是正數，則四舍五入到小數點(diǎn)的m位后，
如果m是負數，則四舍五入到小數點(diǎn)的m位前。
trunc(n,[m]) ：該函數用于截取數字，如果省掉m，就截去小數部分，如果m是正數就截取
到小數點(diǎn)的m位后，如果m是負數，則截取到小數點(diǎn)的前m位。
mod(m,n) ： 取余數。例：select mod(elieming,2) from emp;
floor(n) ：返回小于或是等于n的最大整數。
ceil(n) ：返回大于或是等于n的最小整數。
abs(n) ：返回數字n的絕對值。
acos(n) ：返回數字的反余弦值。
asin(n) ：返回數字的反正旋值。
atan(n) ：返回數字的反正切。
cos(n) ：
exp(n) ：返回e的n次冪。
log（m,n） ：返回對數值。
power(m,n) ：返回m的n次冪。
----日期函數：
sysdate ：獲取當前時(shí)間。
ADD_MONTHS(日期,月數) 返回日期
MONTHS_BETWEEN(日期，日期) 返回月數
LAST_DAY(日期) 當前日期月份最后一天 如倒數第幾天，可以使用減。
ROUND(日期，模式) 當前日期舍入日期 如round(sysdate,’year’)
NEXT_DAY(日期，星期) 如next_day(sysdate,'星期二’)
TRUNC(日期,模式) 截斷日期
EXTRACT(模式 from 日期) 提取時(shí)間部分
Oracle可以進(jìn)行隱形轉換數據類(lèi)型。例：
create table t1(id int)；
insert into t1 values('10') —>這樣oracle會(huì )自動(dòng)的將’10’轉換成10。
日期顯示時(shí)/分/秒。例：
yy ：兩位數字的年份2004—>04。
yyyy ：四位數字的年份 2004年。
mm ：兩位數字的月份8月—>08
dd ：2位數字的天30號—>30
hh24 ：8點(diǎn)—>20
hh12 ：8點(diǎn)—>08
mi、ss —>顯示分鐘/秒
select ename,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’) from emp;
聯(lián)系客服
