欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle详细使用

程序员文章站 2024-01-05 14:15:22
学习Oracle的使用(和mysql使用规则一样用“略”代表) select语句的使用:略。 create创建表的使用:略。但是可以在表名前可以添加模式名称,可以复制表作为一个新表 create table table_name as ( select * from other_name); alt ......
  • 学习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;
      /