ORACLE PL/SQL 高级部分

--PL/SQL高级部分 --PLSQL存储过程简单示范： create or replace procedure modetest( p_inparameter in number, p_outparameter out number, p_inoutparameter in out number ) is v_localvar number; begin v_localvar :=p_inparameter; p_outparameter :=

--PL/SQL高级部分
--PLSQL存储过程简单示范：
create or replace procedure modetest(
  p_inparameter  in number,
  p_outparameter out number,
  p_inoutparameter in out number
  )
  is
  v_localvar number;
  begin
    v_localvar :=p_inparameter;
    p_outparameter :=7;
    v_localvar:=p_inoutparameter;
    v_localvar:=7;
    end modetest;
/

--创建一个无参的存储过程；
SQL> create or replace procedure out_date
  2  is
  3   begin
  4     dbms_output.put_line(sysdate);
  5     end out_date;
  6  /
Procedure created

SQL> exec out_date();
24-10月-13
PL/SQL procedure successfully completed

--创建一个输入输出参数存储过程：
SQL> create or replace procedure proc_tea(
  2  v_cid in classes.cid%type,
  3  v_cname out classes.cname%type
  4  )
  5  is
  6  begin
  7    select cname into v_cname from classes where cid=v_cid;
  8    end;
  9  /
Procedure created

SQL>
SQL> declare
  2   v_cname classes.cname%type;
  3   begin
  4     proc_tea(5,v_cname);
  5     dbms_output.put_line('cname为：'||v_cname);
  6     end;
  7  /
cname为：2222
PL/SQL procedure successfully completed
 
--创建带有输入参数存储过程；
SQL> create or replace procedure update_tea(
  2   v_cid in classes.cid%type
  3  )
  4  is
  5  begin
  6    update classes set cname='5555' where cid=v_cid;
  7    commit;
  8    end update_tea;
  9  /
Procedure created

SQL> exec update_tea(2);
PL/SQL procedure successfully completed

--创建带有输入参数存储过程；
create or replace procedure query_tea(
 v_cid in classes.cid%type,
 v_cname out classes.cname%type
)
is
begin
  select cname into v_cname from classes where cid=v_cid;
  end query_tea;
/

declare
  v_cname classes.cname%type;
  begin
    query_tea(2,v_cname);
    dbms_output.put_line(v_cname);
    end;
--网络上搜索答案：
--out的参数是返回的结果，应该不能改成in吧？
-- 如果在sqlplus下，执行的方法是
--SQL> var c varchar2(20);
-- SQL> exec get_username(324,:c)
--SQL> print c;
--是否在navicat下也需要先定义一个变量才能获得结果呢？
--直接复制过去是执行不了的  

--out 参数必须先在外部声明变量，再作为procedure的out参数调用，调用之后即可在后面获取经过procedure处理的out参数变量。
 
--带输出参数存储过程调用方法，必须在外面声明一个变量，然后将声明变量传入存储过程中，如上
 
--创建一个参数（同为输入输出参数）
SQL> create or replace procedure inoutpro(
  2  v_cid in out number
  3  )
  4  is
  5   begin
  6     v_cid:=v_cid*0.7;
  7     end inoutpro;
  8  /
Procedure created

SQL>
SQL> declare
  2    v_cid classes.cid%type:=2;
  3    begin
  4      inoutpro(v_cid);
  5      end;
  6  /
PL/SQL procedure successfully completed
  
--使用&符号进行页面输值在PL/SQL中动态调用
declare
  v_cid classes.cid%type:=&p_id;
  begin
    update_tea(v_cid);
    end;
    /
   
--1、存储过程中编译时可能出现一些语法问题，但只是警告的方式提示：“创建过程中带有编译问题”，用户可根据“show errors”命令查看
--详细错误信息;
SQL> show errors;
Errors for PROCEDURE SCOTT.QUERY_TEA:
LINE/COL ERROR
-------- --------------------------------------------
3/22     PLS-00302: 必须声明 'CNAM' 组件
0/0      PL/SQL: Compilation unit analysis terminated
--2、可通过DESC命令获得存储过程的形式参数名称、数据类型、以及模式信息
SQL> desc update_tea;
Parameter Type   Mode Default?
--------- ------ ---- --------
V_CID     NUMBER IN

