Oracle資料庫之 PL SQL 學習筆記

1、定義基本變數： 2、引用型的變數： set serveroutput on declare pename emp.ename%type; psal emp.sal%type; begin select ename,sal into pename,psal from emp where empno ...

1、定義基本變數：

2、引用型的變數： set serveroutput on declare pename emp.ename%type; psal emp.sal%type; begin select ename,sal into pename,psal from emp where empno='7521'; dbms_output.put_line(pename||'的薪水是'||psal); end; / 3、記錄型變數： set serveroutput on declare emp_rec emp%rowtype; begin select * into emp_rec from emp where empno='7698'; dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal); end; / 4、if語句的使用 /* 判斷用戶從鍵盤的輸入 */ set serveroutput on --接受一個鍵盤輸入 --num:地址值，含義是在該地址上保存了輸入的值 accept num prompt'請輸入一個數字'; declare --定義變數從鍵盤的輸入 pnum number:=&num; begin if pnum=0 then dbms_output.put_line('您輸入的是0'); elsif pnum=1 then dbms_output.put_line('您輸入的是1'); elsif pnum=2 then dbms_output.put_line('你輸入的是2'); else dbms_output.put_line('其他'); end if; end; / 5、while迴圈： set serveroutput on declare pnum number := 1; begin while pnum <= 10 loop dbms_output.put_line(pnum); pnum := pnum + 1; end loop; end; / 6、loop迴圈 set serveroutput on declare pnum number:=1; begin loop exit when pnum>10; dbms_output.put_line(pnum); pnum:=pnum+1; end loop; end; / 7、for迴圈 set serveroutput on declare pnum number:=1; begin for pnum in 1..10 loop dbms_output.put_line(pnum); end loop; end; / （推薦使用loop迴圈） 8、游標的使用 --查詢並列印員工的姓名和薪水 set serveroutput on /* 游標的屬性 %found:游標找到記錄 %notfound：游標找不到記錄 */ declare --定義一個游標 cursor cemp is select ename,sal from emp; --為游標定義對應的變數 pename emp.ename%type; psal emp.sal%type; begin --打開游標 open cemp; loop --取一條記錄 fetch cemp into pename,psal; exit when cemp %notfound; dbms_output.put_line(pename||'的薪水是'||psal); end loop; --關閉游標 close cemp; end; / 9、實例---給員工漲工資： set serveroutput on declare cursor cemp is select empno,perjob from emp; pempno emp.empno%type; pjob emp.perjob%type; begin open cemp; loop fetch cemp into pempno,pjob; exit when cemp %notfound; if pjob='PRESIDENT' then update emp set sal=sal+1500 where empno=pempno; elsif pjob='ANALYST' then update emp set sal = sal+1000 where empno=pempno; elsif pjob='SALESMAN' then update emp set sal = sal+500 where empno=pempno; else update emp set sal = sal+300 where empno=pempno; end if; end loop; close cemp; commit;（如果update了數據，需在後面加上commit） end; / 10、游標的其他屬性及其使用實例 ①%isopen: if cemp%isopen then dbms_output.put_line('游標一打開'); ②%rowcount: （總共影響的行數） dbms_output.put_line('行數：'||cemp %rowcount); 11、游標數的限制：預設情況下oracle資料庫只允許在同一個會話中打開300個游標查看游標屬性：切換到sys管理員用戶下，conn sys/root as sysdba 就可以切換到sys用戶

