|
|
Lesson 11-13
[php]
Lession11 Manipulating Data
* You can place a subquery in the place of the table name, essentially the same way a view is used.
For example, UPDATE (SELECT * FROM s_dept) SET id = 50 WHERE id = 60;
* Common errors that can occur during user input:
1. Mandatory value missing for a NOT NULL column
2. Duplicate value violates uniqueness constraint
3. Foreign key constraint violated
4. CHECK constraint violated
5. Datatype mismatch
6. Value too wide to fit in column
* The date also contains time information, the default time is midnight (00:00:00). If the RR format is set, the century may not be the current one.
* The SQL*Plus SET VERIFY command lists the substitution prior to executing the statement.
* For date and character values, the ampersand(& and the variable name are enclosed in single quotation marks.
* Do not prefix the SQL*Plus substitution parameter with the ampersand(& in the ACCEPT command.
* In general, use the primary key to identify a single row. -- 非主键条件可能引起多记录更新
* You can use the TRUNCATE command to quickly remove all rows from a table or cluster. Removing rows with the TRUNCATE command is faster than removing them with the DELETE command for the following reasons. The TRUNCATE command is a data definition language command and generates no rollback information. It will be covered in a later lesson. Truncating a table does not fire the table’s DELETE triggers. --TRUNCATE是比DELETE更快的全表删除的命令，但它是DCL命令，无法回滚，它也不会出发表级的DELETE TRIGGER
* If the referential integrity constraint contains the ON DELETE CASCADE option, then the selected row and its children are deleted from their respective tables.
* A transaction begins when the first executable SQL command is encountered and terminates when one of the following occurs:
A COMMIT or ROLLBACK command is issued.
A DDL command, such as CREATE, or DCL command is issued.
Certain errors are detected, such as deadlocks.
The user exits SQL*Plus.
A machine fails or the system crashes.
* Implicit Transaction Processing
Status Circumstance
Automatic commit DDL command or DCL command is issued.
Normal exit from SQL*Plus, without explicitly issuing COMMIT or ROLLBACK.
Automatic rollback Abnormal termination of SQL*Plus, or system failure.
* Data manipulation operations primarily affect the database buffer; therefore, the previous state of the data can be recovered.
* Other user cannot view the results of the data manipulation operations for the current user. Oracle7 institutes read consistency to ensure that each user sees data as it existed at the last commit. -- 保持读一致性，因此只有本用户可以看到自己更改而未提交的数据
* Following a COMMIT, The locks on the affected rows are released; All savepoints are erased.
* If you create a second savepoint with the same name as an earlier savepoint, the earlier savepoint is deleted.
* Manually acquire locks on the database tables by executing a LOCK TABLE command, the SELECT command with the FOR UPDATE clause, or the SET TRANSACTION command with the READ ONLY option.
* The Oracle7 Server implements locks on data to provide data concurrency in the database. Those locks are released when certain events occur (such as a system failure) or the transaction is completed.
* The 'Roll' command are not save in SQL buffer, but the 'Rollback' saved
* An automatic rollback occurs when a system crashes.
-----------------------------------------------
Lession12 Altering Tables and Constraints
* RENAME、TRUNCATE、COMMENT都是DDL语句, 执行DDL命令, 发生autocommit，不论该语句是否成功（语法错误的情况除外）. TRUNCATE also releases the storage space.
* ALTER TABLE ADD columns: 新增的列总是表中的最后一列 The new column becomes the last column.
* If a table already contains rows when a column is added, then all of the fields in the new column are initially NULL. You can define a NOT NULL column only if the table contains no rows because data cannot be specified for existing rows at the same time that the
column is added.
* Cannot drop or rename a column in the table (注意：8i中可以drop columns)
* A change to the default value of a column only affects subsequent insertions to the table.
* You can add, drop, enable, or disable a constraint, but you cannot modify its structure.
* If you enable a UNIQUE or PRIMARY KEY constraint, a UNIQUE or PRIMARY KEY index is automatically created.
* When use DROP or DISABLE constraints, you can use the CASCADE clause drop or disables dependent integrity constraints.
* DROP TABLE table [CASCADE CONSTRAINTS]; The CASCADE CONSTRAINTS option will also remove dependent referential integrity constraints. Only the creator of the table or a user with the DROP ANY TABLE privilege can remove a table.
* You must be the owner of the object (table, view, sequence, or synonym) you rename.
* You can add a comment of up to 2000 bytes about a column, table, view, or snapshot by using the COMMENT command. The comment is stored in he data dictionary and can be viewed in one of the following data dictionary views in the COMMENTS column:
ALL_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
? Additional options for the above syntax are USING INDEX, EXCEPTIONS INTO, and ALL TRIGGERS clauses.
-----------------------------------------------
Lession13 Creating Sequences
* If the INCREMENT BY value is negative, the sequence will descend. Additionally, NOMAXVALUE then specifies a maximum value of -1 and NOMINVALUE sets a minimum value of -(10^26 ).
* ORDER | NOORDER options is only relevant with the Parallel Server option.
* If sequence values are cached, they will be lost if there is a system failure.
* Create Sequence 的缺省值：increament by 1 / start with 1 / nomaxvalue(10^27) / nominvalue(1) / nocycle / nocache(20)
* Do not use the CYCLE option if the sequence is used to generate primary key values.
* You can also confirm the settings of the sequence by selecting from the data dictionary’s USER_SEQUENCES table.
* LAST_NUMBER column is the last number generated by the sequence if you do not use the CACHE option, which means that it is the value you acquire when you use a sequence.NEXTVAL expression. If you use the CACHE OPTION, LAST_NUMBER displays the next value after all the numbers in the cache are used.
* NEXTVAL must be used to generate a sequence number in the current user’s session before CURRVAL can be referenced.
* You can use NEXTVAL and CURRVAL in
The SELECT list of a SELECT statement that is not part of a subquery.
The SELECT list of a subquery in an INSERT statement.
The VALUES clause of an INSERT statement.
The SET clause of an UPDATE statement.
You cannot use NEXTVAL and CURRVAL in
A SELECT list of a view.
A SELECT statement with the DISTINCT keyword.
A SELECT statement with the GROUP BY, HAVING, or ORDER BY clauses.
A subquery in a SELECT, DELETE, or UPDATE statement.
A DEFAULT expression in a CREATE TABLE or ALTER TABLE command.
* 产生序列跳号的原因：
If you roll back a command containing a sequence, the number is lost.
If the sequence caches values in the memory, then those values are lost if the system crashes.
Because the same sequence can be used for multiple tables. If this occurs, each table can contain gaps in the sequential numbers.
* Only if the sequence was created with NOCACHE, view the next available sequence value by querying the USER_SEQUENCES without incrementing it
* You must own or you have the ALTER privilege for the sequence in order to modify it.
* You must be the owner of the sequence or have the DROP ANY SEQUENCE privilege to remove it.
* 不能否在plsql中直接赋值给变量：a := sequence.NEXTVAL, 必须使用select sequence.NEXTVAL from dual;
* 在同一个session中，必须在使用NEXTVAL之后才能使用CURRVAL
-- end ---
[/php] |
|
楼主: easyfree
IP卡
狗仔卡
发表于 2001-11-13 15:22
显身卡
楼主
