Question

oracle plsql 练习 要用到游标

实验十四
一、实验名称：CURSOR
二、实验目的：学会用PL/SQL编写CURSOR。
三、实验内容：

1、编写存储过程，显示各个职位(job),工资在2000元分以上人数和1000元以下人数。
（1）程序代码：
（2）执行结果：

2、编写一个存储过程
将各个职位前3位的职员的姓名，工资，成绩，名次存到一个数据表GOOD中
（1）、程序代码：
（2）、查询执行结果
用到的是oracle中的scott用户下的emp表，用游标解决，谢谢！！！

Answer 1

唉,又是大学生吧,自己不动脑筋.发个例子你看看.
declare
v_num number := &input;
v_last_num number := 0;
v_result varchar(2000) := '';
begin
loop
v_last_num := mod(v_num,10);
v_result := v_result || to_char(v_last_num);
v_num := v_num - v_last_num;
exit when v_num=0;
v_num := v_num / 10;
end loop;
dbms_output.put_line(v_result);
end;

第六章 游标(cursor)管理
create SYNONYM emp for scott.emp;
--作用：
declare
v_ename emp.ename%type;
begin
select ename into v_ename
from emp;

dbms_output.put_line(v_ename);
end;

--显式游标的定义和遍历
--通过无条件的循环来遍历游标
declare
--1.定义游标
cursor c_emp_cursor
is select ename from emp;

v_ename emp.ename%type;
begin
--2.打开游标
open c_emp_cursor;

--3.遍历游标
loop
fetch c_emp_cursor into v_ename; --rs.next();

exit when c_emp_cursor%NOTFOUND;

dbms_output.put_line(v_ename);
end loop;

--4.关闭游标
close c_emp_cursor;
end;

--用WHILE循环来遍历游标
declare
cursor c_emp_cursor
is select * from emp;

v_row emp%rowtype;
begin
open c_emp_cursor;

fetch c_emp_cursor into v_row;

while c_emp_cursor%FOUND loop
dbms_output.put_line(v_row.ename || ' ' || v_row.empno);
fetch c_emp_cursor into v_row;
end loop;

close c_emp_cursor;
end;

--FOR循环遍历游标的方式
declare
cursor c_emp_cursor
is select * from emp;
begin
for v_row IN c_emp_cursor loop
dbms_output.put_line(v_row.empno || ' ' || v_row.ename);
end loop;
end;

--定义参数类型的游标
declare
cursor c_emp_cursor(p_deptno emp.deptno%type)
is select ename from emp where deptno=p_deptno;

v_ename emp.ename%type;
begin
open c_emp_cursor(&input);

loop
fetch c_emp_cursor into v_ename;
exit when c_emp_cursor%NOTFOUND;
dbms_output.put_line(v_ename);
end loop;

close c_emp_cursor;
end;

--为游标指定返回类型1-rowtype
declare
cursor c_emp_cursor
return emp%rowtype --强制类型
is select * from emp;
begin
for v_row IN c_emp_cursor loop
dbms_output.put_line(v_row.empno);
end loop;
end;

--为游标指定返回类型2-记录(Record)类型
declare
--定义一个记录类型
type t_emp_record is record
(
empno emp.empno%type,
ename emp.ename%type
);

v_emp_record t_emp_record;

cursor c_emp_cursor
return v_emp_record --强制类型
is select empno,ename from emp;
begin
open c_emp_cursor;
loop
fetch c_emp_cursor into v_emp_record;
exit when c_emp_cursor%NOTFOUND;
dbms_output.put_line(v_emp_record.empno);
dbms_output.put_line(v_emp_record.ename);
end loop;
close c_emp_cursor;
end;

--如何通过游标来修改数据
declare
--通过游标修改数据要上行级锁
cursor c_emp_cursor
is select * from emp where deptno=20 for update;
begin
for v_row In c_emp_cursor loop
delete from emp
where current of c_emp_cursor;
end loop;
commit;
end;
select * from emp;

--REF引用类型的游标描述
declare
type t_ref_cursor is ref cursor;

v_ref_cursor t_ref_cursor;
begin
open v_ref_cursor
for select * from emp;

close v_ref_cursor;

open v_ref_cursor
for select * from dept;

close v_ref_cursor;
end;

--1.隐式游标(SQL)的使用
--用途：因为数据库系统在做insert,delete,update,select这些操作的时候
--都是通过游标去完成的，所以，在做操作之前我们的数据库系统都会隐式
--的打开一个游标，在执行操作完毕之后隐式的关闭游标，那么每次的操作的结果
--都会保存在一个叫做SQL的隐式游标中，我们可以通过操作这个隐式游标来获取
--数据库操作的信息

begin
delete from emp where deptno=10;

dbms_output.put_line(SQL%ROWCOUNT);
end;

第七章 存储过程(Procedure)和包(Package)

SQLServer - 存储过程 (output参数 return)
Oracle -存储过程(out参数) 函数(return)

--1.存储过程
create or replace procedure print(msg varchar2)
is
begin
dbms_output.put_line(msg);
end;

--1.1 调用存储过程
execute print('helloworld');

--1.2
create or replace procedure findEmp(p_empno emp.empno%type)
is
v_ename emp.ename%type;
begin
select ename into v_ename
from emp where empno=p_empno;

print(v_ename);
exception
when no_data_found then
print('未发现指定员工.');
end;

--1.3 传递参数
create or replace procedure myabs(p_num1 IN number,p_num2 OUT number)
is
begin
if p_num1 >0 then
p_num2 := p_num1;
else
p_num2 := (0 - p_num1);
end if;
end;

--调用
declare
v_result number;
begin
myabs(-987,v_result);
print(v_result);
end;

create or replace procedure change_num(p_num1 IN OUT number,p_num2 IN OUT number)
is
v_temp number;
begin
v_temp := p_num1;
p_num1 := p_num2;
p_num2 := v_temp;
end;

--调用
declare
v_num1 number :=111;
v_num2 number :=222;
begin
change_num(v_num1,v_num2);

print(v_num1);
print(v_num2);
end;

--2.函数(FUNCTION)
create or replace function myabs1(p_num1 number)
return number
is
begin
if p_num1 >= 0 then
return p_num1;
else
return (0 - p_num1);
end if;
end;

begin
print(myabs1(-999));
end;

create or replace function findEmp1(p_empno emp.empno%type)
return emp.ename%type
is
v_ename emp.ename%type;
begin
select ename into v_ename
from emp
where empno=p_empno;

return v_ename;
exception
when no_data_found then
print('未发现员工');
end;

begin
print(findEmp1(7499));
end;

--3.包(Package)-包(package-定义)和包体(package body-实现)
create or replace package mypkg
is
procedure print(msg varchar2);

function findEmp(p_empno number)
return emp.ename%type;
end;

create or replace package body mypkg
is
procedure print(msg varchar2)
is
begin
dbms_output.put_line(msg);
end;

function findEmp(p_empno number)
return emp.ename%type
is
v_ename emp.ename%type;
begin
select ename into v_ename
from emp
where empno=p_empno;

return v_ename;
exception
when no_data_found then
print('未发现员工');
end;
end;

--4.通过函数返回结果集
create or replace function findAllEmps
return SYS_REFCURSOR
is
v_ref_cursor SYS_REFCURSOR;
begin
open v_ref_cursor
for 'select * from emp';

return v_ref_cursor;
end;
我上学时候练习用的