欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracled数据库的进一步理解

程序员文章站 2022-05-08 20:31:38
...
-- PL/SQL编程
-- SQL:   Structed Query Language
-- PL:    Procedure Language
        --是对sql语言的补充
       
--为什么需要PL/SQL编程[在数据库服务端编程]
        --减小网络开销
        --提高性能
        --提高了安全性
       
       
-- PL/SQL块 是基本单元

--允许输出
set serveroutput on;

--案例1
declare
    str varchar2(20) := 'Hello World';
begin
        --输出语句
        dbms_output.put_line(str); 
end;

--修改7499的工资
begin
  update empbak set sal=2000 where empno=7499;
end;

--------------------------------------------------------
/*
数据类型
  标量类型
         数字型 Binary_Integer  Number    PLS_Integer
         字符型  Char   varchar2  long    raw    long Raw
         时间日期型: Date   Timestamp
         布尔型: Boolean
        
  LOB类型
         BFile
         BLOB
         CLOB
         NCLOB
 
  属性类型
         %type
         %rowtype
        
  复合类型
         自定义类型,类似于C语言中的结构体
----------------------------------------------------
操作符
         :=  赋值操作符   相当于Java中的 =
         ||  连接操作符   相当于Java中的 +
         --  单行注释
        <<>> 标签分隔符
        ..   范围操作符
        **   求幂操作符

比较运算符
        =            等于      相当于Java中的 ==
        <> !=        不等于
        >
        <
        >=
        <=

逻辑运算符
        and       并且  相当于Java中的 &&
        or        或者  相当于Java中的 ||
        not       非    相当于Java中的 !
*/

--查询执行编号的员工的姓名
declare
  v_ename varchar2(5);--声明一个变量用来接收查询的结果
begin
  select ename into v_ename from empbak where empno=&aaa;
  dbms_output.put_line('名字是:'||v_ename);
  --处理异常
  exception
     when no_data_found then
        dbms_output.put_line('没有这个编号的员工!');
end;

-----------------------------------------------
--属性类型
--  %type  参照字段类型

--根据编号查询员工的姓名和工资
declare
  --变量的类型参照empbak表中ename字段的类型
  v_ename empbak.ename%type;
  v_sal empbak.sal%type;
begin
  select ename,sal into v_ename,v_sal from empbak where empno=&aaa;
  dbms_output.put_line('名字是:'||v_ename||' ,工资:'||v_sal);
  --处理异常
  exception
     when no_data_found then
        dbms_output.put_line('没有这个编号的员工!');
end;


---------------------------------------------------------------------
--复合类型
declare
  --定义一个复合类型
  type myrecord is record(
        v_ename empbak.ename%type,
        v_sal empbak.sal%type
  );

  --定义一个复合类型的变量
  aa myrecord;
begin
  select ename,sal into aa from empbak where empno=&no;
  dbms_output.put_line('姓名:'||aa.v_ename||' 工资:'||aa.v_sal);
end;


------------------------------------------------------------------------
--根据员工编号查询员工的所有信息
--使用 %rowtype表示整行数据
declare
     --%rowtype 表示变量是一个包含了empbak表所有字段的复合类型
     aa empbak%rowtype;
begin
  select * into  aa from empbak where empno=&no;
   dbms_output.put_line('姓名:'||aa.ename||' 工资:'||aa.sal);
end;

---------------流程控制------------------------------------------

--分支
   --  if
   --根据编号查询员工工资,如果发现工资低于1500,就给涨工资200
   -- 否则减少200
   declare
       v_sal empbak.sal%type;
       v_no empbak.empno%type;
   begin
       v_no :=&eno;
     select sal into v_sal from empbak where empno=v_no;
     if v_sal<1500 then
       update empbak set sal=v_sal+200 where empno=v_no;
     else
       update empbak set sal=v_sal-200 where empno=v_no;
     end if;
   end;
  
  
    --根据编号查询员工岗位,
    --如果是 'MANAGER' 就+500sal
    --如果是 'SALESMAN' 就+300sal
    --如果是 'CLERK' 就+100sal
   
    declare
          v_job empbak.job%type;
           v_no empbak.empno%type;
    begin
         v_no :=&eno;
         select job into v_job from empbak where empno=v_no;
        
         if v_job = 'MANAGER' then
           update empbak set sal=sal+500 where empno=v_no;
         elsif v_job = 'SALESMAN' then
                update empbak set sal=sal+300 where empno=v_no;
         elsif v_job = 'CLERK' then
                update empbak set sal=sal+100 where empno=v_no;
         else 
                null;--什么都不做
         end if;
    end;
   
   
