创建时定义名称 2、可以被Oracle server 保存 3、可以被任何程序调用 4、可以被共享
存储过程：
1、不带参数的存储过程：
SQL> create or replace procedure proc1 is
2
3 v_ename emp.ename%type;
4 v_sal emp.sal%type ;
5
6 begin
7 select ename,sal into v_ename,v_sal from emp where empno=&no;
8 dbms_output.put_line('Name is : '||v_ename||' , '||'Salary is : '||v_sal);
9 exception
10 when no_data_found then
11 dbms_output.put_line('you number is not crrect ,please input again !');
12 when others then
13 dbms_output.put_line('Others error !');
14 end;
SQL> exec proc1; //执行存储过程
SQL> create or replace procedure proc1 is
2
3 v_ename emp.ename%type;
4 v_sal emp.sal%type ;
5
6 begin
7 select ename,sal into v_ename,v_sal from emp where empno=&no;
8 dbms_output.put_line('Name is : '||v_ename||' , '||'Salary is : '||v_sal);
9 exception
10 when no_data_found then
11 dbms_output.put_line('you number is not crrect ,please input again !');
12 when others then
13 dbms_output.put_line('Others error !');
14 end;
通过数据字典查看procedure信息：
带有参数的存储过程： 参数定义中，IN、OUT和IN OUT代表参数的三种不同模式：
IN：当调用存储过程时，该模式的形参接收对应实参的值，并且该是只读的，即不能被修改。默认为IN。 OUT：该形参被认为只能写，既只能为其赋值。在存储过程中不能读它的值。返回时，将该形参值传给相应的实参。
IN OUT：都允许
①in
SQL> create or replace procedure ins_dept
2 (v_deptno in number,v_dname varchar2, v_loc in varchar2) is
3
4 e_dept_err exception;
5 pragma exception_init(e_dept_err ,-0001);
6 begin
7 insert into dept values (v_deptno,v_dname,v_loc);
8 commit;
9 exception
10 when e_dept_err then
11 dbms_output.put_line('You deptno is not unique ,Please input unique deptno number !');
12 when others then
13 dbms_output.put_line('Others error !');
14 end;
②OUT:
SQL> create or replace procedure proc2
2 ( v_empno in number ,v_ename out varchar2,v_sal out number)
3 is
4
5 begin
6
7 select ename,sal into v_ename,v_sal from emp where empno=v_empno;
8
9 dbms_output.put_line ('Employee name is: '||v_ename);
10
11 dbms_output.put_line ('Employee salary is: '||v_sal);
12 exception
13 when no_data_found then
14 dbms_output.put_line('Employee ID is error !');
15 when others then
16 dbms_output.put_line('Others error !');
17 end;
在系统下运行：
SQL> var name varchar2(10);
SQL> var sal number;
SQL> exec proc2(7369,:name,:sal);
PL/SQL procedure successfully completed
name
---------
SMITH
sal
---------
6800 通过PLSQL 块运行：
SQL> declare
2 v_name emp.ename%type;
3 v_sal emp.sal%type;
4 begin
5 proc2(7369,v_name,v_sal);
6 end;
3.IN-OUT:
SQL> create or replace procedure proc3
2 (v_empno in out number ,v_ename out varchar2,v_sal out number)
3 as
4 begin
5 select empno,ename,sal into v_empno,v_ename,v_sal from emp where empno=v_empno;
6 dbms_output.put_line ('Employee ID is: '||v_empno);
7 dbms_output.put_line ('Employee name is: '||v_ename);
8 dbms_output.put_line ('Employee salary is: '||v_sal);
9 exception
10 when no_data_found then
11 dbms_output.put_line('Employee ID is error !');
12 when others then
13 dbms_output.put_line('Others error !');
14 end;
15 /
Procedure created 执行存储过程： SQL> declare
2 v_empno emp.empno%type;
3 v_ename emp.ename%type;
4 v_sal emp.sal%type;
5
6 begin
7 v_empno := &n;
8 proc3(v_empno,v_ename,v_sal);
9
10 end;