oracle simple introduction
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:
Integration of data description, manipulation, control and other functions.
Two ways of use, unified grammatical structure.
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.
The order in which oracle creates tables
- There are tablespaces.
- There are users.
- Use the specified user to create a table and specify the default tablespace for the table.
- Data operation in the table.
Error prone points in oracle learning
- Function can directly return a specific value
- 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;
- 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
- 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.
- You can return specific values directly
- You can assign values when defining variables under as/is
- Storage functions cannot be parameterless
- The default parameter is in, which can be omitted and not written
- The double vertical line of string splicing can be assigned, and the return result of concat() can also be assigned or returned directly.
- You can assign values using the return result of concat()
- You can return the value of the function directly
- Internal functions such as between and reserved words can be used in if
- The value range of between includes the maximum value and the minimum value
- The concat() function has only two maximum formal parameters and cannot be used to splice more than two characters.
- There are two assignment methods: select... into... from dual;
And variable name: = value;
- return and out parameters can exist at the same time.
- % cannot be directly used as the remainder symbol. mod(m,n) must be used to calculate the remainder
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.
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]
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
-- 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 --
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
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) )
Code block structure
Three components: definition structure, execution structure and exception structure
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
Common statements in oracle
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; -- ===================
loop Circulatory body; exit when End condition; end loop; -- =================== while condition loop Circulatory body; end loop;
Data type conversion of oracle
Implicit type conversion
select '2'+3 as result from dual; -- Output result:-->5
-- 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]
Common data types of Oracle
- 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]
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 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.
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
drop sequence Sequence name;
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
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...
drop trigger tri_name; -- tri_name Delete the name of the trigger;
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
**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) 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
- 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 '
- 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)
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;
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;
- What is a cursor?