1、定義游標：列出每個員工的姓名、部門名稱並編程顯示第10個到第20個記錄。
declare
cursor zemp_cursor is (select temp.ename, temp.dname
from (select e.ename, d.dname, ROWNUM rn
from zemp e, zdept d
where e.deptno=d.deptno(+)
and ROWNUM<=20) temp
where temp.rn >10) ;
begin
for zemp_record in zemp_cursor loop --隱式打開游標
dbms_output.put_line('ename is ' || zemp_record.ename || ' dname is ' || zemp_record.dname) ;
end loop; --隱式關閉游標
end;
/
2、定義游標：從雇員表中顯示工資大於3000的記錄，只要姓名、部門編號和工資。編程顯示其中的奇數記錄。
declare
cursor zemp_cursor is (select ROWNUM rn, ename, deptno, sal
from zemp
where sal > 3000) ;
begin
for zemp_record in zemp_cursor loop
--if mod(zemp_record.rn, 2)<>0 then
if mod(zemp_cursor%rowcount, 2)<>0 then
dbms_output.put_line( zemp_cursor%rowcount
--zemp_record.rn
||' ename is ' || zemp_record.ename
||' deptno is ' || zemp_record.deptno
||' sal is ' || zemp_record.sal) ;
end if ;
end loop;
end;
/
3、計算下面級數當末項小於0.001時的部分和。
1/(1*2)+1/(2*3)+1/(3*4)+…+1/(n*(n+1))+ ……
declare
n number :=1 ;
total number :=0 ;
begin
loop
total:=total+1/(n*(n+1)) ;
exit when (1/(n*(n+1)))<0.001 ;
--select sum(total+1/(n*(n+1))) into total from dual ;
n:=n+1;
end loop ;
dbms_output.put_line('The final sum is：'|| total) ;
end ;
/
4、計算s=1*2+2*3+…+N*(N+1),當N=50的值。
declare
n number :=1 ;
total number(10,3):=0 ;
begin
loop
total := total + n*(n+1) ;
exit when n=50 ;
--select sum(total+(1/((n+1)*(n+2)))) into total from dual ;
n:=n+1;
end loop ;
dbms_output.put_line('The final sum is：'|| total) ;
end ;
/
5、兩重循環，計算S=1!+2!+…+10!。
declare
a number:=0;
s number:=0;
begin
for i in 1..10 loop
a:=1;
for j in 1..i loop
a:=a*j;
end loop;
s:=s+a;
dbms_output.put_line(a);
end loop;
dbms_output.put_line('s='||to_char(s));
end;
/
6、編程序求滿足不等式 1+3^2+5^2+…+N^2>2000的最小N值。
declare
n number :=1 ;
total number :=0 ;
begin
loop
total := total+power(n,2) ;
n:=n+2;
exit when total>2000 ;
end loop ;
dbms_output.put_line('The min N is：'|| n) ;
end ;
/
7、將雇員表中的所有工資小於3000增加400，統計出增加工資的人數及增加的工資數量。
declare
cursor sal_cursor is (select sal from zemp where sal<3000) for update of sal nowait ;
--鎖定sal列，保證sal能夠正常更新
n number ;
begin
select count(1) into n from zemp where sal<3000 ;
if n=0 then
RAISE_APPLICATION_ERROR(-20017, '數據不存在！') ;
else
for emp_record in sal_cursor loop
update zemp set sal=emp_record.sal+400 where current of sal_cursor ;
end loop ;
commit; --提交釋放
end if ;
dbms_output.put_line('增加工資的人數：'|| n ||' 增加工資的人數：'|| 400*n) ;
exception
when others then
dbms_output.put_line('數據輸入錯誤') ;
dbms_output.put_line('SQLCODE = '|| SQLCODE) ;
dbms_output.put_line('SQLERRM = '|| SQLERRM) ;
end;
/
8、將雇員表中的部門編號為30的所有員工刪除，統計刪除的人數及刪除人的平均工資。
declare
v_avg number ;
v_count number ;
begin
select avg(sal),count(empno) into v_avg,v_count from zemp where deptno=30 ;
delete from zemp where deptno=30 ;
dbms_output.put_line('刪除的人數：'|| v_count||' 刪除人員的平均工資：'|| v_avg) ;
end ;
/
9、觸發器的定義。
CREATE OR REPLACE TRIGGER TT
BEFORE DELETE OR UPDATE
OR INSERT ON zemp
BEGIN
DBMS_OUTPUT.PUT_LINE('觸發器TT');
END;
/
調用：
UPDATE zemp SET SAL=SAL-100 WHERE SAL>1000;
觸發器TT
16 rows updated
10、從雇員表中顯示工資最高的前五個人的姓名，部門和工資。
declare
cursor zemp_cursor is select sal,ename,deptno from zemp order by sal desc,ename ;
i number :=0 ;
begin
dbms_output.put_line('最高工資的5個人：') ;
for zemp_xixi in zemp_cursor loop
exit when i=5;
dbms_output.put_line('姓名：'|| zemp_xixi.ename||' 部門：'|| zemp_xixi.deptno
||' 工資：'|| zemp_xixi.sal) ;
i:=i+1;
end loop ;
end;
/
11、編寫過程：當給定的部門編號存在時，顯示部門的信息。如果不存在則將該部門插入其中。
create or replace procedure show_insert_proc(v_deptno zdept.deptno%type)
as
v_count number ;
begin
select count(1) into v_count from zdept where deptno=v_deptno;
if v_count=0 then
dbms_output.put_line('不存在該部門！！');
insert into zdept(deptno, dname, loc) values(v_deptno,'資訊','昆山');
commit ;
dbms_output.put_line('插入成功！！');
else
for x in (select * from zdept where deptno=v_deptno) loop
dbms_output.put_line('部門編號：'||x.deptno
||' 部門名稱：'||x.dname
||' 部門位置：'||x.loc) ;
end loop ;
end if ;
end ;
/
12、編寫函數：對雇員表按工資從小到大排序後的第n條到第m條記錄的工資總和。
create or replace function order_zemp_fun(v_start number, v_final number)
return number
as
v_sum number := 0;
begin
if v_start>v_final then
dbms_output.put_line('起點數據大於終點數據！！');
return -1 ;
else
select sum(sal) into v_sum
from (select rownum rn,sal
from (select sal from zemp order by sal)
where rownum<=v_final) temp
where temp.rn>=v_start ;
return v_sum ;
end if ;
end ;
/
測試：
declare
x number;
begin
x:=order_zemp_fun(5, 10);
dbms_output.put_line( '第五到第十條記錄工資總和：'|| x) ;
end ;
/
第五到第十條記錄工資總和：17835.38
PL/SQL procedure successfully completed
13、編寫給定雇員編號從雇員表刪除信息的過程。
create or replace procedure delete_empno_proc(v_empno zemp.empno%type)
as
v_count number ;
begin
select count(1) into v_count from zemp where empno=v_empno ;
if v_count=0 then
dbms_output.put_line('此雇員不存在！');
else
delete zemp where empno=v_empno;
dbms_output.put_line('此雇員成功刪除！');
end if;
end ;
/
14、定義觸發器：對表雇員表建立插入、刪除、修改之前分別顯示：開始插入、開始刪除、開始修改。
CREATE OR REPLACE TRIGGER TT
BEFORE DELETE OR UPDATE OR INSERT
ON zemp
BEGIN
IF DELETING THEN
DBMS_OUTPUT.PUT_LINE('開始刪除');
elsif UPDATING THEN
DBMS_OUTPUT.PUT_LINE('開始更新');
elsif INSERTING THEN
DBMS_OUTPUT.PUT_LINE('開始插入');
END IF;
END TT;
/
15、編寫過程：從雇員表中刪除按工作編號從小到大排序後第N條記錄。
create or replace procedure delete_num_proc(v_num number)
as
i number :=1 ;
v_count number ;
v_empno zemp.empno%type ;
v_ename zemp.ename%type ;
begin
select count(1) into v_count from zemp ;
--錯誤檢查
if (v_num>v_count or v_num<0) then
RAISE_APPLICATION_ERROR(-20017, '數據輸入錯誤！') ;
RETURN ;
else
--查找到員工的編號
for c1 in (select empno,ename from zemp order by empno) loop
if i=v_num then
v_empno:=c1.empno ;
v_ename:=c1.ename ;
exit ;
end if;
i:=i+1 ;
end loop;
--刪除員工信息
delete from zemp where empno=v_empno ;
dbms_output.put_line('員工：'|| v_ename ||' 編號：'||v_empno||' 刪除成功！！') ;
end if ;
exception
when others then
dbms_output.put_line('數據輸入非法！！') ;
dbms_output.put_line('SQLCODE = '|| SQLCODE) ;
dbms_output.put_line('SQLERRM = '|| SQLERRM) ;
end ;
/
