oracle knowledge points summary

Indexes

Create index

CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique Represents a unique index
ON table_name([column1 [ASC|DESC],column2    --bitmap，create bitmap index 
[ASC|DESC],...] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]                                 --Specifies that the index has free space in the data block
[STORAGE (INITIAL n2)]
[NOLOGGING]                                  --Indicates that changes to the table are allowed when creating and rebuilding indexes DML Operation, which should not be used by default
[NOLINE]
[NOSORT];                                    --It means that no sorting is performed when creating an index. It is not applicable by default. If the data is already arranged according to the index order, it can be used

by student1 The registration date of the table is indexed and arranged in descending order. The index name is“ DX1_02". 
create index DX1_02 on student1(Date of registration desc);

Delete index

drop index index_sno;

View index

select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename';

 -- eg:    
create index index_sno on student('name');
select * from all_indexes where table_name='student';

synonym

Oracle synonyms literally mean aliases, which are similar to the functions of views

Create public synonyms

create public synonym synonym_name for table_name;

Create common synonyms

create  synonym synonym_name for table_name;

drop synonym

drop synonym synonym_name;

sequence

Sequence is a sequence number generator, which can automatically generate sequence numbers for rows in the table and generate a set of equally spaced numerical values (type is number). It does not occupy disk space and memory. Its main purpose is to generate the primary key value of the table, which can be referenced in the insert statement, check the current value through query, or increase the sequence to the next value.

Create sequence

CREATE SEQUENCE system permission is required to create a sequence. The sequence creation syntax is as follows:

　　CREATE SEQUENCE Sequence name
　　[INCREMENT BY n]
　　[START WITH n]
　　[{MAXVALUE/ MINVALUE n| NOMAXVALUE}]
　　[{CYCLE|NOCYCLE}]
　　[{CACHE n| NOCACHE}];

Of which:

1. INCREMENT BY is used to define the step size of the sequence. If omitted, it defaults to 1. If a negative value occurs, it means that the value of the Oracle sequence decreases according to this step size.

2. START WITH defines the initial value of the sequence (i.e. the first value generated), which is 1 by default.

3. MAXVALUE defines the maximum value that the sequence generator can produce. The option NOMAXVALUE is the default option, which means that there is no maximum value definition. At this time, for incremental Oracle sequences, the maximum value that the system can generate is the 27th power of 10; For decreasing sequences, the maximum value is - 1.

4. MINVALUE defines the minimum value that the sequence generator can produce. The option NOMAXVALUE is the default option, which means that there is no minimum value definition. At this time, for the decreasing sequence, the minimum value that the system can generate is? The 26th power of 10; For incremental sequences, the minimum value is 1.

5. CYCLE and NOCYCLE indicate whether to CYCLE when the value of the sequence generator reaches the limit value. CYCLE stands for CYCLE and NOCYCLE stands for no CYCLE. If cyclic, when the increment sequence reaches the maximum value, it loops to the minimum value; For the decreasing sequence, when the minimum value is reached, it loops to the maximum value. If you do not CYCLE, an error will occur if you continue to generate new values after reaching the limit value.

6. Cache defines the size of the memory block storing the sequence. The default is 20. NOCACHE means that the sequence is not buffered in memory. Memory buffering of sequences can improve the performance of sequences.

7. NEXTVAL returns the next valid value in the sequence, which can be referenced by any user.

8. The current value of the sequence is stored in CURRVAL. NEXTVAL should be specified before CURRVAL, and both should be valid at the same time.

Create a sequence with a starting value of 50, a step size of 10, no buffer, and a sequence name of "DX1_06".

create sequence DX1_06 increment by 10 start with 50 nocache;

Create a sequence. The starting value of the sequence is 1000, the step size is 2, and the maximum value is 10000. It is non recyclable, and the sequence name is "seq_1".

create sequence seq_1 increment by 2 start with 1000  maxvalue 10000 nocycle;

Modify sequence

alter

Modify the sequence "DX1_06", set the maximum value of the sequence to "82000", the minimum value to "10", and the step size to "5".

alter sequence DX1_06 maxvalue 82000 minvalue 10 increment by 5;

Delete sequence

drop

Delete sequence seq_1.

