oracle學習筆錄

set serveroutput on  開啟伺服器螢幕顯示資訊
set sqlblanklines on  編寫SQL語句時，開啟忽略空格功能

save   c:/sqlplus_test01.txt    --儲存
edit    --開啟記事本進行編輯
get c:/sqlplus_test01.txt    --把記事本中的資訊調入到緩衝中
list    --顯示緩衝中的資訊
desc deptment   --顯示部門表中的表定義]構
l2 4   --顯示第二行到第四行的資訊
change  --修改緩衝中的內容
c/N/M  --把N改成M
del  2 5  --刪除2到5行的資訊
help  index   --協助
? set   --以set開頭的關鍵字協助資訊
col  ID format A10  heading '部門編號'     --把欄位名ID顯示為部門編號，字元類型，長度為10
col bytes format 999,999,999     --把欄位名bytes顯示為指定的格式

 

***************************************************************
set linesize 50
ttitle center "我的標題"  skip 1-
left "測試報表" right  "頁" -
format 999 sql.pno skip 2
select * from dept;
   
--輸出結果
                                 我的標題
測試報表                                                            頁  1
 
   deptno           dname             log
-------------        ---------------       -----------
      10               accounting       new york
      20               research           dalias
      30               sales                 chicago
      40               operations        boston

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

ttitle off  --關閉標題
select * from dept;

--輸出結果
 
   deptno           dname             log
-------------        ---------------       -----------
      10               accounting       new york
      20               research           dalias
      30               sales                 chicago
      40               operations        boston

***************************************************************
select * from deptment;

--輸出結果
id             name
-------      -----------
01          A部門
02          A部門
03          B部門
04          B部門
05          C部門

break on name    --把name欄位去掉重複值

select * from deptment;

--輸出結果
id             name
-------      -----------
01          A部門
02        
03          B部門
04         
05          C部門

comp count label "計數" of id on name   --對分組欄位進行匯總
select * from deptment;

--輸出結果

id                 name
------------     ---------------
01                A部門
02                A部門
------------     *************
            2     計數
03               B部門
04               B部門 
------------     *************
            2      計數
05               C部門
------------     *************      
            1      計數

 

***************************************************************
DDL    資料定義語言 (Data Definition Language)
    1.create 
 create table abc(a varchar2(10),b char(10));
    2.alter
 alter table abc add c number;
  alter table abc drop column c;
    3.drop
DCL   資料控制語言
     1.grant 
 grant select on dept to tt;
 conn tt/tt11
 select * from scott.dept;
     2.revoke
 revoke select  on dept from tt;
 conn  tt/tt11
 select * from scott.dept
DML  資料操作語言
      1.select
 select * from abc;
      2.insert
 insert into abc(a,b) values('abc','xy');
 insert into abc values('bcd','123');
      3.delete
 delete from abc where a = 'abc';
      4.update
 update abc set b='ttt' where a='abc';

***************************************************************
                                                    常用系統函數
1、字元
 lenth,ltrim,replace,rtrim,substr,trim

2、日期
 sysdate,current_date,next_day
3、轉換
 to_char,to_date,to_number
4、聚集合函式
 sum,avg,max,min,count
5、其他
 user,decode,nvl

select length('abcdef') from dual;

--輸出結果

length('abcded')
---------------------
                         6

select length('abc好ef') from dual;

--輸出結果

length('abcded')
---------------------
                         6

select lengthb('abc好ef') from dual;

--輸出結果

lengthb('abcded')
---------------------
                         7

select ltrim('  abc好ef') from dual;

--輸出結果

