存储过程 动态查询用户下的表,该怎么解决
存储过程 动态查询用户下的表
oracle 存储过程中,可以有动态取用户名,然后查询该用户下数据的方法吗
create or replace procedure getAll is
va_member_code VARCHAR2(32);
cursor mycur is
select tt.member_code,
aa.cityname,
tt.cityid,
to_char(tt.open_da, 'yyyy-mm-dd') open_da,
tt.lim_size,
tt.mem_name
from (...);
Begin
for temp in mycur loop
if temp.member_code is not null then
va_member_code := temp.member_code;
select count(1)
into va_LOGIN_TOTALNUM
from '||va_member_code||'.op_log op
where op.optype = '登录'
and to_char(createtime, 'yyyy') = to_char(sysdate, 'yyyy');
在倾斜部分报错说表名无效,我要循环查所有用户下op_log表数据,谁有好方法吗
------解决方案--------------------
动态sql?
------解决方案--------------------
v_sql:='sql statement';
execute immediate v_sql into v_param;
eg:
SQL> declare v_par1 varchar2(100);
2 v_sql varchar2(1000);
3 v_ename varchar2(20):='scott.emp';
4 begin
5 v_sql:='select ename from '
------解决方案--------------------
v_ename
------解决方案--------------------
' where empno=7788';
6 execute immediate v_sql into v_par1;
7 dbms_output.put_line('v_par1:='
------解决方案--------------------
v_par1);
8 end;
9 /
v_par1:=SCOTT
oracle 存储过程中,可以有动态取用户名,然后查询该用户下数据的方法吗
create or replace procedure getAll is
va_member_code VARCHAR2(32);
cursor mycur is
select tt.member_code,
aa.cityname,
tt.cityid,
to_char(tt.open_da, 'yyyy-mm-dd') open_da,
tt.lim_size,
tt.mem_name
from (...);
Begin
for temp in mycur loop
if temp.member_code is not null then
va_member_code := temp.member_code;
select count(1)
into va_LOGIN_TOTALNUM
from '||va_member_code||'.op_log op
where op.optype = '登录'
and to_char(createtime, 'yyyy') = to_char(sysdate, 'yyyy');
在倾斜部分报错说表名无效,我要循环查所有用户下op_log表数据,谁有好方法吗
------解决方案--------------------
动态sql?
execute immediate 'select count(1)
into va_LOGIN_TOTALNUM
from '
------解决方案--------------------
va_member_code
------解决方案--------------------
'.op_log op
where op.optype = '登录'
and to_char(createtime, 'yyyy') = to_char(sysdate, 'yyyy')'
into va_LOGIN_TOTALNUM;
------解决方案--------------------
v_sql:='sql statement';
execute immediate v_sql into v_param;
eg:
SQL> declare v_par1 varchar2(100);
2 v_sql varchar2(1000);
3 v_ename varchar2(20):='scott.emp';
4 begin
5 v_sql:='select ename from '
------解决方案--------------------
v_ename
------解决方案--------------------
' where empno=7788';
6 execute immediate v_sql into v_par1;
7 dbms_output.put_line('v_par1:='
------解决方案--------------------
v_par1);
8 end;
9 /
v_par1:=SCOTT