Definition formats of basic Oracle statements

Definition formats of basic Oracle statements

Oracle built-in data types
I. character data
1. char (size)
2. varchar2 (size) is the most commonly used, with a maximum length of 4000 bytes.
3. nvhar (size) and nvarchar (size)
4. varchar (size)
5. We recommend that you use LOB large data for long.
6. raw stores binary data. We recommend that you use LOB for large data volumes.

Ii. Numbers
1. number (p, s)

Iii. Date
1. data
2. timestamp
3. timestamp with time zone
4. timestamp with local time zone
5. interval year to month
6. interval day to second
Iv. Large Object Data Types
BLOB, CLOB, NCLOB, BFILE, maximum length 4 GB
5. ANSI, DB2, SQL/DS
Vi. User-defined types
Create type
Create type body

SQL statement category
I. Data Query Statement (DQL)
SELECT
Ii. data manipulation Statement (DML)
INSERT, UPDATE, DELETE
Iii. Data Definition Statement (DDL)
CREATE, ALTER, DROP
Iv. Data Control statements (DCL)
GRANT, REVOKE, COMMIT, ROLLBACK, SAVEPOINT

Specific SQL statement
I. create table
Create a table
Create table <table_name>
(
Col_name <datatype>,
Col_name <datatype>,
Col_name <datatype>
)
Ii. alter table
Modify Table
Alter table <table_name>
[Add <col_name datatype>]
[Modify <col_name datatype>]
[Drop column <column_name>]
Iii. rename
Rename a table
Rename old_table_name to new_table_name
4. truncate table
Delete the information in the table. Only the table structure is retained and the deleted data cannot be recovered.
Truncate table <table_name>
V. drop table
Delete table
6. drop table <table_name>
VII. select
Select statement
Select <column_list>
From <table_name>
[Where <codition>]
[Group by <group_by expression>]
[Having <group_condition>] -- the group function can only be written after having.
[Order by <col_name>]

1. dual table
2. sysdate system time
3. Operations used in the where Condition
+ -*/
| Connection
= ,! =, ^ =, <>,>, >=, <, <=, Any, some, all
Not, and, or
In (equivalent to any) and not in (equivalent! = All)
Between x and y
Not between x and y
Is null and is not null (the nvl () function can be used to convert null to the required value)
Exist
Like (_ represents one character, % Represents 0 or more characters)
If the string contains "_" "%", you can use escape '\', as shown in
Like '% s \ _ t % 'escape' \ 'is used to match the "s_t" String
Set Operators
4. Common functions
Initcap is capitalized,
Instr searches for character locations
Ength character length,
Lower to lowercase,
Convert upper to uppercase,
Fill the lpad with a specific length on the left,
Fill the rpad with a specific length on the right,
Rtrim cut characters and Its right characters,
Lrtrim cut characters and left-side characters,
Soundex, similar words
Substr, character Truncation
Chr and ascii characters
Ascii and character ascii codes
Translate, character replacement
Repleace, character, or string replacement
Abs absolute value,
Ceil rounded up,
Floor rounded down,
Square root of sqrt,
Power multiplier,
Sign numbers plus and minus,
Trunc truncates decimal places,
Round,
The power of exp constant e,
Mod remainder,
Ln natural logarithm value,
The base-10 logarithm of log,
Vsize storage space,
The maximum,
The maximum,
Add_months add or subtract the specified month,
Last_day returns the date of the last day of the specified month,
Next_day returns the first day of the next specified date,
Months_between,
Trunc
Date Format:
SYEAR year,
YY,
Q quarter,
MM Month,
RM Rome month,
Month,
WW week,
W the week of the current month,
The day of DDD,
DD day of the month,
D The day of the week,
DY week,
HH, HH12, 12 hexadecimal hours
HH2424 hours,
MI minutes (0 ~ 59 ),
SS seconds (0 ~ 59 ))
To_char converts a date to a character,
To_date converts a character to a date,
To_number converts numbers to characters,
Decode converts specific data into another representation

5. Group functions
Avg average, ignore null
Count,
Max, ignore null,
Min minimum, ignore null,
Stddev standard deviation, ignore null,
Sum, ignore null,
Variance. null is ignored.
6. rowin is the actual physical address for storing each record, and the access to the record is based on rowid, which is the fastest way to access the data in the table.
7. The where clause cannot limit the results of group by. having is required.
8.

8. insert
Insert into <table_name> [col_name,…,]
Values (value ,...);
In sqlplus, parameters can be replaced with & characters. You can use tools to enter insert values one by one.
IX. update
Update <table_name>
Set col_name = value/expression, col_name = value/expression
[Where <conditions>]