ltrim('
-----------
abc好ef

select rtrim('   abc好ef   ') from dual;

--輸出結果

rtrim('ab
----------
   abc好ef

select trim('   abc好ef   ') from dual;

--輸出結果

trim('a
---------
abc好ef

select substr('abcdefg',2,3) from dual;

--輸出結果
sub
-----
bcd

select sysdate from dual;

--輸出結果

sysdate
---------------
21-6月 -10

select current_date from dual;

--輸出結果

current_date
---------------
21-6月 -10

alter session set nls_date_format='dd-mon-yyyy hh:mi:ss';
select current_date from dual;

--輸出結果

current_date
-------------------
21-6月 -2010  03：44：22

select next_day(sysdate,'星期三') from dual;

--輸出結果

next_day(sysdate,'星期三')
-------------------------------------
23-6月 -2010  03:47:03

select to_char(systdate,'yyyy-mm-dd hh:mi:ss') from dual;

--輸出結果

to_char(sysdate,'yy
---------------------------
2010-06-21  03:54:08

select to_date('12-3月-10')  from dual;

--輸出結果

to_date('12-3月-10)
---------------------------
12-3月 -0010 12:00:00

select to_number('00333')  from dual;

--輸出結果

to_number('00333')
---------------------------
                             333

select user from dual;

--輸出結果

user
--------------------------------------------
li

select * from e;

--輸出結果

eid             ename          sex
----------      ----------------  ------------
001 趙1      男       
002 錢2      男 
003 孫3      男 
003 李4      女 
003 周5      女

select sum(decode(sex,'男',1,0))  男人數  ,sum(decode(sex,'女',1,0)  女人數  from e;

--輸出結果

男人數               女人數
------------           -----------
              3                       2

select  *  from  aa;

--輸出結果

a1               a2                a3
-------------   ---------------  -----------------
abc xyz     aa
bca dee     aa
abc ttt                   aa
cba                       aa
bbb                       aa
abc ggd               aa
abc dfd                aa

select a1,nvl(a2,'未輸入') from aa;

--輸出結果

a1               a2                a3
-------------   ---------------  -----------------
abc xyz     aa
bca dee     aa
abc ttt                   aa
cba 未輸入          aa
bbb 未輸入          aa
abc ggd               aa
abc dfd                aa

select * from aa where a2 is null;

--輸出結果

a1               a2                a3
-------------   ---------------  -----------------
cba                       aa
bbb                       aa

 

 

 

 

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

--遊標的屬性
 %FOUND
 %ISOPEN
 %NOTFOUND
 %ROWCOUNT

--遊標與記錄集
*****************************************************************************
declare
cursor mycur is
select * from book;
myrecord books%rowtype;  --定義記錄集與books表類型大小一樣
begin
open mycur;
fetch mycur into myrecord;
while mycur%fount loop
dbms_output.put_line(myrecord.books_id||','||myrecord.books_name);
fetch mycur into myrecord;
end loop;
close mycur;
end;
/
--輸出結果：
0001,中國文學
0002,外國文學
0003,英語閱讀
0004,建築藝術
0005,電腦入門
0006,數值演算法
0007,C語言
*****************************************************************************

--帶參數的遊標
*****************************************************************************
declare
cursor cur_para(id varchar2) is
select books_name from books where book_id=id;
t_name  books.books_name%type;
begin
open cur_para('0001');
loop
fetch cur_para into t_name;
exit when cur_para%notfound;
dbms_output.put_line(t_name);
end loop;
close cur_para;
end;
/

--輸出結果：
中國文學

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

--利用遊標修改表資料
*****************************************************************************
declare
cursor cur is
select name from deptment for update;
text  varchar2(10);
begin
open cur;
fetch cur into text;
while  cur%found loop
update deptment set name=name||'_t' where current of cur;
fetch cur into text;
end loop;
close cur;
end;
/

--輸出結果：
id             name
-------      -----------
01          A部門_t
02          B部門_t
03          C部門_t
04          D部門_t
05          E部門_t

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

--遊標在for迴圈中不用開啟和關閉
*****************************************************************************
begin
for cur in(select name from deptment) loop
dbms_output.put_line(cur.name);
end loop;
end;
/

--輸出結果
A部門_t
B部門_t
C部門_t
D部門_t
E部門_t

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