修改預設的游標數： alter system set open_cursors=400 scope=both; (scope的取值有三個參數： memory：只更改當前實例，不更改系統參數文件 spfile :只更改參數文件，不更改當前實例，需重啟資料庫才能生效 both ：是以上兩者如果設置錯了，可以使用rollback命令回滾 11、帶參數的游標 set serveroutput on declare cursor cemp(dno number) is select ename from emp where deptno=dno; pename emp.ename%type; begin open cemp(10); loop fetch cemp into pename; exit when cemp %notfound; dbms_output.put_line(pename); end loop; close cemp; end; / 12、系統例外： no_data_found （沒有找到數據） too_many_rows （select ... into 語句匹配多個行） zero_divide (被零除） value_error （算術或轉換錯誤） timeout_on_resource (在等待資源時發生超時）

13、自定義例外： set serveroutput on declare pename emp.ename%type; no_emp_found exception; cursor cemp is select ename from emp where empno=12; begin open cemp; fetch cemp into pename; if cemp%notfound then raise no_emp_found; end if; close cemp; exception when no_emp_found then dbms_output.put_line('找不到員工'); when others then dbms_output.put_line('其他'); end; / 14、統計每年入職的員工數 set serveroutput on declare cursor cemp is select to_char(hiredate,'yyyy') from emp; pdate varchar2(4); count80 number := 0; count81 number := 0; count82 number := 0; count87 number := 0; begin open cemp; loop fetch cemp into pdate; exit when cemp%notfound; if pdate='1980' then count80:=count80+1; elsif pdate='1981' then count81:=count81+1; elsif pdate='1982' then count82:=count82+1; else count87:=count87+1; end if; end loop; dbms_output.put_line('總共：'||(count80+count81+count82+count87)); dbms_output.put_line('1980:'||count80); dbms_output.put_line('1981:'||count81); dbms_output.put_line('1982:'||count82); dbms_output.put_line('1987:'||count87); close cemp; end; / 15、案例二：為員工漲工資

/* 做之前先分析 SQL語句 select empno,sal from emp order by sal asc; -->游標-->迴圈-->退出條件：1.工資總額>5w 2.%notfound 變數：1.初始值 2.如何得到漲工資的人數： countEmp number :=0; 漲後的工資總額： salTotal number; select sum(sal) into salTal from emp; 張後的工資總額=漲前的工資總額 + sal * 0.1 */ set serveroutput on declare cursor cemp is select empno,sal from emp order by sal; pempno emp.empno%type; psal emp.sal%type; countEmp number:=0; salTotal number; stop_sal exception; begin select sum(sal) into salTotal from emp; open cemp; loop if salTotal < 50000 then （加入限制只有工資總額在5000以內才執行下麵的代碼） exit when salTotal>50000; fetch cemp into pempno,psal; exit when cemp%notfound; countEmp := countEmp+1; update emp set sal=sal*1.1 where empno=pempno; salTotal := salTotal + psal*0.1; else raise stop_sal; end if; end loop; close cemp; commit; dbms_output.put_line('漲工資人數：'||countEmp||'工資總額：'||salTotal); exception when stop_sal then dbms_output.put_line('漲工資結束'); when others then dbms_output.put_line('其他'); end; / 16、綜合案例四

1、由於最後的結果也是一張表，所以先創建一張表 create table msg1( coursename varchar2(20), dname varchar2(20), count1 number, count2 number, count3 number, avggrade number ); SQL語句 1、得到有哪些系 select dno,dname from dep; -->游標 -->迴圈-->退出條件：notfound 2、得到系中選修了“大學物理“的學生的成績 select grade fromm sc where cno=(select cno from course where cname=??) and sno in (select sno from student where dno =??); -->帶參數的游標 -->迴圈 -->退出條件 -->notfound 變數：1、初始值 2、如何得到每個分數段的人數 count1 number,count2 number,count3 number 每個系選修了大學物理的學生的平均成績 avggrade number 1、算術運算 2、sql語句查詢 set serveroutput on declare --系的游標 cursor cdept is select dno,dname from dep; pdno dep.dno%type; pdname dep.dname%type; --成績游標 cursor cgrade(coursename varchar2,depno number) is select grade from sc where cno=(select cno from course where cname=coursename) and sno in (select sno from student where dno=depno); pgrade sc.grade%type; --每個分數段的人數 count1 number;count2 number;count3 number; --每個系選修了大學物理的學生的平均成績 avggrade number; --課程名稱 pcourseName varchar2(20) := '大學物理'; begin --打開系的游標 open cdept; loop --取一個系的信息 fetch cdept into pdno,pdname; exit when cdept %notfound; --初始化工作 count1:=0;count2:=0;count3:=0; --系的平均成績 select avg(grade) into avggrade from sc where cno=(select cno from course where cname=pcourseName) and sno in (select sno from student where dno=pdno); --取系中選修了大學物理的學生成績 open cgrade(pcourseName,pdno); loop --取一個學生的成績 fetch cgrade into pgrade; exit when cgrade%notfound; --判斷成績的範圍 if pgrade<60 then count1:=count1+1; elsif pgrade>=60 and pgrade<85 then count2:=count2+1; else count3:=count3+1; end if; end loop; close cgrade; --保存當前的結構 insert into msg1 values(pcourseName,pdname,count1,count2,count3,avggrade); end loop; close cdept; dbms_output.put_line('數據查詢成功！'); end; /