| |
Oracle database, some basic grammatical structure, the contents of the square brackets is optional
Braces are required
1PL / SQL block structure
DECLARE
/ *
* Declaration section - define constants, variables, complex data types, cursors, user-defined exception
* /
BEGIN
/ *
* Operative --PL / SQL statements and SQL statements
* /
EXCEPTION
/ *
* Exception handling section - running exception handling
* /
END;
/ * End block tag * /
2 Create a sequence
1 CREATE SEQUENCE [schema.] Sequence_name
2 [{INCREMENT BY | START WITH} integer
3 | {MAXVALUE integer | NOMAXVALUE}
4 | {MINVALUE integer | NOMINVALUE}
5 | {CYCLE | NOCYCLE}
6 | {CACHE integer | NOCACHE}
7 | ...
8 ]...
9 ;
schema: model, that user name
sequence_name: sequence name
INCREMENT BY: defined sequence of steps, if omitted, the default is 1, if negative, represents the value of an Oracle sequence is decreasing in accordance with this step.
START WITH: The initial value of the defined sequence, the default is 1.
MAXVALUE: the maximum sequence generator can produce. NOMAXVALUE is the default option, there is no maximum value on behalf of the definition.
MINVALUE: sequence generator to produce the minimum. NOMINVALUE is the default option, there is no minimum on behalf of the definition.
CYCLE and NOCYCLE: When the generator's sequence value reaches the limit value is circulating. CYCLE behalf of the loop, NOCYCLE representatives do not cycle. If the cycle is maximum when increasing sequence, the cycle to the minimum, the minimum is 1. For when descending sequence reaches a minimum, is recycled to the maximum. If you do not cycle, after the limit is reached, continue to generate new value error occurs.
CACHE: define the size of the memory block to store the sequence, the default is 20, the equivalent of pre-loaded. A partial sequence of the buffer count for quicker obtain sequence values, can improve the performance of the sequence, but the cache option may cause data loss. NOCACHE means no buffer
3 Delete sequence
1 DROP SEQUENCE [. Schema] sequence_name;
4 Modify the sequence
1 ALTER SEQUENCE [schema.] Sequence_name
2 {INCREMENT BY integer
3 | {MAXVALUE integer | NOMAXVALUE}
4 | {MINVALUE integer | NOMINVALUE}
5 | {CYCLE | NOCYCLE}
6 | {CACHE integer | NOCACHE}
7 | ...
8 } ...
9 ;
Note that you can not modify the initial value of the sequence, otherwise they will be reported ORA-02283. If you need to modify the initial value, delete and re-create the sequence to set the initial value of the sequence.
Complex data type
5.1. Records (Record)
1 TYPE record_name IS RECORD (
2 varable1 data_type1 [NOT NULL] [: = default_value],
3 varable2 data_type2 [NOT NULL] [: = default_value],
4 ......,
5 varablen data_typen [NOT NULL] [: = default_value]
6);
5.2 Table (TABLE)
5.2.1. Index table
1 TYPE table_name IS TABLE OF element_type [NOT NULL]
2 INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2];
5.2.2 nested table
1 TYPE type_name IS TABLE OF element_type;
5.3. Array (VARRAY)
1 TYPE varray_name IS VARRAY (size) OF element_type [NOT NULL];
6 explicit cursor
6.1 Declare / Define a cursor
1 CURSOR cursor_name
2 [(parameter_dec [, parameter_dec] ...)]
3 [RETURN datatype]
4 IS
5 select_statement;
6.2 Open the cursor
1 OPEN cursor_name [(cursor_parameter [[,] actual_cursor_parameter] ...)]
6.3 Reading data
1 FETCH {cursor | cursor_variable |: host_cursor_variable}
2 {into_clause | bulk_collect_into_clause [LIMIT numeric_expression]};
6.4 Close Cursor
1 CLOSE cursor_name;
7 cursor FOR loop
1 FOR index_variable IN cursor_name [(value [, value] ...)] LOOP
2 - Cursor handling statements
3 END LOOP;
8 Structure Condition
1. Simple IF Structure
1-- Simple IF Structure
2 IF THEN
3 statement is executed to meet the conditions
4 END IF;
2. IF-ELSE structure
1 - IF-ELSE structure
2 IF THEN
3 statement is executed to meet the conditions
4 ELSE
5 statement execution condition is not satisfied
6 END IF;
3 Multi-IF
1-- multiple IF
2 IF THEN
3 sentence 1 to meet the conditions of execution
4 ELSIF THEN
2:00 5 satisfies the condition statement executed
6 ELSIF THEN
7 statement that satisfies Condition 3 is executed when
8 ELSE
9 statements do not satisfy the condition 1,2,3 executed
10 END IF;
Note: ELSIF can not be written ELSEIF
9 CASE
Syntax
1 CASE conditional expression
2 WHEN conditional expression 1 THEN
3 sentence 1
4 WHEN 2 THEN conditional expression
5 sentence 2
6 ......
7 WHEN condition n THEN expression results
8 statement n
9 [ELSE conditional expression]
10 END CASE;
Syntax two
1 CASE
2 WHEN conditional expression 1 THEN
3 sentence 1
4 WHEN conditional expression 2 THEN
5 sentence 2
6 ......
7 WHEN condition n THEN expressions
8 statement n
9 [ELSE statement]
10 END CASE;
10 loop structure
Simple Cycle
1 LOOP
2 loop statements;
3 [EXIT WHEN ]
4 END LOOP;
WHILE loop
1 WHILE LOOP
2 loop statements;
3 END LOOP;
FOR loop
1 [<< label >>]
2 FOR loop counter IN [REVERSE] LOOP upper limit ..
3 loop statements;
4 END LOOP [<< label >>];
Exception handling is usually placed in the rear of 11, the grammatical structure of PL / SQL procedures for
1 EXCEPTION
2 WHEN {exception [OR exception] ... | OTHERS}
3 THEN statement [statement] ...
11.1 Exception handling non-predefined
Non-predefined exceptions have the error number without a name, it is a way to deal with: their definition of a name, the error number is bound to capture the wrong name. Dealing with such exceptions, it must first non-predefined Oracle exception defined.
Such as:
1 myexcp EXCEPTION;
Then use EXCEPTION_INIT statement with the standard ORACLE error link, such as:
2 PRAGMA EXCEPTION_INIT (myexcp, -02292);
Description: ORA-02292 is a violation of the integrity constraint error code.
11.2-defined exceptions
1 exception name EXCEPTION;
2 PRAGMA EXCEPTION_INIT (name of the exception, error number);
11.3 thrown
1 RAISE exception name
RAISE_APPLICATION_ERROR procedure can be used to create user-defined error messages, you can perform partial use and exception handling part
1 RAISE APPLICATION_ERROR (error number, error description);
11.4 Handling exceptions
1 1 EXCEPTION
2 2 WHEN {exception [OR exception] ... | OTHERS}
3 3 THEN statement [statement] ...
12. FORALL
Use FORALL, you can be sent to multiple batch DML SQL engine to perform to minimize the overhead of the context of the interaction.
1 FORALL index_name IN
2 {lower_bound .. upper_bound
3 | INDICES OF collection_name [BETWEEN lower_bound AND upper_bound]
4 | VALUES OF index_collection
5}
6 [SAVE EXCEPTIONS] dml_statement;
index_name: without a declaration of an identifier, as a collection subscript use.
lower_bound .. upper_bound: numeric expressions to specify a range of valid index numbers lower and upper limits. The analytical expression only once.
INDICES OF collection_name: used to point to the actual subscript sparse array. Skip unassigned elements, for example, DELETE elements, NULL values are also considered.
VALUES OF index_collection_name: the value of the collection as a subscript, and the type of the set value can only be PLS_INTEGER / BINARY_INTEGER.
SAVE EXCEPTIONS: Optional keyword, even if some DML statements fail until FORALL LOOP completed only exception is thrown. You can view information about abnormal use SQL% BULK_EXCEPTIONS.
dml_statement: static statements, such as: UPDATE or DELETE; or dynamic (EXECUTE IMMEDIATE) DML statements
13 Stored Procedures
1 CREATE [OR REPLACE] PROCEDURE [schema.] Procedure_name
2 [(parameter_declaration [, parameter_declaration] ...)]
3 [invoker_rights_clause]
4 {IS | AS}
5 {[declare_section] body | call_spec | EXTERNAL};
procedure_name: process name.
parameter_declaration: Parameter Declaration
The following format
1 parameter_name [[IN] datatype [{: = | DEFAULT} expression]
2 | {OUT | IN OUT} [NOCOPY] datatype
IN: input parameters.
OUT: output parameters.
IN OUT: input and output parameters.
invoker_rights_clause: This process uses who have permission to run
or it is used as the beginning of a PL / SQL block
declare_section: declaration section.
body: the process block body, the operative part.
Generally only in the confirmation process is procedure_name new process or to the process of updating only used keyword OR REPALCE
13 Creating Functions
1 CREATE [OR REPLACE] FUNCTION [schema.] Function_name
2 [(parameter_declaration [, parameter_declaration] ...)
3]
4 RETURN datatype
5 [{invoker_rights_clause
6 | DETERMINISTIC
7 | parallel_enable_clause
8 | RESULT_CACHE [relies_on_clause]
9 }...
10]
11 {{AGGREGATE | PIPELINED} USING [. Schema] implementation_type
12 | [PIPELINED] {IS | AS} {[declare_section] body
13 | call_spec
14 | EXTERNAL
15}
16};
14 Building Package Specification
1 CREATE [OR REPLACE] [EDITIONABLE | NONEDITIONABLE]
2 PACKAGE [schema.] Package_name
3 [invoker_rights_clause]
4 {IS | AS} item_list_1 END [package_name];
package_name: package name.
invoker_rights_clause: Whose use rights to run
A list of common components package declaration: item_list_1
1 {type_definition - Data Type
2 | cursor_declaration - Cursor
3 | item_declaration - variables, constants, etc.
4 | function_declaration - Functions
5 | procedure_declaration - Process
6}
7 [{type_definition
8 | cursor_declaration
9 | item_declaration
10 | function_declaration
11 | procedure_declaration
12 | pragma
13}
14] ...
15 established enclaves
1 CREATE [OR REPLACE] PACKAGE BODY [schema.] Package_name
2 {IS | AS}
3 BEGIN statement [statement | pragma] ...
4 [EXCEPTION exception_handler [exception_handler] ...]
5 [initialize_section]
6 END [package_name];
The use of dynamic SQL EXECUTE IMMEDIATE statement
1 EXECUTE IMMEDIATE dynamic_sql_stmt
2 [{into_clause | bulk_collect_into_clause} [using_clause]
3 | using_clause [dynamic_returning_clause]
4 | dynamic_returning_clause
5];
dynamic_sql_stmt: on behalf of a SQL statement or string expression a PL / SQL block.
into_clause: variable is used to store the selected field values or selected rows. Formats such as:
INTO {variable [, variable] ... | record)
using_clause: SQL or PL / SQL string included for parameter binding placeholders, this clause placeholder bind values can also be used to return a value. Bind_argument input parameter is an expression, its value will be input (IN mode) or output (OUT mode) or O (IN OUT mode) to dynamic SQL statements or PL / SQL block. Bind_argument output parameter is a variable that holds a dynamic SQL can return values. Format as
dynamic_returning_clause: refers USING [IN | OUT | IN OUT] bind_argument
Ming used to store the return value of a variable or record
[[,] [[IN | OUT | IN OUT] bind_argument] ...
Formats such as:
{RETURNING | RETURN} {into_clause | bulk_collect_into_clause} |
|
|
|