【PL/SQL练习】命名块： 存储过程、函数、触发器、包

创建时定义名称 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;