1,游标的类型分哪三种? (隐式,显式,REF)
2,隐式游标的名称是什么? (SQL)
3,隐式游标的属性有哪些? (SQL%found,SQL%notfound,SQL%rowcount,SQL%isopen)
4,用pl/sql语句编程实现,新建一个表student(sno(主键),sname,age)动态执行,然后动态执行插入10条记录
declare
sid varchar2(10);
sna varchar2(10);
age number(3);
begin
sna := 'a';
execute immediate 'create table student(sno varchar2(10) primary key,sname varchar2(10),age number(3))';
for i in 1..10
loop
EXECUTE IMMEDIATE 'insert into student values(:1,:2,:3)' using i,sna,15+i;
end loop;
commit;
end;
5,用pl/sql语句编程实现对student表数据进行更改操作(用游标的方式),把所有学生的年龄加1,而且还有打印输出有多少学生年龄被更改成功!
declare
ag number(3);
begin
update student set age = age + 1;
dbms_output.put_line('有'||SQL%rowcount||'个学生年龄被修改!');
end;
DECLARE
stu student%ROWTYPE;
i number(10):= 0;
CURSOR stu_cur IS SELECT * FROM student for update;
BEGIN
OPEN stu_cur;
LOOP
FETCH stu_cur INTO stu;
EXIT WHEN stu_cur%NOTFOUND;
i := i + 1;
update student set age = age + 1 where sno = stu.sno;
dbms_output.put_line(stu.sno||':'||stu.sname||':'||stu.age);
END LOOP;
CLOSE stu_cur;
dbms_output.put_line('有'||i||'个学生年龄被修改!');
commit;
END;
6,用pl/sql语句编程实现(用游标的方式)把所有学生的年龄小于18岁的人都删除,并且要打印输出被删除多少人
declare
ag number(3);
begin
delete student where age<18;
dbms_output.put_line('有'||SQL%rowcount||'个年龄小于18岁的学生被删除!');
end;
DECLARE
stu student%ROWTYPE;
i number(10):= 0;
CURSOR stu_cur IS SELECT * FROM student WHERE age<18 for update;
BEGIN
OPEN stu_cur;
LOOP
FETCH stu_cur INTO stu;
EXIT WHEN stu_cur%NOTFOUND;
i := i + 1;
delete from student where current of stu_cur;
dbms_output.put_line(stu.sno||':'||stu.sname||':'||stu.age);
END LOOP;
CLOSE stu_cur;
dbms_output.put_line('有'||i||'个年龄小于18岁的学生被删除!');
commit;
END;
7,用REF游标实现,当运行时需要输入一个参数为员工的编号,然后打印输出这个员工的所有信息,还有这个员工所在部门的信息,如果没有这个员工则要给出查不到这个员工的提示(表在scott用户下的emp、dept)
declare
type emp_curtype is ref cursor;
emp_cur emp_curtype;
no number(10);
emprow emp%rowtype;
deptrow dept%rowtype;
begin
no := '&编号';
open emp_cur for select * from emp where empno = no;
fetch emp_cur into emprow;
if emp_cur%notfound then
dbms_output.put_line('emp表中没有编号为:'||no||'的员工!');
else
dbms_output.put_line(emprow.empno||':'||emprow.ename||':'||emprow.job||':'||emprow.sal);
end if;
close emp_cur;
open emp_cur for select * from dept where deptno = no;
fetch emp_cur into deptrow;
if emp_cur%notfound then
dbms_output.put_line('dept表中没有编号为:'||no||'的员工!');
else
dbms_output.put_line(deptrow.deptno||':'||deptrow.dname||':'||deptrow.loc);
end if;
close emp_cur;
end;