2022年Oracle知識(shí)點(diǎn)總結(jié)

1、oracle 知識(shí)點(diǎn)總結(jié)1、oracle數(shù)據(jù)庫(kù)的安裝和配置oracle internet級(jí)數(shù)據(jù)庫(kù) sqlserver 中小企業(yè)級(jí)數(shù)據(jù)庫(kù) access 桌面級(jí)數(shù)據(jù)庫(kù)oracle 的安裝注意： 來(lái)源和目標(biāo)的目錄不允許有中文或空格oracle 數(shù)據(jù)庫(kù)啟動(dòng)時(shí)必需開(kāi)啟的后臺(tái)服務(wù)oracleorahome90tnslistener 使第三方的軟件或語(yǔ)言訪問(wèn)oracleserviceetc oracle的實(shí)例 crud 增刪改查注意：中的 etc是你起的實(shí)例的名字oracle 的開(kāi)發(fā)工具dos 界面的開(kāi)發(fā)平臺(tái) - 運(yùn)行-sqlplus oracle 本身基于 dos 的平臺(tái) - 運(yùn)行- sqlplusw o

2、racle enterprise manager console pl/sql developer7.1.5 創(chuàng)建一個(gè)表的完整流程創(chuàng)建一個(gè)數(shù)數(shù)庫(kù)例子：創(chuàng)建一個(gè)數(shù)據(jù)庫(kù) etc , 物理文件放在 f: ，初始化 1m ,自增長(zhǎng)開(kāi)啟 create tablespace etc datafile f:etc.dbf 精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 1 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 1 頁(yè)，共 40 頁(yè) - - - - - - -

3、- - size 1m autoextend on; 刪除表空間 drop tablespace 數(shù)據(jù)庫(kù)名稱 ; 為該數(shù)據(jù)庫(kù)創(chuàng)建一個(gè)帳號(hào)和密碼語(yǔ)法： create user 用戶名稱 identified by 密碼 default tablespace 數(shù)據(jù)庫(kù)名稱注意： 1、在 oracle 中賬號(hào)和數(shù)據(jù)庫(kù) one2one綁定 2、oracle 的密碼不允許為純數(shù)字或空 3、需要在 system 賬號(hào)完成動(dòng)作修改 oracle 的賬號(hào)密碼語(yǔ)法：alter user 用戶 identified by 新密碼用 grant 權(quán)限 to 用戶 為該帳戶授權(quán)語(yǔ)法： grant 權(quán)限名稱 to 用戶;

4、 撤銷權(quán)限語(yǔ)法： revoke 權(quán)限名稱 from 用戶名; 在該帳號(hào)下創(chuàng)建表oracle 中的事務(wù)處理精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 2 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 2 頁(yè)，共 40 頁(yè) - - - - - - - - - transacation 事務(wù)特點(diǎn)：整體提交 (commit) 整體回滾 (rollback) 事務(wù)的四個(gè)特性原子性不可分割持久性 - 當(dāng)數(shù)據(jù)整整寫入到數(shù)據(jù)庫(kù)物理文件中后，該數(shù)據(jù)被持久化隔離性 -

5、事務(wù)之間相互獨(dú)立互不干擾一致性 - 數(shù)據(jù)安全oracle 中的保留點(diǎn) ( 還原點(diǎn)) 關(guān)鍵字： savepoint 使用方法： savepoint 名稱; 如進(jìn)行保留點(diǎn)回滾 rollback to mark2 oracle 備份數(shù)據(jù)表數(shù)據(jù)create table 備份表名稱 as 查詢語(yǔ)句 ; oracle 備份表結(jié)構(gòu) create table emp_bak as select * from emp where 1=2; 2、oracle的函數(shù)function （系統(tǒng)預(yù)定義函數(shù)）函數(shù)和存儲(chǔ)過(guò)程相比，在于函數(shù)必須有返回值，而存儲(chǔ)過(guò)程只有輸出參數(shù)。語(yǔ)法： create or replace fun

6、ction 函數(shù)名稱 return 返回類型精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 3 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 3 頁(yè)，共 40 頁(yè) - - - - - - - - - as pl/sql語(yǔ)句塊例子：輸入一個(gè)員工工號(hào)，返回該員工所在部門平均工資 create or replace function fun_demo1(eno emp.empno%type) return emp.sal%type as avgsal emp.

