Development of Oracle

The Development of Oracle

Basic structure of Oracle database

Logical structure of Oracle database

Physical structure of Oracle database

data file

log file

Control file

Parameter file

Oracle Memory Structure

Oracle's Process Structure

Database and Management

Database Operation

Related Terms

• Database Name

• Database Instance Name

  Relationship between database name and instance name

• Operating System Environment Variables

• Database Domain Name

• Global Database Name

• Database Service Name

Database Configuration Modification

• NET Service Name
• TNS File Configuration

Create Database - create database_ Name

Creation and management of tablespaces

SQL Language

Data Definition Language - DDL

Common data types

• char
• varchar
• int or integer
• smallint
• real \ float \ double
• numeric
• DateTime
• Text
• Image

create

create table Student(
	sno char(20) primary key,
	sname varchar(20) not null
);

-- Oracle in
    -- Copy Table
    create table table_name as select * from table_name;
    -- Copy table structure only
    create table table_name as select * from table_name
        where 1=2;

The primary key is used to indicate the keyword (primary code) of the table

You can also use this: primary key(sno,cno)

not null is used to indicate that a field cannot be empty

Create View

create view view_name as (select * from table_name);
-- Be careful as

Create Constraints

alter

• add

  alter table table_name add column_name data type;
  -- For example, give Student Table Add Field phone char(16)
  alter table Student add phone char(16);
  

• alter

  -- Modify table structure
  -- alter table Table Name alter column Field name field type
  alter table table_name alter column sdept varchar(1);
  

• drop

  -- Delete Field
  alter table table_name drop column column_name;
  

drop

drop table Student;

Delete table order should be: Associated table - Basic table

Remove Constraints

Data Manipulation Language - DML

select

select column_name from table_name where 

select column_name from table_name table_name_temp where
-- table_name_temp by table_name Instead, not used at this time as Explain
-- where In condition table_name from table_name_temp replace

rownum

select * from (select * from table_name) 
	where rownum<=number;
	-- Find the result select * from table_name Before in number column
	-- Can only be used "<" or "<="

insert

insert into table_name() values ();
-- Omit to
insert into table_name values();

update

update table_name set colmun=() where 

delete

delete from table_name where 

Data Control Language - DCL

grant

-- Give users the right to connect
grant connect to user_name;
    -- Have connect Users with permissions == Has privileges
    -- alter table
    -- create table/index
    -- drop table/view/index
    -- grant
    -- revoke
    -- insert/select/update/delete
    -- audit/noaudit
    
-- Assign voting rights to users
grant create table to user_name;
	-- The user cannot successfully build the table at this time because there is no table space operation right
alter user user_name quota size on table_space_name;
	-- table_space_name Generally users
	
-- take select Permissions assign a role
grant select on table_name to (Role Name);
	-- take select Permissions are given to everyone
grant select on table_name to public;

Before giving users the right to connect, create users:
create user user_name identified by password

If you use a version of Oracle, you need to bring C##before your user name
Write as C##user_name

Note the difference between create table permission and create any table permission

revoke

-- Retract user's right to form
revoke create table from user_name;

deny

Common SQL statements

wildcard

• "%" Match any string
  

• "_" Match any character
  

If you need to query for wildcards, for example, find names with "u" Data

-- escape Provide the escape character as'\'
select sname from Student where sname like '%\_%' escape '\';

order by

• ASC - Ascending
• DESC - Descending

group by

Aggregate function

• avg

• sum

• min

• max

• count

  count (distinct column_name)

having

Used after group by clause to filter the result values of aggregate functions

block nesting

insert into Student() 
	select column_name from table_name where 
-- select Clause column and Student()Corresponding

Connect

Internal connection

External Connection

View Constants

-- Oracle
	-- Oracle Constant in dual in
	select sysdate from dual; 
	
-- SQL Server
	select getdate();

User Input

select * from table_name where column_name=&temp;
-- &temp Input for User

PL/SQL

Anonymous Block-Test Program

DDL cannot be used directly

declare
	variable
	-- The variable assignment statement is var_name:=constant
begin
	program
	-- "<>" "!=" Not equal to
	-- "||" Connect
	-- "and" also
	-- "or" perhaps
exception
	when then
end;

