createdatabase.sql

县级供电企业电费核算源码, 在客户处正常运行8年以上, Delphi 5开发,数据库为Interbase/Firebird, 深入使用Procedure和Trigger等, 对入门者具有很好的参考价值

CREATE PROCEDURE P_MAXTOWNNO AS BEGIN EXIT; END ^
CREATE PROCEDURE P_MAXVILLAGENO AS BEGIN EXIT; END ^
CREATE PROCEDURE P_MAXLINENO AS BEGIN EXIT; END ^
CREATE PROCEDURE P_MAXTRANSFERNO AS BEGIN EXIT; END ^
CREATE PROCEDURE P_MAXHOUSENO AS BEGIN EXIT; END ^
CREATE PROCEDURE P_GETAMMETER AS BEGIN EXIT; END ^
CREATE PROCEDURE P_GETAMMETERNO AS BEGIN EXIT; END ^
CREATE PROCEDURE P_GETUSEDCOUNT AS BEGIN EXIT; END ^
CREATE PROCEDURE P_ADDFEE AS BEGIN EXIT; END ^
CREATE PROCEDURE P_HOUSEUSEDCOUNT AS BEGIN EXIT; END ^
CREATE PROCEDURE P_HOUSESETPRICE AS BEGIN EXIT; END ^
CREATE PROCEDURE P_HOUSEFEE AS BEGIN EXIT; END ^
CREATE PROCEDURE P_AMMETERMOVE AS BEGIN EXIT; END ^
CREATE PROCEDURE P_HOUSEMONTHFEE AS BEGIN EXIT; END ^
CREATE PROCEDURE P_SETTHIS_COUNT AS BEGIN EXIT; END ^
CREATE PROCEDURE P_GETTRANSFERINFO AS BEGIN EXIT; END ^
CREATE PROCEDURE P_SETTHIS_NULL AS BEGIN EXIT; END ^
CREATE PROCEDURE P_MERGETRANSFER AS BEGIN EXIT; END ^
CREATE PROCEDURE P_DISBRANCHTRANSFER AS BEGIN EXIT; END ^
CREATE PROCEDURE P_AMMETERREPLACE AS BEGIN EXIT; END ^
CREATE PROCEDURE P_TRANSFERALTERLINE AS BEGIN EXIT; END ^
CREATE PROCEDURE P_COPYRATE AS BEGIN EXIT; END ^
CREATE PROCEDURE P_GETHOUSEARREARAGE AS BEGIN EXIT; END ^
CREATE PROCEDURE P_REBUILDTRANSFERFEE AS BEGIN EXIT; END ^
CREATE PROCEDURE P_LINECOPYRATE AS BEGIN EXIT; END ^
CREATE PROCEDURE P_LNO2HOUSENO AS BEGIN EXIT; END ^
CREATE PROCEDURE P_CWC2AMMETER AS BEGIN EXIT; END ^
CREATE PROCEDURE P_CALCCOOKS AS BEGIN EXIT; END ^
CREATE PROCEDURE P_CALCTRANSFERCOOKS AS BEGIN EXIT; END ^

ALTER PROCEDURE P_LOG (V_LOG VARCHAR(100),
V_TYPE CHAR(20))
AS 

begin
   Insert into tb_Log (f_date, f_type, f_Log) 
      values ('now', :v_type, :v_Log);
end
 ^

ALTER PROCEDURE P_ADDMSG (V_SENDER CHAR(20),
V_CONTENT CHAR(80))
AS 

begin
   Insert into tb_msg (f_date, f_sender, f_content) 
      values ('now', :v_sender, :v_content);
end
 ^

ALTER PROCEDURE P_SETMSG (V_SENDER CHAR(20),
V_CONTENT CHAR(80))
AS 

begin
   delete from tb_msg;
   Insert into tb_msg (f_date, f_sender, f_content) 
      values ('now', :v_sender, :v_content);
end
 ^

ALTER PROCEDURE P_GETMSG RETURNS (V_SENDER CHAR(20),
V_CONTENT CHAR(80))
AS 

