【笔记】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版）》——清华大学出版社
