Oracle stored procedure Test

Source: Network/Editor: getting started with programming: Unknown

Store test information in Normal Mode

Create related data tables

Create the data table testinfolog and the serial number seq_testinfolog.

Run this script by referring to testinfolog. SQL in the current directory.

Create a Test Information Storage Package

Create a package for storing test information

Compile this package by referring to pkg_test_info.pck in the current directory.

Example

Example 1

Use a test package in a PL/SQL block to save the test information. For more information, see example1. SQL in the current directory.

-- A simple example of result Query

Select * From testinfolog
Declare
I INTEGER: = 1;
Begin
Pkg_test_info.clearallloginfo;
Pkg_test_info.setlogcontext ('Anonymous block test', 'none', 1 );
Pkg_test_info.loginfo ('I = 1 ');
Pkg_test_info.loginfo ('current date = '| to_char (sysdate, 'yyyy-mm-dd '));
End;

Example 2

Store test information in a stored procedure

See test_pkg_test_info.prc in the current directory.

--- Save the test information in the stored procedure and query the result select * From testinfolog

Create or replace procedure test_pkg_test_info (p_param1 int,
P_param2 int,
P _ save log information int default 1) is
Begin
If (P _ save log information) = 1 then
Pkg_test_info.clearallloginfo;
Pkg_test_info.setlogcontext ('stored procedure test', 'test _ pkg_test_info ', 1 );
　　
Pkg_test_info.loginfo ('P _ param1 = '| to_char (p_param1) |', p_param2 ='
| To_char (p_param2 ));
End if;
End;

Uninstall script

Refer to uninsall. SQL. If you need to uninstall and run this script

Other methods to save test information

Introduction to log4plsql

Log4plsql is an open source tool and a log framework implemented under oralce PL/SQL.

Log4plsql is developed based on the evolution of log4j.

Related Sites

Http://log4plsql.sourceforge.net/

Http://sourceforge.net/projects/log4plsql/

Log4plsql is used for testing.

1. Save the test information of the trigger.

An example of a trigger. related information is recorded in the trigger.

The code is located in the log_dml. SQL directory of the current file.

　　CREATE OR REPLACE TRIGGER LOG_DML BEFORE
　　INSERT OR UPDATE OR DELETE
　　ON T_ESSAIS FOR EACH ROW
　　BEGIN
　　IF DELETING OR UPDATING THEN
　　PLOG.INFO('T_ESSAIS:OLD:'||USER||':'||ld.data);
　　END IF;
　　IF INSERTING OR UPDATING THEN
　　PLOG.INFO('T_ESSAIS:NEW:'||USER||':'||:new.data);
　　END IF;
　　end;

2. Save test information during the Stored Procedure

Perform log operations during the storage process and record the parameter information stored in the city to the log data table.

The code is located in testassert. SQL of the directory where the current file is located.

Create or replace procedure testassert (p_param1 int, p_param2 varchar2)

Is

Pctx plog. log_ctx;

Begin

-- If p_param1 <= 1, the related information will be written to the log.

Plog. Assert (pctx, p_param1> 1, 'P _ param1> 1 always false ');

-- If p_param2 <> 'man ', the related information will be written to the log.

Plog. Assert (p_param2 = 'man ', 'P _ param2 <> man ');

Plog. Assert (1 is null, '1 is null always false ');

Plog. Assert (not 1> 1, 'not 1> 1 never false ');

Plog. Assert (1> 2, '1> 2 always false',-20001,

Praiseexceptioniffalse => true,

Plogerrorreplaceerror => false );

Plog. Assert (1> 3, 'Never test there is a raise in previous assert ');

End;

3. A log example of a tree directory is implemented.

The code is located in testassert2. SQL of the directory where the current file is located.

/**
* <P> purpose: this is an example of logging test information during the stored procedure. The tree directory logging method is implemented.
* </P>
* <P>
* Usage
* <Li> compile the Stored Procedure </LI>
* <Li>
* Delete the log information in the tlog table: delete from tlog
* </LI>
* <Li> test the Stored Procedure
* Begin testassert2 (10, 'wdz123 @ hotmailcom '); end;
* </LI>
* <Li> View the result select from tlog </LI>
* </P>
* @ Param p_param1 is an entry parameter. Its value is recorded in the log.
* @ Param p_param2 is another entry parameter. Its value is recorded in the log.
***/
Create or replace procedure testassert2 (p_param1 int, p_param2 varchar2) is
V_ctx plog. log_ctx;
V_year varchar2 (4 );
Begin

