13518219792
Oracle存储过程写法实例
上城网站建设公司创新互联,上城网站设计制作，有大型网站制作公司丰富经验。已为上城1000+提供企业网站建设服务。企业网站搭建\成都外贸网站制作要多少钱，请找那个售后服务好的上城做网站的公司定做！
总结项目中写的存储过程例子：
Oracle存储过程基本语法 存储过程
1 CREATE OR REPLACE PROCEDURE 存储过程名
2 IS/AS
3 BEGIN
4 NULL;
5 EXCEIPTION;
6 END;
1、创建存储过程，后面可用is或者as：
create or replace procedure PRO_COMPLAIN_TEMPLATE as
2、定义变量，此处用到了%TYPE和%ROWTYPE，参考 /database/201211/168564.html ，另外定义了一个游标，TEM_INSTANCE TEMPLATE_CUR%ROWTYPE这个类型定义必须要在游标定义之后：
NEED_DO_FOR_ZL INTEGER;
CURRENT_MAX_ID MEMO_TEMPLET.TEMPLET_ID%TYPE;
CURSOR TEMPLATE_CUR IS SELECT TEMPLET_NAME, TEMPLET_CONTENT FROM SYS_COMPLAINT_TEMPLET;
TEM_INSTANCE TEMPLATE_CUR%ROWTYPE;
3、begin开始块：
begin
4、该插入语句使用了DBLINK，还有使用DBMS_OUTPUT.put_line('打印信息')进行信息输出：
insert into MEMO_TEMPLET (TEMPLET_ID, TEMPLET_CONTENT, TEMPLET_TYPE, TEMPLET_MEMO) (SELECT * FROM MEMO_TEMPLET@COMPANY);
SELECT COUNT(*) INTO NEED_DO_FOR_ZL FROM SYS_COMPLAINT_TEMPLET;
IF(NEED_DO_FOR_ZL 0) THEN
DBMS_OUTPUT.put_line('打印信息');
SELECT MAX(TEMPLET_ID)+1 INTO CURRENT_MAX_ID FROM MEMO_TEMPLET;
DBMS_OUTPUT.put_line('MEMO_TEMPLET最大ID' || CURRENT_MAX_ID);
FOR TEM_INSTANCE IN TEMPLATE_CUR LOOP
INSERT INTO MEMO_TEMPLET (TEMPLET_ID, TEMPLET_CONTENT,
TEMPLET_TYPE, TEMPLET_MEMO) VALUES (CURRENT_MAX_ID,
TEM_INSTANCE.TEMPLET_CONTENT, '7', NULL);
CURRENT_MAX_ID := CURRENT_MAX_ID + 1;
DBMS_OUTPUT.put_line(TEM_INSTANCE.TEMPLET_CONTENT);
END LOOP;
END IF;
COMMIT;
4、exception块，使用WHEN OTHERS THEN，其中用raise可显示错误信息：
exception
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('sqlcode : ' ||sqlcode);
raise;
ROLLBACK;
IF TEMPLATE_CUR%ISOPEN THEN
CLOSE TEMPLATE_CUR;
END IF;
DBMS_OUTPUT.put_line('打印信息');
end;
注：关于游标的使用，如果像本例中使用for循环去遍历游标的话则不需要显式的去用open/close
cursor打开和关闭游标，此处会自己处理，如果使用fetch
into的话就需要显式开关游标，另外任意执行一个update操作，用隐式游标sql的属性%found,%notfound,%rowcount,%isopen观察update语句的执行情况，也可以使用来判断游标状态，如IF
TEMPLATE_CUR%ISOPEN THEN。
create or replace procedures_name(参数名1 in 参数类型1,参数名 in 参数类型2....) is
变量名1 变量类型1 :=赋值;
变量名2 变量类型2 :=赋值;
定义游标：cursor(固定写法) cursor_name(自定义) is select * from table_name;
begin
这中间写sql，至于if和循环什么的自己上网看一些Oracle存储过程的语法
end procedures_name;
create or replace procedure Pr_UserLogin (
username varchar,
password varchar
)
as
begin
--过程体
end
另外，oracle的存储过程不能像sqlserver那样直接反正一个结果集。需要用程序包来实现。
包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成
1.创建包
--创建一个包sp_package
create package sp_package is
--声明该包有一个过程和函数，（没有实现）
procedure update_sal(name varchar2,newsal number);
function annual_nicome(name varchar2) return number;
end;
--创建包体（用于实现已经声明的函数和过程）
create package body sp_package is
procedure update_sal(name varchar2,newsal number)
is
BEGIN
UPDATE emp
SET sal = newsal
WHERE ename = name;
END;
function annual_income(name varchar2)
return number is
annual_salary number;
BEGIN
SELECT sal * 12 + Nvl(comm,0)
INTO annual_salary
FROM emp
WHERE ename = name;
RETURN annual_salary;
END;
end;
--调用包中的内容
exec sp_package.update_sal('name',number);
---创建
create or replace procedure GenTestUserData(nRecordCount in number Default 50000) is
nCurrentCount number; -- 当前表中记录
nMaxLogID number;
nInsertCount number;
begin
select Count(LogID), Nvl(Max(LogID), 0)
into nCurrentCount, nMaxLogID
from MyUser;
nInsertCount:= nRecordCount - nCurrentCount;
if nInsertCount = 0 then
Return;
end if;
-- 插入数据
insert into MyUser(FirstName, LastName, LogID, Mail, Location)
select FirstName, LastName, LogID, 'MyUser@' || To_Char(LogID) || '.Com', '.' || To_Char(LogID) || '.Com'
from (select GetRandomHZ FirstName, GetRandomHZ LastName, nMaxLogID + Rownum LogID
from All_Tab_Columns
where Rownum = nInsertCount) TestData;
end;
/
--删掉
drop procedure GenTestUserData;
Oracle存储过程基本语法：
CREATE OR REPLACE PROCEDURE 存储过程名
IS
BEGIN
NULL;
END;
解释：
行1:
CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它;
行2:
IS关键词表明后面将跟随一个PL/SQL体。
行3:
BEGIN关键词表明PL/SQL体的开始。
行4:
NULL PL/SQL语句表明什么事都不做，这句不能删去，因为PL/SQL体中至少需要有一句;
行5:
END关键词表明PL/SQL体的结束。
