Number of regular Oracle pass procedures

--- Oracle debugging: setserveroutputon; shoerrorssetautotraceon; selecttextfromuser_sourcewherename

--- Oracle debugging: set serveroutput on; sho errorsset autotrace on; select text from user_source where name =

--- Oracle debugging:
Set serveroutput on;
Sho errors
Set autotrace on;
Select text from user_source where;
--- The number of linux instances that are periodically retrieved through the process:
#! /Bin/sh
# ZJ201101300004_fee.sh
# Monthly fee deduction data
#0 7 6 **/gmcc_data/zj/zjlzw/wg/shell/ZJ201101300004_fee.sh>/gmcc_data/zj/zjlzw/wg/shell/ZJ201101300004_fee.log &
Export ORACLE_BASE =/oracle
Export ORACLE_HOME =/oracle/product S/10.2/db
Export PATH = $ ORACLE_HOME/bin: $ ORACLE_HOME/Apache/bin: $ PATH
Export ORACLE_TERM = xterm
Export LD_LIBRARY_PATH =/oracle/products/10.2/db/lib: $ LD_LIBRARY_PATH
Export PATH =/oracle/products/10.2/db/bin: $ PATH
Export ORA_NLS33 = $ ORACLE_HOME/ocommon/nls/admin/data
Export NLS_LANG = AMERICAN_AMERICA.ZHS16GBK
./Etc/profile
Date
Datepath = "/gmcc_data/zj/zjlzw"
Logininfo = 'cat/gmcc_data/zj/zjlzw/login. sh | grep sqlplus'

Datetmp2 = 'date + % d'

$ Logininfo < -- Monthly fee deduction data for ZJ201101300004
Call sp_lzw_ZJ201101300004 ();
Exit;
EOF

Date
Exit

Cat login. SQL
Set newpage none heading off space 0 PAGESIZE 0 trimout on trimspool on linesize 2500 colsep | feedback off termout off pages 0
Set colsep |
Alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';
Set feedback on

Select text from user_source where;
Create or replace PROCEDURE sp_lzw_ZJ201101300004

/** HEAD
* @ Name sp_lzw_ZJ201101300004
* @ Caption: process the table
* @ Type daily processing
* @ Parameter iv_month in varchar2 statistical date. Format: YYYYMMDD
* @ Parameter oi_return out integer Execution status code, 0 normal, other errors
* @ Description process the table
* @ Middle <无>
* @ Version <版本号：1.0>
* @ Author
* @ Create-date <2011-3-23 9:08>
* @ TODO <工作计划>
* @ Version: <1.0>
* @ Mender <修改人>
* @ Modify_date
* @ Modify_desc <修改原因>
* @ Copyright TDS

*/

Is

Vi_task_id integer; -- Task log ID
Vv_task_name varchar2 (30); -- Task Name
Vv_table_name varchar2 (30); -- table name
Vv_task_pos varchar2 (50); -- Task location
Vv_return varchar2 (255); -- Record the return value of the process
Vv_err_msg varchar2 (200); -- error message
Vi_err_code integer; -- error code
Vi_result integer; -- temporary result

Vd_date date; -- Statistical date of the date type,

Vd_now_month date; -- 1 day of the month of vd_date
Vd_pre_month date; -- number 1 of the previous month of vd_date

Vv_date1 varchar2 (10); -- date converted to a character: yyyymm
Vv_date2 varchar2 (10); -- date converted to a character: yyyymm

Vv_date_now varchar2 (10); -- date converted to a character: yyyymm
Vv_date_pre varchar2 (10); -- date converted to a character: yyyymm

Vv_date_now4 varchar2 (10); -- date converted to a character: yymm
Vv_date_pre4 varchar2 (10); -- date converted to character: yymm

Exc_return exception; -- a custom exception is returned in the middle of the program.
Exc_error exception; -- a custom exception is returned when a program error occurs.

Vv_datacnt integer; -- Temporary Variable
FileName varchar2 (128); -- temporary file name

BEGIN

Execute immediate 'alter session enable parallel dml ';

/**
* @ Description variable Initialization
* @ Field-mapping vv_task_name = (' <不带用户名的程序名> ')
* @ Field-mapping vv_table_name = (' <不带用户名的表名> ')
*/

Vd_date: = sysdate; -- Obtain the current month

Vd_now_month: = trunc (sysdate, 'mm'); -- January 1, 1st day of this month
Vd_pre_month: = add_months (trunc (vd_date, 'mm'),-1); -- 1st day of last month

Vv_date_pre: = to_char (vd_pre_month, 'yyyymm'); -- last month yyyymm
Vv_date_now: = to_char (vd_now_month, 'yyyymm'); -- yyyymm of the month

Vv_date_pre4: = to_char (vd_pre_month, 'yymm'); -- yymm, last month
Vv_date_now4: = to_char (vd_now_month, 'yymm'); -- yymm of the month

-- Vv_task_name: = '';
-- Vv_table_name: = '';

Execute immediate 'select count (1) from user_tables where table_name = upper (''' | 'tmp _ lzw_ZJ201101300004 _ '| vv_date_pre | ''')' into vv_datacnt;
If (vv_datacnt> 0) then
Execute immediate 'drop table tmp_lzw_ZJ201101300004 _ '| vv_date_pre | 'purge ';
End if;

--- Mobile phone fee deduction
Execute immediate'
Create table tmp_lzw_ZJ201101300004 _ '| vv_date_pre | 'nologging
Select/* + parallel (a, 16 )*/
Subno
, Out_route
, In_route
, CALL_Date
, CALL_time
, Case when ROLLBACK_FLAG = '0' then AFTER_MOB_FEE else 0-after_mob_end end AFTER_MOB_FEE_0
, Case when ROLLBACK_FLAG = '0' then after_toll_else else 0-after_toll_else end AFTER_TOLL_FEE_0
, Case when ROLLBACK_FLAG = '0' then after_inf_rjelse 0-after_inf_1_end AFTER_INF_FEE_0,
Msrn
From DGDM_DW.TB_DW_LS_CDR_data_day
Where a. CALL_Date> = ''' | vv_date_pre4 | '01''
And a. CALL_Date <''' | vv_date_now4 | '01''
And a. msrn in
(
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''-TTKX1 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''1100 '',
''000000''
)
And deal_date> = to_date (''' | vv_date_pre | '01', ''yyymmdd '')
And deal_date <= to_date (''' | vv_date_now | '09'', ''yyymmdd '')
'
;

/*
Set newpage none heading off space 0 PAGESIZE 0 trimout on trimspool on linesize 2500 colsep | feedback off termout off pages 0
Set colsep |
Alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';
Set feedback on
*/

/* Export
Spool tmp_lzw_ZJ201101300004_201102.txt
Select
SUBNO | '|
Round (sum (nvl (AFTER_MOB_FEE_0, 0) + nvl (AFTER_TOLL_FEE_0, 0) + nvl (AFTER_INF_FEE_0, 0), 2)/100 | '|
MSRN |'
From tmp_lzw_ZJ201101300004_201102
Group by SUBNO, MSRN;
Spool off;
*/

,