CREATE OR REPLACE FUNCTION SP_UPDATE_LOTNO
RETURN NUMBER IS
CURSOR Cur_UpdateLotNo IS
SELECT Max(n_end_lot_no) EndLotNo,
v_seller_code,
v_buyer_code,
v_series,
v_model_code
FROM tb_ship_sched_qty_dt
GROUP BY v_seller_code, v_buyer_code, v_series, v_model_code;
BEGIN
FOR rec IN Cur_UpdateLotNo LOOP
UPDATE tb_slr_byr_srs_mdl
SET n_ending_lot_no = rec.EndLotNo
WHERE v_seller_code = rec.v_seller_code
AND v_buyer_code = rec.v_buyer_code
AND v_series = rec.v_series
AND v_model_code = rec.v_model_code;
END LOOP;
RETURN 1;
END;
RETURN NUMBER IS
CURSOR Cur_UpdateLotNo IS
SELECT Max(n_end_lot_no) EndLotNo,
v_seller_code,
v_buyer_code,
v_series,
v_model_code
FROM tb_ship_sched_qty_dt
GROUP BY v_seller_code, v_buyer_code, v_series, v_model_code;
BEGIN
FOR rec IN Cur_UpdateLotNo LOOP
UPDATE tb_slr_byr_srs_mdl
SET n_ending_lot_no = rec.EndLotNo
WHERE v_seller_code = rec.v_seller_code
AND v_buyer_code = rec.v_buyer_code
AND v_series = rec.v_series
AND v_model_code = rec.v_model_code;
END LOOP;
RETURN 1;
END;
CREATE OR REPLACE procedure data_tab_subscrb
as
long int data_number;
bool flag;
begin
data_number = 13007310001;
flag = true;
while(flag)
if (data_number = 130073101000) then
flag = false;
insert into tab_subscrb
values(
data_number,
1,
50,
10,
10,
10,
10,
8,
731,
1,
0,
0,
2003-4-8,
data_number,
2003-4-8,
2003-4-8,
2003-4-8,
"changsha",
111,
1000000011,
200,
300,
10,
10,
11,
4556456,
9231,
32132,
321,
8,
321321,
0,
2003-4-8
);
data_number = data_number + 1;
end
end
var_i number
begin
var_i := 1;
LOOP
EXIT WHEN var_i > var_num;
insert into tabname values (var_i,col_value1,...);
commit;
var_i := var_i + 1;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
end proc_sy;
/
as
data_number number;
flag boolean;
begin
data_number := 13007310001;
flag := true;
loop
exit when not flag;
if (data_number = 130073101000) then
flag := false;
end if;
insert into tab_subscrb
values(
data_number,
1,
50,
10,
10,
10,
10,
8,
731,
1,
0,
0,
2003-4-8,
data_number,
2003-4-8,
2003-4-8,
2003-4-8,
"changsha",
111,
1000000011,
200,
300,
10,
10,
11,
4556456,
9231,
32132,
321,
8,
321321,
0,
2003-4-8
);
data_number := data_number + 1;
end loop;
end data_tab_subscrb;
/
WHEN OTHERS THEN
NULL;
是什么意思?
(
v_search_id in varchar2,
v_result out varchar2,
v_errmsg out varchar2
)
is
v_step number;
v_fy number;
v_len number;
v_errcode number;
v_servnbr c_appl.serv_nbr%type;
v_servno c_appl.serv_no%type;
v_fwlbzh varchar2(100);
begin
v_result := '0';
v_step := 1;
select item_value into v_servnbr from slxxb where search_id=v_search_id and item_name='SERV_NBR';
select item_value into v_servno from slxxb where search_id=v_search_id and item_name='SERV_NO';
select item_value into v_fwlbzh from slxxb where search_id=v_search_id and item_name='FWLBZH';
--
select item_value into v_fy from slxxb where search_id=v_search_id and item_name='SSF';
insert into slfyb( serv_nbr, fylb, fy ) values( v_servnbr, '02', to_number(v_fy)/100 );
select item_value into v_fy from slxxb where search_id=v_search_id and item_name='XHF';
insert into slfyb( serv_nbr, fylb, fy ) values( v_servnbr, '05', to_number(v_fy)/100 );
select item_value into v_fy from slxxb where search_id=v_search_id and item_name='HJF';
insert into slfyb( serv_nbr, fylb, fy ) values( v_servnbr, '04', to_number(v_fy)/100 );
v_step := 2;
--
v_len:=length(ltrim(rtrim(v_fwlbzh)));
for i in 1..v_len/2 loop
insert into fwlb(serv_nbr,fwzh,service) values(v_servnbr,substr(v_fwlbzh,2*i-1,2),'1');
end loop;
v_step := 3;
exception when others then
v_errcode := SQLCODE;
v_errmsg := substr(SQLERRM,1,100);
rollback;
insert into error_log( action, id, step, error_code, error_msg )
values ( 'NEWP', v_search_id, v_step, v_errcode, v_errmsg );
commit;
v_result := '1';
v_errmsg := '调用附加过程异常,step:'||to_char(v_step)||'!';
end P_NEWPAS;
/
CREATE OR REPLACE PROCEDURE data_tab_subscrb
AS
data_number NUMBER;
flag BOOLEAN;
BEGIN
data_number := 13007310001;
flag := TRUE;
LOOP
EXIT WHEN NOT flag;
IF (data_number = 130073101000) THEN
flag := FALSE;
END IF;
INSERT INTO tab_subscrb(
SUBSCRBID, //NOT NULL NUMBER(38)
ACPTSITEID, //NOT NULL NUMBER(38)
SUBSCRBCHRTID, //NOT NULL CHAR(2)
ACTORCERTTYPEID, // CHAR(2)
WRNTID, // NUMBER(38)
SUBSCRBSTAT, // NUMBER(38)
SVCID, //NOT NULL CHAR(2)
CUSTID, //NOT NULL NUMBER(38)
AREAID, //NOT NULL CHAR(8)
PRESUBSCRBID, // NUMBER(38)
INNETMODEID, // CHAR(2)
PURCHMODEID, // CHAR(1)
RENTEXPDATE, // DATE
SVCNUM, //NOT NULL VARCHAR2(20)
UPDATETIME, //NOT NULL DATE
OPENDATE, //NOT NULL DATE
CLOSEDATE, // DATE
OPENDPT, //NOT NULL VARCHAR2(128)
PWD, // VARCHAR2(6)
CONTRCODE, // VARCHAR2(20)
UNICOMMAXCNSMPT, //NOT NULL NUMBER(38)
SUBSCRBMAXCNSMPT, // NUMBER(38)
ACPTMODEID, //NOT NULL CHAR(2)
QUERYTYPEID, //NOT NULL CHAR(2)
CDRMAILZIP, // VARCHAR2(6)
CDRMAILADDR, // VARCHAR2(128)
CDRRCVRNAME, // VARCHAR2(40)
CDREMAIL, // VARCHAR2(128)
CDRFAX, // VARCHAR2(20)
ACTORNAME, // VARCHAR2(40)
ACTORCERTNUM, // VARCHAR2(20)
USERTYPE, //NOT NULL CHAR(2)
STATDATE //NOT NULL DATE
)
VALUES(
data_number,
1,
50,
10,
10,
10,
10,
8,
731,
1,
0,
0,
2003-4-8,
data_number,
2003-4-8,
2003-4-8,
2003-4-8,
"changsha",
111,
1000000011,
200,
300,
10,
10,
11,
4556456,
9231,
32132,
321,
8,
321321,
0,
2003-4-8
);
data_number := data_number + 1;
END LOOP;
END data_tab_subscrb;
/
请问怎样向一个日期字段插入值?
CREATE OR REPLACE PROCEDURE data_tab_subscrb
AS
data_number NUMBER;
flag BOOLEAN;
BEGIN
data_number := 13007310001;
flag := TRUE;
LOOP
EXIT WHEN NOT flag;
IF (data_number = 130073101000) THEN
flag := FALSE;
END IF;
INSERT INTO tab_subscrb(
SUBSCRBID,
ACPTSITEID,
SUBSCRBCHRTID,
ACTORCERTTYPEID,
WRNTID,
SUBSCRBSTAT,
SVCID,
CUSTID,
AREAID,
PRESUBSCRBID,
INNETMODEID,
PURCHMODEID,
RENTEXPDATE,
SVCNUM,
UPDATETIME,
OPENDATE,
CLOSEDATE,
OPENDPT,
PWD,
CONTRCODE,
UNICOMMAXCNSMPT,
SUBSCRBMAXCNSMPT,
ACPTMODEID,
QUERYTYPEID,
CDRMAILZIP,
CDRMAILADDR,
CDRRCVRNAME,
CDREMAIL,
CDRFAX,
ACTORNAME,
ACTORCERTNUM,
USERTYPE,
STATDATE
)
VALUES(
data_number,
1,
"50",
"10",
10,
10,
"10",
8,
"731",
1,
"0",
"0",
today(),
tostring(data_number),
today(),
today(),
today(),
"changsha",
"111",
"1000000011",
200,
300,
"10",
"10",
"11",
"4556456",
"9231",
"32132",
"321",
"8",
"321321",
"0",
today()
);
data_number := data_number + 1;
END LOOP;
END data_tab_subscrb;
/
REM 首先将原存储过程删除!
DROP PROCEDURE del_employ_sn;REM 创建存储过程 del_employ_snCREATE or REPLACE PROCEDURE del_employ_sn
(
dm OUT varchar2,
nRC OUT number -- 返回代码(0表示成功)
)AS
BEGIN
SELECT fsdm INTO dm FROM grdm_sn for update; --取出本次代码->dm
UPDATE grdm_sn SET fsdm=lpad(fsdm-1,20,'0'); --内码递减。
nRC:=0;
commit;
EXCEPTION
WHEN OTHERS THEN
nRC:=101;
ROLLBACK;
END;
/