Oracle存储过程常⽤语法及其使⽤
1、什么是存储过程
存储过程Procedure是⼀组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，⽤户通过指定存储过程的名称并给出参数来执⾏。它可以接受参数、输出参数，并可以返回单个或多个结果集以及返回值。
2、存储过程基本语法
存储过程的⼀般格式如下：
CREATE [OR REPLACE] PROCEDURE procedure_name
IS [AS]
声明部分
BEGIN
执⾏部分
EXCEPTION
异常处理部分
END;
调⽤存储过程：
call procedure_name();
---------------------------------
exec procedure_name();
---------------------------------
begin
pro_update_emp();
end;
写⼀个简单的存储过程使emp表的sal值增加300。
CREATE or replace procedure pro_update_emp
as
begin
update emp set sal=sal+300;
end;
调⽤存储过程
call pro_update_emp ();
3、数据类型
3.1 %type 数据类型：
当使⽤%TYPE属性定义变量时，Oracle会⾃动地按照数据库表中相应的列来确定新变量的类型和长度。
如下，将emp表的ename字段的数据类型（如 ‘varchar(2)’）赋给变量 v_ename
v_ame%type
3.2 %ROWTYPE数据类型：
如果⼀张表中包含较多的列，则可以使⽤%ROWTYPE来表⽰表中⼀⾏记录的变量的数据类型。
如下：将dept表中⼀⾏中各字段的数据类型(‘number’,’varchar2(50)’,varchar2(50))赋给v_dept_row,⾮常便利，可以直接查询后将⼀⾏数据赋值。
（注：语句中 into 执⾏赋值操作，将查询结果直接赋值给 v_dept_row）
CREATE or replace procedure pro_update_emp
as
v_dept_row dept%rowtype;
begin
select * into v_dept_row from dept where deptno=11;
end;
3.3 %record数据类型：
⾃定义记录的数据类型，声明⼀个⾏数据类型，将每列的数据类型进⾏⾃定义。
type emp_record_type is RECORD( //声明⾃定义数据类型的名字为 emp_record_type
ame%type,
sal emp.sal%type,
comm empm%type,
total_sal sal%type
);
v_emp_record emp_record_type; //定义变量v_emp_record的数据类型为 emp_record_type
3.4 TABLE数据类型：
TABLE(索引表)相当于⼀个键值集合，键是唯⼀的，⽤于查对应的值。键可以是整数或字符串。
declare
type dept_table_type is table of dept%rowtype index by binary_integer; //声明table数据类型 dept_table_type
v_dept_table dept_table_type; //声明v_dept_table的数据类型为 dept_table_type
begin
select * into v_dept_table(0) from dept where deptno=11; //按索引查询并赋值
select * into v_dept_table(1) from dept where deptno=12;
end;
4、带参数的存储过程
4.1 输⼊参数 (IN) ：当为过程定义参数时，如果不指定参数模式，则默认为输⼊参数（注：使⽤输出语句之前应提前运⾏打开输出环境变量语句’set serveroutput on’）
create or replace procedure
pro_query_emp0(v_no pno%type) //in 代表输⼊参数，可省略
as
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=v_no; //将会有提醒输⼊v_no参数的值
dbms_output.put_line('该员⼯薪⽔为：'||v_sal); //输出语句
exception
when no_data_found then
dbms_output.put_line('不到该员⼯！');
end;
调⽤：
call pro_query_emp0(11)
4.2 输出参数 (OUT) ：将查询结果赋值给输⼊参数 out_sql中
create or replace procedure
pro_query_emp(v_no pno%type, out_sal out number)
as
begin
select sal into out_sal from emp where empno=v_no;
exception
when no_data_found then
dbms_output.put_line('不到该员⼯！');
end;
调⽤：
declare
v_pno%type;
v_sal emp.sal%type;
begin
v_no:=&no;
pro_query_emp(v_no,v_sal);
dbms_output.put_line('薪⽔是：'||v_sal);
end;
4.3 输⼊输出参数 (IN OUT)：既作输⼊参数，⼜作输出参数
create or replace procedure pro_testinout
(param_num in out number)
as
begin
select sal into param_num from emp
where empno=param_num;
end;
调⽤：
declare
inout_num number;
begin
inout_num:=&no;
pro_testinout(inout_num);
dbms_output.put_line('⼯资是：'||inout_num);
end;
5、三种传值⽅式
先创建⼀个存储过程
create or replace procedure
pro_add_dept(v_deptno number,v_dname varchar2, v_loc varchar2)
as
begin
insert into dept values(v_deptno,v_dname,v_loc);
end;
按位置传值：
exec pro_add_dept(70,'研发部','北京');
按名称传值:
call pro_add_dept(v_deptno=>90,v_loc=>'南京',v_dname=>'软件部');
混合传值：
exec pro_add_dept(100,v_loc=>'南京',v_dname=>'软件部');
6、流程控制语句
6.1、条件控制语句 IF THEN
基本格式
IF 条件表达式1 THEN
语句段1
ELSIF 条件表达式2 THEN
语句段2
......
ELSIF 条件表达式n
语句段n
END IF;
实例：如果奖⾦comm为空，则将其更新为 v_emp.sal的0.1倍，如果⼩于1000则设为1000，如果以上条件都不满⾜，则将comm在原有的基础上加0.1倍。
if v_empm is null then
update emp set comm=v_emp.sal*0.1 where empno=pno;
elsIf v_empm<1000 then
update emp set comm=1000 where empno=pno;
else
update emp set comm=comm+comm*0.1 where empno=pno;
end if;
6.2、条件控制语句 CASE
基本格式
CASE
WHEN 条件表达式1 THEN
语句段1;
WHEN 条件表达式2 THEN
语句段2;
.
.....
ELSE
语句段n;
END CASE;
实例：将v_sal⼩于3000的评为A级⼯资，在3000⾄5000之间的评为B级⼯资，将其他的评为C级⼯资。
case
when v_sal<3000 then dbms_output.put_line('A级⼯资');
when v_sal>=3000 and v_sal<5000 then dbms_output.put_line('B级⼯资');
else dbms_output.put_line('C级⼯资');
end case;
6.3、LOOP 循环
基本格式：
LOOP
EXIT [WHEN 条件表达式]
语句段;
END LOOP;
实例：循环向dept中插⼊数据
create or replace procedure pro_insert_dept
as
type dept_table_type is table of dept%rowtype index by binary_integer;
i number(1):=0;
v_dept_table dept_table_type;
sql存储过程实例begin
v_dept_table(0).deptno:='14';
v_dept_table(0).dname:='政法研发部';
v_dept_table(0).loc:='上海';
v_dept_table(1).deptno:='15';
v_dept_table(1).dname:='邮政⾦融部';
v_dept_table(1).loc:='北京';
v_dept_table(2).deptno:='16';
v_dept_table(2).dname:='系统集成部';
v_dept_table(2).loc:='深圳';
loop
exit when i>2;
insert into dept values( v_dept_table(i).deptno,v_dept_table(i).dname,v_dept_table(i).loc); i:=i+1; //每次循环i+1，当i的值⼤于2时结束循环
end loop;
end;
6.3、While循环
基本格式：
WHILE 条件表达式 LOOP
语句段;
END LOOP;
实例：
while i<=2 loop
insert into dept values(v_dept_table(i).deptno,v_dept_table(i).dname,v_dept_table(i).loc);
i:=i+1;
end loop;
6.4 FOR循环
基本格式：
（注：如加上reverse表⽰倒叙循环执⾏语句）
FOR 循环变量 in [REVERSE] 初值表达式..终值表达式 LOOP
语句段;
END LOOP;
实例：
for i in 0..v_unt-1 loop
insert into dept values( v_dept_table(i).deptno,v_dept_table(i).dname,v_dept_table(i).loc); end loop;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论