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

1、Oracle 學(xué)問點(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)的目錄不答應(yīng)有中文或空格 Oracle 數(shù)據(jù)庫(kù)啟動(dòng)時(shí)必需開啟的后臺(tái)服務(wù) OracleOrahome90TNSListener 使第三方的軟件或語(yǔ)言拜望 OracleServiceETC Oracle 的實(shí)例 CRUD 增刪改查 留意：中的 ETC 是你起的實(shí)例的名字 Oracle 的開發(fā)工具 DOS 界面的開發(fā)平- 運(yùn)行 -sqlplus 臺(tái) Oracle 本身基于 DOS 的平臺(tái)

2、- 運(yùn)行 - sqlplusw Oracle Enterprise Manager Console PL/SQL 創(chuàng)建一個(gè)表的完整流程 創(chuàng)建一個(gè)數(shù)數(shù)庫(kù) 例子：創(chuàng)建一個(gè)數(shù)據(jù)庫(kù) ETC , 物理文件放在 F: ,初始化 1m , 自增 長(zhǎng)開啟 create tablespace etc datafile f:etc.dbf 第 1 頁(yè),共 40 頁(yè)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ù)

3、庫(kù)名稱 留意： 1,在 Oracle 中賬號(hào)和數(shù)據(jù)庫(kù) one2one 綁 定 2, Oracle 的密碼不答應(yīng)為純數(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 用戶 ; 撤銷權(quán)限 語(yǔ)法： revoke 權(quán)限名稱 from 用戶名 ; 在該帳號(hào)下創(chuàng)建表 Oracle 中的事務(wù)處理 第 2 頁(yè),共 40 頁(yè)Transacation 事務(wù) 特點(diǎn)：整體提交 commit 整體回滾 rollback 事務(wù)的四個(gè)特性

4、 原子性 不行分割 長(zhǎng)期性 - 當(dāng)數(shù)據(jù)整整寫入到數(shù)據(jù)庫(kù)物理文件中后, 該數(shù)據(jù)被 長(zhǎng)期 化 隔離性 - 事務(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ù)

5、和儲(chǔ)備過(guò)程相比,在于函數(shù)必需有返回值,而儲(chǔ)備過(guò)程只有輸出參數(shù)； 語(yǔ)法： create or replace function 函數(shù)名稱 return 返回類型 第 3 頁(yè),共 40 頁(yè)as PL/SQL 語(yǔ)句塊 例子：輸入一個(gè)員工工號(hào),返回該員工所在部門平均工資 create or replace function fun_demo1eno emp.empno%type return emp.sal%type as avgsal emp.sal%type; begin select avgsal into avgsal from empwhere empno=eno; return avgsa

6、l; end; 調(diào)用： select fun_demo17788 from dual; public int addint num1,int num2 int res = 0; res = num1+num2; return res; 單行函數(shù) 單值函數(shù) ：函數(shù)有且只有返回 1 個(gè)值 |- 字符函數(shù) substr instr 字符串連接函數(shù)： concat 第 4 頁(yè),共 40 頁(yè)concat 字符串 1, 字符串 2 左填充 /rpad Oracle 中字符連接操作符號(hào) | 字符串對(duì)齊函數(shù)： lpad - left padding 語(yǔ)法： lpad 字符串 , 預(yù)留位置 , 填充字符 SQL

7、: select lpadename,10, from emp; 字符串截取函數(shù)： substr - substring 語(yǔ)法： substr 字符串 , 截取的起始位置 , 截取個(gè)數(shù) substr 字符串 , 截取的起始位置 SQL: select substrename,1,3 frome emp; 字符查找函數(shù)： instr - inner string 語(yǔ)法： instr 字符串 , 待找的字符 , 查找的起始位置 , 顯現(xiàn) 次數(shù) SQL: select ename,instrename,T,1,1 from emp; 留意：當(dāng)返回值為 0 時(shí),字符不存在 函數(shù)： initcap -

8、initial captor 語(yǔ)法： initcap 字符串 SQL: select initcapename from emp; 函數(shù)： length 語(yǔ)法： length 字符串 SQL: select * from emp where lengthename=5; 函數(shù)： lower / upper 語(yǔ)法： lower 字符串 SQL: select lowerename from emp; 第 5 頁(yè),共 40 頁(yè)|- 日期函數(shù) sysdate add_months 函數(shù)： sysdate SQL: select sysdate from dual; dual- 無(wú)實(shí)際意 義,函 數(shù)測(cè)

9、試或其他測(cè)試使用 月份差： months_between 語(yǔ)法： months_between日期 1, 日期 2 SQL: select * from emp where months_betweensysdate,hiredate=144 函數(shù)： last_day 語(yǔ)法： last_day 日期數(shù)據(jù) SQL: select * from emp where last_dayhiredate-2=hiredate; 函數(shù)： add_months 語(yǔ)法： add_months時(shí)間 , 添加月份 SQL ：select add_monthssysdate,3 from dual; |- 轉(zhuǎn)換函數(shù)

10、 to_char to_date 轉(zhuǎn)換函數(shù) to_char 語(yǔ)法： to_char 時(shí)間數(shù)據(jù) , 制定格式 SQL: select to_charsysdate,yyyy-mm-dd hh24:mi:ss dddd day from dual; yyyy-mm-dd 年月日 第 6 頁(yè),共 40 頁(yè)hh24:mi:ss 時(shí)分秒 24 d 一周第幾天 ddd 一年的第幾天 day 星期幾 to_date: 將文本類型數(shù)據(jù)依據(jù)制定格式轉(zhuǎn)換成時(shí)間類型數(shù)據(jù) 語(yǔ)法： to_date 具體的時(shí)間字符數(shù)據(jù) , 時(shí)間字符串格式 to_date2022-01-19,yyyy-mm-dd 留意： Oracle 中

11、的 date 字段類型及其特殊 |- 數(shù)學(xué)函數(shù) abs ceil floor absn 確定值 ceiln 向上取值 ceilling 天花板 ceil1.1 - 2 floorn 向下取值 floor 地板 floor2.9 - 2 modm,n 求模取余 mod5,2 - 1 powerm,n m 的 n 次方 power2,8 -256 roundm,n 四舍五入 m 待處理數(shù)據(jù) n 精度 round3.1415,2 - truncm 整數(shù)截取 trunc3.1415 = 3 signm 符號(hào) sqrtm 平方根 |- 混合函數(shù) user nvl user : 返回當(dāng)前登錄賬號(hào) SQL

12、select user from dual; 第 7 頁(yè),共 40 頁(yè)nvl : 替空函數(shù) 語(yǔ)法： nvl 字符串 , 替換后的數(shù)據(jù) 留意：替換后的數(shù)據(jù)必需與該字段類型保持一樣 SQL select ename,nvlcomm,0 from emp; 分析函數(shù)：數(shù)據(jù)分析和挖掘功能 作用：主要用于排名使用 函數(shù)： rank Data Mining rank dense_rank 語(yǔ)法： rank over order by 字段名稱 SQL: select rank overorder by sal desc,ename,sal from emp; 如排名相同下一個(gè)將跳轉(zhuǎn) SQL: select

13、 dense_rank overorder by sal desc,ename,sal from emp; 連續(xù) 語(yǔ)法： rank 參數(shù) 1,參數(shù) 2 within grouporder by 字段 1,字段 2 SQL: select ename,rank2850,BLAKE within grouporder by sal,ename from emp; 分組函數(shù)：類似于 SQLServer 中的聚合函數(shù) sum avg count group by 分組關(guān)鍵字 having 條件 3, Oracle 數(shù)據(jù)庫(kù)的查詢 制定區(qū)間查詢 Oracle 中偽列： Oracle 為每張數(shù)據(jù)表 包括用戶

14、自定義創(chuàng)建的數(shù)據(jù)表 都 自動(dòng)創(chuàng)建兩個(gè)位列分別是： rowid, rownum 第 8 頁(yè),共 40 頁(yè)通過(guò)命令： SQL desc 表名 ; -查看表結(jié)構(gòu) rowid: 由 18 個(gè)字符組成唯獨(dú)標(biāo)識(shí)每一行 rownum: 行號(hào) 介紹 問題：使用 Oracle 分層查詢以數(shù)據(jù)結(jié)構(gòu)顯示 emp 表中的人員關(guān) 系 語(yǔ)法： select lpadename,level*5, from emp connect by prior empno=mgr start with mgr is null; 并集：查詢部門 10 的辦事員和部門 20 的經(jīng)理 關(guān)鍵字： union all select * from

15、emp where deptno=10 and job=CLERK union all select * from emp 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 中沒有的工作類型

16、 關(guān)鍵字： minus select job from emp where deptno=30 第 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 開頭創(chuàng)建的對(duì)象稱之為數(shù)據(jù)庫(kù)對(duì)象； 鎖定 / 解除用戶 語(yǔ)法： alter user 用戶名稱 account lock; 留意：該指令一般在 system 賬號(hào)下輸入 解除用戶鎖定 語(yǔ)法： alter user 用戶名稱 account unlock; S

17、QL alter user scott account unlock; 留意： Oracle 11g 中 scott 用戶授權(quán) / 撤銷 賬號(hào)是默認(rèn)鎖定的,需要進(jìn)行解鎖處理； grant 權(quán)限名稱 to 用戶名稱 ; 撤銷用戶權(quán)限 revoke 權(quán)限名稱 from 用戶名 ; 表 數(shù)據(jù)字段的類型 數(shù)字類型： number 語(yǔ)法： number 長(zhǎng)度 , 精度 number5,2 數(shù)字長(zhǎng)度為 3,精度為 2范疇： 第 10 頁(yè),共 40 頁(yè)number5 數(shù)字長(zhǎng)度為 5,精度為 0 范疇： -99999 99999 number 精度默認(rèn)為 0 范疇： -32767 32768 字符類型： va

18、rchar2 可變長(zhǎng)度 char 字符類型 語(yǔ)法： varchar2 長(zhǎng)度 日期類型： date long 數(shù)據(jù)類型： 該字段最大儲(chǔ)備空間為 2GB,該字段不答應(yīng)添加索 引 LOB 數(shù)據(jù)類型：該字段最大儲(chǔ)備空間為 4GB,該字段不答應(yīng)添加索引 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 和

19、 telephone 語(yǔ)法： alter table 表名 add 字段名稱 類型 ; 刪除表字段 address 語(yǔ)法： alter table 表名 drop column 字段名稱 ; 修改表中已有字段的類型 語(yǔ)法： alter table 修改表名稱 表名 modify 字段名稱 新類型 ; 語(yǔ)法： rename 原表名 to 新表名 ; 約束 完整性約束 = 精確性 + 一樣性 約束的四大分類： 實(shí)體完整性約束 行約束 - 盡量削減數(shù)據(jù)表中數(shù)據(jù)的冗余 重復(fù)的數(shù) 據(jù) 技術(shù)實(shí)施：主鍵約束,唯獨(dú)約束 語(yǔ)法： alter table 表名 add primary key 字段名稱 ; alt

20、et table 表名 add unique 字段名稱 ; 添加非空約束 語(yǔ)法： alter table 表名 modify 字段名稱 not null; 域完整性約束 列約束 - 達(dá)到數(shù)據(jù)的精確性,把握數(shù)據(jù)的大小或范 圍或格式 技術(shù)實(shí)施： check 檢查約束 alter table 表名 add constraint 約束名稱 check 條件 ; 引用完整性約束 表間約束 - 技術(shù)實(shí)施：外鍵約束 達(dá)到數(shù)據(jù)的一樣性 添加外鍵約束 alter table 外鍵表 add constraint 約束名稱 foreign key 外鍵字 段 references 主鍵表 主鍵字段 ; 刪除外鍵約

21、束 語(yǔ)法： alter table 表名 drop constraint 約束名稱 ; 刪除匿名約束 語(yǔ)法： alter table 表名 modify 字段名稱 null; 自定義完整性約束 - 以上三種數(shù)據(jù)庫(kù)內(nèi)置約束不能中意開發(fā)人 員的 需求是,需要開發(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

22、 起始數(shù)字 increment by 增長(zhǎng)量 ; 用法：序列對(duì)象通過(guò)兩個(gè)重要的屬性進(jìn)行拜望取值 .nextval - next value - 下一個(gè)值 第 13 頁(yè),共 40 頁(yè)例子： select from dual; .currval - current value - 當(dāng)前值 例子： select 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 usersuserid number2,username varchar210; create sequence seq_users start

23、with 1 increment by 1; insert into users valuesseq_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ù)的拜望安全性 語(yǔ)法： create or replace view 視圖名稱 as 復(fù)雜的 SQL 查詢語(yǔ) 句 視圖是一張?zhí)摂M的數(shù)據(jù)表,在 Table 對(duì)象中不存在,只存在于內(nèi)存中 留意：如視圖由 1 張基表組成,修改視圖將會(huì)修改基表數(shù)據(jù),如視圖 由多 張表