10. delete
Delete from <table_name>
[Where <condition>];
Or delete <table_name>
[Where <condition>];

Differences between truncate and delete:
A. delete can be undone using the rollback command, while truncate cannot
B. truncate cannot trigger any delete trigger.
11. Constraints
1. unique
Tel_number char (10) constraint cm_unique unique,
Combination of constraint cm_unique unique (tel_number, online_email ),
Alter table cm add constraint cm_unique unique (tel_number ),

2. check
Tel_number char (14) check (length (tel_number) = 14 ),

3. not null
Tel_number char (14) not null,

4. primary key
Tel_number char (14) constraint cm_primary primary key,

5. foreigh key
Constraint emp1_foreign foreign key (deptno) references dept1 (deptno ),
Cascade deletion:
Deptno number (2) references dept1 (deptno) [on delete set null] When deleting a subrecord, the corresponding value of the primary record is null.
Constraint emp1_foreign foreign key (deptno) references dept1 (deptno) [on delete cascade] cascading deletion of Primary records
If no primary record is written, the primary record cannot be deleted.

12. Connection
1. Left join:
Indicates that all specified content in the left table is returned.
Select e. ename, d. dname
From emp1 e, dept d
Where e. deptno = d. deptno (+)
2. Right join:
Indicates that all specified content in the right table is returned.
Select e. ename, d. dname
From emp1 e, dept d
Where e. deptno (+) = d. deptno
3. Self-connection
Select worker. ename, manager. ename
From emp worker, emp manager
Where worker. mgr = manager. empno
4. nested Query
The subquery cannot contain the order by grouping statement;
Using exists in oracle is faster than using in queries. When using exists, the system first checks the primary query and runs the subquery to find the first match. When the system executes the in statement, the system first executes the subquery and puts the results in a temporary table with an index. Before executing the subquery, the system suspends the primary query.
5. Set Operations
Union all: the results of two select statements can be repeated.
Union: removes any identical rows based on the results of two select statements.
Minus: removes the second select result from the first select result.
Intersect: returns only the rows that appear in both select statements.

13. PL/SQL
Declare
<Declarations section>
Begin
<Executable command>
Declare
<Declarations section>
Begin
<Executable command>
End;
End;

1. Definition variables are similar to SQL Definitions
Variable_name [constant] datatype [not null] [{: = | default} default_value]
Use constant when defining constants.

2. Interactive input variable value
V_empno number (4): = & v_empno;

3. Print the statement
Dbms_output.put_line (v_empno );
4. Display records
Type record_name is record (field_definition_list );
Example:
Type t_emp is record
(
V_empno emp. empno % type,
V_ename emp. ename % type
);
5. Implicit record
V_emp emp % rowtype;

6. index_by table
Type type_name is table of element_type [not null] index by binary_interger;
Declare
Type table_empno_type is table of emp. empno % type index by binary_integer;
Table_empno table_empno_type;
I binary_integer: = 1;
Begin
Select empno
Into table_empno (I)
From emp
Where e-mapreduce = 7369;
End;
7. variable array
Type type_name is [varray | varying array] (max_size) of element_type [not null]
Declare
Type varray_empno_type is varray (5) of emp. emono % type;
Varray_empno varray_empno_type;
Begin
Varray_empno: = varray_empno_type (7369.7499 );

8. Set Methods
Count: number of elements in the Set
Delete: deletes all elements in a collection.
Delete (x): delete an element whose subscript is x.
Delete (x, y): delete the elements whose subscript ranges from x to y.
Extend: add an element at the end of the set.
Extend (x): Add x elements to the end of the set.
Extend (x, n): Add n x copies to the end of the Set
First: returns the bottom number of the first element. For varray, 1 is always returned.
Last: returns the bottom number of the last element.
Limit returns the maximum number of elements in a variable array set.
Next: returns the elements after x.
Prior: returns the element before x.
Trim: deletes an element from the end.
Trim (x): deletes x elements from the end.

Http://www.cnblogs.com/roucheng/
9. dynamic SQL
Excute immediate dynamic SQL statement using binding parameter list returning into output parameter list;

Str_ SQL: = 'create table' | ''| table_name | '(' | field1 |'' | 'yype1' | ', '| field2 | ''| 'datatype2' | ')';
Execute immediate str_ SQL;

10. if Condition Statement
If condition then
Sequence_of_statements
End if;

If condition then
Sequence_of_statement
Else
Sequence_of_statement
End if;

If condition then
Sequence_of_statement
Else if condition2 then
Sequence_of_statement
Else
Sequence_of_statement
End if;

11. case statements
Case selector
When expression then sequence_of_statements;
When expression then sequence_of_statements;
When expression then sequence_of_statements;
[Else sequence_of_statements;]
End case;

