Oracle使用手册(三)---存储过程与触发器
--存储过程
/*
--1.过程的语法结构
--参见:http://newland.cnblogs.com/archive/2006/04/05/367531.html
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
--2.执行存储过程
begin
存储过程名;
end;
--创建好的存储过程可以被任何程序调用
*/
--3.带参数的存储过程
/*
参数类型
在PL/SQL过程中,可以有3种类型的参数。
in参数:读入参数,主程序向过程传递参数值。
out参数:读出参数,过程向主程序传递参数值。
in out 参数:双向参数,过程与主程序双向交流数据。
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
*/
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--rownum
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--SELECT * FROM STUDENT WHERE sid=2 and ROWNUM<=1;
/
--SELECT * FROM STUDENT WHERE ROWNUM<=5;
/
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
CREATE OR REPLACE PROCEDURE MRFUTESTPRO
( MYID student.sid%type)
AS
MYNAME student.sname%type;
BEGIN
EXECUTE IMMEDIATE 'SELECT SNAME FROM STUDENT WHERE SID=:1 and ROWNUM<2'
USING MYID
RETURNING INTO MYNAME;
DBMS_OUTPUT.PUT_LINE('我的名字:'||MYNAME);
exception
when others then
dbms_output.put_line('没有符合条件的数据');
end MRFUTESTPRO;
/
BEGIN
MRFUTESTPRO(2);
END;
/
--创建一个存储过程实现输入一个学生的sid编号,查询出拥有该sid号的第一个学生的名字.
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
create or replace procedure mrfuproc
( tempid in student.sid%type ,
tempName out student.sname%type
)
as
myName nvarchar2(50);
begin
select sname into tempName from student where sid=tempid and rownum<2;
myName:='姓名'||tempName;
end mrfuproc;
/
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
declare
myid student.sid%type;
myName student.sname%type;
begin
myid:=10;
myName:='';
mrfuproc(myid,myName);
dbms_output.put_line(myid);
dbms_output.put_line(myName);
end;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
/
--创建队列示例
CREATE SEQUENCE idseqSample
START WITH 1 -- 从1开始计数
INCREMENT BY 1 -- 每次加几个
NOMAXVALUE -- 不设置最大值
MINVALUE 1
CACHE 10 --缓冲区大小
NOCYCLE -- 一直累加,不循环
--NOCACHE -- 不建缓冲区
/
--创建索引
CREATE UNIQUE INDEX system.idindex ON system.student(recordid);
/
--创建队列
CREATE SEQUENCE system.idseq start with 1 increment by 1 maxvalue 987654321 minvalue 1 cache 10 ;
/
--创建触发器 ,实现插入student表中纪录时,自动插入recordid字段数据,并实现自动增量.
CREATE OR REPLACE TRIGGER system.idtrigger
BEFORE INSERT ON system.student REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
BEGIN
SELECT system.idseq.NEXTVAL INTO :NEW.recordid FROM DUAL;
END;
/
--测试 ,首先删除表中的所有记录,然后插入30条纪录.最后检查效果.
delete from system.student;
/
declare
i int:=0;
begin
for i in 1..30 loop
insert into system.student (sid,sname,sage)values(i,'frj'||to_char(i),'26');
end loop;
end;
/
select * from system.student;
/
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)