Oracle基础 各种语句的定义格式

Oracle内建数据类型
一、 字符数据
1、 char(size)
2、 varchar2(size) 最常用，最大长度4000字节
3、 nvhar(size)、nvarchar(size)
4、 varchar(size)
5、 long  建议使用LOB大型数据
6、 raw 存储二进制，建议使用LOB大型数据

二、 数字
1、 number(p,s)

三、 日期
1、 data
2、 timestamp
3、 timestamp with time zone
4、 timestamp with local time zone
5、 interval year to month
6、 interval day to second
四、 大型对象数据类型
BLOB、CLOB、NCLOB、BFILE 最大长度4G
五、 ANSI、DB2、SQL/DS
六、 用户自定义类型
create type
create type body

SQL语句分类
一、 数据查询语句（DQL）
SELECT
二、 数据操纵语句（DML）
INSERT、UPDATE、DELETE
三、 数据定义语句（DDL）
CREATE、ALTER、DROP
四、 数据控制语句（DCL）
GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT

具体SQL语句
一、 create table
创建表
create table<table_name>
(
col_name<datatype>,
col_name<datatype>,
col_name<datatype>
)
二、 alter table
修改表
alter table<table_name>
[add<col_name datatype>]
[modify<col_name datatype>]
[drop column<column_name>]
三、 rename
重命名表
rename old_table_name to new_table_name
四、 truncate table
删除表中存在的信息，只保留表结构，删除数据不可恢复
truncate table <table_name>
五、 drop table
删除表
六、 drop table <table_name>
七、 select
选择语句
select <column_list>
from <table_name>
[where <codition>]
[group by <group_by expression >]
[having <group_condition>]                ——组函数只能写having之后
[order by<col_name>]

1. dual表
2. sysdate系统时间
3. where条件中用到的运算
+-*/
||连接
=、!=、^=、<>、>、>=、<、<=、any，some，all
not、and、or
in（相当与any）、not in（相当于!=all）
between x and y
not between x and y
is null、is not null （可使用nvl()函数将null转换成需要的值）
exist
like（_表示一个字符，%表示0个或多个字符）
若字符串中包含“_”“%”，可使用escape ‘\’，如
like ‘%s\_t%’ escape ‘\’用来匹配“s_t”字符串
集合运算符
4. 常用函数
initcap首字母大写、
instr查找字符位置、l
ength字符长度、
lower转化为小写、
upper转化为大写、
lpad在左侧填充特定长度、
rpad在右侧填充特定长度、
rtrim剪切字符及其右侧字符、
lrtrim剪切字符及其左侧字符、
soundex、发音相似单词
substr、字符截取
chr、ascii码代表的字符
ascii、字符的ascii码
translate、字符替换
repleace、字符或字符串替换
abs绝对值、
ceil向上取整、
floor向下取整、
sqrt平方根、
power乘方、
sign数字正负、
trunc截去小数、
round四舍五入、
exp常数e的次幂、
mod余数、
ln自然对数值、
log以10为底的对数值、
vsize存储空间、
greatest一组值中的最大、
least一组值中的最大、
add_months在一个日期上加上或减去指定月份、
last_day返回指定月份的最后一天的日期、
next_day返回下一个指定日期的第一天、
months_between两个日期相隔的月份数、
trunc
日期格式：
SYEAR公元纪年、
YY年、
Q季、
MM月,
RM罗马月,
Month英文月份,
WW当年第几周,
W当月第几周,
DDD当年第几天,
DD当月第几天,
D周内第几天,
DY星期,
HH,HH12,12进制小时数
HH2424小时小时数,
MI分钟数（0~59）,
SS秒数（0~59））
to_char将日期转化为字符、
to_date将字符转化为日期、
to_number将数字转化为字符、
decode将特定数据转变成另一种表示

5. 组函数
avg平均数、忽略null
count查询行数、
max最大值，忽略null、
min最小值，忽略null、
stddev标准差，忽略null、
sum总和，忽略null、
variance方差，忽略null
6. rowin是存储每条记录的实际物理地址，对记录的访问是基于rowid的，这是存取表中数据的最快的方法。
7. where字句不能够对group by的结果进行限定，需要用having限定。
8.

八、 insert
insert into <table_name>[col_name, col_name, col_name,…,]
values(value, value, value, value,…);
在sqlplus下，可以用&字符进行参数替换，可以通过工具逐个输入insert的值
九、 update
update <table_name>
set col_name=value/expression, col_name=value/expression
[where <conditions>]