7、sal%type; begin select avg(sal) into avgsal from emp where empno=eno; return avgsal; end; 調(diào)用： select fun_demo1(7788) from dual; public int add(int num1,int num2) int res = 0; res = num1+num2; return res; 單行函數(shù) (單值函數(shù) ) ：函數(shù)有且只有返回1 個(gè)值 |-字符函數(shù) substr instr 字符串連接函數(shù)： concat 精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - -

8、 - - - - - 第 4 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 4 頁(yè)，共 40 頁(yè) - - - - - - - - - concat(字符串 1, 字符串 2) oracle中字符連接操作符號(hào) | 字符串對(duì)齊函數(shù)： lpad - left padding 左填充 /rpad 語(yǔ)法： lpad( 字符串, 預(yù)留位置 , 填充字符 ) sql: select lpad(ename,10, ) from emp; 字符串截取函數(shù)： substr - substring 語(yǔ)法： substr

9、( 字符串 , 截取的起始位置 , 截取個(gè)數(shù) ) substr(字符串 , 截取的起始位置 ) sql: select substr(ename,1,3) frome emp; 字符查找函數(shù)： instr - inner string 語(yǔ)法： instr(字符串, 待找的字符 , 查找的起始位置 , 出現(xiàn)次數(shù)) sql: select ename,instr(ename,t,1,1) from emp; 注意：當(dāng)返回值為0 時(shí)，字符不存在函數(shù)： initcap - initial captor 語(yǔ)法： initcap(字符串 ) sql: select initcap(ename) from

10、emp; 函數(shù)： length 語(yǔ)法： length( 字符串 ) sql: select * from emp where length(ename)=5; 函數(shù)： lower / upper 語(yǔ)法： lower( 字符串) sql: select lower(ename) from emp; 精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 5 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 5 頁(yè)，共 40 頁(yè) - - - - - - - - - |-

11、日期函數(shù) sysdate add_months 函數(shù)： sysdate sql: select sysdate from dual; dual-無(wú)實(shí)際意義，函數(shù)測(cè)試或其他測(cè)試使用月份差： months_between 語(yǔ)法： months_between(日期 1, 日期 2) sql: select * from emp where months_between(sysdate,hiredate)=144 函數(shù)： last_day 語(yǔ)法： last_day( 日期數(shù)據(jù) ) sql: select * from emp where last_day(hiredate)-2=hiredate;

12、函數(shù)： add_months 語(yǔ)法： add_months(時(shí)間, 添加月份 ) sql：select add_months(sysdate,3) from dual; |-轉(zhuǎn)換函數(shù) to_char to_date 轉(zhuǎn)換函數(shù) to_char 語(yǔ)法： to_char( 時(shí)間數(shù)據(jù) , 制定格式 ) sql: select to_char(sysdate,yyyy-mm-dd hh24:mi:ss d ddd day) from dual; yyyy-mm-dd 年月日精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 6 頁(yè)，共 40 頁(yè) - - - - -

13、 - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 6 頁(yè)，共 40 頁(yè) - - - - - - - - - hh24:mi:ss 時(shí)分秒 (24) d 一周第幾天 ddd 一年的第幾天 day 星期幾to_date: 將文本類型數(shù)據(jù)按照制定格式轉(zhuǎn)換成時(shí)間類型數(shù)據(jù)語(yǔ)法：to_date(具體的時(shí)間字符數(shù)據(jù) ,時(shí)間字符串格式 ) to_date(2010-01-19,yyyy-mm-dd) 注意：oracle 中的 date 字段類型及其特殊 |-數(shù)學(xué)函數(shù) abs ceil floor abs(n) 絕對(duì)值 ceil(n) 向上取值 ceil

14、ling天花板 ceil(1.1) - 2 floor(n) 向下取值 floor地板 floor(2.9) - 2 mod(m,n) 求模取余 mod(5,2) - 1 power(m,n) m的 n 次方 power(2,8) -256 round(m,n) 四舍五入 m 待處理數(shù)據(jù) n 精度 round(3.1415,2) - 3.14 trunc(m) 整數(shù)截取 trunc(3.1415) = 3 sign(m) 符號(hào) sqrt(m) 平方根 |-混合函數(shù) user nvl user : 返回當(dāng)前登錄賬號(hào) sql select user from dual; 精品學(xué)習(xí)資料 可選擇p d

15、 f - - - - - - - - - - - - - - 第 7 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 7 頁(yè)，共 40 頁(yè) - - - - - - - - -nvl : 替空函數(shù)語(yǔ)法：nvl( 字符串 , 替換后的數(shù)據(jù) ) 注意：替換后的數(shù)據(jù)必須與該字段類型保持一致 sql select ename,nvl(comm,0) from emp; 分析函數(shù)：數(shù)據(jù)分析和挖掘功能 data mining rank dense_rank 作用：主要用于排名使用函數(shù)： rank 語(yǔ)法： rank