24、組成,就不會(huì)修改基表數(shù)據(jù) 同義詞 第 14 頁(yè),共 40 頁(yè)作用：Oracle 中的同義詞供應(yīng)各種數(shù)據(jù)庫(kù)對(duì)象 表 的別名, 目的在于 提高數(shù)據(jù)表拜望的安全性,特殊多用戶并發(fā)拜望時(shí)； 語(yǔ)法： create synonym 表的別名 for 表名 ; SQL create synonym bak1 for emp; 擴(kuò)展：公有同義詞 public 語(yǔ)法： create public synonym 別名 for 表名 ; 索引 作用：提高 SQL 查詢語(yǔ)句依據(jù)制定字段查詢的效 率 語(yǔ)法： create index 索引的名稱 on 數(shù)據(jù)表 字段 例子：為 hiredate 字段添加索引,提高按日期

25、查詢的 SQL 語(yǔ)句的 效 率 create index idx_hire on emphiredate 原理：空間換效率 SQL 語(yǔ)句索引優(yōu)化規(guī)章 不帶 where 條件的 SQL 語(yǔ)句確定不能使用索 引 在 where 條件中有索引的字段不能使用函數(shù) 例子：假設(shè)我們查詢?cè)?1987 年參加工作的全部員工 SQL select * from emp where to_charhiredate,yyyy=1987; SQL select * from emp where hiredate=to_date1987,yyyy; 在 where 條件中有索引的字段不能參加運(yùn)算 例子：查詢?cè)?1000

