Simple introduction to oracle (comprehensive)

oracle simple introduction

oracle concept

oracle is a relational database. It has a large database. The large database is composed of one or more instances. Each instance is composed of background processes and logical data. Multiple table spaces and multiple users can be created under one instance. Each user has a default table space, and each table space can become the default table space of a user, The data of users in the same table space will not interfere with each other, and their data is isolated. The table space is composed of logical data structure and physical file. The physical file is responsible for storing data, and the logical data structure is responsible for operating data. To delete a physical file, you need to delete the tablespace first. Logical data contains table structure. The specific location of the table is: user name Table name. In oracle, tables are subordinate to users.

A brief introduction to sql

Brief introduction to sql:

The full name of sql is Structured Query Language, which was first proposed by IBM in 1970 and first used by oracle database. sql is often used to access data, query, update and manage Relational database system.

Structured query language is an advanced non procedural language programing language , allowing users to data structure Working on, there is no need to understand the composition, structure and logic of the underlying database. Structured query language statements can be nested, which makes it have great flexibility and powerful functions.

Features of SQL:

  1. Integration of data description, manipulation, control and other functions.

  2. Two ways of use, unified grammatical structure.

  3. Highly unprocessed.

  4. The language is concise and easy to learn and use.

Main components of sql:

SQL can be divided into three parts: data definition, data manipulation and data control.

  • DML [database operation language]
  • Its statements include: CRUD, create, read, update, delete [also known as action query language]
  • DDL [database definition language]
  • It is the definition of tables, data tables, views, indexes, users, etc.
  • DCL [database control language]
  • It is used to manage user permissions. Its statements in oracle include grant and revoke.

oracle connection

**driver: **oracle.jdbc.driver.oracledriver

**url: **jdbc:oracle:thin:@localhost:1521:oracl

**username: **C##yunyu

**password: **123456

The order in which oracle creates tables

  1. There are tablespaces.
  2. There are users.
  3. Use the specified user to create a table and specify the default tablespace for the table.
  4. Data operation in the table.

Error prone points in oracle learning

  1. Function can directly return a specific value
  1. The data type of a table and a field obtained from a stored procedure or user-defined function can be used


v_stuId username.tablename.field%type;

-- v_stuId Is a custom variable name
-- v_stuId The interpretation of the following sentence is, username Under user tablename Tabular field Type of field;
  1. Variables can also save a row of data in a table. The following expression is used when defining variable types.


v_rowData username.tablename%rowtype;

-- v_rowData Represents the name of the variable that holds a row of table data.
-- v_rowData The interpretation of the following sentence is, username Under user tablename The type of row in the table;

-- Call after saving data
v_rowData.field; -- Displays a field data of the saved row
  1. Select... Into variable name from... where; If there are several data objects processed by the select clause in, you can assign values to several variable objects. During the period, only one into is required, and the assigned objects correspond in turn.
  1. You can return specific values directly
  1. You can assign values when defining variables under as/is
  1. Storage functions cannot be parameterless
  1. The default parameter is in, which can be omitted and not written
  1. The double vertical line of string splicing can be assigned, and the return result of concat() can also be assigned or returned directly.
  1. You can assign values using the return result of concat()
  1. You can return the value of the function directly
  1. Internal functions such as between and reserved words can be used in if
  1. The value range of between includes the maximum value and the minimum value
  1. The concat() function has only two maximum formal parameters and cannot be used to splice more than two characters.
  1. There are two assignment methods: select... into... from dual;

And variable name: = value;

  1. return and out parameters can exist at the same time.
  1. % cannot be directly used as the remainder symbol. mod(m,n) must be used to calculate the remainder

