--tableName 表名 --Fields 字段字符串,如:col1,col2,... --Values 值字符串,如:val1,val2,... create or replace InsertRecord(tableName string,Fields string ,Values string ) as ... v_sql string(1000); begin --解析Fields,到col表里面得到其類型,然后把對應的Values由字符轉換為其字段類型 --拼接成insert 語句字符串,存入v_sql ,然后動態執行之。 end InsertRecord; /
给你一个相关例子,可以参考一下 create or replace function f1(p_table_name varchar2, p_field_name varchar2 ) return number is acc_bal number; sql_string varchar2(1000); begin sql_string := 'select count('||p_field_name||') from '||p_table_name; execute immediate sql_string into acc_bal; return (acc_bal); end f1;
同意以上使用存储过程传参数并且使用动态sql的方法, 我的补充:参数只需要表名,至于字段名、字段类型都可以从表USER_TAB_COLUMNS中获得 select * from user_tab_columns where table_name='表名';
The sample for you:PROCEDURE Get_list(p_table in varchar2, outCursor IN OUT TCursor) as CC TCursor; v_sqlstatement varchar2(500); v_check number:=0; begin
select count(table_name) into v_check from user_tables where table_name=nls_upper(p_table); if v_check>0 then --if p_table='code_bu' then -- v_sqlstatement := ' select nls_upper(CODE_NAME) '||'||'||' CNAME AS CTEXT,CID,CNAME from '|| p_table ||' order by to_number(arrangement)'; -- else v_sqlstatement := ' select nls_upper(CODE_NAME) '||'||'||'''_'''||'||'||' CNAME AS CTEXT,CID,code_name,CNAME from '|| p_table ||' order by to_number(arrangement),CTEXT'; -- end if; open CC for v_sqlstatement; commit; outCursor := CC;
end if; end;
这本来就应该是动态SQL解决的问题啊
create procedure get(p_table in varchar2,p_value in varchar2) as str varchar2(100); begin str:='insert into '||p_table||' (col_name) values ('||p_value||')'; execute immediate str; end; /
create procedure get(p_table in varchar2,p_value in varchar2) as str varchar2(100); begin str:='insert into '||p_table||' (col_name) values ('||p_value||')'; execute immediate str; end; /
--Fields 字段字符串,如:col1,col2,...
--Values 值字符串,如:val1,val2,...
create or replace InsertRecord(tableName string,Fields string ,Values string )
as
...
v_sql string(1000);
begin
--解析Fields,到col表里面得到其類型,然后把對應的Values由字符轉換為其字段類型
--拼接成insert 語句字符串,存入v_sql ,然后動態執行之。
end InsertRecord;
/
create or replace function f1(p_table_name varchar2,
p_field_name varchar2
)
return number
is
acc_bal number;
sql_string varchar2(1000);
begin
sql_string := 'select count('||p_field_name||') from '||p_table_name;
execute immediate sql_string into acc_bal;
return (acc_bal);
end f1;
我的补充:参数只需要表名,至于字段名、字段类型都可以从表USER_TAB_COLUMNS中获得
select * from user_tab_columns where table_name='表名';
CC TCursor;
v_sqlstatement varchar2(500);
v_check number:=0;
begin
select count(table_name) into v_check from user_tables where table_name=nls_upper(p_table);
if v_check>0 then
--if p_table='code_bu' then
-- v_sqlstatement := ' select nls_upper(CODE_NAME) '||'||'||' CNAME AS CTEXT,CID,CNAME from '|| p_table ||' order by to_number(arrangement)';
-- else
v_sqlstatement := ' select nls_upper(CODE_NAME) '||'||'||'''_'''||'||'||' CNAME AS CTEXT,CID,code_name,CNAME from '|| p_table ||' order by to_number(arrangement),CTEXT';
-- end if;
open CC for v_sqlstatement;
commit;
outCursor := CC;
end if;
end;
as
str varchar2(100);
begin
str:='insert into '||p_table||' (col_name) values ('||p_value||')';
execute immediate str;
end;
/
as
str varchar2(100);
begin
str:='insert into '||p_table||' (col_name) values ('||p_value||')';
execute immediate str;
end;
/