oracle模式与pl/sql简析

好书推荐：赵振平老师的oracle11g学习笔记

       这段时间天天纠结于论文 修改 修改 还是修改  漫长、痛苦、而又纠结的过程 有时间看看书 整理下所见所得 感到非常的舒服了  记录学习的脚步 

模式是数据库对象的集合，包括很多对象，常见的表、视图、存储过程、函数、触发器、包、包体、序列、同义词等等，按照赵老师的话即是模式名就是用户名，用户名就是模式名

下图模式的所有对象

下面即是记录学习笔记的东西  必要的地方以加了注释

--创建表
create table STUS
(
  stu_id       VARCHAR2(50) not null,
  stu_name     VARCHAR2(30) not null,
  stu_age      NUMBER not null,
  stu_birthday DATE not null，
   primary key(stu_id)
)


--添加数据
insert into stus values(sys_guid(),'sz',21,sysdate);
insert into stus values(sys_guid(),'gz',20,sysdate);
insert into stus values(sys_guid(),'sh',19,sysdate);
commit;

--删除表中数据
delete from stus;
commit;

--查询表中数据
select * from stus;



--模式(Schema)-->是数据库对象的集合(包括)(模式名就是用户名，用户名就是模式名)

--创建视图  
--视图作用:简化了sql语句、加强了表的安全访问、简化了数据访问的复杂性
--创建只读视图 不能通过视图对基表进行相应的增删改 只能进行查询
create or replace view v_stus 
as
select * from stus 
where stu_age=20
with read only;

--视图中也可以增删改查
create or replace view v_stus_crud
as
select * from stus
where stu_age=19;

--测试通过视图删除基表中的数据
select * from v_stus;
select * from v_stus_crud;
delete from v_stus_crud;
commit;

--查看视图的结构
desc v_stus;

--查看视图的结构
select dbms_metadata.get_ddl('VIEW','V_STUS_CRUD','UNDER') from dual;

--删除视图 如果有参照完整性的话 则需要加上cascade constraints
drop view v_stus_crud cascade constraints;


--动态创建sql语句
create or replace procedure pro_stus
Authid Current_User  --在存储过程中 用户的权限是不可用的  需要使用Authid Current_User才能显示的获得权限
as
   v_sql varchar2(100);
begin
  v_sql:='create or replace view v_stus as select * from stus where stu_age=19 with read only';
  execute immediate v_sql;
end;
/
--在sql*plus中测试存储过程
execute pro_stus;

--查看系统用户
select * from dba_users where username='GA_NEW';
--查看用户权限
select * from role_sys_privs;
--查看角色所拥有的权限
select * from dba_sys_privs where grantee='RESOURCE';



 
--创建序列
create  sequence seq_stus
increment by 1
start with 1
nomaxvalue
nominvalue
nocache
nocycle
order

--currval 获取序列当前值
select seq_stus.currval from dual;  
--nextval 获取下一个值
select seq_stus.nextval from dual;

drop sequence seq_stus;


--创建同义词(隐藏对象名)
--分为共有的和私有的
create  synonym ps for stus;

select * from ps;
select * from stus;
--删除同义词
drop synonym ps;

--打开控制台输出
set serveroutput on;

--pl/sql是一个过程化的语言,属于第三代语言
--pl/sql语句块的执行
    ---过程化的语句由pl/sql过程化的语句执行器处理
    ---sql语句由oracle的sql语句执行器执行
--pl/sql单元的特点(安全、执行效率高、易于扩展、面向对象)
--pl/sql单元包括block、function、procedure、package、package body、trigger、type、type body 8个组成
/*
   多行注释
   [declare]
   begin
     [exception]
   end
*/


declare 
     v_a binary_integer; --变量
     c_a constant number :=100; --常量
begin
  v_a := 20;
  dbms_output.put_line('变量的值为:' || v_a || '常量的值为:' || c_a);
end;    
/

--测试变量的作用域
declare 
  m number :=22;