declare variable v_id integer;
begin
   Select max(f_id) from tb_msg into :v_id;
   if ( v_id is null) then
   begin
       v_sender = '';
       v_content = '';
   end
   else
   begin
      /*取最新的一条*/
      select f_sender, f_content
         from tb_msg
         where f_id = :v_id
         into :v_sender, :v_content;
      delete from tb_Msg;/*删除消息,以免下一次取到*/
   end
end
 ^

ALTER PROCEDURE P_UPD_PASSWORD (V_PERSON_NO CHAR(8),
V_PASSWORD CHAR(6))
AS 

Begin
   Update Tb_Person Set F_Person_Password = :V_Password Where F_Person_No = :V_Person_No;
End
 ^

ALTER PROCEDURE P_GETHOUSE (V_HOUSE_NO CHAR(12),
V_AMMETER_ORDER INTEGER)
RETURNS (V_HOUSE_NAME CHAR(20),
V_AMMETER_LAST_COUNT NUMERIC(15, 2))
AS 

Declare Variable v_Date Date;/*上次抄表时间*/
Begin
   v_house_name = '';
   v_Ammeter_last_count = 0;
   /*取得户名*/
   Select F_House_Name 
      From Tb_House
      Where f_House_No = :v_House_No
      Into :v_House_Name;
   /*取得上次抄表值*/
   Select f_Ammeter_Last_Count
      From Tb_Ammeter
      Where f_House_No = :v_House_No And f_Ammeter_Order = :v_Ammeter_Order
     Into :v_Ammeter_Last_Count;
End
 ^

ALTER PROCEDURE P_GETLAST (V_HOUSE_NO CHAR(12),
V_AMMETER_ORDER INTEGER)
RETURNS (V_AMMETER_LAST_COUNT NUMERIC(15, 2))
AS 

Declare Variable v_ammeter_count numeric(10,2);/*计量表底数*/
Begin
   /*取得上次抄表值*/
   Select f_ammeter_count, f_ammeter_last_count
      From Tb_Ammeter
      Where f_house_no = :v_house_no And f_Ammeter_Order = :v_Ammeter_Order
      Into :v_ammeter_count, :v_ammeter_last_count;
   if (v_ammeter_last_count is null) then/*还没有抄过表,则返回底数*/
      v_ammeter_last_count = v_ammeter_count;
End
 ^

ALTER PROCEDURE P_SETCURRENT (V_CLASS CHAR(10),
V_VALUE CHAR(20))
AS 

begin
   Delete From tb_Current Where f_class = :v_class;
   Insert Into Tb_Current (f_Class, f_Value) Values (:v_Class, :v_Value);      
end
 ^

ALTER PROCEDURE P_GETPRICE (V_ELECT_NO CHAR(2))
RETURNS (V_PRICE NUMERIC(15, 2))
AS 

Begin
   Select f_price
      from tb_Elect
      where f_elect_no = :v_elect_no 
      into :v_price;
   if (v_price is null) then
      v_price = 0;
End
 ^

ALTER PROCEDURE P_GETSERVICEPRICE (V_ELECT_NO CHAR(2))
RETURNS (V_SERVICE_PRICE NUMERIC(15, 2))
AS 

Begin
   Select f_Service_Price
      From tb_Elect
      Where f_Elect_No = :v_Elect_No
      Into :v_Service_Price;
   If (v_Service_Price is Null) Then
      v_Service_Price = 0;
End
 ^

ALTER PROCEDURE P_GETCOUNTRYPRICE (V_ELECT_NO CHAR(2))
RETURNS (V_COUNTRY_PRICE NUMERIC(15, 2))
AS 

Begin
   Select f_Country_Price
      From tb_Elect
      Where f_Elect_No = :v_Elect_No
      Into :v_Country_Price;
   If (v_Country_Price is Null) Then
      v_Country_Price = 0;
End
 ^

ALTER PROCEDURE P_GETCOUNTRY (V_HOUSE_NO CHAR(12))
RETURNS (V_COUNTRY_PRICE1 NUMERIC(15, 2),
V_COUNTRY_PRICE2 NUMERIC(15, 2),
V_COUNTRY_PRICE3 NUMERIC(15, 2),
V_COUNTRY_PRICE4 NUMERIC(15, 2),
V_COUNTRY_AMOUNT1 NUMERIC(15, 2),
V_COUNTRY_AMOUNT2 NUMERIC(15, 2),
V_COUNTRY_AMOUNT3 NUMERIC(15, 2),
V_COUNTRY_AMOUNT4 NUMERIC(15, 2),
V_COUNTRY_AMOUNT NUMERIC(15, 2))
AS 

