Grouping procedures into packages

Question

Lets say I have 50 master tables. For each master table I have 3 procedures to perform CRUD operation. This is a part of real time banking project.

My question is, how to group this procedures so that, it is easy to maintain and use. My plan is to create one package for one master table to group 3 procedures at a time.

Creating packages for procedures is an option, but do I need to create separate package for separate master table, is this feasible? Is this is a good practice?

Answer 1

You've tagged this "performance" so I assume part of your question has to do with performance. The use of packages is not going to have a meaningful impact on performance. Whether you have 1 package with 150 procedures or 50 packages each with 3 procedures or something in between is going to have no meaningful impact on performance. It's true that when you first invoke a procedure in a package the entire package is read into memory so larger packages might involve slightly more variability between calls (the first call has to do a bit more work than subsequent calls). But given the trivial size of even the most involved bit of PL/SQL code, if you're at the point where reading package code into memory is a meaningful fraction of your execution time, you're probably well beyond the point where you should stop optimizing.

Use packages to organize code into collections that are meaningful to you. If some of your tables are closely related, it may make sense to have one package that deals with all those tables.

Generally, I'd much rather have procedures that implement higher-level APIs (e.g. a create order procedure that inserts rows into the orders table in addition to inserting data into the order_lines table and into the invoice table) rather than pure CRUD procedures that interact with exactly one table. That's a much better way to ensure that your data obeys business rules that can't be easily defined with constraints (e.g. that an order always has at least one order_line, that the order_total in order always matches the sum of the order_line_total values in order_line, that an order always produces an invoice, etc.)