Oracle常用SQL集锦----持续更新中......
--1)Primary table
create table T_FRANK( T_NO NUMBER not null, T_NAME NUMBER)tablespace STAGING_TEST2_DATA pctfree 10 pctused 40 initrans 1 maxtrans 255 storage ( initial 1M minextents 1 maxextents unlimited );
alter table T_FRANK add constraint PK_T_FRANK primary key (T_NO) using index tablespace STAGING_TEST2_DATA pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );
alter table T_FRANK modify T_NAME VARCHAR2(30);
create table T_FRANK_TEST( F_ID NUMBER not null, T_NO NUMBER not null, IS_MANDATORY CHAR(1) default '1', QTY NUMBER default '0', PRICE NUMBER(16,4), LAUNCH_DATE DATE, MODIFIER VARCHAR2(50))tablespace STAGING_TEST2_DATA pctfree 10 pctused 40 initrans 1 maxtrans 255 storage ( initial 1M minextents 1 maxextents unlimited );
alter table T_FRANK_TEST add constraint PK_T_FRANK_TEST primary key (F_ID, T_NO) using index tablespace STAGING_TEST2_DATA pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );
alter table T_FRANK_TEST add constraint FK_T_FRANK_TEST_T_FRANK foreign key (T_NO) references T_FRANK (T_NO) on delete cascade;
create index T_FRANK_TEST_F_ID on T_FRANK_TEST (F_ID) tablespace STAGING_TEST2_DATA pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );
--7) insert data to T_FRANKinsert into T_FRANK values(NVL((select max(T_NO) from T_FRANK),0)+1,'Frank1');insert into T_FRANK values(NVL((select max(T_NO) from T_FRANK),0)+1,'Frank2');commit;
insert into T_FRANK_TEST values(NVL((select max(F_ID) from T_FRANK_TEST),0)+1,1,'1',1000,66.98,sysdate,'FrankWang');insert into T_FRANK_TEST values(NVL((select max(F_ID) from T_FRANK_TEST),0)+1,2,'1',1000,66.98,sysdate,'FrankWang');commit;
UPDATE T_FRANK_TEST t SET t.MODIFIER='FrankWang2' where t.f_id=2;commit;
delete from T_Frank f where f.t_no=1;commit;
declare p_table varchar2(30):='T_FRANK_TEST'; v_count number;begin select count(1) into v_count from user_objects where object_name = upper('T_FRANK_TEST'); if v_count > 0 then execute immediate 'drop table ' || p_table ||' cascade constraints'; end if;end;/
DECLARE --define batch delete number of invoices no topnum constant int:=5; ----53473089.=5000*maxrecords=5000*10694, maxnum=10694 maxnum constant int:=2; i int :=1; new_invoice_no invoice_article_size.invoice_no%TYPE; r_row invoice_article_size%rowtype; --get top 500 record and stored to cursor cursor my_cursor is select * from invoice_article_size where invoice_no not in (select invoice_no from invoice) and rownum <= topnum; TYPE t_invoice_no IS TABLE OF varchar2(20) INDEX BY BINARY_INTEGER; j int :=1; t_new_invoice_no t_invoice_no; BEGIN WHILE i<=maxnum LOOP open my_cursor; loop fetch my_cursor into r_row; -- not found, exit from cursor exit when my_cursor%notfound; --get invoce_no t_new_invoice_no(j) := r_row.invoice_no; dbms_output.put_line(t_new_invoice_no(j)); --delete it one by one and commit delete from invoice_article_size where invoice_no = t_new_invoice_no(j); commit; end loop; close my_cursor; dbms_output.put_line(i); --execute another loop i := i+1; END LOOP;END;
alter table INVOICE_ARTICLE_SIZE add constraint FK_INOVICE_ARTICLE_SIZE foreign key (INVOICE_NO,ARTICLE_NO,ERP_ORDER_ID) references INVOICE_ARTICLE (INVOICE_NO,ARTICLE_NO,ERP_ORDER_ID) on delete cascade;
select count(1) from invoice_article_size ins where (ins.invoice_no not in(select invoice_no from invoice));
delete from INVOICE_ARTICLE_SIZE i where (i.invoice_no not in(select invoice_no from invoice));commit;