begin
  declare 
  m number :=39;
  begin
  dbms_output.put_line('变量的值为' || m );
  end;
  dbms_output.put_line('变量的值为' || m );
end;
/  

--绑定变量(在sql*plus中创建  在sql*plus、sql、pl/sql中能够使用)
variable v_db varchar2(100);

begin
  :v_db := '2345';
end;
/

print v_db;

--在sql语句中使用,但是需要在sql*plus中进行执行

begin
  :v_db := 'gz';
end;
/

select * from stus where stu_name=:v_db;

--自定义类型
declare
subtype small_num is number(4,1);
v_test small_num;
begin
  null;
end;
/  


--char varchar varchar2
--char比varchar的效率要高 但是浪费存储空间  性价比最好的即是varchar2

--pl/sql  null表示什么也不做
create or replace procedure pro_test
as
 v_null varchar2(20);
begin
 v_null :=null;
 if v_null is null then dbms_output.put_line('变量为空值');
 else
   dbms_output.put_line('变量不为空值');
  end if;  
end;  
/

--if then elsif else end if
create or replace procedure pro_test2(v_name in varchar2)
as
       v_age stus.stu_age%type;
begin
  select stu_age into v_age from stus where stu_name = v_name;
  if v_age<15 then dbms_output.put_line('年龄小于15');
  elsif v_age<=18 then dbms_output.put_line('年龄在15--18之间');
  else dbms_output.put_line('年龄大于18');
  end if;
  end;
/

--case when then 
create or replace procedure pro_test3(v_name in varchar2)
as
       v_age stus.stu_age%type;
begin
  select stu_age into v_age from stus where stu_name =v_name;
  --if语句
  if v_age is null then dbms_output.put_line('找不到数据');
  else
      --case 语句
      case 
        when v_age<15 then dbms_output.put_line('年龄小于15'); 
        when v_age<=18 then dbms_output.put_line('年龄在15-18之间');
        else 
          dbms_output.put_line('年龄大于18');
       end case;
   end if;
   --处理异常
  exception 
    when no_data_found then
      dbms_output.put_line(v_age||' 错误代码为: '||sqlcode|| ' 错误的描述为:' ||sqlerrm); 
      
  end;
/


--loop  end loop
create or replace procedure pro_test4(v_c in binary_integer)
as
  --     v_age stus.stu_age%type;
   v_count binary_integer :=v_c; 
begin
 /*
  loop
    exit when v_count>10;
    v_count :=v_count+1;
    dbms_output.put_line(v_count);
  end loop; 
  */ 
  while v_count<10 loop
    exit;
    v_count :=v_count+1;
    dbms_output.put_line('while结果集:'||v_count);
  end loop;
  
  for vc in v_count..10 loop
    dbms_output.put_line(vc);
  end loop;
  
end;
/



--goto 标签
create or replace procedure pro_test5
as
 v_count binary_integer :=0;
begin
  <<l1>>
  v_count := v_count+1;
       loop
         dbms_output.put_line('goto语句:'||v_count);
         exit when v_count>4;
         goto l1;
        end loop;  
end;  
/


--exception 异常
create or replace procedure pro_test6
as
 my_exc exception;
 pragma exception_init(my_exc,-20473);
begin
  
  --raise my_exc;
  raise_application_error(-20473,'自定义的异常错误描述符'); -- -20000-->-20999
  exception 
    when my_exc then
      dbms_output.put_line('自定义异常' || sqlcode || sqlerrm); 
end; 
/



--游标
--隐式游标和显示游标
--隐式游标的四个属性----> sql%found sql%notfound sql%rowcount sql%isopen
create or replace procedure pro_test7
as
       
begin
  delete from stus;
  if sql%found then commit;
  else null;
  end if;
end;
/


--显示游标 分为静态游标和动态游标(游标变量)
--游标操作四步骤  游标定义  打开游标   获取数据  关闭游标
create or replace procedure pro_test8(va in number)
as
      v_name stus.stu_name%type; 
      cursor cur_stus(v_age number) is select stu_name from stus where stu_age>v_age;