16、() over (order by 字段名稱 ) sql: select rank() over(order by sal desc),ename,sal from emp; 若排名相同下一個(gè)將跳轉(zhuǎn) sql: select dense_rank() over(order by sal desc),ename,sal from emp; 連續(xù)語(yǔ)法： rank( 參數(shù) 1，參數(shù) 2) within group(order by 字段 1，字段2) sql: select ename,rank(2850,blake) within group(order by sal,ename) from emp

17、; 分組函數(shù)：類似于sqlserver中的聚合函數(shù) sum avg count group by 分組關(guān)鍵字 having 條件3、oracle數(shù)據(jù)庫(kù)的查詢制定區(qū)間查詢oracle中偽列： oracle 為每張數(shù)據(jù)表 (包括用戶自定義創(chuàng)建的數(shù)據(jù)表)都自動(dòng)創(chuàng)建兩個(gè)位列分別是：rowid、rownum 精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 8 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 8 頁(yè)，共 40 頁(yè) - - - - - - - - -通過(guò)

18、命令： sql desc 表名; -查看表結(jié)構(gòu)rowid: 由 18 個(gè)字符組成唯一標(biāo)識(shí)每一行rownum: 行號(hào)介紹 問(wèn)題：使用 oracle 分層查詢以數(shù)據(jù)結(jié)構(gòu)顯示emp表中的人員關(guān)系語(yǔ)法：select lpad(ename,level*5, ) from emp connect by prior empno=mgr start with mgr is null; 并集：查詢部門10 的辦事員和部門20 的經(jīng)理關(guān)鍵字： union all select * from emp where deptno=10 and job=clerk union all select * from emp

19、where deptno=20 and job=manager; 注意：union all不會(huì)消除查詢結(jié)果中的重復(fù)數(shù)據(jù), union會(huì)消除查詢結(jié)果中的重復(fù)記錄交集：查詢部門10 和部門 20 都有的工作類型關(guān)鍵字： intersect select job from emp where deptno=10 intersect select job from emp where deptno=20; 差集：查詢部門30 中有，而部門 10 中沒(méi)有的工作類型關(guān)鍵字： minus select job from emp where deptno=30 精品學(xué)習(xí)資料 可選擇p d f - - - -

20、- - - - - - - - - - 第 9 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 9 頁(yè)，共 40 頁(yè) - - - - - - - - - minus select job from emp where deptno=10; 4、oracle數(shù)據(jù)庫(kù)的數(shù)據(jù)對(duì)象oracle的數(shù)據(jù)庫(kù)對(duì)象：用戶、表、約束、序列、視圖、同義詞和索引定義：但凡使用create 開(kāi)頭創(chuàng)建的對(duì)象稱之為數(shù)據(jù)庫(kù)對(duì)象。鎖定 / 解除用戶語(yǔ)法：alter user 用戶名稱 account lock; 注意：該指令一般在sy

21、stem 賬號(hào)下輸入解除用戶鎖定語(yǔ)法：alter user 用戶名稱 account unlock; sql alter user scott account unlock; 注意：oracle 11g 中 scott賬號(hào)是默認(rèn)鎖定的，需要進(jìn)行解鎖處理。用戶授權(quán) / 撤銷 grant 權(quán)限名稱 to 用戶名稱 ; 撤銷用戶權(quán)限 revoke 權(quán)限名稱 from 用戶名; 表數(shù)據(jù)字段的類型數(shù)字類型： number 語(yǔ)法：number(長(zhǎng)度,精度) number(5,2) 數(shù)字長(zhǎng)度為 3，精度為 2 范圍： -999.99 999.99 精品學(xué)習(xí)資料 可選擇p d f - - - - - - -

22、- - - - - - - 第 10 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 10 頁(yè)，共 40 頁(yè) - - - - - - - - - number(5) 數(shù)字長(zhǎng)度為 5，精度為 0 范圍： -99999 99999 number 精度默認(rèn)為 0 范圍： -32767 32768 字符類型： varchar2 可變長(zhǎng)度 char 字符類型語(yǔ)法：varchar2( 長(zhǎng)度) 日期類型： date long 數(shù)據(jù)類型：該字段最大存儲(chǔ)空間為2gb ，該字段不允許添加索引 lob數(shù)據(jù)類型：該字段最大