drop sequence seq_1;

Some common functions

Query a table and sort it by business

rank() over(partition)

Fills the string with the specified character from the right

rpad(string,padded_length,[pad_string])

• String indicates the filled string
• padded_length refers to the length of characters, which is the number of returned strings. If this number is shorter than the length of the original string, the rpad function will intercept the string into n characters from left to right;
• pad_string is an optional parameter. This string is to be pasted to the right of the string. If this parameter is not written, the lpad function will paste a space to the right of the string.

stored procedure

Definition of stored procedure

No parameters

create or replace procedure Stored procedure name
as
begin
  ----------------------------
end;

With parameters

create or replace procedure myDemo02(name in varchar,age in int)
as
begin
  dbms_output.put_line('name='||name||', age='||age);
end;

Knowledge points

1. There are three ways to create a primary key
2. What cannot be done on the view: define a new base table on the view
3. In sql, a sub query is a query statement embedded in another query statement
4. Reducing foreign keys can achieve entity integrity
5. In the full-text search function, the column to be searched is match()
6. Intermediate connection does not belong to connection type
7. Connection types include: external connection, internal connection and cross connection
8. union can combine multiple SQL query statements to form a combined query
9. grouped by???
10. Use of delete statement DELETE FROM Person WHERE LastName = 'Wilson'
11. Function that returns the current date: current date()
12. Data model: mesh model, hierarchical model and network model
13. Cross connection can be regarded as Descartes connection
14. The purpose of creating indexes for data tables is to improve the retrieval performance of queries
15. view in SQL language is the external mode of database
16. View all tables in the database: show tables
17. start transaction indicates the fast start of a new transaction
18. Function to format date: DATE_FORMAT()
19. SQL language is a non procedural language
20. The sign that matches any character in a regular expression is'. '
21. DML statements are database operation statements. Including update, delete and select
22. DDL database definition language. Including create, alter, drop and truncate
23. declimal is a variable precision floating-point value
24. Logical operator priority: not / and / or
25. limit

Indexes

establish

Single index

create  bitmap  index   Index name  on Table name( Variable name desc );

desc Indicates descending sort.  bitmap It is a bitmap index, which is normal and can not be added

Composite index

           ``````sql
           create index Index name on Table name(Column name 1,Column name 2);              
           
           ``````

View index

select * from user_indexes/user_ind_columns ;

Delete index

drop index Index name;

synonym

Create Synonyms

create synonym synonym for target

View synonyms

select * from user_synonyms;
select * from all_synonyms;

drop synonym

drop synonym synonym;

sequence

Create sequence

create sequence Sequence name

start with Start number

increment by Growth figures

minvalue minimum value

maxvalue Maximum

cycle

nocache

detailed description:

start with start number

increment by increment step length, increasing several numbers each time

minvalue min

maxvalue maximum value à can not be set. If not set, it should be written as nomaxvalue, that is, infinity

Cycle cycle, that is, when the length increases to the maximum value, it will increase again from the minimum value

nocache does not set cache

View sequence

select *  from user_SEQUENCES,all_SEQUENCES;

Reference sequence

xx.nextval / xx.curval

insert into xx surface values (  )  ,

insert into dept values( DX1_06.nextval,'ss','CN','s',NULL,NULL );

Modify sequence

 alter sequence student_id -- The sequence name can also be changed
 minvalue 1   
 maxvalue 99999  
 start with 1   
 increment by 1  
 cycle    -- After 99999, start from scratch
 nocache；  

user

Change Password

alter user user name identified by password;

Create user

