Hi Oracle [1]

[Sequence]
To write a stored procedure at work, I started my dream Oracle programming. At the same time, I am going to write the learning notes for the book "proficient in Oracle 10g PL/SQL programming" (Water Conservancy and hydropower.

Can you write all these information:
Data Query Language -- select
Data manipulation language -- insert, update, delete
Transactional Control Language -- commit, rollback, savepoint
Data Definition Language -- Create Table, alter table, drop table
Data Control Language -- Grant, revoke
In an interview, I was not able to answer such questions. I want to be ashamed and have no choice.

PL/SQL can combine SQL statements into blocks for one transmission, reducing network overhead. This is not a good option for SQL Server.

 

PL/SQLBlock Structure

Declare

/* Definition part */

Begin

/* Execution part */

Exception

/* Exception Handling part */

End;/* indicates the end of a block. A semicolon exists here */

// * This is the end character in the command line */

 

Four block types

Anonymous block, named block, subroutine (process, function, package), trigger

Four variable types

Scalar, composite, reference, lob (large object)

Composite: PL/SQL records, PL/SQL tables, nested tables, varray

Reference: cursor variable (ref cursor), object type variable (ref obj_type)

Lob: Internal lob (clob, blob, nclob), external lob (bfile)

Declare

V_num number (6, 2); The limit variable is fixed to the length and cannot be changed dynamically in the future. It can only be modified manually.

V_num TBL. Num % type; Specify dynamically determines the new type and length

 

Like Delphi: 1 variable value assignment: = 2 Record

 

Note the following when writing SQL statements in the command line: to use a single end; To use a module end, use/to display your query.

 

Query without repeated display: Select distinct * From a_tbl

 

Processing NULL: nvl (expr1, expr2) If expr1 is null, expr2 is returned; if not, expr1 is returned to match the two types.

 

Delete table data and release space: truncate table

 

Oracle does not allow other users to read dirty data (transactions not committed) to ensure read consistency of database data.

Lock to ensure that only one person can perform operations on files at the same time

 

Savepoint is used to cancel some transactions. After the transaction is completed, all save points are automatically deleted.

 

Read-Only transaction: Set transaction read only;

Or: exec dbms_transaction.read_only

 

Grouping Functions

Max, Min, AVG, sum, Count, variance (variance), stddev (standard deviation)

Group by is used to group statistics on query results-for example, the maximum value of each group

Having is used to limit the display results of groups-for example, the maximum value is less than 2800.

Rollup generates small horizontal statistics based on the original statistical results

Cube generates vertical small statistics based on the original statistical results

 

OracleFlow Control statement

If statement

If then

Elsif then

Else

End if;

 

Case Statement (9i)

Case

When then

Else

End case;

 

Basic cycle

Loop

Exit [when];

End Loop ;

 

While Loop

While Loop

End Loop ;

 

For Loop

For _ v in [reverse] lo... Hi Loop

End Loop ;