ORACLE developer/2000 Programming Skills Six

----ORACLE as a large database management system, its powerful and perfect data management function attracts more and more people's attention. In Oracle system development, I have the following experience in developer/2000 programming, hoping to provide some references for beginners.

----A, use Settings menu item properties and form data item properties to restrict the access scope of different users

----An application system often has different users, such as: A labor system, a user is responsible for the management of the basic files of employees, another user is responsible for the management of employee payroll, the management of basic records of employees of employees only query permissions and can not have permission to modify. A simple way to achieve these functions is to:

----set two global variables record user name and password respectively, when entering the system, two users enter different username and password respectively, judge user name, make Set_menu_property (Menu1,enabled,false) Statement to set a user without query permission cannot access the menu item ' MENU1 '.

----Two, improve the query speed of table connection

----When a table joins a query, the following query methods are often used to query the data:

SELECT Recordno,name,age

From table 1

WHERE table 1. Recordno not in

(SELECT Recordno

From table 2

WHERE birthday= ' 710618 ');

----The author found that if the length of table 1 is 6,000 records, the length of table 2 is 1000 records, it takes 4 minutes to produce results. The reason for this is that the comparison operator, not in, is used, and its logical test speed is the slowest. Replaces the not in operator with an outer join, and the query time is shortened to 50 seconds. The modification method is as follows:

SELECT Recordno,name,age

From table 1, table 2

WHERE table 1. recordno= table 2. Recordno (+)

and table 2. Recordno is NULL

and table 2. Birthday (+) = ' 710618 ';

----Three, use library module to realize module resource sharing

----in programming many from modules need to share a single program unit. ORACLE FORM 4.5 presents the concept of a library module. The library module is used to establish the shared program cell Library, which reduces the disk footprint and increases the maintainability of the program. The process of using library module to share program unit is: Click on the File-new-liberary option of the System menu, set up a library module, add a program unit under Programs units node, write the unit code that needs to share the program, and save after compiling. When the form module needs to be shared, connect the library module under the module's attached libraries node (when the screen prompts you to delete the path, the user chooses No). This allows the user to use the shared program unit directly in the form module.

----Four, causes of error in query data easily

----When executing a data query, it is often found that the results of the query differ from those expected, mainly because:

----1. Use the SELECT * from < table name > WHERE < field name > like '% ' statement to query for fields with the < field name > NULL. This should be handled in addition to the case where the field is null. such as: SELECT * from < table name > WHERE < field name > like '% ' OR < field name > is NULL

----2. When you connect tables by using the Union field, the connection order is not in the order of writing, but by the first field in the join table. Therefore, in order for the tables to be connected in the desired order, insert the first field in each table to specify the sort ordinal.

----v. Using views to organize report data is simple and easy to maintain

----ORACLE REPORT2.5 provides a variety of report formats, but in the report to define a variety of statistical information not only cumbersome and not conducive to future maintenance, use the view of the various statistics, summary data prepared, can play a multiplier effect, once the data source has been adjusted, just modify the background view, Instead of changing the front-end program.

Table 1 structure: unit NAME Recoreno SEX

Office King 51 Men

Finance Section Lee 42 female

.. .. .. ..

----1. Set up the view to summarize the data first:

CREATE VIEW rytj (unit, Boy,girl)

As SELECT unit, SUM (DECODE (SEX, ' man ')

, 1,0)), SUM (DECODE (SEX, ' female ', 1,0))

from table 1;

----2. Use ORACLE report 2.5 to view ' RYTJ '.

----Six, a simple way to add serial numbers to the report

----ORACLE Report 2.5 does not provide a serial number function, the following simple method can be used to automatically add a serial number: In the data template, add a formula column to the query group CF_XH (4), assign an initial value of 0, click the [EDIT] button, insert the code return (: cf_ch+1).