【笔记】Oracle SQL语句 | 基础篇

整理了一下Oracle SQL的基本语句，主要针对Oracle的使用者（Oracle数据库维护和管理员的常用语句之后整理），可作为一个大纲参考，对某些语句或函数并未深入的详解，只是简单列出，留个印象，真正在实践中遇到问题时能够联想起来，再Google之即可。

作为刚接触学习Oracle的同学，掌握基本的操作语句（sqlplus环境常用命令，CRUD基本语句），进而进阶到PL/SQL编程（存储过程，函数等），差不多可以应付日常的应用需求，之后如果有工作需求或兴趣，可以再进一步学习数据库维护管理，语句优化，以及Oracle体系结构等知识。

  

----------1. SQL PLUS常用命令-------------
------1.1 连接数据库
sqlplus / as sysdba  ---本地操作系统认证，无需listener进程
sqlplus username/password  ---连接本地数据库，服务处于可用状态
sqlplus username/password@orcl  ---listener进程处于可用状态
sqlplus username/password@host:port/sid  ---无需tnsnames.ora配置

sqlplus /nolog
conn / as sysdba
conn username/password
conn sys/password as sysdba
conn username/password@orcl
conn sys/password@orcl as sysdba
conn username/password@host:port/sid

------1.2 打开/关闭数据库
startup [nomount|mount|open|force] [restrict] [pfile=filename]
shutdown [normal|transactional|immediate|abort]

------1.3 HELP命令
help st  ---模糊查询
help startup  ---完整查询
help index  ---命令清单

------1.4 SET设置运行环境
set echo on|off  ---列出命令
set serverout on [size n]|off  ---存储过程是否显示
set heading on|off   ---列标题
set pagesize 14   ---一页行数
set newpage 1   ---一页中空行数
set linesize 80   ---一行最多字符总数
set pause on|off|text   ---是否每页暂停
set numformate $999,999,999.00   ---数值默认格式

------1.5 格式化查询结果
---column
col empno format $999,999.00
col sal heading SALARY
col sal off|on
col sal null 'NULL'
col sal wrapped|word_wrapped
---ttitle/btitle
title center 'SALARY LIST'
btitle left 'DATE:2018.07.08'
title off
title on

------1.6 查看对象或变量
---desc
desc object;
#desc object   ---命令输入中间使用
---show
show all|parameters db_block_size|sga|spool|user;

------1.7 运行命令或脚本
edit/save/get filename
/   ---执行上一条语句或块
start/@ test.sql
---spool
spool [filename] [create|replace|append]
spool off/out

----------2. SQL语句基础-------------
------2.1 查询SELECT
/*
select {[distinct | all] column | *}
[into table_name]
from {table | views | other select}
[where conditions]
[group by columns]
[having conditions]
[order by columns]
*/
---简单查询
select a.rowid as RI,a.job,a.sal*(1+0.1),a.* from emp a;
---不重复记录
select distinct job from emp;
---筛选查询
select empno from emp where sal > 1500 or sal < 500;  --->,<,!,<>,>=,<= value|ANY/ALL(B)
select empno from emp where ename like 'L_\_%' escape ''; ---Li_ke; %,_,escape
select empno from emp where job in ('PRESIDENT','MANAGER');  ---not in
select empno from emp where job sal between 2000 and 3000;  ---not between A and B
select street_address from locations where state_province is null;
---分组查询
select job,avg(sal),sum(sal),max(sal),min(sal),count(job) 
from emp 
group by job
having avg(sal) > 2000---单列，分组列名or统计函数
select deptno,job,avg(sal),max(sal) from emp group by deptno,job
group by rollup(deptno,job);  ---多列，rollup/cube/grouping sets
---排序查询
order by sum(sal) desc;   ---asc
order by 3 desc;   ---asc

------多表关联查询SELECT
---表别名
select e.empno,e.ename,d.dname
from emp e,dept d
where e.deptno = d.deptno
and e.job = 'MANAGER';   ---执行顺序from，where，select
---内连接
select e.empno,e.ename,d.dname
from emp e join dept d
on e.deptno = d.deptno;
---外连接
select e.empno,e.ename,d.dname
from emp e left|right|full join dept d
on e.deptno = d.deptno;  ---A∪(A∩B),(A∩B)∪B,A∪B-A∩B
---自然连接
select empno,ename,job,dname
from emp natural join dept
where sal > 2000;  ---相同名称的列自动连接
---自连接
select em2.name as Manager, em1.name as Employee
from emp em1 left join emp em2
where em1.mgr=em2.empno
order by em1.mgr;  ---上下级关系或层次关系
---交叉连接
select count(*) from dept cross join emp;  ---笛卡尔积

