常用的oracle语法

批量插入语法：

create or replace procedure(

    ftpname    in varchar2,

 

    ftpip      in varchar2,

 

    ftpuser    in varchar2,

 

    ftppwd     in varchar2,

 

    ftpport    in number,

 

    ftpappcode in varchar2,

 

    locCode      in varchar2) IMPORT_DATA is

 

begin

  insert into DME_DOC_SYS_INFO

  (LINE_ID,

   DOC_CODE,

   DOC_NAME,

   DOC_CONTENT_TYPE,

   DOC_SIZE,

   DOC_SUFFIX_NAME,

   DOC_FTP_PATH,

   DOC_FTP_FILENAME,

   APP_LINE_ID,

   BILL_CODE,

   TYPE_LINE_ID,

   CREATED_BY,

   CREATED_BY_NAME,

   CREATED_DATE)

  select dme_doc_sys_info_s.nextval,

         getdoccode,

         e.file_name,

         'doc_content_type',

         e.file_doc_number,

         substr(e.file_name, instr(e.file_name, '.', -1, 1)),

         substr(e.file_full_path,instr(e.file_full_path, '\', 1, 2) + 1),

         e.file_ftp_name,

         '101',

         'bill_code',

         '253',

         '503426',

         'shenchen_09_09_29',

         sysdate

    from export_temp e;

end IMPORT_DATA;

<---------------------------------------------------------------------------------

----------------------------------------------------------------------------------->

游标的分类：显示游标、隐式游标、引用类型游标

 

游标

 

create or replace procedure SP_IMPORT_DME_DOC is

 

       V_DOC_CODE VARCHAR2(100);

 

       V_DOC_NAME VARCHAR2(200);

 

       V_DOC_SIZE INTEGER;

 

       V_DOC_SUFFIX VARCHAR2(50);

 

       V_DOC_FTP_PATH VARCHAR2(200);

 

       V_DOC_FTP_FILENAME VARCHAR2(200);

 

       V_BILL_CODE VARCHAR2(50);

 

       V_LINE_ID NUMBER;

 

       V_ENCRYPT_SUPPORTED INTEGER;

 

       CURSOR CURSOR_TASK IS

 

              SELECT DOC_CODE, DOC_NAME, DOC_SIZE, DOC_SUFFIX_NAME, DOC_FTP_PATH, DOC_FTP_FILENAME, BILL_CODE, SEGMENT1

 

               FROM V_DME_DOC;

 

begin

 

     OPEN CURSOR_TASK;

 

    

 

     FETCH CURSOR_TASK INTO V_DOC_CODE, V_DOC_NAME, V_DOC_SIZE, V_DOC_SUFFIX, V_DOC_FTP_PATH, V_DOC_FTP_FILENAME, V_BILL_CODE, V_ENCRYPT_SUPPORTED;

 

    

 

     WHILE CURSOR_TASK%FOUND

 

     LOOP

 

         SELECT DME_DOC_SYS_INFO_S.NEXTVAL INTO V_LINE_ID FROM DUAL;

 

    

 

         INSERT INTO DME_DOC_SYS_INFO(

 

                LINE_ID,

 

                DOC_CODE,

 

                DOC_NAME,

 

                DOC_CONTENT_TYPE,

 

                DOC_SIZE,

 

                DOC_SUFFIX_NAME,

 

                DOC_FTP_PATH,

 

                DOC_FTP_FILENAME,

 

                APP_LINE_ID,

 

                BILL_CODE,

 

                TYPE_LINE_ID,

 

                CREATED_BY,

 

                CREATED_BY_NAME,

 

                CREATED_DATE,

 

                LAST_UPDATED_BY,

 

                LAST_UPDATED_BY_NAME,

 

                LAST_UPDATED_DATE,

 

                SEGMENT1)

 

          VALUES(V_LINE_ID, V_DOC_CODE, V_DOC_NAME, 'application/octet-stream', V_DOC_SIZE, V_DOC_SUFFIX, V_DOC_FTP_PATH, V_DOC_FTP_FILENAME,

 

               101, V_BILL_CODE, 253, '10098963', '唐文', SYSDATE, '10098963', '唐文', SYSDATE, V_ENCRYPT_SUPPORTED);

 

              

 

          FETCH CURSOR_TASK INTO V_DOC_CODE, V_DOC_NAME, V_DOC_SIZE, V_DOC_SUFFIX, V_DOC_FTP_PATH, V_DOC_FTP_FILENAME, V_BILL_CODE, V_ENCRYPT_SUPPORTED;

 

     END LOOP;

 

    

 

     CLOSE CURSOR_TASK;

 

    

 

     -- clean temp table

 

     DELETE FROM V_DME_DOC;

 

end SP_IMPORT_DME_DOC;

 

<----------------------------------------------------------------------------------------------------------

 

----------------------------------------------------------------------------------------------------------->