十、 delete
delete from <table_name>
[where <condition>];
或delete  <table_name>
[where <condition>];

truncate和delete的区别：
a、delete可以使用rollback命令进行撤销，而truncate不可
b、truncate不能触发任何delete触发器。
十一、 约束
1、 unique
tel_number char(10) constraint cm_unique unique,
组合constraint cm_unique unique(tel_number,online_email),
alter table cm add constraint cm_unique unique(tel_number),

2、 check
tel_number char(14) check(length(tel_number)=14),

3、 not null
tel_number char(14) not null,

4、 primary key
tel_number char(14) constraint cm_primary primary key,

5、 foreigh key
constraint emp1_foreign foreign key(deptno) references dept1(deptno),
级联删除：
deptno number(2) references dept1(deptno) [on delete set null] 删除子记录时主记录相应值为null
constraint emp1_foreign foreign key(deptno) references dept1(deptno) [on delete cascade] 删除子记录时级联删除主记录
若不写则子记录存在不可删除主记录。

十二、 连接
1、 左连接:
表示左表中指定的内容全部返回
select e.ename,d.dname
from emp1 e,dept d
where e.deptno = d.deptno(+)
2、 右连接:
表示右表中指定的内容全部返回
select e.ename,d.dname
from emp1 e,dept d
where e.deptno(+) = d.deptno
3、 自连接
select worker.ename,manager.ename
from emp worker,emp manager
where worker.mgr = manager.empno
4、 嵌套查询
子查询中不能有order by分组语句；
oracle中使用exists比使用in查询速度快。因为在使用exists时，系统会先检查主查询，然后运行子查询知道它找到第一个匹配项；而在系统在执行in语句时，会先执行子查询并将结果放到一个加了索引的临时表，在执行子查询之前，系统先将主查询挂起。
5、 集合运算
union all：结合两个select语句结果，可以有重复
union：结合两个select语句结果，消除任何相同的行
minus：从第一个select结果中消除第二个select结果
intersect：只返回同时出现在两个select语句中的行

十三、 PL/SQL
declare
<declarations section>
begin
<executable command>
declare
<declarations section>
begin
<executable command>
end;
end;

1、 定义变量和sql定义相似
variable_name [constant] datatype [not null][{:=|default} default_value]
定义常量时使用constant。

2、 交互式输入变量值
v_empno number(4):=&v_empno;

3、 打印语句
dbms_output.put_line(v_empno);
4、 显示记录
type record_name is record(field_definition_list);
例：
type t_emp is record
(
v_empno emp.empno%type,
v_ename emp.ename%type
);
5、 隐式记录
v_emp emp%rowtype;

6、 index_by表
type type_name is table of element_type [not null] index by binary_interger;
declare
type table_empno_type is table of emp.empno%type index by binary_integer;
table_empno table_empno_type;
i binary_integer:=1;
begin
select empno
into table_empno(i)
from emp
where empno=7369;
    end;
7、 可变数组
type type_name is [varray|varying array] (max_size) of element_type [not null]
declare
type varray_empno_type is varray(5) of emp.emono%type;
varray_empno varray_empno_type;
begin
varray_empno:=varray_empno_type(7369.7499);

8、 集合的方法
count：集合中的元素个数
delete：删除集合中所有元素
delete(x)：删除下标为x的元素
delete(x,y)：删除下标从x到y的元素
extend：在集合末尾添加一个元素
extend(x)：在集合末尾添加x个元素
extend(x,n)：在集合末尾添加n个x的副本
first：返回第一个元素的下标号，对于varray始终返回1
last：返回最后一个元素的下标号
limit返回可变数组集合的最大的元素个数
next：返回x之后的元素
prior：返回x之前的元素
trim：从末端删除一个元素
trim(x)：从末端删除x个元素

http://www.cnblogs.com/roucheng/
9、 动态sql
excute immediate 动态SQL语句 using 绑定参数列表 returning into输出参数列表；

str_sql:=’create table’||’ ’||table_name||’(’||field1||’ ’||’datatype1’||’,’|| field2||’ ’||’datatype2’||’)’;
execute immediate str_sql；

10、 if条件语句
if condition then
sequence_of_statements
end if;

if condition then
sequence_of_statement
else
sequence_of_statement
end if;

if condition then
sequence_of_statement
else if condition2 then
sequence_of_statement
else
sequence_of_statement
end if;

11、 case语句
case selector
when expression then sequence_of_statements;
when expression then sequence_of_statements;
when expression then sequence_of_statements;
[else  sequence_of_statements;]
end case;

