Oracle的入门之路

最新推荐文章于 2024-07-24 18:54:32 发布

Flashx2

最新推荐文章于 2024-07-24 18:54:32 发布

阅读量472

点赞数

文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/oneslagman/article/details/123307056

版权

一、Oracle前言

数据定义语言（DDL）：create drop alter
数据操纵语言（DML）：insert select delete update
事务控制语言（TCL）:commit savepoint rollback
数据控制语言（DCL）：grant revoke

--注意：建议Oracle代码不区分大小写：建议尽量用大写（因为写进去的小写最后会被转换为大写在去执行！）

--建议尽量每行代码的后面加一个分号，因为在PLSQL Developer中可以执行，但是在SQL Plus中却执行不出来结果，另外一种情况下也执行不了！（全部选起来，再去点击执行按钮会报错）。

二、Oracle中创建表和删除表以及添加约束和修改约束

创建表及约束：

create table student(
sno number not null primary key,
sname varchar2(30) not null unique,
ssex char(6) default '女',
sage number(3,0) check(sage>18 and sage<30),
saddress varchar2(50)
);
 --删除刚才创建的表
 drop table student;
 --插入一条数据
insert into student
  (sno, sname, ssex, sage, saddress)
values
  (1, 'aa', '男', 20, '教室');
--查询
select * from student;  
 --删除
 delete student where sno=1;
 --或者
 delete from student where sno=1;
 --修改
 update student set sage = 21 where sno=1;

创建表及添加修改约束：

 --创建老师表
create table teacher(
tno number(10)，
sno number,
name varchar(30)
);
--设置tno为主键
alter table teacher add constraint tno_pk primary key(tno);
--设置外键
alter table teacher add constraint sno_fk foreign key(sno) references student(sno);
--修改名字可以为空约束
alter table student modify(sname null);
--多行添加
insert into student
  (select 2,'张三','男',24,'长沙' from dual
  union
  select 3,'李四','男',25,'武汉' from dual
  union
  select 4,'王五','男',25,'武汉' from dual
);
--注意：多行插入也可以和MySQL一样

三、伪表和伪列

伪表：

--dual：伪表
select 1+1 from dual;
select lower('SDA') from dual;

伪列：

--伪列
--rowid rownum
--rowid:唯一性
--rowid:让每一条记录都唯一起来。
--伪列上查看前三条数据
select a.*,rownum from student a where rownum<4;
--或者
select a.*,rownum from student a where rownum<=3;
--注意：rownum只能小于或者小于等于，不能大于或单独的等于,oracle没有limit。

连接：

--||:连接符号，将两列并入一列
select sname||sage from student;

四、Oracle中创建表空间和删除

一、创建表空间：
--注意要用数据库管理员的身份创建（scott的权限不足）

--1.创建一个比较简单的表空间
create tablespace stu1
--创建表空间名为stu
--tablespace：表空间的关键字
datafile 'd:\stu1.dbf'
--数据文件保存在d:/stu.dbf （文件的后缀名为dbf）
--datafile:数据文件
--注意：Oracle中的字符串用单引号（''）
size 100m
--大小为100m
--2.创建比较复杂的表空间
create tablespace stu
--创建表空间名为stu
--tablespace：表空间的关键字

datafile 'd:\stu.dbf'

size 100m

AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
--autoextend on：表空间大小不够用时自动扩展
--next 32m 自动扩展增量为32MB 
--MAXSIZE UNLIMITED ：最大空间：无限制
LOGGING
--logging 是对象的属性，创建数据库对象时，oracle 将日志信息记录到练级重做日志文件中。代表空间类型为永久型!
EXTENT MANAGEMENT LOCAL
--extent management local   代表管理方式为本地
SEGMENT SPACE MANAGEMENT AUTO; 
--磁盘扩展管理方法:
--使用该选项时区大小由系统自动确定。由于 Oracle 可确定各区的最佳大小，所以区大小是可变的。
--删除表空间
drop tablespace stu1

ORACLE中，表空间是数据管理的基本方法，所有用户的对象要存放在表空间中，也就是用户有空间的使用权，才能创建用户对象．否则是不允许创建对象，因为就是想创建对象,如表,索引等，也没有地方存放,Oracle会提示:没有存储配额．

因此，在创建对象之前，首先要分配存储空间．分配存储，就要创建表空间：

　　创建表空间示例如下：