--3、可根据静态数据字典user_source获取存储在数据库中的存储过程信息，对象名称、对象类型、代码行号、创建对象源代码。。。
select distinct(name) from user_source
NAME                           TYPE               LINE TEXT
QUERY_TEA                      PROCEDURE             1 procedure query_tea(
QUERY_TEA                      PROCEDURE             2  v_cid in classes.cid%type,
QUERY_TEA                      PROCEDURE             3  v_cname out classes.cnam%type
QUERY_TEA                      PROCEDURE             4 )
QUERY_TEA                      PROCEDURE             5 is
QUERY_TEA                      PROCEDURE             6 begin
QUERY_TEA                      PROCEDURE             7   select cname into v_cname from classes where cid=v_cid;
QUERY_TEA                      PROCEDURE             8   end query_tea;
QUERY_TEA                      PROCEDURE             9

--4、存储过程也有权限，同数据表的权限相同，如其他用户想操作当前用户存储过程需授权。如将out_date授予scott用户
SQL> grant execute on out_date to ekp;
Grant succeeded
--授权成功。
--使用ekp用户登录，调用存储过程需加“用户名.存储过程名称”
SQL> execute scott.out_date();
24-10月-13
PL/SQL procedure successfully completed

 

--触发器
--创建触发器(默认语句级触发器)
create or replace trigger t_classes
 after insert on classes
 begin
   dbms_output.put_line('你执行了插入操作...');
   end t_classes;

--testing trigger
insert into classes values(seq_stuid.nextval,'A');

--创建触发器，10月26日之前禁止操作classes表
create or replace trigger t_vaild_classes
before insert or update or delete on classes
begin
  if sysdate     raise_application_error(-20500,'10月26日之前不能操作该对象表！');
    end if;
    end t_vaild_classes;
   
--语句级触发器和行级触发器事例比较
create or replace trigger t_classes
before delete on classes
begin
  dbms_output.put_line('你执行了删除操作...');
  end t_row_classes;

--testing  只触发一次
SQL> delete from classes;
你执行了删除操作...
3 rows deleted

create or replace trigger t_row_classes
before delete on classes
for each row
begin
  dbms_output.put_line('你执行了删除操作...');
  end t_row_classes;

--testing  每执行一条数据都触发   
SQL> delete from classes;
你执行了删除操作...
你执行了删除操作...
你执行了删除操作...
你执行了删除操作...
你执行了删除操作...
4 rows deleted

--触发器事件不仅可以是一个DML操作，还可以由多个DML操作组成，当在触发器中包含多个触发事件时，为了分别针对不同的事件进行不同的处理
--需要使用条件谓词（inserting,updating,deleting）来判断是哪个触发器事件触发了触发器
create or replace trigger t_classes1
before insert or update or delete on classes
begin
  if inserting then
    dbms_output.put_line('你执行了插入操作!');
    elsif updating then
      dbms_output.put_line('你执行了更新操作!');
      elsif deleting then
        dbms_output.put_line('你执行了删除操作!');
        end if;
        end t_classes1;

--testing
SQL> insert into classes values(seq_stuid.nextval,'D');
你执行了插入操作!
1 row inserted

SQL> update classes set cname='A' where cid=1;
你执行了更新操作!
0 rows updated

SQL> delete from classes where cname='D';
你执行了删除操作!

--创建具有新增-删除事件的触发器
create or replace trigger inser_dele_trigger
after insert or delete on classes
declare
 v_count number;
 v_cname varchar2(10);
 begin
   if inserting then
     select count(*) into v_count from classes;
     dbms_output.put_line('总条数：'||v_count);
     elsif deleting then
       select cname into v_cname from classes where cid=195;
       dbms_output.put_line('195对应的CNAME为：'||v_cname);
       end if;
       end inser_dele_trigger;
      
--testing
SQL> insert into classes values(seq_stuid.nextval,'AAA');
总条数：8
1 row inserted

SQL> delete from classes where cid='AAA';
195对应的CNAME为：WANGRONG

--使用行级触发器标示符
-- ：old ：new
--1、只能在行级触发器中使用：old :new标示符
--2、在触发器体的sql语句中使用pl/sql语句中使用这边标识符时，前面要加":" 而在行级触发器的when限制条件中使用这些标识符时，
--前面不要加“：”

--创建一个触发器，在修改classes表的cid时，同时更新studentInfo表的相对应cid字段
create or replace trigger tg_upd_studentinfo
after update of cid on classes
for each row
  begin
    update studentinfo set cid=:new.cid where cid=:old.cid;
    end tg_upd_studentinfo;

--tesing
SQL> update classes set cid=200 where cid=190;
你执行了更新操作!
1 row updated

SQL> select * from studentinfo;
                                  STUID SNAME                                          CID ADDRESS
--------------------------------------- ---------- --------------------------------------- --------------------
                                      1 张三                                           200  陕西西安