王志广的个人博客

oracle学习

admin发表于2016-01-11  917次浏览 标签: 

ORACLE是以分布式数据库为核心的B/S体系结构的数据库

1、创建表和列
create table 表名 (
列名 类型(10),
name varchar2(10),
subject varchar2(10),
score number
);
删除列: alter table 表名 drop column 列名
增加列: alter table 表名 add (列名 列类型)
改表名: alter table 表名 rename to 新表名

2、约束
约束类型:
NOT NULL 非空约束
UNIQUE 唯一性约束
PRIMARY KEY 主键约束
FOREIGN KEY 外键约束
CHECK 检查约束

建表时创建约束
create table 表名 (
列1 数据类型 constraint 约束名1 约束类型,
列2 数据类型 constraint 约束名2 约束类型,

);
外键约束
create table 表名 (
列1 列类型
contraint 约束名 foreign key (列名) references 其他表(列名)
注意: 另一张表被引用的列需是主键或Unique

修改约束
alter table 表名 modify (
列名 constraint 约束名 not null
) ;

删除约束
alter table 表名 drop constraint 约束名;

3、序列
序列就是序号生成器!
create sequence 序列名 increment by n
start with n
maxvalue n | nomaxvalue
minvalue n | nominvalue
cycle | nocycle
cache n | nocache
使用的时候调用为:
insert into xx表(col1,col2) values (seq1.nextavl,yy);
修改删除
alter sequence 序列名 选项 新值
drop sequence 序列名

4、同义词
同义词就是别名
create [public] synonym 同义词 for 用户名.对象名
drop synonym 同义词

5、查询语句
select 列名 from 表名
Where 条件查询
group by 分组
having 筛选
order by 排序

limit实现
rownum虚拟列
select rownum from abc where rownum >20

6、子查询
where型子查询即是:把内层sql语句查询的结果作为外层sql查询的条件
select * from abc where id in (select id from abcd)
select * from abc where id exists (select id from abcd)
select * from (select * from abc) where a>1

7、连接查询
select * from aaa as a join bbb as b on a.id=b.id
左连接 left join
内连接 inner join

8、视图
视图是一种虚拟表,本身不保存数据,而是从表中取得的数据,可以理解为表的映射,或更简单的理解为一个查询结果。
创建视图:
Create or replace view 视图名
As select 语句
With read only –是否只读
With check option –是否执行约束检查

删除视图:
Drop view 视图名

9、索引
创建索引
create [unique] index 索引名
on 表名(列1,列2…)

删除索引
Drop index 索引名

10、事物
原子性(Atomicity)
一致性(Consistency)
隔离性(Isolation)
持久性(Durability)
开启事务(第1条dml语句即进入事务)
执行sql操作(普通sql操作)
设置保存点(savepoint 保存点)
提交/回滚(commit/rollback)
部分回滚(rollback to 保存点)

11、sqlpls
结构定义
declare
变量声明部分
begin
执行部分
exception
异常处理部分
End
dbms_output.put_line(i);输出

11.1、变量
变量 的定义有2种格式
变量名 变量类型 [约束] default 默认值
变量名 变量类型 [约束] [:=初始值]

11.2、if
if 条件 then

elsif 条件 then

end if;

11.3、while
while 条件 loop
循环体
end loop;

11.4、for
for 循环变量 in [reverse] 起始值..终止值 loop
循环体
end loop;

11.4、loop
loop
循环体
exit when 条件;
end loop;
exit退出

12、存储过程和存储函数

字符串函数
concat(字符串1,字符串2) –连接字符串,2个参数
chr() ,ascii()
instr(字符串,子串,start,occurrence) // occu代表第几次出现
length(字符串)
lower(),upper()
lpad(字符串,长度,填充字符),rpad()
ltrim(),rtrim(),trim()
replace(字符串,子串,替换字符串)
substr(字符串,开始位置,长度)

数学函数
abs()绝对值
ceil()进1取整
floor()舍余取整
mod()取模
round(数据,舍入位置) –位置可以是负
trunc(数据,舍入位置)
round()与trunc的区别在于round()四舍五入
trunc() 截取

日期函数
add_months(日期,整月数) // 计算日期+N月后的新日期
last_day(日期) // 该日期所在月的最后一天
months_between(日期1,日期2)//返回日期相差的月份 (浮点型)
next_day(日期,周N)//返回最近的周N的日期
说明:周日->周六 编号 1 2 。。。7

类型转换函数
to_char() 把日期/数字转换为字符串
例:to_char(hiredate,’yyyy-mm-dd day’)
to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’)
to_date()字符串转日期型
to_date(’12-09-2013′,’dd-mm-yyyy’)
to_number(字符串,格式字符串)
to_number(‘1,000.50′,’999,999,99’)

