Common functions and tools of Oracle

Time:2021-5-2

1、 Brief introduction of the author

First of all, I would like to introduce myself. In my traditional work, the author basically used Oracle in the first year, and the technology was very backward, but there was no way. At that time, my strength was really weak, and I needed to hone, and I also needed to provide myself with living expenses. Let’s not talk about more.

I believe many of my friends are like me. Understand the natural understand, to work hard to enhance their own is the key.

2、 Tool recommendation

Next, I will recommend some common Oracle functions and tools.

1. Common functions

 

select greatest(’11’,’5′) from dual;– Minimum value of string

select least(4,1) from dual;– Take the minimum number

select wm_ Concat (column name) from dual– Turn a column into a row

Rank () over (partition by grouping column name order by sorting column name DESC) — group and sort according to a column and rank

select listagg(o.serialno, ‘,’) within group(order by o.serialno)from REPORT_ Forms O — column wrap with comma

SELECT A.COL FROM A WHERE TRANSLATE(A.COL,’*0123456789′,’*’) IS NULL;– Filter a column of pure numbers (the column contains other strings and numbers):

select * from user_ tab_ comments a where a.table_ Name =’table name ‘– See table explanation

COMMENT ON TABLE b_ So is’ table explanation ‘– Explain the table when creating it

Substr (‘string ‘, – 8) — function to intercept string

  replace(‘V_ ENDRERATIOWAYQUO’,’*’,”); — Replace function with string

  Select substr (‘aaa-bbb ‘, 1, instr (‘aaa-bbb’, ‘-‘, – 1) – 1) value from dual– Intercepts all values before a string
  — Single column primary key
  Alter table name add constraint primary key name primary key (the primary key field to be set);
  — composite keys
  alter table ECS_STORE.TF_B_AIR_CONFIG add constraint TF_B_AIR_CONFIG_PK primary key (TYPE_ID , PROVINCE_CODE);

 

2. Create primary key auto increment

 

–Create primary key auto increment–

/*Step 1: create a table*/
create table t_user(
Id int primary key, – primary key, self growing
username varchar(20),
password varchar(20),
type varchar(20)
);

/*Step 2: create a custom sequence*/
CREATE SEQUENCE user_sequence
Increment by 1 — how many at a time
Start with 1 — count from 1
Nomaxvalue — do not set the maximum value
Nocycle — accumulate all the time, do not cycle
Nocache — no buffer

/*Step 3: set up trigger*/
create trigger mem_trig before
insert on t_user for each row when (new.id is null)
begin

select user_sequence.nextval into:new.id from dual;

end;

3. Creation of storage process

 

CREATE OR REPLACE PACKAGE PKG_RIS_RF IS
/*Stored procedure without parameters*/
— PROCEDURE WATCH_MONKEY;
/*Stored procedure with parameters*/
PROCEDURE PROC_CAL_RF101(start_date IN VARCHAR2, end_date IN VARCHAR2);
END PKG_RIS_RF;

 

/*Stored procedure with parameters*/
CREATE OR REPLACE PACKAGE BODY PKG_RIS_RF IS
/*
*Stored procedure with parameters
*/
PROCEDURE PROC_CAL_RF101(start_date IN VARCHAR2, end_date IN VARCHAR2) IS
/*Parameter declaration*/
— name VARCHAR2(12);
BEGIN
–Treatment body
name := ‘Hello Oracle!’;
–Exception handling
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line(‘CATCH EXCEPTIOIN’);
WHEN OTHERS THEN
DBMS_ OUTPUT.PUT_ Line (‘exception Code: ‘+ sqlcode’)– Sqlcode stands for exception code
DBMS_ OUTPUT.PUT_ Line (‘exception information: ‘+ sqlerrm’)– Sqlerrm stands for exception information
END PROC_CAL_RF101;
END PKG_RIS_RF;

4. Dblink creation

 

create public database link dblinkname connect to username identified by password
   using ‘(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = database_ip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =servicename)
)
)’;
Where the user name is the user name, the password is the password of the database, and the database_ IP is the database IP address and servicename is the service name.

 

5. Oracle shortcut key settings

(1) create a TXT text locally

 

 

(2) before the equal sign is the shortcut key you want to use in PLSQL. If you enter that shortcut key, you will output the statement after your equal sign. Here, my shortcut key statement will also be posted to you

iiv=insert into table values()
ii=insert into
up=update
se=select
fr=from
wh=where
de=delete
df=delete from
sf=select * from
scf=select count(1) from
pr=procedure
fu=function
us=update new_tablet set
dt=drop table new_table
ssf=select sum() from
saf=select AVG() from
to=to_date(”,’YYYY-MM-DD’)
co=count(1)
–Between the two data
bet=select * from table a where a.time between timemin and timemax

(3. PLSQL settings

 

Select the blue part of the above screenshot and click enter

 

Click the blue part above, select the txt file you created above, restart Oracle, and you can enjoy the shortcut.

 

 

 

Recommended Today

Looking for frustration 1.0

I believe you have a basic understanding of trust in yesterday’s article. Today we will give a complete introduction to trust. Why choose rust It’s a language that gives everyone the ability to build reliable and efficient software. You can’t write unsafe code here (unsafe block is not in the scope of discussion). Most of […]