23、存儲(chǔ)空間為4gb ，該字段不允許添加索引 clob ：大字符類型 - 文章(小說(shuō)， cnki) blob ：大二進(jìn)制類型 - 圖片、音頻、視頻 .多媒體文件 flob ：文件定位器 - 內(nèi)存指針創(chuàng)建數(shù)據(jù)表的語(yǔ)法結(jié)構(gòu)： create table 表名 ( 字段名稱 1 類型 約束, 字段名稱 1 類型 約束, 字段名稱 n 類型 約束 ) 使用 desc 表名 查看表結(jié)構(gòu)給表添加字段 address 和 telephone 語(yǔ)法：alter table 表名 add( 字段名稱類型); 精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 11 頁(yè)，共 40

24、 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 11 頁(yè)，共 40 頁(yè) - - - - - - - - -刪除表字段 address 語(yǔ)法：alter table 表名 drop column 字段名稱 ; 修改表中已有字段的類型語(yǔ)法：alter table 表名 modify( 字段名稱新類型 ); 修改表名稱語(yǔ)法：rename 原表名 to 新表名 ; 約束完整性約束 = 準(zhǔn)確性 + 一致性 約束的四大分類：實(shí)體完整性約束 ( 行約束) - 盡量減少數(shù)據(jù)表中數(shù)據(jù)的冗余( 重復(fù)的數(shù)據(jù)) 技術(shù)實(shí)施：主鍵約束、唯

25、一約束語(yǔ)法：alter table 表名 add primary key(字段名稱 ); altet table 表名 add unique(字段名稱 ); 添加非空約束語(yǔ)法：alter table 表名 modify( 字段名稱 not null); 域完整性約束 ( 列約束 ) - 達(dá)到數(shù)據(jù)的準(zhǔn)確性，控制數(shù)據(jù)的大小或范圍或格式技術(shù)實(shí)施： check 檢查約束 alter table 表名 add constraint 約束名稱 check( 條件); 引用完整性約束 ( 表間約束 ) - 達(dá)到數(shù)據(jù)的一致性技術(shù)實(shí)施：外鍵約束精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - -

26、- - - - - 第 12 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 12 頁(yè)，共 40 頁(yè) - - - - - - - - -添加外鍵約束 alter table 外鍵表 add constraint 約束名稱 foreign key(外鍵字段) references 主鍵表( 主鍵字段 ); 刪除外鍵約束語(yǔ)法：alter table 表名 drop constraint 約束名稱 ; 刪除匿名約束語(yǔ)法：alter table 表名 modify( 字段名稱 null); 自定義完整性約束

27、 - 以上三種數(shù)據(jù)庫(kù)內(nèi)置約束不能滿足開(kāi)發(fā)人員的需求是，需要開(kāi)發(fā)人員自定一些約束條件技術(shù)實(shí)施：觸發(fā)器使用 sql 腳本批量插入數(shù)據(jù)使用命令 sql 路徑+文件名稱 sql f:/a.sql; 序列定義： oracle 中使用 sequence來(lái)實(shí)現(xiàn)字段的自增長(zhǎng)功能，和sqlserver中的 identity屬性類型語(yǔ)法： create sequence 序列名稱 start with 起始數(shù)字 increment by 增長(zhǎng)量; 用法：序列對(duì)象通過(guò)兩個(gè)重要的屬性進(jìn)行訪問(wèn)取值 .nextval - next value - 下一個(gè)值精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - -

28、 - - - - - 第 13 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 13 頁(yè)，共 40 頁(yè) - - - - - - - - -例子：select seq_1.nextval from dual; .currval - current value - 當(dāng)前值例子：select seq_1.currval from dual; 如何實(shí)現(xiàn)其自增長(zhǎng)功能通過(guò)語(yǔ)法結(jié)構(gòu)將序列對(duì)象與對(duì)應(yīng)的數(shù)據(jù)表進(jìn)行綁定，實(shí)現(xiàn)其自增長(zhǎng)功能。例子： create table users(userid number(2),u

29、sername varchar2(10); create sequence seq_users start with 1 increment by 1; insert into users values(seq_users.nextval,alvin); 注意： oracle 建議一個(gè)序列對(duì)象盡與一張數(shù)據(jù)表進(jìn)行綁定技巧： create sequence 序列名稱 ; 默認(rèn)從 1 每次增長(zhǎng) 1 視圖作用：1、簡(jiǎn)化復(fù)雜的 sql語(yǔ)句 2、提高數(shù)據(jù)的訪問(wèn)安全性語(yǔ)法：create or replace view 視圖名稱 as 復(fù)雜的 sql查詢語(yǔ)句視圖是一張?zhí)摂M的數(shù)據(jù)表，在table 對(duì)象中不存在，