定义:
create or replace procedure 名称[(参数)]
authid current_user|definer –以定义者还是调用者的身份运行
is[不要加declare]
变量声明部分
begin
主体部分
exception
异常部分
end;
删除
drop procedure 存储过程名
drop function 函数名

13、plsql和数据库交互
select 列1,列2… into 变量1,变量2
from

……

 

14、异常处理
异常的判断语法:
exception
when 异常1 or 异常2 then
语句..;
when 异常3 or 异常4 then
语句 …;
when others then
语句 …;
end
内部定义错误类型:
NO_DATA_FOUND 在使用SELECT INTO 结构,并且语句返回NULL值的时候;访问嵌套表中已经删除的表或者是访问INDEX BY表(联合数组)中的未初始化元素就会出现该异常
TOO_MANY_ROWS 常见错误,在使用SELECT INTO 并且查询返回多个行时引发。如果子查询返回多行,而比较运算符为相等的时候也会引发该异常。
ZERO_DIVIDE 将某个数字除以0的时候,会发生该异常
ACCESS_INTO_NULL 试图访问未初始化对象的时候出现
CASE_NOT_FOUND 如果定义了一个没有ELSE子句的CASE语句,而且没有CASE语句满足运行时条件时出现该异常
COLLECTION_IS_NULL 当程序去访问一个没有进行初始化的NESTED TABLE或者是VARRAY的时候,会出现该异常
CURSOR_ALREADY_OPEN 游标已经被OPEN,如果再次尝试打开该游标的时候,会出现该异常
DUP_VAL_ON_INDEX 如果插入一列被唯一索引约束的重复值的时候,就会引发该异常(该值被INDEX认定为冲突的)
INVALID_CURSOR 不允许的游标操作,比如关闭一个已经被关闭的游标,就会引发
INVALID_NUMBER 给数字值赋非数字值的时候,该异常就会发生,这个异常也会发生在批读取时候LIMIT子句返回非正数的时候
LOGIN_DENIED 程序中,使用错误的用户名和密码登录的时候,就会抛出这个异常
NOT_LOGGED_ON 当程序发出数据库调用,但是没有连接的时候(通常,在实际与会话断开连接之后)
PROGRAM_ERROR 当Oracle还未正式捕获的错误发生时常会发生,这是因为数据库大量的Object功能而发生
ROWTYPE_MISMATCH 如果游标结构不适合PL/SQL游标变量或者是实际的游标参数不同于游标形参的时候发生该异常
SELF_IS_NULL 调用一个对象类型非静态成员方法(其中没有初始化对象类型实例)的时候发生该异常
STORAGE_ERROR 当内存不够分配SGA的足够配额或者是被破坏的时候,引发该异常
SUBSCRIPT_BEYOND_COUNT 当分配给NESTED TABLE或者VARRAY的空间小于使用的下标的时候,发生该异常(类似于java的ArrayIndexOutOfBoundsException)
SUBSCRIPT_OUTSIDE_LIMIT 使用非法的索引值来访问NESTED TABLE或者VARRAY的时候引发
SYS_INVALID_ROWID 将无效的字符串转化为ROWID的时候引发
TIMEOUT_ON_RESOURCE 当数据库不能安全锁定资源的时候引发
USERENV_COMMITSCN_ERROR 只可使用函数USERENV(‘COMMITSCN’)作为INSERT语句的VALUES子句中的顶级表达式或者作为UPDATE语句的SET子句中的右操作数
VALUE_ERROR 将一个变量赋给另一个不能容纳该变量的变量时引发

15、游标
游标是一种私有的工作区,用于保存sql语句的执行结果.
在执行一条sql语句时,数据库服务区工作区,
这里保存了sql语句执行的相关信息
工作区有2种形式的游标,隐式的和显式的.
隐式游标由数据库自动定义,显示游标由用户自己定义
begin
delete from student where sid=9;
if SQL%ROWCOUNT>0 then
dbms_output.put_line(‘影响了’);
else
dbms_output.put_line(‘没影响’);
end if;
end;

自定义游标
cursor 游标名[(参数1,参数2..)]
is
select语句 [for update] ;
open 游标名
fetch 游标名 to 变量1,变量2;
close 游标名;

16、触发器
触发器是一类特殊的事务 ,
可以监视某种数据操作(insert/update/delete),
并触发相关操作(insert/update/delete)
创建触发器的语法
create trigger 触发器名称
after/before/instead of(触发时间)
insert/update/delete [of列名] (监视事件)
on 表名 (监视地址)
[for each row [when条件]]
begin
sql1;
..
sqlN;
end

删除触发器
drop trigger triggerName

 

 

你可以发表评论引用到你的网站或博客,或通过RSS 2.0订阅这个日志的所有评论。
上一篇:
下一篇:
没有评论
我来说两句

  Ctrl+Enter