Oracle详细使用

• 学习Oracle的使用（和mysql使用规则一样用“略”代表）

  • select语句的使用：略。

  • create创建表的使用：略。但是可以在表名前可以添加模式名称，可以复制表作为一个新表

      create table table_name as (
      select * from other_name
    );

  • alter修改表结构的使用：

    • 增加一列：略。

    • 修改一个或者多列的属性或者约束：略。另外Oracle提供【不】可见属性[in]visible

    • 删除一列或者多列（物理删除）

         alter table table_name drop (col1);
         alter table table_name drop(col1, col2) 

    • 修改列名名称：

        alter table table_name rename column old_name to new_name;

    • 修改表名名称：

        alter table table_name rename to new_name

  • 删除表数据drop：

    • 可以将表数据删除放置回收站中，若在后面添加purge，可以完全删除

        drop table table_name purge;

    • oracle还提供了表级联约束条件。多个表如果有关联，则必须先删除子表，后删除父表。

    • Oracle提供删除多个表的功能

        begin
          for rec in 
            (select table_name from all_tables
             where table_name like 'test_%')
          loop
            execute immediate 'drop table ' || rec.table_name || 'cascade constrains';
          end loop;
        end;
        /

  • 删除列set unused column:（逻辑删除）

    • 作用是将选中的列设置不可用，这样在数据操作时这列的数据是不可见的

        alter table table_name set unused column column_name;

    • 执行完上述操作之后，可以以另一种方式物理删除列

        alter table table_name drop unused columns;

• 学习Oracle的使用

  • modify修改列

    • 在修改列的过程中，不能随意修改类型长度

        alter table table_name modify column_name 类型(长度)

    • 若文本的长度大于设置的长度，执行时就会报错，可以使用替代函数裁剪数据（类型是varchar2）：

        update tahle_name
        set column_name=replace(column_name, "替代前str", "替代后str")

    • 可以结合表达式给列设置值

        alter table table_name modify column_name 类型(长度) generated always as (生成值的表达式)

  • 表名称重命名rename

      rename table_name to new_name

      rename table table_name to new_name

  • 数据类型

    • 数值类型NUMBER

      • number(pre[, next]) 第一个参数表示整数的位数，第二个参数表示小数的位数，即可以表示整数和小数， 若小数的位数超过自定义的位数，系统会四舍五入。number的表现范围mysql的int，smallint，number，decimal 另外FLOAT类型是数值类型的子类

    • 字符串类型char、nchar、varchar2、nvarchar2其实和mysql的char、varchar的语义是一样的，只不过带n表示存储 当前国家的Unicode码

    • 日期类型DATE

      • 日期类型转换为字符串时间to_char(date类型)

      • 字符串时间转换为日期类型to_date("字符串时间", "字符串格式")

      • 在插入语句涉及日期时，可以DATE指明字符串时间，或者使用to_date

  • 数据操作

    • 插入数据

      • 单条数据插入：略。

      • 多条数据插入

        • 在有条件时，all代表的意思是如果都满足条件，都要执行插入操作；first即只在第一次满足条件时插入数据

        • 无条件：

            insert all
                into table_name(col1, col2) values (val1, val2)
                into table_name(col1, col2) values (val1, val2)
                into table_name(col1, col2) values (val1, val2)
            子查询语句;

        • 有条件：

            insert first
              when condition1 then
                into table_name(col1, col2) values (val1, val2)
              when condition2 then 
                into table_name(col1, col2) values (val1, val2)
              else
                into table_name(col1, col2) values (val1, val2)
            子查询语句;

    • 更新语句update和删除语句delete： 略。 在删除数据时，若两表之间存在关联，必须先删除子表中的数据，后删除父表的数据； 若在创建子表的过程中指定on delete cascade表级约束，在删除父表时，会连同与子表关联的数据都会删除

  • 数据合并 merge语句就是同时执行多个语句（操作对象就是目标表），并将得到的数据集合并在目标表中， merge语句的语法： python """ merge into target_table using source_table on search_condition when matched then 数据操作1 when not matched then 数据操作2 """ 如果数据操作是更新，尽量不要操作同一行数据 如果数据操作是删除，则会删除on条件和where条件匹配的行记录

  • 数据排序order by：略。另外Oracle提供了nulls last约束，可以将null的行记录放置在最后

  • 数据去重distinct：略。

  • 数据集记录数限制： 12版本以上使用fetch next 数量 rows [only / with ties], 12版本以下使用where rownum <= 数量 可以在前面添加偏移量offset 数量，和mysql操作相同

  • 通配符%和：略。如果匹配%或者 则使用escape str格式化后面一个字符即可：

      select * from table_name where column_name like '%25!%%' escape '!'

  • group by分组语义和mysql一样，只不过group by语句是在where条件之前

  • having语句是和group by搭配使用，它们是连在一起的

