Oracle惯用SQL集锦-持续更新中.

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
  );

 

-- 2)add PK for table T_FRANK

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
  );

-- update field's type

alter table T_FRANK modify T_NAME VARCHAR2(30);

--3)Foreign table

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
  );

-- 4)add PK for table T_FRANK_TEST

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
  );

 
--5)add FK for T_FRANK_TEST

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;

--6)Create/Recreate indexes

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_FRANK
insert 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;

--8) insert data to T_FRANK_TEST

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;

--9) update data

UPDATE T_FRANK_TEST t SET t.MODIFIER='FrankWang2' where t.f_id=2;
commit;

--10) delete T_FRANK(cascade delete T_FRANK_TEST)

delete from T_Frank f where f.t_no=1;
commit;

--11) delete the data and the table
--因为需要，Oracle有没有类似My SQL的drop table if exists这类的支持，只好自己写一个Declare了

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;
/

--12) usage for CURSOR , LOOP and WHILE in DECLARE
下面是实际工作中遇到的问题：

For this sap invoice error, there are two work need to do: please follow below order to execute the statement(1 -> 2), thanks!

1> Data patch:
Delete the dirty data from the table INVOICE_ARTICLE_SIZE, in order to delete it quickly(the dirty data is too large(about53473089)), below was my prepared statement:
According to preliminary estimates, below statement need about 13 hours in my local env. May be it need few time in Production.

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;

2> Hotfix:
add cascade delete for INVOICE_ARTICLE_SIZE, below is the SQL:(it also need long time to execute, it depends on number of the record in the size table.)

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;

BTW, may be this data patch can’t finish today,
But our system will continue to generate dirty data before you do Hotfix,
Hence I suggest, you’d better execute below sql to check whether there are dirty data exist:

select count(1) from invoice_article_size ins where (ins.invoice_no not in(select invoice_no from invoice));

If above select result>0, it means there are dirty data exist, you can use below delete statement to delete it directly.

delete from INVOICE_ARTICLE_SIZE i where (i.invoice_no not in(select invoice_no from invoice));
commit;

--13)待续。。。