CREATE TABLESPACE "SAMPLE"
LOGGING
DATAFILE D:\ORACLE\ORADATA\ORA92\LUNTAN.ora SIZE 5M
EXTENT  MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

上面的语句分以下几部分：

第一: CREATE TABLESPACE "SAMPLE" 　创建一个名为 "SAMPLE" 的表空间.（对表空间的命名,遵守Oracle 的命名规范就可了.）

ORACLE可以创建的表空间有三种类型:

(1)TEMPORARY: 临时表空间,用于临时数据的存放;

创建临时表空间的语法如下:

CREATE TEMPORARY TABLESPACE "SAMPLE"......

(2)UNDO : 还原表空间. 用于存入重做日志文件.

创建还原表空间的语法如下:

CREATE UNDO TABLESPACE "SAMPLE"......

(3)用户表空间: 最重要,也是用于存放用户数据表空间

可以直接写成: CREATE TABLESPACE "SAMPLE"

TEMPORARY 和 UNDO 表空间是ORACLE 管理的特殊的表空间.只用于存放系统相关数据.

第二: LOGGING

有 NOLOGGING 和 LOGGING 两个选项,

NOLOGGING: 创建表空间时,不创建重做日志.

LOGGING 和NOLOGGING正好相反, 就是在创建表空间时生成重做日志.

用NOLOGGING时,好处在于创建时不用生成日志,这样表空间的创建较快,但是没能日志,数据丢失后,不能恢复,但是一般我们在创建表空间时,是没有数据的,按通常的做法,是建完表空间,并导入数据后,是要对数据做备份的,所以通常不需要表空间的创建日志,因此,在创建表空间时,选择 NOLOGGING,以加快表空间的创建速度.

第三: DATAFILE 用于指定数据文件的具体位置和大小.

如: DATAFILE D:\ORACLE\ORADATA\ORA92\LUNTAN.ora SIZE 5M

说明文件的存放位置是 D:\ORACLE\ORADATA\ORA92\LUNTAN.ora , 文件的大小为5M.

如果有多个文件,可以用逗号隔开:

DATAFILE D:\ORACLE\ORADATA\ORA92\LUNTAN.ora SIZE 5M,

D:\ORACLE\ORADATA\ORA92\dd.ora SIZE 5M

但是每个文件都需要指明大小.单位以指定的单位为准如 5M 或 500K.

对具体的文件,可以根据不同的需要,存放大不同的介质上,如磁盘阵列,以减少IO竟争.

指定文件名时,必须为绝对地址,不能使用相对地址.

第四: EXTENT MANAGEMENT LOCAL 存储区管理方法

在Oracle 8i以前,可以有两种选择,一种是在字典中管理(DICTIONARY),另一种是本地管理(LOCAL ),从9I开始,只能是本地管理方式.因为LOCAL 管理方式有很多优点.

在字典中管理(DICTIONARY): 将数据文件中的每一个存储单元做为一条记录,所以在做DM操作时,就会产生大量的对这个管理表的Delete和Update操作.做大量数据管理时,将会产生很多的DM操作,严得的影响性能,同时,长时间对表数据的操作,会产生很多的磁盘碎片,这就是为什么要做磁盘整理的原因.

本地管理(LOCAL): 用二进制的方式管理磁盘,有很高的效率,同进能最大限度的使用磁盘. 同时能够自动跟踪记录临近空闲空间的情况，避免进行空闲区的合并操作。

第五: SEGMENT SPACE MANAGEMENT

磁盘扩展管理方法:

SEGMENT SPACE MANAGEMENT: 使用该选项时区大小由系统自动确定。由于 Oracle 可确定各区的最佳大小，所以区大小是可变的。

UNIFORM SEGMENT SPACE MANAGEMENT:指定区大小，也可使用默认值 (1 MB)。

第六: 段空间的管理方式:

AUTO: 只能使用在本地管理的表空间中. 使用LOCAL管理表空间时,数据块中的空闲空间增加或减少后，其新状态都会在位图中反映出来。位图使 Oracle 管理空闲空间的行为更加自动化，并为管理空闲空间提供了更好的性,但对含有LOB字段的表不能自动管理.

MANUAL: 目前已不用,主要是为向后兼容.

第七: 指定块大小. 可以具体指定表空间数据块的大小.

创建例子如下:

1 CREATE TABLESPACE "SAMPLE"

2 LOGGING

3 DATAFILE D:\ORACLE\ORADATA\ORA92\SAMPLE.ora SIZE 5M,