• 学习Oracle的使用

  • 数据操作符

    • exits的返回值是True or False，也是存在的意思，如果是针对性的查询数据建议使用in语句

    • any用法其实和JavaScript的some方法的语义一样

    • all用法其实和JavaScript的every方法的语义一样

    • union的用法和mysql一样。 两个数据集的列数和类型一定要相等，列名可以不同，

          select name1, add_time
          from table_name1 t1
          union
          select name2, add_time
          from table_name2 t2

    • intersect行记录交集 两个数据集的列数和类型一定要相等，列名可以不同 帅选的都是行记录和列名相同的行保存

    • minus行记录差集 两个数据集的列数和类型一定要相等，列名可以不同 帅选的都是行记录和列名相同的行保存

  • 数据表连接 on条件执行完主表的数据不变，该连接的还是得连接 where条件执行对象是在多表连接完后的数据集

    • inner join内连接，using(column)等同于on后字段值相等，column必须存在于两个表中

        select * 
        from table_name1 t1
        inner join table_name2 t2 on t1.id = t2.id
        select *
        from table_name1 t1
        inner join table_name2 t2 using(id)

    • left join左连接：略

        select *
        from table_name1 t1
        left join table_name2 t2 on t1.id = t2.id
        and t1.name = "str"
        select *
        from table_name1 t1
        left join table_name2 t2 on t1.id = t2.id
        where t1.name = "str"

    • right join右连接：略

    • cross join笛卡尔连接，这个可以没有条件

        select *
        from table_name1 t1
        cross join table_name2 t2

    • 自连接：指的是两张相同的表某一列或者多列进行内连接

  • 数据库约束

    • 主键：

      • 列级约束：略

      • 表级约束：略。但是若指定主键约束名，表示方式：constraint primary_key_name primary key (col1, ...)

      • 添加主键：

          alter table table_name add constraint primary_key_name 
          primary key (col1)

      • 删除drop/启用enable/禁用disable主键：

          alter table table_name drop constraint primary_key_name 

          alter table table_name drop primary key

    • 外键：

      • 只能是表级约束：foreign key(column) references main_table(main_column) on delete [cascade/set null]

      • 添加外键：

          alter table table_name add constraint foreign_key_name 
          foreign key (col1) references table_name(col1)

      • 删除drop/启用enable/禁用disable外键：

          alter table table_name drop constraint foreign_key_name 

    • not null：略

    • unique：

      • 列级约束：略

      • 表级约束：略。但是若指定唯一约束名，表示方式：constraint unique_name unique (col1, ...)

      • 添加唯一：

          alter table table_name add constraint unique_name 
          unique (col1)

      • 删除drop/启用enable/禁用disable唯一：

          alter table table_name drop constraint unique_name 

    • 检查约束check 只有符合check后的表达式的要求才能更新或者新增数据

      • 列级约束：column_name data_type check (expression)

      • 表级约束：若指定check约束名，表示方式：constraint check_name check (expression)

      • 添加主键：

          alter table table_name add constraint check_name 
          check (expression)

      • 删除drop/启用enable/禁用disable主键：

          alter table table_name drop constraint check_name 
          alter table table_name drop primary key

  • 过程

    • 创建过程 """ create [or replace] procedure procedure_name [(parameter[, parameter])] is declare [declaration_section] begin executable_section [exception exception_section] end 

      [procedure_name]; """
      
        create or replace procedure insert_user
          (id in user.id%type, 
           name in user.name%type,
           res out number,
           res_msg out varchar2(20))
        is
        begin 
          res:=1;
          res_msg:='插入数据成功';
          insert into user values (id, name);
          exception
            when dup_val_on_index then 
              res:=-2000;
              res_msg:='插入数据重复';
            when others then
              res:=sqlcode;
              res_msg:=sqlerrm;
        end insert_user;
        /

    • 调用过程

        declare 
          res number;
          res_msg varchar2(20);
        begin 
          insert_user(101, '胡先森', res, res_msg);
          dbms_output.put_line('状态码' || res || ', 状态信息' || res_msg);
        end;
        /

    • 删除过程

        drop procedure insert_user

  • 游标

    • 游标声明

      • 无参数游标：该游标的结果集是所有的course_name并存在c1中，其course_name与name_in匹配

          cursor c1
            is 
              select course_number
              from course
              where course_name = name_in;
          for data in c1 loop:
            ...
          end loop;

      • 带参数游标：该游标的结果集是所有的course_number，其subject与通过参数传递给 游标的subject_name相匹配

         cursor c2(subject_name in varchar2)
              is
                select course_number
                from course
                where subject = subject_name

      • 带return子句的游标：返回值是course表的科目是chinese的所有列

          cursor c3
            return course%rowtype
          is
            select *
            from course
            where subject = 'chinese'

    • 游标打开

        open c1;

    • 游标提取 fetch cursor_name into variable_list; cursor_name：游标名称 variable_list：游标所需要的参数

        fetch c1 into output_number;

    • 游标关闭

        close c1;

    • 游标属性

      • %ISOPEN

        • 如果光标处于打开状态，则返回TRUE;如果光标处于关闭状态，则返回FALSE。

      • %FOUND

        • 如果声明了游标，但不打开，则返回INVALID_CURSOR，或者游标已关闭。

        • 如果游标处于打开状态，则返回NULL，但未执行提取。

        • 如果执行成功，则返回TRUE。如果没有行被返回，则返回FALSE。

      • %NOTFOUND

        • 如果声明了游标，但不打开，则返回INVALID_CURSOR，或者游标已关闭。

        • 如果游标处于打开状态，则返回NULL，但未执行提取。

        • 如果执行了成功的提取，则返回FALSE。 如果没有行被返回，则返回TRUE。

      • %ROWCOUNT

        • 如果声明了游标，但不打开，则返回INVALID_CURSOR,或者光标已关闭。

        • 返回获取的行数。

        • 除非遍历整个游标，否则ROWCOUNT属性不会给出真正的行数。 换句话说，不应该依赖这个属性来告诉游标在打开后有多少行。

        create or replace function select_all
          (name_in in varchar2)
          return number
        is 
          output_number number;
          cursor c1
          is 
            select course_number
            from course
            where course_name = name_in;
        begin 
            open c1;
            fetch c1 into output_number;
            if c1%notfound then 
              output_number := 9999;
            end if;
            close c1;
          return output_number;
        end;

