Oracle基础（摘）

来源：互联网发布：天津菜鸟网络招聘编辑：程序博客网时间：2024/05/17 06:30

一，简单介绍

-- 表
create table test (names varchar2(12),
                   dates date,
                   num   int,
                   dou   double);
-- 视图
create or replace view vi_test as
select * from test;

-- 同义词
create or replace synonym aa
for dbusrcard001.aa;

-- 存储过程
create or replace produce dd(v_id in employee.empoy_id%type)
as
begin

end
dd;

-- 函数
create or replace function ee(v_id in employee%rowtype) return varchar(15)
is
var_test varchar2(15);
begin
return var_test;
exception when others then

end

-- 三种触发器的定义
create or replace trigger ff
alter delete
on test
for each row
declare
begin
   delete from test;
   if sql%rowcount < 0 or sql%rowcount is null then
      rais_replaction_err(-20004,"错误")
   end if
end

create or replace trigger gg
alter insert
on test
for each row
declare
begin
   if :old.names = :new.names then
      raise_replaction_err(-2003,"编码重复");
   end if
end

create or replace trigger hh
for update
on test
for each row
declare
begin
if updating then
if :old.names <> :new.names then
reaise_replaction_err(-2002,"关键字不能修改")
end if
end if
end

-- 定义游标
declare
   cursor aa is
      select names,num from test;
begin
   for bb in aa
   loop
        if bb.names = "ORACLE" then

        end if
   end loop;

end

-- 速度优化，前一语句不后一语句的速度快几十倍
select names,dates
from test,b
where test.names = b.names(+) and
b.names is null and
b.dates > date('2003-01-01','yyyy-mm-dd')

select names,dates
from test
where names not in ( select names
                       from b
                      where dates > to_date('2003-01-01','yyyy-mm-dd'))

-- 查找重复记录
select names,num
from test
where rowid != (select max(rowid)
                 from test b
                where b.names = test.names and
                      b.num = test.num)

-- 查找表TEST中时间最新的前10条记录
select * from (select * from test order by dates desc) where rownum < 11

-- 序列号的产生
create sequence row_id
minvalue 1
maxvalue 9999999999999999999999
start with 1
increment by 1

insert into test values(row_id.nextval,....)

二，Oracle PL/SQL游标的学习

一游标是什么

游标字面理解就是游动的光标。

用数据库语言来描述：游标是映射在结果集中一行数据上的位置实体，有了游标，用户就可以访问结果集中的任意一行数据了，将游标放置到某行后，即可对该行数据进行操作，例如提取当前行的数据等。

二游标的分类

显式游标和隐式游标

显式游标的使用需要4步：

1. 声明游标

CURSOR mycur(vartype number) is
select emp_no,emp_zc from cus_emp_basic
where com_no = vartype;

2. 打开游标

open mycur(000627)

注：000627是参数

3. 读取数据

fetch mycur into varno, varprice;

4. 关闭游标

close mycur;

三游标的属性

oracle 游标有4个属性：%ISOPEN，%FOUND，%NOTFOUND，%ROWCOUNT。

%ISOPEN判断游标是否被打开，如果打开%ISOPEN等于true,否则等于false；

%FOUND %NOTFOUND判断游标所在的行是否有效，如果有效，则%FOUNDD等于true，否则等于false；

%ROWCOUNT返回当前位置为止游标读取的记录行数。

四示例

set serveroutput on;
declare
varno varchar2(20);
varprice varchar2(20);

CURSOR mycur(vartype number) is
select emp_no,emp_zc from cus_emp_basic
where com_no = vartype;
begin

if mycur%isopen = false then
open mycur(000627);
end if;

fetch mycur into varno,varprice;
while mycur%found
loop
dbms_output.put_line(varno||','||varprice);
if mycur%rowcount=2 then
exit;
end if;
fetch mycur into varno,varprice;
end loop;

close mycur;
end;

PL/SQL记录的结构和C语言中的结构体类似，是由一组数据项构成的逻辑单元。

PL/SQL记录并不保存在数据库中，它与变量一样，保存在内存空间中，在使用记录时候，要首先定义记录结构，然后声明记录变量。可以把PL/SQL记录看作是一个用户自定义的数据类型。

set serveroutput on;
declare

type person is record
(
empno cus_emp_basic.emp_no%type,
empzc cus_emp_basic.emp_zc%type);

person1 person;

cursor mycur(vartype number)is
select emp_no,emp_zc from cus_emp_basic
where com_no=vartype;

begin
if mycur%isopen = false then
open mycur(000627);
end if;

loop
fetch mycur into person1;
exit when mycur%notfound;
dbms_output.put_line('雇员编号:'||person1.empno||',地址:'||person1.empzc);
end loop;

close mycur;
end;

典型游标for 循环

游标for循环示显示游标的一种快捷使用方式，它使用for循环依次读取结果集中的行数据，当form循环开始时，游标自动打开（不需要open），每循环一次系统自动读取游标当前行的数据（不需要fetch)，当退出for循环时，游标被自动关闭（不需要使用close）。使用游标for循环的时候不能使用open语句，fetch语句和close语句，否则会产生错误。

set serveroutput on;
declare

cursor mycur(vartype number)is
select emp_no,emp_zc from cus_emp_basic
where com_no=vartype;

begin
for person in mycur(000627) loop
dbms_output.put_line('雇员编号:'||person.emp_no||',地址:'||person.emp_zc);
end loop;
end;

三，使用游标复制table的内容
--利用游标复制表(效率不高)
declare
cursor csr_1 is
select ks_xm,ks_zkz,bk_cj from ahzk_all_cj;
i number;
begin
for idx in csr_1 loop
insert into tmp values(idx.ks_xm,idx.ks_zkz,idx.bk_cj);
i:=csr_1%rowcount; --获取当前记录数
if(i mod 1000=0) then
dbms_output.put_line(i);
commit;
end if;
end loop;
commit;
end;

--附：高效复制表
declare

begin
insert into tmp
select ks_xm,ks_zkz,bk_cj from ahzk_all_cj;
if(sql%found) then
dbms_output.put_line('插入' || sql%rowcount || '行记录');
end if;
commit;
end;

三，怎样看oracle查询语句执行计划

SQLPLUS的AutoTrace是分析SQL的执行计划，执行效率的一个非常简单方便的工具，在绝大多数情况下，也是非常有用的工具。
1。如何设置和使用AUTOTRACE

SQL> connect / as sysdba

SQL> @?/rdbms/admin/utlxplan.sql

Table created.

SQL> create public synonym plan_table for plan_table;

Synonym created.

SQL> grant select,update,insert,delete on plan_table to public;

Grant succeeded.

SQL> @?/sqlplus/admin/plustrce.sql

SQL>grant plustrace to public.

2. 理解和使用AutoTrace

对于SQL 调整，使用Autotrace是最简单的方法了，我们只需要做：

SQL>SET AUTOTRACE ON

我们就可以看到我们SQL的执行计划，执行成本（PHYSICAL READ/CONSISTENT READ...)

加上SET Timing On或者Set Time On，我们可以得到很多我们需要的数据。

然后在toad里面对某一条sql语句按下Ctrl+e就可以看到这条语句的执行计划了。