PL SQL开发指导

1. Use the correct datatype so that implicit conversion will be avoided

2. Use bind variable to avoid unnecessary parsing

3. Use BULK COLLECT, % ATTRIBUTES wherever required

4. Use WHEN OTHERS exception as the last resort and handle exceptions

5. Use %TYPE and %ROWTYPE attributes. No code change is required when schema structure changes

6. Write as Little Code as Possible

7. Synchronize Program and Data Structures

8. Center All Development Around Packages

9. Don't declare data in your package specification

10. Avoid writing repetitive code inside your package bodies

11. Never exit from a FOR or WHILE LOOP (cursor) with an EXIT or RETURN statement

12. Don't let functions have OUT or IN OUT parameters

13. Do not use exceptions to perform branching logic

14. Do not " SELECT COUNT(*)“

15. Do not use the names of tables or columns for variable names

16. Walk through each other’s code

17. Adapt a consistent, readable format that is easy to maintain

18. Adapt logical, consistent naming conventions for modules and data structures

19. Standardize module and program headers

20. Tag module END statements with module names

21. Name procedures with verb phrases and functions with noun phrases

22. Self-document using block and loop labels

23. Express complex expressions unambiguously using parentheses

24. Use vertical code alignment to emphasize vertical relationships

 

25. Comment tersely with value-added information

26. Adopt meaningful naming conventions for source files

27. Use CONSTANT declarations for variables whose values do not change

28. Perform complex variable initialization in the executable section

29. Replace complex expressions with Boolean variables and functions

30. Remove unused variables and code

31. Beware of and avoid implicit datatype conversions

32. Package application-named literal constants together

33. Centralize TYPE definitions in package specifications

34. Use package globals judiciously and only in package bodies

35. Move static expressions outside of loop and SQL statements

36. Set guidelines for application-wide error handling before you start coding

37. Catch all exceptions and convert to meaningful return codes before returning to non-PL/SQL host programs

38. Use your own raise procedure in place of explicit calls to RAISE_APPLICATION_ERROR

39. Use WHEN OTHERS only for unknown exceptions that need to be trapped

40. Standardize named application exceptions in package specifications

41. Use incremental COMMITs to avoid rollback segment errors when changing large number of rows

42. Define multi-rows cursors in packages so they can be used for multiple programs

43. Fetch into cursor records, never into a hard-coded list of variables

44. Use COUNT only when the actual number of occurrences is needed

45. Use a cursor FOR loop to fetch all rows in a cursor unconditionally and never use to fetch just one row

46. Specify columns to be updated in a SELECT FOR UPDATE statement

47. Reference cursor attributes immediately after executing the SQL operation

48. Use FORALL to improve performance of collection-based DML

 

49. Never return NULL from Boolean functions

50. Minimize the size of the trigger execution sections

51. Implement server problem logs and “to do” lists using database triggers

52. Validate complex business rules with DML triggers

53. Freeze and build package specifications before implementing package bodies

54. Handle expected and named exceptions when perforiming file I/O

55. Soft-code directory names in your calls to UTL_FILE.FOPEN

56. Backup/Purge process should be done periodically

57. Database health check-up must be performed periodically and if required, it is necessary to resize the database files appropriately

58. Instead of having the full code in a Program or Routine, should split the code into multiple sub Paragraphs which will increase the code readability.