Free 30-day beta access · no payment · progress saved
The business problem
A sensitive transformation is duplicated in several applications.
What you will be able to do
Choose a function or procedure, define parameters and test effects.
Create a function
Create a procedure
Choose the right abstraction
Test errors
Scope of this micro-course
This micro-course targets one focused skill. It does not replace a complete path or server administration training.
Prerequisites
SQL queries
Transactions
Basic types
Project and practice
9 verified exercises are available. Validation is based on the programme exercises.
Focused path
Your programme, in order.
Content remains centralised: completing an activity here also updates the path that contains it.
Micro-lessons
01
The problem to solve before syntax
A UPDATE without WHERE can modify an entire table. A restarted import can also create duplicates if the uniqueness and conflict behavior are not defined.
First write the SELECT that identifies the lines, run the change in a transaction, use RETURNING to observe the effects, and explicitly choose the conflict key of the UPSERT.
Check the number of records returned before and after, check the returned values and test resuming the import. A safe operation is repeatable or explicitly non-repeatable.
A rule copied in several applications diverges. A function can centralize it close to the data, but logic that is too opaque or very coupled becomes difficult to test and deploy.
A function returns a value or a set and can be used in a query depending on its properties. A procedure is called with CALL and is suitable for an imperative operation; exact capacities depend on the DBMS.
Create a billing business function 2 training scenario
Encapsulate pure, typed, reusable computation. You must produce the result, check it on the source data and explain what it allows you to decide in this billing system.
Create a logistics business function 3 · training scenario
Encapsulate pure, typed, reusable computation. You must produce the result, check it on the source data and explain what it allows you to decide in this national expeditions.
Create an HR business function 4 · training scenario
Encapsulate pure, typed, reusable computation. You must produce the result, control it on the source data and explain what it allows you to decide in this human resources management.
Create an audit business function 5 training scenario
Encapsulate pure, typed, reusable computation. You must produce the result, check it on the source data and explain what it allows you to decide in this audit log.
Insert then update billing data 2 · training scenario
Make two controlled changes and check the final state. You must produce the result, check it on the source data and explain what it allows you to decide in this billing system.
Insert then update logistics data 3 · training scenario
Make two controlled changes and check the final state. You must produce the result, check it on the source data and explain what it allows you to decide in this national expeditions.
Make two controlled changes and check the final state. You must produce the result, control it on the source data and explain what it allows you to decide in this human resources management.