如题！急等高手进！！SQL> declare
2 v_serv_id ldy_phs_01.serv_id%type;
3 v_name_d price.name@bill%type;
4 v_exp_date_t ldy_phs_01.exp_date%type ;
5 v_exp_date_d ods.serv_price.exp_date@bill%type;
6 v_name_t ldy_phs_01.price_name%type ;
7 cursor ldy_c is
8 select serv_id
9 from ldy_phs_01;
10
11 cursor ldys_c(v_s ldy_phs_01.serv_id%type) is
12 select a.name,b.exp_date
13 from ods.serv_price@bill b,price@bill a
14 where b.serv_id=v_s and b.price_id=a.price_plan_id;
15 begin
16
17 open ldy_c;
18
19 loop
20 fetch ldy_c into v_serv_id;
21 exit when not ldy_c%found;
22
23 open ldys_c(v_serv_id);
24 loop
25 fetch ldys_c into v_name_d,v_exp_date_d;
26 exit when not ldys_c%found;
27 v_name_t := v_name_t||v_name_d;
28 v_exp_date_t := v_exp_date_t||to_char(v_exp_date_d,'yymm');
29
30 end loop;
31 close ldys_c;
32 update ldy_phs_01 set exp_date=v_exp_date_t, price_name=v_name_t
33 where serv_id=v_serv_id;
34 end loop;
35 close ldy_c;
36
37 end;
38 /
declare
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: 数字或值错误
ORA-06512: 在line 27
SQL>
2 v_serv_id ldy_phs_01.serv_id%type;
3 v_name_d price.name@bill%type;
4 v_exp_date_t ldy_phs_01.exp_date%type ;
5 v_exp_date_d ods.serv_price.exp_date@bill%type;
6 v_name_t ldy_phs_01.price_name%type ;
7 cursor ldy_c is
8 select serv_id
9 from ldy_phs_01;
10
11 cursor ldys_c(v_s ldy_phs_01.serv_id%type) is
12 select a.name,b.exp_date
13 from ods.serv_price@bill b,price@bill a
14 where b.serv_id=v_s and b.price_id=a.price_plan_id;
15 begin
16
17 open ldy_c;
18
19 loop
20 fetch ldy_c into v_serv_id;
21 exit when not ldy_c%found;
22
23 open ldys_c(v_serv_id);
24 loop
25 fetch ldys_c into v_name_d,v_exp_date_d;
26 exit when not ldys_c%found;
27 v_name_t := v_name_t||v_name_d;
28 v_exp_date_t := v_exp_date_t||to_char(v_exp_date_d,'yymm');
29
30 end loop;
31 close ldys_c;
32 update ldy_phs_01 set exp_date=v_exp_date_t, price_name=v_name_t
33 where serv_id=v_serv_id;
34 end loop;
35 close ldy_c;
36
37 end;
38 /
declare
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: 数字或值错误
ORA-06512: 在line 27
SQL>
SQL> desc ldy_phs_01
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
REGION_ID_ACCT NUMBER(10)
SERV_ID NUMBER(12)
ACCT_ID NUMBER(12)
SERV_TYPE_ID NUMBER(9)
ACC_NBR VARCHAR2(16)
CHARGE_01 NUMBER
CHARGE_TOTAL01 NUMBER
CHARGE_02 NUMBER
CHARGE_TOTAL02 NUMBER
NAME VARCHAR2(200)
CONTACT_NAME VARCHAR2(100)
CONTACT_ACC_NBR VARCHAR2(100)
PRICE_NAME VARCHAR2(300)
EXP_DATE VARCHAR2(300)
SQL> desc ods.serv_price@bill
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
CITY_ID NUMBER(6)
SERV_ID NUMBER(12)
TAB_NBR NUMBER(18)
ACC_NBR_SEQ NUMBER(5)
ACC_NBR VARCHAR2(19)
SO_DATE DATE
COMPL_DATE DATE
PRICE_ID NUMBER(6)
EFF_DATE DATE
EXP_DATE DATE
SO_NBR VARCHAR2(38)
ACT_TYPE CHAR(1)
PARAM_VALUE VARCHAR2(30)
STS_DATE DATE
PROD_ID NUMBER(6)
START_DATE DATE
SQL> desc price@bill
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
PRICE_PLAN_ID NOT NULL NUMBER(6)
NAME NOT NULL VARCHAR2(120)
TYPE NOT NULL CHAR(2)
CHARGE_FLAG NOT NULL CHAR(2)
GRADE NOT NULL CHAR(2)
LOCAL_NET_ID NOT NULL NUMBER(6)
AREA_ID NOT NULL NUMBER(6)
EFF_DATE NOT NULL DATE
EXP_DATE DATE
STS NOT NULL CHAR(2)
STS_DATE NOT NULL DATE
CREATE_DATE NOT NULL DATE
REMARKS VARCHAR2(2048)
UNIT CHAR(2)
EFF_PERIOD NUMBER(6)
WM_FLAG CHAR(2)
TAG_PRICE_ID NUMBER(6)
END_DATE DATE