--插商品参数表 for row_data in par_name_cur loop v_sql := 'insert into tb_ttp_goods_parameters(ind_id,good_id,par_sn,par_alias,par_value) values(158,:a,:b,:c,:d)'; dbms_output.put_line(v_sql); execute immediate v_sql using v_seq_good_id, row_data.par_sn, row_data.par_alias,----------row_data_bas.row_data.bas_column---------问题在这里;
end loop; end loop;
for row_data_bas in cur_bas loop --将seq_ttp_goods.nextval赋给v_seq_good_id select seq_ttp_goods.nextval into v_seq_good_id from dual;
--插商品表 v_sql1 := 'insert into tb_ttp_goods(id,tele_code,good_name,good_type,good_industry,good_band,good_state,price,sn,createdate,et_date) values(:8,:1,:2,:3,158,:4,''1'',:5,seq_ttp_goods_sn.nextval,:6,to_date(:7,''yyyy-mm-dd''))'; dbms_output.put_line(v_sql1); execute immediate v_sql1 using v_seq_good_id, row_data_bas.tele_code, row_data_bas.pro_name, row_data_bas.pro_model, row_data_bas.pro_brand_id, row_data_bas.et_price, row_data_bas.create_date, row_data_bas.et_time;
--插商品参数表 for row_data in par_name_cur loop v_sql := 'insert into tb_ttp_goods_parameters(ind_id,good_id,par_sn,par_alias,par_value) values(158,:a,:b,:c,:d)'; dbms_output.put_line(v_sql); execute immediate v_sql using v_seq_good_id, row_data.par_sn, row_data.par_alias,----------row_data_bas.row_data.bas_column---------问题在这里;
------------ -- EXAMPLES -- -- create or replace procedure copy(source in varchar2, -- destination in varchar2) is -- -- This procedure copies rows from a given source table to -- a given destination table assuming that both source and destination -- -- tables have the following columns: -- -- - ID of type NUMBER, -- -- - NAME of type VARCHAR2(30), -- -- - BIRTHDATE of type DATE. -- id number; -- name varchar2(30); -- birthdate date; -- source_cursor integer; -- destination_cursor integer; -- rows_processed integer; -- begin -- -- prepare a cursor to select from the source table -- source_cursor := dbms_sql.open_cursor; -- dbms_sql.parse(source_cursor, -- 'select id, name, birthdate from ' || source); -- dbms_sql.define_column(source_cursor, 1, id); -- dbms_sql.define_column(source_cursor, 2, name, 30); -- dbms_sql.define_column(source_cursor, 3, birthdate); -- rows_processed := dbms_sql.execute(source_cursor); -- -- -- prepare a cursor to insert into the destination table -- destination_cursor := dbms_sql.open_cursor; -- dbms_sql.parse(destination_cursor, -- 'insert into ' || destination || -- ' values (:id, :name, :birthdate)'); -- -- -- fetch a row from the source table and -- -- insert it into the destination table -- loop -- if dbms_sql.fetch_rows(source_cursor)>0 then -- -- get column values of the row -- dbms_sql.column_value(source_cursor, 1, id); -- dbms_sql.column_value(source_cursor, 2, name); -- dbms_sql.column_value(source_cursor, 3, birthdate); -- -- bind the row into the cursor which insert -- -- into the destination table -- dbms_sql.bind_variable(destination_cursor, 'id', id); -- dbms_sql.bind_variable(destination_cursor, 'name', name); -- dbms_sql.bind_variable(destination_cursor, 'birthdate', birthdate); -- rows_processed := dbms_sql.execute(destination_cursor); -- else -- -- no more row to copy -- exit; -- end if; -- end loop; -- -- -- commit and close all cursors -- commit; -- dbms_sql.close_cursor(source_cursor); -- dbms_sql.close_cursor(destination_cursor); -- exception -- when others then -- if dbms_sql.is_open(source_cursor) then -- dbms_sql.close_cursor(source_cursor); -- end if; -- if dbms_sql.is_open(destination_cursor) then -- dbms_sql.close_cursor(destination_cursor); -- end if; -- raise; -- end; --
附上我的代码。CREATE OR REPLACE PROCEDURE procedure_goods is v_sql varchar2(2000); v_sql1 varchar2(2000); v_sql2 varchar2(2000); v_sql3 varchar2(2000); v_sql4 varchar2(2000); v_sql5 varchar2(2000); v_par_value varchar2(2000); v_band_id number; --定义接收序列值得变量,插商品参数表需要 v_seq_good_id number; --用于大横表插纵表游标 cursor par_name_cur is select par_alias, par_sn, bas_column from tb_ttp_industry_parameters; row_data par_name_cur%rowtype; --设置待插商品表和商品参数表的游标 cursor cur_bas is select * from (select t.*, row_number() over(partition by t.tele_code order by t.create_date desc) rn from (select a.tele_code, a.pro_name, a.pro_model, a.pro_brand_id, a.et_price, a.create_date, a.et_time, a.pro_brand_name from bas_dev_info_group a where a.tele_code not in (select tele_code from tb_ttp_goods) and a.tele_code in (Select BAS_DEV_INFO.TERM_MODEL From BAS_DEV_INFO where term_model is not null)) t) where rn = 1; row_data_bas cur_bas%rowtype; --设置待更新商品参数表数据游标 cursor cur_bas_update is select * from (select t.*, row_number() over(partition by t.tele_code order by t.create_date desc) rn from (select a.tele_code, a.create_date, b.id from bas_dev_info_group a, tb_ttp_goods b where a.tele_code = b.tele_code and a.create_date > b.createdate) t) where rn = 1; row_data_bas_update cur_bas_update%rowtype; --设置待掺入品牌表的游标 cursor cur_bas_band_add is select distinct pro_brand_name, pro_brand_id from bas_dev_info_group k where k.pro_brand_id not in (select s.pro_brand_id from TB_TTP_BANDS s); row_data_band cur_bas_band_add%rowtype;begin --初始化v_seq_good_id v_seq_good_id := 0; --插入新增品牌 for row_data_band in cur_bas_band_add loop v_sql2 := 'insert into tb_ttp_bands(id,band_name,band_sn,pro_brand_id,pro_brand_name) values(seq_ttp_band.nextval,:x,seq_ttp_band_sn.nextval,:y,:z)'; execute immediate v_sql2 using row_data_band.pro_brand_name, row_data_band.pro_brand_id, row_data_band.pro_brand_name; /* dbms_output.put_line(v_sql2);*/ end loop; --删除商品参数表中需要更新的记录 for row_data_bas_update in cur_bas_update loop v_sql3 := 'delete from tb_ttp_goods_parameters where good_id = :v'; execute immediate v_sql3 using row_data_bas_update.id; /* dbms_output.put_line(v_sql3);*/ end loop; --重新插入商品参数表更新后的记录 for row_data_bas_update in cur_bas_update loop for row_data in par_name_cur loop v_sql4 := 'insert into tb_ttp_goods_parameters(ind_id,good_id,par_sn,par_alias,par_value) select 158,:a,:b,:c,' || row_data.bas_column || ' from bas_dev_info_group'; execute immediate v_sql4 using row_data_bas_update.id, row_data.par_sn, row_data.par_alias; /*dbms_output.put_line(v_sql4);*/ end loop; end loop; for row_data_bas in cur_bas loop --将seq_ttp_goods.nextval赋给v_seq_good_id select seq_ttp_goods.nextval into v_seq_good_id from dual; select id into v_band_id from tb_ttp_bands where band_name = row_data_bas.pro_brand_name; --插商品表 v_sql1 := 'insert into tb_ttp_goods(id,tele_code,good_name,good_type,good_industry,good_band,good_state,price,sn,createdate,et_date) values(:8,:1,:2,:3,158,:4,''1'',:5,seq_ttp_goods_sn.nextval,:6,to_date(:7,''yyyy-mm-dd''))'; /*dbms_output.put_line(v_sql1);*/ execute immediate v_sql1 using v_seq_good_id, row_data_bas.tele_code, row_data_bas.pro_name, row_data_bas.pro_model, v_band_id, row_data_bas.et_price, row_data_bas.create_date, row_data_bas.et_time;
--插商品参数表 for row_data in par_name_cur loop
v_sql := 'insert into tb_ttp_goods_parameters(ind_id,good_id,par_sn,par_alias,par_value) values(158,:a,:b,:c,:d)'; /* dbms_output.put_line(v_sql);*/ execute immediate v_sql using v_seq_good_id, row_data.par_sn, row_data.par_alias,----------row_data_bas.row_data.bas_column---------问题在这里; ;
end loop;
end loop;end; /
declare sql_id integer; v_sql varchar2(4000); begin v_sql := 'insert into tb_ttp_goods_parameters(ind_id,good_id,par_sn,par_alias,par_value) values(158,:a,:b,:c,:d)';
dbms_sql.execute(sql_id); --总之 dbms_sql.bind_variable 就相当于using。不过using 后面的个数是固定的,这个每次绑定一个,可以循环,所以参数个数可以不固定了;using后面的参数名必须写死,但是这个你可以先把后面的值取到别的可以命名的变量中,再赋值进去(比如先用execute immediate 'select row_data.*** into a from ....' 再把A给它。) end;
for row_data in par_name_cur loop dbms_sql.bind_variable(destination_cursor, 'a',v_seq_good_id ); dbms_sql.bind_variable(destination_cursor, 'b',row_data.par_sn); dbms_sql.bind_variable(destination_cursor, 'c',row_data.par_alias); dbms_sql.bind_variable(destination_cursor, 'd',row_data_bas.row_data.bas_column); end loop; 我要取 row_data_bas 这个游标里字段名为 row_data.bas_column 的数据还是不好取呀可能是我 没还没理解你的意思,能再给解释解释怎么做么,只有你能帮我了。。
我基本明白了,上面理解反了,把上面的忘了吧,你暂时用不到。 declare a anytype; b anytype; c anytype; d anytype; --这四个类型改成 par_sn, par_alias ....的类型 v_cur_sql varchar2(4000); v_refcur sys_refcursor; begin v_cur_sql := 'select par_sn,par_alias .... from ..... where .....'; open v_refcur for v_cur_sql; loop fetch v_refcur into a,b,c....; exit when v_refcur%notfound; insert into tb_ttp_goods_parameters(a,b,c....); end loop; end;
--插商品参数表
for row_data in par_name_cur loop
v_sql := 'insert into tb_ttp_goods_parameters(ind_id,good_id,par_sn,par_alias,par_value)
values(158,:a,:b,:c,:d)';
dbms_output.put_line(v_sql);
execute immediate v_sql
using v_seq_good_id, row_data.par_sn, row_data.par_alias,----------row_data_bas.row_data.bas_column---------问题在这里;
end loop;
end loop;
for row_data_bas in cur_bas loop
--将seq_ttp_goods.nextval赋给v_seq_good_id
select seq_ttp_goods.nextval into v_seq_good_id from dual;
--插商品表
v_sql1 := 'insert into tb_ttp_goods(id,tele_code,good_name,good_type,good_industry,good_band,good_state,price,sn,createdate,et_date)
values(:8,:1,:2,:3,158,:4,''1'',:5,seq_ttp_goods_sn.nextval,:6,to_date(:7,''yyyy-mm-dd''))';
dbms_output.put_line(v_sql1);
execute immediate v_sql1
using v_seq_good_id, row_data_bas.tele_code, row_data_bas.pro_name, row_data_bas.pro_model, row_data_bas.pro_brand_id, row_data_bas.et_price, row_data_bas.create_date, row_data_bas.et_time;
--插商品参数表
for row_data in par_name_cur loop
v_sql := 'insert into tb_ttp_goods_parameters(ind_id,good_id,par_sn,par_alias,par_value)
values(158,:a,:b,:c,:d)';
dbms_output.put_line(v_sql);
execute immediate v_sql
using v_seq_good_id, row_data.par_sn, row_data.par_alias,----------row_data_bas.row_data.bas_column---------问题在这里;
end loop;
end loop;
for row_data in par_name_cur loop
v_sql := 'insert into tb_ttp_goods_parameters(ind_id,good_id,par_sn,par_alias,par_value)
values(158,:a,:b,:c,:d)';
dbms_output.put_line(v_sql);
execute immediate v_sql
using v_seq_good_id, row_data.par_sn, row_data.par_alias,----------row_data_bas.row_data.bas_column---------问题在这里;给d变量赋值的是 row_data的某个字段吗,还是什么,能详细点吗?
-- | open_cursor |
-- -----------
-- |
-- |
-- v
-- -----
-- ------------>| parse |
-- | -----
-- | |
-- | |---------
-- | v |
-- | -------------- |
-- |-------->| bind_variable | |
-- | ^ ------------- |
-- | | | |
-- | -----------| |
-- | |<--------
-- | v
-- | query?---------- yes ---------
-- | | |
-- | no |
-- | | |
-- | v v
-- | ------- -------------
-- |----------->| execute | ->| define_column |
-- | ------- | -------------
-- | |------------ | |
-- | | | ----------|
-- | v | v
-- | -------------- | -------
-- | ->| variable_value | | ------>| execute |
-- | | -------------- | | -------
-- | | | | | |
-- | ----------| | | |
-- | | | | v
-- | | | | ----------
-- | |<----------- |----->| fetch_rows |
-- | | | ----------
-- | | | |
-- | | | v
-- | | | --------------------
-- | | | | column_value |
-- | | | | variable_value |
-- | | | ---------------------
-- | | | |
-- | |<--------------------------
-- | |
-- -----------------|
-- |
-- v
-- ------------
-- | close_cursor |
-- ------------
--
---------------
-- EXAMPLES
--
-- create or replace procedure copy(source in varchar2,
-- destination in varchar2) is
-- -- This procedure copies rows from a given source table to
-- a given destination table assuming that both source and destination
-- -- tables have the following columns:
-- -- - ID of type NUMBER,
-- -- - NAME of type VARCHAR2(30),
-- -- - BIRTHDATE of type DATE.
-- id number;
-- name varchar2(30);
-- birthdate date;
-- source_cursor integer;
-- destination_cursor integer;
-- rows_processed integer;
-- begin
-- -- prepare a cursor to select from the source table
-- source_cursor := dbms_sql.open_cursor;
-- dbms_sql.parse(source_cursor,
-- 'select id, name, birthdate from ' || source);
-- dbms_sql.define_column(source_cursor, 1, id);
-- dbms_sql.define_column(source_cursor, 2, name, 30);
-- dbms_sql.define_column(source_cursor, 3, birthdate);
-- rows_processed := dbms_sql.execute(source_cursor);
--
-- -- prepare a cursor to insert into the destination table
-- destination_cursor := dbms_sql.open_cursor;
-- dbms_sql.parse(destination_cursor,
-- 'insert into ' || destination ||
-- ' values (:id, :name, :birthdate)');
--
-- -- fetch a row from the source table and
-- -- insert it into the destination table
-- loop
-- if dbms_sql.fetch_rows(source_cursor)>0 then
-- -- get column values of the row
-- dbms_sql.column_value(source_cursor, 1, id);
-- dbms_sql.column_value(source_cursor, 2, name);
-- dbms_sql.column_value(source_cursor, 3, birthdate);
-- -- bind the row into the cursor which insert
-- -- into the destination table
-- dbms_sql.bind_variable(destination_cursor, 'id', id);
-- dbms_sql.bind_variable(destination_cursor, 'name', name);
-- dbms_sql.bind_variable(destination_cursor, 'birthdate', birthdate);
-- rows_processed := dbms_sql.execute(destination_cursor);
-- else
-- -- no more row to copy
-- exit;
-- end if;
-- end loop;
--
-- -- commit and close all cursors
-- commit;
-- dbms_sql.close_cursor(source_cursor);
-- dbms_sql.close_cursor(destination_cursor);
-- exception
-- when others then
-- if dbms_sql.is_open(source_cursor) then
-- dbms_sql.close_cursor(source_cursor);
-- end if;
-- if dbms_sql.is_open(destination_cursor) then
-- dbms_sql.close_cursor(destination_cursor);
-- end if;
-- raise;
-- end;
--
v_sql varchar2(2000);
v_sql1 varchar2(2000);
v_sql2 varchar2(2000);
v_sql3 varchar2(2000);
v_sql4 varchar2(2000);
v_sql5 varchar2(2000); v_par_value varchar2(2000);
v_band_id number; --定义接收序列值得变量,插商品参数表需要
v_seq_good_id number; --用于大横表插纵表游标
cursor par_name_cur is
select par_alias, par_sn, bas_column from tb_ttp_industry_parameters;
row_data par_name_cur%rowtype; --设置待插商品表和商品参数表的游标
cursor cur_bas is
select *
from (select t.*,
row_number() over(partition by t.tele_code order by t.create_date desc) rn
from (select a.tele_code,
a.pro_name,
a.pro_model,
a.pro_brand_id,
a.et_price,
a.create_date,
a.et_time,
a.pro_brand_name
from bas_dev_info_group a
where a.tele_code not in
(select tele_code from tb_ttp_goods)
and a.tele_code in
(Select BAS_DEV_INFO.TERM_MODEL
From BAS_DEV_INFO
where term_model is not null)) t)
where rn = 1;
row_data_bas cur_bas%rowtype; --设置待更新商品参数表数据游标
cursor cur_bas_update is
select *
from (select t.*,
row_number() over(partition by t.tele_code order by t.create_date desc) rn
from (select a.tele_code, a.create_date, b.id
from bas_dev_info_group a, tb_ttp_goods b
where a.tele_code = b.tele_code
and a.create_date > b.createdate) t)
where rn = 1;
row_data_bas_update cur_bas_update%rowtype; --设置待掺入品牌表的游标
cursor cur_bas_band_add is
select distinct pro_brand_name, pro_brand_id
from bas_dev_info_group k
where k.pro_brand_id not in
(select s.pro_brand_id from TB_TTP_BANDS s);
row_data_band cur_bas_band_add%rowtype;begin
--初始化v_seq_good_id
v_seq_good_id := 0; --插入新增品牌
for row_data_band in cur_bas_band_add loop
v_sql2 := 'insert into tb_ttp_bands(id,band_name,band_sn,pro_brand_id,pro_brand_name)
values(seq_ttp_band.nextval,:x,seq_ttp_band_sn.nextval,:y,:z)';
execute immediate v_sql2
using row_data_band.pro_brand_name, row_data_band.pro_brand_id, row_data_band.pro_brand_name;
/* dbms_output.put_line(v_sql2);*/
end loop; --删除商品参数表中需要更新的记录
for row_data_bas_update in cur_bas_update loop
v_sql3 := 'delete from tb_ttp_goods_parameters where good_id = :v';
execute immediate v_sql3
using row_data_bas_update.id;
/* dbms_output.put_line(v_sql3);*/
end loop; --重新插入商品参数表更新后的记录
for row_data_bas_update in cur_bas_update loop
for row_data in par_name_cur loop
v_sql4 := 'insert into tb_ttp_goods_parameters(ind_id,good_id,par_sn,par_alias,par_value)
select 158,:a,:b,:c,' ||
row_data.bas_column || ' from bas_dev_info_group';
execute immediate v_sql4
using row_data_bas_update.id, row_data.par_sn, row_data.par_alias;
/*dbms_output.put_line(v_sql4);*/
end loop;
end loop; for row_data_bas in cur_bas loop
--将seq_ttp_goods.nextval赋给v_seq_good_id
select seq_ttp_goods.nextval into v_seq_good_id from dual;
select id
into v_band_id
from tb_ttp_bands
where band_name = row_data_bas.pro_brand_name;
--插商品表
v_sql1 := 'insert into tb_ttp_goods(id,tele_code,good_name,good_type,good_industry,good_band,good_state,price,sn,createdate,et_date)
values(:8,:1,:2,:3,158,:4,''1'',:5,seq_ttp_goods_sn.nextval,:6,to_date(:7,''yyyy-mm-dd''))';
/*dbms_output.put_line(v_sql1);*/
execute immediate v_sql1
using v_seq_good_id, row_data_bas.tele_code, row_data_bas.pro_name, row_data_bas.pro_model, v_band_id, row_data_bas.et_price, row_data_bas.create_date, row_data_bas.et_time;
--插商品参数表
for row_data in par_name_cur loop
v_sql := 'insert into tb_ttp_goods_parameters(ind_id,good_id,par_sn,par_alias,par_value)
values(158,:a,:b,:c,:d)';
/* dbms_output.put_line(v_sql);*/
execute immediate v_sql
using v_seq_good_id, row_data.par_sn, row_data.par_alias,----------row_data_bas.row_data.bas_column---------问题在这里; ;
end loop;
end loop;end;
/
sql_id integer;
v_sql varchar2(4000);
begin
v_sql := 'insert into tb_ttp_goods_parameters(ind_id,good_id,par_sn,par_alias,par_value)
values(158,:a,:b,:c,:d)';
sql_id := dbms_sql.open_cursor;
dbms_sql.parse(sql_id, v_sql); --用v_sql声明一个动态游标,把游标和sql_id绑定 --一个循环,去取 row_data.***的值
dbms_sql.bind_variable(destination_cursor, 'a', <给a的值>);
dbms_sql.bind_variable(destination_cursor, 'b', <给b的值>);
-- ....这里不管怎么样,你总能循环的把你需要的参数的值取出来吧,就是 row_data.par_sn ...的值
--循环结束
dbms_sql.execute(sql_id); --总之 dbms_sql.bind_variable 就相当于using。不过using 后面的个数是固定的,这个每次绑定一个,可以循环,所以参数个数可以不固定了;using后面的参数名必须写死,但是这个你可以先把后面的值取到别的可以命名的变量中,再赋值进去(比如先用execute immediate 'select row_data.*** into a from ....' 再把A给它。)
end;
sql_id := dbms_sql.open_cursor;
dbms_sql.parse(sql_id, v_sql);
for row_data in par_name_cur loop
dbms_sql.bind_variable(destination_cursor, 'a',v_seq_good_id );
dbms_sql.bind_variable(destination_cursor, 'b',row_data.par_sn);
dbms_sql.bind_variable(destination_cursor, 'c',row_data.par_alias);
dbms_sql.bind_variable(destination_cursor, 'd',row_data_bas.row_data.bas_column);
end loop;
我要取 row_data_bas 这个游标里字段名为 row_data.bas_column 的数据还是不好取呀可能是我
没还没理解你的意思,能再给解释解释怎么做么,只有你能帮我了。。
我基本明白了,上面理解反了,把上面的忘了吧,你暂时用不到。
declare
a anytype;
b anytype;
c anytype;
d anytype; --这四个类型改成 par_sn, par_alias ....的类型
v_cur_sql varchar2(4000);
v_refcur sys_refcursor;
begin
v_cur_sql := 'select par_sn,par_alias .... from ..... where .....';
open v_refcur for v_cur_sql;
loop
fetch v_refcur into a,b,c....;
exit when v_refcur%notfound;
insert into tb_ttp_goods_parameters(a,b,c....);
end loop;
end;
比如说 定义两个游标a和b, 三个数据表分别 T1 T2 T3 ,
现在a接收 T1 表的几行数据,每行数据又若干字段
b接收 T2 表的几行数据,每行数据又若干字段现在我在循环a里嵌套循环b, 取a里的数据可以通过 a.字段名取到该字段所对应的值, 现在问题来了,我要取b里的数据,也可以通过b.字段名来取,但现在我要通过 b.字段名 所对应的值作为a的字段来取a里面的数据(也就是类似于这种形式,a.b.b的字段名来取,但这种写法是不允许的,所以寻求解决办法) 可以看我上面给出的代码给出方案,拜托各位了。。