Hey, Oracle, "1."

Sequence
In order to write stored procedures in the work,. At the same time, I'm going to write a study note on "Mastering Oracle 10g Pl/sql Programming" (Water Conservancy and hydropower).

Can you write all this:
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 a job interview, I failed to answer such questions. Wanted only to be ashamed, without him.

Pl/sql can combine SQL statements into chunks, a single transmission, and reduce network overhead, which is not as good as using SQL Server in the past.



PL/SQL block Structure

DECLARE

/* Definition Part * *

BEGIN

/* Executive Part * *

EXCEPTION

/* Exception Handling Section * *

End; /* block end tag, here is a semicolon * * *

///////* This is the Terminator on the command line. *



Four-block type

Anonymous blocks, named blocks, subroutines (procedures, functions, packages), triggers

Four variable types

Scalar (Scalar), composite (composite), reference (Reference), LOB (Large Object)

Composite:pl/sql Records, pl/sql tables, nested tables, Varray

References: 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); ß variable is dead length, cannot be changed dynamically in future, can only be modified manually

V_num Tbl.num%type; ß Dynamic determination of new types and lengths



Looks like delphi:1. Variable assignment: = 2 recording record



Note When writing SQL statements on the command line: single end to use; The end of the module to use/to display your query



Do not repeat the query: SELECT DISTINCT *from a_tbl



Process NULL:NVL (EXPR1,EXPR2) returns EXPR2 if EXPR1 is null; expr1 both types to match if not



Delete table data and free space: TRUNCATE table



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

Lock to ensure that only one person can operate on the file at the same time



SavePoint is used to cancel part of a transaction and automatically delete all save points after the transaction ends.



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, each group of maximum values

Having is used to restrict the display of grouped displays-for example, the maximum value is less than 2800

Rollup on the basis of the original statistic results to generate horizontal small statistics

Cube generates longitudinal small statistics on the basis of original statistic results



Oracle's Process Control statements

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;