oracle-存储过程-游标
CREATE OR REPLACE
procedure "exception3" as
--⽤户⾃定义异常
e_too_high_sal exception; --声明⾃定义异常
v_sal employees.salary%type;
begin
select salary into v_sal from employees where employee_id = 100;
if v_sal > 10000 then raise e_too_high_sal;
end if;
exception
when e_too_high_sal then dbms_output.put_line('⼯资太⾼');
end;
CREATE OR REPLACE
PROCEDURE "cursor2"
AS
-- 定义游标
cursor emp_sal_cursor is  SELECT SALARY,EMPLOYEE_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = 80; BEGIN
for c in emp_sal_cursor loop
dbms_output.put_line('employeeid: '|| c.EMPLOYEE_ID || ' salary:' || c.SALARY);
end loop;
END;
-- 打印出manager_id为100的员⼯的last_name,email,salary信息（使⽤游标，记录类型）
type emp_record is record(
v_last_name EMPLOYEES.LAST_NAME%type,
v_email EMPLOYEES.EMAIL%type,
v_salary EMPLOYEES.SALARY%type
);
v_emp_record emp_record;
--定义游标
cursor emp_cursor is select LAST_NAME,EMAIL,SALARY FROM EMPLOYEES WHERE MANAGER_ID = 100; BEGIN
-- 打开游标
open emp_cursor;
-- 提取游标
FETCH emp_cursor INTO v_emp_record;
--迭代
while emp_cursor%found loop
dbms_output.put_line('LAST_NAME:' || v_emp_record.v_last_name ||
' EMAIL:' || v_emp_record.v_email ||
'  SALARY:' || v_emp_record.v_salary);
FETCH emp_cursor INTO v_emp_record;
end loop;
-- 关闭游标
close emp_cursor;
END;
CREATE OR REPLACE
PROCEDURE "cursor4" AS
-- 员⼯⼯资低于5000 ⼯资增长5%  ⼯资低于10000 增长 3% ⼯资低于15000 增长2% 其他增长1%
--定义游标
cursor emp_cursor_salary is select SALARY,EMPLOYEE_ID FROM EMPLOYEES;
v_temp NUMBER(4,2);
BEGIN
for c in emp_cursor_salary loop
oracle游标的使用if c.SALARY <= 5000 then
v_temp := 0.05;
elsif c.SALARY <= 10000 and c.SALARY > 5000 then
v_temp := 0.03;
elsif c.SALARY <=15000 and c.SALARY > 10000 then
v_temp := 0.02;
else
v_temp := 0.01;
end if;
update EMPLOYEES set salary = salary * (1+ v_temp) WHERE EMPLOYEE_ID = c.EMPLOYEE_ID;
end loop;
END;
--隐式游标：更新指定员⼯salary（涨⼯资10），如果该员⼯没有到，则打印查⽆此⼈的信息BEGIN
UPDATE EMPLOYEES
SET SALARY = SALARY + 10
WHERE EMPLOYEE_ID = 1001;
if sql%notfound then dbms_output.put_line('查⽆此⼈');
end if;
END;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。