注意:本章只要求掌握前两节,综合性强
7.1存储过程
创建过程:
(1)过程代码书写在1.txt文件中;2)在sql plus 中,@ d:\1.txt; )
create or replace procedure p1 is
变量定义;
begin
end;
/
--例题7-1
create or replace procedure p6 is
begin
insert into dept values(77,'市场拓展部','吉林');
end;
/
--调用过程:
1)exec p1;
2)begin
p1;
end;
/
//PS:可以将代码写入TXT文件中,再@
7.1.2存储过程的参数
1.in模式参数(例题7-5)
创建过程:
create or replace procedure i_d(x in number,y in varchar2,z in varchar2) is
//create or replace procedure i_d2(x in dept.deptno%type,y in dept.dname%type,z in dept.loc%type) is
begin
insert into dept values(x,y,z);
end;
/
begin
i_d2(23,'工程部','商丘');
end;
/
调用过程:(包含三种传参的方法:)
a)按名称传递(参数无顺序)
begin
i_d(z=>'成都',x=>15,y=>'采购部');
end;
/
b)按位置传递(参数严格顺序)
begin
i_d(28,'工程部','洛阳');
end;
/
c)混合传递(先按位置,后按名称)
begin
i_d(26,'编辑部',z=>'开封');
end;
/
2.out模式参数
例题7-9:查询结果是一条记录
创建过程:
create or replace procedure s_d(num_d in dept.deptno%type,x out varchar2,y out dept.loc%type) is
begin
select dname,loc into x,y from dept where deptno=num_d;
end;
/
调用过程:
declare
i dept.dname%type;
j dept.loc%type;
begin
s_d(10,i,j);
dbms_output.put_line(i||j);
end;
/
调用过程(exec):
var i varchar2(20);
var j varchar2(20);
exec s_d(10,:i,:j);
print :i :j;
或者:
create or replace procedure s_d(num_d in dept.deptno%type,x out varchar2,y out dept.loc%type) is
begin
select dname,loc into x,y from dept where deptno=num_d;
dbms_output.put_line(x||y);
end;
/
declare
i dept.dname%type;
j dept.loc%type;
begin
s_d(10,i,j);
dbms_output.put_line('调用过程'||i||j);
end;
/
[补充]
查询结果是多条记录:使用游标
create or replace procedure p3(x) is
cursor c1 is select ....;
begin
end;
/
例题6-19:
1)用PL/SQL方法:
declare
cursor cur_emp(var_job in varchar2:='SALESMAN') is select empno,ename,sal from emp where job=var_job;
type record_emp is record
(
x emp.empno%type