Oracle Advanced Section Content

1.pl/sql Basic Statement

DECLARE

BEGIN

END;

/

Looping statements

DECLARE

I Number (2): = 1;

BEGIN

While i<100

LOOP

i:=i+1;

END LOOP;

END;

/

DECLARE

I Number (2): = 1;

BEGIN

LOOP

EXIT when i<100;

i:=i+1;

END LOOP;

END;

/

Judgment statement

DECLARE

I Number (2): = 1;

BEGIN

IF I>2 Then

....

Elsif I>1 Then

....

ELSE

....

END IF;

END;

/

2. Cursor (with or without parameters, refer to the Scott table)

DECLARE

cursor [cursor name] is a SELECT ename from EMP;

PNAME EMP. Ename%type;

BEGIN

OPEN [cursor name]

LOOP

FETCH [] into PNAME;

EXIT when [cursor name]%notfound;

END LOOP;

END;

/

3. Stored Procedures (procedure)

CREATE OR REPLACE PROCEDURE Pp_pro

As

PNAME VARCHAR2 (10);

BEGIN

SELECT ename into Pp_pro;

END;

4. Storage functions

CREATE OR REPLACE FUNCTION pp_fun RETURN VARCHAR2

As

FNAME varchr2:= ' HELLO ';

BEGIN

RETURN FNAME;

END;

5. Trigger (Trigger)

CREATE OR REPLACE TRIGGER Oop_tri

Before/after

Insert/delete/update on EMP

DECLARE

BEGIN

END;

Summarize:

PLSQLis dedicated to Oracle server, on the basis of SQL, added some procedural control statements, called Plsql 过程化包括有：类型定义，判断，循环，游标，异常或例外处理。。。 Plsql emphasizing the process 因为SQL是第四代命令式语言，无法显示处理过程化的业务，所以得用一个过程化程序设计语言来弥补SQL的不足之处， SQL and plsql are not substitution relationships, they are bridging relationshipsCURSOR 类似于JDBC中的ResultSet对象的功能，从上向下依次获取每一记录的内容 PROCEDURE (stored procedure): Use Oracle syntax in advance, write a piece of program with business function, long-term storage in Oracle server, for client and program language remote access （1）PLSQL每次执行都要整体运行一遍，才有结果(2) Plsql can not be encapsulated, long-term storage in the Oracle server （3）PLSQL不能被其它应用程序调用，例如：AVA程序调用：preparedStatement-->CallableStatement

What exactly is the relationship between stored procedures and plsql?
The stored procedure is an aspect of plsql application, and Plsql is the basis of the stored procedure, that is, the stored procedure must use Plsql

How the stored procedure is called:

• Call stored procedure One, exec stored procedure
• Call stored procedure mode two, Plsql
• Call stored procedure method Three, Java program

function (stored functions)

声明：适合不是强行要你使用，只是优先考虑 什么情况下【适合使用】存储过程？什么情况下【适合使用】存储函数？Essentially no difference. Just a function like: Only one variable can be returned with a limit. A stored procedure can return more than one. Functions can be embedded in SQL and can be called in Select, and stored procedures do not work. The essence of execution is the same.
There are many function limitations, such as the inability to use temporary tables and only table variables. There are also some functions that are not available, and so on. and stored procedures are relatively less restrictive
1. In general, the function of the stored procedure implementation is a bit more complex, and the function implementation of the function is relatively strong.
2. Parameters can be returned for stored procedures, and functions can only return values or table objects.
3. The stored procedure is typically performed as a separate part (exec execution), and the function can be invoked as part of a query statement (select Call), since the function can return a Table object, so it can be located in the query statement after the FROM keyword.
4. When the stored procedure and function are executed, SQL Manager will go to the procedure cache to fetch the corresponding query statement, and if there is no corresponding query in the procedure cache, SQL Manager compiles the stored procedures and functions.          什么情况【适合使用】过程函数，什么情况【适合使用】SQL？ 【适合使用】过程函数：          》需要长期保存在数据库中          》需要被多个用户重复调用          》业务逻辑相同，只是参数不一样          》批操作大量数据，例如：批量插入很多数据 【适合使用】SQL：          》凡是上述反面，都可使用SQL          》对表，视图，序列，索引，等这些还是要用SQL                  -------------------------------------------------------------------------------------触发器 oracle常用命令: hostcls 清屏 rollback 回滚 后必须执行 commit 提交 show recyclebin;查看回收站 flashback table emp to before drop;--闪回 drop table emp purge;--彻底删除表   什么是触发器【Trigger】？ 不同的DML(SELECT/UPDATE/DELETE/INSERT)操作,触发器能够进行一定的拦截,符合条件的操作,才能操作基表, 否则不能操作基表,类似于javaweb中的Filter,Struts2的Interceptor 为什么要用触发器？   如果没有触发器,那么DML所有操作,均可无限制的操作基表

Optimization of the database

1) Database design aspects:
A. To optimize the query, avoid full-table scanning as far as possible, and first consider indexing on the columns involved in where and order by.
B. You should try to avoid null values in the WHERE clause, otherwise it will cause the engine to discard full table scans using the index, such as: Select ID from t where num is null you can set the default value of 0 on NUM to ensure that the NUM column in the table does not have a null value , and then query: Select ID from t where num=0

