[Order] In order to write the stored procedure in the work, I started the dream of Oracle programming. At the same time, I am ready to write learning notes for learning "Exquisite Oracle 10G PL / SQL Program" (Water Conservancy Hydropower). Can you write all these: Data Query Language - SELECTData Manipulation Language - INSERT, UPDATE, DELETETransactional Control Language - COMMIT, ROLLBACK, SAVEPOINTData Definition Language - CREATE TABLE, ALTER TABLE, DROP TABLEData Control Language - GRANT, REVOKE I have not answered this problem in an interview. I want to have embarrassment, no him.
PL / SQL can combine the SQL statement into blocks, once transfer, reduce network overhead, this is efficient, and there is no such good thing when using SQL Server in the day.
PL / SQL block structure
Declare
/ * Defined part * /
Begin
/ * Execution section * /
EXCEPTION
/ * Exception processing section * /
END; / * End tag, this is a semicolon * /
/ / * This is the end of the command line * /
Four large blocks
Anonymous block, named block, subroutine (process, function, package), trigger
Four major variable types
Scarra, Composites, Reference (Reference), LOB (Large Object)
Composite: PL / SQL Record, PL / SQL Table, Nested Table, Varray
Refer to: Robet Variables (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, and will not be dynamically changed in the future, only manually modified
v_num tbl.num% type; ß dynamic determines new types and lengths
Very like Delphi: 1 variable assignment: = 2 Record Record
Note when writing SQL statements in the command line: Single ends; the module ends to use / to display your query
Do not repeat the query: SELECT DISTINCT * FROM A_TBL
Processing NULL: NVL (expr1, expr2) Returns EXPR2 if EXPR1 is NULL; if not, return EXPR1 type to match
Delete the table data and release space: Truncate Table
Oracle does not allow other users to read dirty data (not submitted transactions) to ensure that database data read consistency
Unlock, make sure that the operation of the file at the same time can only have one person
SavePoint is used to cancel some transactions, and all save points are automatically deleted after the transaction is over.
Read-only business: SET Transaction Read Only
Or: exec dbms_transaction.read_only
Group function
Max, Min, AVG, SUM, Count, Variance, STDDEV (standard deviation)
Group By is used to group statistics for query results - such as the maximum value of each group
Having is used to limit the group display result - such as a maximum of less than 2800
ROLLUP generates a horizontal small statistically based on the original statistics
Cube generates portrait small statistics on the basis of the original statistical results
Oracle process control statement
IF statement if Then elsif kil1en else endiff;
Case statement (9i) Case WHEN THEN ELSE END CASE
Basic cycle
Loop
EXIT [WHEN]; END
Loop
;
While loop while
Loop
End
Loop
;
For cycle for _v in [Reverse] Lo..hi loop endloP
;