--- Set the root node name of log information

V_ctx: = plog. INIT ('test information ');

--- Construct a 2nd level tree log

Plog. setbeginsection (v_ctx, 'Procedure _ testassert2 _ test information ');

Plog. setbeginsection (v_ctx, 'detection entry parameter ');

-- If p_param1 <= 1, the related information will be written to the log.

Plog. Assert (v_ctx, p_param1> 1, 'P _ param1> 1 always false ');

-- If p_param2 <> 'man ', the related information will be written to the log.

Plog. Assert (p_param2 = 'man ', 'P _ param2 <> man ');

--- Close the 2nd directory of tree logs

Plog. setendsection (v_ctx, 'detection entry parameter ');

--- The following is a sequence of processing based on actual business needs.

NULL; --- here is some business processing code

--- Actual end-related business processing

--- Check the service processing result

-- Construct another level 2 tree log directory

Plog. setbeginsection (v_ctx, 'check Service Processing result ');

--- Here is the check code for the relevant processing results based on actual needs.

　　select to_char(sysdate, 'YYYY') into v_Year from dual;
　　PLOG.assert(v_CTX,
　　v_Year = '2003',

'Current year should be = 2003, actually = '| v_year );

--- End this is the check code for the relevant processing results based on actual needs.

-- Disable the 2nd directory of the current tree log

Plog. setendsection (v_ctx, 'check Service Processing result ');
Plog. setendsection (v_ctx, 'Procedure _ testassert2 _ test information ');
End;

Application of log4plsql

Because plog provides assert processing, you can consider checking the test results in the stored procedure and package, and save the test results as needed, for testing algorithm-based stored procedures, you can use scripts to perform automated regression testing.

Example

1. Description:

This example may not be applied (there is no relevant database environment, and the corresponding package is missing), but some scripts can be opened to illustrate the meanings of related expressions. If it is within the company, it can run under the Fuzhou project database.

2. References

Refer to the test of the owner integration algorithm with the application. If there is an application, there is no property loft, no property rights sharing, and no population resettlement.

Related stored procedures/Packages

Pkg_condebuginfo.normal_ownerreq_recursive

Plog package

Related Documents

Reference material test example of how to apply for an integrated calculation. Doc

The input of test data is manually input. All scripts can be generated as needed.

Reference document: test the testing data requirements listed in the testing .doc of the integrated calculation method of the applicant.

Dynamically Modify/generate test data. Test Data is dynamically changed/generated based on test cases.

See pkg_condebuginfo.normal_owner_updatedata3

Test result check

See pkg_condebuginfo.normal_owner_check_data3

Extension

In order to make the test information based on PL/SQL better. You can do this from the following aspects.

1. You can modify the plog package provided by log4plsql by yourself,

1.1 modify the tlog of the data table and add some fields to save other information (for example, you can save the Client IP address ).

Modify the Stored Procedure plog. addrow and save the relevant information to the test data table. ---Www.bianceng.cn

You can obtain some running environment information from the background, for example, using the following method to obtain some information

Sys_context ('userenv', 'current _ user ')

Sys_context ('userenv', 'IP _ address ')

1.2 add other methods.

You can add other methods to the plog package as needed, such as log deletion.

It is better to package another layer 1, like the above Oracle package pkg_condebug, to define a package for some related packaging.

2. Modify the view vlog to display some test results as required.

For example, create the following view.

Create or replace view vlog2
Select
Luser database user,
Plog. getlevelintext (Llevel) as test information level,
Lsection log directory,
Ltexte error message,
Ldate generation error time
From tlog

3. Automated algorithm Testing

The test data generated in the above log4plsql application can be enhanced as needed, and the test data can be generated randomly based on business needs. Or Generate Test Data Based on key cases.