Dameng database stored procedures and stored procedure calls

/***manager***/create OR REPLACE PROCEDURE "gzapp_09". " Sp_olap_v_buildingtype "(" P_date "in DATE)
AUTHID definer
is
/*========================================== ===================
* Function Description: Housing type analysis (high-rise buildings, multi-storey buildings, temporary huts, others)
* Statistical city level, administrative district, Street Town, neighborhood Committee
* Entry Parameters: Statistical time
* Export parameters: None
* Creator and time: DFB 20160922
* Modify people and time:
*============================================================*/
V_sqlcode Number
V_SQLERRM varchar2 (4000);
V_begintime varchar2 (20);
V_endtime varchar2 (20);
V_FWDLX varchar2 (10);
V_sql VARCHAR2 (5000);
BEGIN
V_begintime:=to_char (sysdate, ' yyyy/mm/dd hh24:mi:ss ');
EXECUTE IMMEDIATE ' TRUNCATE TABLE tn_olap_v_buildingtype_tmp1 ';
Delete from Olap_v_buildingtype;
Commit;

INSERT INTO TN_OLAP_V_BUILDINGTYPE_TMP1 (
Tjqy,
Qydm,
FWLX01,
FWLX02,
FWLX03,
FWLX04 )

SELECT tjqy,
QYDM,
fwlxgcly FWLX01,--High-rise building type building number
fwlxdcly FWLX02,--Low-rise building type building number
FWLXLSPW FWLX03,--Temporary Shack type building number
FWLXQT FWLX04--other types of buildings
From T_STAT_CZWD
where tjrq=p_date
and bblx= ' M ';
COMMIT;

--01-04 types of housing buildings
For FWDLX in 1.. 4 loop

If FWDLX < ten Then
V_FWDLX: = ' 0 ' | | FWDLX;
Else
V_FWDLX: = FWDLX;
End If;

V_sql: = ' INSERT into Olap_v_buildingtype
(buitypedm,--Building type Code
BUITYPEMC,--Housing Building type name
Tjqy,--Statistical Area
QYDM,--Area Code
QYMC,--Zone Name
XZQDM,--Administrative Code
XZQ,--the name of the canton
JZDM,--Street Town Code
JZ,--Street town name
JWHDM,--Neighborhood Code
JWH,--the name of the Neighborhood Committee
Number of amount--
)
Select T.FWDLX,
A.MC,
T.tjqy,
T.QYDM,
case where tjqy= ' shijb ' Then ' city '
When tjqy= "Xzq" then B.xzq
When tjqy= "JZ" then B.JZ
When tjqy= "JWH" then B.JWH
End QYMC,
B.XZQDM,
B.XZQ,
B.JZDM,
B.JZ,
B.JWHDM,
B.JWH,
T.amount
From (select "| | v_fwdlx| | " FWDLX,QYDM, Fwlx ' | | v_fwdlx| | ' amount,tjqy from TN_OLAP_V_BUILDINGTYPE_TMP1) T,
--(SELECT * from T_xtzd where dmlx= ' fwlx ') A,
(select Buitypedm dm,buitypemc mc,id from Olap_measure_buildingtype) A,--change the system dictionary table to this dimension table modified to 2016.10.11 DFB
Olap_v_localarea b
where t.fwdlx=a.id
and T.QYDM=B.QYDM ';
Execute immediate v_sql;
End Loop;

V_endtime:=to_char (sysdate, ' yyyy/mm/dd hh24:mi:ss ');
--Log program execution start time and end time
Insert into T_log (Pro_name,tj_date,begin_time,end_time,run_result) VALUES (' Sp_olap_v_buildingtype ', p_date,v_ Begintime,v_endtime, ' Y ');
Commit
--Exception handling
EXCEPTION
When OTHERS Then
ROLLBACK;
V_sqlcode: =sqlcode;
V_SQLERRM: =substr (SQLERRM, 1, 2000);
Insert into T_log (pro_name,err_time,err_code,err_infor,run_result,tj_date) VALUES (' Sp_olap_v_buildingtype ', SYSDATE,V_SQLCODE,V_SQLERRM, ' N ', p_date);
Commit
Dbms_output. Put_Line (SUBSTR (SQLERRM, 1, 200));

END Sp_olap_v_buildingtype;

------------------------------------------------a stored procedure call-----------------------------------------------

/***manager***/create OR REPLACE PROCEDURE "gzapp_09". " Pkg_stat_day "(" P_date "in DATE)
AUTHID Definer

is
/*=============================================================
* Feature description: Daily report Schedule Rollup
* Entry parameters: Statistical Time
* Export parameters: No
* Creator and time: DFB 20160906
* modifier and time:
*============================================================*/
V_sqlcode number;
V_SQLERRM varchar2 (4000);
V_begintime varchar2 (20);
V_endtime varchar2 (20);
BEGIN

V_begintime:=to_char (sysdate, ' yyyy/mm/dd hh24:mi:ss ');

Sp_olap_v_buildingtype (p_date);
V_endtime:=to_char (sysdate, ' yyyy/mm/dd hh24:mi:ss ');
--Log program execution start time and end time
Insert into T_log (Pro_name,tj_date,begin_time,end_time,run_result) VALUES (' Pkg_stat_day ', p_date,v_begintime,v_ Endtime, ' Y ');
Commit
--Exception handling
EXCEPTION
When OTHERS Then
ROLLBACK;
V_sqlcode: =sqlcode;
V_SQLERRM: =substr (SQLERRM, 1, 2000);
Insert into T_log (pro_name,err_time,err_code,err_infor,run_result,tj_date) VALUES (' Pkg_stat_day ', sysdate,v_ SQLCODE,V_SQLERRM, ' N ', p_date);
Commit
Dbms_output. Put_Line (SUBSTR (SQLERRM, 1, 200));

END Pkg_stat_day;

Dameng database stored procedures and stored procedure calls