一,基本入门介绍
看创建存储过程的基本定义。注意,带有[]的都是可选的,可有可无的。只是语法能通过,当然根据自己需要
处理。
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
create table students
(
ID int,
userName varchar(100),
userPass varchar(100),
userAge int
)
insert into students values(1,'jack','jjjaa',23);
insert into students values(2,'rose','jjjaa',21);
insert into students values(3,'lucy','jjjaa',22);
insert into students values(4,'Tony','jjjaa',24);
commit;
这里我们新建一个存储过程,对于某个用户添加年龄,哈哈,当然这个是没什么意义的,学习,从简单入手。
在实际开发中,语法,原理是一样的。
create or replace procedure SP_Update_Age
(
uName in varchar,--note,here don't have length ,sql have lenth ,not in oracle.
Age in int
)
as
begin
update students set UserAge = UserAge + Age where userName = uName;
commit;
end SP_Update_Age;
在执行存储过程之前,我们先查看原来的数据。
select * from students
/*********************
ID USERNAME USERPASS USERAGE
1 jack jjjaa 23
2 rose jjjaa 21
3 lucy jjjaa 22
4 Tony jjjaa 24
**********************/
然后我们在SQL Editor中执行如下存储过程。注意看是怎么调用的:
exec SP_UPDATE_AGE('jack',1);
select * from students;
/********************
ID USERNAME USERPASS USERAGE
1 jack jjjaa 24 --noted,have changed
2 rose jjjaa 21
3 lucy jjjaa 22
4 Tony jjjaa 24
*********************/
二,基本语法介绍
1,变量赋值
变量名 := 值;
2,判断语句。
if
比较式
then
begin
end;
end
if
create or replace procedure Test(x in out number)
is
begin
if x<0 then
begin
x:= 0 - x;
end;
elsif x > 0 then --noted here elsif
begin
x:= x ;
end;
else
x:= 0;
end if;
end Test;
set serveroutput on; --没这句话,看不到dmbs_output信息。
declare
num number;
begin
num:= -1;
test(num);
dbms_output.put_line( 'num = ' || num );
end;
/******************************
num = 1
PL/SQL procedure successfully completed.
*******************************/
3,For循环,
For in ..loop;
set serveroutput on;
DECLARE
x NUMBER := 100;
BEGIN
FOR i IN 1..10 LOOP --noted here
IF MOD(i,2) = 0 THEN -- i is even
dbms_output.put_line( 'i: '||i||' is even ' );
ELSE
dbms_output.put_line('i: '|| i||' is odd' );
END IF;
x := x + 100;
dbms_output.put_line('x value: '|| x);
END LOOP;
COMMIT;
END;
/*************************
i: 1 is odd
x value: 200
i: 2 is even
x value: 300
i: 3 is odd
x value: 400
i: 4 is even
x value: 500
i: 5 is odd
x value: 600
i: 6 is even
x value: 700
i: 7 is odd
x value: 800
i: 8 is even
x value: 900
i: 9 is odd
x value: 1000
i: 10 is even
x value: 1100
PL/SQL procedure successfully completed.
*************************/
4,While 循环。
create or replace Procedure Test2(i in out number)
as
begin
while i < 10 loop
begin
i:= i+1;
end;
end loop;
end Test2;
set serveroutput on;
declare
num number;
begin
num:= 1;
test2(num);
dbms_output.put_line( 'num = ' || num );
end;
/*********************
num = 10
PL/SQL procedure successfully completed.
***********************/
Oracle存储过程
1、定义
所谓存储过程(Stored Procedure),就是一组用于完成特定数据库功能的SQL语句集,该SQL语句集
经过编译后存储在数据库系统中。在使用时候,用户通过指定已经定义的存储过程名字并给出相应的
存储过程参数来调用并执行它,从而完成一个或一系列的数据库操作。
2、存储过程的创建
Oracle存储过程包含三部分:过程声明,执行过程部分,存储过程异常。
(1)无参存储过程语法
create or replace procedure NoParPro
as //声明
;
begin // 执行
;
exception//存储过程异常
;
end;
(2)带参存储过程实例
create or replace procedure queryempname(sfindno emp.empno%type)
as
sName emp.ename%type;
sjob emp.job%type;
begin
....
exception
....
end;
(3)带参数存储过程含赋值方式
create or replace procedure runbyparmeters
(isal in emp.sal%type,
sname out varchar,
sjob in out varchar)
as
icount number;
begin
select count(*) into icount from emp where sal>isal and job=sjob;
if icount=1 then
....
else
....
end if;
exception
when too_many_rows then
DBMS_OUTPUT.PUT_LINE('返回值多于1行');
when others then
DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错!');
end;
其中参数IN表示输入参数,是参数的默认模式。
OUT表示返回值参数,类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值,表示该参数可以将某个值传递回调用他的过程
IN OUT表示该参数可以向该过程中传递值,也可以将某个值传出去。
(4)存储过程中游标定义使用
as //定义(游标一个可以遍历的结果集)
CURSOR cur_1 IS
SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,
SUM(usd_amt)/10000 usd_amt_sn
FROM BGD_AREA_CM_M_BASE_T
WHERE ym >= vs_ym_sn_beg
AND ym <= vs_ym_sn_end
GROUP BY area_code,CMCODE;
begin //执行(常用For语句遍历游标)
FOR rec IN cur_1 LOOP
UPDATE xxxxxxxxxxx_T
SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn
WHERE area_code = rec.area_code
AND CMCODE = rec.CMCODE
AND ym = is_ym;
END LOOP;
(5)游标的定义
--显示cursor的处理
declare
---声明cursor,创建和命名一个sql工作区
cursor cursor_name is
select real_name from account_hcz;
v_realname varchar2(20);
begin
open cursor_name;---打开cursor,执行sql语句产生的结果集
fetch cursor_name into v_realname;--提取cursor,提取结果集中的记录
dbms_output.put_line(v_realname);
close cursor_name;--关闭cursor
end;
3、在Oracle中对存储过程的调用
(1)过程调用方式一
declare
realsal emp.sal%type;
realname varchar(40);
realjob varchar(40);
begin //过程调用开始
realsal:=1100;
realname:='';
realjob:='CLERK';
runbyparmeters(realsal,realname,realjob);--必须按顺序
DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);
END; //过程调用结束
(2)过程调用方式二
declare
realsal emp.sal%type;
realname varchar(40);
realjob varchar(40);
begin //过程调用开始
realsal:=1100;
realname:='';
realjob:='CLERK';
--指定值对应变量顺序可变
runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob);
DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);
END; //过程调用结束
(3)过程调用方式三(SQL命令行方式下)
1、SQL>exec proc_emp('参数1','参数2');//无返回值过程调用
2、SQL>var vsal number
SQL> exec proc_emp ('参数1',:vsal);// 有返回值过程调用
或者:call proc_emp ('参数1',:vsal);// 有返回值过程调用