Declare variable v_elect_no1 char(2);
Declare variable v_elect_no2 char(2);
Declare variable v_elect_no3 char(2);
Declare variable v_elect_no4 char(2);
Declare variable v_count1 numeric(10,2);
Declare variable v_count2 numeric(10,2);
Declare variable v_count3 numeric(10,2);
Declare variable v_count4 numeric(10,2);
Begin
   Select f_Elect_No, 
          f_elect_no1,
          f_elect_no2,
          f_elect_no3,
          f_Count, 
          f_count1,
          f_count2,
          f_count3
      from tb_house
      where f_house_no = :v_house_no
      into :v_elect_no1,
           :v_elect_no2,
           :v_elect_no3,
           :v_elect_no4,
           :v_count1,
           :v_count2,
           :v_count3,
           :v_count4;

   execute procedure p_getCountryPrice :v_elect_no1 returning_values :v_Country_price1;
   execute procedure p_getCountryPrice :v_elect_no2 returning_values :v_Country_price2;
   execute procedure p_getCountryPrice :v_elect_no3 returning_values :v_Country_price3;
   execute procedure p_getCountryPrice :v_elect_no4 returning_values :v_Country_price4;

   v_Country_Amount1 = f_45(v_Count1 * v_Country_Price1,2);
   v_Country_Amount2 = f_45(v_Count2 * v_Country_Price2,2);
   v_Country_Amount3 = f_45(v_Count3 * v_Country_Price3,2);
   v_Country_Amount4 = f_45(v_Count4 * v_Country_Price4,2);

   /*国家价合计*/
   /*v_Country_Amount =   v_Country_Amount1 + v_Country_Amount2 + v_Country_Amount3 + v_Country_Amount4;*/
/*以下适用于国家价有补差额的情况*/
   select f_CountryAmount
      From tb_Fee
      Where f_House_No = :v_House_No
      Into :v_Country_Amount;
End
 ^

ALTER PROCEDURE P_GETSERVICE (V_HOUSE_NO CHAR(12))
RETURNS (V_SERVICE_PRICE1 NUMERIC(15, 2),
V_SERVICE_PRICE2 NUMERIC(15, 2),
V_SERVICE_PRICE3 NUMERIC(15, 2),
V_SERVICE_PRICE4 NUMERIC(15, 2),
V_SERVICE_AMOUNT1 NUMERIC(15, 2),
V_SERVICE_AMOUNT2 NUMERIC(15, 2),
V_SERVICE_AMOUNT3 NUMERIC(15, 2),
V_SERVICE_AMOUNT4 NUMERIC(15, 2),
V_SERVICE_AMOUNT NUMERIC(15, 2))
AS 

Declare variable v_elect_no1 char(2);
Declare variable v_elect_no2 char(2);
Declare variable v_elect_no3 char(2);
Declare variable v_elect_no4 char(2);
Declare variable v_count1 numeric(10,2);
Declare variable v_count2 numeric(10,2);
Declare variable v_count3 numeric(10,2);
Declare variable v_count4 numeric(10,2);

Begin
   Select f_Elect_No, 
          f_elect_no1,
          f_elect_no2,
          f_elect_no3,
          f_Count, 
          f_count1,
          f_count2,
          f_count3
      from tb_house
      where f_house_no = :v_house_no
      into :v_elect_no1,
           :v_elect_no2,
           :v_elect_no3,
           :v_elect_no4,
           :v_count1,
           :v_count2,
           :v_count3,
           :v_count4;

   execute procedure p_getServicePrice :v_elect_no1 returning_values :v_Service_price1;
   execute procedure p_getServicePrice :v_elect_no2 returning_values :v_Service_price2;
   execute procedure p_getServicePrice :v_elect_no3 returning_values :v_Service_price3;
   execute procedure p_getServicePrice :v_elect_no4 returning_values :v_Service_price4;

   /*农网维护费=金额-国家费*/
   v_Service_Amount1 = f_45(v_Count1 * v_Service_Price1,2);
   v_Service_Amount2 = f_45(v_Count2 * v_Service_Price2,2);
   v_Service_Amount3 = f_45(v_Count3 * v_Service_Price3,2);
   v_Service_Amount4 = f_45(v_Count4 * v_Service_Price4,2);

   /*农网维护费合计*/
   /*v_service_Amount = V_Service_Amount1 + V_Service_Amount2 + V_Service_Amount3 + V_Service_Amount4;*/
