Practical application of Oracle9i analysis functions

We may encounter many headaches in Oracle commands and some functions, but sometimes we cannot solve this problem, we recommend that you use the Oracle9i analysis function to solve related problems in order to relieve your difficulty in sorting out Oracle commands and some functions.

SQL * PLUS command SQL programming manual

Oracle9i analysis functions

Recommendation circle: Database circle

1. More related recommendations -------------------------> 01

 
 

  
  
set linesize 500  
  
  
set pagesize 100  
 
 

Run or r or (Symbol)/------------> both indicate re-executing the previous command.

Alter session set NLS_date_format = 'yyyy-MM-DD '; modify a user session to modify the Date Format

The default date format is: 'dd-MON-YY'

 
 

  
  
SQL> insert into scott.emp(empno,ename,sal) 
values(&employeeid,'&name',&employeesal); 
 
 

Enter the value of employeeid: 9001

Enter the value of name: jiake02

The value of employeesal is 4000.

Original Value 2: values (& employeeid, '& name', & employeesal)

New Value 2: values (9001, 'jiake02', 4000)

This command can be used multiple times during the Oracle9i function analysis, such as pressing the symbol/And then pressing the Enter key to repeatedly enter the value.

Use subqueries in insert statements. For example

 
 

  
  
insert into managers(id,name,salary,hiredate)  
  
  
select empno,ename,sa,hiredate  
  
  
from emp  
  
  
where job='manager';  
 
 

II ------------------> 02

1. Each SQL statement in Oracle is a transaction. When commit or rollback is used to end the transaction

2. savepoint update_dept; set the save point;

You can use the rollback to update_dept; Method to Solve the Problem of step-by-step processing or selective execution;

Orcale connects two strings and uses two |, while SQL uses &

% Represents Multiple Arbitrary characters, and _ represents one arbitrary character.

Is null is used to determine whether it IS NULL

Iii. lesson ---------------------> 03

SQL functions:

Single Row function: ---->

 
 

  
  
General/Character/Number/Date/Conversion 
 
 

1. Character Functions

Conversion Function ---->

 
 

  
  
LOWER select lower(ename) from scott.emp; 
 
 

/UPPER select * from scott. emp where ename = upper ('King ');

The first letter of/INTCAP is in upper case, and the other letters are in lower case.

 
 

  
  
SQL> select initcap(ename) from scott.emp;  
  
  
INITCAP(EN  
  
  
Smith  
 
 

The above content is an introduction to some of Oracle9i analysis functions.