12、 loop循环
loop
  sequence_of_statements
  if a>0 then   
exit              或exit when a>0
end if;
end loop;

13、 for-loop语句
for counter in [reverse] lower_bound..higher_bound loop
sequence_of_statement
        end loop;

如：
        for I in 1..v_count loop
list(i) :=i*I;
end loop;

14、 while-loop语句
while condition loop
sequence_of_statements
end loop;

15、 游标定义
a、cursor cursor_name [(parameter[,parameter]…)]
[return return_type] is select_statement
b、open cursor_name
c、fetch cursor_name into variable[,variable,…]
d、close cursor_name

例：
declare
  cursor c_emp_ename is select ename form emp;
v_ename emp.ename%type;
v_count binary_integer;
begin
select count(rowed)
into v_count
from emp;
open c_emp_ename;
for I in i..v_count loop
fetch c_emp_ename into v_ename;
dbms_output.put_line(vname);
end loop;
close c_emp_ename;
end

16、 cursor for循环及其替代语句
a、 先定义游标，之后用in(cursor_name)的方式使用该循环
cursor cursor_dept is select deptno ,dname from dept order by deptno;
for var in cursor_dept loop
在这里可以使用var来得到游标所指数据
end loop
b、 采用in(查询语句)的方式使用该循环
for var  in(select deptno ,dname from dept order by deptno;) loop
在这里可以使用var来得到游标所指数据
end loop

17、 显示游标属性
%found：if c_emp_ename %fount then … end if;
% notfount：exit when c_emp_ename %notfound;
%isopen：if c_emp_ename % isopen then … end if;
%rowcount：提取次数if c_emp_name %rowcount >10 then … end if

18、 隐式游标（SQL游标）
用来处理insert、update、delete和返回一行的select into语句，对这几个语句进行操作时判断处理结果用的。
不可使用open、fetch和close进行操作。
也包含%fount、%notfount、%isopen(总是false)、%rowcount。

19、 异常处理
a、 异常的抛出方式
pl/sql运行时
raise exception_name
调用raise_application_erroe
b、 exception
when exception_name then
    处理代码；
when exception_name then
    处理代码；
when others then
    处理代码；
c、 自定义异常
declare
  exceptin_name exception;
begin
  statements;
raise <exception_name>
exception
  when <exception_name> then
end;

20、 子程序

1、 存储过程
create [or replace] procedure <procedure_name>
(<arg1[in|out|in out] ,datatype,……>)
is|as
[local declaration]
begin
executable statements
[exception handler]
edn [procedure_name]

2、 函数
create [or replace] function <function_name>
(<arg1[mode],datatype>,……)
return<datatype> is|as
[local declaration]
begin
executable statements
[exception handler]
end [function_name]

函数和过程都可以通过参数列表接收或返回另个或多个值；函数和过程的主要区别在于他们的调用方式，过程是作为一个独立的执行语句调用的，而调用函数后需将函数的返回值赋值给某一变量。
3、 包
包定义：
create [or replace] package package_name {as|is}
public_variable_declarations|
public_type_declarations|
public_exception_declarations|
public_cursor_declarations|
function_declarations声明|
procedure_specifications声明
end [package_name]
包主体：
create [or replace] package body package_name {as|is}
public_variable_declarations|
public_type_declarations|
public_exception_declarations|
public_cursor_declarations|
function_declarations实现|
procedure_specifications实现
end [package_name]

4、 触发器
create [or replace] trigger trigger_name 触发事件 触发事件
on {table_or_view_name|database}
[referencing [old [as] <old_name>][new [as] <new_name>]]      //更新时用
[for each row [when condition]]      //加上则为行级触发，否则为语句级触发
trigger_body

触发时间：
before：数据库动作之前触发器执行。
after：数据库动作之后触发器执行
instead of：触发器被触发，但相应的操作并不被执行，而运行的仅是触发器SQL语句本身。用在 使不可被修改的视图能够支持修改。

触发事件：
insert on：向表或视图插入一行时
update of：更新表或视图某一行时
delete on：删除表或视图某一行时
create：创建一个数据库对象时
alter：修改一个数据库对象时
drop：删除一个数据库对象时
start：打开数据库时触发触发器，在事件后触发
shutdown：关闭数据库时触发触发器，在事件前触发
logon：当一个会话建立时触发，事件前触发
logoff：关闭会话时触发，事件前触发
server：服务器错误发生时触发，事件后触发。

  条件谓词：
inserting、updationg、deleting