http://dev.9cbs.net/develop/Article/49/Article/49/49678.shtm [Sequence] In order to write a stored procedure in the work. 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 / * Define section * / becom / * Execute section * / exception / * Exception section * / END; / * End tag, a semicolon * / / * This is the end of the command line * /
Four block types anonymous block, named block, subroutine (process, function, package), trigger four major variable type scales (SCALAR), Composites, Reference (Large Object) Composite: PL / SQL record, PL / SQL table, nested table, Varray Reference: Cursor variable (REF OBJ_TYPE) LOB: Internal LOB (Clob, Blob, NClob), External LOB (BFile) Declare V_Num Number 6, 2); the ß variable is deadline, and cannot change dynamic changes in the future, only manually modify 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 dirt data (not submitted transactions) to ensure that the database data reads consistency, ensuring that only one person on the file at the same time.
SavePoint is used to cancel some transactions, and all save points are automatically deleted after the transaction is over.
Read-only transaction: set transaction read only; or: exec dbms_transaction.read_only
Group functions 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 is used to limit packet display results - such as the largest The value of less than 2800 rollup generates a horizontal small statistical Cube on the basis of the original statistical result. Based on the original statistics.
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 end
Loop
;