30、只存在于內(nèi)存中注意：若視圖由 1 張基表組成，修改視圖將會(huì)修改基表數(shù)據(jù)，若視圖由多張表組成，則不會(huì)修改基表數(shù)據(jù)同義詞精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 14 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 14 頁(yè)，共 40 頁(yè) - - - - - - - - -作用：oracle 中的同義詞提供各種數(shù)據(jù)庫(kù)對(duì)象(表)的別名，目的在于提高數(shù)據(jù)表訪問(wèn)的安全性，尤其多用戶并發(fā)訪問(wèn)時(shí)。語(yǔ)法： create synonym 表的別名 for 表名; sq

31、l create synonym bak1 for emp; 擴(kuò)展：公有同義詞 public 語(yǔ)法： create public synonym 別名 for 表名; 索引作用：提高 sql查詢語(yǔ)句按照制定字段查詢的效率語(yǔ)法： create index 索引的名稱 on 數(shù)據(jù)表 (字段) 例子：為 hiredate字段添加索引，提高按日期查詢的sql語(yǔ)句的效率 create index idx_hire on emp(hiredate) 原理：空間換效率 sql 語(yǔ)句索引優(yōu)化規(guī)則不帶 where 條件的 sql語(yǔ)句一定不能使用索引在 where 條件中有索引的字段不能使用函數(shù)例子：假設(shè)我們查詢

32、在1987 年參加工作的所有員工sql select * from emp where to_char(hiredate,yyyy)=1987; sql select * from emp where hiredate=to_date(1987,yyyy); 在 where 條件中有索引的字段不能參與運(yùn)算例子：查詢?cè)?10000天以前參加工作的員工信息 sql select * from emp where sysdate-hiredate10000; 精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 15 頁(yè)，共 40 頁(yè) - - - - - - -

33、 - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 15 頁(yè)，共 40 頁(yè) - - - - - - - - - sql select * from emp where hiredate select e.*,d.dname from emp e,dept d where e.deptno=d.deptno and d.dname=accounting; oracle 中常用的數(shù)據(jù)字典 oracle是以中以表管表的模式，同時(shí)數(shù)據(jù)字典是有表或視圖組成。數(shù)據(jù)字典的分類： user_xxx ： 表示當(dāng)前用戶所擁有的數(shù)據(jù)庫(kù)對(duì)象 all_xxxx ： 表示當(dāng)

34、前用戶與權(quán)力查看的數(shù)據(jù)庫(kù)對(duì)象 dba_xxxx ： 表示數(shù)據(jù)中所擁有的全部對(duì)象，只有在超級(jí)管理員級(jí)別下才可查看。 xxxx ：代表數(shù)據(jù)庫(kù)對(duì)象的復(fù)數(shù)形式，例如：tables indexes . 精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 16 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 16 頁(yè)，共 40 頁(yè) - - - - - - - - -5、oracle數(shù)據(jù)庫(kù)的多表查詢等連接： select emp.ename,dept.dname from

35、emp,dept where emp.deptno=dept.deptno; 使用內(nèi)連接模式編寫：內(nèi)連接： inner join .on. select emp.ename,dept.dname from emp inner join dept on emp.deptno=dept.deptno; 分析：部門為主顯示字段，dept為主表姓名為輔助顯示字段， emp 為輔助表外連接： select d.dname,e.ename from emp e,dept d where e.deptno(+)=d.deptno; 6、oracle數(shù)據(jù)庫(kù)的游標(biāo)游標(biāo)cursor 游標(biāo)的分類：隱式游標(biāo)： ora

36、cle自動(dòng)應(yīng)以一個(gè)隱式游標(biāo)名稱為sql ,該游標(biāo)不被程序員控制，自動(dòng)開(kāi)啟、操作及結(jié)束。顯式游標(biāo)： 可供程序員自己創(chuàng)建及操作|- 靜態(tài)游標(biāo)|- 動(dòng)態(tài)游標(biāo)靜態(tài)游標(biāo)loop 循環(huán)游標(biāo)/ for 循環(huán)游標(biāo)精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 17 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 17 頁(yè)，共 40 頁(yè) - - - - - - - - -=loop 循環(huán)游標(biāo) = 步驟 1：創(chuàng)建一個(gè)游標(biāo)cursor 游標(biāo)名稱is sql 查詢語(yǔ)句 ; 步驟

