Original address: https://www.toutiao.com/i6502733303550837261/
The SQL language is divided into: DDL data Definition Language, DML data manipulation language, DCL is database control language, TC transaction Control Language
*DDL data definition Language: is the language in the SQL language set responsible for data structure definition and database object definition, consisting of the Create, alter and drop,rename,truncate several grammars.
*DML data Manipulation Language, which allows the user to implement basic operations on the database, to query, add, modify, and delete the data in the database, by Select,insert,update, The delete four syntax consists of.
*DCL Database Control Language (Data Control Language): Statements that are used to set or change permissions for a database user or role, including (Grant,revoke, etc.) statements.
*TC Object Control Language (Transaction control): Used to modify data permanently stored in a table or to cancel these modifications, including (Commit,rollback,savepoint) statements.
*** DDL language * * *
Used for database object creation, modification, deletion; database, data tables, database indexes, stored procedures, user functions, triggers, or user self-training objects, etc., can be processed using the create,alter,drop instruction, and for various database objects, the directive has many parameters.
----Renaming table names
Reanme Old_name to New_name;
----creating tables and adding primary KEY constraints
Method 1:
CREATE TABLE INFOR (SID VARCHAR2 (20),
PID VARCHAR2 (10),
Money number (12,2));
ALTER TABLE INFOR
ADD CONSTRAINT pk_info PRIMARY KEY (SID);
Method 2:
CREATE TABLE infors (SID VARCHAR2 () not NULL CONSTRAINT pk_infors PRIMARY KEY,
PID VARCHAR2 (10),
Money number (12,2));
----Create a new table as needed from an existing table
CreateTable infors as (SELECT B.compcode as unit code,
B.compname as unit name,
A.persid as personnel code,
A.perid as ID number,
A.pername as personnel name,
(Case A.persex
When ' 1 ' Then ' men '
When ' 2 ' then ' women '
ELSE ' not stated '
END) as Personnel gender
From Perinfor a,compinfor B
WHERE A.compcode = B.compcode
and B.compzrea = ' 150XXX ');
Append table fields----Modify tables
ALTER TABLE INFOR ADD change_money number (12,2);
----Modify the name of a table field
ALTER TABLE INFOR RENAME COLUMN money to moneys;
----Modify the data type or data length of a table field
ALTER TABLE INFOR MODIFY Moneys VARCHAR2 (12);
----Delete a table
DROP TABLE infors;
----Delete a field from a table
ALTER TABLE INFOR DROP COLUMN Change_money;
----Create users and Passwords
Create user test identified by password;
----Create a user and specify a tablespace
Select Username,default_tablespace from Dba_users;
Create userusernameidentified by PASSWORD
Default Tablespacedata
Temporaray tablespacetemp;
Create user nmsi_cf identified by nmsi_cf_57g default Tablespace data_nmsi;
Create user drm_nm identified by drm_nm default Tablespace data_nmsi_drm;
Create user Nm_report identified by nm_report default Tablespace data_nmsi_drm;
Dropuser CFDXPT CASCADE;
----Modifying a user's tablespace
Alter user NMSI_CF default Tablespace data_nmsi;
Alter user drm_nm default Tablespace data_nmsi_drm;
Alter user nm_report default Tablespace data_nmsi_drm;
----Delete a user
Drop user test;
----Modify User passwords
Alter user test identified by Password1;
----Modify the user's lock mode
Alter user test account unlock;
----Create roles and passwords
CREATE ROLE China identified by PASSWORD;
----Remove a role
DROP ROLE China;
----Modifying role passwords
Alterrole China Identifiedby PASSWD;
----Create an index
Normal index//
CREATE INDEX index_name on table_name (COLUMN_NAME);
Unique index///
CREATE UNIQUE INDEX index_name on table_name (COLUMN_NAME);
Specify the attribution table space for the index//
CREATE INDEX index_name on table_name (column_name) tablespace tab_space;
--The foreign key and the primary key are associated
Select A.owner as primary key owner,
A.table_name as primary key table,
B.column_name as primary key column,
C.owner as foreign key owner,
C.table_name as foreign key table,
D.column_name as foreign key column
From User_constraints A
Left JOIN User_cons_columns b
On a.constraint_name = B.constraint_name
Left JOIN User_constraints C
On c.constraint_name = A.constraint_name
Left JOIN User_cons_columns D
On c.constraint_name = D.constraint_name
and A.constraint_type = ' P '
and a.table_name = ' table name '--the table that needs to see the primary foreign key relationship
Order BY A.table_name;
--Delete FOREIGN key cascade
ALTER TABLE us_role_connection DROP constraint fk_ro_u_id;
----Modifying indexes
Rename index///
ALTER INDEX old_index_name RENAME to New_index_name;
----Delete an index
DROP INDEX index_name;
----Delete primary key
1 Determines whether the primary key has a foreign key, if any, executes the following, without executing the 2nd statement
ALTER TABLE TABLENAME DROP constraint column cascade;
2 Invalid primary key defined
ALTER TABLE tablename disable Primary_column;
3 Deleting an index
Drop index index_name;
4 or directly perform a delete primary key
Altertable TABLENAME Dropprimarykey;
----Create a View
Create a normal view//
CreateView One_view as
SelectCount (AAC001) as number
From ac01_00;
Create a view that detects if a where condition is met, and only if the Where condition is met to use DML statements for views//
Createorreplaceview One_view as
SelectCount (AAC001) as number
From ac01_00
where AAB001 in (SELECT AAB001 from ab01_00 WHERE AAB301 = ' 150499 ')
Withcheckoptionconstraint One_view_dml;
Create a read-only view that cannot be DML-language views//
Createorreplaceview One_view as
SelectCount (AAC001) as number
From ac01_00
where AAB001 in (SELECT AAB001 from ab01_00 WHERE AAB301 = ' 150499 ')
Withreadonlyconstraint One_view_dml;
----Modifying views
Alterview One_view
Dropconstraint One_view_dml;
----Delete a view
DROPVIEW One_view;
A simple application of----function to determine the time format of a character type
CREATE OR REPLACE Function < function name >
[< parameter list;]
[return< function data type;] Is|as
[< local variable declaration;]
BEGIN
< process body >
return< function Value >
end[< Function Name >];
CREATE [OR REPLACE] FUNCTION function_name
{(Parameter_name [in| Out| In out] type [, ...]}
RETURN type
[IS | As]
BEGIN
Function_body
END function_name;
Create a function//
--Create a function, function name (check_date), specify the parameter name (p_date) and data type to pass to the function, and specify the data type returned by the parameter.
Createorreplacefunction check_date (p_date VARCHAR2) Returnnumberis
--Define variables and data types for passing parameters
V_date DATE;
--Start function body
BEGIN
--variable assignment and the data format of the specified checksum, the column of the check returns fault when empty
V_date: = To_date (NVL (p_date, ' FAULT '), ' yyyy-mm-dd HH24:MI:SS ');
--Returns 1 when matched against
RETURN1;
--exception
EXCEPTION
--If it returns 0 for other cases
Whenothersthen
RETURN0;
--End Function
END check_date;
Detection function//
SELECT check_date (Join_time)
From Infors;
OR
SELECT *
From Hsqinfor
WHERE check_date (AAC006) = ' 1 ';
Delete a function//
Dropfunction check_date;
----Create a sequence
CREATE SEQUENCE < sequence name >
MINVALUE < values >--Define minimum values
Nomaxvalue--Do not set the maximum value
Start with < value >--define sequence values from several beginnings
INCREMENT by < value >--Define sequence interval values
Nocycle--Do not set the loop
Cache < value >;--definition tells the buffer size
CreateSequence Sequence_name
[MAXVALUE max_num | Nomaxvalue]
[MINVALUE Min_num | Nominvalue]
[Startwith start_num]
[IncrementBy Increment_num]
[CYCLE | Nocycle]
[CACHE Cache_num | NOCACHE]
[ORDER | Noorder];
Create sequence Seq_01
Increment by 1--+1 per time
Start with 37-starting from 37 +
Nomaxvalue--Do not set the maximum value
Nocycle------always accumulate without circulation
Cache 20;
----Modifying a sequence
Alter Sequence seq_01 increment by 1;
----Delete a sequence
DROP SEQUENCE seq_01;
----use sequences and triggers to import data or interpolation into a table
Reset sequence
1, select Seq_name.nextval from dual; Assuming the results are 5656
2, altersequence seq_name incrementby-5655; Note Yes-(n-1)
3, select Seq_name.nextval from dual;//again, walk, reset to 1
4, altersequence seq_name incrementby1;//Restore
You can write a stored procedure, the following is the complete stored procedure, and then call the parameters:
Createorreplaceprocedure Seq_reset (V_seqname varchar2) as n number (10);
TSQL VARCHAR2 (100);
Begin
ExecuteImmediate ' SELECT ' | | v_seqname| | '. Nextval from dual ' into N;
n:=-(n-1);
tsql:= ' alter sequence ' | | v_seqname| | ' increment by ' | | N
ExecuteImmediate tsql;
ExecuteImmediate ' SELECT ' | | v_seqname| | '. Nextval from dual ' into N;
tsql:= ' alter sequence ' | | v_seqname| | ' increment by 1 ';
ExecuteImmediate tsql;
End Seq_reset;
Method 1:
Create a Table//
CreateTable tabname (VID integernotnullconstraint pk_tabname PRIMARYKEY,
NAMES VARCHAR2 (20),
per_id VARCHAR2 (18));
Create a sequence//
CreateSequence seqvid–-sequence Name
incrementby1–-per +1
startwith1--starting from 1
Nomaxvalue; --Non-cyclic
Create a trigger//
CREATE [Orreplace] trigger< trigger name >
[before| after]< trigger event >ON< table name >
Foreachrow
BEGIN
<pl/sql Program Body >
end< trigger name >;
CREATE [Orreplace] TRIGGER trigger_name
[before| After]trigger_event
On table_name
[Foreachrow]
BEGIN
Trigger_body
END trigger_name;
Createorreplacetrigger trvid–-Create or replace trigger, trigger name Trvid
Beforeinserton tabname--when inserting an tabname table
Foreachrow--declared as row-level triggers
Begin--Trigger start
SELECT Seqvid. Nextval into:new. VID from DUAL; --Running body
END Trvid; --End OF Trigger
Interpolation test///
Insertinto tabname
(NAMES, per_id)
Selectnames, per_id
Frominfors
whereper_id = ' 150XXXXXXXXXXXXXXX ';
Check//
SELECT * from TabName;
disabling and enabling triggers//
ALTER TRIGGER Trvid DISABLE;
ALTER TRIGGER Trvid ENABLE;
ALTER TABLE tabname DISABLE all TRIGGER;
ALTER TABLE tabname ENABLE all TRIGGER;
Delete a trigger, delete a sequence//
DROP TRIGGER Trvid;
DROP SEQUENCE Seqvid;
DROP TABLE tabname;
Method 2:
Sequence///
CREATE SEQUENCE seqname–-sequence Name
INCREMENT by 1–-+1 per time
Start with--starting from 1
MAXVALUE 99999999– maximum value;
Trigger//
Create Trigger Trname before insert
On ryxx_22
For each row
Declare
Integrity_error exception;
errno integer;
ErrMsg char (200);
Dummy integer;
Found Boolean;
Begin
--Column lcId uses sequence Sequence_lotcateid
Select Seqname.nextval INTO:new.xh from dual;
--Errors handling
exception
When Integrity_error Then
Raise_application_error (errno, errmsg);
End
----creation Process
CreateTable infors (per_id VARCHAR2) Notnull,
pro_id VARCHAR2 (4) Notnull,
Pro_name VARCHAR2 (Notnull),
Pro_money number (12,2) DEFAULT ' 0 ');
Insertinto infors VALUES (' 1 ', ' 1001 ', ' Ginseng fruit ', ' 200 ');
Insertinto infors VALUES (' 2 ', ' 2001 ', ' Flat peach ', ' 180 ');
Insertinto infors (per_id,pro_id,pro_name) VALUES (' 3 ', ' 3001 ', ' Ganoderma lucidum ');
SELECT *
From Infors;
CREATE [Orreplace] procedure< process name >
[< parameter list;]
[IS | As]
[< local variable declaration;]
BEGIN
< process body >
end[< Process Name >];
CREATE [Orreplace] PROCEDURE procedure_name
[(Parameter_name [In | Out | INOUT] Type [,...])]
[IS | As]
BEGIN
Procedure_body
END Procedure_anme;
----Create or replace the process update_product_infors, set 2 parameters, one for the product ID, one for the product price adjustment multiplier
Createorreplaceprocedure update_product_infors (p_pro_id in Infors.pro_id%type,
P_pro_money Innumber) as
----declaring variables
V_infors_count INTEGER;
----process starts
BEGIN
----statistics specify the number of product IDs
SelectCount (*)
Into V_infors_count
From Infors
WHERE pro_id = p_pro_id;
-----If the product exists, then execute the UPDATE statement to modify the price of the product, and then submit
IF V_infors_count = 1THEN
UPDATE infors
SET Pro_money = Pro_money * P_pro_money
WHERE pro_id = p_pro_id;
COMMIT;
ENDIF;
----If an exception occurs, the rollback is performed
EXCEPTION
Whenothersthen
ROLLBACK;
----End Process
END update_product_infors;
--Errors in the validation process in command Window--
Createorreplaceprocedure update_product_infors (p_pro_id in Infors.pro_id%type,
P_pro_money Innumber) as
V_infors_count INTEGER;
BEGIN
SelectCount (*)
Into V_infors_count
From Infors
WHERE pro_id = p_pro_id;
IF V_infors_count = 1THEN
UPDATE infors
SET Pro_money = Pro_money * P_pro_money
WHERE pro_id = p_pro_id;
COMMIT;
ENDIF;
EXCEPTION
Whenothersthen
ROLLBACK;
END update_product_infors;
/
----Show Process errors
ShowErrors;
--Call process--
SELECT * from infors WHERE pro_id = ' 1001 ';
Call Update_product_infors (1001,2);
SELECT * from infors WHERE pro_id = ' 1001 ';
---delete process--
Dropprocedure update_product_infors;
"Go" ORACLE SQL Foundation-ddl language of rites eight mesh 2017-12-23 21:26:21