1.Declare variables as VARCHAR2(N)
- Use %TYPE instead of VARCHAR2(N), unless it is a “single point defintion”.
- Incase the variable stand for combine column, use SUBTYPE and define subtype at the declaration of the package in order to tuning.
- Overleading means we have more than 1 func/proc have the same name with different parameter lists (aka static polymorphism)
- Don’t copy the code, let reuse the code through subprogram. The trick here is use uninitiated variable for nonexistence parameters.
3.Use low-level error handling logic
- Don‘t call RAISE_ERROR_APPLICATION/explicitly insert into log table or write to file/ call buil-in func in each handler.
- Use generic or shared error management.
4.Rely on row -by-row processing
- With FOR ALL and BULK COLLECTION we can eliminate the context switches PL/SQL Runtime Engine to SQL Engine.
5.Write spaghetti code
- Use top down technique to make the code easy to read and re-useable.
- An executable block should not more than 50 lines long.