37、2：開(kāi)啟游標(biāo)open 游標(biāo)名稱 ; - 執(zhí)行定義的 sql查詢語(yǔ)句并將結(jié)果集合存放到游標(biāo)中步驟 3：使用 loop 循環(huán)遍歷游標(biāo)中的數(shù)據(jù)并進(jìn)行相應(yīng)處理loop fetch 游標(biāo)名稱 into 變量; exit when 游標(biāo)名稱 %notfound; . end loop; 步驟 4：關(guān)閉游標(biāo)close 游標(biāo)名稱 ; 例子：顯示部門編號(hào)為10 的員工姓名declare -步驟 1：創(chuàng)建一個(gè)游標(biāo)cursor mycur is select ename from emp where deptno=10; en emp.ename%type; 精品學(xué)習(xí)資料 可選擇p d f - - - - - -

38、- - - - - - - - 第 18 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 18 頁(yè)，共 40 頁(yè) - - - - - - - - -begin -步驟 2：開(kāi)啟游標(biāo)open mycur; -步驟 3：使用 loop 循環(huán)遍歷游標(biāo)中的數(shù)據(jù)并進(jìn)行相應(yīng)處理loop fetch mycur into en; exit when mycur%notfound; dbms_output.put_line(en); end loop; -步驟 4：關(guān)閉游標(biāo)close mycur; end; =fo

39、r 循環(huán)游標(biāo) = declare -步驟 1：創(chuàng)建一個(gè)游標(biāo)cursor mycur is select * from emp where deptno=10; erow emp%rowtype; begin -步驟 3：使用 for 循環(huán)遍歷游標(biāo)中的數(shù)據(jù)并進(jìn)行相應(yīng)處理for erow in mycur loop dbms_output.put_line(erow.ename); 精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 19 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - -

40、- - - - - 第 19 頁(yè)，共 40 頁(yè) - - - - - - - - -end loop; end; 動(dòng)態(tài)游標(biāo) (擴(kuò)展) 作用：使用游標(biāo)變量高度重用( 只能使用 loop 循環(huán)) 語(yǔ)法結(jié)構(gòu)：步驟 1：聲明一個(gè)動(dòng)態(tài)游標(biāo)類型 type 類型名稱 is ref cursor; 步驟 2：使用聲明號(hào)的類型創(chuàng)建一個(gè)游標(biāo)變量游標(biāo)名稱類型名稱 ; 步驟 3：打開(kāi)游標(biāo)并且綁定sql語(yǔ)句 open 游標(biāo)名稱 for sql查詢語(yǔ)句步驟 4：使用循環(huán)遍歷游標(biāo)中的數(shù)據(jù)并進(jìn)行處理 loop fetch 游標(biāo)名稱 into 變量; exit when 游標(biāo)名稱 %notfound .數(shù)據(jù)處理 . end lo

41、op; 步驟 5：重復(fù) 34 步驟 6：關(guān)閉游標(biāo)精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 20 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 20 頁(yè)，共 40 頁(yè) - - - - - - - - - close 游標(biāo)名稱 ; 例子：顯示部門 30 的員工姓名及 dept 表中全部部門名稱declare -聲明一個(gè)游標(biāo)類型 type cur is ref cursor; -聲明游標(biāo)變量 mycur cur; -聲明變量 erow emp%rowty

42、pe; drow dept%rowtype; begin -打開(kāi)游標(biāo) open mycur for select * from emp where deptno=30; loop fetch mycur into erow; exit when mycur%notfound; dbms_output.put_line(erow.ename); end loop; dbms_output.put_line(=); -打開(kāi)游標(biāo) open mycur for select * from dept; loop 精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第

43、21 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 21 頁(yè)，共 40 頁(yè) - - - - - - - - - fetch mycur into drow; exit when mycur%notfound; dbms_output.put_line(drow.dname); end loop; -關(guān)閉游標(biāo) close mycur; end; 游標(biāo)所有屬性游標(biāo)名稱 % 屬性名稱屬性名稱含義notfound 當(dāng)游標(biāo)中沒(méi)有遍歷的數(shù)據(jù)時(shí)返回1 found 當(dāng)游標(biāo)中存在有遍歷的數(shù)據(jù)時(shí)返回1 isopen

44、當(dāng)游標(biāo)開(kāi)啟狀態(tài)時(shí)返回1 rowcount 返回游標(biāo)影響行數(shù)7、oracle數(shù)據(jù)的 pl/sql pl/sql 塊語(yǔ)法格式： declare 聲明 . 變量聲明區(qū) begin 程序開(kāi)始 . 執(zhí)行語(yǔ)句區(qū) exception 異常處理部分精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 22 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 22 頁(yè)，共 40 頁(yè) - - - - - - - - - . end; 程序結(jié)束 ; 變量的聲明標(biāo)量標(biāo)識(shí)符，不區(qū)分大小寫，先

