Advantages and disadvantages of "top" global variables

In the process of doing the project recently, the use of plsql is very much, mainly to write the stored procedure to realize the business logic. But in the process of coding encountered very strange problems.

The problem is: Define a variable in the package header, Current_time: = Sysdate, and then use this defined variable in procedure to insert directly into the table. A very simple implementation of it.

But strangely, every time the insert into the table is somehow changing, sometimes it's right, sometimes it's an indeterminate time, and sometimes it's just like the last time, it's really a headache.

Let's take a look at the code snippet below.

Package Header:

2. Create or Replace package pkg_life_aml_data is
6. current_time Date: = Sysdate;
10. procedure T_normal (i_start_date in varchar2,
12. I_end_date in Varchar2,
14. i_organ_id in t_company_organ.organ_id%type);

Package body (the code that takes only the most critical parts):

2. Procedure T_normal (I_start_date in Varchar2,
4. I_end_date in Varchar2,
6. i_organ_id in t_company_organ.organ_id%type) is
10. INSERT INTO t_su_data
12. (Insert_time)
14. Values
16. (current_time);
18. end;

Through the above code is not difficult to see, this is actually a very simple conding ... But it does have a problem. The problem is being in the position of this current_time definition, which is actually a global variable.

The global variable in Plsql is given a value when the package is initialized, and will not change in the same session, so this leads to the reason why the time of the insert is incorrect, but it is not clear how Oracle determines the start and end of a session, because testing , whose time is sometimes correct.

So, the correct way is to insert the data, directly use the sysdate, and not in the Baotou place to assign sysdate to Current_time, so whether in Java or in the Plsql global variables can not be used, then when can use it?

1. The simplest, it is a constant, it will never change, then the definition of global variables will be very useful.

2.package_b want to use a value or a variable in the package_a, then you can define a global variable current_time (not assigned, only defined) in Package_a, and assign it to the stored procedure, and then switch to Package_b. Then in Package_b only need to call Package_a.current_time, you can get the value of the variable in package_a, and then do some operations in package_b based on this value. This is very useful.

To summarize, do not define variables as global variables! It's too dangerous.

Advantages and disadvantages of "top" global variables