oracle第三天

標籤：

HelloWorld.sql

set serveroutput on

declare
--說明部分
begin
--程式
dbms_output.put_line(‘Hello World‘);
end;
/

if語句.sql

--判斷使用者輸入的數字

set serveroutput on

--接收鍵盤輸入
--num: 地址值，在該地址上，存了輸入的值
accept num prompt ‘請輸入一個數字‘;

declare
--定義變數儲存輸入的數字
--隱式轉換
pnum number := #
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;
/

帶參數的游標.sql

--查詢某個部門中員工的姓名

set serveroutput on

declare
--帶參數的游標
cursor cemp(dno number) is select ename from emp where deptno=dno;
pename emp.ename%type;
begin
open cemp(20);
loop
fetch cemp into pename;
exit when cemp%notfound;

dbms_output.put_line(pename);

end loop;
close cemp;
end;
/

給員工漲工資.sql

--漲工資 總裁1000 經理800 其他400

set serveroutput on

declare
cursor cemp is select empno,empjob from emp;
pempno emp.empno%type;
pjob emp.empjob%type;
begin
rollback;

open cemp;
loop
--取一個員工 漲工資
fetch cemp into pempno,pjob;
exit when cemp%notfound;

--判斷職位
if pjob = ‘PRESIDENT‘ then update emp set sal=sal+1000 where empno=pempno;
elsif pjob = ‘MANAGER‘ then update emp set sal=sal+800 where empno=pempno;
else update emp set sal=sal+400 where empno=pempno;
end if;

end loop;

close cemp;

--事務的隔離等級
commit;

dbms_output.put_line(‘漲工資完成‘);
end;
/

 游標

 

--使用遊標查詢員工姓名和工資，並列印

/*
1. 游標的屬性
%isopen: 是否開啟
%rowcount: 行數
%notfound: 沒有記錄

2. 預設允許一次開啟300個游標(修改游標： 第四天 管理方案)
SQL> show parameters cursor

NAME TYPE VALUE
------------------------------------ ----------- -------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 20
*/

set serveroutput on

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;
/

 記錄型變數

----查詢並列印7839的姓名和薪水

set serveroutput on

declare
--記錄型變數 代表一行
emp_rec emp%rowtype;
begin

select * into emp_rec from emp where empno=7839;

dbms_output.put_line(emp_rec.ename||‘的薪水是‘||emp_rec.sal);
end;
/

執行個體1

/*
執行個體1：統計每年入職的員工個數。

SQL語句
1. select to_char(hiredate,‘RR‘) from emp; --> 游標 --> 迴圈(notfound)
2. count80 number := 0;
count81 number := 0;
count82 number := 0;
count87 number := 0;
*/
set serveroutput on

declare
cursor cemp is select to_char(hiredate,‘RR‘) from emp;
phiredate varchar2(4);

--計數器
count80 number := 0;
count81 number := 0;
count82 number := 0;
count87 number := 0;
begin
open cemp;
loop
--取一個員工
fetch cemp into phiredate;
exit when cemp%notfound;

--判斷年份
if phiredate = ‘80‘ then count80:=count80+1;
elsif phiredate = ‘81‘ then count81:=count81+1;
elsif phiredate = ‘82‘ then count82:=count82+1;
else count87:=count87+1;
end if;

end loop;
close cemp;

dbms_output.put_line(‘Total:‘||(count80+count81+count82+count87));
dbms_output.put_line(‘80:‘||count80);
dbms_output.put_line(‘81:‘||count81);
dbms_output.put_line(‘82:‘||count82);
dbms_output.put_line(‘87:‘||count87);
end;
/

 執行個體2

/*
為員工長工資。從最低工資調起每人長10％，但工資總額不能超過5萬元,
請計算長工資的人數和長工資後的工資總額，並輸出輸出長工資人數及工資總額。

SQL語句:
1. select empno,sal from emp order by sal;-->游標 -->迴圈(1. > 5w 2. 漲完)
2. countEmp number := 0;
3. 長工資後的工資總額: *. select sum(sal) from emp
*. 漲後=漲前+sal*0.1 (*)

練習： 工資<5w
*/
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;
begin
--初始的工資總額
select sum(sal) into salTotal from emp;

open cemp;
loop
--第一個允出準則
exit when salTotal> 50000;

--取一個員工
fetch cemp into pempno,psal;

--第二個允出準則
exit when cemp%notfound;

--漲工資
update emp set sal =sal *1.1 where empno=pempno;

--人數
countEmp := countEmp+1;


--工資總額
salTotal := salTotal + psal *0.1;
end loop;
close cemp;

commit;

dbms_output.put_line(‘人數:‘||countEmp||‘ 工資總額:‘||salTotal);

end;
/

 

執行個體3

 

/*
用PL/SQL語言編寫一程式，實現按部門分段（6000以上、(6000，3000)、3000元以下)
統計各工資段的職工人數、以及各部門的工資總額(工資總額中不包括獎金)

SQL語句
1. 部門: select deptno from dept; --> 游標 --> 迴圈
2. 部門中員工的薪水: select sal from emp where deptno=??? --> 帶參數的游標 --> 迴圈
3. count1 number; count2 number; count3 number;
4. 部門工資總額: salTotal number;
select sum(sal) into salTotal from emp where deptno=???
*/
set serveroutput on

declare
--部門
cursor cdept is select deptno from dept;
pdeptno dept.deptno%type;

--部門中員工的薪水
cursor cemp(dno number) is select sal from emp where deptno=dno;
psal emp.sal%type;

