一、概念
是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。
二、基本使用
1、创建存储过程sql结构
CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)]
{AS|IS}
[说明部分]
BEGIN
可执行部分
[EXCEPTION
错误处理部分]
END [过程名];
2、创建存储过程sql示例
--创建或者替换存储过程myDemo01
create or replace procedure myDemo01
as
begin
dbms_output.put_line('hello word, my name is stored procedure 111');
end;
create or replace procedure myDemo02
as
name varchar(10);--声明变量，注意varchar需要指定长度
age int;
begin
name:='xiaoming';--变量赋值
age:=18;
dbms_output.put_line('name='||name||', age='||age);--通过||符号达到连接字符串的功能
end;
create or replace procedure myDemo03(name in varchar,age in int)
as
begin
dbms_output.put_line('name='||name||', age='||age);
end;
3、存储过程的调用
--调用存储过程1
declare
begin
myDemo01;
end;
--调用存储过程2
begin
myDemo01; --在此处也可使用myDemo01();完成存储过程的调用
end;
--调用存储过程3
call myDemo01();
--调用存储过程4
SQL> set serveroutput on --可以再command命令窗口执行
SQL> exec mydemo01 --下面是输出结果
三、个人练习sql参考
----------------------------------------------
----------------------------------------------
create table emp(
empno int primary key,
empname varchar2(20));
insert into emp values(1,'Linda');
--需要注意的是，要使用insert all into来插入。语句的最后要加一条select 1 from dual语句。
insert all into emp values(2,'Bob')
into emp values(3,'Lisa')
into emp values(4,'Lu')
into emp values(5,'Pom')
select 1 from dual;
----------------------------------------------
create or replace procedure test00
as
empno int;
empname varchar2(20);
begin
empno:=1;-- :=表示赋值
empname:='emp1';
dbms_output.put_line('empno='||empno||',empname='||empname);-- ||表示字符串连接
end test00;
call test00();
----------------------------------------------
create or replace procedure test01
as
empno emp.empno%type; --表示类型与emp的empno字段类型一致
empname emp.empname%type;
begin
empno:=1;
empname:='emp1';
dbms_output.put_line('empno='||empno||',empname='||empname);
end test01;
call test01();
----------------------------------------------
create or replace procedure test02(empno in int,empname in varchar2)
as
begin
dbms_output.put_line('empno='||empno||',empname='||empname);
end test02;
call test02(2,'sdf');
--注;在调用存储过程时，=>前面的变量为存储过程的形参且必须于存储过程中定义的一致，而=>后的参数为实际参数。当然也不可以不定义变量使用实参
declare
no int;
name varchar2(20);
begin
no := 1;
name := 'name';
test02(empno => no, empname => name);
end;
call test02(empno=>2,empname=>'sdf');
----------------------------------------------
--注：in代表输入，out用于输出，参数默认类型是in类型。
--其中参数IN表示输入参数，是参数的默认模式。
--OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
--OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
--IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去
create or replace procedure test03(empno1 in emp.empno%type,empname out emp.empname%type)
as
begin
dbms_output.put_line('存储过程输入empno参数='||empno1);--输出存储过程输入参数的值
--select 'lululu' into empname from dual ;
select e.empname into empname from emp e where e.empno=empno1; --这里无法使用where e.empno=empno做查询,参数名和搜索字段名不能一样
--SELECT INTO STATEMENT该语句将select到的结果赋值给一个或多个变量
--select into 要求select from 子句的查询结果有且只有一条数据，其它情况都会抛异常，注意加异常保护
exception when others then
dbms_output.put_line('error');
end test03;
declare
empname emp.empname%type;--声明变量
empno emp.empno%type;
begin
empno:=3;
test03(empno1=>2, empname=>empname);--等同于下一行
dbms_output.put_line('empname=' || empname);--接收后进行打印
test03(empno, empname=>empname);--empname作为存储过程的输入参数，接收存储过程中输出参数值
dbms_output.put_line('empname=' || empname);--接收后进行打印
end;
----------------------------------------------
create or replace procedure test04 as
s int;
begin
s:=10/0;
exception when others then
dbms_output.put_line('error!');
end test04;
call test04();
----------------------------------------------
create or replace procedure test05 as
s int;
i int;
begin
s := 1;
loop
dbms_output.put_line(s);
s := s + 1;
if s > 5 then
exit;
end if;
end loop;
for i in 1 .. 5 loop
dbms_output.put_line(i);
end loop;
end test05;
call test05();
----------------------------------------------
create or replace procedure test06 as
begin
for stu in (select * from emp) loop
if (stu.empno < 5) then
dbms_output.put_line('empno='||stu.empno||',empname='||stu.empname);
end if;
end loop;
end test06;
call test06();
----------------------------------------------
create or replace procedure test07 as
empno int := 1; --可以在此声明变量+初始化变量
begin
while empno <= 5 loop
dbms_output.put_line(empno);
empno := empno + 1;
end loop;
end test07;
call test07();
----------------------------------------------
create or replace procedure test08 is
--使用游标需要用is，而不是as
empRow emp%rowtype; --empRow为emp表的每一行
cursor empRows is --游标empRows指向emp的select * from emp的查询结果
select * from emp;
begin
for empRow in empRows loop
dbms_output.put_line('empno=' || empRow.empno || ',empname=' ||
empRow.empname);
end loop;
end test08;
call test08();
----------------------------------------------
create or replace procedure test09
as
empno int := 1; --可以在此声明变量+初始化变量
begin
while empno <= 5 loop
dbms_output.put_line(empno);
empno := empno + 1;
end loop;
end test09;
call test09();
----------------------------------------------
create or replace procedure test10 as
empcount int;
i int := 0;
begin
select count(1) into empcount from emp;
dbms_output.put_line('总数：' || empcount);
while i < empcount loop
dbms_output.put_line('显示：'||i);
i := i + 1;
if mod(i, 5) = 0 then
dbms_output.put_line('提交！');//每5条提交一次
commit;
end if;
end loop;
COMMIT; -- 最后一批不够5条的提交一次
dbms_output.put_line('提交！');
dbms_output.put_line('任务结束!');
end test10;
call test10();
----------------------------------------------
create or replace procedure test11(interval in int) as
empcount int;
i int := 0;
--interval int := 2; --间隔数
begin
select count(1) into empcount from emp;
dbms_output.put_line('总数：' || empcount);
while i < empcount loop
dbms_output.put_line('显示：' || i);
i := i + 1;
if mod(i, interval) = 0 then
dbms_output.put_line('提交！');
commit;
end if;
if mod(i, interval) <> 0 and i = empcount then
dbms_output.put_line('提交！'); -- 最后一批不够2条的提交一次
commit;
end if;
end loop;
dbms_output.put_line('finished!');
end test11;
call test11(2);
四、oracle函数
参考资料：https://www.cnblogs.com/thomasbc/p/12517931.html
create or replace function DelegateAbnormal(organ_id varchar2) return varchar2
is
begin
return organ_id;
end;
select DelegateAbnormal('OYP@SZAIC') from dual
四、参考博文及资料
https://blog.csdn.net/weixin_41968788/article/details/83659164
sys.dbms_scheduler.create_job创建定时任务
%notfound
存储过程 for循环
存储过程入门
oracle定时任务
五、其它资料
sys_guid()：sys_guid(),是Oracle 8i 后提供的函数。SYS_GUID产生并返回一个十六进制32位的全球唯一的标识符
628
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