--case语句 
    --根据编号查询员工岗位,
    --如果是 'MANAGER' 就+500sal
    --如果是 'SALESMAN' 就+300sal
    --如果是 'CLERK' 就+100sal

declare
    v_job emp.job%type;
    v_no empbak.empno%type;
begin
    v_no:=&no;
    select job into v_job from empbak where empno=v_no;
   
    case v_job
      when 'MANAGER' then
        update empbak set sal=sal+500 where empno=v_no;
      when 'SALESMAN' then
         update empbak set sal=sal+300 where empno=v_no;
      when 'CLERK' then
         update empbak set sal=sal+100 where empno=v_no;
      else
         null;
    end case;
end;
  
--循环
--loop
--创建表
create table aa(id number,name varchar2(20));
--往表中插入10条测试数据

declare
       i number:=0;
begin
 
    loop
       exit when i>=10;--当i等于10的时候退出循环
       insert into aa values(i,'张三'||i);
       i:=i+1;
    end loop;
end;


--for循环
begin
        --for中的i是不需要声明的,会自加,包含两边的边界
       for i in 10..20 loop
           insert into aa values(i,'张三'||i);
       end loop;
end;


--while循环
declare
       i number:=21;
begin
 
   while i<=30 loop
         insert into aa values(i,'张三'||i);
          i:=i+1;
   end loop;
end;


--跳转
--goto
declare
   i number:=0;
begin
 
   dbms_output.put_line('循环开始');
   loop
       if i=10 then
          goto aa;
       end if;
       dbms_output.put_line('循环中'||i);
       i:=i+1;
   end loop;
   dbms_output.put_line('循环结束111');
    <<aa>> --goto的标签
   dbms_output.put_line('循环结束222');
end;


--null语句
--表示此地不执行任何代码


---------------------------------------------------------------------------
--游标类型:用来表示一个结果集(多条数据)的类型
隐式游标
   任何一个DML语句执行的时候都会默认创建一个游标
   这个游标的名字是SQL
   隐式游标有四个属性可以使用:
   %found --执行的语句影响到了一行或者多行数据,就为true,否则为false
   %notfound --执行的语没有影响数据,就为true,否则为false
   %rowcount --获得执行语句影响到了多少行
   %isopen --判断游标是否打开,隐式游标始终是false
  
--判断数据是否修改成功
begin
   update empbak set sal=10000 where empno=&no;
  
   if SQL%rowcount=0 then
     dbms_output.put_line('更新失败!!');
   else
     dbms_output.put_line('更新成功!!');
   end if;
end;


显式游标
--获取部门10的所有员工的姓名和工资
declare
   v_ename emp.ename%type;
   v_sal emp.sal%type;
   --定义显式游标,一定要绑定一个查询语句
   --游标mc中装的是查询语句的结果集
   cursor mc  is select ename,sal from emp where deptno=10;
begin
  --打开游标[游标默认是关闭的],打开后才能够提取游标中的数据
  open mc;
       loop 
           --提取游标中的数据
           --每fetch一次,就提取了一条数据
           fetch mc into v_ename,v_sal;
           --判断是否有数据,如果没有就退出
           exit when mc%notfound;
           dbms_output.put_line(v_ename||'  '||v_sal);
       end loop;
    --关闭游标
    close mc;
end;



--获取部门10的所有员工的基本信息
declare
   v_emp emp%rowtype;
   --定义显式游标,一定要绑定一个查询语句
   --游标mc中装的是查询语句的结果集
   cursor mc  is select * from emp where deptno=10;