4 D:\ORACLE\ORADATA\ORA92\dd.ora SIZE 5M

5 EXTENT MANAGEMENT LOCAL

6 UNIFORM SEGMENT SPACE MANAGEMENT

7* AUTO

表空间已创建。

要删除表空间,可以

DROP TABLESPACE SAMPLE;

五、Oracle中创建角色赋权和收权

--创建角色、赋权、收权时注意要用数据库管理员的身份创建（scott的权限不足）

--1.创建一个简单的角色
create user sb
--创建一个用户：用户名为sb
identified by 123
--该账号的密码为123

--2.创建一个带有表空间的角色
create user sc
--创建一个用户：用户名为sc
identified by 456
--该账号的密码为456
default tablespace stu
--默认表空间为：stu

--把CONNECT、RESOURCE角色授予用户sb
GRANT CONNECT,RESOURCE TO sb;
--允许用户查看 EMP 表中的记录
GRANT SELECT ON emp TO sb;
--允许用户更新 EMP 表中的记录
GRANT UPDATE ON emp TO sb;

--撤销用户epet的RESOURCE角色
REVOKE RESOURCE FROM sb; 

--常用系统预定义角色
--CONNECT：临时用户。
--RESOURCE：更为可靠和正式的用户。
--DBA：数据库管理员角色，拥有管理数据库的最高权限。

六、PL/SQL

--打印Hello World
begin
dbms_output.put_line('Hello World1');//输出语句
dbms_output.put_line('Hello World2');
end;

--利用声明变量实现查询7369的所有信息
--%type:取现有的数据表中的数据的类型
--%rowtype:取表中一行的数据类型
declare
eid emp.empno%type:=7369; //将emp表中的empno列的数据类型赋给eid，并给eid赋值为7369
emprow emp%rowtype; //将emp表中的一行赋给emprow
begin
  select * into emprow from emp where empno=eid; //从emp表中取出一行，并赋值给emprow
  dbms_output.put_line('编号为'||eid||'的员工姓名为'||emprow.ename);
end;

斐波那契数列

--斐波那契数列

--loop循环
declare
a number:=1;
b number:=0;
c number:=0;
i number:=1;
begin
  loop
    c:=a+b;
    a:=b;
    b:=c;
    i:=i+1;
    exit when i>6;
    end loop;
    dbms_output.put_line(c);
end;

--while循环
declare
a number:=1;
b number:=0;
c number:=0;
i number:=1;
begin
  while(i<=6)loop
  c:=a+b;
  a:=b;
  b:=c;
  i:=i+1;
  end loop;
  dbms_output.put_line(c);
end;

--for循环
declare
a number:=1;
b number:=0;
c number:=0;
i number:=1;
begin
  for i in 1..30
    loop
      c:=a+b;
      a:=b;
      b:=c;
      end loop;
      dbms_output.put_line(c);
end;

--if-else语句
--查询员工编号为7369的薪水，如果薪水大于600输出高薪水，如果小于600底薪水
declare
eid number(10):=7369;
sal emp.sal%type;
begin
  select sal into sal from emp where empno=eid;
  if sal>600 then
    dbms_output.put_line('高薪水');
    else dbms_output.put_line('底薪水');
    end if;
end;

--查询员工编号为7369的薪水，如果薪水大于1500输出高薪水，如果小于1500-900中等薪水，
--小于900底薪水
declare
eid number(10):=7369;
sal emp.sal%type;
begin
  select sal into sal from emp where empno=eid;
  if(sal>1500) then
  dbms_output.put_line('高薪水');
  elsif(sal>900) then
  dbms_output.put_line('中等薪水');
  else dbms_output.put_line('底薪水');
  end if;
end;

--case
--查询员工编号为7369的工资等级。注意：只能为固定值
--800  A     950   B   1100   C    else   no
--方法一：
declare
eid number(10):=7369;
sal emp.sal%type;
begin 
  select sal into sal from emp where empno=eid;
  case sal
    when '800' then dbms_output.put_line('A');
    when '950' then dbms_output.put_line('B');
    when '1100' then dbms_output.put_line('C');
    else dbms_output.put_line('no');
  end case;
end;

函数递归

--递归实现1+2+3+4加到100的和
create or replace function test01(n in number)
return number is
begin 
  if n=1 then return 1;
  else return (n+test01(n-1));
  end if;
  end test01;
  
select test01(100) from dual;
drop function test01;

