1.修改默认的最大连接数
oracle的最大连接数缺省值是100,建库时默认值是150,
主要受processes这个系统参数(实际还应受操作系统限制),
这是个静态参数,修改后必需重启数据库才能生效,如果权限不够,需要以管理员身份登陆
alter system set processes=200 scope=spfile;
2.PL/SQL Developer提供了自动提交的设置:(DML操作如 insert,delete,update,select...for update 等都会触发表级锁和行级锁)
- SELECT l.session_id sid,
- s.serial#,
- l.locked_mode 锁模式,
- l.oracle_username 登录用户,
- l.os_user_name 登录机器用户名,
- s.machine 机器名,
- s.terminal 终端用户名,
- o.object_name 被锁对象名,
- s.logon_time 登录数据库时间
- FROM v$locked_object l, all_objects o, v$session s
- WHERE l.object_id = o.object_id
- AND l.session_id = s.sid
- ORDER BY sid, s.serial#;
a.事务执行后自动提交:Tools -> Preferences -> Window Types -> SQL Window:勾选“AutoCommit SQL”,即可实现自动提交操作。
b.退出登录自动提交:Tools -> Preferences -> Oracle -> Connection -> Log off with open transaction:选择“Commit”。
1.从父节点查出子节点:start with .....connect by prior ......
select 字段名 from表名 START WITH PK_PRODUCT_SN = '{0}' CONNECT BY PRIOR PK_KEYP_SN = PK_PRODUCT_SN2.length与lengthb的区别
区别:length求得是字符长度,lengthb求得是字节长度。
SQL> select length('哈哈') from dual t;
LENGTH('哈哈')
--------------
2
SQL> select lengthb('哈哈') from dual t;
LENGTHB('哈哈')
---------------
4
1. CASE WHEN 表达式有两种形式
--简单Case函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE
WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
2. CASE WHEN 在语句中不同位置的用法
SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1 /*sex 1为男生,2位女生*/
ELSE NULL
END) 男生数,
COUNT (CASE WHEN sex = 2 THEN 1
ELSE NULL
END) 女生数
FROM students GROUP BY grade;--SELECT CASE WHEN 用法
SELECT T2.*, T1.*
FROM T1, T2
WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND
T1.SOME_TYPE LIKE 'NOTHING%'
THEN 1
WHEN T2.COMPARE_TYPE != 'A' AND
T1.SOME_TYPE NOT LIKE 'NOTHING%'
THEN 1
ELSE 0
END) = 1--WHERE CASE WHEN 用法
SELECT
CASE WHEN target_qty <= 500 THEN '1'
WHEN target_qty > 500 AND target_qty <= 600 THEN '2'
WHEN target_qty > 600 AND target_qty <= 800 THEN '3'
WHEN target_qty > 800 AND target_qty <= 1000 THEN '4'
ELSE NULL END salary_class, -- 别名命名
COUNT(1)
FROM t_pm_mo_base
GROUP BY
CASE WHEN target_qty <= 500 THEN '1'
WHEN target_qty > 500 AND target_qty <= 600 THEN '2'
WHEN target_qty > 600 AND target_qty <= 800 THEN '3'
WHEN target_qty > 800 AND target_qty <= 1000 THEN '4'
ELSE NULL END; --GROUP BY CASE WHEN 用法 分别汇总各个区间的人数
6.DECODE() 函数
select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')
from employees;
*另一种形式的if语句
7.wm_concat(column)函数,可以进行所选字段数据值的合并
或者 listagg 函数
select listagg(UNIT, ',') within group( order by UNIT) as UNIT from tb_b_unit;