oracle_procedure

define：

　　存储过程（Stored Procedure ）是一组为了完成特定功能的SQL 语句 集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。存储过程是由流控制和SQL 语句书写的过程，这个过程经编译和优化后 存储在数据库服务器中，应用程序使用时只要调用即可。在ORACLE 中，若干个有联系的过程可以组合在一起构成程序包。 

advantage：

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.stored procedure can  be used  many times  to reduce database  developer work task.
4.high safety,set a user to use specify stored procedure )可设定只有某用户才具有对指定存储过程的使用权。

1.basic syntax

create [or replace] procedure pro_name [parameter1[,parameter2]] is|as

begin

plsql_sentences;

[exception]

[dowith_sentences;]

end [pro_name];

note: bracket [] represent  can ignore, | represent need option either is or as.

2.exercise

1.compile

 create or replace procedure pro_insertDept is
 begin
     insert into dept values(90,'market','fuzhou');
      commit;
      dbms_output.put_line('insert into successfully');
end pro_insertDept;
 /ou

note: if you come out a error can use show error command;

2.execute

preface:you should lauch result print out in following sentence.

set serveout on

next:you can use the below sentences execute procedure.

execute[exec] pro_insertDept;

3.invoking procedure in pl/sql program block.

begin

　　pro_insertDept;

end;

3.stored parameter

1.Stroed procedure patameter contain  in,out ,in out  three variety model.

a.in model (default model)  example in the following code

CREAET OR REPLACE PROCEDURE pro_insertDept(
    num_deptno in number,
    var_ename in varchar2,
    var_loc in varchar2
) AS
BEGIN
    INSERT INTO dept VALUES(num_deptno,var_ename,var_loc); 
    commit;
END pro_insertDept;
/

 

begin
    pro_insertDept(var_ename=>'purchase',num_deptno => 11,var_loc => 'fz');
       -- Open the cursor and loop through the records
       FOR v_rec IN (SELECT deptno, dname,loc FROM dept) LOOP       
       -- Print the foo and bar values
       dbms_output.put_line('deptno=' || v_rec.deptno || ', dname=' || v_rec.dname||',loc='||v_rec.loc);
       END LOOP;
end;
begin
    pro_insertDept(31,'sal','xm');
       -- Open the cursor and loop through the records
       FOR v_rec IN (SELECT deptno, dname,loc FROM dept) LOOP       
       -- Print the foo and bar values
       dbms_output.put_line('deptno=' || v_rec.deptno || ', dname=' || v_rec.dname||',loc='||v_rec.loc);
       END LOOP;
end;

begin
    pro_insertDept(31,var_ename=>'mainten','xm');
       -- Open the cursor and loop through the records
       FOR v_rec IN (SELECT deptno, dname,loc FROM dept) LOOP       
       -- Print the foo and bar values
       dbms_output.put_line('deptno=' || v_rec.deptno || ', dname=' || v_rec.dname||',loc='||v_rec.loc);
       END LOOP;
end;

 

1.note

　　　　　　　　　in is input parameter type.

1.specify name passing parameter with in model  is disorder.

advantage:understand meaning when you look at the codeing

disadvantage:if insert data a large number and you have a lot of work

2.follow the location passing parameter is order

a.if you forget parameter order can use desc keword.

3.meger passing patameter.

a.if you use specify name passing parameter pass values.after coding use the same method,also.because specify name passing parameter has a order.

b.out model

 

create or replace procedure select_dept(
  num_deptno in number,--定义in模式变量，要求输入部门编号
  var_dname out dept.dname%type,--定义out模式变量，可以存储部门名称并输出
  var_loc out dept.loc%type) is
begin
  select dname,loc
  into var_dname,var_loc
  from dept
  where deptno = num_deptno;--检索某个部门编号的部门信息
exception
  when no_data_found then --若select语句无返回记录
    dbms_output.put_line('该部门编号的不存在');--输出信息
end select_dept;
/

 

DECLARE
    var_dname dept.dname%type;
    var_loc dept.loc%type;
BEGIN
    select_dept(1,var_dname,var_loc);
    DBMS_OUTPUT.PUT_LINE('dept name is:'||var_dname||',location is:'||var_loc);
END;

variable var_dname varchar2(50);
variable var_loc varchar2(50);
execute select_dept(15,:var_dname,:var_loc);
if you not display result then can use below sentences
　　1.print var_dname var_loc;
　　2.select :var_danme,:var_loc from dua; 

summary:

　　out is a output paramter type.

　　step:

　　　　1.create procedure.

　　　　2.define declare variable type in pl/sql block,in order to incept out values.

　　　　3.call stored procedure incept return value in out parameter.

　　note：you need define variable in out model otherwise show error give you in the progarm.

 

 c.in out model

in out model is input parameter as well as output parameter.