plsql-sample-code/customer_pkg.pks at main · mortenbra/plsql-sample-code · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
create or replace package customer_pkg
as

  /*

  Purpose:    package handles customers

  Remarks:

  Date        Who  Description
  ----------  ---  -------------------------------------
  05.11.2020  MBR  Created

  */

  -- customer status
  c_status_active                constant xy_customer.customer_status%type := 'ACTIVE';
  c_status_inactive              constant xy_customer.customer_status%type := 'INACTIVE';

  -- add new customer
  function new_customer (p_customer_name in varchar2) return number;

  -- get customer row
  function get_customer (p_customer_id in number) return xy_customer%rowtype;

  -- get customer name
  function get_customer_name (p_customer_id in number) return xy_customer.customer_name%type;

  -- set customer
  procedure set_customer (p_customer_id in number,
                          p_customer_name in varchar2);

  -- set customer row
  procedure set_customer (p_row in xy_customer%rowtype);

  -- delete customer
  procedure delete_customer (p_customer_id in number);

  -- purge old customers
  procedure purge_old_customers (p_since_date in date,
                                 p_delete_audit_trail in boolean := false);

end customer_pkg;
/