oracle+plsql語句大全.doc

plsql語句的編程手冊 SQL PLUS一、SQL PLUS 1 引言 SQL命令 以下17個是作為語句開頭的關(guān)鍵字： alter drop revoke audit grant rollback* commit* insert select comment lock update create noaudit validate delete rename 這些命令必須以“;”結(jié)尾 帶*命令句尾不必加分號，并且不存入SQL緩存區(qū)。 SQL中沒有的SQL*PLUS命令 這些命令不存入SQL緩存區(qū) define pause # del quit $ describe remark / disconnect run accept document save append edit set break exit show btitle get spool change help sqlplus clear host start column input timing compute list ttitle connect newpage undefine copy - 2 數(shù)據(jù)庫查詢 數(shù)據(jù)字典 TAB 用戶創(chuàng)建的所有基表、視圖和同義詞清單 DTAB 構(gòu)成數(shù)據(jù)字典的所有表 COL 用戶創(chuàng)建的基表的所有列定義的清單 CATALOG 用戶可存取的所有基表清單 select * from tab; describe命令 描述基表的結(jié)構(gòu)信息 describe dept select * from emp; select empno,ename,job from emp; select * from dept order by deptno desc; 邏輯運算符 = !=或 = .05*sal order by comm/sal desc; 日期型數(shù)據(jù)的運算 add two days to 6-Mar-87 6-Mar-87 + 2 = 8-Mar-87 add two hours to 6-Mar-87 6-Mar-87 + 2/24 = 6-Mar-87 and 2hrs add 15 seconds to 6-Mar-87 6-Mar-87 + 15/(24*60*60) = 6-Mar-87 and 15 secs 列名的別名 select ename employee from emp where deptno=10; （別名：employee） select ename,sal,comm,comm/sal C/S RATIO from emp where comm.05*sal order by comm/sal desc; SQL命令的編輯 list or l 顯示緩沖區(qū)的內(nèi)容 list 4 顯示當(dāng)前SQL命令的第4行，并把第4行作為當(dāng)前行，在該行號后面有個*。 change or c 用新的內(nèi)容替換原來在一行中第一次出現(xiàn)內(nèi)容 SQLc/(.)/(analyst)/ input or i 增加一行或多行 append or a 在一行后追加內(nèi)容 del 刪除當(dāng)前行 刪除SQL緩沖區(qū)中的當(dāng)前行 run 顯示并運行SQL緩沖區(qū)中的命令 / 運行SQL緩沖區(qū)中的命令 edit 把SQL緩沖區(qū)中的命令寫到操作系統(tǒng)下的文本文件， 并調(diào)用操作系統(tǒng)提供的編輯器執(zhí)行修改。 - 3 數(shù)據(jù)操縱 數(shù)據(jù)的插入 insert into dept values (10,accounting,new york); insert into dept (dname,deptno) values (accounting,10); 從其它表中選擇插入數(shù)據(jù) insert into emp (empno,ename,deptno) select id,name,department from old_emp where department in(10,20,30,40); 使用參數(shù) insert into dept values(&eptno,&name,&loc); 執(zhí)行時，SQL/PLUS對每個參數(shù)將有提示用戶輸入 參數(shù)對應(yīng)日期型或字符型數(shù)據(jù)時，可在參數(shù)上加引號，輸入時就可不用引號 insert into dept values(&eptno,&dname,&loc); 插入空值（NULL） insert into dept values(50,education,null); 插入日期型數(shù)據(jù) 日期型數(shù)據(jù)缺省格式：DD-MON-YY insert into emp (empno,ename,hiredate) values(7963,stone,07-APR-87); 系統(tǒng)時間：SYSDATE insert into emp (empno,ename,hiredate) values(7600,kohn,SYSDATE); 數(shù)據(jù)更新 update emp set job=manager where ename=martin; update emp set job=market rep where ename=salesman; update emp set deptno=40,job=market rep where job=salesman; 數(shù)據(jù)刪除 delete emp where empno=765; 更新的提交 commit 自動提交方式 set autocommit on 如果狀態(tài)設(shè)為開，則使用inesrt,update,delete會立即提交。 更新取消 rollback 兩次連續(xù)成功的commit之間的操作，稱為一個事務(wù) - 4 創(chuàng)建基表、視圖 創(chuàng)建基表 create table dept (deptno number(2), dname char(14), loc char(13); 數(shù)據(jù)字典會自動更新。 一個基表最多254列。 表名列名命名規(guī)則： 限制 第一個字符必須是字母，后面可任意（包括 $ # _ 但不能是逗號）。 名字不得超過30個字符。 唯一 某一用戶的基表名必須唯一，不能是ORACLE的保留字，同一基表的列名互不相同。 使用雙引號 如果表名用雙引號括起來，則可不滿足上述規(guī)則； 只有使用雙引號，才能區(qū)別大、小寫； 命名時使用了雙引號，在以后的操作也必須使用雙引號。 數(shù)據(jù)類型： char(n) （不得超過240字符） number(n,d) date long （最多65536字符） raw （二進(jìn)制原始數(shù)據(jù)） 空值處理 有時要求列值不能為空 create table dept (deptno number(2) not null, dname char(14), loc char(13); 在基表中增加一列 alter table dept add (headcnt number(3); 修改已有列屬性 alter table dept modify dname char(20); 注：只有當(dāng)某列所有值都為空時，才能減小其列值寬度。 只有當(dāng)某列所有值都為空時，才能改變其列值類型。 只有當(dāng)某列所有值都為不空時，才能定義該列為not null。 例： alter table dept modify (loc char(12); alter table dept modify loc char(12); alter table dept modify (dname char(13),loc char(12); 創(chuàng)建視圖 create view managers as select ename,job,sal from emp where job=manager; 為視圖列名取別名 create view mydept (person,title,salary) as select ename,job,sal from emp where deptno=10; with check option選項 使用with check option，保證當(dāng)對視圖插入或更新數(shù)據(jù)時， 該數(shù)據(jù)必須滿足視圖定義中select命令所指定的條件。 create view dept20 as select ename,job,sal,deptno from emp where deptno=20 with check option; 在做下述操作時，會發(fā)生錯誤 update dept20 set deptno=30 where ename=ward; 基表、視圖的拷貝 create table emp2 as select * from emp; 基表、視圖的刪除 drop table 表名 drop view 視圖名 - 5 SQL*PLUS報表功能 SQL*PLUS的一些基本格式命令 column deptno heading department column ename heading name column sal heading salary column sal format $99,999.00 ttitle sample report for|hitech corp btitle strictly confidential break on deptno compute sum of sal on deptno run 表頭和表尾 ttitle sample report for|hitech corp btitle right strictly confidential “|”表示換行，結(jié)尾不必加分號 選項有三種：left right center 使用TTITLE，系統(tǒng)將自動地在每頁的頂部顯示日期和頁號。 TTITLET和BTITLE命令有效，直至重新設(shè)置表頭或表尾，或退出SQL*PLUS。 下面命令使標(biāo)題語句失效 TTITLE OFF BTITLE OFF 列名 column命令定義用于顯示列名 若名字為一個單詞，不必加引號 column ename heading employee column ename heading employee|name （|為換行） 取消欄定義 column ename clear 列的格式 column ename format A15 column sal format $9,999.99 column comm like sal like子句，使得某一列的格式參照另一列格式，它拷貝列名及其格式 控制記錄顯示分組順序 break on deptno （不顯示重復(fù)值） select deptno,ename from emp order by deptno; （ORDER BY子句用于控制BREAK） 顯示為 10 clark niller 20 smith scott 30 allen blake 每次只有一個BREAK命令起作用，但一次可以在多個列上使用BREAK命令 break on 列名1 on 列名2 記錄分組 break on deptno skip 2 select deptno,ename from emp order by deptno; 每個deptno之間空兩行 clear break（取消BREAK命令） break on page（每次從一新頁開始） break on report（每次從一新報表開始） break on page on report（聯(lián)合使用） 分組計算 break on deptno skip 2 compute sum of sal on deptno 計算每個部門的工資總和 skip子句使部門之間的信息分隔開 其他計算命令 compute avg of sal on deptno（平均值） count 非空值的總數(shù) MAX 最大值 MIN 最小值 STD 標(biāo)準(zhǔn)偏差 VAR 協(xié)方差 NUMBER 行數(shù) 使compute命令失效 一旦定義了COMPUTE，則一直有效，直到 關(guān)閉COMPUTE（clear compute） SQL/PLUS環(huán)境命令 show 選項 （顯示當(dāng)前參數(shù)設(shè)置情況） show all（顯示全部參數(shù)） 設(shè)置參數(shù) set 選項 值或開關(guān) set autocommit on SET命令包括 set autocommit off|on|immediate （自動提交，OFF缺?。?set echo off|on （命令文件執(zhí)行，是否在終端上顯示命令本身，OFF缺?。?set feedback off|on （ON：查詢結(jié)束時，給出結(jié)果，記錄數(shù)的信息，缺??； OFF：無查詢結(jié)果，記錄數(shù)的信息） set heading off|on （ON：列的頭標(biāo)在報表上顯示，缺??；OFF：不在報表上顯示） set linesize n 一行顯示的最大字符數(shù)，缺省為80 set pagesize n 每頁的行數(shù)，缺省是14 set pause off|on|text （ON：當(dāng)報表超過一頁時，每顯示一屏就暫停顯示，等待用戶打回車鍵，再接著顯示； OFF：頁與頁不停頓，缺??；text：頁與頁停頓，并向用戶提示信息） SET BUFFER buffer 設(shè)置當(dāng)頭的命令緩沖區(qū)，通常情況下，SQL命令緩沖區(qū)已為當(dāng)前緩沖區(qū)。 由于SQL命令緩沖區(qū)只能存放一條SQL命令， 所以可用其它緩沖區(qū)來存放SQL命令和SQL*PLUS命令。 經(jīng)常用到的設(shè)置可放在login.sql文件中。 SET NULL set null no data select ename,comm from emp where deptno=30; 把部門30中無傭金雇員的傭金顯示為“NO DATA”。 set null是SQL*PLUS命令，用它來標(biāo)識空值（NULL），可以設(shè)置為任意字符串。 存盤命令SAVE save 文件名 input 1 select empno,ename,job 2 from emp 3 where job=analyst save research 目錄中會增加一個research.sql文件。 編輯命令EDIT edit EDIT編輯當(dāng)前緩沖區(qū)中的內(nèi)容。 編輯一個文件 edit research 調(diào)入命令GET get research 把磁盤上的文件內(nèi)容調(diào)入緩沖區(qū)，并顯示在屏幕上，文件名尾不必加.sql。 START命令 運行指定的文件 start research 輸出命令SPOOL spool tryfile 不僅可以使查詢結(jié)果在屏幕上顯示，還可以使結(jié)果存入文件 停止向文件輸出 spool off 把查詢結(jié)果在打印機(jī)上輸出，先把它們存入一個文件中， 然后不必使用SPOOL OFF，而用： spool out SPOOL OUT關(guān)閉該文件并在系統(tǒng)缺省的打印機(jī)上輸出 制作報表舉例 edit tryfile set echo off set autocommit on set pagesize 25 insert into emp (empno,ename,hiredate) values(9999,geiger,sysdate); insert into emp (empno,ename,deptno) values(3333,samson,20); spool new_emp select * from emp where deptno=20 or deptno is null / spool off set autocommit off 用start命令執(zhí)行這個文件 - 6 函數(shù) 字符型函數(shù) initcap(ename);將ename中每個詞的第一個字母改為大寫。 如：jack smith-Jack Smith length(ename);計算字符串的長度。 substr(job,1,4); 其它 lower upper least 取出字符串列表中按字母排序排在最前面的一個串 greatest 取出字符串列表中按字母排序排在最后的一個串 日期函數(shù) add_month(hiredate,5) 在雇傭時間上加5個月 month_between(sysdate,hiredate) 計算雇傭時間與系統(tǒng)時間之間相差的月數(shù) next_day(hiredate,FRIDAY) 計算受雇日期之后的第一個星期五的日期例 select ename,sal,next_day(sysdate,FRIDAY) as_of from emp where deptno=20; （as_of是別名） 如果不用to_char函數(shù)，日期在ORACLE中的缺省格式是DD_MON_YY to_char(date,date picture) select ename,to_char(hiredate,Dy Mon dd,yyyy) hired from emp where deptno=10; to_date(字符串,格式) insert into emp(empno,ename,hiredate) values(7999,asms,to_date(070387083000,MMDDYYHHMISS); 日期型數(shù)據(jù)的格式 dd 12 dy fri day friday ddspth twelfth mm 03 mon mar month march yy 87 yyyy 1987 例 Mar 12,1987 Mon dd,yyyy MAR 12,1987 MON dd,yyyy Thursday MARCH 12 Day MONTH dd Mar 12 11:00am Mon dd hh:miam Thu,the twelfth Dy,theddspth 算術(shù)函數(shù) least(v1,v2) select ename,empno,mgr,least(empno,mgr) lownum from emp where empno0 trunc(sal,0) 取sal的近似值（截斷） 空值函數(shù) nvl(v1,v2) v1為列名，如果v1不是空值，nvl返回其列值。 v1為空值，返回v2的值。 聚組函數(shù) select sum(comm) from emp; （返回一個匯總信息） 不能把sum用在select語句里除非用group by 字符型、日期型、數(shù)字型的聚組函數(shù) min max count可用于任何數(shù)據(jù)類型 select min(ename) from emp; select min(hiredate) from emp; select min(sal) from emp; 有多少人有工作？ select count(job) from emp; 有多少種不同的工種？ select count(distinct job) from emp; count distinct 計算某一字段中不同的值的個數(shù) 其它聚組函數(shù)（只用于數(shù)字型數(shù)據(jù)） avg 計算平均工資 select avg(sal) from emp; stddev 計算工資的平均差 select stddev(sal) from emp; sum 計算總工資 select sum(sal) from emp; group by子句 select deptno,sum(sal),avg(sal) from emp group by deptno; 按多個條件分組 每個部門的雇員數(shù) select deptno,count(*) from emp group by deptno; 每個部門的每個工種的雇員數(shù) select deptno,job,count(*) from emp group by deptno,job; 滿足條件的分組 （where是針對select的，having是針對group by的） 哪些部門的工資總和超過了9000 select deptno,sum(sal) from emp group by deptno having sum(sal)9000; select小結(jié) 除去職員，哪些部門的工資總和超過了8000 select deptno,sum(sal) from emp where job!=clerk group by deptno having sum(sal)8000 order by sum(sal); - 7 高級查詢 等值聯(lián)接 select empno,ename,job,emp.deptno,dname from emp,dept where emp.deptno=dept.deptno; 外聯(lián)接 select ename,dept.deptno,loc from emp,dept where emp.deptno(+)=dept.deptno; 如果在dept.deptno中有的數(shù)值，在emp.deptno中沒有（如deptno=40）， 則作外聯(lián)接時，結(jié)果中會產(chǎn)生一個空值 自聯(lián)接：同一基表的不同行要做聯(lián)接，可使用自聯(lián)接 指出每個雇員的經(jīng)理名字 select worker.ename,manager.ename manager from emp worker,emp manager where worker.mgr=manager.empno; 非等值聯(lián)接 哪些雇員的工資屬于第三級別 select ename,sal from emp,salgrade where grade=3 and sal between losal and hisal; （基表salgrade：grade losal hisal） 集合運算 行的連接 集合運算把2個或多個查詢結(jié)果合并為一個 union-set union Rows of first query plus of second query, less duplicate rows intersect-set intersection Rows both queries have in common minus-set difference rows unique to the first query 介紹幾個視圖 account view ename sal job sales view ename sal job research view ename sal job union運算 返回一個查詢結(jié)果中有但又不重要的行，它將基表或視圖中的記錄合并在一起 所有部門中有哪些雇員工資超過2000 對應(yīng)列的數(shù)據(jù)類型必須相同 select ename,sal from account where sal2000 union select ename,sal from research where sal2000 union select ename,sal from sales where sal2000; intersect運算 返回查詢結(jié)果中相同的部分 各個部門中有哪些相同的工種 select job from account intersect select job from research intersect select job from sales; minus運算 返回在第一個查詢結(jié)果中與第二個查詢結(jié)果不相同的那部分行記錄。 有哪些工種在財會部中有，而在銷售部中沒有？ select job from account minus select job from sales; 子查詢 slect ename,deptno from emp where deptno= (select deptno from emp where ename=smith); 多級子查詢 select ename,job,sal from emp where job= (select job from emp where ename=clark) or sal (select sal from emp where ename=clark); 多個基表與子查詢 select ename,job,sal from emp,dept where loc=new york and emp.deptno=dept.deptno and sal (select sal from emp where ename=scott); 子查詢中使用聚組函數(shù) select ename,hiredate from emp where hiredate= (select min(hiredate) from emp); 8 授權(quán) 系統(tǒng)權(quán)限 DBA 所有權(quán)限 RESOURCE 注冊，創(chuàng)建新的基表 CONNECT，注冊，查詢 只有DBA才有權(quán)創(chuàng)建新的用戶 grant connect to scott identified by tiger; DBA或用戶自己可以改變用戶口令 grant connect to scott identified by leopard; 基表權(quán)限1 有兩種方法獲得對基表操作的權(quán)限 創(chuàng)建自己的基表 獲得基表創(chuàng)建用戶的許可 grant select,insert on emp to scott; 這些權(quán)限有 select insert update delete alter index 把所有權(quán)限授于他人 grant all on emp to scott; 同義詞 select * from scott.emp 創(chuàng)建同義詞 為用戶allen的EMP基表創(chuàng)建同義詞employee create synonym employee for allen.emp 基表權(quán)限2 你可以使其他用戶有這樣的權(quán)力，即其他用戶可以把你的基表權(quán)限授予他人 grant all on emp to scott with grant option; 收回權(quán)限 系統(tǒng)權(quán)限 只有被DBA收回 基表權(quán)限 隨時都可以收回 revoke insert on emp from scott; - 9 索引 建立索引 create index emp_ename on emp(ename); 刪除索引 drop index emp_ename; 關(guān)于索引 只對較大的基表建立索引（至少50條記錄） 建立索引之前插入數(shù)據(jù) 對一個基表可建立任意多個索引 一般是在作為主鍵的列上建立索引 建立索引之后，不影響SQL命令的執(zhí)行 建立索引之后，ORACLE自動維護(hù)和使用索引 保證數(shù)據(jù)唯一性 提高執(zhí)行速度的同時，索引還可以保證每個記錄中的每個列值是不重復(fù)的。 create unique index emp_empno on emp(empno); - 練習(xí)和答案 有沒有工資比獎金多的雇員？如果有，按工資的降序排列。 如果有兩個以上的雇員工資相同，按他們的名字排序。 select ename employee,sal salary,comm commision from emp where salcomm order by sal desc,ename; 列出有關(guān)雇員姓名、獎金占收百分比的信息。 要求顯示時列名意義明確，按雇員姓名排序，不包括獎金未知的雇員。 select ename employee,(comm/(comm+sal)*100 incentive from emp where comm is not null order by ename; 在chicago（部門30）工作的所有雇員的工資上漲10%。 update emp set sal=1.1*sal where deptno=30; update emp set sal=1.1*sal where deptno=(select deptno from dept where loc=chicago); 為hitech公司新建一個部門，編號為50，其它信息均不可知。 insert into dept(dname,deptno) values(faclities,50); 創(chuàng)建視圖，三個列名，其中不包括職員信息 create view employee(employee name, employee number, employee job) as select ename,empno,job from emp where job!=clerk; 制作工資報表，包括雇員姓名、受雇時間（按星期計算），工資和部門編號， 一頁顯示一個部門的信息，每頁尾，顯示該頁的工資之和以及受雇時間之和， 報表結(jié)尾處，顯示所有雇員的工資總和以及受雇時間總和， 工資按美元計算，受雇時間按星期計算，每頁的上方應(yīng)有標(biāo)題。 ttitle service break on deptno on page on report compute sum of sal on deptno compute sum of sal on report compute sum of service_length on deptno compute sum of service_length on report column sal format $99,999.00 column service_length format 9999 select deptno,ename employee,(sysdate-hiredate)/7 service_length,sal from emp order by deptno; 制作報表，包括雇員姓名、總收入和受傭日期， 且：姓名的第一個字母必須大寫，雇傭日期格式為MM/DD/YYYY， 總收入包括沒有獎金的雇員的總收入，姓名按字母順序排列。 col hire dateormat A12 col employee format A10 col compensation format $99,999.00 select initcap(ename) employee, (sal+nvl(comm,0) compensation, to_char(hiredate,MM/DD/YYYY) hire date from emp order by ename; 列出有超過7個周邊國家的國家名字和面積。 select nation,area from nation where code in (select nation_code from border group by nation_code having count(*)7); 列出所有面積大于等于日本的島國的國名和人口。 select nation,population from nation,border where code=nation_code(+) and nation_code is null and area= (select area from nation where upper(nation)=JAPAN); 列出所有邊界在其它國家中的國名，并且顯示其邊界國家名字。 break on nation select nation1.nation, nation2.nation borderin_country from nation nation1,border,nation nation2 where nation1.code=border.nation_code and border.border_code=nation2.code order by nation1.nation; - - PL/SQL 2 PL/SQL的塊結(jié)構(gòu)和數(shù)據(jù)類型 塊結(jié)構(gòu)的特點 嵌套 begin . begin . exception . end; exception . end; 標(biāo)識符： 不能超過30個字符 第一個字符必須為字母 其余字符可以是字母，數(shù)字，$，_，或# 不區(qū)分大小寫形式 如果用雙引號括起來，則字符順序可以任意排列，并區(qū)分大小寫形式 無SQL保留字 數(shù)據(jù)類型 數(shù)字型： 整數(shù)，實數(shù)，以及指數(shù) 字符串： 用單引號括起來 若在字符串表示單引號，則使用兩個單引號 字符串長度為零（兩個單引號之間沒有字符），則表示NULL 字符： 長度為1的字符串 數(shù)據(jù)定義 語法 標(biāo)識符常數(shù) 數(shù)據(jù)類型NOT NULL:=PL/SQL表達(dá)式; :=表示給變量賦值 數(shù)據(jù)類型包括 數(shù)字型 number(7,2) 字符型 char(120) 日期型 date 布爾型 boolean（取值為true,false或null，不存貯在數(shù)據(jù)庫中） 日期型 anniversary date:=05-JUL-95; project_completion date; 布爾型 over_budget boolean not null:=false; available boolean; （初始值為NULL） %type類型匹配 books_printed number(6); books_sold book_printed%type; manager_name emp.ename%type; 變量賦值 變量名:=PL/SQL表達(dá)式 numvar:=5; boolvar:=true; datevar:=11-JUN-87; 字符型、數(shù)字型表達(dá)式中的空值 null+=null（空值加數(shù)字仍是空值） null=null（空值與數(shù)字進(jìn)行比較，結(jié)果仍是空值） null|字符串=字符串（null即） （空值與字符串進(jìn)行連接運算，結(jié)果為原字符串） 變量作用范圍 標(biāo)識符在宣言它的塊中有效 標(biāo)識符如果不在子塊中重新定義，則在PL