--計數器
count1 number; count2 number; count3 number;

--部門的工資總額
salTotal number;
begin
open cdept;
loop
--取一個部門
fetch cdept into pdeptno;
exit when cdept%notfound;

--初始化
count1:=0;count2:=0;count3:=0;

--部門的工資總額
select sum(sal) into salTotal from emp where deptno=pdeptno;

--部門中員工的薪水
open cemp(pdeptno);
loop
--取一個員工的薪水cl
fetch cemp into psal;
exit when cemp%notfound;

--判斷
if psal< 3000 then count1:=count1+1;
elsif psal>=3000 and psal<6000 then count2:=count2+1;
else count3:=count3+1;
end if;

end loop;
close cemp;

-- 儲存當前結果
insert into msg1 values(pdeptno,count1,count2,count3,nvl(salTotal,0));

end loop;
close cdept;

commit;

dbms_output.put_line(‘完成‘);
end;
/

系統例外

--被0除

set serveroutput on

declare
pnum number;
begin
pnum := 1/0;

exception
when Zero_Divide then dbms_output.put_line(‘1:0不能做被除數‘);
dbms_output.put_line(‘2:0不能做被除數‘);
when Value_error then dbms_output.put_line(‘算術或轉換錯誤‘);
when others then dbms_output.put_line(‘其他例外‘);
end;
/

--列印1~10

set serveroutput on

declare
pnum number := 1;
begin
loop
--退出迴圈
exit when pnum > 10;

dbms_output.put_line(pnum);

--加一
pnum := pnum + 1;

end loop;
end;
/

引用型變數

--查詢並列印7839的姓名和薪水

set serveroutput on

declare
--定義引用型變數
pename emp.ename%type;
psal emp.sal%type;
begin

--查詢7839的姓名和薪水
select ename,sal into pename,psal from emp where empno=7839;

-- 列印
dbms_output.put_line(pename||‘的薪水是‘||psal);
end;
/

自訂例外

--查詢50號部門的員工

set serveroutput on

declare
cursor cemp is select ename from emp where deptno=50;
pename emp.ename%type;

--自訂例外
no_emp_found exception;
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;
/

out參數

/*
create [or replace] PROCEDURE 過程名(參數列表)
AS
PLSQL子程式體；
查詢並返回某個員工的姓名 月薪和職位

思考: out參數太多???
*/
create or replace procedure queryEmpInfo(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin
select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;

end;
/

觸發器應用情境二

/*
資料確認

漲後的薪水不能少於漲前的薪水
CREATE [or REPLACE] TRIGGER 觸發器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW [WHEN(條件) ] ]
PLSQL 塊

*/
create or replace trigger checksal
before update
on emp
for each row
begin
--if 漲後的薪水< 漲前的薪水 then
if :new.sal < :old.sal then
raise_application_error(-20002,‘漲後的薪水不能少於漲前的薪水.漲前:‘||:old.sal||‘ 漲後:‘||:new.sal);
end if;
end;
/

觸發器應用情境一

/*
實施複雜的安全性檢查

禁止在非工作時間 往emp表中插入資料
CREATE [or REPLACE] TRIGGER 觸發器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
PLSQL 塊

周末:to_char(sysdate,‘day‘) in (‘星期六‘,‘星期日‘)
上班前 下班後: to_number(to_char(sysdate,‘hh24‘)) not between 9 and 18
*/
create or replace trigger securityEmp
before insert
on emp
begin
if to_char(sysdate,‘day‘) in (‘星期六‘,‘星期日‘) or
to_number(to_char(sysdate,‘hh24‘)) not between 9 and 18 then

raise_application_error(-20001,‘不能在非工作時間插入資料‘);

end if;
end;
/

儲存函數

/*
CREATE [OR REPLACE] FUNCTION 函數名(參數列表)
RETURN 函數實值型別
AS
PLSQL子程式體；

查詢某個員工的年度營收

*/
create or replace function queryEmpIncome(eno in number)
return number
as
psal emp.sal%type;
pcomm emp.comm%type;
begin

select sal,comm into psal,pcomm from emp where empno=eno;

return psal*12+nvl(pcomm,0);
end;
/

帶參數的預存程序

/*
create [or replace] PROCEDURE 過程名(參數列表)
AS
PLSQL子程式體；

為指定的員工漲100塊錢 並列印漲前和漲後的薪水
*/
create or replace procedure raiseSalary(eno in number)
as
psal emp.sal%type;
begin
--漲前薪水
select sal into psal from emp where empno=eno;

--漲100
update emp set sal=sal+100 where empno=eno;

--要不要commit???

dbms_output.put_line(‘漲前:‘||psal||‘ 漲後:‘||(psal+100));

end;
/

第一個觸發器

/*
成功插入員工後，自動輸出“成功插入一個新員工”
CREATE [or REPLACE] TRIGGER 觸發器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
PLSQL 塊

*/
create or replace trigger sayNewEmp
after insert
on emp
begin
dbms_output.put_line(‘成功插入一個新員工‘);
end;
/

第一個預存程序

/*
create [or replace] PROCEDURE 過程名(參數列表)
AS
PLSQL子程式體；

列印Hello World

調用預存程序
1. exec sayHelloWorld();
2. begin
sayHelloWorld();
sayHelloWorld();
end;
/
*/

create or replace procedure sayHelloWorld
as
--說明部分
begin
dbms_output.put_line(‘Hello World‘);

end;
/

 

oracle第三天