Basic Oracle syntax

 

-- Table
Create Table Test (names varchar2 (12 ),
Dates date,
Num int,
Dou Double );
-- View
Create or replace view vi_test
Select * from test;

-- Synonym
Create or replace synonym AA
For dbusrcard001.aa;

-- Stored Procedure
Create or replace produce dd (v_id in employee. empoy_id % type)
As
Begin

End
Dd;

-- Function
Create or replace function EE (v_id in employee % rowtype) return varchar (15)
Is
Var_test varchar2 (15 );
Begin
Return var_test;
Exception when others then

End

-- Trigger Definition
Create or replace trigger FF
Alter Delete
On Test
For each row
Declare
Begin
Delete from test;
If SQL % rowcount <0 or SQL % rowcount is null then
Rais_replaction_err (-20004, "error ")
End if
End

Create or replace trigger gg
Alter insert
On Test
For each row
Declare
Begin
If: Old. Names =: New. Names then
Raise_replaction_err (-2003, "Duplicate encoding ");
End if
End

Create or replace trigger HH
For update
On Test
For each row
Declare
Begin
If updating then
If: Old. Names <>: New. Names then
Reaise_replaction_err (-2002, "the keyword cannot be modified ")
End if
End if
End

-- Define a cursor
Declare
Cursor AA is
Select names, num from test;
Begin
For BB in AA
Loop
If BB. Names = "oracle" then

End if
End loop;

End

-- Speed Optimization: the speed of the previous statement without the last statement is dozens of times faster
Select names, dates
From test, B
Where test. Names = B. Names (+) and
B. names is null and
B. Dates> date ('2017-01-01 ', 'yyyy-mm-dd ')

Select names, dates
From Test
Where names not in (select names
From B
Where dates> to_date ('2017-01-01 ', 'yyyy-mm-dd '))

-- Query duplicate records
Select names, num
From Test
Where rowid! = (Select max (rowid)
From Test B
Where B. Names = test. Names and
B. num = test. Num)

-- Search for the first 10 latest records in the test table
Select * from (select * from test order by dates DESC) Where rownum <11

-- Generation of serial numbers
Create sequence row_id
Minvalue 1
Max value 9999999999999999999999
Start with 1
Increment by 1

Insert into test values (row_id.nextval ,....)