create  (c##)user username identified by password;    // To create an ordinary user, you need to add c##

Lock user

ALTER USER username ACCOUNT LOCK; locking

ALTER USER username ACCOUNT UNLOCK; Unlock

delete user

drop user username ;

Learning new skills such as sorting

sort

(DENSE_)RANK( ) OVER ([ query_partition_clause ] order_by_clause)
rank():Jump, two first, the next is the third
dense_rank():Non jumping,Two 1st,Next is number two

Divided into: (1) continuous or discontinuous: dense_rank,rank

     (2)Partition or no partition: use partition，Not used partition

PL/SQL

. Differences between and / or

In SQL*Plus, type the following PL/SQL block, ending with a dot (.). If you want to RUN the contents of the buffer, you can use the "RUN" command or the "/" command.
1

stay SQL*Plus Type the following in PL/SQL Block, in dots(.)end. If you want to run the contents of the buffer, you can use“ RUN"Command or“ / "Command.


set serveroutput on The command is open COMMAND The output stream in the command window.

If you don't do it, you don't output it. It's terrible!!!!!!!!!!!!!!!!!!!

Separator	describe
+,-, *, /	Addition, subtraction / negation, multiplication, Division
%	Property binding
'	string delimiter
.	Component selector
(,)	Expression or list separator
:	Host variable indicator
,	Item separator
"	Reference identifier separator
=	Relational operator
@	Remote access indicator
;	Declaration or statement Terminator
:=	Assignment Operators
=>	Association operator
ΙΙ	concatenation operator
**	Exponential operator
<<, >>	Label separator (start and end)
/*, */	Multiline comment separator (start and end)
--	Single line comment indicator
..	Range operator
<, >, <=, >=	Relational operator
<>, '=, ~=, ^=	Different versions of the 'not equal to' operator

program

3. Put: write the contents to memory and wait until put_ Output with line
4,put_line: Needless to say, output characters

Output statement

dbms_output.put_line(  );   //  Line is a line feed

dbms_output.put()  Normal output

Creation process

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 
END procedure_name;
SQL

Execution process

• Use EXECUTE keyword

EXECUTE Process name;

• The name of the procedure called from the PL/SQL block

begin 
 Process name;
end;

function

Create function

CREATE [OR REPLACE] FUNCTION function_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
RETURN return_datatype 
{IS | AS} 
BEGIN 
   < function_body > 
END [function_name];
SQL

Among them,

• Function name is the name that specifies the function to be created.
• The [OR REPLACE] option indicates whether modification of existing functions is allowed.
• The optional parameter list contains the name, mode and type of the parameter. IN represents the value to be passed externally, and OUT represents the parameter to be used to return the value outside the procedure.
• The function must contain a return statement.
• The RETURN clause specifies the data type to be returned from the function.
• Function body contains executable parts.
• Use AS keyword instead of IS keyword to create independent functions.

Query function

cursor

1 Overview

1. What is a cursor?
   A data structure used to store multiple pieces of query data('Result set')，
   It has one 'Pointer'，Move from top to bottom('fetch')，So that 'Traverse each record'
   
2. Advantages and disadvantages
   (1) increase sql 'Execution efficiency'
   (2) sacrifice 'Memory'

Implicit Cursor

Explicit Cursor

Using an explicit cursor includes the following steps-

• Declare cursor initialization memory
• Open cursor to allocate memory
• Get data from cursor
• Close the cursor to free the allocated memory

declare cursor

Declaring cursors uses names and associated SELECT statements to define cursors. For example-

CURSOR c_customers IS 
   SELECT id, name, address FROM customers;
SQL

Open cursor

Opening the cursor allocates memory for the cursor and prepares it to extract the row record data returned by the SQL statement. For example, open the cursor defined above as follows:

OPEN c_customers;
SQL

Get cursorget cursors access only one row at a time. For example, get a row from the cursor opened above, as shown in the following code:

FETCH c_customers INTO c_id, c_name, c_addr;
SQL

Close cursor

Closing the cursor means freeing the allocated memory. For example, close the cursor opened above, as shown below:

CLOSE c_customers;
SQL

exception handling

abnormal	Oracle error code	SQLCODE	describe
ACCESS_INTO_NULL	06530	-6530	Raised when an empty object is automatically assigned a value.
CASE_NOT_FOUND	06592	-6592	This error is raised WHEN no option in the WHEN clause of the CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL	06531	-6531	This problem occurs when a program attempts to apply a collection method other than EXISTS to an uninitialized nested table or varray, or when a program attempts to assign a value to an element of an uninitialized nested table or varray.
DUP_VAL_ON_INDEX	00001	-1	This error is raised when trying to store duplicate values in a column with a unique index.
INVALID_CURSOR	01001	-1001	This error is raised when an attempt is made to perform an disallowed cursor operation, such as closing an unopened cursor.
INVALID_NUMBER	01722	-1722	The conversion of a string to a number failed because the string does not represent a valid number.
LOGIN_DENIED	01017	-1017	Raised when a program attempts to log in to the database with an invalid user name or password.
NO_DATA_FOUND	01403	+100	Raised when the SELECT INTO statement does not return any rows.
NOT_LOGGED_ON	01012	-1012	Raised when the database call is not connected to the database.
PROGRAM_ERROR	06501	-6501	Raised when PL/SQL encounters an internal problem.
ROWTYPE_MISMATCH	06504	-6504	Raised when the cursor gets a value in a variable with an incompatible data type.
SELF_IS_NULL	30625	-30625	Raised when a member method is called, but an instance of the object type is not initialized.
STORAGE_ERROR	06500	-6500	Raised when PL/SQL runs out of memory or memory is corrupted.
TOO_MANY_ROWS	01422	-1422	Raised when the SELECT INTO statement returns multiple rows.
VALUE_ERROR	06502	-6502	Raised when an arithmetic, conversion, truncation, or sizeconstraint error occurs.
ZERO_DIVIDE	01476	1476	Raised when an attempt is made to divide a number by zero.

Syntax for exception handling

The general syntax for exception handling is as follows. Here, you can enumerate as many exceptions as possible and specify how to handle them. The default exception will be handled using WHEN... THEN, as shown in the following syntax-

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)> 
EXCEPTION 
   <exception handling goes here > 
   WHEN exception1 THEN  
      exception1-handling-statements  
   WHEN exception2  THEN  
      exception2-handling-statements  
   WHEN exception3 THEN  
      exception3-handling-statements 
   ........ 
   WHEN others THEN 
      exception3-handling-statements 