--递归求出第21位 1,1,2,3,5,8,13,21...(此处非递归)
create or replace function test02(n in number)
return number is
a number(10):=1;
b number(10):=0;
c number(10):=0;
begin 
  for i in 1..n
    loop
      c:=a+b;
      a:=b;
      b:=c;
      end loop;
      return c;
  end test02;

select test02(6) from dual;
drop function test02;

--1.编写一个函数，要求能将阿拉伯数字（a,b,c,d,e,f,g,h,i）分别替换为
--（1,2,3,4,5,6,7,8,9）,非数字全部替换为0,禁止使用replace
create or replace function test03(str in varchar2)
return varchar2 is
newStr varchar2(100):='';
r varchar2(2):='';
begin 
  for i in 1..length(str)
    loop
      select substr(str,i,1) into r from dual;
      case r
        when 'a' then newStr:=newStr||1;
        when 'b' then newStr:=newStr||2;
        when 'c' then newStr:=newStr||3;
        when 'd' then newStr:=newStr||4;
        when 'e' then newStr:=newStr||5;
        when 'f' then newStr:=newStr||6;
        when 'g' then newStr:=newStr||7;
        when 'h' then newStr:=newStr||8;
        when 'i' then newStr:=newStr||9;
          else newStr:=newStr||0;
      end case;
    end loop;
    return newStr;
  end test03;
  
select test03('abcdklgasngoeirg;ajldgqeoihoiqehglds') from dual;
drop function test03;

--2.要求编写函数，要求能将传进来的字符串反序输出，比如'abcdef'，要求输出为'fedcba'
create or replace function test04(str in varchar2)
return varchar2 is
newStr varchar2(100):='';
r varchar2(1):='';
begin 
  for i in 1..length(str)
    loop
      newStr:=substr(str,i,1)||newStr;
    end loop;
    return newStr;
  end test04;

select test04('123456789') from dual;
drop function test04;

异常

--系统自带异常
--no_data_found、too_many_rows......
--8.编写一个程序块，接受一个雇员名，从emp表中显示该雇员的工作岗位与薪水，
--若输入的雇员名不存在，显示“该雇员不存在”信息。
declare 
ejob emp.job%type;
esal emp.sal%type;
ename1 emp.ename%type:='SMITH';
begin 
  select job,sal into ejob,esal from emp where ename=ename1;
    dbms_output.put_line('工作岗位：'||ejob);
    dbms_output.put_line('薪水：'||esal);
    exception when no_data_found then dbms_output.put_line('该雇员不存在');
  end;
--自定义异常
declare
eid emp.empno%type:=7369;
esal emp.sal%type;
mye exception; --声明异常
begin 
  select sal into esal from emp where empno=eid;
  if esal>2500 then
    dbms_output.put_line('不错不错');
    else 
      raise mye; --抛异常
      end if;
      exception when mye then --处理异常
        dbms_output.put_line('加工资');
  end;

--
declare
n_s number(5);
e_my exception;
pragma exception_init(e_my,-20001);
begin
select count(ename) into n_s from emp where ename like 'S%';
if n_s=0 then
raise e_my;
end if;
dbms_output.put_line('数量是'||n_s);
exception 
when e_my then
dbms_output.put_line('人员为空');
end;

游标

--5.某公司要根据雇员的职位来加薪，公司决定按下列加薪结构处理：
--              Designation    Raise
              -----------------------
--              Clerk          500
--              Salesman       1000
--              Analyst        1500
--              Otherwise      2000
--编写一个程序块,接受一个雇员名，从emp表中实现上述加薪处理。
declare
ejob emp.job%type;
esal emp.sal%type;
cursor myr(enames emp.ename%type) is select job,sal from emp where ename=enames for update of ename;
begin 
  open myr('SMITH');
  fetch myr into ejob,esal;
  case ejob
    when 'CLERK' then 
      update emp set sal=(esal+500) where current of myr;
      dbms_output.put_line('加薪后的薪水：'||(esal+500));
    when 'SALESMAN' then 
      update emp set sal=(esal+1000) where current of myr;
      dbms_output.put_line('加薪后的薪水：'||(esal+1000));
    when 'ANALYST' then 
      update emp set sal=(esal+1500) where current of myr;
      dbms_output.put_line('加薪后的薪水：'||(esal+1500));
