procedure

创建过程

CREATE OR REPLACE PROCEDURE name
    [(parameter[, parameter, ...])]
AS
    [local declarations]
BEGIN
    executable statements
[EXCEPTION
    exception handlers]
END [name];

   
   
CREATE OR REPLACE PROCEDURE Discount AS
  CURSOR c_group_discount IS
    SELECT distinct s.course_no, c.description
      FROM section s, enrollment e, course c
     WHERE s.section_id = e.section_id
       AND c.course_no = s.course_no
     GROUP BY s.course_no, c.description, e.section_id, s.section_id
    HAVING COUNT(*) >= 8;
BEGIN
  FOR r_group_discount IN c_group_discount LOOP
    UPDATE course
       SET cost = cost * .95
     WHERE course_no = r_group_discount.course_no;
    DBMS_OUTPUT.PUT_LINE('A 5% discount has been given to ' ||
                         r_group_discount.course_no || ' ' ||
                         r_group_discount.description);
  END LOOP;
END;
SQL> select to_char(line,99)||'>',text from user_source where name='DISCOUNT';
TO_C TEXT
---- ----------------------------------------------------------------------
  1> PROCEDURE Discount
  2> AS
  3> CURSOR c_group_discount
  4> IS
  5> SELECT distinct s.course_no, c.description
  6> FROM section s, enrollment e, course c
  7> WHERE s.section_id = e.section_id
  8> AND c.course_no = s.course_no
  9> GROUP BY s.course_no, c.description,
 10> e.section_id, s.section_id
 11> HAVING COUNT(*) >=8;
 12> BEGIN
 13> FOR r_group_discount IN c_group_discount
 14> LOOP
 15> UPDATE course
 16> SET cost = cost * .95
 17> WHERE course_no = r_group_discount.course_no;
 18> DBMS_OUTPUT.PUT_LINE
 19> ('A 5% discount has been given to '||
 20> r_group_discount.course_no||' '||
 21> r_group_discount.description
 22> );
 23> END LOOP;
 24> END;

过程传入传出参数

参数用于在调用环境和服务器之间来传递值，包括传输用于处理的值，或者执行过程后所返回的值。三种参数模式分别是IN、OUT和IN OUT

1.Modes

Modes指明所传递的参数是传入的，还是传出的。

2.Formail and Actual Parameters(形参和实参)

形参是模块头部的圆括号中所指明的名称。实参是当该模块被调用时，圆括号中作为参数列表的值或者表达式。形参和相关的实参必须属于相同的或者兼容的数据类型。

3.使用参数值传入约束（数据类型）

形参不需要对数据类型做出约束。例如，无需指定诸如varchar2(60)之类的约束，只需要在参数列表中给参数名定义VARCHAR2。当发出调用时，在传入值时加入约束

4.匹配形参和实参

可以使用两种方法来匹配形参和实参：位置表示法和命名表示法。位置表示法是依靠位置序号来标示：当执行过程时所使用参数的顺序匹配过程头部定义时的形参顺序。命名表示法是一种显式的标识方法，使用符号=>，如下所示：

    formal_parameter_name .=> argument_value

   
   
create or replace procedure find_name(
	i_student_id in number,
	o_first_name out varchar2,
	o_last_name out varchar2)
as
begin
	select first_name,last_name 
	into o_first_name,o_last_name
	from student
	where student_id=i_student_id;
exception
	when others then
 		dbms_output.put_line('Error in finding student_id:
'||i_student_id);
end find_name;
/
DECLARE
  v_local_first_name student.first_name%TYPE;
  v_local_last_name  student.last_name%TYPE;
BEGIN
  find_name(145, v_local_first_name, v_local_last_name);
  DBMS_OUTPUT.PUT_LINE('Student 145 is: ' || v_local_first_name || ' ' ||
                       v_local_last_name || '.');
END;
/
Student 145 is: Paul Lefkowitz.
PL/SQL procedure successfully completed.

出参

  
  
create or replace procedure p_getappkey
(vappname varchar2,
 vappkey out varchar2
)
is
begin
    for x in (select appkey from t_apps a
      where a.appname=vappname)
      loop
         vappkey:=x.appkey;
      end loop;
end;
declare
   v2 varchar2(200);
   begin
     p_getappkey(vappname => '白猫计划',vappkey => v2);
   dbms_output.put_line(v2); 
end;