Compiling robust PL/SQL code (1): same field type, % type, subtype

This article uses a simple PL/SQL case to show you the significance of designing robust code.

In my previous system, there were 5-6 core business databases. Various databases had to interact with each other because of business needs.
In particular, in the design of some key data models, the meaning of each database is consistent.
A typical field such as app_col (I use this app_col to represent a certain type of field)
Many table structures in each database contain such keyword fields.
However, the actual situation is a headache. Our designers will design the definition of the travel region.

For example, on the ora_a database:

[SQL]
View plaincopyprint?

1. Table_aa.app_col number (4 ),
2. Table_ AB .app_col char (3 ),
3. Table_ac.app_col varchar2 (5 ),
4. Table_ad.app_col number,
5. Table_ AE .app_col varchar2 (4)

table_aa.app_col number(4),table_ab.app_col char(3),table_ac.app_col varchar2(5),table_ad.app_col number,table_ae.app_col varchar2(4)

Ora_ B Database

[SQL]
View plaincopyprint?

1. Table_ba.app_col number,
2. Table_bb.app_col char (4 ),
3. Table_bc.app_col varchar2 (6)
4. ...

table_ba.app_col number,table_bb.app_col char(4),table_bc.app_col varchar2(6)...

In fact, in the entire system (all databases), The app_col column has the same meaning.
However, due to the randomness and misunderstanding of the designers of each module, many definition methods have emerged in the system.
The final consequence is that the exchange of data between modules will not change much.
The same PL/SQL code is invalid when it comes to another db. You need to adjust the variable type definition in the code.

Imagine: A comprehensive application system that has created 5000 tables/per dB and 20000 modules
Because of the needs of business development, the customer needs to transform it (in fact, such requirements are normal in the Communication Industry)
After analysis by system design experts, to meet the business needs, we need
Add a new column to some tables a. a, B. B, and B. C, and modify the code of more than 20 modules.
Among them, 50 modules reference the data in the tables to be modified.
The actual situation may be worse. Our system designers may not be able to give accurate information on which modules reference the data in the tables that need to be modified by the current business support.

See the following code:

[SQL]
View plaincopyprint?

1. Create Table Test
2. (ID number primary key,
3. Name varchar2 (32 ),
4. Address varchar2 (32 ),
5. Phone varchar2 (11)
6. );
8. Create or replace procedure proc_test (p_id in number) is
9. V_buf varchar2 (200 );
10. Begin
11. Select name | ',' | Address | ',' | phone into v_buf from test where id = p_id;
12. /* Other business processing code section ...*/
13. End;
14. /

Create Table Test (ID number primary key, name varchar2 (32), address varchar2 (32), phone varchar2 (11); Create or replace procedure proc_test (p_id in number) is Vanderbilt Buf varchar2 (200); beginselect name | ',' | Address | ',' | phone into v_buf from test where id = p_id; /* Other business processing code section... */end ;/

In the above code, we can say that there is no problem, or there are some "potential" (which requires certain conditions to be triggered) problems.
If the customer wants to support the employee ID that contains characters in the ID in the future due to business needs, the customer must provide business support in advance.
Then, we need to modify the Data Type of the ID field from number to varchar2. At this time, my proc_test program will change to invalid.
This service will be supported in the future, so it will not cause any problems during the release, and there will be no problems in testing.
After two months, the customer discovered this problem only when using it. Similar cases have seen too many

If you change the code to the following format, is the effect much better?

[SQL]
View plaincopyprint?

1. Create or replace procedure proc_test (p_id in test. ID % type) is
2. V_buf varchar2 (200 );
3. Begin
4. Select name | ',' | Address | ',' | phone into v_buf from test where id = p_id;
5. /* Other business processing code section ...*/
6. End;
7. /

Create or replace procedure proc_test (p_id in test. id % type) is v_buf varchar2 (200); beginselect name | ',' | Address | ',' | phone into v_buf from test where id = p_id; /* Other business processing code section... */end ;/

Now, the Data Type of the p_id parameter is directly locked to the Data Type of the column in the table.
During each compilation process, PL/SQL queries the Data Type of the column from the data dictionary and uses it in the compiled code.
Therefore, if you really set this column to a letter or number type, it will not cause any harm to the code.
Compared with the above statement, is it a lot more robust?

Look at the hard-coded v_buf varchar2 (200) line above );
In general, this will not cause any problems. The v_buf variable is a cache used to store exported data.
In the original test table structure, the maximum length of the name, address, and phone fields is 75.
So the length of 200 bytes is OK.
Suddenly one day, the customer asked me again: I asked my ID to correspond to the complete contact information, and the address should be accurate to the house number.
Unfortunately, we have to modify the table structure and code again. Isn't it annoying?

Simply re-declare v_buf
V_buf varchar2 (1000 );
What if there are hundreds of similar adjustments in my application? Does the workload of modifying the 100-segment code drive you crazy? And you cannot guarantee that your modifications are well considered.

In this case, Oracle provides a good solution: Use subtype
Subtype is an alias for another defined data type.
For example, in this case, define a new data type of listbuffer_t, which is actually another name of varchar2 (1000 ).

[SQL]
View plaincopyprint?

1. Create or replace package app_def
2. Subtype listbuffer_t is varchar2 (1000 );
3. End app_def;

CREATE OR REPLACE PACKAGE app_def ASSUBTYPE listbuffer_t IS VARCHAR2 (1000);END app_def;

With this subtype, You can adjust my declaration:
V_buf app_def.listbuffer_t;
In this way, I remove the hard encoding from the proc_test Process Declaration.

If you declare similar variables, you can use predefined, application-specific data types, such as listbuffer_t.
Then we can reduce the error point to a position in the Code, for example, in the app_def package in this case
Is it convenient to adjust it? If 1000 bytes are not enough, I only need to modify this line of code in app_def and re-compile it.
Is it relatively robust?

Solution after modification:

[SQL]
View plaincopyprint?

1. SQL> Create Table Test
2. 2 (ID number primary key,
3. 3 name varchar2 (32 ),
4. 4 address varchar2 (32 ),
5. 5 phone varchar2 (11)
6. 6 );
7. Table created
8. SQL>
9. SQL> Create or replace package app_def
10. 2 subtype listbuffer_t is varchar2 (1000 );
11. 3 end app_def;
12. 4/
13. Package created
15. SQL> Create or replace procedure proc_test (p_id in test. ID % type) is
16. 2 v_buf app_def.listbuffer_t;
17. 3 begin
18. 4 select name | ',' | Address | ',' | phone into v_buf from test where id = p_id;
19. 5/* Other business processing code section ...*/
20. 6 end;
21. 7/
22. Procedure created
23. SQL>

SQL> Create Table Test2 (ID number primary key, 3 name varchar2 (32), 4 address varchar2 (32), 5 phone varchar2 (11) 6 ); table createdsql> SQL> Create or replace package app_def as2 subtype listbuffer_t is varchar2 (1000); 3 end app_def; 4/package createdsql> Create or replace procedure proc_test (p_id in test. id % Type) is2 v_buf app_def.listbuffer_t; 3 begin4 select name | ',' | Address | ',' | phone into v_buf from test where id = p_id; 5/* Other business processing code section... */6 end; 7/procedure createdsql>