26、0 天以前參加工作的員工信息 SQL select * from emp where sysdate-hiredate10000; 第 15 頁(yè),共 40 頁(yè)SQL select * from emp where hiredate select from empe,dept d where 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)前用戶與權(quán)力查看的數(shù)據(jù)庫(kù)對(duì)象 DBA_xxxx ： 表示數(shù)據(jù)

27、中所擁有的全部對(duì)象,只有在超級(jí)治理員級(jí)別下 才可查看； xxxx ：代表數(shù)據(jù)庫(kù)對(duì)象的復(fù)數(shù)形式,例如： tables indexes . 第 16 頁(yè),共 40 頁(yè)5, Oracle 數(shù)據(jù)庫(kù)的多表查詢 等連接： select emp.ename,dept.dname from 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; 分析：部門為主顯示字段

28、, 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) ： Oracle 自動(dòng)應(yīng)以一個(gè)隱式游標(biāo)名稱為 SQL ,該 游標(biāo)不被程序員把握,自動(dòng)開啟,操作及終止； 顯式游標(biāo) ： 可供程序員自己創(chuàng)建及操作 |- 靜態(tài)游標(biāo) |- 動(dòng)態(tài)游標(biāo) 靜態(tài)游標(biāo) loop 循周游標(biāo) / for 循周游標(biāo) 第 17 頁(yè),共 40 頁(yè)=loop 循周游標(biāo) = 步驟 1：創(chuàng)建一個(gè)游標(biāo) cursor

