Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle database, some basic grammatical structures     - Linux and Windows virtual machine shared folders ---- Linux install VMware tools (Linux)

- Simple security measures to reinforce the Linux kernel (Linux)

- Docker in the development and practice of IFTTT (Server)

- Ubuntu 14.04 installed Nvidia CUDA 7.5 and build Python Theano deep learning development environment (Linux)

- Detailed Linux su command to switch users Mistakes (Linux)

- PXE installation of Linux servers (Server)

- Java executable file to read information from a database copy (Programming)

- Linux filtration empty file command summary (Linux)

- Install KVM on Ubuntu and build a virtual environment (Linux)

- Android Studio Clear Project or Rebuild Project Error (Linux)

- Definition Format Oracle basis of various statements (Database)

- Linux top command to get started (Linux)

- Add local search to your Android app (Programming)

- installation process of Matlab 2012a under Ubuntu 14.04 (Linux)

- Add your own kernel and ramfs based on an existing Linux LiveCD (Linux)

- Teach you to diagnose problems with strace (Linux)

- VMware clone Linux find eth0 (Linux)

- Java string intern constant pool resolution Introduction (Programming)

- Hadoop2.0 configuration yarn success (Server)

- floating IP in OpenStack neutron (Server)

 
         
  Oracle database, some basic grammatical structures
     
  Add Date : 2018-11-21      
         
       
         
  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}
     
         
       
         
  More:      
 
- Use XtraBackup to MySQL database online incremental backup and recovery (Database)
- GitLab issued Merge Request return error 500 when the two solutions log (Linux)
- CentOS 7 version how to achieve the power to start the graphical interface (Linux)
- SecureCRT session buffer size settings (Linux)
- Ubuntu 14.04 install the NVIDIA driver + CUDA + MATLAB (Linux)
- Redhat 7 modify the default run level method --RHEL7 use systemd to create a symbolic link to the default runlevel (Linux)
- IIS virtual host of safety knowledge (Linux)
- Terminal fun: 6 interesting Linux command-line tools (Linux)
- Composition and dynamic memory allocation C program (Programming)
- Linux operating system security can not be ignored (Linux)
- Hadoop2.6.3 build clusters and the development of MapReduce WIN7 by Eclipse on Linux demo (Server)
- Oracle how to assess the true concurrent session (Database)
- Oracle procedure or function Empty Table (Database)
- MongoDB Study Notes (1) - Install MongoDB on Windows systems (Database)
- CentOS 6.5 system installation Oracle11.2.0.4 basic steps (Database)
- Ceph Source Analysis: Network Module (Server)
- Java inner classes (Programming)
- Integrated security administrator Linux accident management (Linux)
- Linux + Apache + PHP + Oracle based environment to build (Server)
- Ubuntu 14.10 users to install Audio Recorder 1.5.7 (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.