See dbsnake on Weibo have a training because it was held in Beijing, not in the past. So ask dbsnake if he can pass me a copy of the relevant document. Dbsnake emailed me saying something wasn't written into the document. This document is also a process of accumulating dbsnake's experience with Oracle, all of which is not practical, so the opportunity to participate in more of this type of training is helpful for technical improvement. In this document dbsnake organizes a PL/SQL development document. Post it and share it.

Common PL/SQL development principles bysnake:

(1) Binding variables are widely used, especially bulk binding, as this can effectively avoid sql hard resolution and PL/SQL engine and SQL engine context switching!

(2) UROWID IS WIDELY USED TO HANDLE DML STATEMENTS

(3) Use DDL statements sparingly in your storage process, as this may disrupt the continuity of your tracksaction, and more seriously, may block DML operations and may result in a large number of library cache latch contentions and may result in some sql execution plan changes.

(4) don't make a comet where you shouldn't be in a stored procedure, especially if your stored procedure is called by another stored procedure, have you considered that doing so would break the continuity of the transaction that calls your parent stored procedure?

(5) pay attention to the amount of data you face, small amount of data processing methods and massive data processing methods are not the same!

(6) when looping, pay attention to empty the value of temporary variables

(7) pay attention to the problem of "select into variable" and encapsulate the "select into variable" with subbegin statements to avoid possible errors, so as to avoid performing select count before the "select into variable"

(8) Don't let oracle execute your PL/SQL code with implicit conversions

(9) When you define a vachar2 variable in PL/SQL, you can define it as varchar2 (4000) when you don't know the length of the variable you define, which is not wasted at all!

(10) if you write about a set of stored procedures that have a logical association, then i suggest that you encapsulate these stored procedures into a package

(11) Correct all compiler-time compilers in your PL/SQL code to prompt out the warning

(12) when looping must pay attention to exit, otherwise it is too terrible!

(13) be sure to pay attention to fetch and exit when dealing with explicit cursor, otherwise it will be terrible! here's a real case i've dealt with, and the problems exposed in this case are a good illustration of how to be careful when dealing with cursor:

Just after 1 p.m. this afternoon, I received a letter from a colleague about our 10gR2 development environment, which was not connected to us, and reported the error "ORA-00257: archiver error." Connect internalonly,until freed.”。 It's obvious that the whole system is stuck because the archive doesn't go ahead.

I went up to have a look, and it was true that the directory where the archive logs were stored was full. The directory size of the archive logs in the above development environment is 10 G, and log alert shows that the system generated 10 G archive logs between 11:30 today and around 1 p.m., 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 is clear.

Keep only one archive log, and then manually remove all other archive logs, be careful not to drop all archive logs to rm at once, otherwise it may cause the handle of the file system to not be released, and the space occupied by these archive logs can not be freed. Then run an ADDM report and see what caused so many archive logs to be generated in such a short period of time. It is clear from the ADDM report that the root cause of so many archive logs is that several sqls in a stored procedure execute more than 14 million times in a short period of time.

kill the session where this stored procedure is located with alter system kill process -9 to prevent it from generating a steady stream of archive logs.

then i analyzed the stored procedure above and found that the root cause was that a cursor was opened in the stored procedure, and a record of the cursor was recorded in a record, and then a loop was opened to run the above-mentioned sql statements in the loop. but the fatality is that there is no other record in the cursor at the end of the loop, which causes the conditions of the above loop to always be true, so the above sql will be carried out forever if i do not interrupt. this explains why a large number of archive logs are generated in a short period of time, and the space for undo tablespace is exhausted.

subsequent processes are omitted here...

(14) bulk collect into not too much data at a time, it is recommended that the amount of data in one collec is less than 10000, you can use bulk binding comes with the limit clause to limit or use rownum to limit

(15) if you use bulk binding, why waste time writing sql statements such as insertinto tablename (column1, column2,......,column100) values (i), value2 (i) ,......,value100 (i)? if possible, write your own stored procedure code generator and let it help you generate such a statement. you should concentrate on more useful aspects!

(16) Do you want your code to be executed concurrently? If you don't want or your code simply can't be executed concurrently, think of a way to control concurcurement! It's good to control at the application level, such as trying to lock the record with update nowait before update, or using the features currently read by DML statements to avoid concurition

(17) don't write statements such as insert into tablename1 select from tablename2, do you consider extensibility? what if the tablename1 or tablename2 were added or decreased in the future?

(18) use hist with caution unless you know exactly what you're doing. for example, here you force oracle to use an index, if the name of the index is changed later, the resulting change in the execution plan, what do you do? have you taken this into account?

(19) note the problem of associated update loss of data, update statement if there is no specified where condition that is to do update operation on all data, this is too scary!

(20) use temporary tables, sometimes temporary tables are very useful! especially when updating massive amounts of data based on a complex array of conditions

(21) try to avoid using recurring rules in stored procedures! not that you can't use the regression rule (the regression rule is useful in certain cases), but that you must avoid the situation of unlimited retransmission when using the retransmission rule!

(22) WRITE THE COMMENTS IN YOUR PL/SQL CODE, THIS IS VERY IMPORTANT! NOT WRITING A NOTE DOESN'T MEAN YOU'RE GREAT!

-------------------------------------------------------------------------------------------------------

QQ:492913789

Email:[email protected]

Blog: http://www.cndba.cn/dave

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

DBA1 GROUP: 62697716 (FULL); DBA2 GROUP: 62697977 (FULL) DBA3 GROUP: 62697850 (FULL)

DBA SUPERGROUP: 63306533 (FULL); DBA4 GROUP: 83829929 DBA5 GROUP: 142216823

DBA6 GROUP: 158654907 CHAT GROUP: 40132017 CHAT GROUP 2: 69087192

--Adding groups is required to explain the relationship between Oracle table space and data files in the notes, otherwise the request will be rejected