L5WvLzNs

1--- Write a package which consists of one function f_itemchk and two procedures  proc_insert and proc_update
2--- Function will check the existence of empno. If it exists then update the record for that employee using procedure proc_update.
3---- Otherwise insert the record using procedure proc_insert
4
5--- Package Specifications
6
7create or replace package emp_number_check
8is
9    -- Declaring the function
10    function f_itemchk ( p_empno in number)
11    return number;
12    
13    --- Declaring the procedure
14    
15    procedure proc_update (
16            p_empno number,
17            p_ename varchar2,
18            p_sal number
19        );
20    
21    procedure proc_insert ( 
22            p_empno number,
23            p_ename varchar2,
24            p_sal number
25            );
26            
27end emp_number_check;
28 
29
30create or replace package body emp_number_check
31is
32    --- Function Declaration
33    
34    function f_itemchk (p_empno in number)
35    return number
36    is
37        v_empno number;
38    begin
39         
40        select empno into v_empno from emp where empno = p_empno;
41        if v_empno is not null then
42            proc_update(v_empno, 'ADITH', 5000);
43            dbms_output.put_line(' The details of existing record has been updated successfully ' );
44            return 1;
45            
46        elsif v_empno is null then
47            proc_insert( v_empno, 'ADITH', 10000);
48            dbms_output.put_line( ' The details of the new record has been inserted sucessfully ' );
49            return 1;
50        return 0;    
51        end if;
52    end f_itemchk;
53    
54    -- Procedure for updating record
55    
56    procedure proc_update (
57        p_empno number,
58        p_ename varchar2,
59        p_sal number
60        )
61    as
62    begin
63        
64        update emp set ename = p_ename where empno = p_empno;
65        update emp set sal = p_sal where empno = p_empno;
66        dbms_output.put_line( 'Employee Updated Successfully...');
67    end proc_update;
68    
69    
70    -- Procedure for inserting record
71    
72    procedure proc_insert ( 
73        p_empno number,
74        p_ename varchar2,
75        p_sal number
76        )
77    as
78    begin
79        insert into emp (emp.empno, emp.ename, emp.sal) values (p_empno, p_ename, p_sal);
80        dbms_output.put_line (' Employee Inserted Successfully..');
81    end proc_insert;
82end;
83
84
85--- Execution
86
87declare
88    emp_number  number;
89begin
90    dbms_output.put_line( ' Enter the employee number ');
91    emp_number := emp_number_check.f_itemchk(&emp_1);
92    if emp_number = 1 then
93        dbms_output.put_line( ' You have made a change to table and the function has been called ' );
94    end if;
95    
96end;