• 学习Oracle的使用

• mysql触发器 语法： create trigger trigger_name after/before insert/update/delete on table_name for each row begin sql语句; end; 注：这里的new指的是order_table新增一行数据的对象,一般是after之后的操作对象；old一般指的是before之前的操作对象

    create or replace trigger insert_trigger
    before update on goods_table
    for each row
    declare 
      num number;
    begin 
      insert into order_table values (:gid, num);
    end;
    /

• oracle触发器 instead of 只能作用于视图中的行级触发器上 语法： create [or replace] trigger trigger_name after/before/instead of [insert [or update [or delete]]] on table_name [for each row] 行级触发器 [declare 变量声明] begin [可以夹带条件判断，如下] [if inserting then] 数据操作语句 [elsif updating then] 数据操作语句 [elsif deleting then] 数据操作语句 [end if;] exception 发生异常时执行的语句 end;

    create or replace trigger insert_trigger
    before update on goods_table
    for each row
    declare 
      num number;
    begin 
      insert into order_table values (:gid, num);
    end;
    /

  主键自增触发器

  create or replace trigger auto_increment
    before insert on table_name  
    for each row
  declare
    -- local variables here
  begin
    select seq_id.nextval into :new table_id from dual;
  end auto_increment;
  insert into table_name(col1, col2, col2);

  表级触发器：和上述语法及操作一样，只不过没有for each row，而且是不能使用:old和:new

• create or replace trigger insert_trigger
  before update on goods_table
  
  begin 
  insert into order_table (id, num)
  select gid, num from goods_table;
  end;
  /