精华内容
下载资源
问答
  • Oracle实现自增

    2021-05-03 03:19:00
    首先创建一个序列(sequence),实现自增--Createsequencecreatesequencet_user_seqminvalue1--最小值:1nomaxvalue--没有最大值startwith1--从1开始incrementby1--每次递增1nocache--不使用缓存(也可设置为cachen,...

    首先创建一个序列(sequence),实现自增

    -- Create sequence

    create sequence t_user_seq

    minvalue 1 -- 最小值:1

    nomaxvalue -- 没有最大值

    start with 1 -- 从1开始

    increment by 1 --每次递增1

    nocache --不使用缓存 (也可设置为 cache n, 区别见本文最后附注部分)

    order;

    创建触发器,在触发器中使用之前创建好的sequence :t_user_seq

    -- Create trigger

    create or replace trigger t_user_trigger

    -- 在往表t_user中插入新数据之前触发

    before insert on t_user

    for each row

    begin

    select  t_user_seq.nextval

    -- 自增字段: user_id

    into:new.user_id

    from sys.dual ;

    end;

    最后执行几条insert 语句测试一下。

    附注:

    如果指定CACHE值,Oracle就可以预先在内存里面放置一些Sequence,这样存取的快些。cache里面的取完后,Oracle自动再取一组到cache。使用cache或许会跳号, 比如数据库突然不正常down掉(shutdown abort),cache中的Sequence就会丢失。举个例子:比如你的sequence中cache 100,那当你sequence取到90时突然断电,那么在你重启数据库后,sequence的值将从101开始。

    如果指定NOCACHE值,Oracle就不会预先在内存里面存放Sequence,当然这也就可以避免数据库不正常down掉的sequence丢失。不过会产生一些问题:创建nocache   sequence在高并发访问时,容易导致row cache lock等待事件,主要原因是每次获取nextval时都需要修改rowcache中的字典信息。使用nocache  sequence,还会导致如下问题:

    由于每次修改字典信息都需要commit,可能导致log file sync等待,nocache sequence在RAC环境下,会对基于sequence生成的列创建的索引造成实例间大量索引块争用

    基于以上问题,避免创建nocache sequence。

    再来看看sequence相关保护机制:

    row cache lock:在调用sequence.nextval情况下需要修改数据字典时发生,对应row cache lock事件

    SQ lock:在内存缓存(并非rowcache)上获取sequence.nextval时发生,对应enq:SQ-contention事件

    SV lock:RAC环境下获取cache+order属性的sequence.nextval时发生,对应DFS lock handle事件

    什么情况下使用cache什么时间上使用nocache?

    我个人感觉应该尽量使用cache,因为现在的数据库很多都是在高并发的情况下运行的,首先这样可以搞性能,并且也不会产生row cache lock等待事件。可能有些人会担心数据库不正常的down掉会产生序列号间断,但这也是很少的情况。当然如果你的业务要求是绝不能产生间断的序列号,那就要使用nochache了。

    参考:http://blog.csdn.net/duanning397/article/details/7670302

    展开全文
  • Oracle 视图创建自增ID

    2020-08-24 09:17:10
    select ROW_NUMBER() over (order by getdate()) as id

    select ROW_NUMBER() over (order by sysdate()) as id

    展开全文
  • View Constraints Oracle does not enforce view constraints. However,operations on views are subject to the integrity constraints defined on the underlying base tables. This means that you can enforce ...

    View Constraints

    Oracle does not enforce view constraints. However,operations on views

    are subject to the integrity constraints defined on the underlying

    base tables. This means that you can enforce constraints on views

    through constraints on base tables.

    Notes on View Constraints View constraints are a subset of table

    constraints and are subject to the following restrictions:

    You can specify only unique,primary key,and foreign key constraints

    on views. However,you can define the view using the WITH CHECK OPTION

    clause,which is equivalent to specifying a check constraint for the

    view.

    View constraints are supported only in DISABLE NOVALIDATE mode. You

    cannot specify any other mode. You must specify the keyword DISABLE

    when you declare the view constraint. You need not specify NOVALIDATE

    explicitly,as it is the default.

    The RELY and NORELY parameters are optional. View constraints,because

    they are unenforced,are usually specified with the RELY parameter to

    make them more useful. The RELY or NORELY keyword must precede the

    DISABLE keyword. Please refer to “RELY Clause” for more information.

    Because view constraints are not enforced directly,you cannot specify

    INITIALLY DEFERRED or DEFERRABLE.

    You cannot specify the using_index_clause,the exceptions_clause

    clause,or the ON DELETE clause of the references_clause.

    You cannot define view constraints on attributes of an object column.

    展开全文
  • oracle创建自增主键

    千次阅读 2013-03-01 13:59:14
    在网上查了oracle自增主键的列子,但是里面语句涉及的只是自己并不了解,所以本人结合着文档学习了一下oracle自增主键所涉及的一些知识。 首先是触发器: Use the CREATE TRIGGER statement to create a ...

    在网上查了oracle自增主键的列子,但是里面语句涉及的只是自己并不了解,所以本人结合着文档学习了一下oracle自增主键所涉及的一些知识。

    首先是触发器:

    Use the CREATE TRIGGER statement to create a database trigger, which is:

    • A stored PL/SQL block associated with a table, a schema, or the database or

    • An anonymous PL/SQL block or a call to a procedure implemented in PL/SQL or Java

    Oracle Database automatically executes a trigger when specified conditions occur.

     

    咱们要用的是与一个表相关的存储pl/sql语句块。

    Prerequisites

    To create a trigger in your own schema on a table in your own schema or on your own schema (SCHEMA), you must have the CREATE TRIGGER system privilege.

    先决条件,在一个表上创建出发器必须有create trigger系统权限

    If the trigger issues SQL statements or calls procedures or functions, then the owner of the trigger must have the privileges necessary to perform these operations. These privileges must be granted directly to the owner rather than acquired through roles.

    如果触发器发表了一个sql语句或者程序或者函数,触发器的所有者必须有执行这些操作的权限。这些权限一定要直接授权给该所有者而不是通过角色获得。

    大概了解了触发器,咱们看看语法

     

     

    一下语法说明:

    create_trigger

    OR REPLACE

    Re-creates the trigger if it exists, and recompiles it.

    Users who were granted privileges on the trigger before it was redefined can still access the procedure without being regranted the privileges.

    就是如果该触发器存在,重新创建,重新编译它

    咱们的sql语句先写个初步:create or replace  trigger trigger_sequence

    BEFORE

    Causes the database to fire the trigger before running the triggering event. For row triggers, the trigger fires before each affected row is changed.

    就是说执行触发器事件之前引起数据库调用该触发器。对于行触发器,触发器在每个受影响的行改变前调用。

    触发器事件就是引起该触发器出发的操作,而行触发器就是如果触发器事件引起的每个行都会引起触发器出发

    对于咱们是insert 一行之前要先把序列的值赋给该行的主键,也就是选中before

    sql语句这回写成

    :create or replace  trigger trigger_sequence before insert on author

     

    WHEN (condition)

    Specifies a SQL condition that the database evaluates for each row that the triggering statement affects. If the value of condition is TRUE for an affected row, then trigger_body runs for that row; otherwise, trigger_body does not run for that row. The triggering statement runs regardless of the value of condition.

    The condition can contain correlation names (see "referencing_clause ::="). In condition, do not put a colon (:) before the correlation name NEW, OLD, or PARENT (in this context, it is not a placeholder for a bind variable).

    指定一个sql条件,该sql条件是怎么计算出来的呢?是数据库对触发该触发器的语句所影响的每行计算的值。若所受影响行该值是true,则trigger_body执行所影响的行。否则不执行。引起触发器触发的语句都会执行而不管条件值是true还是false。

    注意下面的说明:

    条件语句可以包括相关名。在条件语句中,不要把冒号放在条件名NEW.OLD或者PARENT之前(在上下文中,它不是一个对绑定变量的符号占位符)

    咱们来看看correlation names是什么

     

    Correlation Names and Pseudorecords

    Note:

    This topic applies only to triggers that fire at row level—that is, row-level simple DML triggers and compound DML triggers with row-level timing point sections.
    注意此主题只用在行级触发器,simple DML triggers和compound DML triggers

    A trigger that fires at row level can access the data in the row that it is processing by using correlation names. The default correlation names are OLD, NEW, and PARENT. To change the correlation names, use the REFERENCING clause of the CREATE TRIGGER statement (see "referencing_clause ::=").

    行级触发器可以获得改行的数据,它整处理的,通过用相关名。默认的相关名是OLD,NEW,和PARENT.改变相关名用REFERENCING clause of the CREATE TRIGGER

    对于咱们其实就是要拿到该插入行的id。

     If the trigger is created on a table or view, then OLD and NEW refer to the current row of the table or view, and PARENT is undefined.

    如果触发器在一个表被创建。则OLD和NEW参照该表或者视图的当前行,PARENT无定义。

    对于咱们来说把触发器创建在author表上。接着咱们来看看OLD,NEW PARENT是什么

    OLD, NEW, and PARENT are also called pseudorecords, because they have record structure, but are allowed in fewer contexts than records are. The structure of a pseudorecord is table_name%ROWTYPE, where table_name is the name of the table on which the trigger is created (for OLD and NEW) or the name of the parent table (for PARENT).

    In the trigger_body of a simple trigger or the tps_body of a compound trigger, a correlation name is a placeholder for a bind variable. Reference the field of a pseudorecord with this syntax:

    :pseudorecord_name.field_name
    

    然后咱们可以试试文档给出的例子

    DROP TABLE Emp_log;
    CREATE TABLE Emp_log (
      Emp_id     NUMBER,
      Log_date   DATE,
      New_salary NUMBER,
      Action     VARCHAR2(20));
     
    Create trigger that inserts row in log table after EMPLOYEES.SALARY is updated:
    
    CREATE OR REPLACE TRIGGER log_salary_increase
      AFTER UPDATE OF salary ON employees
      FOR EACH ROW
    BEGIN
      INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)
      VALUES (:NEW.employee_id, SYSDATE, :NEW.salary, 'New Salary');
    END;
    /
    Update EMPLOYEES.SALARY:
    
    UPDATE employees
    SET salary = salary + 1000.0
    WHERE Department_id = 20;
    


    你会发现触发器插入到emp_log里的:NEW.employee_id和:NEW.salary就是触发该触发器的行UPDATE employeesSET salary = salary + 1000.0WHERE Department_id = 20;的列值employee_id,

    那么咱们就有思路把先判断insert author主键是否存在,

    即:create or replace  trigger trigger_sequence before insert on author when (new,id is null)

    In the WHEN clause of a conditional trigger, a correlation name is not a placeholder for a bind variable. Therefore, omit the colon in the preceding syntax.

    这句就是说不要前面加引号记下来

    添加语句块

     

    create or replace  trigger trigger_sequence before insert on author when(new.id is null)

    begin

     

    end;

    咱们看看statement怎么写

    注意select_into_statement了吗,查一下它

    SELECT INTO Statement

    The SELECT INTO statement retrieves values from one or more database tables (as the SQL SELECT statement does) and stores them in variables (which the SQL SELECT statement does not do).

    select into 语句返回值,该值是源自一个或者更多个数据库的表中,并把他们存入到变量里。

    咱们的目的是把sequence存入的id里

    select into语法

    即select sequence.currval

    into_clause

    variable即要插入的变量

    select author_id_sequence.currval into :new,id;

    把sql写全

    create or replace  trigger trigger_sequence before insert on author when(new.id is null)

    begin

    select author_id_sequence.currval into :new,id;

    end;

    现在咱们还底创建自增序列

    create sequence author_id_sequence increment by 1 start with 1;

    了解下什么是自增序列

    CREATE SEQUENCE

    Purpose

    Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

    When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. After a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

    当自增序列被创建,序列自增独立于事务提交和回滚。若2个用户同时提升了同一个序列,那么每个用户获得的序列值也许有间隔,因为序列值正被另外的用户创建。一个用户从不会获得被另个用户创建的序列值。在序列值被一个用户创建后,该用户可以继续获得该值而不管序列被另外用户创建。

    Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.

    序列值是被创建独立于表,所以同一个序列可以被多个表使用。那个可能的序列值将会出现跳跃,因为他们被穿件使用在最终回滚了的事务,另外一个单一用户不知道领完的用户证用着该序列。

    After a sequence is created, you can access its values in SQL statements with the CURRVAL pseudocolumn, which returns the current value of the sequence, or the NEXTVAL pseudocolumn, which increments the sequence and returns the new value.

    这一段要注意:在一个序列被创建后,你可以获得他的值在sql语句用CURRVAL伪列,他会返回目前序列的值或者NEXTVAL伪列,返回序列增加后的,新的值。

    最后咱们的sq;语句

    create sequence author_id_sequence increment by 1 start with 1;

    create or replace  trigger trigger_sequence before insert on author when(new.id is null)
    begin
    select author_id_sequence.currval into :new.id;
    end;

    但是出现错误。。。。when 子句不能与表级触发器使用。

    还记得咱们之前看过的

    带when 必须是行级触发器吗

    改成

    create or replace  trigger trigger_sequence before insert on author for each row
    when(new.id is null)
    begin
    select author_id_sequence.currval into :new.id;
    end;

    还是出现错误

    未找到要求的from关键字。

    就是说咱们又该from关键字没有写。。。

    再次查看文档找出错误:

    注意咱们写完into_clause没有写from语句

    而sequence应该怎么写from呢?

    在文档sequence中找到

    The sequence number can also be referenced outermost SELECT of a query or subquery. For example:

    SELECT Order_seq.NEXTVAL FROM dual;
    

    原来使dual

    改成

    create or replace  trigger trigger_sequence before insert on author for each row
    when(new.id is null)
    begin
    select author_id_sequence.currval into :new.id from dual;
    end;

    触发器创建成功

    咱们insert author看看主键有没有增加吧

    结果又出现错误

    查文档

    ORA-08002: sequence string.CURRVAL is not yet defined in this session
    Cause: sequence CURRVAL has been selected before sequence NEXTVAL
    Action: select NEXTVAL from the sequence before selecting CURRVAL
    就是用CURRVAL之前要用NEXTVAL,
    然后查了一下NEXTVAL select author_id_sequence.currval from dual
    就好了。
    oracle 主键自增写完了,希望大家以后除了百度外,还有多参考文档来进行查询,毕竟嚼别人吃过的饭也不好吃。。。
    展开全文
  • 之前一直用mysql开发网站,最近换掉工作,碰到了oracle,发现了好多问题,oracle主键不支持自增。。搜了一下才发现要但是通过触发器(trigger)和序列(sequence)可以实现。一新建一个测试表-- Create tablecreate ...
  • 1. 创建表 sql CREATE TABLE MY_DEMO( "ID" NUMBER NOT NULL, "MCC" VARCHAR2(255), PRIMARY KEY ("ID") ) .... 注意 : 如果报 表或视图不存在,则在表名前加模式名 ,格 式 为 “模式名”.“表名”
  • Oracle创建主键自增表(sql语句实现)及触发器应用1、创建表复制代码 代码如下:createtableTest_Increase(useridnumber(10)NOTNULLprimarykey,/*主键,自动增加*/usernamevarchar2(20));2、创建自动增长序列复制代码 ...
  • 一、前言这里记录一次将MySQL数据库中的表数据迁移到Oracle数据库中的全过程 ,使用工具 Navicat,版本 12.0.11操作环境及所用工具:mysql5.7oracle18cwindowsNavicat12.0.11idea二、开始移植点击 工具 ->...
  • 创建视图时插入自增id

    万次阅读 2016-09-19 16:23:12
    因为在实际工作中需要查询最大id,来求出总的数量,有时候由于数据的删除导致Max(id)求出的,与实际的数量是不符的,故想到用视图插入自增id来使Max(id)求出的即为总数。Create view view_MYAddId As select row_...
  • oracle主键自增1。用hibernate的方式第一步,建立表,并设置主键为tableName_id.第二步,建立sequence视图,sql语句如下create sequence tableName_idminvalue 1start with 1increment by 1nocyclenocache;其中,...
  • oracle
  • Oracle实现主键自增

    2019-01-23 10:04:14
    Oracle实现主键自增 因为oracle不存在mysql 的自增方法auto_increment,所以在Oracle中要实现字段的自增需要使用序列和触发器来实现字段的自增。关于oracle的安装和卸载可以看我上一篇文章。 第一步:创建序列 语法...
  • mysql和oracle插入的时候有一个很大的区别是,oracle支持序列做id,mysql本身有一个列可以做自增长字段,mysql在插入一条数据后,如何能获得到这个自增id的值呢?方法一:是使用last_insert_idmysql> SELECT LAST...
  • 如果表存在,则查询结果插入到表中: insert into table_1 select * from table_2;...如果新表要增加自增id,再输入以下命令: alter table table_1 add id int not null auto_increment primary ...
  • Oracle不能像MySQL那样设置主键自增,在写入数据的时候需要用 <序列+触发器>的方式使数据表的一列或多列实现自增。两者可在视图中创建,也可以使用sql语句创建。 1、创建序列 dual 的sql语句: create ...
  • Oracle中如何创建使用SEQUENCESOracle提供了sequence对象,由系统提供自增长的序列号,通常用于生成数据库数据记录的自增长主键或序号的地方.下面介绍一下关于sequence 的生成,修改,删除等常用的操作:1. 创建Sequence...
  • 数据库对象命名规范 数据库对象包括表、视图...主键(Primary key) pk_ 外键(Foreign key) fk_ Check约束(Check Constraint) ck_ Default 约束(Default Constraint) df_ 视图(View) vi_ 索引(Index) idx_ 存储过程(Stor
  • 将表t_uaer的字段ID设置为自增:(用序列sequence的方法来实现)----创建表Create table t_user( Id number(6),userid varchar2(20),loginpassword varchar2(20),isdisable number(6) );----创建序列 create sequence ...
  •  在oracle使用insert增加数据时,使用sequence自增时报错ORA-02287: 此处不允许序号  源代码: &lt;insert id="insert"&gt; INSERT INTO sysmgtdb.PUB_DICTTYPE(ID,CODE,NAME,VERSION,...
  • 第一种在oracle数据库中创建Sequences 和 Triggers(触发器)两个必须配合使用才可以生效。 举例:Sequences 的创建,可以使用plsql工具新建。 -- Create sequence create sequence CMU_SYSTEM_LOG_LOG_ID minvalue...
  • Oracle主键自增1.用hibernate的方式第一步、建立表,并设置主键为tableName_id;第二步、建立sequence视图,sql语句如下create sequence tableName_seq minvalue 1 start with 1 increment by 1 nocycle nocache;...
  • 视图添加主键(唯一列值)

    千次阅读 2010-05-09 11:39:02
    参考 http://topic.csdn.net/t/20050629/16/4112826.html   产生唯一值很简单 select newid() as 列名,* from 表   http://huamengxing.iteye.com/blog/120162 ...NEWID ,而Oracle中使用SYS_GUID ...
  • 背景Oracle数据库提供了sequence的特性,即用户可以通过create sequence seq语法创建一个sequence对象,用来生成一个单机或者RAC环境下,唯一,单调递增/递减,全局有序(RAC环境)的数字,极大的简化了应用的设计,...
  • 1 在表视图的列上创建。双击表视图,打开table properties ———&gt;columns ,双击要设置的列(显示列的序号的那个按钮,单击后,会显示横向的黑色箭头)。打开column properties 对话框。在 ‘general’ 项中...
  • --数据泵 EXPDP 导出工具的使用--=================================对于Oracle数据库之间的导入导出,可以使用Oracle提供的导入导出工具EXP/IMP来实现。EXP/IMP是Oracle早期提供的数据导入导出工具。在Oracle10g 中...
  • oracle创建视图触发器

    2021-05-05 09:03:19
    1.视图触发器语法 2.视图触发器作用使用视图触发器,可以实现对视图进行增删改时,同时对视图相关的表进行增删改。强行向视图中插入数据时会报如下错误: 3.视图触发器实例-- 创建视图触发器,实现向视图中插入数据...
  • oracle主键自增1。用hibernate的方式第一步,建立表,并设置主键为tableName_id.第二步,建立sequence视图,sql语句如下 create sequence tableName_seq minvalue 1 start with 1 increment by 1 nocycle ...
  • 5、oracle建表的时候同时创建主键,外键,注释,约束,索引1 --主键create table emp (id number constraint id_pr primary key ,name1 varchar(8));create table emp9 (id number ,name1 varchar(8) ,constraint ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 7,600
精华内容 3,040
关键字:

oracle视图自增主键