工作用oracle常用sql记录及mybatis用法记录

1.oracle常用sql

常用的基础增删改查就不记录了，基本上可以随手写

-- 查看死锁
SELECT
	sess.sid,
	sess.serial#,
	lo.oracle_username,
	lo.os_user_name,
	ao.object_name,
	lo.locked_mode
FROM
	v$locked_object lo,
	dba_objects ao,
	v$session sess
WHERE
	ao.object_id = lo.object_id
	AND lo.session_id = sess.sid;
	
-- 查看死锁语句
select sql_text from v$sql where hash_value in 
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object));

-- 死锁处理
alter system kill session 'sid,serial#';

-- 查看数据库当前连接数
select count(*) from v$process;

-- 查看数据库最大连接数
select value from v$parameter where name ='processes'; 

-- 查看当前数据库用户使用数据情况
select osuser, a.username, b.sql_text, machine
from v$session a, v$sqlarea b
where a.sql_address =b.address; 

-- 查看连接信息
select * from v$session WHERE username = 用户名 ORDER BY osuser, process, MACHINE, port;

-- 查看所有的dblink
select * from dba_db_links;

-- 创建dblink
create public database link dblink名称 connect to 用户名 identified by 密码
　　 using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ip地址)(PORT = 端口)))(CONNECT_DATA = (SERVICE_NAME = 服务名)))';

-- 分组排序取第一条
SELECT * FROM (SELECT t.*, row_number () over (partition BY t.user_id, t.user_type ORDER BY t.create_time DESC ) rw 
         		FROM t_user_info t WHERE t.deleted = 0
			  ) m 
WHERE m.rw = 1;

2.mybatis用法（oracle数据库）

1.sql块使用，用于多次使用到的相同sql块
	<!-- 查询块 -->
	<sql id="sql_select">
		t.id, t.name
	</sql>
	<!-- 用户查询 -->
	<select id="queryUser" resultType="com.lu.test.entity.User">
		SELECT
		<include refid="sql_select"/>
		FROM t_user t
	</select>
2.条件使用
<select id="queryUser" parameterType="com.lu.test.entity.User" resultType="com.lu.test.entity.User">
	SELECT
		t.id, t.name
	FROM t_user t
	WHERE t.deleted = 0
	and t.status = #{status}
	<if test="type != null and type != ''">
        and t.type = #{type}
    </if>
</select>
3.批量新增（参数为对象集合）
<insert id="insertUser" parameterType="java.util.List">
    INSERT INTO t_user (id,name,type,status)
    <foreach collection="list" item="item" separator="UNION ALL">
        SELECT
        #{item.id}, #{item.name}, #{item.type}, #{item.status}
        FROM dual
    </foreach>
</insert>
4.批量修改
<update id="updateUser" parameterType="java.util.List" >
    <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
        UPDATE t_user SET
            name= #{item.name}
        WHERE id = #{item.id}
    </foreach>
</update>
5.循环foreach使用，第3点和第4点都用到了，只是具体根据sql语句需要，open close separator 内容不同，
下面循环最终生成的sql语句为  AND ((t.type = '1' and t.status = '1') or (t.type = '2' and t.status = '2') or ...) AND 
	AND
	<foreach collection="list" item="item" open="(" close=")" separator="or">
		(t.type = #{item.type} and t.status = #{item.status})
	</foreach>
	AND 
6.分支 choose when otherwise
<choose>
    <when test="type == 1">
        and t.status = '1'
    </when>
    <when test="type == 2">
        and t.status = '4'
    </when>
    <otherwise>
        and t.status = '8'
    </otherwise>
</choose>