05_Oracle
每周一看 升官发财 (0^0)
- DDL数据定义语言 语句自动提交
create drop truncate alter - DML数据操作语言 用commit,rollback结束当前事物
update delete insert - TCL事务控制语言 :commit rollback savepoint(保留点)
- DCL数据控制语言 :grant
- d: 用数字表达一周内的第几天 1代表周日
day: 全拼表达星期几(sunday)
month:
mon: 简拼表达月(mar)
months_between(”,”): 相差多少月
add_months(,n): 加n个月
next_day(”,’星期’): 出现下个参数的日期
last_day(”): 同月的最后一天 - 数据类型
number
char
varchar2(一定要有值)(空格敏感) - 一些关键字
exists 存在
default 缺省(默认)
sysdate 系统时间
show user 显示用户名
rollback 回滚(到上次提交后)
commit 提交(旧事物的结束,新事物的开始)
where 过滤行
group by 分组
having 过滤组
order by 排序(自然小-->大,asc升序)desc降序
|| 拼接运算符
distinct 消除重复行
case 1 when 2 then 3 when 4 then 5 else 6 end;
decode(1,2,3,4,5,6)
cross join
inner join on 内连接
outer(left /right ) join on
union/union all 并
intersect 交
minus 差
rownum 伪列 行标号1,2,3…
rowid 伪id
constraint 给约束写个名字,可以不写(也是系统自动创建
索引的名字)
primary key 主键约束
unique key 唯一约束
not null 非空约束
check(col_name ….)
references foreign key
多个表级约束用’,’分隔.
外键后面加:
on delete cascade; delete父表时将删除子表对应的行
on delete set null; delete 父表时将子表对应的列赋值为 null
transaction 事物(undo segment 未提交保存的地方)
锁
synonym 同义詷 create synonym ytf for account;
inline view
view 约束 with check option,with read only;
index (index segment,rowid) - 单行函数
数值函数:round,trunc
转换函数:to_char(,”),to_date(,”),to_number
字符类型:upper 大写,lower 小写,initcap 首字母大写,
concat 拼接 length lpad 右对齐 rpad
一般函数 nvl,ltrim/rtrim(去空格的函数 fm也行写字符串里面)
组函数
max,min,count,sum,avg - –过滤条件
in like is_null between_and =
and or not <>,!=,^=
col_name like ‘h_%’ escape ‘\’; ‘找h_开头的
(case when 1=2… then 3…
when 1=4…then 5…
else 6…
end)=decode(1,2,3,4,5,6); - from-where-group_by-having-select-order_by
–执行顺序 - alter session set nls_date_format=
‘yyyy mm dd hh24:mi:ss’;
–改变session中的日期格式 - alter table tab_name drop constraint_name/primary key;
–删除约束/删除主键约束 - alter table tab_name drop(col_name)
–删除一列 - alter table tab1_name add 表级约束语法(constraint constraint_name foreign key(col_name) references tab2_name(col_name))
–增加约束 - alter table tab_name add(c1 char)
–增加一列 - alter table tab_name modify(col_name null);
–将not null改为null列 - alter index index_name rebuild;
–重建索引 - alter procedure/package/function p_name compile;
–编译过程/包/函数 - alter package pkg_ytf compile body;
–编译包体 - alter trigger trig_name compile;
–编译触发器 - alter trigger trig_name disable/enable
–触发器无效/有效 - alter table tab_name disable/enable all triggers;
–该表所有触发器无效/有效 create index index_name on table_name(col);
–按照某表的列创建索引create table table_name (
col_name1 类型,col_name2 类型….);
–创建表- create table table_name as select…;
–创建表,类型和数据与子查询结果一样 - create or replace view as select….;
–创建视图 - create sequence se_name start with 1(缺省 1) maxvalue 5
cache 3 ; (day309.sql(day08 42页面))
–创建序列号 - create or replace procedure p_name(
v_name in/out/in_out type…)]is[可以定义变量]
begin exception end;
–创建过程 - create or replace function f_name(…) return type is
[可以定义变量] begin end;
–创建函数 - create or replace package pkg_name is end;
–创建包 create or replace package body pkg_name
is begin <初始化代码> end;
–创建包体insert into table_name values();
–添加一行数据
insert into table_name select….;
–添加子查询的数据- delete from table_name where…;
–删除行,表数据删除高水位线没动,表占的空间没有释放,可以回滚 - drop table tab_name purge;
–删除表 - drop index/view/sequence/procedure/function/package _name;
–删除索引/视图/序列号 - truncate table table_name;
–删除所有记录,DDL语句自动提交,没有回滚,空间释放,不可以回滚 - update table_name set col_name=….where…;
–改数据
select * from table_name;
select * from (select….);
–查询
/*************PL/SQL************************/
- –procedure language/SQL
4个关键字 declare begin exception end - type rc_name is record(变量名1 类型1,变量名2 类型2…);
–记录类型的声明 - type type_name is table of val_type index by key_type
–关联数组声明 index_by_table
关联数组的方法:exists(i)第i个元素是否存在,避免抛出异常
count返回联合数组的元素个数,空数组值是0
first/last 返回最小/最大下标号,为空则返回 null
next(n)/prior(n)返回第n个元素的前一个元素下标和后一个
元素的下标,如果不存在,则返回 null
trim(n) 从最后一个元素删除n个元素
delete 删除所有元素 delete(n)删除第n个 delete(m,n)删除m到n
遍历数组,由于下标可以不连续,要判断元素是否存在v_name.exists(i) - v_id account.id%type(v_id的类型是表account中id列的类型)
v_a account%rowtype(v_a的类型是记录类型,包含account中所有列的类型)
if .. then end;
if .. then .. else .. end;
if .. then elsif then .. else .. end; - –分支
loop exit when .. end loop;
while .. loop .. end loo;
for i in 下限..上限 loop…end loop; - –循环
动态语句:execute immediate ‘DDL,DML’;
cursor cur_name is select * from account;(返回0或者多条记录)
对cursor %isopen cursor是open其值为true
%notfound %found%rowcount
隐式cursor SQL%….
cursor–open–fetch–close–用cursor的顺序 异常: ORACL预定义异常
NO_DATA_FOUND 没有数据 too_many_rows多行 invalid_cursor 没open
zero_divide 除0 dup_val_on_index重复索引 value_error值类型不对非预定义异常:先声明异常,绑定异常
pragma exception_init(e_name,-0001)用户自定义异常:先声明异常,绑定异常,抛出异常
(raise e_name),再捕获
sqlcode:返回当前错误代码 sqlerrm:返回当前错误的消息文本,用文件保存
procedure 参数是形参in不能作赋值目标 out有个返回值 in_out- 想要在存储过程中用DDL语句,要DBA直接给用户授权
grant create table to user_name; - 调用其他用户的过程,必须由过程的属主授予权限
grant execute on proc_name to user_name exec p_name;可以直接执行一个过程/函数…
绑定变量 variable a number; exec :a:=1;
print :a;打印绑定变量的值难点: trigger 触发器 (enable,disable)
触发时间:before,after
触发事件:insert,update,delete
触发器类型:statement,row
触发顺序:before_statement before_row after_row after_statement
create or replace trigger tri_name
before/after insert/update/delete
on tab_name
for each row
declare
begin
<执行语句>
end;|--------|---:old ---|---:new ---| insert null 要插入的值 update 原始值 新值 delete 原始值 null
:old,:new会在当条SQL语句(包含trigger)执行完毕后才会改变,不是瞬发的
- create or replace procedure
- 匿名程序(declare begin exception end)。
- 过程头(in;out;in out;形参,实参,对于out,in out 型参数要求实参必须是变量)。
- 用户 角色 权限 怎么在存储过程中正确执行DDL语句。
- 变量(for 循环变量 局部变量 全局变量 绑写变量)。
- SQL语句分析过程soft parse 软分析,hard parse硬分析,程序员要写带定变量的规范的sql。
- 静态sql,动态sql。静态sql效率高。
PL/SQL中的静态SQL - racle 在解析sql时会把plsql中定义的变量转为绑定变量,减少了硬分
析次数 - server process将执行完的SQL cache起来,cursor不关闭,当再次执行
SQL时,不需要软分析
过程中的参数会自动转化为绑定变量
匿名块
数据类型(记录 集合 遍历 方法)变量 控制结构SQL(ddl dml select)
cursor 表态sql动态sql 绑定变量sql(编译(soft parse;hard parse)
执行)session exception()
有名块
procedure function 参数问题
package变量(局部 全局 绑定)
trigger dml(update insert delete)
PL/SQL的特点
结构化模块化编程
良好的可移植性 跨平台(操作系统)
良好的可维护性
提升系统性能
(最大问题)不便于向异构数据库移植应用程序
–面试 一定要知道的 添彩哦
truncate table table_name;–删除大表快 删除高水位线
FTS全表扫描,找到HWM高水位线,之下所有的Date bolck
scan based index基于索引的扫描
transaction 事物
server process oracletarena
每创建一个连接,oracle服务器端多一个server prcess进程,
oracle中多了一个session
数据库上的并发连接
JDBC与PL/SQL的优缺点:静态 动态 的优缺点
编译(软 硬分析) 执行