29、 游標(biāo)名稱 is SQL 查詢語(yǔ)句 ; 步驟 2：開啟游標(biāo) open 游標(biāo)名稱 ; 到游標(biāo)中 - 執(zhí)行定義的 SQL 查詢語(yǔ)句并將結(jié)果集合存放 步驟 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%t

30、ype; 第 18 頁(yè),共 40 頁(yè)begin -步驟 2：開啟游標(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_lineen; end loop; -步驟 4：關(guān)閉游標(biāo) close mycur; end; =for 循周游標(biāo) = declare -步驟 1：創(chuàng)建一個(gè)游標(biāo) cursor mycur is select * from emp where deptno=10; erow emp%rowtype; begi

31、n -步驟 3：使用 for 循環(huán)遍歷游標(biāo)中的數(shù)據(jù)并進(jìn)行相應(yīng)處理 for erow in mycur loop dbms_output.put_lineerow.ename; 第 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：打開游標(biāo)并且綁定 SQL 語(yǔ)句 open 游標(biāo)名稱 for SQL 查詢語(yǔ)句 步驟 4：使用循環(huán)遍歷游標(biāo)中的數(shù)據(jù)并進(jìn)行處理 l

32、oop fetch 游標(biāo)名稱 into 變量 ; exit when 游標(biāo)名稱 %notfound . 數(shù)據(jù)處理 . end loop; 步驟 5：重復(fù) 34 步驟 6：關(guān)閉游標(biāo) 第 20 頁(yè),共 40 頁(yè)close 游標(biāo)名稱 ; 例子：顯示部門 30 的員工姓名及 dept 表中全部部門名稱 declare - 聲明一個(gè)游標(biāo)類型 type cur is ref cursor; - 聲明游標(biāo)變量 mycur cur; - 聲明變量 erow emp%rowtype; drow dept%rowtype; begin - 打開游標(biāo) open mycur for select * from emp

33、 where deptno=30; loop fetch mycur into erow; exit when mycur%notfound; dbms_output.put_lineerow.ename; end loop; dbms_output.put_line=; - 打開游標(biāo) open mycur for select * from dept; loop 第 21 頁(yè),共 40 頁(yè)fetch mycur into drow; exit when mycur%notfound; dbms_output.put_linedrow.dname; end loop; - 關(guān)閉游標(biāo) close

34、 mycur; end; 游標(biāo)全部屬性 游標(biāo)名稱 %屬性名稱 屬性名稱 含義 1notfound 當(dāng)游標(biāo)中沒有遍歷的數(shù)據(jù)時(shí)返回 1found 當(dāng)游標(biāo)中存在有遍歷的數(shù)據(jù)時(shí)返回 isopen 當(dāng)游標(biāo)開啟狀態(tài)時(shí)返回 1rowcount 返回游標(biāo)影響行數(shù) 7, Oracle 數(shù)據(jù)的 PL/SQL PL/SQL 塊 語(yǔ)法格式： declare 聲明 . 變量聲明區(qū) begin 程序開頭 . 執(zhí)行語(yǔ)句區(qū) exception 反常處理部分 第 22 頁(yè),共 40 頁(yè). end; 程序終止 ; 變量的聲明 標(biāo)量標(biāo)識(shí)符,不區(qū)分大小寫,先聲明再使用 變量聲明的語(yǔ)法結(jié)構(gòu)：標(biāo)量名稱 類型 ; 例子： declare

35、eno number4; edate date; begin end; 屬性引用類型 |- 字段引用類型 例子： declare eno emp.ename%type; |- 行引用類型 例子： declare erow emp%rowtype; 變量的賦值 四種方式： 聲明時(shí)賦值 第 23 頁(yè),共 40 頁(yè)賦值運(yùn)算符 “:= ” 例子：聲明一個(gè)變量賦值為 2 declare num1 number2:=2; . begin . end; 執(zhí)行區(qū)賦值 例子：聲明一個(gè)變量賦值為 2 declare num1 number2; . begin num1:=2; . end; 對(duì)話框賦值 - 程序與

36、用戶之間進(jìn)行數(shù)據(jù)交互 例子：聲明一個(gè)變量賦值為 2 declare num1 number2; . 第 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 表 中 declare simth 的工資復(fù)制到變量 esal 中 esal emp.sal%type; begin select sal into esal from emp where ename=SMITH; end; Oracle 的 PL/

37、SQL 中屏幕輸出語(yǔ) 句 語(yǔ)法： dbms_output.put_linexxxxxxx; 將信息輸出并換行 dbms_output.putxxxxxxx; 輸出不換行 留意： Oracle 默認(rèn)關(guān)閉屏幕輸出功能,需要開發(fā)人員輸入指令將其功能 開啟 SQL set serveroutput on; PL/SQL 中的反常處理 語(yǔ)法： exception 第 25 頁(yè),共 40 頁(yè)when 反常名稱 then . 處理方式 . 反常： others - 任何反常都可捕獲 執(zhí)行語(yǔ)句部分 循環(huán)的使用： for while loop for 語(yǔ)法結(jié)構(gòu)： for 循環(huán)變量 in 起始數(shù)字 . 終止數(shù)字

38、loop . 循環(huán)體 . end loop; 例子 1：顯示數(shù)字 101 reverse declare i number; begin for i in reverse 1.10 loop dbms_output.put_linei; end loop; end; 例子 1：顯示乘法口訣 declare i number; 第 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.putj|*|i|=|i*j| ; end loop; - 換行 dbms

39、_output.put_line ; end loop; end; while 循環(huán) 語(yǔ)法： while 進(jìn)入條件 loop . 循環(huán)體 . end loop; loop 循環(huán) 語(yǔ)法： loop exit when 推出條件 ; 第 27 頁(yè),共 40 頁(yè). 循環(huán)體 . end loop; 條件判定 語(yǔ)法： if 條件 then elsif 條件 then else end if; 例子：模擬一個(gè)登錄結(jié)構(gòu),用戶名為 declare username varchar210; password varchar210; begin - 用戶輸入賬號(hào)及密碼 username:=& 賬號(hào) ; passw

40、ord:=& 密碼 ; - 規(guī)律判定 admin 密碼為 chinasoft if username=admin and password=chinasoft then dbms_output.put_line 歡迎登錄： |username; else dbms_output.put_line end if; end; 處理自定義反常 賬號(hào)或密碼錯(cuò)誤！ ; 例子：如用戶輸入的數(shù)字不再 110 之內(nèi)就拋出反常 步驟 1：創(chuàng)建一個(gè)反常對(duì)象 在變量聲明區(qū)輸入一下代碼 exp exception; - 聲明一個(gè)反常對(duì)象,名稱為 exp 步驟 2：編寫條件判定結(jié)構(gòu)拋出反常 在語(yǔ)句執(zhí)行區(qū)編寫一下代碼 i

41、f . then raise 反常對(duì)象名稱 ; - 拋出反常 end if; 步驟 3：反常捕獲 在語(yǔ)句執(zhí)行區(qū)編寫 exception when 反常對(duì)象名稱 then . 處理方式 . declare 第 29 頁(yè),共 40 頁(yè)n number; - 用來(lái)接收用戶輸入的數(shù)字 exp exception; - begin n:=& 數(shù)字 ; - 判定 創(chuàng)建一個(gè)反常對(duì)象 if n10 then raise exp; - 拋出反常 end if; dbms_output.put_linen; exception when exp then end; dbms_output.put_line 數(shù)字范

42、疇不再 110 之內(nèi) ; 8, Oracle 數(shù)據(jù)庫(kù)中的儲(chǔ)備過(guò)程,函數(shù),數(shù)據(jù)包 Oracle 中的儲(chǔ)備過(guò)程 Oracle 中的 PL/SQL 語(yǔ)句塊,沒出名字,成為匿名 的 PL/SQL 塊,代碼重 用 性低；需要為 PL/SQL 塊起名字,起名后 的 子程序的分類：儲(chǔ)備過(guò)程,自定義函數(shù) 儲(chǔ)備過(guò)程 語(yǔ)法： create or replace procedure as PL/SQL 塊統(tǒng)稱為子程 序； 儲(chǔ)備過(guò)程名稱 第 30 頁(yè),共 40 頁(yè)P(yáng)L/SQL 塊 如何調(diào)用儲(chǔ)備過(guò)程 兩種方式： 1,指令方式 - exec 儲(chǔ)備過(guò)程名稱 ; 2,使用 PL/SQL 塊調(diào)用 begin 儲(chǔ)備過(guò)程名稱 ;

43、end; 帶參數(shù)的儲(chǔ)備過(guò)程 兩大類： a 帶輸入?yún)?shù) b 帶輸出參數(shù) a 帶輸入?yún)?shù)的儲(chǔ)備過(guò)程 語(yǔ)法： create or replace procedure 儲(chǔ)備過(guò)程名稱 參數(shù)名稱 1 in 類型 , . , 參數(shù)名稱 n 類型 as PL/SQL 塊 b 帶輸出參數(shù)的儲(chǔ)備過(guò)程 語(yǔ)法： create or replace procedure 類型 , . , 參數(shù)名稱 n out 類型 as PL/SQL 塊 儲(chǔ)備過(guò)程名稱 參數(shù)名稱 1 out 例子：顯示部門 10 的平均工資 使用輸出參數(shù) 第 31 頁(yè),共 40 頁(yè)create or replace procedure pro_demo3

