索引
位图索引示例:
字段 | 字段 | 索引 | 索引 | 索引 | 索引 | 索引 | 查询(xb=女 and hy=否) | ||
XB | HY | xb=男 | xb=女 | xb=未知 | hy=是 | hy=否 | 将两个索引做与运算 | ||
---|---|---|---|---|---|---|---|---|---|
男 | 是 | 1 | 0 | 0 | 1 | 0 | 0 | ||
女 | 是 | 0 | 1 | 0 | 1 | 0 | 0 | ||
女 | 否 | 0 | 1 | 0 | 0 | 1 | 1(符合条件) | ||
男 | 是 | 1 | 0 | 0 | 1 | 0 | 0 | ||
100亿 | 100亿 | - | - | - | - | - | - | ||
未知 | 否 | 0 | 0 | 1 | 0 | 1 | 0 | ||
女 | 是 | 0 | 1 | 0 | 1 | 0 | 0 |
PL/SQL编程
PL/SQL块
-- 完整语句
[DECLARE]
-- 变量声明区
BEGIN
-- 代码区
[EXCEPTION]
-- 异常处理区
END;
-- 最基本
begin
dbms_output.put_line('hello, world');
end;
-- 顺序执行
begin
dbms_output.put('hello, ');
dbms_output.put_line('world');
end;
-- 用 declare 声明变量
-- 变量必须要跟着类型
declare
name varchar2(20);
age number;
begin
-- 变量赋值
name := 'Tom';
age := 12;
dbms_output.put_line('您好,' || name);
dbms_output.put_line('您是 ' || name || ', 你现在 ' || age || ' 岁。');
end;
-- 可以带异常处理
declare
name varchar2(20);
age number;
result number;
begin
name := 'Tom';
age := 12;
result := 10/0;
dbms_output.put_line('您好,' || name);
dbms_output.put_line('您是 ' || name || ', 你现在 ' || age || ' 岁。');
exception
when others then
dbms_output.put_line(name || ', 你算错了,除数不能为 0');
end;
-- 将异常信息保存到表中
create table mylog (msg varchar2(100), createdate date default sysdate);
select * from mylog;
declare
name varchar2(20);
age number;
result number;
errmsg varchar2(40);
begin
name := 'Tom';
age := 12;
result := 10/0;
dbms_output.put_line('您好,' || name);
dbms_output.put_line('您是 ' || name || ', 你现在 ' || age || ' 岁。');
exception
when zero_divide then
errmsg := sqlerrm;
dbms_output.put_line(name || ', 你算错了,除数不能为 0');
insert into mylog (msg) values ('TOM:' || errmsg);
commit;
end;
-- 可以给变量赋予默认值,:= 或 default
-- 可以通过 constant 设定常量,即不可更改的量
-- 异常处理区,可以处理多个异常
declare
name CONSTANT varchar2(20) := 'CAT';
age number default 15;
begin
dbms_output.put_line('您好,' || name);
dbms_output.put_line('您是 ' || name || ', 你现在 ' || age || ' 岁。');
exception
when zero_divide then
dbms_output.put_line('aaaaa');
when others then
dbms_output.put_line('bbbbbbb');
end;
-- 单独为某一段代码处理异常
DECLARE
vemp emp%rowtype;
BEGIN
BEGIN
select * into vemp from emp where ename = 'KINGs';
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('ccc');
END;
dbms_output.put_line('bbb');
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('aaa');
END;
类型与赋值
DECLARE
-- Scalar 类型
a number;
b varchar2(20); -- 4000
c date;
d clob;
-- 属性类型
vempno emp.empno%type;
vsal emp.sal%type default 111;
BEGIN
dbms_output.put_line('1: ' || vsal);
-- 第一种赋值方式
vsal := 222;
dbms_output.put_line('2: ' || vsal);
-- 第二种赋值方式
select sal into vsal from emp where ename = 'KING';
dbms_output.put_line('3: ' || vsal);
-- 第三种赋值方式
update emp set sal = 444 where ename = 'KING' returning sal + nvl(comm, 0), empno into vsal, vempno;
dbms_output.put_line('4: ' || vsal);
dbms_output.put_line('5: ' || vempno);
rollback;
-- 第四种赋值方式(fetch into, 游标)
END;
-- rowtype
-- 可以进一步简化赋值
DECLARE
vemp emp%rowtype;
BEGIN
select * into vemp from emp where ename = 'KING';
dbms_output.put_line(vemp.empno || ':' || vemp.ename || ':' || vemp.sal || '/' || vemp.deptno);
END;
控制流程
if ... then ... else ... end if; case ... when ... then ... when ... then ... else ... end case; loop ... end loop; for ... in x..y loop ... end loop; while ... loop ... end loop;
-- 向数据库中插入批量数据示例
-- 用到了 for in 循环 和 if else 判断
create table haha (id int primary key, name varchar2(20));
create sequence seq_haha;
-- pl/sql
DECLARE
begin_time timestamp;
BEGIN
begin_time := systimestamp;
FOR i IN 1..100003 LOOP
insert into haha values (seq_haha.nextval, 'X_' || dbms_random.string('U', 10));
if mod(i, 1000) = 0 then
commit;
elsif i = 100003 then
dbms_output.put_line('总共花费了' || (systimestamp - begin_time) || '时间');
commit;
end if;
END LOOP;
END;
-- 以下三种方式是等效的
-- 1. for in
begin
for i in 1..100000 loop
insert into haha values (seq_haha.nextval, 'X_' || dbms_random.string('U', 10));
end loop;
commit;
end;
-- 2. while
declare
n int := 100001;
begin
while n > 1 loop
insert into haha values (seq_haha.nextval, 'X_' || dbms_random.string('U', 10));
n := n - 1;
end loop;
commit;
end;
-- 3. loop exit
declare
n int := 1;
begin
loop
insert into haha values (seq_haha.nextval, 'X_' || dbms_random.string('U', 10));
n := n + 1;
exit when n > 100000;
end loop;
commit;
end;
游标
-- 隐式游标
begin
delete from emp where sal > 9500;
-- sql%isopen
-- sql%found
-- sql%notfound
-- sql%rowcount
if sql%found then
dbms_output.put_line('you');
else
dbms_output.put_line('wu');
end if;
rollback;
end;
-- 如果结果集有多条数据,隐式游标会报错
-- 需要使用显式游标
declare
vemp emp%rowtype;
begin
select * into vemp from emp where sal > 3000;
dbms_output.put_line(vemp.ename);
end;
-- 游标的使用,分下面几步
-- 1. 定义
-- 2. 打开
-- 3. 获取数据(循环)
-- 4. 关闭
declare
vemp emp%rowtype;
-- 定义游标
cursor c_emp
is select * from emp where sal > 2000;
begin
-- 打开游标
open c_emp;
-- 提取游标当前数据
fetch c_emp into vemp;
loop
dbms_output.put_line(vemp.ename || '的工资是' || vemp.sal);
fetch c_emp into vemp;
exit when c_emp%notfound;
end loop;
-- 关闭游标
close c_emp;
end;
-- for in 循环会自动维护游标的打开与关闭
declare
cursor c_emp
is select * from emp where sal > 3000;
begin
for vemp in c_emp loop
dbms_output.put_line(vemp.ename || '的工资是' || vemp.sal);
end loop;
end;
-- 甚至可以更简
begin
for vemp in (select * from emp where mgr = 7698) loop
dbms_output.put_line(vemp.ename || '的工资是' || vemp.sal);
end loop;
end;
-- 游标可以带参数
declare
cursor c_emp(s number)
is select * from emp where sal > s;
begin
for vemp in c_emp(&sal) loop
dbms_output.put_line(vemp.ename || '的工资是' || vemp.sal);
end loop;
end;
-- 动态游标
-- 略
动态语句
两种方式:
- execute immediate
- dbms_sql package
DECLARE
name varchar2(50);
sal number;
BEGIN
-- 字符串
name := upper('&ename');
-- 动态执行
execute immediate 'select sal+nvl(comm,0) from emp where ename =''' || name || '''' into sal;
dbms_output.put_line(name || '的工资是' || sal);
END;
异常处理
异常分两部分:
- 异常编号(sqlcode)
- 异常描述(sqlerrm),可选,有的异常只有编号但没有描述
分两种:
- 系统异常,比如 data_not_found, zero_devide 等异常。
- 用户自定义异常
DECLARE myinput number; -- 声明异常 myexception exception; BEGIN myinput := &mynum; if myinput > 10 then dbms_output.put_line('OK'); elsif myinput >= 0 then -- 主动抛出异常 raise myexception; else -- 主动抛出异常, 是 raise 语句的封装 raise_application_error(-20002, '数字必须要大于0'); end if; EXCEPTION -- 捕获异常 when myexception then dbms_output.put_line('数字太小'); END;
Procedure
有了名字的 pl/sql 块,可以反复使用。
CREATE OR REPLACE PROCEDURE ptest1 (myinput number)
AS
-- 声明异常
myexception exception;
BEGIN
if myinput > 10 then
dbms_output.put_line('OK');
elsif myinput >= 0 then
-- 主动抛出异常
raise myexception;
else
-- 主动抛出异常, 是 raise 语句的封装
raise_application_error(-20002, '数字必须要大于0');
end if;
EXCEPTION
-- 捕获异常
when myexception then
dbms_output.put_line('数字太小');
END;
-- 存储过程的参数类型
create or replace procedure pxxx (name in varchar2, -- 不能指定长度
s out number, c out number)
is
begin
select sal, nvl(comm, 0) into s, c from emp where ename = name;
end;
-- 调用过程
declare
s number;
c number;
begin
pxxx('KING', s, c);
dbms_output.put_line(s || '----' || c);
end;
Function
函数跟过程的区别在于,它有一个明显的返回值,可以在 sql 语句中直接调用。
-----------------------------------------
-- 定义一个函数,从 emp 中查询某人的工资 --
-----------------------------------------
create or replace function pyyy(name in varchar2) return number is
r number;
begin
select sal + nvl(comm, 0) into r from emp where ename = name;
return r;
end;
-- 函数的调用
select pyyy('CLARK') from dual;
select * from emp where sal + nvl(comm, 0) >= pyyy('KING');
-------------------------
-- 定义一个函数,求平方 --
-------------------------
create or replace function mypower(input number) return number is
begin
return input * input;
end;
-- 函数的调用
select mypower(444) from dual;
select power(444, 2) from dual;
-------------------------
-- 定义一个函数,求明天 --
-------------------------
create or replace function my_next_day return date is
begin
return sysdate + 1;
end;
-- 函数的使用
select my_next_day from dual;
导入/导出
imp/exp 命令
查看帮助
exp help=y
将 scott 用户下所有的表都导出来,放到 aaa.sql 文件中:
exp system/pwd file=D:\aaa.dmp owner=(scott);
将数据导入到 vip 用户中:
imp system/pwd file=D:\aaa.dmp fromuser=(scott) touser=(vip);