.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
/**创建一个存储过程**/
create or replace procedure myproc(id varchar2)
as
name varchar(2);
begin
select book_name into name from books where bookid = id ;
DBMS_OUTPUT.PUT_LINE(name);
end;
/**调用存储过程**/
declare
name varchar2(8);
begin
name = '1000';
myproc(name);
end;
bigin
myproc(“001”);
end;
execute myproc(“001”)
/**带判断语句**/
create or replace procedure test(x in number) is
begin
if x >0 then
begin
x := 0 - x;
end;
end if;
if x = 0 then
begin
x: = 1;
end;
end if;
end test;
/**带输出参数的**/
create or replace procedure myproc2(id varcahr,name out varcahr2)
is
begin
select book_name into name from books where books_id = id ;
end;
declare
tid varchar2(10);
tname varchar2(10);
begin
tid:='0001';
myproc2(tid,tname);
end;
语句级触发器 无论受影响的行数是多少，都只执行一次
SQL> CREATE OR REPLACE TRIGGER trgdemo
AFTER INSERT OR UPDATE OR DELETE
ON order_master
BEGIN
IF UPDATING THEN
DBMS_OUTPUT.PUT_LINE(‘已更新 ORDER_MASTER 中的数据');
ELSIF DELETING THEN
DBMS_OUTPUT.PUT_LINE(‘已删除 ORDER_MASTER 中的数据');
ELSIF INSERTING THEN
DBMS_OUTPUT.PUT_LINE(‘已在 ORDER_MASTER 中插入数据');
END IF;
END;
行级触发器 对DML语句修改的每个行执行一次
SQL> CREATE TABLE TEST_TRG
(ID NUMBER, NAME VARCHAR2(20));
SQL> CREATE SEQUENCE SEQ_TEST;
SQL> CREATE OR REPLACE TRIGGER BI_TEST_TRG
BEFORE INSERT OR UPDATE OF ID
ON TEST_TRG
FOR EACH ROW
BEGIN
IF INSERTING THEN
SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL;
ELSE
RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值！');
END IF;
END;
/**删除触发**/
crate or replace trigger del_deptid
after delete on deptment
for eache row
begin
delete from emp where id =:old.id;
end;
/**增加出发**/
crate or replace trigger insert_dept
after insert on depement
for each row
begin
insert into emp(eid,ename,id) values('121','qeer',:new.id);
end;
end;
/**修改触发**/
create or replace trigger update_dept
after update on deptment
for each row
begin
update emp set id:=new.id where id = :old.id
end;
/**删除触发（有异常）**/
crate or replace trigger del_deptid
after delete on deptment
for eache row
begin
if :old.id = '01' then
raise_application_error(-20000,'不允许删除！'); (-20999~-20000)
end if;
end;
deptment(部门表)
id name
01 a部门
02 b部门
03 c部门
emp员工表
eid ename sex id
001 张三 男 01
002 李四 女 02
003 王五 男 03