12. loop
Loop
Sequence_of_statements
If a> 0 then
Exit or exit when a> 0
End if;
End loop;

13. for-loop statements
For counter in [reverse] lower_bound... higher_bound loop
Sequence_of_statement
End loop;

For example:
For I in 1 .. v_count loop
List (I): = I * I;
End loop;

14. while-loop statement
While condition loop
Sequence_of_statements
End loop;

15. cursor Definition
A. cursor cursor_name [(parameter [, parameter]…)]
[Return return_type] is select_statement
B. open cursor_name
C. fetch cursor_name into variable [, variable,…]
D. close cursor_name

Example:
Declare
Cursor c_emp_ename is select ename form emp;
V_ename emp. ename % type;
V_count binary_integer;
Begin
Select count (rowed)
Into v_count
From emp;
Open c_emp_ename;
For I in I .. v_count loop
Fetch c_emp_ename into v_ename;
Dbms_output.put_line (vname );
End loop;
Close c_emp_ename;
End

16. cursor for loop and its alternative statements
A. Define the cursor first, and then use the loop in (cursor_name ).
Cursor cursor_dept is select deptno, dname from dept order by deptno;
For var in cursor_dept loop
You can use var to obtain the data indicated by the cursor.
End loop
B. Use this loop using in (query statement ).
For var in (select deptno, dname from dept order by deptno;) loop
You can use var to obtain the data indicated by the cursor.
End loop

17. Display cursor attributes
% Found: if c_emp_ename % fount then... End if;
% Notfount: exit when c_emp_ename % notfound;
% Isopen: if c_emp_ename % isopen then... End if;
% Rowcount: number of times of extraction if c_emp_name % rowcount> 10 then... End if

18. Implicit cursor (SQL cursor)
These statements are used to process insert, update, delete, and select into statements that return a row. These statements are used to judge the processing result.
You cannot use open, fetch, or close operations.
It also includes % fount, % notfount, % isopen (always false), and % rowcount.

19. Exception Handling
A. Exception throw Method
Pl/SQL Runtime
Raise prediction_name
Call raise_application_erroe
B. exception
When exception_name then
Process Code;
When exception_name then
Process Code;
When others then
Process Code;
C. Custom exceptions
Declare
Except tin_name exception;
Begin
Statements;
Raise <prediction_name>
Exception
When <prediction_name> then
End;

20. subroutine

1. Stored Procedure
Create [or replace] procedure <procedure_name>
(<Arg1 [in | out | in out], datatype,……>)
Is |
[Local declaration]
Begin
Executable statements
[Exception handler]
Edn [procedure_name]

2. Functions
Create [or replace] function <function_name>
(<Arg1 [mode], datatype> ,......)
Return <datatype> is |
[Local declaration]
Begin
Executable statements
[Exception handler]
End [function_name]

Functions and processes can receive or return another or multiple values through the parameter list. The main difference between a function and a process is their call method, A process is called as an independent execution statement. After a function is called, the return value of the function must be assigned to a variable.
3. Package
Package definition:
Create [or replace] package package_name {as | is}
Public_variable_declarations |
Public_type_declarations |
Public_exception_declarations |
Public_cursor_declarations |
Function_declarations statement |
Procedure_specifications statement
End [package_name]
Package subject:
Create [or replace] package body package_name {as | is}
Public_variable_declarations |
Public_type_declarations |
Public_exception_declarations |
Public_cursor_declarations |
Function_declarations implementation |
Procedure_specifications implementation
End [package_name]

4. triggers
Create [or replace] trigger trigger_name trigger event
On {table_or_view_name | database}
[Referencing [old [as] <old_name>] [new [as] <new_name>] // used for updating
[For each row [when condition] // a row-Level Trigger is added; otherwise, a statement-Level Trigger is performed.
Trigger_body

Trigger time:
Before: trigger execution before database action.
After: trigger execution after database action
Instead of: the trigger is triggered, but the corresponding operation is not executed, and only the SQL statement of the trigger is run. Used to enable modifications to a view that cannot be modified.

Trigger event:
Insert on: When a row is inserted into a table or view
Update of: when updating a table or View row
Delete on: When a table or View row is deleted
Create: when creating a database object
Alter: when modifying a database object
Drop: When deleting a database object
Start: trigger triggered when the database is opened and triggered after the event
Shutdown: trigger triggered when the database is shut down before the event
Logon: triggered when a session is established and before the event
Logoff: triggered when the session is closed and triggered before the event
Server: triggered when a server error occurs and after the event.

Condition predicates:
Inserting, updationg, and deleting