Differences

This shows you the differences between two versions of the page.

@@ Line 1: / Line 1: @@
+<code>
+CREATE OR REPLACE PACKAGE pck_ws
+  IS
+eur2usd number;
+pi constant number := 3.14;
+function get_emp_by_index(i number) return varchar2;
+function get_next_emp  return varchar2;
+function get_next_emp2  return varchar2;
+FUNCTION get_number
+     ( p_name varchar2)
+     RETURN number;
+FUNCTION get_char
+     ( p_name varchar2)
+     RETURN varchar2;
+FUNCTION get_date
+     ( p_name varchar2)
+     return date;
+procedure add_emp (
+    p_first_name in working_salary.first_name%type,
+    p_last_name  in working_salary.last_name%type,
+    p_salary     in working_salary.salary%type default null,
+    p_bonus      in working_salary.bonus%type  default null,
+    p_hire_date  in working_salary.hire_date%type default sysdate);
+procedure chg_emp (
+    p_id         in working_salary.id%type,
+    p_first_name in working_salary.first_name%type default null,
+    p_last_name  in working_salary.last_name%type default null,
+    p_salary     in working_salary.salary%type default null,
+    p_bonus      in working_salary.bonus%type  default null,
+    p_hire_date  in working_salary.hire_date%type default null);
+procedure get_emp (
+    p_first_name in working_salary.first_name%type default null,
+    p_last_name  in working_salary.last_name%type default null,
+    p_salary     in working_salary.salary%type default null);
+/* has to be private anyway
+procedure add_emp (
+    p_id         in working_salary.id%type,
+    p_first_name in working_salary.first_name%type,
+    p_last_name  in working_salary.last_name%type,
+    p_salary     in working_salary.salary%type default null,
+    p_bonus      in working_salary.bonus%type  default null,
+    p_hire_date  in working_salary.hire_date%type default sysdate);
+*/
+END; -- Package spec
+/
+CREATE OR REPLACE PACKAGE BODY pck_ws
+IS
+e_uk_violated exception;
+e_out_of_bounds exception;
+e_no_emp exception;
+pragma EXCEPTION_INIT
+    (e_uk_violated, -00001);
+cursor c_ws is select * from working_salary;
+r_ws c_ws%rowtype;
+type emp_type is table of working_salary%rowtype;
+t_emp emp_type;
+function get_next_emp return varchar2 is
+begin
+    if not c_ws%isopen then
+      open c_ws;
+    end if;
+    fetch c_ws into r_ws;
+    if c_ws%notfound then
+        close c_ws;
+        open c_ws;
+        fetch c_ws into r_ws;
+    end if;
+    return r_ws.first_name||' '||r_ws.last_name;
+end;
+function get_next_emp2 return varchar2 is
+begin
+    if not c_ws%isopen then
+      open c_ws;
+    end if;
+    fetch c_ws into r_ws;
+    if c_ws%notfound then
+        close c_ws;
+        open c_ws;
+        fetch c_ws into r_ws;
+    end if;
+    return r_ws.first_name||' '||r_ws.last_name;
+end;
+FUNCTION get_number( p_name varchar2) return number
+as
+     ret parameters.n%type;
+     begin
+     select  n into ret from parameters where name = p_name;
+     RETURN ret;
+     end;
+FUNCTION get_char( p_name varchar2) return varchar2
+as
+     ret parameters.vc%type;
+     begin
+     select vc into ret from parameters where name = p_name;
+     return ret;
+     end;
+FUNCTION get_date( p_name varchar2) return date
+as
+     ret parameters.d%type;
+     begin
+     select d into ret from parameters where name = p_name;
+     return ret;
+     end;
+procedure add_emp (
+    p_id         in working_salary.id%type,
+    p_first_name in working_salary.first_name%type,
+    p_last_name  in working_salary.last_name%type,
+    p_salary     in working_salary.salary%type default null,
+    p_bonus      in working_salary.bonus%type  default null,
+    p_hire_date  in working_salary.hire_date%type default sysdate)
+    is
+    begin
+    insert into working_salary (
+       id,
+       first_name,
+       last_name,
+       salary,
+       bonus,
+       hire_date
+    ) values (
+       p_id,
+       p_first_name,
+       p_last_name,
+       p_salary,
+       p_bonus,
+       p_hire_date
+    );
+    EXCEPTION
+    when e_uk_violated then
+      update working_salary set
+         bonus = nvl(p_bonus, bonus),
+         salary = nvl(p_salary, salary)
+      where first_name = p_first_name and
+            last_name  = p_last_name;
+    end;
+procedure chg_emp (
+    p_id         in working_salary.id%type,
+    p_first_name in working_salary.first_name%type default null,
+    p_last_name  in working_salary.last_name%type default null,
+    p_salary     in working_salary.salary%type default null,
+    p_bonus      in working_salary.bonus%type  default null,
+    p_hire_date  in working_salary.hire_date%type default null)
+    is
+    update_stmt varchar2(1000);
+    update_stmt_trim varchar2(1000);
+    begin
+    update_stmt := 'update working_salary set ';
+    if p_first_name is not null then
+      update_stmt := update_stmt || ' first_name = '||dbms_assert.enquote_literal(p_first_name)||',';
+    end if;
+    if p_last_name is not null then
+      update_stmt := update_stmt || ' last_name = '||dbms_assert.enquote_literal(p_last_name)||',';
+    end if;
+    if p_salary is not null then
+      update_stmt := update_stmt || ' salary = '||p_salary||',';
+    end if;
+    if p_bonus is not null then
+      update_stmt := update_stmt || ' bonus = '||dbms_assert.enquote_literal(p_bonus)||',';
+    end if;
+    if p_hire_date is not null then
+      update_stmt := update_stmt || ' hire_date = '||dbms_assert.enquote_literal(p_hire_date)||',';
+    end if;
+    update_stmt := rtrim(update_stmt,',');
+    update_stmt := update_stmt || ' where id =' || p_id;
+    dbms_output.put_line(update_stmt);
+    execute immediate update_stmt;
+end;
+procedure get_emp (
+    p_first_name in working_salary.first_name%type default null,
+    p_last_name  in working_salary.last_name%type default null,
+    p_salary     in working_salary.salary%type default null) is
+    c_emp sys_refcursor;
+    stmt varchar2(1000);
+    r working_salary%rowtype;
+    begin
+    stmt := 'select * from working_salary where ';
+    if p_first_name is not null then
+      stmt:= stmt || 'first_name = '||dbms_assert.enquote_literal(p_first_name)||' and';
+    end if;
+    if p_last_name is not null then
+      stmt:= stmt || 'last_name = '||dbms_assert.enquote_literal(p_last_name)||' and';
+    end if;
+    if p_salary is not null then
+      stmt:= stmt || 'salary = '||p_salary||' and';
+    end if;
+    stmt := rtrim(stmt,'and');
+    dbms_output.put_line(stmt);
+    open c_emp for stmt;
+    loop
+      fetch c_emp into r;
+      exit when c_emp%notfound;
+      dbms_output.put_line(r.first_name||' '||
+                  r.last_name ||' '||
+                  r.salary     ||' '||
+                  r.bonus     ||' '||
+                  r.hire_date);
+    end loop;
+    end;
+procedure add_emp (
+    p_first_name in working_salary.first_name%type,
+    p_last_name  in working_salary.last_name%type,
+    p_salary     in working_salary.salary%type default null,
+    p_bonus      in working_salary.bonus%type  default null,
+    p_hire_date  in working_salary.hire_date%type default sysdate)
+    is
+    new_salary number;
+    begin
+    add_emp(p_id=>null,
+            p_first_name=>p_first_name,
+            p_last_name=>p_last_name,
+            p_salary=>p_salary,
+            p_bonus=>p_bonus,
+            p_hire_date=>p_hire_date);
+    end;
+function get_emp_by_index(i number) return varchar2
+   is
+   begin
+   if not t_emp.exists(i) then
+     raise e_out_of_bounds;
+   end if;
+   return t_emp(i).first_name||' '||t_emp(i).last_name;
+   end;
+begin
+  eur2usd := get_number('eur2usd');
+  select * bulk collect into t_emp from working_salary;
+END;
+/
+</code>

dokuwiki

User Tools

Site Tools

Differences

Page Tools