"Go" ORACLE SQL Foundation-ddl language of rites eight mesh 2017-12-23 21:26:21

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