几十个实用的PL/SQL(3)
2024-07-21 02:08:01
供稿：网友
第三阶段
q.使用ref游标显示“emp”表中的值。
a.
declare
type emprectyp is record
(
empno emp.empno%type,
ename emp.ename%type,
job emp.job%type,
mgr emp.mgr%type,
hiredate emp.hiredate%type,
sal emp.sal%type,
comm emp.comm%type,
deptno emp.deptno%type
);
type emp_cursor is ref cursor return emp%rowtype;
vemp_cur emp_cursor;
vemp_rec emprectyp;
begin
open vemp_cur for select * from emp;
loop
fetch vemp_cur into vemp_rec;
exit when vemp_cur%notfound;
dbms_output.put(vemp_rec.empno||' '||vemp_rec.ename||' '||vemp_rec.job);
dbms_output.put(vemp_rec.mgr||' '||vemp_rec.hiredate||' '||vemp_rec.sal);
dbms_output.put_line(vemp_rec.comm||' '||vemp_rec.deptno);
end loop;
close vemp_cur;
end;
/
q.从“emp”中获得值送到pl/sql表，将pl/sql表中的薪水值增加500，并向用户显示增加的薪水及其他详细信息。
a.
declare
type emprec is record
(
empno emp.empno%type,
ename emp.ename%type,
job emp.job%type,
mgr emp.mgr%type,
hiredate emp.hiredate%type,
sal emp.sal%type,
comm emp.comm%type,
deptno emp.deptno%type
);
i binary_integer:=1;
type emp_tab is table of emprec index by binary_integer;
vemp emp_tab;
cursor c1 is select * from emp;
begin
for x in c1
loop
vemp(i).empno:=x.empno;
vemp(i).ename:=x.ename;
vemp(i).job:=x.job;
vemp(i).mgr:=x.mgr;
vemp(i).hiredate:=x.hiredate;
vemp(i).sal:=x.sal+500;
vemp(i).comm:=x.comm;
vemp(i).deptno:=x.deptno;
i:=i+1;
end loop;
for j in 1..i-1
loop
dbms_output.put(vemp(j).empno||' '||vemp(j).ename||' '||vemp(j).job);
dbms_output.put(vemp(j).mgr||' '||vemp(j).hiredate||' '||vemp(j).sal);
dbms_output.put_line(vemp(j).comm||' '||vemp(j).deptno);
end loop;
end;
/
q.一旦将值送到pl/sql表后，尝试在pl/sql表中插入新记录并且删除某些现有的记录。
a.
declare
type emprec is record
(
empno emp.empno%type,
ename emp.ename%type,
job emp.job%type,
mgr emp.mgr%type,
hiredate emp.hiredate%type,
sal emp.sal%type,
comm emp.comm%type,
deptno emp.deptno%type
);
i binary_integer:=1;
type emp_tab is table of emprec index by binary_integer;
vemp emp_tab;
cursor c1 is select * from emp;
begin
for x in c1
loop
vemp(i).empno:=x.empno;
vemp(i).ename:=x.ename;
vemp(i).job:=x.job;
vemp(i).mgr:=x.mgr;
vemp(i).hiredate:=x.hiredate;
vemp(i).sal:=x.sal;
vemp(i).comm:=x.comm;
vemp(i).deptno:=x.deptno;
i:=i+1;
end loop;
-- for j in 1..i-1
-- loop
-- dbms_output.put(vemp(j).empno||' '||vemp(j).ename||' '||vemp(j).job);
-- dbms_output.put(vemp(j).mgr||' '||vemp(j).hiredate||' '||vemp(j).sal);
-- dbms_output.put_line(vemp(j).comm||' '||vemp(j).deptno);
-- end loop;
--插入记录
dbms_output.put_line('插入记录:');
vemp(i).empno:=1000;
vemp(i).ename:='goldens';
vemp(i).job:='software';
vemp(i).mgr:=null;
vemp(i).hiredate:='2003-01-04';
vemp(i).sal:=8888;
vemp(i).comm:=10;
vemp(i).deptno:=10;
for j in 1..i
loop
dbms_output.put(vemp(j).empno||' '||vemp(j).ename||' '||vemp(j).job);
dbms_output.put(vemp(j).mgr||' '||vemp(j).hiredate||' '||vemp(j).sal);
dbms_output.put_line(vemp(j).comm||' '||vemp(j).deptno);
end loop;
--删除第5、6条记录
dbms_output.put_line('删除第5、6条记录:');
for j in 5..i-2
loop
vemp(j).empno:=vemp(j+2).empno;
vemp(j).ename:=vemp(j+2).ename;
vemp(j).job:=vemp(j+2).job;
vemp(j).mgr:=vemp(j+2).mgr;
vemp(j).hiredate:=vemp(j+1).hiredate;
vemp(j).sal:=vemp(j+2).sal;
vemp(j).comm:=vemp(j+2).comm;
vemp(j).deptno:=vemp(j+2).deptno;
end loop;
vemp(i-1).empno:=null;
vemp(i-1).ename:=null;
vemp(i-1).job:=null;
vemp(i-1).mgr:=null;
vemp(i-1).hiredate:=null;
vemp(i-1).sal:=null;
vemp(i-1).comm:=null;
vemp(i-1).deptno:=null;
vemp(i).empno:=null;
vemp(i).ename:=null;
vemp(i).job:=null;
vemp(i).mgr:=null;
vemp(i).hiredate:=null;
vemp(i).sal:=null;
vemp(i).comm:=null;
vemp(i).deptno:=null;
for j in 1..i-2
loop
dbms_output.put(vemp(j).empno||' '||vemp(j).ename||' '||vemp(j).job);
dbms_output.put(vemp(j).mgr||' '||vemp(j).hiredate||' '||vemp(j).sal);
dbms_output.put_line(vemp(j).comm||' '||vemp(j).deptno);
end loop;
end;
/
