Oracle實驗8--Merge與歸檔

風骨散人Chiam發表於2020-12-11
一、	Merge操作
1.	平臺搭建:

SQL>     create table PRODUCTS
  2      (
  3      PRODUCT_ID INTEGER,
  4      PRODUCT_NAME VARCHAR2(60),
  5      CATEGORY VARCHAR2(60)
  6      );
 
Table created
SQL>     create table NEWPRODUCTS
  2      (
  3      PRODUCT_ID INTEGER,
  4      PRODUCT_NAME VARCHAR2(60),
  5      CATEGORY VARCHAR2(60)
  6      );
 
Table created

SQL> 
SQL> begin
  2      insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
  3      insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
  4      insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
  5      insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
  6      insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
  7      commit;
  8      Insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
  9      insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
 10      insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
 11      insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
 12      commit;
 13  end;
 14  /

PL/SQL procedure successfully completed
結果:
SQL> select * from products;
 
                             PRODUCT_ID PRODUCT_NAME                                                 CATEGORY
--------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------
                                   1501 VIVITAR 35MM                                                 ELECTRNCS
                                   1502 OLYMPUS IS50                                                 ELECTRNCS
                                   1600 PLAY GYM                                                     TOYS
                                   1601 LAMAZE                                                       TOYS
                                   1666 HARRY POTTER                                                 DVD
 
SQL> select *from newproducts;
 
                             PRODUCT_ID PRODUCT_NAME                                                 CATEGORY
--------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------
                                   1502 OLYMPUS CAMERA                                               ELECTRNCS
                                   1601 LAMAZE                                                       TOYS
                                   1666 HARRY POTTER                                                 TOYS
                                   1700 WAIT INTERFACE                                               BOOKS

2.實現Merge1)	合併實現:

SQL> MERGE INTO products p
  2  USING newproducts np
  3  ON (p.product_id = np.product_id)
  4  WHEN MATCHED THEN
  5    UPDATE SET
  6    p.product_name = np.product_name,
  7    p.category = np.category
  8    DELETE WHERE (p.category = 'ELECTRNCS')
  9  WHEN NOT MATCHED THEN
 10    INSERT
 11    VALUES (np.product_id, np.product_name, np.category)
 12  /
 
4 rows merged

結果:
SQL> select * from products;
 
                             PRODUCT_ID PRODUCT_NAME                                                 CATEGORY
--------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------
                                   1501 VIVITAR 35MM                                                 ELECTRNCS
                                   1600 PLAY GYM                                                     TOYS
                                   1601 LAMAZE                                                       TOYS
                                   1666 HARRY POTTER                                                 TOYS
                                   1700 WAIT INTERFACE                                               BOOKS
 
SQL> select *from newproducts;
 
                             PRODUCT_ID PRODUCT_NAME                                                 CATEGORY
--------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------
                                   1502 OLYMPUS CAMERA                                               ELECTRNCS
                                   1601 LAMAZE                                                       TOYS
                                   1666 HARRY POTTER                                                 TOYS
                                   1700 WAIT INTERFACE                                               BOOKS

(2)	分別實現:

SQL> merge into products p
  2  using newproducts np
  3  on (p.product_id=np.product_id)
  4  when matched then
  5    update set
  6    p.product_name=np.product_name,
  7    p.category=np.category;
 
3 rows merged
 
SQL> 
SQL> Merge into products p
  2  using newproducts np
  3  on (p.product_id = np.product_id)
  4  when  not matched then
  5    insert values (np.product_id,np.product_name,np.category);
 
1 row merged

結果:
SQL> select * from products;
 
                             PRODUCT_ID PRODUCT_NAME                                                 CATEGORY
--------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------
                                   1501 VIVITAR 35MM                                                 ELECTRNCS
                                   1600 PLAY GYM                                                     TOYS
                                   1601 LAMAZE                                                       TOYS
                                   1666 HARRY POTTER                                                 TOYS
                                   1700 WAIT INTERFACE                                               BOOKS
                                   1502 OLYMPUS CAMERA                                               ELECTRNCS
 
