Common PL/SQL development principles

(1) Binding variables are widely used, especially bulk binding, as this effectively avoids the hard parsing of SQL and the context switching of the PL/SQL engine, and of the SQL engine!
(2) Extensive use of UROWID to process DML statements (Urowid is a rowid extension, Oracle recommends using UROWID instead of ROWID)
(3) Use DDL statements (Create, ALTER, DROP, truncate, and so on) sparingly in your stored procedures, as this may disrupt the continuity of your transaction and, more seriously, may block DML operations and may result in a large library Cache latch contention and may cause changes in some SQL execution plans.
(4) Do not commit in the stored procedure should not commit, especially when your stored procedure will be called by another stored procedure, you consider that you do this will destroy the call your parent stored procedure transaction continuity?
(5) Pay attention to the amount of data you face, the processing method of small data volume and the processing method of massive data is not the same!
(6) When looping, be careful to empty the value of the temporary variable
(7) Note the "SELECT INTO variable" issue, use the sub-begin statement to encapsulate the "SELECT INTO variable" to avoid possible errors, so that you can avoid the "select INTO variable" before the execution of select COUNT (*)
(8) Do not have an implicit conversion when Oracle executes your PL/SQL code
(9) When you define a VARCHAR2 variable in PL/SQL, you can define it as VARCHAR2 (4000) When you don't know the length of the variable you're defining, and it's not wasted!
(10) If you write a set of stored procedures logically related, then I suggest you have to encapsulate these stored procedures in a package
(11) Correct all compile-time compiler prompts in your PL/SQL code warning
(12) When the cycle must pay attention to exit, otherwise it is too scary!
(13) When dealing with an explicit cursor, be sure to pay attention to the fetch and exit, otherwise it's horrible! The following is a real case that I have dealt with, and the problem that is exposed in this case is a good indication of the need to pay attention to fetch and exit when dealing with the cursor:

      1 o'clock in the afternoon today when I received a letter from a colleague, the letter mentioned that one of our 10GR2 development environment is not even in, reported such a mistake "ora-00257:archiver error. Connect Internalonly,until freed. " It was clear that the archive did not go down and the whole system was stuck.
      I went up and saw that the directory where the archived logs were stored was full. The directory size of the storage archive log for the development environment described above is 10 G,alert log shows that the system has produced 10 g of archived logs from today's 11:30 to 1 o'clock in the afternoon, and continues to produce a steady stream. The entire system is stuck because the space is full when the archive log continues to be generated. The reason to know, the process is very clear.
       keep only one archive log, and then manually remove all other archived logs, note that there is no need to leave all the archived logs to RM immediately, otherwise the file system handle may not be released. In turn, the space occupied by these archive logs cannot be freed. Then run a ADDM report and see what led to so many archived logs in such a short period of time. It is clear from the ADDM report that the root cause of so many archived logs is that a few SQL in a stored procedure executes 1400多万次 for a short time.
     use the ALTER system kill session with Kill-9 to kill the session where the stored procedure is located, to prevent it from continuously generating archived logs.
     Then I analyzed the stored procedure, and found that the root cause is that in this stored procedure a cursor,fetch is opened to a record in the cursor, and then a loop is opened, Run the above SQL statements in the loop. But the fatal thing is not to fetch another record in the cursor at the end of the loop, which causes the condition of the above loop to always be true, so the above SQL will be executed forever if it is not interrupted by me. This explains why a lot of archived logs are generated in a short time, and the space for undo tablespace is exhausted. The follow-up process is omitted here ...

Bulk collect into do not collect too much data at a time, it is recommended to collect the amount of data within 10,000, you can use the bulk binding of the limit clause to restrict or use rownum to limit
(15) If you use bulk binding, why waste time writing such as Insertinto TableName (column1,column2,......, column100) VALUES (value1 (i), value2 (i),......, VALUE100 (i)) above the SQL statement? If possible, write your own stored procedure Code generator and let it help you generate such statements. You should concentrate on the more useful aspects!
(16) Do you want your code to be executed concurrently? If you do not want or your code will not be able to be executed concurrently, then think of a way to control concurrency! It is good to control at the application level, such as attempting to add a lock to the record for update nowait before the update, or using the attributes currently read by the DML statement to avoid concurrency is a good idea.
(17) Do not write statements such as insert into tablename1 select* from tablename2, do you consider extensibility? What if the tablename1 or tablename2 increase or decrease the field later?
(18) Use hint sparingly, unless you know exactly what you are doing. For example, here you force Oracle to use an index, and if the name of the index is changed in the future, what will you do with the change of the execution plan? Have you considered this situation?
(19) Pay attention to the problem of associated updates lost data, if the UPDATE statement does not specify a where condition that is to do all the data update operation, this is too scary!
(20) Use temporary tables, sometimes temporary tables are useful! Especially when it comes to updating massive amounts of data based on a bunch of complex conditions.
(21) Try to avoid using recursive rules in stored procedures! Not that it is not possible to use a recursive rule (which is useful in certain situations), but rather to avoid infinite recursion when using a recursive rule!
(22) Write the comments in your PL/SQL code, which is important! Not writing notes doesn't mean you're great!

Common PL/SQL development principles