45、聲明再使用變量聲明的語(yǔ)法結(jié)構(gòu)：標(biāo)量名稱類型; 例子： declare eno number(4); edate date; begin end; 屬性引用類型 |- 字段引用類型例子： declare eno emp.ename%type; |- 行引用類型例子： declare erow emp%rowtype; 變量的賦值四種方式：聲明時(shí)賦值精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 23 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 23 頁(yè)，

46、共 40 頁(yè) - - - - - - - - -賦值運(yùn)算符“:=”例子：聲明一個(gè)變量賦值為2 declare num1 number(2):=2; . begin . end; 執(zhí)行區(qū)賦值例子：聲明一個(gè)變量賦值為2 declare num1 number(2); . begin num1:=2; . end; 對(duì)話框賦值 - 程序與用戶之間進(jìn)行數(shù)據(jù)交互例子：聲明一個(gè)變量賦值為2 declare num1 number(2); . 精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 24 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可

47、選擇p d f - - - - - - - - - - - - - - 第 24 頁(yè)，共 40 頁(yè) - - - - - - - - - begin num1:=& 請(qǐng)輸入數(shù)字 ; . end; 注意點(diǎn)：當(dāng)輸入字符串類型數(shù)據(jù)的使用書寫格式為：name:=&姓名; 當(dāng)輸入數(shù)字類型數(shù)據(jù)的使用書寫格式為：age:=&年齡; o. 賦值模式例子：將 emp表中 simth 的工資復(fù)制到變量esal 中 declare esal emp.sal%type; begin select sal into esal from emp where ename=smith

48、; end; oracle 的 pl/sql中屏幕輸出語(yǔ)句語(yǔ)法：dbms_output.put_line(xxxxxxx); 將信息輸出并換行 dbms_output.put(xxxxxxx); 輸出不換行注意：oracle 默認(rèn)關(guān)閉屏幕輸出功能，需要開(kāi)發(fā)人員輸入指令將其功能開(kāi)啟 sql set serveroutput on; pl/sql中的異常處理語(yǔ)法：exception 精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 25 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - -

49、- - - - - 第 25 頁(yè)，共 40 頁(yè) - - - - - - - - - when 異常名稱 then .處理方式 . 異常：others - 任何異常都可捕獲執(zhí)行語(yǔ)句部分 循環(huán)的使用： for while loop for語(yǔ)法結(jié)構(gòu)： for 循環(huán)變量 in 起始數(shù)字 . 結(jié)束數(shù)字 loop .循環(huán)體. end loop; 例子 1：顯示數(shù)字 101 (reverse) declare i number; begin for i in reverse 1.10 loop dbms_output.put_line(i); end loop; end; 例子 1：顯示乘法口訣declar

50、e i number; 精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 26 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 26 頁(yè)，共 40 頁(yè) - - - - - - - - - j number; begin -外層循環(huán)控制行數(shù) for i in 1.9 loop -內(nèi)層循環(huán)控制個(gè)數(shù) for j in 1.i loop dbms_output.put(j|*|i|=|i*j| ); end loop; -換行 dbms_output.put_lin

51、e( ); end loop; end; while 循環(huán)語(yǔ)法：while 進(jìn)入條件 loop .循環(huán)體 . end loop; loop 循環(huán)語(yǔ)法：loop exit when 推出條件 ; 精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 27 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 27 頁(yè)，共 40 頁(yè) - - - - - - - - - .循環(huán)體 . end loop; 條件判斷語(yǔ)法： if 條件 then elsif 條件 then el

52、se end if; 例子：模擬一個(gè)登錄結(jié)構(gòu)，用戶名為admin密碼為 chinasoft declare username varchar2(10); password varchar2(10); begin -用戶輸入賬號(hào)及密碼 username:=&賬號(hào); password:=&密碼; -邏輯判斷 if username=admin and password=chinasoft then dbms_output.put_line(歡迎登錄： |username); 精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 28 頁(yè)，共 4