END;

Example

SET SERVEROUTPUT ON SIZE 99999;
DECLARE 
   c_id customers.id%type := 100; 
   c_name  customerS.name%type; 
   c_addr customers.address%type; 
BEGIN 
   SELECT  name, address INTO  c_name, c_addr 
   FROM customers 
   WHERE id = c_id;  
   DBMS_OUTPUT.PUT_LINE ('full name: '||  c_name); 
   DBMS_OUTPUT.PUT_LINE ('address: ' || c_addr); 

EXCEPTION 
   WHEN no_data_found THEN 
      dbms_output.put_line('No qualified customer information was found!'); 
   WHEN others THEN 
      dbms_output.put_line('Error!'); 
END; 
/

Fill in the default values.

NVL (XX, 0) defaults to 0 zero

grammar

NVL(eExpression1, eExpression2)

parameter
eExpression1, eExpression2

If eExpression1 evaluates to a null value, NVL() returns eExpression2. If eExpression1 does not evaluate to a null value, eExpression1 is returned. eExpression1 and eExpression2 can be either data type. If the results of eExpression1 and eExpression2 are null values, NVL() returns NULL..

Delete delete

delete from xx  where 

insert

insert into Table name values(Value 1, value 2,......);

insert into Table name(Column 1, column 2,......)values(Value 1, value 2,......);

insert into Table name 2(Column 1, column 2,......)select Value 1, value 2,...... from Table name 1;  (Table 2 must exist, column 1, column 2,......(must exist)

insert into Table 2 select * from Table 1;

select Value 1, value 2,...... into Table name 2 from Table name 1; (Table 2 does not exist. The table name will be automatically created when inserting (Table 2)

trigger

old / new

1. When using the insert statement, if there is no data in the original table, the newly inserted data is new for the table after inserting data, as shown in the figure:

2. When the delete statement is used, the deleted data is od relative to the data in the table after deleting the data, as shown in the figure:

3. When using the update statement, when modifying the data of the original table, compared with the data of the modified table, the modified data in the original table is old, and the modified data in the modified table is new, as shown in the figure:

RAISE_APPLICATION_ERROR

Maybe not many people know raise_ APPLICATION_ What is the purpose of error, although I have literally guessed what this function is for. Exception handling for testing
We all use dbms_output.put_line to output exception information, but in practical applications, the exception information needs to be returned to the calling client.
In fact, RAISE_APPLICATION_ERROR is the transfer of application specific errors from the server to the client application (SQLPLUS on other machines or other foreground development languages)