一：问题排查
1、锁表解决
select object_name, machine, s.SID, s.SERIAL#, user#, username from gv$locked_object l, dba_objects o, gv$session s where l.OBJECT_ID = o.OBJECT_ID and l.SESSION_ID = s.SID; alter system kill session '521,35494';--杀掉对应锁表进程
2、查看sql进程
SELECT a.username, a.machine, b.sql_id, b.SQL_FULLTEXT FROM v$session a, v$sqlarea b WHERE a.sql_address = b.address AND a.SQL_HASH_VALUE = b.HASH_VALUE;--查看sql进程
3、查看sql日志
select s.SQL_TEXT 语句, s.SQL_FULLTEXT 完成语句, s.CPU_TIME cpu总耗时, s.ELAPSED_TIME 执行总耗时, s.EXECUTIONS 执行次数, s.FIRST_LOAD_TIME 初次加载时间, s.LAST_LOAD_TIME 最后加载时间, s.LAST_ACTIVE_TIME 最后执行时间 from v$sqlarea s where s.SQL_TEXT like '%ent_query_result%';--查看sql执行情况，v$sql表有同样的信息
二：查询
1、关联查询
--笛卡尔积 select * from tableA,tableB;--查出两个表的所有可能（结果积=A列*B列） where 条件可以筛选所有想得到的结果积，内连接，外连接，全连接，自然链接只是语法的适配，得到更易理解的结果，也有效率的提升。 --内连接 select * from tableA A inner join tableB B on A.ID = B.ID;--得到交集，同名列都会展示，自然连接则会去重 --自然链接 select * from tableA A natural join tableB B;--以表列名为连接条件（多个相同则全部比较），值相同的为结果集，where 条件不可以使用连接列 --外连接 select * from tableA A left join on tableB B on A.ID = B.ID;--左外链接 select * from tableA A,tableB B where A.ID = B.ID(+);--左外链接 select * from tableA A right join on tableB B on A.ID = B.ID;--右外链接 select * from tableA A,tableB B where A.ID(+) = B.ID;--右外链接 --全外连接 select * from tableA A full outer join tableB B on A.ID = B.ID;--得到并集
2、sql函数
列转行函数
pivot( max(query_count) for query_reason in ( '01' as reason1, '02' as reason2, '03' as reason3, '04' as reason4 ) tips： max(query_count) ：query_count要填充到新列上的数据字段 for query_reason ：query_reason要转成列的字段 in (exper) ：exper对要转成列的每一个值指定一个列名 注意：pivot函数转换时，需指定max(列) 、for 列，其他列会以group by的形式新列追加到转换后的表。 举例： 要转换数据：为表text select 22 query_count, '01' query_reason, '160000' bch_cde from dual union all select 29 query_count, '02' query_reason, '160002' bch_cde from dual union all select 90 query_count, '02' query_reason, '160000' bch_cde from dual union all select 90 query_count, '26' query_reason, '160000' bch_cde from dual union all select 12 query_count, '26' query_reason, '160010' bch_cde from dual 要求：以query_reason为新列，query_count为新列数据转换 select * from test pivot(max(query_reason) for query_reason in( '01' as queryreason01, '02' as queryreason02, '26' as queryreason26)) t; 结果： bch_cde queryreason01 queryreason02 queryreason26 160000 22 90 90 160002 29 160010 12
其他函数
--排序函数 rank() over 查出指定条件后的进行排名，指定列相同，空出对应名次 dense_rank() over 查出指定条件后的进行排名，指定列相同，不空出对应名次 row_number() over 查出指定条件后的进行排名，指定列相同，也会进行排序 demo： select name,subject,score,rank() over(partition by subject order by score desc nulls last) ran from student_score;--nulls last解决rank() over时null值最大问题 --自关连函数 start with connect by prior（递归算法） select bch_cde from s_bch start with bch_cde = '初始条件' connect by prior bch_cde = bch_sup_cde; --字符函数 ASCII(X) --返回字符X的ASCII码 CONCAT(X,Y) --连接字符串X和Y INSTR(X,STR[,START][,N) --从X中查找str，可以指定从start开始，也可以指定从n开始 LENGTH(X) --返回X的长度 LOWER(X) --X转换成小写 UPPER(X) --X转换成大写 INITICAP(char) --把每个字符串的第一个字符换成大写 SELECT INITICAP ('mr.ecop') FROM TABLE_NAME; Mr.Ecop LTRIM(X[,TRIM_STR]) --把X的左边截去trim_str字符串，缺省截去空格 RTRIM(X[,TRIM_STR]) --把X的右边截去trim_str字符串，缺省截去空格 TRIM([TRIM_STR FROM]X) --把X的两边截去trim_str字符串，缺省截去空格 lpad(string,pad_len[,pad_str]) --从左边对字符串使用指定的字符进行长度填充,pad_str省略时填充空格 rpad(string,pad_len[,pad_str]) --从右边对字符串使用指定的字符进行长度填充,pad_str省略时填充空格 REPLACE(X,old,new) --在X中查找old，并替换成new SUBSTR(X,start[,length]) --返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾 LENGTH(char) --求字符串的长度 SELECT LENGTH ('ACD') FROM TABLE_NAME; 3 LENGTHB(char) --求字符串的字节长度，即字符串在字段中占字节长度 SELECT LENGTH ('ACD') FROM TABLE_NAME; 3 || --并置运算符 SELECT 'ABCD'||'EFGH' FROM TABLE_NAME; ABCDEFGH --数字函数 ABS(X) --X的绝对值 ABS(-3)=3 ACOS(X) --X的反余弦 ACOS(1)=0 COS(X) --余弦 COS(1)=0.54030230586814 CEIL(X) --大于或等于X的最小值 CEIL(5.4)=6 FLOOR(X) --小于或等于X的最大值 FLOOR(5.8)=5 LOG(X,Y) --X为底Y的对数 LOG(2，4)=2 MOD(X,Y) --X除以Y的余数 MOD(8，3)=2 POWER(X,Y) --X的Y次幂 POWER(2，3)=8 ROUND(X[,Y]) --X在第Y位四舍五入 ROUND(3.456，2)=3.46 SQRT(X) --X的平方根 SQRT(4)=2 TRUNC(X[,Y]) --X在第Y位截断 TRUNC(3.456，2)=3.45 SIGN(n) --若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 SELECT SIGN (12) FROM TABLE_NAME; 1 --聚合函数 AVG --平均值 AVG（表达式） SUM --求和 SUM(表达式) MIN、MAX --最小值、最大值 MIN(表达式)、MAX(表达式) COUNT --数据统计 COUNT（表达式） --日期函数 systimestamp --系统精确时间 to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') --转化日期 to_date('2010-01-12 23:12:11','yyyy-mm-dd hh24:mi:ss') --转换日期格式 ADD_MONTHS(sysdate,n) --增加或减少n月的日期 LAST_DAY(sysdate) --返回指定日期的当前月份的最后一天日期 NEXT_DAY(sysdate,c) --返回指定日期的后一周星期c对应的日期，c为1~7或Mon~Sunday sysdate + n --增减或减少那天的日期 EXTRACT({YEAR|MONTH|DAY|HOUR|MINUTE|SECOND} | {TIMEZONE_HOUR|TIMEZONE_MINUTE}} FROM {date_value|interval_value}) --日期中取出对应年、月、日 EXTRACT(second from dt2-dt1) --计算两个时间间隔，sysdate类型只能紧缺到天，timestamp类型可以精确到秒 MONTHS_BETWEEN(r1,r2) --返回r1日期和r2日期间隔的月份。当r1>r2时，返回正数，r1和r2是不同月的同一天，返回整数，否则返回的小数。当r1<r2时，返回的是负数 --单行函数 NVL(string1, replace_with) --string1为NULL，则NVL函数返回replace_with的值 NVL2(expr1,expr2,expr3) --表达式expr1值为NULL，返回表达式expr3的值；表达式expr1值不为NULL，返回表达式expr2的值 DECODE(条件值,值1,译值1,值2,译值2,缺省值) --条件值与值对比，取译值，都对不上取缺省值 coalesce(exp1,exp2,...,expn) --依次参考各表达式，遇到非null值返回该值。如果都是空值，最终返回一个空值 case when expr1 then value1 when expr2 then value2 else value end --依次执行表达式，成立则取对应值 MAX(COL2) KEEP (DENSE_RANK FIRST/LAST ORDER BY COL1) OVER(PARTITION BY DEPARTMENT) as value;--分组排序后取出最大/最小值 FIRST_VALUE(EMPLOYEE_ID) OVER(PARTITION BY DEPARTMENT ORDER BY AGE, SALARY DESC) as value;--分组排序后取出最小值 ratio_to_report(SCORE) OVER(PARTITION BY DEPARTMENT);--score在分组中占总score的百分比
3、高级语法
union all A union all B--合并两个结果集 union A union B--合并两个结果积并去重 intersect A intersect B--取的是两个结果的交集并且去重 minus A minus B--A结果集存在但是B结果集不存在的数据，经过去重，按第一列进行升序后返回。 merge into merge into T T1 using (select '1001' as a, 2 as b from dual) T2--要比较的字段 on (T1.a = T2.a)--关联关系 when matched then update set T1.b = T2.b--执行更新 when not matched then insert (a, b) values (T2.a, T2.b);--执行新增 --用于处理insertOrUpdate这种需求。如果数据库中存在数据就update，如果不存在就insert。 rollup与cube与grouping sets--分组查询的子条件语句 select * from dual group by rollup(A,B,C) 等价与 select * from dual group by() --无分组 union all select * from dual group by A union all select * from dual group by A,B union all select * from dual group by A,B,C union all select * from dual group by cube(A,B,C) 等价与 select * from dual group by() --无分组 union all select * from dual group by A union all select * from dual group by B union all select * from dual group by C union all select * from dual group by A,B union all select * from dual group by A,C union all select * from dual group by B,C union all select * from dual group by A,B,C group by grouping sets((A,B,C))：等价于group by A,B,C group by grouping sets(A,B,C)：等价于group by union all group by B union all group by C group by grouping sets(A,(B,C))：等价于group by A union all group by B,C With as--相当于创建临时表 insert into table2 with s1 as (select rownum c1 from dual connect by rownum <= 10), s2 as (select rownum c2 from dual connect by rownum <= 10) select a.c1, b.c2 from s1 a, s2 b where a.ID = b.ID;
三：数据库建设
1、导入/导出
--整库（导入导出时会出现文件找不到问题，文件最好再对应表空间目录下） exp 管理员账号/密码 full=y file=C:\person.dmp imp 管理员账号/密码 full=y file=person.dmp --用户 exp 管理员账号/密码 file=C:\person.dmp owner=用户名 imp 管理员账号/密码 file=person.dmp fromuser=用户名 --表 exp 管理员账号/密码 file=C:\person.dmp tables=t_person,t_student imp 管理员账号/密码 file =person.dmp tables=t_person,t_student
2、存储过程
创建存储过程： CREATE OR REPLACE PROCEDURE 存储过程名(param1 in type,param2 out type) IS
demo_exception EXCEPTION;--自定义异常 变量1 类型(值范围); 变量2 类型(值范围); BEGIN 过程函数
if (1<2) {
Raise demo_exception;--预期错误发生，抛出自定义异常
} Exception
when demo_exception then
Rollback;
处理方法
Raise;--抛出当前异常 When others then Rollback; Raise;--抛出当前异常 END 存储过程名; 存储过程示例： create or replace procedure test_count3(v_id in int,v_name out varchar2) is begin select c_name into v_name from tb_store where c_stono=v_id; dbms_output.put_line(v_name); exception when no_data_found then dbms_output.put_line('no_data_found'); end; 存储过程调用： declare v_name varchar(200); begin test_count3('1101',v_name); end;
3、游标
游标定义： 游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理， 最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。 游标有两种类型：显式游标和隐式游标。 在前述程序中用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。 但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。游标一旦打开，数据就从数据库中传送到游标 变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。 TPYE类型： v_org sf_org%rowtype; --与sf_org表类型相同 v_parent_id sf_org.parent_id%type;--与sf_org.parent_id字段类型相同 type type_name is ref cursor;--定义一种类型，此类型引用游标类型 type type_record is record (name1 varchar2(10),name2 varchar2(10));--定义一个记录行类型 type type_table is table of type_record;--定义一个存放记录行类型的集合，即表类型 隐式游标： DML操作和单行SELECT语句会使用隐式游标,它们是:插入操作:INSERT、更新操作：UPDATE、删除操作：DELETE、单行查询操作：SELECT ... INTO ...。 系统可以通过隐式游标属性了解操作的状态和结果，使用名字SQL来访问，但只能访问前一个操作属性，所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。 属性： SQL%ROWCOUNT 整型 代表DML语句成功执行的数据行数 SQL%FOUND 布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功 SQL%NOTFOUND 布尔型 与SQL%FOUND属性返回值相反 SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假。在执行SQL语句之后，Oracle自动关闭SQL游标，所有隐式游标的%ISOPEN属性始终为FALSE。 示例：--通过游标属性判断操作是否执行成功 UPDATE emp SET sal=sal+100 WHERE empno=1234; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('成功修改雇员工资！'); COMMIT; ELSE DBMS_OUTPUT.PUT_LINE('修改雇员工资失败！'); END IF; 显式游标: 声明游标 cursor cursor_name [(parameter[,parameter]...)] [return return_type] is select_statement; tips： cursor_name:游标的名称。 parameter:用于为游标指定输入参数。在指定数据类型时，不能使用长度约束。 return_type:定义游标提取的行的类型。 select_statement:游标定义的查询语句。 打开游标：open cursor_name[(parameters)]; 提取游标：fetch cursor_name into variables;--variables变量名。 关闭游标: close cursor_name;--显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。 游标属性 cursor_name%FOUND: 布尔型 只有在DML语句影响一行或多行时，%FOUND才返回TRUE。 cursor_name%NOTFOUND: 布尔型 与%FOUND的作用相反。若DML语句没有影响任何行，则返回TRUE。 cursor_name%ROWCOUNT: 整型 返回DML语句影响的行数。若没有影响任何行，则返回0。 cursor_name%ISOPEN: 布尔型 返回游标是否已打开。 游标使用： 示例一：loop循环 cursor cur_stu is select stu_name,stu_sex,stu_email from tb_student; open cur_stu;--打开游标 loop fetch cur_stu into v_name,v_sex,v_email; exit when cur_stu%notfound; dbms_output.put_line(v_name||' '||v_sex||' '||v_email); end loop; close cur_stu;--关闭游标 示例二：while循环 declare cursor cur_emp is select * from emp; row_emp cur_emp%rowtype; begin open cur_emp; fetch cur_emp into row_emp; while cur_emp%found loop dbms_output.put_line(row_emp.empno||'----'||row_emp.ename); fetch cur_emp into row_emp; end loop; close cur_emp; end; 示例三：for循环 --FOR循环简化了游标，不需要open、fetch和close语句，不需要%FOUND检测是否到最后一条记录，这一切Oracle隐式的帮我们完成了。 declare cursor emp_cur is select empno,ename,sal from emp where job='manager' for update; begin for emp_row in emp_cur loop update emp set sal=sal-1000 where current of emp_cur; end loop; commit; end;
4、视图
试图创建： create [or replace] [force] view [schema.]view_name [(column1,column2,...)] as subquery [with check option] [constraint constraint_name] [with read only]; tips: or replace: 如果存在同名的视图, 则使用新视图"替代"已有的视图 force: "强制"创建视图,不考虑基表是否存在,也不考虑是否具有使用基表的权限 column1,column2,...：视图的列名. subquery：一条完整的SELECT语句，可以在该语句中定义别名 with check option: 插入或修改的数据行必须满足视图定义的约束. with read only：默认可以通过视图对基表执行增删改操作，现实开发中，基本上不通过视图对表中的数据进行增删改操作. 视图示例： create or replace view empdetail as select empno,ename,job,hiredate,emp.deptno,dname from emp join dept on emp.deptno=dept.deptno with read only; 试图操作： drop view view_name--语句删除视图 create or replace view view_name--修改试图
5、序列
序列语法： CREATE SEQUENCE 序列名--序列名 [INCREMENT BY n]--步长 [START WITH n]--起始数 [{MAXVALUE/MINVALUE n| NOMAXVALUE}]--最大/小值 [{CYCLE|NOCYCLE}]--是否循环 [{CACHE n| NOCACHE}];--是否缓冲 序列示例： create sequence seq_userid increment by 1 start with 1 maxvalue 99999 cycle cache 1000; 序列使用： currval：表示当前值，新序列若没使用过nextval不能直接使用当前值，会报错； nextval：表示序列的下一个值 select seq_name.currval from dual; --序列当前值 select seq_name.nextval from dual; --序列下一个值 序列操作： dorp sequence seq_name;--删除序列 alert sequence seq_name increment by 10 nomaxvalue nocycle cache 10;--修改序列(修改序列只能修改这几个值，不能修改初始值)
6、函数
创建函数： create or replace function 函数名([参数1 模式 参数类型][,参数1 模式 参数类型]) return 返回值类型 as 变量1 变量类型; 变量2 变量类型; begin 函数体; end 函数名; tips: 参数的模式有3种:(如果没有注明, 参数默认的类型为 in.) in:为只读模式,在函数中,参数的值只能被引用,不能被改变; out:为只写模式,只能被赋值,不能被引用; in out:可读可写 函数示例： create or replace function function1(para1 in number, para2 in number) return number as begin if para1 > para2 then return para1; else return para2; end if; end function1; 函数使用： select function1(666, 333) from dual;
四：其他