begin
  open cur_stus(va);
  loop
    fetch cur_stus into v_name;
    exit when cur_stus%notfound;
    dbms_output.put_line(v_name);
  end loop; 
  close cur_stus; 
end;
/


-- %type(某个字段的类型) %rowtype(某一行的类型)

--动态游标
create or replace procedure pro_test9(input_num in number)
as
 type my_cur is ref cursor; --声明了一个游标类型
 v_cur my_cur;
 rec stus%rowtype;
begin
  case input_num
    when 1 then open  v_cur for select * from stus;
    else
      open v_cur for select * from stus where stu_age>19;
   end case;
   
   loop
     fetch v_cur into rec;
     exit when v_cur%notfound;
     dbms_output.put_line('结果为:'||rec.stu_id||' '||rec.stu_name);
   end loop;    
   close v_cur;     
end;   
/


--使用for in 输出游标的内容
create or replace procedure pro_test9
as
cursor cur_stus is select * from stus;
begin
  
  for rec in cur_stus 
    loop
      dbms_output.put_line(rec.stu_name);
    end loop;
end;
/


--触发器使用java、c、pl/sql编写的程序单元，
--存储于数据库中，能够被触发事件(dml([update or insert or delete of 字段])/ddl/system event/user event)触发从而执行特定的动作
--触发事件
create trigger 触发器名称
before/after/instead of 事件 on 表名/视图名/数据库
[for each row]
--触发限制
[when (条件)]
--触发动作
begin
  null;
  end;

/*
触发器的分类--行级触发、语句级触发
         before触发、after触发
         compound触发(拥有四个时间点，语句前后、行级前后、比较特殊)
         instead of触发(用于更新视图)
         system event、user event
         
  在触发动作区域，在行级触发时，可以使用(:new.列名 :old.列名 访问列值 
                              可使用referencing进行重命名 eg: referencing new as new_one)   
                              
     
*/

create or replace trigger stu_trigger
after update on stus
referencing new as new_val
            old as old_val
for each row
begin
  dbms_output.put_line('新值' || :new_val.stu_name);
  dbms_output.put_line('旧值' || :old_val.stu_name);
  end;
/

update stus set stu_name='gz' where stu_age=20;
commit;

alter trigger stu_trigger disable;
  alter trigger stu_trigger enable; 

drop trigger stu_trigger;



--动态sql语句 --只有在运行的时候才知道内容  两种方式执行(本地动态sql execute immediate 或者 dbms_sql)
--静态sql语句--内容已经确定 为pl/sql的一部分

--用dll语句创建对象时 建议使用||来进行连接 不推荐使用绑定对象

--在进行动态sql语句返回值 可分为三种情况
--返回单行单列的值  返回单行多列的值  返回多行多列的值

--返回单行单列的值
create or replace procedure shsl_pro
Authid Current_User
as
sql_string varchar2(100);
v_max_age number;
begin
  sql_string :='select max(stu_age) from stus where stu_name=:a';
  execute immediate sql_string
  into v_max_age  --返回值放入变量中
  using 'gz';  --替换使用占位符的参数
  dbms_output.put_line(v_max_age);
end;  
/

--返回单行多列的值
create or replace procedure shdl_pro
Authid Current_User  
as
sql_string varchar2(100);
v_max_age number;
v_stu_id varchar2(100);
begin
  sql_string :='select stu_age,stu_id from stus where stu_name=:a';
  execute immediate sql_string
  into v_max_age,v_stu_id
  using 'gz';
  dbms_output.put_line(v_max_age||' '||v_stu_id);
end;  
/