44、avgsal out emp.sal%type as begin select avgsal into avgsal from emp where deptno=10; end; 調(diào)用： 只能使用 PL/SQL 塊 declare getsal emp.sal%type; begin pro_demo3getsal; dbms_output.put_linegetsal; end; /1 ,輸入指定員工的員工編號(hào),輸出其所在部門的其他姓名 儲(chǔ)備過(guò) 程,帶參數(shù)的游標(biāo),輸入?yún)?shù) 思路： *1 ,使用 SQL 語(yǔ)句查詢 7788 員工所在部門的其他員工信 息 select * from emp wh

45、ere deptno=select deptno from emp where empno=7788 and empno7788; *2 ,修改成 PL/SQL 塊 declare cursor mycureno emp.empno%type is select * from emp where deptno=select deptno from emp where empno=eno and empnoeno; 第 32 頁(yè),共 40 頁(yè)erow emp%rowtype; tmp emp.empno%type; begin tmp:=& 工號(hào) ; for erow in mycurtmp l

46、oop dbms_output.put_lineerow.ename; end loop; end; * 3 ,修改成為儲(chǔ)備過(guò)程 create or replace procedure pro_demo4tmp emp.empno%type as cursor mycureno emp.empno%type is select * from emp where deptno=select deptno from emp where empno=eno and empnoeno; erow emp%rowtype; begin for erow in mycurtmp loop dbms_out

