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

example:

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.

example:

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:

    declare
	Definition part
begin
	Executive part
	exception
	Abnormal part
end;   

-- 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
else
	Logical implementation area
end if;
-- ===================
if condition then
	Logical implementation area
elsif condition then
	Logical implementation area
...
else 
	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
	else
   	Logical implementation area
   end case;
-- ===================

    Circular statement

    loop
 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

    Introduction:

    Syntax:

    create [or replace] trigger tri_name
before/after insert/delete/update of tablename/[field on tablename]
[for each row]
[dclare]
	-- Definable variable 
begin
	-- 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.
end;


-- 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
declare
	-- Variable name definition area
		v_ercode number:=to_number((to_char(sysdate,'YYYY')||'5'));
begin
	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
is
	Variable definition area
begin
	Logical implementation area
	return ...;
end;

    Custom function chestnut

    -- Custom function
create or replace function fun_sayhi(v_num1 in number) 
return varchar2
as
	v_date varchar2(20);
	v_say varchar2(20);
	v_name varchar2(10);
	v_end varchar2(40);
	v_time number;
begin
	-- Get the current time (24-hour format)
	v_time:=to_number(to_char(sysdate,'HH24'));
	-- 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';
	else 
		v_say:='Good evening';
	end if;
	-- Judge input value
	if mod(v_num1,2)=0 then 
		v_name:='sir';
	else
		v_name:='ma'am';
	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.

    Similarities:

    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.

    Differences:

    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.

    Syntax:

    CREATE OR REPLACE PROCEDURE pro_name(parameter [in/out/in out] Parameter type)
as/is
	-- Variable name definition area;
BEGIN
	-- Business logic implementation area;
	EXCEPTION    -- Stored procedure exception
END 

-- 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
as
	v_name varchar2(30):='yunyu';
	v_date varchar2(40):=to_char(sysdate,'YYYY-MM-dd HH24:mi:ss');
begin
	dbms_output.put_line((v_date||' >> '||v_name));
end;

    Call stored procedure [no parameter]

    -- Call method 1 of parameterless stored procedure
call pro_sayhi();

-- Call stored procedure method 2
begin
	pro_sayhi();
end;

-- 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)
is
	v_date varchar2(30):=to_char(sysdate,'YYYY-MM-dd HH24:mi:ss');
begin
	v_redata:='hello world ->'||v_date||' ->'||v_name;
end;

    Call stored procedure [with reference]

    -- Write code block to call
declare
	-- This area can define variables
	data varchar2(50);
begin
	-- This area calls the function
	pro_sayhello('23',data);
	-- Output return result
	dbms_output.put_line(data);
end;

    Delete stored procedure

    drop procedure pro_sayhello;


-- The same deletion as a parameterless stored procedure

  16. Cursor operation

    Introduction:

    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: https://blog.csdn.net/qq_34745941/article/details/81294166 (very good, recommended)

    grammar

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

    Little chestnut

    declare
-- 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;
begin
 -- 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;
end;

Keywords: Database Oracle SQL

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

Popular Keywords

©2024 Programming VIP