1.distinct关键字,教材上都是select distinct a.txt from array a;但是我想用在其他地方的时候,比如
select a.txt, distinct b.no, distinct c.id from array a, bio b, cif c; 却报错,请问distinct这个关键字的用法都长神马样呀?
2.老师让我写一个存储过程,判断该表是不是加入到了分区里去了,如果是的话就执行下面这段SQL语句:
tempsql:='ALTER TABLE CIF_CROSS_RELATIONS
ADD PARTITION par_'||s_Data_Date||' VALUES LESS THAN(s_Data_Date)
TABLESPACE LZDP_DATA_1104';
execute immediate tempsql;没接触过,请问该怎么写这个存储过程呀?
select a.txt, distinct b.no, distinct c.id from array a, bio b, cif c; 却报错,请问distinct这个关键字的用法都长神马样呀?
2.老师让我写一个存储过程,判断该表是不是加入到了分区里去了,如果是的话就执行下面这段SQL语句:
tempsql:='ALTER TABLE CIF_CROSS_RELATIONS
ADD PARTITION par_'||s_Data_Date||' VALUES LESS THAN(s_Data_Date)
TABLESPACE LZDP_DATA_1104';
execute immediate tempsql;没接触过,请问该怎么写这个存储过程呀?
create or replace procedure out_time is
begin
dbms_output.put_line(systimestamp);
end out_time;
2.SQL>
SQL> create or replace procedure helpyou(s_Data_Date in varchar2) as
2 tempsql varchar2(2000);
3 begin
4 tempsql := 'ALTER TABLE CIF_CROSS_RELATIONS
5 ADD PARTITION par_' || s_Data_Date ||
6 ' VALUES LESS THAN(s_Data_Date)
7 TABLESPACE LZDP_DATA_1104';
8 execute immediate tempsql;
9 end;
10 /Procedure created
create or replace procedure out_time is
begin
dbms_output.put_line(systimestamp);
end out_time;
SQL>
SQL> create or replace procedure helpyou(s_Data_Date in varchar2) as
2 tempsql varchar2(2000);
3 v_count number;
4 begin
5 select count(*)
6 into v_count
7 from dba_tab_partitions
8 where table_name = 'CIF_CROSS_RELATIONS';
9 if v_count > 0 then
10 tempsql := 'ALTER TABLE CIF_CROSS_RELATIONS
11 ADD PARTITION par_' || s_Data_Date ||
12 ' VALUES LESS THAN(s_Data_Date)
13 TABLESPACE LZDP_DATA_1104';
14 execute immediate tempsql;
15 end if;
16 end;
17 /Procedure createdSQL>
--表本来就是存在的,只是对表有没有进行分区吧!
select partition_name from user_tab_partitions
where table_name='CIF_CROSS_RELATIONS'
--如果partition_name有值,则说明该表是分区表,执行Sql即可