------子查询SELECT
select empno from emp
where sal > (select min(sal) from emp);  ---单行
select empno from emp
where deptno in (select deptno from dept where dname<>'SALES');  ---多行 ANY/ALL
select empno from emp f
where sal > (select avg(sal) from emp where job = f.job);  ---关联
      
------2.2 新增INSERT
insert into dept(deptno,dname,loc) values(88,'design',''beijing);  ---单条
accept salary prompt 'Please input salary';
insert into jobs values('PRO','IT',&salary,DEFAULT);  ---替代变量，default值
insert into jobs_temp select * from jobs where jobs.max_salary > 10000;  ---批量

------2.3 更新UPDATE
update emp set sal = sal*1.2 where job = 'SALESMAN';
update emp set job = DEFAULT where ename = 'SCOTT';
update emp set sal = (select avg(sal) from emp where job = 'MANAGER');

------2.4 删除DELETE/TRUNCATE
delete from jobs where job_id = 'PRO';
truncate table jobs_temp;

------2.5 常用系统函数
---字符类
--ASCII/CHR,CONCAT/||,INITCAP,INSTR,LENGTH,
--LOWER/UPPER,LTRIM/RTRIM/TRIM,REPLACE,SUBSTR
---数字类
--ABS,CEIL,COS,EXP,FLORR,LOG,MOD,POWER,ROUND,SIGN,SIN,SQRT,TRUNC
---日期/时间类
--ADD_MONTHS,LAST_DAY,MONTHS_BETWEEN,NEW_TIME,SYSDATE
---转换类
--CHARTORWIDA,CONVERT,ROWIDTOCHAR,TO_CHAR,TO_DATE,TO_NUMBER,NVL
---聚合类
--AVG,COUNT,MAX,MIN,SUM,VARIANCE,STDDEV

----------3. PL/SQL编程-------------
------3.1 块结构
declare
  a number(7,2):=999.99;  --数字类型decimal,double,int,numeric,binary_integer,pls_integer
  b varchar2(10);  --字符类型char,long,nchar,nvarchar2
  c date;  --日期类型
  d emp.job%type;  --%type类型
  e emp%rowtype;  --%rowtype类型
  type ftype is record
  (
       f1 number(7,2),
       f2 varchar2(10)
  );
  f ftype;  --record类型
  g boolean;  --布尔类型
  con_h constant int:=1500;  --常量
  ---
  i int:=0;
  sum_i int:=0;
  ---
  cursor cur_emp(var_job in varchar2:='SALESMAN')
  is select empno,ename,sal from emp where job=var_job;  --定义游标
  type record_emp is record
  (
    var_empno emp.empno%type,
    var_ename emp.ename%type,
    var_sal   emp.sal%type
  );
  emp_row record_emp;
  cursor cur_emp2 is select * from emp where deptno = 30;
  ---
  primary_iterant exception;
  pragma exception_init(primary_iterant,-00001); --自定义错误编号异常
  sal_exception exception;  ---自定义业务逻辑异常
begin
  --基本类型
  select sal,ename,sysdate,job into a,b,c,d
  from emp where empno = 7499;
  dbms_output.put_line('Date:'||c||','||b||' job:'||d||',salary:'||a);
  --%rowtype类型
  select * into e
  from emp where empno = 7499;
  dbms_output.put_line(e.ename||' empno:'||e.empno);
  --%record类型
  select sal,ename into f
  from emp where empno = 7499;
  dbms_output.put_line(f.f2||' salary:'||f.f1);
  --选择语句if
  g:=a<con_h;
  if g then
    dbms_output.put_line('salary '||a||'<'||con_h);
  elsif a>con_h then
    dbms_output.put_line('salary '||a||'>'||con_h);
  else
    dbms_output.put_line('salary '||a||'='||con_h);
  end if;
  --选择语句case
  case g
    when true then
      dbms_output.put_line('salary '||a||'<'||con_h);
    when false then
      dbms_output.put_line('salary '||a||'>='||con_h);
    else
      dbms_output.put_line('Can not Happen!');
  end case;
  --循环语句loop
  loop
    i:=i+1;
    sum_i:=sum_i+i;
    exit when i=100;
  end loop;
  dbms_output.put_line('loop:sum of first 100 num is:'||sum_i);
  --循环语句while
  i:=0;
  sum_i:=0;
  while i<=99 loop
    i:=i+1;
    sum_i:=sum_i+i;
  end loop;
  dbms_output.put_line('while:sum of first 100 num is:'||sum_i);
  --循环语句for
  sum_i:=0;
  for i in reverse 1..100 loop
    sum_i:=sum_i+i;
  end loop;
  dbms_output.put_line('for:sum of first 100 num is:'||sum_i);
  ---显式游标
  open cur_emp('MANAGER'); --打开游标
  fetch cur_emp into emp_row;  --获取当前游标记录
  while cur_emp%found loop  --%notfound,rowcount,isopen
    dbms_output.put_line(emp_row.var_ename||' empno:'||emp_row.var_empno||' salary:'
                       ||emp_row.var_sal);
    fetch cur_emp into emp_row;  --指向下一记录
  end loop;
  close cur_emp;  --关闭游标
  --隐式游标
  update emp set sal=sal*1.01 where job = 'SALESMAN';
  if sql%notfound then
    dbms_output.put_line('No record update!');
  else
    dbms_output.put_line(sql%rowcount||' record updated!');
  end if;
  --for循环游标
  for emp_record in cur_emp
  loop
    dbms_output.put_line(emp_record.empno||' empno:'||emp_record.ename||' salary:'
                       ||emp_record.sal);    
  end loop;  --显示
  for emp_record in (select empno,ename,sal from emp where job = 'SALESMAN')
  loop
    dbms_output.put_line(emp_record.empno||' empno:'||emp_record.ename||' salary:'
                       ||emp_record.sal);
  end loop;  --隐式
  ---异常处理
  select * into e from emp; --where empno = 7499;
  insert into dept values(10,'Software','SZ');
  if e.sal > con_h then
    raise sal_exception;
  end if;  
