通过将表名作为参数使用oracle中的存储过程从表中搜索数据 [英] Searching data from a table using stored procedure in oracle by passing tablename as a parameter

问题描述

此过程无法正常工作.

create or replace procedure bank_search_sp
(
p_tablename in varchar2,
p_searchname in varchar2,
p_bankcode out varchar2,
p_bankname out varchar2,
p_dist_code out number
)
as
v_tem varchar2(5000);
begin
v_tem :='select bankcode,bankname,dist_code from ' || UPPER (p_tablename) || '
where bankname like '''|| p_searchname||'';
execute immediate v_tem into p_bankcode,p_bankname,p_dist_code using p_searchname ;
commit;
end bank_search_sp;

该过程正在创建中，但是我不知道执行该过程时实际发生了什么，这是显示的错误

the Procedure is getting created but i dont know what actually happens when it was executed ,This is the error shown

ORA-01756: quoted string not properly terminated
ORA-06512: at "PENSIONS.BANK_SEARCH_SP", line 14
ORA-06512: at line 1 

推荐答案

此行中缺少尾随逗号:

v_tem :='select bankcode,bankname,dist_code from ' || UPPER (p_tablename) || '
where bankname like '''|| p_searchname||'''';

注意字符串片段末尾的4个逗号.第一个打开字符串，最后一个关闭字符串，中间两个插入一个逗号，它将成为搜索表达式的关闭逗号.

Notice the 4 commas at the end of the string fragment. The first opens the string, the last closes it, the middle two insert a single comma that will be the closing comma for the search expression).

UPPER()功能不是必需的； Oracle不在乎对象名称的大小写.

The UPPER() function is not necessary; Oracle does not care the casing of the object names.

我不确定具有相同结构的多个表是否是最佳解决方案.只有一个表带有一个指示列之间的差异的索引列会更好吗?

I am not sure if having multiple tables with the same structure is the best solution. Would not it be better to have one table only with an indexed column indicating the difference between banks?

这篇关于通过将表名作为参数使用oracle中的存储过程从表中搜索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！