47、put.put_lineerow.ename; end loop; end; *4 ,調(diào)用 第 33 頁(yè),共 40 頁(yè)exec pro_demo47788; /2 ,輸出制定員工所在部門的平均工資 儲(chǔ)備過(guò)程, 輸入?yún)?shù), 輸出參數(shù) 思路： *1 ,編寫 SQL 語(yǔ) 句 select avgsal from empwhere deptno=select deptno from empwhere empno=7788 * 2 , PL/SQL 語(yǔ) 句 declare eno emp.empno%type; esal emp.sal%type; begin eno:=& 編號(hào) ; select av

48、gsal into esal from emp where deptno=select deptno from emp where empno=eno; dbms_output.put_lineesal; end; * 3 ,修改為儲(chǔ)備過(guò)程 create or replace procedure pro_demo5eno in emp.empno%type,esal out emp.sal%type as begin select avgsal into esal from emp where deptno=select 第 34 頁(yè),共 40 頁(yè)deptno from emp where e

49、mpno=eno; end; * 4 ,調(diào)用 declare tmp emp.sal%type; begin pro_demo57788,tmp; dbms_output.put_linetmp; end; 程序包 程序包是一個(gè)函數(shù)或儲(chǔ)備過(guò)程的集合； 程序包的結(jié)構(gòu)：第一部分：程序包的聲明 create or replace package 包名稱 as 儲(chǔ)備過(guò)程的聲明 ; 自定義函數(shù)的聲明 ; end; 其次部分：程序包主體 create or replace package body 包名稱 as 儲(chǔ)備過(guò)程的實(shí)現(xiàn) ; 第 35 頁(yè),共 40 頁(yè)自定義函數(shù)的實(shí)現(xiàn) ; 例子：完成對(duì) EMP 表進(jìn)

50、行增刪改的操 作 part1: create or replace package emp_pack procedure add_empenoemp.empno%type,enmemp.ename%type,dno emp.deptno%type; procedure update_empeno emp.empno%type,esal emp.sal%type; procedure delete_empeno emp.empno%type; end; part2: create or replace package body emp_pack as - 添加 procedure add_empenoemp.empno%type,enmemp.ename%type,dno emp.deptno%type as