exception
  when too_many_rows then
    dbms_output.put_line('too many rows!');  --预定义异常
  when primary_iterant then
    dbms_output.put_line('duplicate record!');  --自定义错误编号异常
  when sal_exception then
    dbms_output.put_line('sal is high!');  --自定义业务逻辑异常
end;
/

------3.2 存储过程
---创建
create or replace procedure square(
  numb in out number default 100,
  flag in boolean) 
is  --as
  i int:=2;  --内部变量
begin
  if flag then
    numb:=power(numb,i);  --平方
  else
    numb:=sqrt(numb);  --开方
  end if;
exception
  when others then
    dbms_output.put_line('Error!');
end;
/

---调用
declare
 var_number number;
 var_temp number;
 boo_flag boolean;
begin
 var_temp:=3;
 var_number:=var_temp;
 boo_flag:=false;
 square(var_number,boo_flag);  --调用
 dbms_output.put_line(var_number);
end;
/ ---单独调用可以在sqlplus中使用exec命令

---删除
drop procedure square;

------3.3 函数
--创建
create or replace function get_avg_pay(num_deptno number) return number --必须有返回值
is
  num_avg_pay number;  --内部变量
begin
  select avg(sal) into num_avg_pay from emp where deptno=num_deptno;--获取某个部门的平均工资
  return(round(num_avg_pay,2));--返回平均工资
exception
  when no_data_found then --若此部门编号不存在
    dbms_output.put_line('该部门编号不存在');
    return(0); --返回平均工资为0
end;
/

--调用
declare
  avg_pay number;
begin
  avg_pay:=get_avg_pay(10);
  dbms_output.put_line('平均工资:'||avg_pay);
end;
/

--删除
drop function get_avg_pay;

------3.4 触发器
--创建
create or replace trigger tri_dept
  before insert or update or delete ---after/instead of
  on dept ---语句级触发器  
  ---for each row 行级别触发器
  ---替换触发器instead of主要针对视图
declare
  var_tag varchar2(10);
begin
  if inserting then
    var_tag := 'Insert';
  elsif updating then
    var_tag := 'Update';
  elsif deleting then
    var_tag := 'Delete';
  end if;
  insert into dept_log values(var_tag,sysdate);--向日志表中插入对dept表的操作信息
end tri_dept;
/

--触发
insert into dept values(66,'Sales','SH');
update dept set loc='SY' where deptno = 66;
delete from dept where deptno = 66;

--删除
drop trigger tri_dept;

------3.5 程序包
---程序包
create or replace package pack_emp is
  function fun_avg_sal(num_deptno number) return number;
  procedure pro_regulate_sal(var_job varchar2,num_proportion number);
end pack_emp;
/

---程序包主体
create or replace package body pack_emp is
  function fun_avg_sal(num_deptno number) return number is  --引入“规范”中的函数
    num_avg_sal number;--定义内部变量
  begin
    select avg(sal)
    into num_avg_sal
    from emp
    where deptno = num_deptno;--计算某个部门的平均工资
    return(num_avg_sal);--返回平均工资
  exception
    when no_data_found then--若未发现记录
      dbms_output.put_line('该部门编号不存在雇员记录');
    return 0;--返回0
  end fun_avg_sal;

  procedure pro_regulate_sal(var_job varchar2,num_proportion number) is--引入“规范”中的存储过程
  begin
    update emp
    set sal = sal*(1+num_proportion)
    where job = var_job;--为指定的职务调整工资
  end pro_regulate_sal;
end pack_emp;
/

---删除
drop package pack_emp;

注：部分SQL语句来源于《Oracle 11g从入门到精通（第2版）》——清华大学出版社