PL/SQL 存储过程
无参数的存储过程：
create or replace procedure out_time
is
begin
dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd'));
end;
/
调用： exe
无参数的存储过程：
create or replace procedure out_time
is
begin
dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd'));
end;
/
调用： exec out_time;
--------------------------------------------------------------
带有in参数的过程：
create or replace procedure add_employee
(
eno in number,
name in varchar2,--注意，定义参数的时候不能指定大小
sal in number
)
is
begin
insert into emp(empno,ename,sal) values(eno,name,sal);
end;
/
调用：exec add_employee(5569,'lixin',5589);
--------------------------------------------------------------
带有out 参数的过程：
create or replace procedure query_employee
(
eno in number,
name out varchar2,
salary out number
)
is
begin
select ename, sal into name, salary from emp where empno = eno;
end;
/
调用：
SQL> var name varchar2(20);
SQL> var salary number;
SQL> exec query_employee(7788,:name,:salary);
PL/SQL procedure successfully completed
name
---------
SCOTT
salary
---------
3000
--------------------------------------------------------------
带有in out参数的过程：
create or replace procedure pro_in_out
(
num1 in out number,
num2 in out number
)
is
v1 number;
v2 number;
begin
v1 := num1 / num2;
v2 := mod(num1,num2);
num1 := v1;
num2 := v2;
end;
/
调用：
SQL> var n1 number;
SQL> var n2 number;
SQL> exec :n1 := 100;
SQL> exec :n2 := 30;
SQL> exec pro_in_out(:n1, :n2); --不能直接传值,必须传变量,因为是in out类型参数
PL/SQL procedure successfully completed
n1
---------
3.33333333333333
n2
---------
10
传递参数时可以根据位置传递，也可以根据名称传递：
exec add_employee(123,name=>'lixin',sal=>88564);
查看过程源码：
select text from user_source where name = 'ADD_EMPLOYEE';
删除过程：
drop procedure add_employee;
