oracle 10g 创建存储过程示例 新

1.创建存储过程
SQL> create or replace function get_sal3(name varchar2)
  2  return number is
  3  v_sal number(6,2);
  4  begin
  5  select sal into v_sal from emp where upper(ename)=upper(name);
  6  return v_sal;
  7  end;
  8  /

函数已创建。

SQL> var salary number
SQL> exec :salary:=get_sal3('scott')

PL/SQL 过程已成功完成。

SQL> print salary

    SALARY
----------
      3000

2.在PL/SQL的命令窗口中创建存储过程
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
 
SQL> create procedure insert_dept (no in number,name in varchar2)
  2  is
  3  begin
  4  insert into dept (deptno,dname) values (no,name);
  5  end;
  6  /
 
Procedure created
 
SQL> create procedure insert_Dept2(no number,name varchar2)
  2  is
  3  begin
  4   insert into dept (deptno,dname) values (no,name);
  5  end;
  6  /
 
Procedure created
 
SQL> exec insert_dept2(50,'sall');
 
PL/SQL procedure successfully completed

3.在PL/SQL的程序窗口中创建存储过程
create or replace procedure insert_dept4(no in number, Name in varchar2) is
begin
  insert into dept (deptno,dname)values(no,name);
end insert_dept4;
按F8执行