if then - elsif then - else - end if;

if then
elsif then
else
end if;

loop - end loop

-- loop
loop
	exit when -- End cycle
end loop;

-- while
while () loop
end loop;

-- for
for var in [] loop
end loop;

Common Functions

Output one line: dbms_output.put_line();

dbms_output.put_line('muhuai');

Output without empty lines: dbms_output.put()

Byte padding: rpad();

dbms_output.put_line(rpad('muhuai',number,char_c));
-- number For the character length of the output line, char_c For Fill Character

cursor

Explicit Cursor

declare cursor

( Cursor and Query Associations

cursor cursor_name is select 

Use cursors

-- open
open cursor_name;
close cursor_name;

-- Traversing cursors
-- A variable of cursor type is required
var_cursor cursor_name%rowtype;
fetch cursor_name into var_cursor;
-- At this point the cursor places the first data var_cursor

open cursor_name;
	fetch cursor into var_cursor;
	while cursor%found loop
		-- Loop ends when cursor convenience ends
	end loop;
close cursor_name;

select for update

Implicit Cursor

Cannot open/close/loop/fetch

Cursor reset after commit operation

sql%found -- Description statement modified to line
sql%notfound -- Description statement not modified to line
sql%rowcount -- Number of lines modified by statement

Dynamic Cursor-Cursor Variable-Weak Cursor

Cursor specific query statements are not defined in the declare block, only cursor names are defined in the declare block, and sql statements are defined in the begin block

declare
	type ref_cursor_name is ref cursor; -- Define Variables
	var_ref_cursor ref_cursor_name; -- Dynamic Cursor
begin
	open var_ref_cursor for select ; -- Open Dynamic Cursor
	-- At this point the cursor is var_ref_cursor
	-- Traverse dynamic cursors in the same way as traversing cursors
	close var_ref_cursor;
end;

stored procedure

Requires call through main program (test program call)

The procedure segment needs to be run and saved before the main program can be called

create or replace procedure procedure_name 
[(var_in in var_type,var_out out var_type)] is
	-- Variable Definition
begin
	-- program
exception
end;

-- Test Program Call
declare
begin
	procedure_name[(var_in,var_out)];
	-- perhaps
	procedure_name[(var_1=>var_in,var_2=>var_out)];
end;

function

Requires call through main program (test program call)

The procedure segment needs to be run and saved before the main program can be called

create or replace function function_name
[(var_in in var_type)] [return return_type] is
begin
	-- If there is return，Then it must return
end;

package

Requires call through main program (test program call)

The procedure segment needs to be run and saved before the main program can be called

Create--Block--Package

The header and package are not in the same block, but the header and package names are guaranteed to be the same

-- Baotou
create or replace package package_name is
	-- Variable Definition
	var var_type;
	cursor cursor_name is select ;
	procedure procedure_name
	(var_in in_type,var_out out out_type);
	function function_name(var_in in_type)
    return return_type;
end package_name;

-- Envelope
create or replace package body package_name as
	-- Package Variable Definition
begin
	-- Implementation of Header Programs and Functions
end package_name;

-- Test program
declare

begin
	var_1:=package_name.var;
	package_name.procedure_name();
	var_2:=package_name.function_name();
	open package_name.cursor_name;
	close package_name.cursor_name;
end;

SQL Development

Dynamic SQL - For DDL and indeterminate DML

execute immediate to execute SQL statements in PL/SQL

execute immediate
'create table table_name ···';

trigger

Similar to a procedure or function

Occurs on DML operations

Trigger name cannot be the same

-- Create trigger
create or replace trigger trigger_name
[before|after|instead of] [insert|delete|update]
on table_name for each row
begin
	if inserting then
	elsif updating then
	elsif deleting then
	end if;
end;

-- Delete trigger
drop trigger trigger_name;

-- prohibit|Open Trigger
alter trigger trigger_name disable|enable;

Pseudo Record

-- :new
-- delete Statement does not exist :new

-- :old
-- insert Statement does not exist :old

raise_application_error

-- Custom error type return
-- because Oracle Medium error code-20000 Previously occupied, so custom error code from-20000 start
-- Error code range [-20000,-20999]
if then
	raise_application_error(-20000,'Error Content');
end if;