存储过程
创建过程范例
create or replace procedure pro_kingsql_p1(
p_one in varchar2,--可以传入参数
p_two out varchar2,--可以返回值
p_three in out varchar2)--既可以传入参数，又可以返回值
is
begin
dbms_output.put_line('parameter: '||'p_one: '||p_one ||'p_two: '||p_two ||',p_three:'||p_three);
end;
/
set serveroutput on
declare
two varchar2(20):='zhuohui';
three varchar2(20):='hong';
begin
pro_kingsql_p1('kingsql',two,three);
end;
/
parameter: p_one: kingsql p_two: ,p_three:hong
PL/SQL procedure successfully completed.
declare
begin
pro_kingsql_p1('kingsql','KINGSQL2','KINGSQL3');
end;
/
pro_kingsql_p1('kingsql','KINGSQL2','KINGSQL3');
*
ERROR at line 3:
ORA-06550: line 3, column 26:
PLS-00363: expression 'KINGSQL2' cannot be used as an assignment target
ORA-06550: line 3, column 37:
PLS-00363: expression 'KINGSQL3' cannot be used as an assignment target
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
create or replace procedure pro_kingsql_p1(
p_one in varchar2,--可以传入参数
p_two out varchar2,--可以返回值
p_three in out varchar2--既可以传入参数，也可以返回值
)is
two_a varchar2(20):='haha';
begin
p_two:=two_a;--twoa变量赋给p_two参数.
dbms_output.put_line('parameter: '||p_one ||',p_two: '||p_two ||',p_three: '||p_three);
end;
/
~
SQL> set serveroutput on
SQL> declare
2 two varchar2(20):='zhuohui';
3 three varchar2(20):='hong';
4 begin
5 pro_kingsql_p1('kingsql',two,three);
6 end;
7 /
parameter: kingsql,p_two: haha,p_three: hong
PL/SQL procedure successfully completed.
OUT、IN OUT不能设置默认值
create or replace procedure pro_kingsql_p1(
p_one in varchar2,
p_two out varchar2,
p_three in out varchar2 default ‘kingsql3’)
is
begin
dbms_output.put_line('parameter: '||'p_one: '||p_one ||'p_two: '||p_two ||',p_three:'||p_three);
end;
/
会报错
过程调用
create or replace procedure pro_kingsql_p2(
v_001 in varchar2,
v_002 out varchar2,
v_003 out varchar2)
is
zhuohui varchar2(100):='zhuohui';
hong varchar2(100):='hong';
begin
v_002:=zhuohui;
v_003:=hong;
dbms_output.put_line(v_001||'tel:13820305037');
pro_kingsql_p1(v_001,v_002,v_003);
end;
/
declare
two varchar2(20):='hehe';
three varchar2(20):='gaga';
begin
pro_kingsql_p2('kingsql',two,three);
end;
/
删除过程
DROP PROCEDURE 过程名
比较存储过程和函数
过程：执行一个PL/SQL语句，在头部不包含RETURN语句，可以返回多个值，不包含RETURN语句
函数：作为表达式的一部分调用，在头部必须包含一个RETURN字句，必须返回一个单个的值，必须包含至少一个RETURN语句
写一个输出工资等级的过程
create or replace procedure pro_emp_02(
v_eno emp.empno%type)
is
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=v_eno;
case
when v_sal between 0 and 2000 then
dbms_output.put_line(v_ename||'每个月赚'||v_sal||'小意思');
when v_sal between 2001 and 5000 then
dbms_output.put_line(v_ename||'每个月赚'||v_sal||'中等意思');
when v_sal>5000 then
dbms_output.put_line(v_ename||'每个月赚'||v_sal||'开始有意思');
else
dbms_output.put_line(v_ename||'每个月赚'||v_sal||'小意思');
end case;
end pro_emp_02;
/
execute pro_emp_02(7788);
SCOTT每个月赚3000中等意思
写一个显示年薪的过程
create or replace procedure pro_emp_01(
v_eno in emp.empno%TYPE)
is
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
begin
select ename,sal into v_ename,v_sal from emp where empno=v_eno;
dbms_output.put_line(v_ename||'''s annual salary is'||v_sal*12);
end pro_emp_01;
/
Execute pro_emp_01(7566);
JONES's annual salary is35700