--    when 'OTHERWISE' then dbms_output.put_line('加薪后的薪水：'||sal+2000);
    else 
      update emp set sal=(esal+2000) where current of myr;
      dbms_output.put_line('加薪后的薪水：'||(esal+2000));
    end case;
    close myr;
  end;

select * from emp
--6.编写一个程序块，将emp表中雇员名全部显示出来。
declare 
enames varchar(30);
cursor myr is select ename from emp;
begin 
  open myr;
  loop
    fetch myr into enames;
    exit when myr%notfound;
    dbms_output.put_line(enames);
    end loop;
    close myr;
  end;


--7.编写一个程序块，将emp表中前5人的名字显示出来。
declare 
enames varchar(30);
cursor myr is select ename from emp 
where rownum<=5;
begin 
  open myr;
  loop
  fetch myr into enames;
  exit when myr%notfound;
  dbms_output.put_line(enames);
  end loop;
  close myr;
  end;
  
  --2.  使用For循环，接受一个部门号，从emp表中显示该部门的所有雇员的姓名，工作和薪水。
declare
--enames emp.ename%type;
--ejob emp.job%type;
--esal emp.sal%type;-
cursor myr(eid emp.deptno%type) is select ename,job,sal from emp where deptno=eid;
begin 
    for i in myr(30)
    loop
      --fetch myr into enames,ejob,esal;
      dbms_output.put_line(i.ename||'::'||i.job||'::'||i.sal);
      --dbms_output.put_line(enames||'::'||ejob||'::'||esal);
      end loop;
  end;


select * from emp;

--3.    使用带参数的游标，实现第2题。
declare
--enames emp.ename%type;
--ejob emp.job%type;
--esal emp.sal%type;-
cursor myr(eid emp.deptno%type) is select ename,job,sal from emp where deptno=eid;
begin 
    for i in myr(30)
    loop
      --fetch myr into enames,ejob,esal;
      dbms_output.put_line(i.ename||'::'||i.job||'::'||i.sal);
      --dbms_output.put_line(enames||'::'||ejob||'::'||esal);
      end loop;
  end;


--4.编写一个PL/SQL程序块，从emp表中对名字以“A”或“S”开始的所有雇员按他们基本薪水的10%给他们加薪。
declare
nums number(10):=0;
cursor myr is select * from emp where ename like 'A%' or ename like 'S%' for update 
of ename;
begin 
  for i in myr
    loop
      nums:=i.sal*1.1;
      update emp set sal=nums where current of myr;
      end loop;
  end;
  
select * from emp;
--5.    emp表中对所有雇员按他们基本薪水的10%给他们加薪，如果所增加后的薪水大于5000卢布，则取消加薪。
declare
nums number(10);
cursor myr is select * from emp for update;
begin 
  for i in myr
    loop
      nums:=i.sal*1.1;
      if nums<=5000 then
        update emp set sal=nums where current of myr;
        end if;
      end loop;
  end;

游标嵌套

--游标嵌套
--for循环
declare
cursor myr1 is select deptno from dept;
cursor myr2(deptno1 dept.deptno%type) is select ename from emp where deptno=deptno1;
begin 
  for i in myr1
    loop
       for j in myr2(i.deptno)
         loop
           dbms_output.put_line(i.deptno||':'||j.ename);
           end loop;
     end loop;
  end;
  
--loop循环
declare
deptno1 dept.deptno%type;
enames emp.ename%type;
cursor myr1 is select deptno from dept;
cursor myr2(deptno2 dept.deptno%type) is select ename from emp where deptno=deptno2;
begin 
  open myr1;
  loop
    fetch myr1 into deptno1;
    exit when myr1%notfound;
    open myr2(deptno1);
    loop
      fetch myr2 into enames;
      exit when myr2%notfound;
      dbms_output.put_line(deptno1||' '||enames);
      end loop;
      close myr2;
    end loop;
    close myr1;
  end;

REF游标和动态SQL

--游标嵌套
--for循环
declare
cursor myr1 is select deptno from dept;
cursor myr2(deptno1 dept.deptno%type) is select ename from emp where deptno=deptno1;
begin 
  for i in myr1
    loop
       for j in myr2(i.deptno)
         loop
           dbms_output.put_line(i.deptno||':'||j.ename);
           end loop;
     end loop;
  end;
  
