Oracle的游标

来源：互联网发布：gulp 压缩js css 编辑：程序博客网时间：2024/05/18 01:56

----------------------------------------------------------
-----------游标的使用----------------------------
-----------------------------------------------------
----打印出 80 部门的所有的员工的工资:salary: XXX
declare
--1. 定义游标
cursor salary_cursor is select salary,employee_id from employees where department_id = 80;
v_salary employees.salary%type;
v_empid employees.employee_id%type;
begin
--2. 打开游标
open salary_cursor;

--3. 提取游标
fetch salary_cursor into v_salary,v_empid;

--4. 对游标进行循环操作: 判断游标中是否有下一条记录
while salary_cursor%found loop
dbms_output.put_line('v_empid'||v_empid||' salary: ' || v_salary);
fetch salary_cursor into v_salary,v_empid;
end loop;

--5. 关闭游标
close salary_cursor;
end;

create or replace procedure testCursor is
--1. 定义游标
cursor salary_cursor is select salary,employee_id from employees where department_id = 80;
v_salary employees.salary%type;
v_empid employees.employee_id%type;
begin
open salary_cursor;
loop
fetch salary_cursor into v_salary,v_empid;
exit when salary_cursor%notfound;
dbms_output.put_line('v_empid'||v_empid||' salary: ' || v_salary);
end loop;
close salary_cursor;
/*
--2. 打开游标
open salary_cursor;
--3. 提取游标
fetch salary_cursor into v_salary,v_empid;
--4. 对游标进行循环操作: 判断游标中是否有下一条记录
while salary_cursor%found loop
dbms_output.put_line('v_empid'||v_empid||' salary: ' || v_salary);
fetch salary_cursor into v_salary,v_empid;
end loop;
--5. 关闭游标
close salary_cursor;*/
end testCursor;

create or replace package body sp_package is
function pgkFun(v_department varchar2) return VARCHAR2 is
v_emp EMPLOYEES%ROWTYPE;--与 EMPLOYEES表中的各字段相同
CURSOR emp_cursor IS SELECT * FROM emploYEES t WHERE t.DEPARTMENT_ID = v_department
AND t.MANAGER_ID = '100';
v_temp VARCHAR2(4000):= '';
begin
/*SELECT * INTO v_emp
FROM EMPLOYEES t
WHERE t.DEPARTMENT_ID = v_department;*/

--2打开游标
/*OPEN emp_cursor;
--提取游标
FETCH emp_cursor INTO v_emp;
--对游标进行循环操作: 判断游标中是否有下一条记录
WHILE emp_cursor%FOUND LOOP
v_temp := v_temp || v_emp.LAST_NAME||',';
fetch emp_cursor into v_emp;
END LOOP;*/


-----------------
/*OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_emp;
EXIT WHEN emp_cursor%NOTFOUND;
v_temp := v_temp || v_emp.LAST_NAME||',';
END LOOP;*/


-------------------

OPEN emp_cursor;
FETCH emp_cursor INTO v_emp;
WHILE emp_cursor%FOUND LOOP
v_temp := v_temp || v_emp.LAST_NAME||',';
FETCH emp_cursor INTO v_emp;
END LOOP;

return v_temp;
end;
end;

-----------------------------------------------ref

create or replace function cursorDemo return varchar2 is

Type MyRefCurA IS REF CURSOR RETURN table_name%RowType;
Type MyRefCurB IS REF CURSOR ;
vRefCurA MyRefCurA;

begin
Open vRefCurA For Select * from table_name
Where id = '123';
loop
fetch vRefCurA into vTempA;
exit when vRefCurA%notfound;
DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||' '|| vTempA.name
||' vTempA.name2='||vTempA.id_dc_car_fits ||' '||vTempA.name3 );
end loop;
Close vRefCurA;
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------------------------------');

return '';
end cursorDemo;

--------
declare
--1. 定义游标
cursor salary_cursor is select salary,employee_id from employees where department_id = 80;
type emp_record is record(
v_salary employees.salary%type,
v_empid employees.employee_id%type
);
---声明一个记录类型的变量
v_emp_record emp_record;

begin
--2. 打开游标
open salary_cursor;

--3. 提取游标
fetch salary_cursor into v_emp_record;

--4. 对游标进行循环操作: 判断游标中是否有下一条记录
while salary_cursor%found loop
dbms_output.put_line('v_empid'||v_emp_record.v_empid||' salary: ' || v_emp_record.v_salary);
fetch salary_cursor into v_emp_record;
end loop;

--5. 关闭游标
close salary_cursor;
end;

-------
declare
cursor sal_cursor is select salary ,last_name from employees where department_id = 80;
v_sal number(10);
v_name varchar2(20);
begin
open sal_cursor;

fetch sal_cursor into v_sal,v_name;

while sal_cursor%found loop
dbms_output.put_line(v_name||'`s salary is '||v_sal);
fetch sal_cursor into v_sal,v_name;
end loop;

close sal_cursor;

end;

(法二：使用for循环)
declare

cursor emp_cursor is select last_name,email,salary from employees where manager_id = 100;

begin

for v_emp_record in emp_cursor loop
dbms_output.put_line(v_emp_record.last_name||','||v_emp_record.email||','||v_emp_record.salary);
end loop;
end;

---利用游标, 调整公司中员工的工资:
/*
工资范围调整基数
0 - 5000 5%
5000 - 10000 3%
10000 - 15000 2%
15000 - 1%
*/
declare
--定义游标
cursor emp_sal_cursor is select salary, employee_id from employees;

--定义基数变量
temp number(4, 2);

--定义存放游标值的变量
v_sal employees.salary%type;
v_id employees.employee_id%type;
begin
--打开游标
open emp_sal_cursor;

--提取游标
fetch emp_sal_cursor into v_sal, v_id;

--处理游标的循环操作
while emp_sal_cursor%found loop
--判断员工的工资, 执行 update 操作
--dbms_output.put_line(v_id || ': ' || v_sal);

if v_sal <= 5000 then
temp := 0.05;
elsif v_sal<= 10000 then
temp := 0.03;
elsif v_sal <= 15000 then
temp := 0.02;
else
temp := 0.01;
end if;

--dbms_output.put_line(v_id || ': ' || v_sal || ', ' || temp);
update employees set salary = salary * (1 + temp) where employee_id = v_id;

fetch emp_sal_cursor into v_sal, v_id;
end loop;
--关闭游标
close emp_sal_cursor;
end;

declare
--定义游标
cursor my_cursor is select age from mytable where user_id like'%user_id%';
v_age mytable.age%type;
begin
--打开游标
open my_cursor;
--提取游标
fetch my_cursor into v_age;
--对游标进行循环操作，判断游标中是否有下一条记录 %found
while my_cursor%found loop
    dbms_output.put_line('v_age:'||v_age);
    fetch my_cursor into v_age;
end loop;
--关闭游标
close my_cursor;
end;

--for 循环
declare
--定义游标
cursor my_cursor is select age from mytable where user_id like'%user_id%';
--v_age mytable.age%type;
begin
for v_my_record in my_cursor loop
    --dbms_output.put_line(v_age);
    dbms_output.put_line('v_age:'||v_my_record.age);
end loop;
/*
--打开游标
open my_cursor;
--提取游标
fetch my_cursor into v_age;
--对游标进行循环操作，判断游标中是否有下一条记录 %found
while my_cursor%found loop
    dbms_output.put_line('v_age:'||v_age);
    fetch my_cursor into v_age;
end loop;
--关闭游标
close my_cursor;
*/
end;

0 0