请教SQL:返回某表的主键列及此列的最大值,以数据集返回,考虑表是任意的,有可能有多列主键。
我写了以下,实现了一半功能,查出表的主键列:SELECT T1.COLUMN_NAME FROM SYS.USER_CONS_COLUMNS T1
INNER JOIN SYS.USER_CONSTRAINTS T2 ON T1.TABLE_NAME=T2.TABLE_NAME AND T1.CONSTRAINT_NAME=T2.CONSTRAINT_NAME
WHERE T1.TABLE_NAME=某表变量 AND T2.CONSTRAINT_TYPE='P'
怎样再加上此列最大值 的列?谢谢朋友们帮忙!节日快乐!!
我写了以下,实现了一半功能,查出表的主键列:SELECT T1.COLUMN_NAME FROM SYS.USER_CONS_COLUMNS T1
INNER JOIN SYS.USER_CONSTRAINTS T2 ON T1.TABLE_NAME=T2.TABLE_NAME AND T1.CONSTRAINT_NAME=T2.CONSTRAINT_NAME
WHERE T1.TABLE_NAME=某表变量 AND T2.CONSTRAINT_TYPE='P'
怎样再加上此列最大值 的列?谢谢朋友们帮忙!节日快乐!!
再有这里需要在PL/SQL中执行动态SQL了,因为在SQL语句执行时,我们并不能确定主键列的名称。
主键列 最大值
-------------------------------------------
PK1 max(PK1)
PK2 max(pk2)
....
Str_Sql varchar2(200) := null;
cursor Cur_COLUMN is
select T1.COLUMN_NAME
from SYS.USER_CONS_COLUMNS T1
inner join SYS.USER_CONSTRAINTS T2
on T1.TABLE_NAME = T2.TABLE_NAME
and T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME
where T1.TABLE_NAME = upper(str_TABLE_NAME)
and T2.CONSTRAINT_TYPE = 'P';
Row_COLUMN_NAME Cur_COLUMN%rowtype;
begin
Str_Sql := 'select ';
for Row_COLUMN_NAME in Cur_COLUMN
loop
Str_Sql := Str_Sql || 'Max(' || Row_COLUMN_NAME.Column_Name || '),';
end loop;
Str_Sql := substrb(Str_Sql, 1, length(Str_Sql) - 1);
Str_Sql := Str_Sql || ' from ' || str_TABLE_NAME;
dbms_output.put_line(Str_Sql);
execute immediate Str_Sql;
exception
when others then
dbms_output.put_line('Error');
end Max_P_Key_Column;
declare
sSql varchar2(2000);
tmpsql varchar2(2000);
cursor c1 is
select a.column_name from user_cons_columns a,user_constraints b
where a.constraint_name=b.constraint_name and a.table_name=b.table_name
and b.constraint_type='P' and a.table_name='SMART_DEAL_201108_G';
begin
sSql := 'select ';
for c2 in c1 loop
tmpsql := tmpsql||',Max('||c2.Column_name||')';
end loop;
tmpSql := substr(tmpsql,2,length(tmpsql));
sSql := sSql||tmpsql||' from SMART_DEAL_201108_G ';
begin
execute immediate sSql;
dbms_output.put_line(sSql);
exception
when others then
dbms_output.put_line('Error');
end;
end;