/*以下适用于农维费有补差额的情况*/
   select f_ServiceAmount
      From tb_Fee
      Where f_House_No = :v_House_No
      Into :v_Service_Amount;
End
 ^

ALTER PROCEDURE P_GETCURRENT (V_CURRENTDATE CHAR(20))
RETURNS (V_CURRENTYEAR CHAR(4),
V_CURRENTMONTH CHAR(2))
AS 

Declare variable v_year smallint;
Declare variable v_month smallint;
Declare variable v_day smallint;
Declare variable v_Date Date;
Begin
   /*如果未指定日期，则取当前日期*/
   If (v_CurrentDate = '') Then
      v_Date = Cast('Now' As Date);
   Else
      v_Date = Cast(v_CurrentDate As Date);
   v_year = f_year(v_Date);
   v_month = f_month(v_Date);
   v_day = f_day(v_Date);
   If ( v_month < 10 ) Then
      V_CurrentMonth = '0' || Cast(v_month As Char(1));
   Else
      V_CurrentMonth = Cast(v_month As Char(2));
   V_CurrentYear = Cast(v_year as Char(4));
End
 ^

ALTER PROCEDURE P_GETVILLAGENAME (V_VILLAGE_NO CHAR(5))
RETURNS (V_VILLAGE_NAME CHAR(20))
AS 

Begin
   Select f_Village_Name
      From tb_Village
      Where f_Village_no = :v_village_No
      into :v_Village_Name;
   If (v_Village_Name Is Null) Then
      v_Village_Name = '';
End
 ^

ALTER PROCEDURE P_GETADDRESS (V_HOUSE_NO CHAR(12))
RETURNS (V_ADDRESS CHAR(50))
AS 

Declare variable v_Town_No Char(3);
Declare variable v_Town_Name Char(20);
Declare variable v_Village_No Char(5);
Declare variable v_Village_Name Char(20);
Declare variable v_Position Char(10);
Begin
   select f_village_no
      from tb_House
      Where f_house_no = :v_house_no
      Into :v_Village_No;
   If ( v_Village_No is Null) then
   Begin
      v_Address = '';
   End
   Else
   Begin
      V_Town_No = f_substr(v_village_no,1,3);
      Select f_Town_Name
         From tb_Town
         Where f_town_no = :v_Town_No
         into :v_town_Name;
      Select f_Village_Name
         From tb_Village
         Where f_Village_no = :v_village_No
         into :v_Village_Name;
      Select f_Position
         From tb_House
         Where f_house_no = :v_house_No
         into :v_position;
      If (v_town_name = v_village_name) then
         v_Address = f_trim(v_village_name) || f_trim(v_position);
      else
         v_Address = f_Trim(v_town_Name) || f_trim(v_village_name) || f_trim(v_position);
   End
End
 ^

ALTER PROCEDURE P_GETLINE (V_HOUSE_NO CHAR(12))
RETURNS (V_LINE CHAR(20))
AS 
  
Declare variable v_Line_No Char(3);
Begin
   v_Line_No = f_Substr(v_house_no,1,3);
   Select f_Line_Name
      From tb_Line
      Where f_Line_no = :v_Line_No
      into :v_Line;
   If ( v_Line Is Null) Then
      v_Line = "";
End
 ^

ALTER PROCEDURE P_SETPRICE AS 
 
Declare variable v_Elect_No Char(2);
Declare variable v_Price Numeric(10,2);
Begin
   /*清除原有价格*/
   Update Tb_House 
         Set F_Price = 0,