begin
  --打开游标[游标默认是关闭的],打开后才能够提取游标中的数据
  open mc;
       loop 
           --提取游标中的数据
           --每fetch一次,就提取了一条数据
           fetch mc into  v_emp;
           --判断是否有数据,如果没有就退出
           exit when mc%notfound;
           dbms_output.put_line(v_emp.ename||'  '||v_emp.sal);
       end loop;
    --关闭游标
    close mc;
end;

--带参数的游标
--获取部门人数为3人的部门的所有员工信息
declare
   v_emp emp%rowtype;
   v_deptno emp.deptno%type;
   --定义带参数的游标
   cursor mc(a number)  is select * from emp where deptno=a;
begin
  --获得部门人数为3人的部门
   select deptno into v_deptno from emp group by deptno having count(deptno)=3;
  --打开游标的时候传入参数
  open mc(v_deptno);
       loop 
           --提取游标中的数据
           --每fetch一次,就提取了一条数据
           fetch mc into  v_emp;
           --判断是否有数据,如果没有就退出
           exit when mc%notfound;
           dbms_output.put_line(v_emp.ename||'  '||v_emp.sal);
       end loop;
    --关闭游标
    close mc;
end;


--通过游标更新数据
--查询部门10的所有人的姓名和工资,将每个人的工资加100
declare
   v_ename empbak.ename%type;
   v_sal empbak.sal%type;
    --定义可以更新数据的游标,指定更新的列
    cursor mc is select ename,sal from empbak where deptno=20 for update of sal;
begin
    open mc; 
    loop
         fetch mc into v_ename,v_sal;
         exit when mc%notfound;
         dbms_output.put_line(v_ename||'  '||v_sal);
        
         if v_sal<2000 then
             --current of mc当前游标
             update empbak set sal=sal+500 where current of mc;
         end if;
    end loop;
    close mc;            
end;


--使用for循环提取游标中的数据
declare
   cursor mc  is select ename,sal from emp where deptno=10;
begin
       --for循环会自动打开和关闭游标
       --for循环会自动判断是否提取结束
       for aa in mc loop 
           dbms_output.put_line(aa.ename||'  '||aa.sal);
       end loop;
end;


REF游标
--查询部门10的所有人的姓名和工资
declare
       --定义游标类型
       type cursor_type is ref cursor;
       --定义游标变量,不需要绑定查询语句
       mc cursor_type;
       v_ename empbak.ename%type;
       v_sal empbak.sal%type;
       v_empno empbak.empno%type;
       v_job empbak.job%type;
      
begin
       --打开游标的时候绑定查询语句
       open mc for select ename,sal from empbak where deptno=10; 
      
       --提取数据
      loop
         fetch mc into v_ename,v_sal;  
         exit when mc%notfound;
         dbms_output.put_line(v_ename||'  '||v_sal);
       end loop;
       close mc;
      
      
       open mc for select empno,job from empbak;
      
       loop
        
       fetch mc into v_empno,v_job;
       exit when mc%notfound;
            dbms_output.put_line(v_empno||'  '||v_job);
        end loop;
        close mc;
end;

----------------------------------------------------------------------------
--数据库对象过程 函数  包  触发器

浪潮之巅
吴军  google
腾讯  搜搜
天使投资



--过程:用于完成一系列不可分割的任务的子程序
--过程必须调用才能执行
--修改指定编号员工的工资
create or replace procedure pro_1 is

begin
  update empbak set sal=2000 where empno=7499;
end;

--执行过程
exec pro_1;

--创建一个带参数的过程
--参数的类型是不用定义长度的
--过程的参数分为传入参数和传出参数
--传入参数 in  在调用的时候需要传入一个值 
-- 传出参数 out 在调用完之后会输出一个值
--in可以省略不写
--等到Java中调用过程的时候再来详细讲传入和传出参数
create or replace procedure pro_2(v_empno in number,v_sal in out number) is

begin
  update empbak set sal=v_sal where empno=v_empno;
end;


--为部门表添加一个字段maxnum,整型,该字段表示部门编制最大人数。
--创建一个存储过程add_emp,该存储过程实现添加员工的功能,参数为empbak%rowtype类型。
--当某个部门的员工数超过该部门的最大编制时,使用异常处理方式提示用户“该部门人数编制已满,不能再添加员工”。
--写一个存储过程call_addemp,验证上述存储过程的正确性。

