ORACLEPL/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 < '10-26日-13' then
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 陕西西安