--返回多行多列的值
create or replace procedure dhdl_pro
Authid Current_User
as
sql_string varchar2(100);
type v_cursor is ref cursor;
dhdl_cursor v_cursor;
rec stus%rowtype;
begin
  sql_string :='select * from stus where stu_age > :a';
  open  dhdl_cursor for sql_string  
  using 18;
  loop
    exit when dhdl_cursor%notfound;
    fetch dhdl_cursor into rec;
    dbms_output.put_line(rec.stu_id||' '||rec.stu_name);
  end loop;  
  close dhdl_cursor;
end;  
/

--存储过程 执行效率高、隐藏数据、减少通信量
--独立子程序和包子程序
--%type %rowtype 可以间接的获取数据类型

--在sql*plus中执行存储过程  exec 存储过程名(参数列表)
--在块中 存储过程名(参数列表)

begin
  dhdl_pro;
 end;
/ 

--存储过程重新编译
alter procedure dhdl_pro compile;
drop procedure dhdl_pro;


--函数(function)必须有返回值

create or replace function func_test1(iid varchar2)
return number
as
v_age stus.stu_age%type;
begin
  select stu_age into v_age from stus where stu_id=iid;
  return(v_age);
end;  
/

select func_test1('E85587EB4C594395B681C068F046724E') from dual;

alter function func_test1 compile;
drop function func_test1;


--包由包头(包的说明部分)和包体(包的主体)组成 提高数据安全性、加快模块化开发、提高程序的执行性能

create or replace package pac_test
as
       type my_cursor is ref cursor;
       procedure pro_pac(iid in varchar2);
end;
/

create or replace package body pac_test
as
       procedure pro_pac(iid in varchar2)
         as
         v_age stus.stu_age%type;
        begin
          select stu_age into v_age from stus where stu_id=iid;
          dbms_output.put_line('结果为:' || v_age);
        end;   
end;
/

drop package pac_test;
drop package body pac_test;

--包中存储过程与函数的重载(参数个数/参数类型)

--重载的函数的返回值类型不一致 也不能算为重载
--存储过程的参数的模式不一样不能重载
--重载函数的的参数的类型不能属于同一组类 例如 char与varchar2

--测试
create or replace package pac_over1
as
       procedure p(ipara in varchar2);
       procedure p(ipara out varchar2);
end;
/

create or replace package body pac_over1
as
       procedure p(ipara in varchar2)
        as
        begin
          dbms_output.put_line('输入参数' || ipara);
        end; 
        procedure p(ipara out varchar2)
        as
        begin
          ipara := 'test';
        end;      
end;
/
  


create or replace package pac_over
as
       procedure p(iid in varchar2);
       procedure p(iid2 out char); 
end;
/

create or replace package body pac_over
as
       procedure p(iid in varchar2)
         as
         begin
           dbms_output.put_line('测试重载存储过程1');
         end; 
        procedure p(iid2 out char)
         as
         begin
           dbms_output.put_line('测试重载存储过程2');
           iid2   :='c';
         end;         
end;
/

create or replace procedure pro_test_pac
as
       v_test char;
begin
  pac_over.p2(v_test);
  dbms_output.put_line(v_test);
end; 
/ 


create or replace procedure pro_debug
as
v_a number :=10;
c_a constant binary_integer :=20;
begin
  v_a := v_a+c_a;
  dbms_output.put_line('结果为:' || v_a);
end;
/  
  

  附上存储过程的调试 也是很有用的东西

创建测试存储过程

create or replace procedure pro_debug
as
v_a number :=10;
c_a constant binary_integer :=20;
begin
  v_a := v_a+c_a;
  dbms_output.put_line('结果为:' || v_a);
end;
/ 

上图选中你要调试的存储过程  点击test

点击上图或者f9开始调试 点下图

进入单步调试模式 两次

进入到存储过程里面

你可以设置断点  即 在想要加上断点的那一行开头行号数字那鼠标左键双击即可

查看变量  在Variable那一列输入你想要查看变量的名称   在调试的过程中   即可看到变量的变化值   

在存储过程外调试 查看变量的值也是一样的  函数、触发器 的调试过程也是类似的