Go语言视频零基础入门到精通

admin

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

1. CREATE

复制代码

1. 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 (

1. SCHEMA

复制代码

), you must have the

1. CREATE

复制代码

1. 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

1. condition

复制代码

is

1. TRUE

复制代码

for an affected row, then

1. trigger_body

复制代码

runs for that row; otherwise,

1. trigger_body

复制代码

does not run for that row. The triggering statement runs regardless of the value of

1. condition

复制代码

.
The

1. condition

复制代码

can contain correlation names (see"referencing_clause ::="). In

1. condition

复制代码

, do not put a colon (:) before the correlation name

1. NEW

复制代码

,

1. OLD

复制代码

, or

1. 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 usingcorrelation names. The default correlation names are

1. OLD

复制代码

,

1. NEW

复制代码

, and

1. PARENT

复制代码

. To change the correlation names, use the

1. REFERENCING

复制代码

clause of the

1. CREATE

复制代码

1. TRIGGER

复制代码

statement (see"referencing_clause ::=").
行级触发器可以获得改行的数据，它整处理的，通过用相关名。默认的相关名是OLD,NEW,和PARENT.改变相关名用REFERENCING clause of the

1. CREATE

复制代码

1. TRIGGER

复制代码

。
对于咱们其实就是要拿到该插入行的id。
  If the trigger is created on a table or view, then

1. OLD

复制代码

and

1. NEW

复制代码

refer to the current row of the table or view, and

1. PARENT

复制代码

is undefined.
如果触发器在一个表被创建。则OLD和NEW参照该表或者视图的当前行，PARENT无定义。
对于咱们来说把触发器创建在author表上。接着咱们来看看OLD,NEW PARENT是什么

1. OLD

复制代码

,

1. NEW

复制代码

, and

1. 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

1. table_name

复制代码

1. %ROWTYPE

复制代码

, where

1. table_name

复制代码

is the name of the table on which the trigger is created (for

1. OLD

复制代码

and

1. NEW

复制代码

) or the name of the parent table (for

1. PARENT

复制代码

).
In the

1. trigger_body

复制代码

of a simple trigger or the

1. tps_body

复制代码

of a compound trigger, a correlation name is aplaceholder for a bind variable. Reference the field of a pseudorecord with this syntax:

1. :pseudorecord_name.field_name

复制代码

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

1. DROP TABLE Emp_log;
2. CREATE TABLE Emp_log (
3.   Emp_id     NUMBER,
4.   Log_date   DATE,
5.   New_salary NUMBER,
6.   Action     VARCHAR2(20));
8. Create trigger that inserts row in log table after EMPLOYEES.SALARY is updated:
10. CREATE OR REPLACE TRIGGER log_salary_increase
11.   AFTER UPDATE OF salary ON employees
12.   FOR EACH ROW
13. BEGIN
14.   INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)
15.   VALUES (:NEW.employee_id, SYSDATE, :NEW.salary, "New Salary");
16. END;
17. /
18. Update EMPLOYEES.SALARY:
20. UPDATE employees
21. SET salary = salary + 1000.0
22. 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

1. 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

1. SELECT

复制代码

1. INTO

复制代码

statement retrieves values from one or more database tables (as the SQL

1. SELECT

复制代码

statement does) and stores them in variables (which the SQL

1. 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

1. CREATE

复制代码

1. 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

1. CURRVAL

复制代码

pseudocolumn, which returns the current value of the sequence, or the

1. 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

1. SELECT

复制代码

of a query or subquery. For example:

1. 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: sequencestring.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 主键自增写完了，希望大家以后除了百度外，还有多参考文档来进行查询，毕竟嚼别人吃过的饭也不好吃。。。