【Oracle】数据库应用技术上机-week13-游标循环merge

it's-bellaaaa

于 2022-01-13 11:34:15 发布

阅读量148

点赞数 2

分类专栏：数据库文章标签： oracle 数据库 dba

本文链接：https://blog.csdn.net/Bella_Seven/article/details/122470287

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1.游标与%type结合

SQL> declare
  2    cursor c_1 is select empno from scott.emp;
  3    v_1 scott.emp.empno%type;
  4  begin
  5    open c_1;
  6    fetch c_1 into v_1;
  7    dbms_output.put_line(v_1);
  8    fetch c_1 into v_1;
  9    dbms_output.put_line(v_1||c_1%rowcount);
 10    close c_1;
 11  end;
 12  /
 
7369
74992
 
PL/SQL procedure successfully completed

2.游标与%rowtype结合

SQL> declare
  2    cursor c_1 is select * from scott.emp;
  3    v_1 scott.emp%rowtype;
  4  begin
  5    open c_1;
  6    fetch c_1 into v_1;
  7    dbms_output.put_line(v_1.empno);
  8    fetch c_1 into v_1;
  9    dbms_output.put_line(c_1%rowcount);
 10    close c_1;
 11  end;
 12  /
 
7369
2
 
PL/SQL procedure successfully completed

3.游标的%rowtype类型

SQL> declare
  2    cursor c_1 is select empno,ename,sal,emp.deptno,dname from scott.emp,scott.dept
  3    where scott.emp.deptno=scott.dept.deptno;
  4    v_1 c_1%rowtype;
  5  begin
  6    open c_1;
  7    fetch c_1 into v_1;
  8    dbms_output.put_line(v_1.empno||' '||v_1.sal);
  9    close c_1;
 10  end;
 11  /
 
7782 2450
 
PL/SQL procedure successfully completed

4.带参数的游标

SQL> declare
  2    cursor c_1 (v_xb xs.xb%type) is select * from xs where xb=v_xb;
  3    v_1 xs%rowtype;
  4  begin
  5    open c_1('男');
  6    fetch c_1 into v_1;
  7    dbms_output.put_line(v_1.xm);
  8    close c_1;
  9  end;
 10  /
 
王林
 
PL/SQL procedure successfully completed

5. while循环

SQL> declare
  2  s number:=1;
  3  n number:=2;
  4  begin
  5    while n<=10
  6      loop
  7        s:=s*n;
  8        n:=n+1;
  9      end loop;
 10      dbms_output.put_line(to_char(s));
 11  end;
 12  /
 
3628800
 
PL/SQL procedure successfully completed

6.loop endloop

SQL> declare
  2  s number:=1;
  3  n number:=2;
  4  begin
  5      loop
  6        s:=s*n;
  7        n:=n+1;
  8        exit when n>10;
  9      end loop;
 10      dbms_output.put_line(to_char(s));
 11  end;
 12  /
 
3628800
 
PL/SQL procedure successfully completed

7.for循环

SQL> declare
  2  s number:=1;
  3  n number:=2;
  4  begin
  5    for n in 1..10
  6      loop
  7        s:=s*n;
  8      end loop;
  9      dbms_output.put_line(to_char(s));
 10  end;
 11  /
 
3628800
 
PL/SQL procedure successfully completed

8.merge
平台搭建：

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      );
SQL> insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
 
1 row inserted
SQL> insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
 
1 row inserted
SQL> insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
 
1 row inserted
SQL> insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
 
1 row inserted
SQL> insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
 
1 row inserted
SQL> commit;
 
Commit complete
 
SQL> 
SQL> insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
 
1 row inserted
SQL> insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
 
1 row inserted
SQL> insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
 
1 row inserted
SQL> insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
 
1 row inserted
SQL> commit;
 
Commit complete

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

1）products为目标表，newproducts为源表，则若产品号相匹配，根据源表信息修改目标表的产品名(product_name)和产品类别（category）

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> select * from PRODUCTS;
 
                             PRODUCT_ID PRODUCT_NAME                                                 CATEGORY
--------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------
                                   1501 VIVITAR 35MM                                                 ELECTRNCS
                                   1502 OLYMPUS CAMERA                                               ELECTRNCS
                                   1600 PLAY GYM                                                     TOYS
                                   1601 LAMAZE                                                       TOYS
                                   1666 HARRY POTTER                                                 TOYS

2）products为目标表，newproducts为源表，则若产品号不匹配，则根据源表将新的纪录添加到目标表。

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
                                   1502 OLYMPUS CAMERA                                               ELECTRNCS
                                   1600 PLAY GYM                                                     TOYS
                                   1601 LAMAZE                                                       TOYS
                                   1666 HARRY POTTER                                                 TOYS
                                   1700 WAIT INTERFACE                                               BOOKS
 
6 rows selected

9.一个merge例子带update,delete 和insert 三种操作

SQL> merge into products p
  2  using newproducts np
  3  on(p.product_id=np.product_id)
  4  when matched then
  5    update
  6    set p.product_name=np.product_name,p.category=np.category
  7    DELETE WHERE (p.category = 'ELECTRNCS')
  8  when not matched then
  9    insert values(np.product_id,np.product_name,np.category);
 
4 rows merged