Oracle10g学习笔记
《Oracle10g学习笔记》
第一节
-- 安装好Oracle10g，scott默认为锁定状态；
-- dos命令符启动sqlplus：
-- sqlplus /nolog
-- 解锁：
conn sys/pass as sysdba;
alter user scott account unlock;
commit;
quit
conn scott/tiger//请输入新密码，并确认后OK
-- 开始记录所有操作
spool c:\name.sql
-- 关闭操作记录
spool off
-- 创建用户(dba)
create user miqiang identified by pass;
-- 授权（连接、开发者）
grant connect, resource to miqiang;
-- grant (create|alter|drop) user to miqiang;
-- grant (create|alter|drop) [any] (table|index|sequence|...) to miqiang;
-- grant (all|select|update|delete|insert|alter) on (tableName|viewName|...) to miqiang; -- 撤销权限
revoke connect, resource from miqiang;
-- 删除用户
drop user miqiang;
drop user miqiang cascade; -- 级联删除（慎用）
-- 格式化输出长度
col id format 9999;
col ename format a10;
-- 简单查看当前用户的表
select * from tab;
-- 简单查看当前用户的对象
select * from cat;
-- 查看表结构
desc scott.emp;
-- 查看当前用户现有表
select table_name, user from user_tables;
-- 查看所有用户现有表
select table_name, user from all_tables;
-- 查看当前用户下的所有对象
select object_name, object_type from user_objects;
-- 查看所有对象
select object_name, object_type from all_objects;
-- 查看所有用户（需要sysdba权限）
select userName, lock_date from dba_users;
select * from all_users;
-- Oracle数据类型（※推荐）：
※char 定长字符串2000bytes
※varchar2 变长字符串4000bytes 索引最大749 nchar 定长国际码2000bytes
nvarchar2变长国际码4000bytes
※date 年月日时分秒
long 超长字符串2G
blob 二进制数据4G
※clob 字符数据4G
※number(p,s) 数字类型（p为整数位，s为小数位）最高38位
※integer 整数类型
float 双精度浮点类型
-- Oracle10g的新特性flashback闪回区：
-- 查看是否开启了闪回功能
select flashback_on from v$database;
-- 恢复被误删除的表
flashback table tableName（原名）to before drop;
-- 删除表，不进入回收站
drop table tableName purge;
-- 清空回收站
purge recyclebin;
-- 清除回收站中的某个表
purge table tableName（原名）
-- 运算符
=(等于)、!= (不等于)、^=(不等于)、< >(不等于)、
<(小于)、>(大于)、<=(小于等于)、>=(大于等于)、
in(列表)、not in(不在列表)、between(介于之间)、not between (不介于之间)、like(模式匹配)、not like (模式不匹配)、
is null (为空)、is not null(为不空)。
-- 逻辑比较符
and、or、not
-- 算数运算符
+、-、*、/
-- 指定列名
as
-- 可以查询纯算术表达式，sys.dual表只有一个数据，是专门用来方便我们计算各种表达式的表
select 3*9 from sys.dual;
-- 还可以查询当前的日期表达格式
select sysdate from sys.dual;
-- 字符串联接符||
select '张三' || '你好' as hello from sys.dual;
-- 两个“单引号”代表一个字符“单引号”
select '''张三''' || '你好' as hello from sys.dual;
-- 检索前五行数据
select rownum, id, name from scott.students where rownum <= 5;
-- 分组查询group by colName having
select school from students where id>2 group by school having school in ('石油', '邮电', '西电');
-- 排序order by colName (desc|asc)
select * from students order by age desc, id asc;
***************** 导出*****************
-- 在dos命令提示符下：
-- 将数据库TEST完全导出，用户名system 密码pass 导出到D:\daochu.dmp中
exp system/pass@TEST file=d:\daochu.dmp full=y
-- 将数据库中system用户与sys用户的表导出
exp system/pass@TEST file=d:\daochu.dmp owner=(system,sys)
-- 将数据库中的表table1 、table2导出
exp system/pass@TEST file=d:\daochu.dmp tables=(table1,table2)
***************** 导入*****************
-- 导入全部和某用户的全部
imp system/pass@TEST file=d:\daochu.dmp full=Y
-- 导入某表
imp system/pass@TEST file=d:\daochu.dmp tables=(table1)
http://localhost:1158/em
http://localhost:5560/isqlplus/
*********************************************************************
********************************第二节*******************************
*********************************************************************
-- 子查询：
-- 1、简单子查询：
select * from students where teacher_id = (select id from teachers where teacherName='米老师'); -- 2、带in的子查询：
select * from students where teachers_id in (select id from teachers);
-- 3、带all和any的子查询：
-- 1）all：表示全部，>all表示大于子查询中查询到的所有值
-- 2）any：表示任意一个，>any表示大于子查询中查询到的任意一个值（注：some 与any等效）
-- 4、带exists的子查询：
select * from students where exists (select * from teachers where students.teachers_id=teachers.id);
-- 5、带union的子查询（注：两个查询的列类型须完全一致）：
select studentName from students
union
select teacherName from teachers;
-- 6、带intersect的子查询，两个结果集的交集：
select studentsName from students
intersect
select teacherName from teachers;
-- 7、带minus的子查询，两个结果集的差集：
select studentsName from students
minus
select teacherName from teachers;
-- 函数：
-- 1、ceil()函数：
-- 用法：ceil（n），取大于等于数值n的最小整数。
-- 2、mod()函数：
-- 用法：mod（m,n），取m整除n后的余数。
-- 3、power()函数：
-- 用法：power（m,n），取m的n次方。
-- 4、round()函数：
-- 用法：round（m,n），四舍五入，保留n位。
-- 5、sign()函数：
-- 用法：sign（n）。n>0，取1；n=0，取0；n<0，取-1。
-- 6、avg()函数：
-- 用法：avg（字段名），求平均值，要求字段为数值型。
-- 7、count()函数：
-- 用法：count（字段名）或count（*），统计总数。
-- 8、min()函数：
-- 用法：min（字段名），计算数值型字段的最小数。
-- 9、max()函数：
-- 用法：max（字段名），计算数值型字段的最大数。
-- 10、sum()函数：
-- 用法：sum（字段名），计算数值型字段总和。
-- 11、to_char([,])函数：
-- 用法：to_char(100) to_char(字段名, 格式)。
-- 例子：to_char(1234.5678, '$99,999.99')
-- to_char(sysdate, 'yyyy-MM-dd HH:mi:ss')
-- 12、to_date(,)函数：
-- 用法：to_date('1980-12-31 23:59:59', 'yyyy-MM-dd HH24:mi:ss')。
-- 13、to_number(,)函数：
-- 用法：to_number('$2,000.00', '$99,999.99')。
-- 14、nvl(,)函数：
-- 用法：nvl(comm, 0)，如果遇到null值，则用0替代。
-- 15、lower()upper()函数：
-- 用法：lower(字段名) upper(字段名) ，大小写转换函数。
-- 16、substr(,,)函数：
-- 用法：substr('1234567890', 2, 5) ，截取字符串函数，5为截取长度。-- 17、chr() ascii()函数：
-- 用法：chr(97) ascii('a') ，字符和ASCII互相转换函数。
-- 日期：
-- 修改本地日期显示格式（仅针对本次会话）：
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
-- 日期格式
格式元素说明
AD 或 A.D. 带有或不带有句号的AD 标记
BC 或 B.C. 带有或不带有句号的BC 标记
D 一周中的天(1-7)
DAY天的名称(Sunday - Saturday)
DD 一月中的天(1 - 31)
DDD 一年中的天(1 - 366)
DY天的缩写(Sun - Sat)
HH 一天中的小时(1 - 12)
HH24 一天中的小时(0 - 23)
MI 分钟(0-59)
MM 月(01-12)
MON 月名称的缩写
MONTH 月的名称
SS 秒(0-59)
YYYY 4 个数字表示的年
-- 表复制：
create table tea
as (select * from teachers where rownum <= 100);
-- PL/SQL结构示例：
set serveroutput on -- 打开输出开关
declare
myid int not null := 100; -- :=赋值
pi constant number(20,10) := 3.1415926535; -- constant为常量
myName scott.teachers.teacherName%type := '张三'; -- 变量类型为teachers表teacherName字段类型
type mydata is record -- 自定义“记录”类型
(
aid int,
adate date
);
md mydata;
rowdata scott.teachers%rowtype; -- 行数据类型
begin
select id, dt into md from scott.test where id=100;
select * into rowdata from scott.test where id=101;
dbms_output.put_line('编号：'||myid||' 姓名：'||myName||' PI：'||pi||' aId：'||md.adate||' rowdata_id：'||rowdata.id);
commit;
end;
/
-- （注：PL/SQL中允许使用乘方运算符5**2）
*********************************************************************
********************************第三节*******************************
*********************************************************************
-- if语句：
declare
age number(3) := 70;
begin
if age>18 then
if age>60 then
dbms_output.put_line('老人');
else
dbms_output.put_line('成人');
end if;
else
dbms_output.put_line('儿童');
end if;
end;
-- elsif语句：
begin
if(1 = 1)then
dbms_output.put_line('等于1');
dbms_output.put_line('真的等于1');
elsif(1 = 2)then -- 注意这里“elsif”
dbms_output.put_line('等于2');
else
dbms_output.put_line('大于2');
end if;
commit;
end;
-- case分支语句
declare
x int := 3;
a varchar2(20);
begin
case x
when 1 then a := 'a';
when 2 then a := 'b';
when 3 then a := 'c';
else a := 'x';
end case;
dbms_output.put_line(a);
commit;
end;
x varchar2(20) := 'abc';
a varchar2(20);
begin
case x
when 'aaa' then a := 'a';
when 'bbb' then a := 'b';
else a := 'x';
end case;
dbms_output.put_line(a);
commit;
end;
declare
x int := 2;
a varchar2(20);
begin
case
when x = 1 then a := 'a';
when x = 2 then a := 'b';
else a := 'x';
end case;
dbms_output.put_line(a);
commit;
end;
-- loop循环语句：
declare
i int := 0;
begin
loop
dbms_output.put_line(i);
if i>=10 then
exit;
else
i := i + 1;
end if;
end loop;
commit;
end;
i int := 0;
begin
loop
dbms_output.put_line(i);
i := i + 1;
exit when i>=10;
end loop;
commit;
end;
declare
i int := 0;
begin
while i<10
loop
dbms_output.put_line(i);
i := i + 1;
end loop;
commit;
end;
begin
for i in 0 .. 10 loop
dbms_output.put_line(i);
end loop;
commit;
end;
-- 自定义函数
create or replace function myMax(num1 number, num2 number) return number is
maxNum number;
begin
if num1 > num2 then
maxNum := num1;
else
maxNum := num2;
end if;
return maxNum;
end;
-- 查看自定义函数
select object_name from user_objects where object_type='FUNCTION';
-- 执行自定义函数
begin
dbms_output.put_line(myMax(3, 30));
end;
-- 删除自定义函数
drop function myMax;
-- 带in和out的自定义函数
create or replace function myf(num1 in number, num2 out number) return number
is
begin
num2 := num1 + num1;
return num1 + num2;
end;
-- 执行
declare
num1 number;
num2 number;
begin
num1 := myf(10, num2);
dbms_output.put_line(num1 || ' ' || num2);
end;
********************************************************************* ********************************第四节******************************* *********************************************************************
-- 创建表、索引和约束：
create table teachers
(
id number(10) not null,
teacherName varchar2(20) not null,
sex varchar2(2) default '男' not null,
age number(3) not null,
constraint pk_teachers_id primary key (id),
constraint un_teachers_teacherName unique (teacherName),
constraint ck_teachers_sex check (sex in ('男', '女')),
constraint ck_teachers_age check (age between 1 and 100)
);
create table students
(
id number(10) primary key,
studentName varchar2(20) unique not null,
sex varchar2(2) default '男' check(sex in ('男', '女')) not null, --default必须在check之前age number(3) check(age between 1 and 100) not null,
teacherId number(10) references teachers(id) on delete cascade
);
-- 创建索引：
create index ix_teachers_age on scott.teachers(age);
create index ix_students_age on scott.students(age);
-- 视图授权（sysdba）：
-- grant create view to scott
-- 视图：
create view v_ts
as
select t.id teacherId, t.teacherName, t.sex teacherSex, t.age teacherAge, s.id studentId, s.studentName, s.sex studentSex, s.age studentAge from teachers t inner join students s on t.id = s.teacherId;
-- 插入测试数据：
insert into teachers (id, teacherName, sex, age) values (100, '张老师', default, 30);
insert into teachers (id, teacherName, sex, age) values (101, '李老师', default, 30);
insert into teachers (id, teacherName, sex, age) values (102, '王老师', default, 30);
insert into teachers (id, teacherName, sex, age) values (103, '胡老师', default, 30);
insert into students (id, studentName, sex, age, teacherId) values (1, '小张', default, 19, 101); insert into students (id, studentName, sex, age, teacherId) values (2, '小李', default, 15, 103); insert into students (id, studentName, sex, age, teacherId) values (3, '小王', default, 18, 102); insert into students (id, studentName, sex, age, teacherId) values (4, '小海', default, 15, 103); insert into students (id, studentName, sex, age, teacherId) values (5, '小宋', default, 17, 101); insert into students (id, studentName, sex, age, teacherId) values (6, '小梅', '女', 16, 100);
insert into students (id, studentName, sex, age, teacherId) values (7, '小夏', '女', 16, 101);
insert into students (id, studentName, sex, age, teacherId) values (8, '小江', '女', 17, 103); commit;
-- 查询视图：
select * from v_ts;
-- 删除索引
drop index ix_teachers_age;
-- 删除视图
drop view v_ts;
-- 删除数据，注意顺序：
delete from scott.students;
delete from scott.teachers;
-- 删除表，注意顺序：
drop table scott.students purge;
drop table scott.teachers purge;
-- 创建表
create table scott.ttt
(
id number(8) not null,
constraint ck_ttt_id check (id>100) -- 添加约束
);
-- 修改表，添加一列
alter table scott.ttt
add userName varchar2(20) not null;
-- 修改表，添加多列
alter table scott.ttt
add
(
sex varchar2(20) default '男',
age number(3) not null
);
-- 修改表，添加一个约束
alter table scott.ttt
add constraint ck_ttt_sex check (sex in ('男', '女'));
-- 修改表，添加多个约束
alter table scott.ttt
add
(
constraint pk_ttt primary key (id),
constraint ck_ttt_age check (age between 16 and 120)
);
-- 修改表，删除一个约束（删除某一列，该列上的所有约束都将被删除，偶尔需要单独删除某一约束，可用本方法）
alter table scott.ttt
drop constraint ck_ttt_sex;
-- 删除一列
alter table scott.ttt
drop (userName);
-- 删除多列
alter table scott.ttt
drop (sex, age);
-- 新建表
create table scott.test
(
id number(8) primary key,
userName varchar2(20) not null,
sex varchar2(4) default '男' not null,
mydate date default sysdate not null,
constraint unique_test_userName unique (userName),
constraint check_test_sex check (sex in ('男', '女'))
);
-- 游标操作：
declare
cursor c_test is select id, userName from scott.test; -- 1、定义游标
r_test c_test%rowtype; -- 游标行类型
begin
open c_test; -- 2、打开游标
loop
fetch c_test into r_test; -- 赋值
exit when c_test%notfound; -- 循环结束条件
dbms_output.put_line('编号：'||r_test.id||' 姓名：'||r_https://www.360docs.net/doc/c414921372.html,erName);
end loop;
dbms_output.put_line('<游标中有'||c_test%rowcount||' 行数据>');
close c_test; -- 3、关闭游标
commit;
end;
-- 触发器
create or replace trigger t_test_userName
before insert or update -- insert|update|delete
of userName
on scott.test
referencing new as new_value -- 新值
old as old_value -- 旧值
for each row
when(new_https://www.360docs.net/doc/c414921372.html,erName != 'admin') -- 条件可省略
begin
:new_https://www.360docs.net/doc/c414921372.html,erName := :new_https://www.360docs.net/doc/c414921372.html,erName || '_user';
end;
-- 插入数据
insert into scott.test (id, userName, sex) values (001, 'admin', '男');
insert into scott.test (id, userName, sex) values (100, '张三', '男');
insert into scott.test (id, userName, sex) values (101, '小丽', '女');
-- 删除触发器
drop trigger scott.t_test_userName;
-- 创建序列
create sequence sq_test_index -- 创建序列，序列名sq_test_index
increment by 1 -- 步长为1，表明升序排列，可为负数
start with 1 -- 从1开始
maxvalue 999999999 -- 设置最大值
minvalue 1 -- 设置最小值
nocycle -- 不循环
cache 100 -- 设置缓冲序列个数
noorder -- 不保证序列按顺序递增（默认值）
;
-- scott.sq_test_index.nextval：第一次引用nextval，返回序列的初始值。后面每次引用nextval，用已定义的步长增加序列值，并返回序列新的增加以后的值。
-- scott.sq_test_index.currval：在引用currval之前必须先引用nextval。因为currval的引用返回指定序列的当前值，该值是最后一次对nextval的引用所返回的值。用nextval生成一个新值以后，可以继续使用currval访问这个值，不管另一个用户是否增加这个序列。
-- 使用序列向数据表中插入编号
insert into scott.test (id, userName, sex) values (scott.sq_test_index.nextval, '秦始皇', '男');
-- ID触发器
create or replace trigger scott.insertTestId
before insert
on scott.test
referencing
new as new_value
for each row
begin
select sq_test_index.nextval into :new_value.id from dual;
end;
*********************************************************************
********************************第五节*******************************
*********************************************************************
-- 简单存储过程
create or replace procedure pro_hello
as -- as | is
begin
dbms_output.put_line('Hello world!');
end;
/
-- 执行
execute pro_hello; -- 小括号可以省略
-- 执行
call pro_hello(); -- 必须带小括号
-- 执行
begin
pro_hello;
end;
/
-- 带参数的存储过程
create or replace procedure pro_hello
(
name varchar2 -- 参数不能定义长度，多个参数用逗号“,”隔开)
as
begin
dbms_output.put_line('Hello ' || name || ' !');
end;
/
-- in 、out 、in out
create or replace procedure pro_hello
(
name in varchar2, -- 输入参数
hello in out varchar2, -- 输入、输出参数
address out varchar2 -- 输出参数
)
as
begin
hello := name || '，' || hello || '，';
address := '一起去机房吧！';
end;
-- 执行存储过程
declare
hello varchar(20) := '下午好';
address varchar(50);
begin
pro_hello('张三', hello, address);
dbms_output.put_line(hello || address);
end;
/
-- 带默认值的存储过程
create or replace procedure pro_hello
(
name in varchar2 default '小丽',
hello in varchar2 default '早上好',
address in varchar2 default '一起去机房吧！'
)
as
begin
dbms_output.put_line(name || '，' || hello || '，' || address); end;
/
-- 执行带默认值的存储过程（1）
begin
pro_hello();
end;
/
-- （2）
begin
pro_hello('小海');
end;
/
-- （3）
begin
pro_hello('小海', '下午好');
end;
/
-- （4）
begin
pro_hello('小海', '下午好', '一起去打篮球吧！');
end;
-- （5）指定参数名称
begin
pro_hello(hello => '下午好', address => '一起去打篮球吧！');
end;
/
-- 如果已存在test表，则删除
declare
num number := 0;
begin
select count(*) into num from tab where tname='TEST';
if (num = 1) then
dbms_output.put_line('删除原有test表！');
execute immediate 'drop table scott.test purge'; -- 执行删除表操作end if;
end;
/
-- 新建表
create table scott.test
(
id number(8) primary key,
userName varchar2(20) not null,
sex varchar2(4) default '男' not null,
mydate date default sysdate not null,
constraint unique_test_userName unique (userName),
constraint check_test_sex check (sex in ('男', '女'))
);
-- test表需要的序列
create sequence s_test;
-- 插入数据的存储过程（增、删、改操作同理）
create or replace procedure pro_insertTest
(
userName in https://www.360docs.net/doc/c414921372.html,erName%type,
sex in scott.test.sex%type,
mydate in scott.test.mydate%type,
id out scott.test.id%type
)
as
begin
select s_test.nextval into id from sys.dual;
insert into scott.test (id, userName, sex, mydate) values (id, userName, sex, mydate);
end;
/
-- 执行插入数据的存储过程
declare
id scott.test.id%type;
begin
pro_insertTest('刘罗锅', '男', to_date('2000-12-12', 'YYYY-MM-DD'), id);
dbms_output.put_line('新增数据编号为：' || id);
end;
/
-- 通过“包”创建查询数据的存储过程
-- 创建程序包头
create or replace package package_test
as
type c_test is ref cursor; -- 检索数据的游标
procedure pro_findTest(cs out scott.package_test.c_test);
end;
/
-- 创建程序包体
create or replace package body package_test
as
procedure pro_findTest
(
cs out scott.package_test.c_test
)
as
begin
open cs for select * from scott.test;
end pro_findTest;
end;
/
-- 执行包中的存储过程，检索所有数据
declare
c_test scott.package_test.c_test; -- 声明包中的游标变量
r_test scott.test%rowtype; -- 要检索数据的行类型
begin
package_test.pro_findTest(c_test);
loop
fetch c_test into r_test; -- 赋值
exit when c_test%notfound;
dbms_output.put_line('编号：'||r_test.id||' 姓名：'||r_https://www.360docs.net/doc/c414921372.html,erName);
end loop;
dbms_output.put_line('<游标中有'||c_test%rowcount||' 行数据>');
close c_test; -- 关闭游标
commit;
end;
/
-- 删除包体
drop package body package_test;
-- 删除包头
drop package package_test;
-- 过程中的异常处理
create or replace procedure pro_hello
(
name varchar2
)
as
ex_nameNull exception; -- 定义姓名空异常变量
ex_nameAdmin exception; -- 定义特殊姓名禁止异常变量
begin
if name is null then
raise ex_nameNull; -- 发起姓名空异常
end if;
if name = 'admin' then
raise ex_nameAdmin; -- 发起特殊姓名禁止异常
end if;
dbms_output.put_line('Hello ' || name || ' !');
exception -- 异常部分
when ex_nameNull then -- 处理空异常
dbms_output.put_line('姓名不能为空！');
when ex_nameAdmin then -- 处理特殊姓名禁止异常
dbms_output.put_line('不能问候管理员！');
end;
/
execute pro_hello(null); -- 引发空异常
execute pro_hello('admin'); -- 引发特殊姓名禁止异常
execute pro_hello('小丽'); -- 不引发异常
-- Oracle分页语句
select * from
(
select t.*, rownum rn from (
select * from test order by id desc -- 控制排序方式（asc|desc）
) t where rownum <= 30 -- pageId * pageSize
) where rn > 20 -- (pageId - 1) * pageSize
;
-- JDBC
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:mqdb";
-- JDBC执行“过程”插入数据，返回数据主键值
String sql = "begin insert into https://www.360docs.net/doc/c414921372.html,ers values (s_users.nextval, '小丽') returning id into ?; end;"; CallableStatement call = conn.prepareCall(sql);
call.registerOutParameter(1, OracleTypes.NUMBER);
int count = call.executeUpdate();
if (count == 1) {
int key = call.getInt(1);
}