--loop循环
declare
deptno1 dept.deptno%type;
enames emp.ename%type;
cursor myr1 is select deptno from dept;
cursor myr2(deptno2 dept.deptno%type) is select ename from emp where deptno=deptno2;
begin 
  open myr1;
  loop
    fetch myr1 into deptno1;
    exit when myr1%notfound;
    open myr2(deptno1);
    loop
      fetch myr2 into enames;
      exit when myr2%notfound;
      dbms_output.put_line(deptno1||' '||enames);
      end loop;
      close myr2;
    end loop;
    close myr1;
  end;


--REF游标
--1、REF游标分为强类型和弱类型

--2、强类型
--查询emp表中所有的数据
declare
type myr1 is ref cursor return emp%rowtype;
myr2 myr1;
enames emp%rowtype;
begin 
  open myr2 for select * from emp;
  loop
    fetch myr2 into enames;
    exit when myr2%notfound;
    dbms_output.put_line(enames.ename);
    end loop;
    close myr2;
  end;
  
--3、弱类型
--查询emp表中所有的数据
declare
type myr1 is ref cursor;
myr2 myr1;
emps emp%rowtype;
begin 
  open myr2 for select * from emp;
  loop
    fetch myr2 into emps;
    exit when myr2%notfound;
    dbms_output.put_line(emps.ename);
    end loop;
    close myr2;
  end;


--4、动态sql案例
declare
sal1 emp.sal%type:=1500;
sal2 emp.sal%type:=2000;
type myr1 is ref cursor;
myr2 myr1;
emps emp%rowtype;
begin
  open myr2 for 'select * from emp where sal>:1 and sal<:2 order by sal desc'
  using sal1,sal2;
  loop
    fetch myr2 into emps;
    exit when myr2%notfound;
    dbms_output.put_line(emps.ename);
    end loop;
    close myr2;
  end;


select ename from emp where sal>1500 and sal<2000;


select * from scott.student;

七、SQL高级

同义词

--同义词
--私有同义词和公有同义词
--创建一个同义词
create synonym emm for scott.emp;
--删除同义词
drop synonym emm;
--创建一个同义词
create or replace synonym a for scott.emp;
--运用
select * from a;
--创建一个公有的同义词
grant create public synonym to scott;
create or replace public synonym b for scott.emp;
create or replace public synonym c for scott.emp;
select * from b;
select * from c;
--授权
grant drop public synonym to scott;
drop public synonym b;
--收权
revoke drop public synonym from scott;

序列

--序列
create sequence aa
start with 1
increment by 1
maxvalue 100
minvalue 1
--查询序列
select aa.nextval from dual;
--查看当前值
select aa.currval from dual;
--删除序列
drop sequence aa;
--修改序列
alter sequence aa maxvalue 1000;
--序列使用
create table stu(
sid number,
names varchar2(10)
);

insert into stu values(aa.nextval,'zzz');
select * from stu;
drop table stu;

视图、索引

--视图
create or replace view bb
as
select * from stu;

create or replace force view cc
as
select * from stu;

drop view aa;
drop view cc;

--索引
create index dd on stu(sid);
drop index dd;

存储过程

--存储过程
--不带参数存储过程
create or replace procedure getname
as 
enames varchar2(30);
begin
  select ename into enames from emp where empno=7369;
  dbms_output.put_line(enames);
  end;

--调用
begin
  getname;
  end;
call getname();
drop procedure getname;

--带参数的存储过程
--根据员工编号查询的名字。
create or replace procedure getnames(eno number)
as
enames varchar2(30);
begin
  select ename into enames from emp where empno=eno;
  dbms_output.put_line(enames);
  exception when no_data_found then
    dbms_output.put_line('此人不存在');
    when others then
     dbms_output.put_line('有错！'); 
  end;
  
--调用
begin
  getnames(131);
end;
call getnames(131);

--in和out参数(只能用begin  end;调用)
--根据编号查询名字（名字要返回出来，存储过程中不打印）
create or replace procedure selectname(eid in number ,enames out varchar2)
as
enames1 varchar2(30);
begin
  select ename into enames1 from emp where empno=eid;
  enames:=enames1;
  end;

--调用
declare 
eid number(6):=7369;
ename varchar2(30);
begin
  selectname(eid,ename);
  dbms_output.put_line(ename);
  end;
  

--c.in out 参数
--实现a 和b的值交换。
create or replace procedure selectname(a in out number,b in out number)
as
c number(4);
begin
  c := a;
  a := b;
  b := c;
  end;

--调用
declare
a number(4):=10;
b number(4):=20;
begin
  selectname(a,b);
  dbms_output.put_line('原来a为10，a:'||a);
  dbms_output.put_line('原来6为20，b:'||b);
  end;