create table deptbak as select * from dept;

alter table deptbak add (maxnum number(5));

create or replace procedure pro_add_emp(v_emp empbak%rowtype) is
--定义变量
   v_count number;--部门已有人数
   v_maxnum number;--部门允许最大人数
   myexception Exception;--定义一个异常对象
  
begin
  --调用函数,获得部门已经有的人数
  v_count:=fun_1(v_emp.deptno);
  --获得该部门允许的最大人数
  select maxnum into v_maxnum from deptbak where deptno= v_emp.deptno;
   dbms_output.put_line(v_count||' '||v_maxnum);
  if v_count<v_maxnum then
     insert into empbak values(v_emp.empno,v_emp.ename,v_emp.job,v_emp.mgr,v_emp.hiredate,v_emp.sal,v_emp.comm,v_emp.deptno);
  else
    raise myexception;--抛出异常
  end if;
 
  exception
    when myexception then
      dbms_output.put_line('该部门人数已满,不能增加新员工');
end;


----------------
create or replace procedure pro_call_pro_add_emp(v_empno number) is
   v_emp emp%rowtype;
begin
  select * into v_emp from emp where empno=v_empno;
  --调用其它过程
  pro_add_emp(v_emp);
end;



-------------------------------
函数:是一个定义了返回值的子程序
--根据部门编号查询该部门已经有的员工数
--参数是部门编号
--返回值是该部门已经有的总人数
create or replace function fun_1(v_deptno number) return number is
     v_count number;
begin
  select count(empno) into v_count from empbak where deptno= v_deptno;
  return v_count;
end;

--如何调用函数
--1.在sql语句中调用函数
--2.在过程中调用函数
--3.在函数中调用函数

--函数的特点
--1.参数只能是传入参数
--2.必须有返回值,并且只能有一个返回值
--3.函数的参数和返回值都不能是PL/SQL类型的


--程序包:类似于命名空间
--包中可以定义类型,变量,游标,过程,函数
--包由包头和包体组成
--包头类似于C的头文件,包体类似于C的原文件
create or replace package mypackage is
   type type_cursor is ref cursor;
   type myrecord is record(v_ename emp.ename%type,v_sal emp.sal%type);
   procedure pro_22(v_empno number);--根据编号查询员工信息
   function fun_22(v_empno number) return number;--根据员工编号查询员工工资
end mypackage;

--包体是包中过程和函数的实现
create or replace package body mypackage is
--过程的实现
procedure pro_22(v_empno number) is
   --定义ref游标变量
   mucursor mypackage.type_cursor;
   v_emp emp%rowtype;
begin
    open mucursor for select *  from empbak where empno=v_empno;
    loop
      fetch mucursor into v_emp;
      exit when mucursor%notfound;
      dbms_output.put_line(v_emp.ename||' '||v_emp.sal);
    end loop;
   close mucursor;
end;

--函数的实现
function fun_22(v_empno number) return number is
   v_sal emp.sal%type;
begin
   select sal into v_sal from emp where empno=v_empno;
   return v_sal;
end;

end mypackage;



--包外面的过程和函数同样可以使用包中的内容
create or replace procedure pro_23 is
       mr mypackage.myrecord;--使用包中定义的类型
begin
  select ename,sal into mr from empbak where empno=7369;
  dbms_output.put_line(mr.v_ename||' '||mr.v_sal);
end;


-----------------------------------------------------
--触发器:事件触发自动执行的存储过程
          before /after /instead  insert / update /delete of 列名  on 表名
         

--创建一个被开除的员工信息表
create table empTemp as select * from empbak where 1=2;

