Data room charging system-use of stored procedures and charging system storage process

Data room charging system-use of stored procedures and charging system storage process

In the "checkout" section of the data center billing system, You must select an operator and click the "checkout" button to change the registration, recharge, and return business status of the operator to "settled ". Registration, recharge, and refund are recorded in three tables. If you follow the traditional method, you need to write three functions at the DAL layer, and update the isCheck of each table to "true ", not to mention how much code is written and how much effort is required. This reduces the system running speed and is prone to errors.

In the restructuring of the charging system for the individual version of the data room, We don't just need to implement the functions as we used to, but become "lazy" and Say No to repetitive work, the stored procedure is introduced here to solve this problem. A stored procedure is a module written using SQL statements and flow control statements. After compilation and optimization, it is stored in the database on the database server and can be called during use.

1. Create a stored procedure:

Select "programmability" under the stored procedure to be used, click "+", select "Stored Procedure", and right-click "create stored procedure" in the shortcut menu ", the following interface is displayed. A lot of code is actually a template. You just need to fill it out.

Note: The data type must be specified after the parameter, which is consistent with the corresponding field type in the database.

2. Use the stored procedure's D-Layer Code:

'After checkout, change the user's business [Recharge, refund, registration Card] isCheck to true Public Function UpdateIsCheck (ecard As Card) As Boolean Implements ICheckOut. updateIsCheck Dim sqlHelper As New SqlHelper. sqlHelper instantiate sqlHelper class Dim primitive type As <strong> CommandType </strong> = CommandType. storedProcedure 'defines the command type. Stored Procedure <strong> Dim execution text As String = "PROC_CheckOut" </strong> 'database execution string' transmits the parameter Dim params As SqlParameter () params = {New SqlParameter ("@ ischeck", ecard. proisCheck), New SqlParameter ("@ userID", ecard. prouserID)} Return sqlHelper <strong>. executeNoQuery (plain text, partition type, params) </strong> End Function

3. SQL statement code:

Public Function ModifyPwd(euser As User) As Integer Implements IPwd.ModifyPwd        Dim strSQL As String = "update T_User set userPwd =@pwd where userID=@userID"        Dim params() As SqlParameter = {New SqlParameter("@pwd", euser.ProuserPwd), New SqlParameter("@userID", euser.ProuserID)}        Dim helper As New SqlHelper.sqlHelper        Dim result = helper.ExecuteNoQuery(strSQL, CommandType.Text, params)        Return result    End Function

Through the comparison above, it is found that the use of stored procedures is very simple. You only need to change CommandType to StoredProcedure, instead of carefully writing SQL statements, but instead of the name of the written stored procedure.

Compared with using SQL statements directly, calling stored procedures in applications has the following benefits: reduces programming workload and reduces network traffic. The network traffic for calling a stored procedure with few rows of data may not be significantly different from that for directly calling SQL statements. However, if the Stored Procedure contains hundreds of rows of SQL statements, therefore, its performance is definitely much higher than that of a single SQL statement. The database has been parsed and optimized during the creation of the stored procedure. Once a stored procedure is executed, a stored procedure will be retained in the memory, so that you can directly call the stored procedure from the memory the next time you execute the same stored procedure, thus improving the execution speed. At the same time, SQL functions and flexibility are enhanced to indirectly implement security control.

This is the first time to use the stored procedure and will be continuously learned and used in the future. After taking the first step, the road ahead will be easy.

School Data room Charging System

Baidu search Internet cafe management system or IDC billing system, you can find many.

Using Stored Procedures in oracle to call Functions

I also want to do this. The following program has been tested and the cost value can be obtained normally. It is normal to insert the cashier information table !!
-- Create a table
Create table member_info (
Memberid NUMBER,
Memberlevel VARCHAR2 (20 ),
Memberdis NUMBER)
;
Create table cost_info (
Memberid NUMBER,
Spend FLOAT,
Cost float)
;

Insert into member_info VALUES (1, 'a, 0.1 );
Insert into member_info VALUES (2, 'a, 0.2 );
Insert into member_info VALUES (3, 'a, 0.3 );
Insert into member_info VALUES (4, 'a, 0.4 );
-- Create a function
Create or replace function function_mem (p_memeberid member_info.MEMBERID % TYPE, p_spend cost_info.SPEND % TYPE)
RETURN FLOAT
IS
V_cost cost_info.COST % TYPE;
V_memberdis member_info.MEMBERDIS % TYPE;
BEGIN
SELECT memberdis INTO v_memberdis FROM member_info WHERE memberid = p_memeberid;
V_cost: = v_memberdis * p_spend;
RETURN v_cost;
EXCEPTION
WHEN OTHERS THEN
Dbms_output.put_line (SQLERRM );
END;
-- Create procedure
Create or replace procedure proc_mem (p_memeberid member_info.MEMBERID % TYPE, p_spend cost_info.SPEND % TYPE)
IS
V_cost cost_info.COST % TYPE;
BEGIN
SELECT function_mem (p_memeberid, p_spend) INTO v_cost FROM dual;
Insert into cost_info VALUES (p_memeberid, p_spend, v_cost );
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
Dbms_output.put_line (SQLERRM );

END;
---- Execute procedure
BEGIN
Proc_mem (1,1000 );
END;... remaining full text>