使用人力资源数据库的程序 [英] PROCEDURES using HR database

问题描述

我想编写一个程序，该程序将首先打印雇员的雇员编号和薪水(即7839). 然后它将增加雇员7839(这将是雇员的薪水 符合以下条件的员工表中的数字:

I want to Write a PROCEDURE that will first print the Employee Number and Salary of an employee (i.e. 7839). Then it will increase the salary of an employee 7839 (this will be employee number in the table employee) as per following conditions:

Condition-1: If experience is more than 10 years, increase salary by 20%.
Condition-2: If experience is greater than 5 years, increase salary by 10%.
Condition-3: All others will get an increase of 5% in the salary.

程序将在增加之前和之后打印员工编号和薪水 我尝试了以下步骤，但不确定它的准确性..我需要将其转换为PROCEDURE代码.

The program will print the Employee Number and salary before and after the increase i tried the following steps but not sure how accurate is it..I need to convert it to a PROCEDURE code.

请告知

    DECLARE
     veno  emp.empno%type:=&veno;
     vsal  emp.sal%type;
     vexp  number;
BEGIN
    select empno,sal,trunc(to_char(months_between(sysdate,hiredate)/12))into veno,vsal,vexp from emp where empno=veno;
DBMS_OUTPUT.PUT_LINE('before update:' ||chr(10)||veno||chr(10)||vsal);
    if vexp>=10 then
        update emp set sal=sal+(sal*.20) where empno=veno; 
        select sal into vsal from emp where empno=veno;
        DBMS_OUTPUT.PUT_LINE('after update:' ||chr(10)||vsal);
   elsif vexp>=5 then
        update emp set sal=sal+(sal*.10) where empno=veno;
        select sal into vsal from emp where empno=veno;
        DBMS_OUTPUT.PUT_LINE('after update:' ||chr(10)||vsal);
     else 
        update emp set sal=sal+(sal*.05) where empno=veno;
        select sal into vsal from emp where empno=veno;
        DBMS_OUTPUT.PUT_LINE('after update:' ||chr(10)||vsal);
   end if;
END;
/

推荐答案

尝试使用RETURNING子句再添加一个异常块

Try this one else go with RETURNING clause and add an exception block

DECLARE
     veno  emp.empno%type:=&veno;
     vsal  emp.sal%type;
     vexp  number;
BEGIN
    select empno,sal,trunc(to_char(months_between(sysdate,hiredate)/12))into veno,vsal,vexp from emp where empno=veno;
DBMS_OUTPUT.PUT_LINE('before update:' ||chr(10)||veno||chr(10)||vsal);
    if vexp>=10 then
        update emp set sal=sal+(sal*.20) where empno=veno; 
        select sal into vsal from emp where empno=veno;
        DBMS_OUTPUT.PUT_LINE('after update:' ||chr(10)||vsal);
   elsif vexp>=5 then
        update emp set sal=sal+(sal*.10) where empno=veno;
        select sal into vsal from emp where empno=veno;
        DBMS_OUTPUT.PUT_LINE('after update:' ||chr(10)||vsal);
     else 
        update emp set sal=sal+(sal*.05) where empno=veno;
        select sal into vsal from emp where empno=veno;
        DBMS_OUTPUT.PUT_LINE('after update:' ||chr(10)||vsal);
   end if;
END;
/

这篇关于使用人力资源数据库的程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！