53、0 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 28 頁(yè)，共 40 頁(yè) - - - - - - - - - else dbms_output.put_line(賬號(hào)或密碼錯(cuò)誤！ ); end if; end; 處理自定義異常例子：若用戶輸入的數(shù)字不再110之內(nèi)則拋出異常步驟 1：創(chuàng)建一個(gè)異常對(duì)象在變量聲明區(qū)輸入一下代碼 exp exception; -聲明一個(gè)異常對(duì)象，名稱為exp 步驟 2：編寫條件判斷結(jié)構(gòu)拋出異常在語(yǔ)句執(zhí)行區(qū)編寫一下代碼 if . then raise 異常對(duì)象名稱 ; -拋出異常 end

54、 if; 步驟 3：異常捕獲在語(yǔ)句執(zhí)行區(qū)編寫 exception when 異常對(duì)象名稱 then .處理方式 . declare 精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 29 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 29 頁(yè)，共 40 頁(yè) - - - - - - - - - n number; -用來(lái)接收用戶輸入的數(shù)字 exp exception; -創(chuàng)建一個(gè)異常對(duì)象begin n:=&數(shù)字; -判斷 if n10 then ra

55、ise exp; -拋出異常 end if; dbms_output.put_line(n); exception when exp then dbms_output.put_line(數(shù)字范圍不再 110之內(nèi)); end; 8、oracle數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程、函數(shù)、數(shù)據(jù)包oracle 中的存儲(chǔ)過(guò)程oracle 中的 pl/sql語(yǔ)句塊，沒(méi)有名字，成為匿名的pl/sql塊，代碼重用性低。需要為 pl/sql塊起名字，起名后的pl/sql塊統(tǒng)稱為子程序。子程序的分類：存儲(chǔ)過(guò)程、自定義函數(shù)存儲(chǔ)過(guò)程語(yǔ)法： create or replace procedure 存儲(chǔ)過(guò)程名稱 as 精品學(xué)習(xí)資料 可選

56、擇p d f - - - - - - - - - - - - - - 第 30 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 30 頁(yè)，共 40 頁(yè) - - - - - - - - - pl/sql塊如何調(diào)用存儲(chǔ)過(guò)程兩種方式： 1、指令方式 - exec 存儲(chǔ)過(guò)程名稱 ; 2、使用 pl/sql塊調(diào)用 begin 存儲(chǔ)過(guò)程名稱 ; end; 帶參數(shù)的存儲(chǔ)過(guò)程兩大類： a) 帶輸入?yún)?shù) b) 帶輸出參數(shù) a) 帶輸入?yún)?shù)的存儲(chǔ)過(guò)程語(yǔ)法： create or replace procedure 存儲(chǔ)過(guò)

57、程名稱 (參數(shù)名稱 1 in 類型, . ,參數(shù)名稱 n 類型) as pl/sql塊b) 帶輸出參數(shù)的存儲(chǔ)過(guò)程語(yǔ)法： create or replace procedure 存儲(chǔ)過(guò)程名稱 (參數(shù)名稱 1 out 類型, . ,參數(shù)名稱 n out 類型) as pl/sql塊例子：顯示部門 10 的平均工資 (使用輸出參數(shù) ) 精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 31 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 31 頁(yè)，共 40 頁(yè) -

58、 - - - - - - - - create or replace procedure pro_demo3(avgsal out emp.sal%type) as begin select avg(sal) into avgsal from emp where deptno=10; end; 調(diào)用：只能使用 pl/sql塊 declare getsal emp.sal%type; begin pro_demo3(getsal); dbms_output.put_line(getsal); end; /1、輸入指定員工的員工編號(hào)，輸出其所在部門的其他姓名( 存儲(chǔ)過(guò)程、帶參數(shù)的游標(biāo)、輸入?yún)?shù))

59、思路： *1、使用 sql語(yǔ)句查詢 7788員工所在部門的其他員工信息 select * from emp where deptno=(select deptno from emp where empno=7788) and empno7788; *2、修改成 pl/sql塊 declare cursor mycur(eno emp.empno%type) is select * from emp where deptno=(select deptno from emp where empno=eno) and empnoeno; 精品學(xué)習(xí)資料 可選擇p d f - - - - - - - -

60、 - - - - - - 第 32 頁(yè)，共 40 頁(yè) - - - - - - - - -精品學(xué)習(xí)資料 可選擇p d f - - - - - - - - - - - - - - 第 32 頁(yè)，共 40 頁(yè) - - - - - - - - - erow emp%rowtype; tmp emp.empno%type; begin tmp:=&工號(hào); for erow in mycur(tmp) loop dbms_output.put_line(erow.ename); end loop; end; * 3、修改成為存儲(chǔ)過(guò)程 create or replace procedure pro_demo4(tmp emp.empno%type) as