--总结：
--in参数：只作为参数传进去。
--out参数：返回的结果。
--in out参数：既要作为参数传进去，又要作为结果返回出来。

--存储过程的调用
--begin end;可以调用所有的存储过程
--call只能调用带参数的存储过程。调用无参数的时要加().不建议使用。容易和创建的（）混淆。

触发器

--不能删除李文才
--创建触发器
create or replace trigger tridel  --创建触发器
after delete--什么操作
on stuInfo--表
for each row --行触发
 begin
   --条件
   if(:old.stuName='李文才') then
   raise_application_error(-20004,'该生不能删除！');
   end if;
   end; 
   
--序列
create sequence a
start with 0
increment by 1
maxvalue 2000
minvalue 0

create table s3(
no number(6),
name varchar2(30)
)

insert into s3(name) values('cc')

--创建触发器
create or replace trigger triInsert
before insert 
on s3
for each row 
  begin
    :new.no:=a.nextval;
    end;
    
select * from s3;
--禁用触发器
alter trigger triInsert disable
--启用
alter trigger triInsert enable
--删除触发器
drop trigger triInsert 
   
   
--触发器
--表级触发器
create or replace trigger modify_stu
before insert or update or delete on student
begin
   if deleting then
     raise_application_error(-20001,'该表不允许删除数据');
   elsif updating then
     raise_application_error(-20002,'该表不允许修改数据');
    elsif inserting then
     raise_application_error(-20003,'该表不允许插入数据');
    end if;
end;

--行级触发器
--after
--创建触发器：将对student表的操作都记录到stu_log表中（update of 用于指定一个
--或多个字段，指定字段被更新时才会触发触发器）
create or replace trigger modify_stu
after insert or delete or update of stu_name
on student
for each row
  begin 
    if inserting then
      insert into stu_log values(1,'insert',sysdate,:new.stu_name);
    elsif deleting then
       insert into stu_log values(2,'delete',sysdate,:old.stu_name);
    elsif updating then
      insert into stu_log values(3,'update_old',sysdate,:old.stu_name);
      insert into stu_log values(4,'update_new',sysdate,:new.stu_name);
     end if;
end;

--before
--创建触发器：实现id的隐式自增
create or replace trigger modify_stu 
before insert on student
for each row
declare
next_id number;
begin
  select seq_test.nextval into next_id from dual;
  :new.id :=next_id;
end;

程序包

--程序包
--分为两部分：规范、主体
--规范
--1.程序包（函数和存储过程）
--存储过程(根据编号查名字)
--函数(根据编号查名字)
--规范部分
create or replace package pg is--或者as   创建程序包规范
procedure noname(eid number);--声明存储过程
function selectname(eid number) return varchar2;--声明函数
end pg;--结束程序包规范
--主体
create or replace package body pg is--创建程序包主体
procedure noname(eid number)--完成储存过程的主体
is
enames varchar2(30);
begin 
  select ename into enames from emp where empno=eid;
  dbms_output.put_line(enames);
  end;
--函数
function selectname(eid number) return varchar2--完成函数体
is
enames varchar2(30);
begin
  select ename into enames from emp where empno=eid;
  return enames;
  end;
end pg;

--删除规范
drop package pg;
--删除主体
drop package body pg;

--执行顺序问题：先执行规范，再执行主体。

--调用存储过程
call pg.noname(7369);
begin
  pg.noname(7369);
  end;
--调用函数
select pg.selectname(7369) from dual;


--2.程序包（存储过程和游标嵌套使用）
--根据编号查询
--规范
create or replace package pb is
cursor c_emp(eid number) return emp%rowtype;--游标声明
procedure pro;--根据编号查询
end pb;

--主体
create or replace package body pb is
cursor c_emp(eid number) return emp%rowtype --声明游标
is
select * from emp where empno=eid;
procedure pro--声明存储过程
is
emprow emp%rowtype;
begin
  open c_emp(7369);
  fetch c_emp into emprow;
  dbms_output.put_line(emprow.ename);
  close c_emp;--结束游标
  end;--结束存储过程
  end pb;--结束程序包
 

--调用
begin
  pb.pro;
  end;

事务


--commit:提交
--rollback:回滚
--savepoint:保存点

--commit:提交
--自动提交：（关键字有：alter 、 create 、 drop 、 grant 、 revoke等）
--显示提交：（关键字有：insert、 update 、delete 等）
--rollback:回滚
--savepoint:保存点