--当开除一个员工之后,将员工的信息从emobak表移到emptemp表中
create or replace trigger tri_1
after delete on empbak
for each row --行级触发器,每影响到一行数据,就执行一次
begin
  insert into emptemp values (:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
end;


--禁止在休息时间更新员工信息[增加/删除/修改]

create or replace trigger tri_2
before insert or delete or update on empbak
for each row
begin
   if to_char(sysdate,'DAY') in ('星期六','星期日') or to_char(sysdate,'HH24:MI') not between '08:30' and '17:30' then
     raise_application_error(-20001,'不能在休息时间更新员工信息!');
   end if;
end;



------------------------------------------------------------------
declare
   v_emp empbak%rowtype;
   cursor mc is select * from empbak;
begin
open mc;

fetch mc into v_emp;
if mc%found then
  dbms_output.put_line('发现数据');
else
  dbms_output.put_line('没有发现数据');
end if;
close mc;
end;

----------
declare
      v_ename empbak.ename%type;
      v_sal empbak.sal%type;
      v_deptno empbak.deptno%type;
      cursor mc is select ename,sal,deptno from empbak for update of  sal;
begin
  open mc;
       loop
         fetch mc into v_ename,v_sal,v_deptno;
         exit when mc%notfound;
        
         if v_deptno=30 then
           update empbak set sal=sal+200 where current of mc;
            dbms_output.put_line(v_ename||' '||v_deptno||' '||v_sal);
         end if;
       end loop;
 
  close mc;
end;


---------------------------------------------------------

create or replace procedure add_dept(v_deptno number,v_dname varchar2) is

begin
  insert into deptbak values(v_deptno,v_dname,'',0);
end;


-----------------------------------
create or replace function get_name(v_empno number) return varchar2 is
       v_ename empbak.ename%type;
begin
       select ename into v_ename from empbak where empno=v_empno;
       return v_ename;
end;


----------------------------------------------
创建学生表
create table student (
       stdid number,
       math number(4,1),
       article number(4,1),
       languag number(4,1),
       total number(4,1),
       average number(4,1)
       );

课外表
create table out_school(
       stdid number,
       practice varchar2(20),
       coment varchar2(2)
);

declare
       a number:=40;
       b number:=50;
       c number:=60;
       d number;
       e number;
begin
  for i in 1..10 loop
        a:=a+i;
        b:=b+i;
        c:=c+i;
        d:=a+b+c;
        e:=d/3;
    insert into student values(i,a,b,c,d,e);
  end loop;
end;

-----
如果课外成绩是A,总分就+20
create or replace procedure pro_stu is
       v_coment out_school.coment%type;
       v_stdid out_school.stdid%type;
       --通过游标查询所有学生的课外成绩
       cursor mc is  select stdid,coment from  out_school;
begin
       open mc;
            loop
                 fetch mc into v_stdid,v_coment;
                 exit when mc%notfound;
                 if v_coment='A' then
                   update student set total=total+20 where stdid=v_stdid;

                 end if;
            end loop;
       close mc;     
end;

----------------------------------------------------------------
--限定只对部门号为20的记录进行行触发器操作。
--如果改变的sal和comm比现在少的话报错,
--删除记录的话也报错,
--其他操作执行。

create or replace trigger tri_3
before delete or update of sal,comm  on empbak
for each row
when (old.deptno=20) --限定条件,只对部门编号为20的员工触发操作
begin
     case
       when updating('sal') then
            ifOracled数据库的进一步理解
            
    
    博客分类: Oracle数据库 oraclePL/SQL块游标触发器ld.sal>:new.sal then
              raise_application_error(-20012,'不能降低工资!');
            end if;
           
       when updating('comm') then 
            ifOracled数据库的进一步理解
            
    
    博客分类: Oracle数据库 oraclePL/SQL块游标触发器ld.comm>:new.comm then
                 raise_application_error(-20013,'不能降低奖金!');
            end if;
        
       when deleting then
             raise_application_error(-20014,'不能开除!');
     end case;
end;


create table dropped_obj(
       vname varchar2(20),
       vtype varchar2(20),
       times date
)

CREATE OR REPLACE TRIGGER log_drop_obj
AFTER DROP ON SCHEMA
BEGIN
  INSERT INTO dropped_obj
  VALUES( Sys_context('userenv','ip_address'),
          ORA_DICT_OBJ_TYPE, SYSDATE);
END;

------------------------------------------------------------

select sys_context('USERENV','db_name') from dual

Oracle:
  数据类型[表字段的类型]
 
  DDL语句 [create  drop  alter]
  DML语句 [insert into/ select/update/delete]
  复杂查询[条件查询/模糊匹配查询/自连接查询]