Oracle-merge命令

1.根据查询的xs表创建表xs1

create table xs1 as select * from xs;

2.删除表xs1的所有内容，只保留表结构

 	 truncate table xs1

3.向xs1中插入一条记录

insert into xs1(XH,XM,ZYM,XB,CSSJ,ZXF) 
      	   values('007' ,'test', '计算机','男', TO_DATE('19900130','YYYYMMDD'),36);

4.merge 命令

 	 merge into xs a
        using xs1 b on (a.xh=b.xh)
 	 when not matched then 
   		 insert (a.xh,a.xm,a.zym,a.xb,a.cssj,a.zxf) values (b.xh,b.xm,b.zym,b.xb,b.cssj,b.zxf);

5.创建表PRODUCTS 和 NEWPRODUCTS

create table PRODUCTS
     (
         PRODUCT_ID        INTEGER,
         PRODUCT_NAME      VARCHAR2(60),
         CATEGORY          VARCHAR2(60)
     );
  	 create table NEWPRODUCTS
     (
        PRODUCT_ID         INTEGER,
        PRODUCT_NAME       VARCHAR2(60),
        CATEGORY           VARCHAR2(60)
     );

6.插入记录到表PRODUCTS 和 NEWPRODUCTS

begin
 	 insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
 	 insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
  	insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
  	insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
 	 insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
  commit;
  Insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
  insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
  insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
  insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
  commit;
end; 

7.merge 命令 into旧表 using新表

	merge into products p
        using newproducts np on (p.product_id = np.product_id)
  	when matched then 
    --除了主键以外 全都要update
    update set p.product_name = np.product_name,
               p.category = np.category
    delete where (p.category = 'ELECTRNCS') 
 	 when not matched then 
    --插入的时候要包含主键
    insert values(np.product_id, np.product_name, np.category); 

8.查询表一共多少条记录

	select count(*) from xs;

9.查询表xs一共多少记录 并把结果赋值给v_1

declare
  v_1 number;
begin
  select count(*) into v_1 from xs;
  dbms_output.put_line('一共有 '||v_1||' 记录');
  exception 
    when others then 
      dbms_output.put_line('Error');
end;

10.查询一条记录 把这一条记录的查询结果保存到变量中

declare
  v_xm xs.xm%type;
  v_zym xs.zym%type;
	begin
  select xm,zym into v_xm,v_zym from xs where xh='007';
  dbms_output.put_line(v_xm||' '||v_zym);
  exception 
    when others then 
      dbms_output.put_line('Error');
end;

11.去掉where子句 查询多条记录的时候 会报错 执行异常里面的代码

declare
  v_xm xs.xm%type;
  v_zym xs.zym%type;
	begin
  select xm,zym into v_xm,v_zym from xs;
  dbms_output.put_line(v_xm||' '||v_zym);
  exception 
    when others then 
      dbms_output.put_line('Error');
end;

12.不加异常会自动抛出异常 这个会抛出： 实际返回的行数超出请求的行数

	declare
  v_xm xs.xm%type;
  v_zym xs.zym%type;
	begin
  select xm,zym into v_xm,v_zym from xs;
  dbms_output.put_line(v_xm||' '||v_zym);
end;  

13.添加上各种异常

declare
  v_xm xs.xm%type;
  v_zym xs.zym%type;
begin
  select xm,zym into v_xm,v_zym from xs;
  dbms_output.put_line(v_xm||' '||v_zym);
  exception 
    when too_many_rows then
      dbms_output.put_line('Error：查询到多行');
    when no_data_found then
      dbms_output.put_line('Error：没有查询到数据');
    when others then 
      dbms_output.put_line('Error：其他'||sqlcode||' '||sqlerrm);
end;

14.行类型 rowtype 查询到的是一行内容

declare
   v_1 scott.emp%rowtype;
begin
  select  * into v_1 from scott.emp where empno='7788';
  dbms_output.put_line(v_1.empno||' '||v_1.ename);
end;    

15.查询系统时间

 select sysdate from dual; 

16.时间可以加减运算

 select sysdate-to_date('20190401','yyyymmdd') from dual;

17.to_char 转换为字符串

 select to_char(sysdate,'DY') from dual;