Permission Operation

  1. Create tablespace

    create tablespace myspace
    datafile 'c:\myspace.dbf'
    size 500m
    autoextend on
    next 20m;
    -- myspace It is a table space, and its name is user-defined.
    -- datafile Is the location of the set physical storage file.
    -- size Used to set the initial size of the tablespace.
    -- autoextend Are you in this dbf The file will be automatically extended after application.
    -- next What is the size of each automatic expansion.
  2. Create user

    create user C##yunyu
    identified by 123456
    default tablespace myspace;
    -- C##yunyu Is the user name. It needs to be used for unknown reasons C##To execute the user name.
    -- identified by Followed by the password of the account.
    -- default tablespace This is followed by the user's default tablespace. [create user required assignments]
    -- [A tablespace can create many users]
  3. oracle naming convention

    Variable: v_ Variable name
    Constant: c_ Constant name
    Process: pro_ Process name
    Function: fun_ Function name
    view: view_ view name
    Index: i_ Index name
    Sequence: seq_ Table name

  4. Define variables

    -- grammar
    v_name type [not null][default value];
    -- example
    flag boolean not null default false;
    -- be careful boolean Type needs default value
    -- v_name Custom variable name
  5. Allocation authority [divided into system authority and entity authority]

    -- [[system permission]
    grant dba[,resouce,connect] [on tablename] to C##yunyu;
    -- dba Is the highest authority of the system.
    -- resource Indicates that you can only create entities, not database structures.
    -- connect Permission indicates that you can connect but cannot create entities and database structures.
    revoke dba[,resouce,connect] [on tablename] to C##yunyu;
    -- revoke Followed by the system permissions to be reclaimed.
    -- [Entity authority]
    grant select[,execute,update,insert,alter,index,delete,all] [on tablename] to C##yunyu;
    -- execute Permission to execute stored procedures
    -- all Permissions include all permissions
  6. Create table

    create table student(
    	id number(5) primary key,
        name varchar(20) not null,
        sex varchar(4) not null,
        age number(3),
        birthday date,
        pet varchar(30)
  7. Code block structure

    Three components: definition structure, execution structure and exception structure

    Code analysis:

    	Definition part
    	Executive part
    	Abnormal part
    -- For example, code blocks can be used for testing when calling stored procedures
    -- The definition section can define variables
    -- The executive part is to execute specific logic
    -- The exception part is optional, which is used to handle exceptions
  8. Common statements in oracle

    Conditional statement

    if condition then
    	Logical implementation area
    end if;
    -- ===================
    if condition then
    	Logical implementation area
    	Logical implementation area
    end if;
    -- ===================
    if condition then
    	Logical implementation area
    elsif condition then
    	Logical implementation area
    	Logical implementation area
    end if;
    -- ===================
    case Variable name
    	when Specific value 1 then
    		Logical implementation area
    	when Specific value 2 then
    		Logical implementation area
    	when Specific value 3 then
    		Logical implementation area
       	Logical implementation area
       end case;
    -- ===================

    Circular statement

     Circulatory body;
    exit when End condition;  
    end loop; 
    -- ===================
    while condition loop
    	Circulatory body;
    end loop; 
  9. Data type conversion of oracle

    Implicit type conversion

    select '2'+3 as result from dual;
    -- Output result:-->5

    Show cast

    -- format string 
    select to_char(sysdate,'yyyy"year"MM"day"dd"day"') from dual;
    -- Output: February 15, 2022
    -- =========================================
    -- character string/Time roll date format
    select to_date('2022 February 15','yyyy"year"MM"month"dd"day"') from dual;
    select to_date('2022-02-15','yyyy"-"MM"-"dd') from dual;
    select to_date('2022@02@15','yyyy"@"MM"@"dd') from dual;
    -- All the above outputs: 2022-02-15 00:00:00[Date format, which can be stored in date [Chinese]
    -- =========================================
    -- String to number
    select to_number('231')+324 as result from dual;
    -- The output result is: 555 [string type data can be converted to number Type data save]
  10. Common data types of Oracle

    • character
    • char [fixed length, maximum 2000 bytes]
    • varchar2 [variable length, maximum 4000 bytes, one English and one Chinese character occupy the same size] (recommended by oracle)
    • varchar [variable length, max. 4000 bytes, one English byte and two Chinese bytes]
    • long [large text type, similar to mysql text, with a maximum storage of 2G]
    • Numerical type
    • number(5) [maximum value that can be saved, 99999]
    • number(5,2) [maximum value that can be saved, 999,99]
    • [the first value represents the number of numbers, and the second value represents the number of decimal places]
    • Date type
    • Date [date time type, can save year month day hour: minute: second]
    • timestamp [date time type, which can be saved to the last 9 digits of seconds based on date]
    • Binary type (big data type)
    • clob [storage string, maximum storage 4G]
    • blob [store photos, videos, audio and other binary files, with a maximum of 4G]
  11. sequence

    Explanation: the function of sequence is to realize the self increment of Oracle. It is a counter and will generate a certain number.

    Create sequence

    create sequence Sequence name
    	[increment by 1]  -- Step size: if omitted, it defaults to 1. If it is a decrement operation, it is set to a negative value
    	[start with 1]    -- Initial value, starting from 1 [increment]/Decreasing]
    	[maxvalue=?/minvalue=?/nomaxvalue] -- It can be composed of maximum value, minimum value, and no maximum value.
    	[cycle/nocycle]  -- cycle Indicates a cycle. After the cycle reaches the maximum value, it changes gradually from the initial value. nocycle It means no cycle. However, if the maximum value is set, no cycle will occur, and there will be an error when it is changed to the maximum value.
    	[cache number];  -- CACHE(buffer)Defines the size of the memory block storing the sequence. The default is 20.

    Modify sequence

    alter sequence Sequence name
      [incrment by n]  -- Modify step size
      [maxvalue n/minvalue n/nomaxvalue]  -- Modify the maximum value
      [cycle/nocycle]  -- Modify whether to cycle
      [cache n/nocache];  -- Modify sequence cache
    *The initial value of the sequence cannot be modified

    Delete sequence

    drop sequence Sequence name;
  12. synonym

    Introduction: synonym is a camouflage mechanism to prevent the direct exposure of real data structure in sql query. By establishing synonyms for table names, the disclosure of real table names can be avoided when operating tables.

    There are two types of synonyms:

    One is a public synonym that can be accessed by all database users.

    The second is a private synonym, which can only be used within a pattern.

    ==Note: = = certain rights are required for the establishment and deletion of synonyms.

    Create private synonyms

    create or replace synonym syn_name for username.tablename;
    -- syn_name Is a custom synonym name
    -- username Is the user name
    -- tablename Is the object to be defined as a synonym

    Create public synonyms

    create or replace public synonym syn_name for username.tablename;
    -- syn_name Is a custom synonym name
    -- username Is the user name
    -- tablename Is the object to be defined as a synonym

    Use of synonyms

    select ... from syn_name where ...;
    -- syn_name Is a synonym for customization
  13. trigger



    create [or replace] trigger tri_name
    before/after insert/delete/update of tablename/[field on tablename]
    [for each row]
    	-- Definable variable 
    	-- Trigger logic implementation area
    	-- Get new data<--> :new.field;
    	-- Fetch old data<--> :old.field;
    	-- Intercept to prevent the execution statement from being executed, throw an exception, and use raise_application_error(Exception code,Abnormal information);
    	-- Release requires no operation, and the previous statements can be executed normally without throwing exceptions.
    -- Parameter introduction:
    	tri_name: Custom trigger name
    	before: Indicates before the statement is executed [statement refers to the addition, deletion and modification operation you want to perform]
    	after: Indicates after the statement is executed.
    	insert/pudate/delete It means that you can select any trigger scenario. Of course, you can also use or to connect different scenarios
    	of It can be followed by the table object to be triggered or a field in the table.
    	No for each row is a statement level trigger: it is triggered once when the table is operated once, no matter how many pieces of data are operated this time.
    	There is row level trigger for each row: if the table is operated once, but multiple rows of data are affected, the corresponding trigger will be triggered for each affected row.
    	raise_application_error(Exception number (exception information): the error number information ranges from - 20001 to - 20999, and the exception information is user-defined.

    Take a little Chestnut:

    -- Create trigger
    create or replace trigger tri_studentinfo
    before delete or update on C##yunyu.studentinfo 
    for each row
    	-- Variable name definition area
    		v_ercode number:=to_number((to_char(sysdate,'YYYY')||'5'));
    	if :old.sid=1 then
    		raise_application_error(-v_ercode,'What you do is:'||:old.sname||',You do not have permission to operate on this user...');
    	end if;
    end tri_studentinfo;
    -- When executed 
    delete from studentinfo where sid=1;
    -- Or execute
    update studentinfo set sname='lyl' where sid=1;
    -- Output result:> ORA-20225: You are operating cloud and. You are not authorized to operate this user...

    Delete trigger

    drop trigger tri_name;
    -- tri_name Delete the name of the trigger;
  14. Function / storage function

    Introduction: the stored procedure is the one that should exist most. However, due to historical reasons, the database design retains the stored function, which is not much different from the stored procedure.

    Function definition syntax

    create or replace function fun_name(Variable name [in/out/in out] data type,...)
    return data type
    	Variable definition area
    	Logical implementation area
    	return ...;

    Custom function chestnut

    -- Custom function
    create or replace function fun_sayhi(v_num1 in number) 
    return varchar2
    	v_date varchar2(20);
    	v_say varchar2(20);
    	v_name varchar2(10);
    	v_end varchar2(40);
    	v_time number;
    	-- Get the current time (24-hour format)
    	-- Judgment time range
    	if v_time between 5 and 11 then
    		v_say:='Good noon';
    	elsif v_time between 12 and 13 then
    		v_say:='Good noon';
    	elsif v_time between 14 and 17 then
    		v_say:='Good afternoon';
    		v_say:='Good evening';
    	end if;
    	-- Judge input value
    	if mod(v_num1,2)=0 then 
    	end if;
    	-- Get current time
    	select to_char(sysdate,'yyyy-MM-dd HH24:mi') into v_date from dual;
    	-- Select and assign to a variable and return, or return directly.
    	v_end := v_date||' >> '||v_say||v_name;
    	return v_end;
    end fun_sayhi;
    -- call
    select fun_sayhi(20) as sddsdsa from dual;
    -- results of enforcement
    -- 2022-02-14 15:15 >> Good afternoon, sir

    Delete custom function

    drop function fun_name;
    -- fun_name Is the name of your custom function
  15. stored procedure

    **Introduction: * * is similar to the storage function. The following are the same and different from the storage function.


    1. The definition method and calling method are basically the same.

    2. The structure is the same and the function is the same.

    3. Both stored procedures and stored methods can return values through the out parameter.


    1. The storage method needs to have a return value, and the stored procedure does not.

    2. The storage method cannot be without parameters, and the stored procedure can be without parameters.

    3. The stored procedure has exception handling, and the storage method does not.


    CREATE OR REPLACE PROCEDURE pro_name(parameter [in/out/in out] Parameter type)
    	-- Variable name definition area;
    	-- Business logic implementation area;
    	EXCEPTION    -- Stored procedure exception
    -- pro_name Is a custom stored procedure name
    -- [in/out/in out]Indicates whether to write or not. If not, the default is yes in
    -- as/is There is no difference here, optional

    Take a small chestnut [nonparametric stored procedure]

    -- Create stored procedure [no parameter]
    create or replace procedure pro_sayhi
    	v_name varchar2(30):='yunyu';
    	v_date varchar2(40):=to_char(sysdate,'YYYY-MM-dd HH24:mi:ss');
    	dbms_output.put_line((v_date||' >> '||v_name));

    Call stored procedure [no parameter]

    -- Call method 1 of parameterless stored procedure
    call pro_sayhi();
    -- Call stored procedure method 2
    -- Output result: 2022-02-14 21:06:30 >> yunyu

    Delete stored procedure [no parameter]

    drop procedure pro_sayhi;
    -- pro_sayhi Represents the custom name of the stored procedure.

    Create stored procedure [with reference]

    --Create a parameterized stored procedure
    create or replace procedure pro_sayhello
    (v_name in varchar2,v_redata out varchar2)
    	v_date varchar2(30):=to_char(sysdate,'YYYY-MM-dd HH24:mi:ss');
    	v_redata:='hello world ->'||v_date||' ->'||v_name;

    Call stored procedure [with reference]

    -- Write code block to call
    	-- This area can define variables
    	data varchar2(50);
    	-- This area calls the function
    	-- Output return result

    Delete stored procedure

    drop procedure pro_sayhello;
    -- The same deletion as a parameterless stored procedure
  16. Cursor operation


    1. What is a cursor?
      A data structure ('result set ') used to store multiple pieces of query data,
      It has a 'pointer' that moves up and down ('fetch ') to' traverse each record '
    2. Advantages and disadvantages
      (1) Improve sql 'execution efficiency'
      (2) Sacrifice 'memory'

    come from: (very good, recommended)


    	-- Define the variable name, which is used to store the found data for later call traversal
    	-- declare cursor 
    	cursor cur_name( parameter list ) 
    	select ... from tablename where ...;
    	-- Open cursor
     	open cur_name(Chuan Shen);
     	-- Extract data
     	fetch cur_name into Variable name;
     	-- Close cursor
     	close cur_name;

    Little chestnut

    -- Define variables that store data
     v_stu_info C##yunyu.studentinfo%rowtype;
     -- Step 1: Declare cursor [with parameters]
     cursor cur_stu_info(v_id C##yunyu.studentinfo.sid%type) is
       select * from C##yunyu.studentinfo t where t.sid = v_id;
     -- Step 2: Open cursor
     open cur_stu_info(1);
     -- Step 3: Extract data
     fetch cur_stu_info
       into v_stu_info;
     dbms_output.put_line(v_stu_info.sid || ' : ' || v_stu_info.sname);
     -- Step 4: Close cursor
     close cur_stu_info;

Keywords: Database Oracle SQL

Added by dmikester1 on Tue, 15 Feb 2022 06:08:19 +0200