Two steps for calling the Oracle Stored Procedure

The following articles mainly describe the content of the Oracle stored procedure. In fact, calling the stored procedure is the process of writing the SQL statement related to flow control, this process is compiled and optimized and stored on the database server. You only need to call FienReport for use.

The following uses a specific example to learn how to use FineReport to call the Oracle stored procedure.

Step 1: Define the stored procedure.

StScroe is a table in the cmdele database, which records the student's score information. The table structure is as follows:

Define the Stored Procedure for the returned list-because the Oracle stored procedure does not return values, all the returned values are replaced by the out parameter, and the list is no exception, but because it is a set, therefore, you cannot use common parameters. You must use pagkage. Therefore, the stored procedure must be defined in two parts:

Create a package as follows:

 
 

  
  
CREATE OR REPLACE PACKAGE TESTPACKAGE AS  
  
  
TYPE Test_CURSOR IS REF CURSOR;  
  
  
END TESTPACKAGE;  
 
 

Create a stored procedure:

 
 

  
  
CREATE OR REPLACE PROCEDURE p_STSCORE
(Class in varchar2,p_CURSOR out TESTPACKAGE.Test_CURSOR) IS  
  
  
BEGIN  
  
  
OPEN p_CURSOR FOR SELECT * FROM FR.STSCORE where STSCORE.ClassNo=Class;  
  
  
END p_STSCORE;  
 
 

Step 2: Call the stored procedure.

Start the FineReport designer, right-click the data source panel, and select private data source. The private data source dialog box is displayed.
Click the Add button to create a private data source named ds1. Set database to Oracle data source and query type to stored procedure. In the SQL text box, write the following statement to call the stored procedure:

 
 

  
  
{call fr.p_stscore('[?Class|Class1?]',?)} 
 
 

3. Click Preview to preview the queried data, as shown below:

So far, the FineReport designer has successfully called the Oracle stored procedure. You can design the report style as needed.