Simple stored procedures. Functions

2011-05-19  来源:本站原创  分类:Database  人气:54 

Stored procedure consists of three parts, the definition part, the operative part, prior to use exception handling part of the execution set serveroutput on;
1, the most simple stored procedure

begin
                null;
        end;
          /

Or

begin
                DBMS_OUTPUT.PUT_LINE('This is First Prccedure!');
        end;
        /

2, with a defined part of the stored procedure (v_str is to define a variable of type string)

declare
          v_str varchar2(100) :='This is second procedure!';
        begin
          DBMS_OUTPUT.PUT_LINE(v_str);
        end;
        /

or

declare
                v_str varchar2(100) ;
        begin
                SELECT 'This is second procedure!' INTO v_str FROM DUAL;
                DBMS_OUTPUT.PUT_LINE(v_str);
        end;
        /

3, with the exception handling part of the stored procedure

Declare
                v_name varchar(2);
        Begin
                SELECT sname INTO v_name FROM student WHERE sid=&sid;
        Exception
                when NO_DATA_FOUND then
                DBMS_OUTPUT.PUT_LINE(' Please enter the correct SID!');
         End;
        /

4, but with the name of a stored procedure without parameters

create or replace procedure first_pro
is
begin
   DBMS_OUTPUT.PUT_LINE(' With names but without parameters stored procedure !');
end;
 Execute a stored procedure
Exec first_pro();
call first_pro();

5, with the name and parameters, and came out with a parameterized stored procedure

create or replace procedure second_pro(p_sid in number, p_sname out varchar2,p_cid in out number)
is
begin
   select sname, c_id into p_sname,p_cid from student where sid=p_sid;
end;

Call the stored procedure

declare
  v_name varchar2(20);
  v_cid number;
begin
  second_pro(2,v_name,v_cid);
  dbms_output.put_line('v_name='||v_name);
  dbms_output.put_line('v_cid='||v_cid);
end;

6, there were no reports of arguments passed to the stored procedure only

create or replace procedure third_pro(p_sid in number,p_sname varchar2)
is
begin
      update student set sname = p_sname where sid=p_sid;
End;
 Call the stored procedure
Exec third_pro(100,'Mary');
Call third_pro(100,'Lily');

7, a function return value

create or replace function first_fun(p_sid number)
return varchar2
is v_name varchar2(20);
begin
   select sname into v_sname from student where sid=p_sid;
   return v_sname;
end;
 Call
Select first_fun(100) from dual;

8, multiple return values

create or replace function first_fun(p_sid number,p_sname out varchar2)
return number
is v_cid number;
begin
   select sname,c_id into p_sname,v_cid from student where sid=p_sid;
   return v_cid;
end;
 Call
declare
        v_name varchar2(20);
        v_cid number;
begin
        v_cid:=first_fun(100,v_name);
        dbms_output.put_line('v_name='||v_name);
        dbms_output.put_line('v_cid='||v_cid);
end;
相关文章