--创建表
create table stu(
sno number(3),
sname varchar2(15)
);

select * from stu;

insert into stu(sno, sname) values(1, 'aa');
commit;

insert into stu(sno, sname) values(2, 'bb');

select * from stu;

insert into stu(sno, sname) values(2, 'bb');
commit;

insert into stu(sno, sname) values(3, 'bb');
rollback;

insert into stu(sno, sname) values(4, 'bb');
insert into stu(sno, sname) values(5, 'bb');
savepoint aa;
insert into stu(sno, sname) values(6, 'bb');
insert into stu(sno, sname) values(7, 'bb');
insert into stu(sno, sname) values(8, 'bb');
rollback to aa;

--总结：
--commit:对显示操作，进行数据的提交
--rollback:回滚到上次提交的位置。
--savepoint:设置一个保存点。可以回滚到设置的那个点的位置。
--PL/SQL Developer：软件在关闭的时候会帮我们提交我们没有提交的数据。

八、SQL优化

--数据库性能化

--数据库性能优化采取的工作可以分为6个部分。
--1.优化数据库结构设计。
--2.优化SQL语句。
--3.调整服务器内存分配。
--4.调整硬盘I/O。
--5.调整操作系统参数。
--6.使用Oracle工具优化。

--SQL语句优化。
--1.选择合理的表名顺序
--注意：列少的应放在最后面。（因为从右往左解释）。
select * from emp;--8列
select * from dept;--3列
--性能差
select * from dept a,emp b 
where a.deptno=b.deptno;
--性能好
select * from emp b,dept a
where a.deptno=b.deptno;

--2.避免使用*
--差
select * from dept;
--好
select deptno,dname,loc from dept;

--3.高效删除重复的记录。
delete from emp a 
where a.rowid > (select min(x.rowid)from emp x where x.empno=a.empno);

--1.
select * from student; 
--2.
select a.*,a.rowid from student a;
--3.
select min(x.rowid)from student x;
--4.
select * from student a
where a.rowid > (select min(x.rowid)from student x where x.sno=a.sno);
--0  1
-- 0 1 2 3 4 5 6 7 
-- 0 1 2 3 4 5 6 7  8 9 
-- 0 1 2 3 4 5 6 7 8 9 A B C D E F 10

--4.减对表的查询（子查询）
select * from emp;
--3次
select * from emp where sal=(select max(sal) from emp) and comm=(select max(comm) from emp);
--优化
select * from emp where (sal,comm)=(select max(sal),max(comm) from emp);
--总结：适当的可以用where条件代替子查询

--5.使用表别名
select a.ename,b.dname from emp a,dept b where a.deptno=b.deptno;

--6.exists代替in
--找出deptno的薪水
select ename from emp where deptno=20;

select sal from emp where ename in(select ename from emp where deptno=20);

select sal from emp a where exists(select * from emp b where deptno=20 and a.empno=b.empno );

--7.用not exists 代替 not in.
select sal from emp where ename not in(select ename from emp where deptno=20);

select sal from emp a where not exists(select * from emp b where deptno=20 and a.empno=b.empno );
--8.用表连接替换exists
select ename from emp a where exists(select * from dept b where a.deptno=b.deptno and deptno=30);

--优化
select ename from emp a ,dept b where a.deptno=b.deptno and b.deptno=30;

select ename from emp a
inner join
dept b
on a.deptno=b.deptno and b.deptno=30;

--9.用exists替换distinct
select distinct deptno from emp;
--优化
select deptno from dept a where exists(select deptno from emp b where a.deptno=b.deptno);
--10.索引优化。

--总结：
--1.在两表联查时，列少的写后面，l列多的写前面。
--2.查询语句时，尽量少用*
--3.尽量用where条件代替两表联查。
--4.用exists替换distinct
--5.使用表别名
--6.exists代替in

--这里优化只是属于一部分

Flashx2

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle的入门之路

一、Oracle前言数据定义语言（DDL）：create drop alter 数据操纵语言（DML）：insert select delete update 事务控制语言（TCL）:commit savepoint rollback 数据控制语言（DCL）：grant revoke --注意：建议Oracle代码不区分大小写：建议尽量用大写（因为写进去的小写最后会被转换为大写在去执行！）--建议尽量每行代码的后面加一个分号，因为在PLSQL Develop..
复制链接

扫一扫