Oracle 数据类型
VARCHAR2(size)
可变长度的字符串,其最大长度为size个字节;size的最大值是4000,而最小值是1;你必须指定一个VARCHAR2的size;
NVARCHAR2(size)
可变长度的字符串,依据所选的国家字符集,其最大长度为size个字符或字节;size的最大值取决于储存每个字符所需的字节数,其上限为4000;你必须指定一个NVARCHAR2的size;
NUMBER(p,s)
精度为p并且数值范围为s的数值;精度p的范围从1到38;数值范围s的范围是从-84到127;
例如:NUMBER(5,2) 表示整数部分最大3位，小数部分为2位；
NUMBER(5,-2) 表示数的整数部分最大为7其中对整数的倒数2位为0,前面的取整。
NUMBER 表示使用默认值,即等同于NUMBER(5);
LONG
可变长度的字符数据,其长度可达2G个字节;
DATE
有效日期范围从公元前4712年1月1日到公元后4712年12月31日
RAW(size)
长度为size字节的原始二进制数据,size的最大值为2000字节；你必须为RAW指定一个size;
LONG RAW
可变长度的原始二进制数据，其最长可达2G字节;
CHAR(size)
固定长度的字符数据,其长度为size个字节;size的最大值是2000字节,而最小值和默认值是1;
NCHAR(size)
也是固定长度。根据Unicode标准定义
CLOB
一个字符大型对象,可容纳单字节的字符;不支持宽度不等的字符集;最大为4G字节
NCLOB
一个字符大型对象,可容纳单字节的字符;不支持宽度不等的字符集;最大为4G字节;储存国家字符集
BLOB
一个二进制大型对象;最大4G字节
BFILE
包含一个大型二进制文件的定位器,其储存在数据库的外面；使得可以以字符流I/O访问存在数据库服务器上的外部LOB;最大大小为4G字节.
字符函数
CONCAT(字符串1,字符串2)
将字符串1和字符串2连接成一个新的字符串
示例: select CONCAT(job,ename) from emp
LPAD(字段,总的大小,添充字符)
左填充即向右对齐
示例: select empno,lpad(sal,10,'*') from emp
RPAD(字段,总的大小,添充字符)
右填充即向左对齐
示例: select empno,rpad(sal,10) from emp
LOWER(字符串)
将字符串全部变成小写;
UPPER(字符串)
将字符串全部变成大写;
INITCAP(字符串)
将字符串变成第一个字母大写,其余都变成小写;
LENGTH(字符串)
求出字符串的长度;
SUBSTR(字符串,开始位置,长度)
从字符串中取子串;
示例: select substr(ename,2,3) from emp;--从ename的第2位开始取3位
INSTR(字符串,字符)
查看字符是否在字符串中存在;不存在返回0;存在则返回字符所在的的位置；如果有两个以上的字符则返回第一个的位置.
示例:select instr(ename,'S') from emp;
TRIM(字符 FROM 字符串)
去掉字符串首尾的字符;
示例: select trim('S' from ename) from emp;
TO_CHAR()
将不是其他类型转成字符类型；
对于日期型可以控制其格式:TO_CHAR(日期,'格式');
其中格式有: 'YYYY' --以4为显示年;
'YEAR' --以标准格式显示年； 'MM' ; 'MON' ; 'DD' ; 'DAY'; 'HH' ; 'MI' ;'SS'
REPLACE(字符串,字符串1,字符串2)
将字符串中的字符1替换成字符2;
示例: select replace(ename,'SC','SS') from emp;
TRANSLATE(字符串,字符串1,字符串2)
替换多的字符;
示例: select translate(ename,'SH','AB') from emp;
--表示将ename中的'S'换成'A','H'换成'B';
ASCII(char)
求字符的ascii码
NLSSORT(字符串)
对字符串排序.
数学函数
ADD_MONTHS(日期,数字)
在以有的日期上加一定的月份;
示例:
select add_months(hiredate,20),hiredate from emp;
LAST_DAY(日期)
求出该日期的最后一天.
MONTHS_BETWEEN(日期1，日期2)
求出两个月之间的天树（注意返回的天数为小数);
示例:
select months_between(sysdate,hiredate) from emp;
NEW_TIME(时间,时区,'gmt')
按照时区设定时间.
NEXT_DAY(d,char)
返回d指定的日期之后并满足char指定条件的第一个日期
其他函数
VSIZE(类型)
求出数据类型的大小;
NVL(字符串,替换字符)
如果字符串为空则替换，否则不替换
常用命令
DESC 表名
查看表的信息.
SET SERVEROUT [ON|OFF]
设置系统输出的状态.
SET PAGESIZE <大小>
设置浏览中没页的大小
SET LINESIZE <大小>
设置浏览中每行的长度
SET AUTOPRINT [ON|OFF]
设置是否自动打印全局变量的值
SELECT SYSDATE FROM DUAL
查看当前系统时间
ALTER SESSION SET nls_date_format='格式'
设置当前会话的日期格式
示例:ALTER SESSION SET nls_date_format='dd-mon-yy hh24:mi:ss'
SELECT * FROM TAB
查看当前用户下的所有表
SHOW USER
显示当前用户
HELP TOPIC
显示有那些命令
SAVE <file_name>
将buf中的内容保存成一个文件
RUN <file_name>
执行已经保存的文件;也可以写成@<file_name>
GET <file_name>
显示文件中的内容
LIST
显示buf中的内容
ED
用记事本打开buf,可以进行修改
DEL 行数
删除buf中的单行
DEL 开始行 结束行
删除buf中的多行
INPUT 字符串
向buf中插入一行
APPEND 字符串
将字符串追加到当前行
C/以前的字符串/替换的字符串
修改buf中当前行的内容
CONNECT
连接
DISCONNECT
断开连接
QUIT
退出sql*plus
EXP
导出数据库（可以在DOS键入exp help=y 可以看到详细说明)
示例: exp scott/tiger full=y file=e:\a.dmp; --导出scott下的所有东西
exp scott/tiger tables=(emp,dept) file=e:\emp.dmp --导出scott下的 emp,dept表
IMP
导入数据库（可以在DOS键入imp help=y 可以看到详细说明)
imp scott/tiger tables=(emp,dept) file=e:\emp.dmp
异常类型
CURSOR_ALREADY_OPEN
试图"OPEN"一个已经打开的游标
DUP_VAL_ON_INDEX
试图向有"UNIQUE"中插入重复的值
INVALID_CURSOR
试图对以关闭的游标进行操作
INVALID_NUMBER
在SQL语句中将字符转换成数字失败
LOGIN_DENIED
使用无效用户登陆
NO_DATA_FOUND
没有找到数据时
NOT_LOGIN_ON
没有登陆Oracle就发出命令时
PROGRAM_ERROR
PL/SQL存在诸如某个函数没有"RETURN"语句等内部问题
STORAGE_ERROR
PL/SQL耗尽内存或内存严重不足
TIMEOUT_ON_RESOURCE
Oracle等待资源期间发生超时
TOO_MANY_ROWS
"SELECT INTO"返回多行时
VALUE_ERROR
当出现赋值错误
ZERO_DIVIDE
除数为零
数据包
定义：定义包的规范
create [or replace] package <数据包名>
as
--公共类型和对象声明
--子程序说明
end;
定义包的主体
create [or replace] package body <数据包名> as
-- 公共类型和对象声明
--子程序主体
begin
--初始化语句
end;
使用：
eg:
--创建数据包规范：
create or replace packagepack_1as
nnumber;
procedurep_1;
functionf_1return number;
end;
--创建数据包主体：
create or replace package bodypack_1as
procedurep_1is
r emp%rowtype;
begin
select*intorfromempwhereempno =7788;
dbms_output.put_line(r.empno ||' '||r.ename||' '||r.sal );
end;
functionf_1return number is
r emp%rowtype;
begin
select*intorfromempwhereempno =7788;
returnr.sal;
end;
end;
--使用包:
declare
nnumber;
begin
n:=&请输入员工号;
pack_1.n:=n;
pack_1.p_1;
n:=pack_1.f_1;
dbms_output.put_line('薪水为 '||n);
end;
在包中使用ref游标eg:
--创建数据包规范： create or replace package pack_2 as type c_type is ref cursor; --建立一个ref游标类型 procedure p_1(c1 in out c_type); --过程的参数为ref游标类型 end; --创建数据包主体: create or replace package body pack_2 as procedure p_1(c1 in out c_type) is begin open c1 for select * from emp; end; end; --使用包 var c_1 ref cursor; set autoprint on; execute pack_2.p_1(:c_1);删除包：
drop package <包名>;
触发器
创建触发器：
create [or replace] trigger <触发器名>
before|alter
insert|delete|update [of <列名>] on <表名>
[for each row]
when (<条件>)
<pl/sql块>
“for each row”指定触发器每行触发一次。
“of <列名>”不写表示整个表的所有列。
特殊变量:
:new --为一个引用最新的列值；
:old --为一个引用以前的列值；
这些变量只有使用了关键字“for each row”时才存在。且update语句两个都有，而insert只有:new,delete只有:old;
使用raise_application_error
语法：raise_application_erro(错误号(-20000到-20999),消息[,{true|false}]);
抛出用户自定义错误.
如果参数为’true’,则错误放在先前的堆栈上。
instead of 触发器
instead of 触发器主要针对视图(view) 将触发的dml语句替换成为触发器中的执行语句，而不执行dml语句。
禁用某个触发器
alter trigger <触发器名> disable
重新启用触发器
alter trigger <触发器名> enable
禁用所有触发器
alter trigger <触发器名> disable all triggers
启用所有触发器
alter trigger <触发器名> enable all triggers
删除触发器
drop trigger <触发器名>
自定义对象
create [or replace] type <对象名> as object
(
属性1 类型
属性2 类型
.
.
方法1的规范 (member procedure <过程名>)
方法2的规范 (member function <函数名> return 类型)
.
prama restric_references (<方法名>,wnds/rnds/wnds/rnps);
)
wnds --不能写入数据库状态;
rnds --不能读出数据库状态;
wnps --不能写入包状态;
rnds --不能读出包状态；
创建对象主体
create [or repalce] type body <对象名> as
方法1的规范 (member procedure <过程名> is <PL/SQL块>)
方法2规范 (member function <函数名> return 类型 is <PL/SQL块>)
end;
使用map方法或order方法
用于对自定义类型排序。每个类型只有一个map或order方法。
格式: map member function <函数名> return 类型
order member function <函数名> return number
创建对象表
create table <表名> of <对象类型>
eg:
--1 创建name类型 create or replace type name_type as object ( f_name varchar2(20, l_name varchar2(20), map member function name_map return varchar2 ); create or replace type body name_type as map member function name_map return varchar2 is ---对f_name和l_name排序 begin return f_name||l_name; end; end; --2 创建address类型 create or replace type address_type as object ( city varchar2(20), streat varchar2(20), zip number, order member function address_order(other address_type) return number; ); create or replace type body address_type as order member function address_order(other address_type) return number is --对zip进行排序 begin return self.zip-other.zip; end; end; --创建stu对象 create or replace type stu_type as object ( stu_id number(5), stu_name name_type, stu_addr address_type, age number(3), birth date, map member function sut_map return number, member procedure update age ); create or replace type body stu_type as map member function stu_map return number --对stu_id进行排序 begin return stu_id; end; member procedure update_age is --求年龄用现在时间-birth begin update student set age = to_char(sysdate,'yyyy') - to_char(birth,'yyyy') where stu_id = self.stu_id; end; end; --4 创建对象表 create table student of stu_type(primary key(stu_id)); --5向对象中表插值 insert into student values(1,name_type('关','羽'),address_type('武汉','成都路',430000),null,sysdate-365*20) --6使用对象的方法 declare aa stu_type; begin --value()将对象表的每一行转成行对象括号中为表的别名 select value(s) into aa from student s where stu_id =1; aa.update_age(); end; --7 查看类型的值 select stu_id,s.stu_name.f_name,s.stu_name.l_name from student s; -- 8 --ref()求出行对像的oid,括号中必须为表的别名; deref()将oid变成行对象； select ref(s) from student s;其他
在PL/SQL中使用DDL
将sql语句赋给一个varchar2变量，再用execute immediate这个varchar2变量即可；
eg:
declare str varchar2(200); begin str := 'create table test(id number,name varchar2(20))' execute immediate str; --但是要对这个表插入也必须使用execute immediate 字符变量 str: = 'insert into test values(2,''c'')'; execute immediate str; end;
判断表是否存在
eg:
declare n tab.tname%type; begin select tname into n from table where tname = '&请输入表名' dbms_output.put_line('此表已存在'); exception when no_data_found then dbms_output.put_line('还没有此表');\ end;
查看已有的过程：
eg:
select object_name,object_type,status from user_objects where object_type ='PROCEDURE'object_type必须为大写的procedure
PL/SQL表
pl/sql表只有两列，其中第一列为序号列为integer类型，第二列为用户自定义列。
定义：TYPE <类型名> IS TABLE OF <列的类型> [NOT NULL] INDEX BY BINARY_INTEGER;
<列的类型>可以为Oracle的数据行以及自定义类型;
属性方法：
.count --返回pl/sql表的总行数
.delete --删除pl/sql表的所有内容
.delct(行数) --删除pl/sql表的指定的行
.first --返回表的第一个index;
.next(行数) --这个行数的下一条的index;
.last --返回表的最后一个index;
使用
eg:
DECLARE TYPE mytable IS TABLE OF VARCHAR2(20) index by binary_integer; --定义一个名为mytable的PL/sql表类型; cursor c_1 is select ename from emp; n number:=1; tab_1 mytable; --为mytable类型实例化一个tab_1对象; BEGIN for i in c_1 loop tab_1(n):=i.ename; --将得到的值输入pl/sql表 n:=n+1; end loop; n:=1; tab_1.delete(&要删除的行数); --删除pl/sql表的指定行 for i in tab_1.first..tab_1.count loop dbms_output.put_line(n||' '||tab_1(n)); --打印pl/sql表的内容 n:=tab_1.next(n); end loop; EXCEPTION WHEN NO_DATA_FOUND THEN --由于删除了一行，会发生异常，下面语句可以接着删除的行后显示 for i in n..tab_1.count+1 loop dbms_output.put_line(n||' '||tab_1(n)); n:=tab_1.next(n); end loop; END;
PL/SQL记录
pl/sql表只有一行，但是有多列
定义：type <类型名> is record <列名1 类型1,列名2 类型2,…,列名n 类型n> [not null]
<列的类型>可以为Oracle的数据类行以及用户自定义类型;可以是记录类型的嵌套
使用
eg:
DECLARE TYPE myrecord IS RECORD(id emp.empno%type, name emp.ename%type,sal emp.sal%type); --定义一个名为myrecoed的PL/sql记录类型; rec_1 myrecord; --为myrecord类型实例化一个rec_1对象; BEGIN select empno,ename,sal into rec_1.id,rec_1.name,rec_1.sal from emp where empno=7788; --将得到的值输入pl/sql记录 dbms_output.put_line(rec_1.id||' '||rec_1.name||' '||rec_1.sal); --打印pl/sql记录的内容 END;
结合使用PL/SQL表和PL/SQL记录
eg:
DECLARE CURSOR c_1 is select empno,ename,job,sal from emp; TYPE myrecord IS RECORD(empno emp.empno%type,ename emp.ename%type, job emp.job%type,sal emp.sal%type); --定义一个名为myrecoed的PL/sql记录类型; TYPE mytable IS TABLE OF myrecord index by binary_integer; --定义一个名为mytable的PL/sql表类型;字段类型为PL/sql记录类型； n number:=1; tab_1 mytable; --为mytable类型实例化一个tab_1对象; BEGIN --赋值 for i in c_1 loop tab_1(n).empno:=i.empno; tab_1(n).ename:=i.ename; tab_1(n).job:=i.job; tab_1(n).sal:=i.sal; n:=n+1; end loop; n:=1; --输出 for i in n..tab_1.count loop dbms_output.put_line(i||' '||tab_1(i).empno ||' '||tab_1(i).ename||' '||tab_1(i).job||' '||tab_1(i).sal); end loop; END;
强型REF游标
定义：type <游标名> is ref cursor return <返回类型>;
操作:
open <游标名> for <select 语句> --打开游标
fetch <游标名> into 变量1,变量2,变量3,…,变量n;
或者fetch <游标名> into 行对象; --取出游标当前位置的值
属性：
%notfound
%found
%rowcount
%isopen
使用:
eg:
DECLARE type c_type is ref cursor return emp%rowtype; --定义游标 c_1 c_type; --实例化这个游标类型 r emp%rowtype; BEGIN dbms_output.put_line('行号 姓名 薪水'); open c_1 for select * from emp; loop fetch c_1 into r; exit when c_1%notfound; dbms_output.put_line(c_1%rowcount||' '||r.ename||' '||r.sal); --输出结果,需要 set serverout on 才能显示. END LOOP; close c_1; END;
弱型REF游标
定义：type <游标名> is ref cursor;
操作:
open <游标名> for <select 语句> --打开游标
fetch <游标名> into 变量1,变量2,变量3,…,变量n;
或者fetch <游标名> into 行对象; --取出游标当前位置的值
属性：
%notfound
%found
%rowcount
%isopen
使用：
eg:
set autoprint on; var c_1 refcursor; DECLARE n number; BEGIN n:=&请输入; if n=1 then open :c_1 for select * from emp; else open :c_1 for select * from dept; end if; END;
过程
定义：create [or replace] procedure <过程名> [(参数列表)] is
[局部变量声明]
begin
可执行语句;
exception
异常处理语句;
end [<过程名>];
变量的为类型:
in 为默认类型，表示输入;
out 表示只输出;
in out 表示即输入又输出;
操作已有的的过程：
在PL/SQL块中直接使用过程名;
在程序外使用 execute <过程名>[(参数列表)]
使用：
eg:
创建过程
create or replace procedure p_1(n in out number) is r emp%rowtype; BEGIN dbms_output.put_line('姓名 薪水'); select * into r from emp where empno=n; dbms_output.put_line(r.ename||' '||r.sal); --输出结果,需要 set serverout on 才能显示. n:=r.sal; END; 使用过程: declare n number; begin n:=&请输入员工号; p_1(n); dbms_output.put_line('n的值为 '||n); end;
删除过程：
drop procedure <过程名>;
函数
定义：
create [or replace] function <函数名> [(参数列表)] return 数据类型 is
[局部变量声明]
begin
可执行语句;
exception
异常处理语句;
end [<过程名>];
变量类型：
in 为默认类型，表示输入;
out表示只输出
in out 表示即可输入又输出;
使用：
eg:
创建函数：
create or replace function f_1(n number) return number is r emp%rowtype; begin dbms_output.put_line('姓名 薪水'); select * into r from where empno = n; dbms_output.put_line(r.ename || '' ||r.sal); --输出结果,需要set serverout on才能显示. retrun r.sal; end;
declare n number; m number; begin n:=&请输入员工号; m:=f_1(n); dbms_output.put_line('m的值为 '||m); end;
drop function <函数名>;