游標(biāo) cursor定義 游標(biāo) 實(shí)際上是一個指針 指向 結(jié)果集的每一行數(shù)據(jù)初始的時候 指向第一行數(shù)據(jù)作用 處理多行數(shù)據(jù) ------for select語法declarecursor 游標(biāo)名 is select語句;-------1 聲明游標(biāo)beginopen 游標(biāo)名;-----------------------2 打開游標(biāo)fetch 游標(biāo)名 into 變量1 ,變量2......;---3. 使用游標(biāo) 提取游標(biāo)****----1 提取數(shù)據(jù) (2) 交給變量 3 指針下移close 游標(biāo)名; ---------------------4 關(guān)閉游標(biāo)end;select * from emp例題 打印輸出 emp表中所有員工的姓名 崗位declarecursor c1 is select ename,job from emp ; -----1v_ename emp.ename%type;V_job emp.job%type;beginopen c1 ; -----------2fetch c1 into v_ename,V_job; ----3dbms_output.put_line(v_ename||V_job);close c1; -----------------4end;declarecursor c1 is select ename,job from emp ; -----1v_ename emp.ename%type;V_job emp.job%type;beginopen c1 ; -----------2fetch c1 into v_ename,V_job; ----3dbms_output.put_line(v_ename||V_job);close c1; -----------------4end;注意 游標(biāo) 是需要配合 循環(huán)來使用的loop例題 打印輸出 emp表中所有員工的姓名 崗位declarecursor c1 is select ename,job from emp ; ------1v_ename varchar2(20);V_job emp.job%type;beginopen c1 ; -------------------------------------2loopfetch c1 into V_ename,V_job ; -------------------3exit when c1%notfound;dbms_output.put_line(V_ename||v_job);end loop;close c1;end;游標(biāo)的四個屬性1. 游標(biāo)名%found --------------游標(biāo)有值的時候返回 true2. 游標(biāo)名%notfound ------------游標(biāo)沒有值的時候 返回 true3. 游標(biāo)名%isopen -------判斷游標(biāo)是否打開 如果是 則返回 true4. 游標(biāo)名%rowcount -------統(tǒng)計游標(biāo)處理的行數(shù) ------返回的是數(shù)值練習(xí)例題 打印輸出 emp表中所有員工的姓名 崗位--whiledeclarecursor c1 is select ename,job from emp ;V_ename emp.ename%type;V_job emp.job%type;beginopen c1 ;fetch c1 into V_ename,V_job ;while c1%found ----------游標(biāo)有值的時候loopdbms_output.put_line(V_ename||V_job);fetch c1 into V_ename,V_job ;end loop;close c1;end;for 循環(huán)配合游標(biāo)使用1.for 循環(huán)會 自動的打開和關(guān)閉游標(biāo)2. for 循環(huán)會 自動的 fetch 游標(biāo)例題 打印輸出 emp表中所有員工的姓名 崗位--fordeclarecursor c1 is select ename,job from emp ;----1 聲明游標(biāo)beginfor i in c1 ---游標(biāo)loopdbms_output.put_line(i.ename||i.job);end loop;end;等價寫法declarebeginfor i in (select ename,job from emp)loopdbms_output.put_line(i.ename||i.job);end loop;end;------------------------------------練習(xí)打印輸出 員工的姓名崗位薪資部門編號部門名稱以及部門平均工資--必須使用游標(biāo) ---3種方法 loop while for--------------------------------------------------------------------有名塊 有名字的 是可以永久保存到數(shù)據(jù)庫中隨時拿來調(diào)用 比如 to_date函數(shù) function--特點(diǎn) 函數(shù) 有 且只有一個返回值自定義函數(shù)語法create [or replace] function 函數(shù)名[(形參1 形參類型,形參2 形參類型....)]return 返回值的類型------------------------------------------------以上的所有類型 不能寫長度is|as--聲明部分begin--執(zhí)行部分 核心部分 實(shí)現(xiàn)函數(shù)的過程的部分return 最終的值 ; ----函數(shù)最終的返回結(jié)果end ;例題 創(chuàng)建沒有參數(shù)的函數(shù) 返回上個月的最后一天create or replace function fu_97 --------------------名字 有意義return date ----返回值的類型isV_d date; -----變量beginselect add_months(last_day(sysdate),-1)into V_dfrom dual;return v_d;end;---有名塊select fu_97 -----使用函數(shù)的時候 里面的參數(shù)的個數(shù) 順序 屬性要和創(chuàng)建時形參一直from dual例題 創(chuàng)建一個有參數(shù)的函數(shù) 要求 返回任意一個日期的上個月的最后一天create or replace function fu_97( v_d date ) ---形參return dateisV_a date; ----變量beginselect add_months(last_day( V_d ) ,-1) into V_a from dual;return v_a;end;select fu_97( to_date(2000/3/1,yyyy/mm/dd) ) from dual;練習(xí) 創(chuàng)建一個函數(shù)要求 傳入一個員工編號 返回該員工的部門的平均工資create or replace function fu_97(v_empno number)return numberisv_deptno number;V_avg number;beginselect deptno into V_deptno from emp where empnoV_empno;select avg(sal) into V_avg from emp where deptnoV_deptno;return v_avg;end;select ename,fu_97(7566) from empselect fu_97(7566) from dual;CREATE or replace FUNCTION fu_avg_sal(V_d number)return NUMBERisV_a number;BEGINSELECT AVG(b.sal)into V_aFROM emp aINNER JOIN emp bon a.deptno b.deptnowhere a.empno v_d;return V_a;end;SELECT fu_avg_sal(7566) FROM dual;練習(xí) 創(chuàng)建一個函數(shù) 傳入一個員工編號如果該員工的工資等級是 1 則返回 低等級 2-3 中等級 4-5 高等級create or replace function fu_97 (v_empno number)return varchar2isV_grade number;beginselect gradeinto V_gradefrom empleft join salgradeon sal between losal and hisalwhere empnoV_empno;if v_grade 1 thenreturn 低等級;elsif v_grade between 2 and 3 thenreturn 中等級;elsif v_grade in(4,5) thenreturn 高等級;end if;end;select fu_97(7566) from dual;----------------------------------------------------存儲過程 --有名塊 ---數(shù)據(jù)庫對象之一是將 任務(wù) 語句 存儲起來 隨時拿來調(diào)用---函數(shù) 有且只有一個返回值 ----返回值---存儲過程 把過程存儲起來 ----沒有返回值創(chuàng)建存儲過程語法create [or replace ] procedure (形參1 形參類型,形參2 形參類型)------------------以上類型不能寫長度isbeginend;例題 創(chuàng)建一個存儲過程 傳入一個員工編號 打印輸出該員工的姓名create or replace procedure sp_97(v_empno number)isV_ename emp.ename%type;beginselect ename into V_ename from emp where empnoV_empno;dbms_output.put_line(V_ename);end;調(diào)用存儲過程1. call 存儲過程();2. 用 程序塊 調(diào)用存儲過程declarebegin存儲過程(); ----調(diào)用存儲過程end;call sp_97(7566); --調(diào)用的時候 參數(shù)的個數(shù)順序?qū)傩院蛣?chuàng)建時一致declarebeginsp_97(7839);end;練習(xí) 創(chuàng)建一個存儲過程 傳入 員工編號姓名崗位薪資入職日期以及部門編號要求 將傳入的參數(shù) insert 插入到 emp表中create or replace procedure sp_97(V_empno number,V_ename varchar2,V_job varchar2,V_hiredate date,V_sal number,V_deptno emp.deptno%type)isbegininsert into emp (empno,ename,job,sal,deptno,hiredate)values (V_empno,V_ename,V_job,V_sal,V_deptno,V_hiredate);end;call sp_97(3344,馬德華,豬八戒,sysdate,1,10)select * from emp練習(xí) 1 創(chuàng)建一個函數(shù)函數(shù)函數(shù) 要求 傳入部門編號 返回該部門的平均工資create or replace function fu_97(V_deptno number)return numberisV_avg number;beginselect avg(sal) into V_avg from emp where deptnoV_deptno;return V_avg;end;select fu_97(10) from dual;2.創(chuàng)建一個存儲過程 傳入一個員工編號 如果 該員工的工資 高于自己部門平均工資則降薪200 低于 漲薪200 等于 不變要求 1. 必須利用第一題的函數(shù) 2. 打印輸出漲薪 前后的薪資create or replace procedure sp_97(v_empno number)isV_sal number;V_d number;V_sal1 number;beginselect sal,deptno into V_sal,V_d from emp where empnoV_empno;if V_sal fu_97(v_d ) thenupdate emp set salsal-200 where empnoV_empno returning sal into V_sal1;elsif V_sal fu_97(v_d) thenupdate emp set salsal200 where empnoV_empno returning sal into V_sal1;elsenull; --什么都不做end if;dbms_output.put_line(v_sal || v_sal1);end;call sp_97(7566);----------------------------------------------------------存儲過程的三種形參1 輸入型形參 in ----默認(rèn)2. 輸出型形參 out3. 輸入輸出型形參 in out1 輸入型形參 in ----默認(rèn)create or replace procedure sp_97(v_empno [in] number)isV_sal number;V_d number;V_sal1 number;beginselect sal,deptno into V_sal,V_d from emp where empnoV_empno;if V_sal fu_97(v_d ) thenupdate emp set salsal-200 where empnoV_empno returning sal into V_sal1;elsif V_sal fu_97(v_d) thenupdate emp set salsal200 where empnoV_empno returning sal into V_sal1;elsenull; --什么都不做end if;dbms_output.put_line(v_sal || v_sal1);end;2. 輸出型形參 out例題 創(chuàng)建一個存儲過程 插入一個員工編號 傳出一個員工姓名--例題 創(chuàng)建一個存儲過程 傳入一個員工編號 打印輸出該員工的姓名create or replace procedure sp_97( V_empno in number ,V_ename out varchar2 )isbeginselect ename into V_ename from emp where empnov_empno;end;call sp_97( 7566,變量 ); ------不能用call 調(diào)用declarea varchar2(20);beginsp_97(7566 , a );---接收了 返回的姓名 adbms_output.put_line(a);---a 是變量end;--可以通過 out 型形參 返回值 -----存儲過程也可以有返回值存儲過程和函數(shù)區(qū)別函數(shù)有且只有一個返回值 存儲過程可以通過 out 輸出型形參 有多個返回值練習(xí) 傳入員工編號 傳入該員工的工資等級create or replace procedure sp_97(V_empno in number,v_grade out number)isbeginselect grade into V_gradefrom empleft join salgradeon sal between losal and hisalwhere empnoV_empno;end;declarea number;beginsp_97(7566,a);dbms_output.put_line(a);end;3 輸入輸出型形參 in out --了解例題傳入員工編號 輸出該員工的姓名create or replace procedure sp_97( v_a in out emp%rowtype )isbeginselect ename into v_a.ename from emp where empnov_a.empno;end;declarev_b emp%rowtype; ------V_a 個數(shù) 順序 屬性 完全一致beginv_b.empno:7566;sp_97( v_b );dbms_output.put_line(v_b.ename);end;------------------------------------------------存儲過程結(jié)束# Oracle PL/SQL 練習(xí)題10道中等難度約束要求1. 允許存儲過程、自定義函數(shù)、IF判斷、CASE、FOR/WHILE循環(huán)2. 禁止觸發(fā)器、異常處理塊(EXCEPTION)3. 環(huán)境基于經(jīng)典emp、dept表題目可直接在SCOTT用戶下運(yùn)行不需要自建業(yè)務(wù)表 說明函數(shù)必須有返回值存儲過程無返回值可使用IN/OUT參數(shù)不許寫EXCEPTION部分。## 題目1存儲過程?IF判斷編寫存儲過程p_check_sal傳入員工編號p_empno。查詢該員工工資- 工資大于3000輸出員工XXX工資偏高- 工資1500~3000輸出員工XXX工資正常- 小于1500輸出員工XXX工資偏低要求使用DBMS_OUTPUT打印結(jié)果。## 題目2函數(shù)?IF編寫函數(shù)f_get_job_level接收崗位p_job返回崗位等級數(shù)字- PRESIDENT → 1- MANAGER →2- ANALYST →3- 其余崗位返回4。## 題目3存儲過程?WHILE循環(huán)編寫存儲過程p_print_num傳入數(shù)字p_n使用**WHILE循環(huán)**打印1~p_n之間所有偶數(shù)。## 題目4函數(shù)?FOR循環(huán)編寫函數(shù)f_sum_even接收入?yún)_max使用FOR循環(huán)計算1~p_max所有偶數(shù)之和返回總和。## 題目5存儲過程?IF 查詢 OUT參數(shù)創(chuàng)建存儲過程p_dept_stats入?yún)⒉块T編號p_deptno兩個OUT參數(shù)o_emp_count(部門人數(shù))、o_avg_sal(部門平均工資)。邏輯如果部門人數(shù)大于5則把平均工資上浮10%賦值給o_avg_sal否則保持原平均工資。## 題目6函數(shù)?CASE判斷編寫函數(shù)f_sal_tax傳入工資p_sal使用CASE表達(dá)式計算模擬個稅并返回- sal1000扣稅0- 1000sal2000扣5%- 2000sal3500扣10%- sal3500扣15%返回扣稅金額。## 題目7存儲過程?FOR循環(huán) IF嵌套存儲過程p_sal_update_loop傳入部門號p_deptno。遍歷該部門全部員工FOR循環(huán)游標(biāo)for- 如果崗位是MANAGER工資增加200- 如果崗位是CLERK工資增加100其他崗位工資不變。執(zhí)行update更新表。 提示使用FOR rec IN (select empno,job,sal from emp where deptnop_deptno) LOOP禁止顯式聲明cursor。## 題目8函數(shù)?循環(huán)判斷編寫函數(shù)f_count_high_sal入?yún)⒉块T編號p_deptno統(tǒng)計該部門工資大于2500的員工人數(shù)返回統(tǒng)計數(shù)量。使用FOR循環(huán)遍歷不允許直接count聚合一步返回結(jié)果必須循環(huán)逐個判斷計數(shù)。## 題目9存儲過程?多條件IFOUT輸出字符串存儲過程p_emp_info輸入員工編號p_empno輸出OUT字符串o_result。拼接信息姓名:xxx崗位:xxx附加規(guī)則- 入職早于1982年追加[老員工]- 工資2800追加[高薪]。## 題目10綜合函數(shù)調(diào)用存儲過程IF循環(huán)1. 復(fù)用第6題函數(shù)f_sal_tax2. 創(chuàng)建存儲過程p_show_tax_list(p_deptno number)使用FOR循環(huán)遍歷該部門所有員工調(diào)用f_sal_tax得到每個人扣稅DBMS_OUTPUT打印姓名:xxx工資:xxx扣稅:xxx。---