6 rows selected
 
SQL> select *from newproducts;
 
                             PRODUCT_ID PRODUCT_NAME                                                 CATEGORY
--------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------
                                   1502 OLYMPUS CAMERA                                               ELECTRNCS
                                   1601 LAMAZE                                                       TOYS
                                   1666 HARRY POTTER                                                 TOYS
                                   1700 WAIT INTERFACE                                               BOOKS

二、	歸檔檔案
切換到“SYS”使用者:

SQL> show user;
USER"SYSTEM"
SQL> conn as SYSDBA
請輸入使用者名稱:  SYS
輸入口令:
已連線。
SQL> show user;
USER"SYS"
 
方法一:

SQL> ARCHIVE LOG LIST
資料庫日誌模式            存檔模式
自動存檔             啟用
存檔終點            USE_DB_RECOVERY_FILE_DEST
最早的聯機日誌序列     12
下一個存檔日誌序列   14
當前日誌序列           14

方法二:
SQL> select name,log_mode from v$database
  2  ;

NAME      LOG_MODE
--------- ------------
ORCL      ARCHIVELOG


修改資料庫的歸檔模式:
SQL> shutdown immediate;
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup nomount;
ORACLE 例程已經啟動。

Total System Global Area  612368384 bytes
Fixed Size                  1292036 bytes
Variable Size             230689020 bytes
Database Buffers          373293056 bytes
Redo Buffers                7094272 bytes
SQL> alter database mount;

資料庫已更改。

SQL> alter database archivelog;

資料庫已更改。



三、	分支語句
【9-6SQL> declare
  2     v_Num integer :=1;
  3     v_Sum integer :=0;
  4  begin
  5    loop
  6      v_Sum := v_Sum +v_Num;
  7      dbms_output.put_line(v_Num);
  8      IF v_Num = 3 THEN
  9        EXIT;
 10        END IF;
 11      dbms_output.put_line('+');
 12      v_Num := v_Num +1;
 13      end loop;
 14      dbms_output.put_line('=');
 15      dbms_output.put_line(v_Sum);
 16  end;
 17  /
 
1+2+3=6
 
PL/SQL procedure successfully completed
【9-7SQL> DECLARE
  2    v_num integer :=1;
  3    v_sum integer :=0;
  4  BEGIN
  5    LOOP
  6      v_sum := v_sum+v_num;
  7      dbms_output.put_line(v_num);
  8      exit when v_num=3;
  9      dbms_output.put_line('+');
 10      v_num := v_num+1;
 11    END LOOP;
 12    dbms_output.put_line('=');
 13    dbms_output.put_line(v_sum);
 14  END;
 15  /
 
1
+
2
+
3
=
6
 
PL/SQL procedure successfully completed
【9-8SQL> DECLARE
  2    v_Num INTEGER := 1;
  3    v_Sum INTEGER := 0;
  4  BEGIN
  5      while v_Num <= 3
  6      loop
  7        v_Sum := v_Sum + v_Num;
  8        dbms_output.put_line(v_Num);
  9        IF v_Num < 3 THEN
 10          dbms_output.put_line('+');
 11        END IF;
 12        v_Num := v_Num + 1;
 13      END LOOP;
 14      dbms_output.put_line('=');
 15      dbms_output.put_line(v_Sum);
 16  END;
 17  /
 
1
+
2
+
3
=
6
 
PL/SQL procedure successfully completed
【9-9SQL> DECLARE
  2    v_num integer;
  3    v_sum integer:=0;
  4  BEGIN
  5     for v_num in 1..3
  6     LOOP
  7        v_sum:=v_sum+v_num;
  8        dbms_output.put_line(v_num);
  9        IF v_num<3 THEN
 10          dbms_output.put_line('+');
 11        END IF;
 12     END LOOP;
 13     dbms_output.put_line('=');
 14     dbms_output.put_line(v_sum);
 15  END;
 16  /
 
1
+
2
+
3
=
6
 
PL/SQL procedure successfully completed

相關文章