C. Not all indexes are valid for queries, and SQL is optimized for queries based on the data in the table, and when there is a large number of data duplication in the index column, the query may not take advantage of the index, as there are fields in the table Sex,male, female almost half, So even if you build an index on sex, it doesn't work for query efficiency.

D. The index is not the more the better, although the index can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and UPDATE, because the INSERT or update when the index may be rebuilt, so how to build the index needs careful consideration, depending on the situation. The number of indexes on a table should not be more than 6, if too many you should consider whether some of the indexes that are not commonly used are necessary.

E. Avoid updating the index data columns as much as possible, because the order of the indexed data columns is the physical storage order of the table records, which can consume considerable resources once the column values change to make the order of the entire table record. If the application needs to update the index data columns frequently, you need to consider whether the index should be built as an index.

F. Use numeric fields as much as possible, and if fields with numeric information are not designed as character types, this can degrade query and connection performance and increase storage overhead. This is because the engine compares each character in a string one at a time while processing queries and joins, and it is sufficient for a numeric type to be compared only once.

G. Use Varchar/nvarchar instead of Char/nchar as much as possible, because the first variable-length field has a small storage space and can save storage space, and secondly for queries, the search efficiency in a relatively small field is obviously higher.

H. Try to use table variables instead of temporary tables. If the table variable contains a large amount of data, be aware that the index is very limited (only the primary key index).

I. Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.

J. Temporary tables are not unusable, and they can be used appropriately to make certain routines more efficient, for example, when you need to repeatedly reference a dataset in a large table or a common table. However, for one-time events, it is best to use an export table.

K. When creating a temporary table, if you insert a large amount of data at one time, you can use SELECT INTO instead of CREATE table to avoid causing a large number of logs to increase speed, and if the amount of data is small, create table and insert to mitigate the resources of the system tables.

L. If a temporary table is used, be sure to explicitly delete all temporary tables at the end of the stored procedure, TRUNCATE table first, and then drop table, which avoids longer locking of the system tables.

2) SQL statement aspect:

A. You should try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for a full table scan using the index.

B. You should try to avoid using or in the WHERE clause to join the condition, otherwise it will cause the engine to discard full table scans using the index, such as: Select ID from t where num=10 or num=20 can query: Select ID from t WH Ere num=10 UNION ALL select IDs from T where num=20

C. In and not is also used with caution, otherwise it will cause a full table scan, such as: Select ID from t where num in (three-to-three) for consecutive values, can be used between do not use in the: Select ID from t where Num between 1 and 3

D. The following query will also cause a full table scan: Select ID from t where name like '%abc% '

E. If you use a parameter in the WHERE clause, it also causes a full table scan. Because SQL resolves local variables only at run time, the optimizer cannot defer the selection of access plans to run time; it must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is still unknown and therefore cannot be selected as an input for the index. The following statement will perform a full table scan: Select ID from t where [email protected] can be changed to force query using index: SELECT ID from T with (index name) where [email Protec Ted

F. Try to avoid expression of the field in the Where clause, which will cause the engine to discard the use of the index for a full table scan. For example: Select ID from t where num/2=100 should be changed to: Select ID from t where num=100*2

G. You should try to avoid function operations on the fields in the WHERE clause, which will cause the engine to discard the full table scan using the index. such as: Select ID from t where substring (name,1,3) = ' abc ' –name with ABC start ID select ID from t where DATEDIFF (day,createdate, ' 2005-11- 30′) =0– ' 2005-11-30 ' generated ID should be changed to: Select ID from t where name like ' abc% ' select ID from t where createdate>= ' 2005-11-30′a nd createdate< ' 2005-12-1′

H. Do not perform functions, arithmetic operations, or other expression operations on the left side of the "=" in the WHERE clause, or the index may not be used correctly by the system.

I. Do not write meaningless queries, such as the need to generate an empty table structure: Select Col1,col2 into #t from T where 1=0 such code will not return any result set, but will consume system resources, should be changed to this: CREATE TABLE #t (...)

J. Many times replacing in with exists is a good choice: Select num from a where num in (select num from B) is replaced with the following statement: Select Num from a where exists ( Select 1 from b where num=a.num)

K. Do not use SELECT * from t anywhere, use a specific field list instead of "*", and do not return any fields that are not available.

L. Avoid using cursors as much as possible because cursors are inefficient and should be considered for overwriting if the cursor is manipulating more than 10,000 rows of data.

M. Try to avoid returning large amounts of data to the client, and if the amount of data is too large, you should consider whether the corresponding requirements are reasonable.

N. Try to avoid large transaction operations and improve the system concurrency capability.

3) Java aspect: key content

A. Create as few objects as possible.

B. Reasonable positioning of the system design. A large number of data operations, and a small number of data operations must be separate. A lot of data manipulation, certainly not the ORM framework is fixed. ，

C. Manipulating data using a JDBC link database

D. Control the memory, let the data flow, not all read to the memory and processing, but the edge of reading edge processing;

E. Reasonable use of memory, some data to be cached

Oracle Advanced Section Content