存储过程`procedure`

2020.01.29 BoobooWei

定义存储过程

create or replace procedure add_sal
is
--变量声明
begin
update emp set sal=sal*1.1;
end;
/

调用存储过程

begin
add_sal;
end;
/

execute add_sal;
exec add_sal;

执行结果

SQL> create or replace procedure booboo
2 is
3 begin
4 dbms_output.put_line('+++++++');
5 end;
6 /

SQL> begin
2 booboo;
3 end;
4 /
+++++++

PL/SQL procedure successfully completed.

SQL> execute booboo;
+++++++

PL/SQL procedure successfully completed.

SQL> exec booboo;
+++++++

PL/SQL procedure successfully completed.

实践

实践1-带有导入型形式参数

根据雇员编号涨工资

create or replace procedure add_sal
(p_empno number,p_sal number)
is
v_old_sal emp.sal%type;
begin
select sal into v_old_sal from emp where empno=p_empno;
if p_sal<v_old_sal or p_sal is null then
raise_application_error(-20000,'工资不能减少');
else
update emp set sal=p_sal where empno=p_empno;
end if;
end;
/
exec add_sal(7369,700);
drop procedure add_sal;

查看程序源代码:

select text from user_source where name='ADD_SAL';

实践2-带有导出型形式参数的

  1. 编写匿名块进行测试
/* 测试 */
declare
v_empno emp.empno%type:=&p_empno;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=v_empno;
dbms_output.put_line(v_ename||' '||v_sal);
end;
/
  1. 创建存储过程 get_ename
/* 创建存储过程 get_ename */
create or replace procedure get_ename
(p_empno in emp.empno%type,
p_ename out emp.ename%type,
p_sal out emp.sal%type)
is
begin
select ename,sal into p_ename,p_sal from emp where empno=p_empno;
end;
/
  1. 调用存储过程

调用方法1:

declare
g_ename emp.ename%type;
g_sal emp.sal%type;
begin
get_ename(7900,g_ename,g_sal);
dbms_output.put_line(g_ename||' '||g_sal);
end;
/

调用方法2:

var g_ename varchar2(10)
var g_sal number
exec get_ename(7900,:g_ename,:g_sal);

实践3-导入/导出型的形式参数

create or replace procedure get_emp
(g_test in out varchar2)
is
begin
select ename into g_test from emp where empno=g_test;
end;
/

declare
v1 varchar2(10):=7839;
begin
get_emp(v1);
dbms_output.put_line(v1);
end;
/

实践4-带有default值的形式参数

create or replace procedure add_emp
(p_ename emp.ename%type,
p_job emp.job%type default 'CLERK',
p_mgr emp.mgr%type default 7698,
p_hiredate date default sysdate,
p_sal emp.sal%type default 1000,
p_comm emp.comm%type default null,
p_deptno emp.deptno%type default 30)
is
begin
insert into emp values
(seq_empno.nextval,
p_ename,
p_job,
p_mgr,
p_hiredate,
p_sal,
p_comm,
p_deptno);
end;
/

形参赋值

形参赋值的位置表示法:

exec add_emp('Tom','SALESMAN');

形参赋值的名称表示法:

exec add_emp(p_ename=>'Tom',p_job=>'SALESMAN',p_deptno=>20);

形参赋值的混合表示法:

exec add_emp('Tom','SALESMAN',p_sal=>2000,p_mgr=>7839);

存储过程中的事务处理风格

create or replace procedure add_sal
(p_empno number,p_sal number)
is
v_old_sal emp.sal%type;
begin
select sal into v_old_sal from emp where empno=p_empno;
if p_sal<v_old_sal or p_sal is null then
raise_application_error(-20000,'工资不能减少');
else
update emp set sal=p_sal where empno=p_empno;
commit;
end if;
end;
/

自治事务

create or replace procedure add_sal
(p_empno number,p_sal number)
is
pragma autonomous_transaction;
v_old_sal emp.sal%type;
begin
select sal into v_old_sal from emp where empno=p_empno;
if p_sal<v_old_sal or p_sal is null then
raise_application_error(-20000,'工资不能减少');
else
update emp set sal=p_sal where empno=p_empno;
commit;
end if;
end;
/

调用者模式

create or replace procedure add_sal
(p_empno number,p_sal number)
authid current_user
is
pragma autonomous_transaction;
v_old_sal emp.sal%type;
begin
select sal into v_old_sal from emp where empno=p_empno;
if p_sal<v_old_sal or p_sal is null then
raise_application_error(-20000,'工资不能减少');
else
update emp set sal=p_sal where empno=p_empno;
commit;
end if;
end;
/

declare
v_empno number;
v_time timestamp;
begin
delete emp where empno=7369
returning empno,current_timestamp into v_empno,v_time;
dbms_output.put_line(v_empno||' '||v_time);
end;
/

动态sql语句

create or replace procedure test_create
(t_name varchar2)
is
v_sql varchar2(1000);
begin
v_sql:='create table '||t_name||' (x int)';
execute immediate v_sql;
end;
/

declare
p_deptno number:=50;
p_dname varchar2(10):='APP';
p_loc varchar2(13):='BJ';
begin
execute immediate 'insert into dept values (:1,:2,:3)' using p_deptno,p_dname,p_deptno;
end;
/

create or replace procedure test_create
(v_sql varchar2)
is

begin
execute immediate v_sql;d
end;
/