1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

simple procedure execution

Discussion in 'SQL PL/SQL' started by krithika@2001, Jul 6, 2009.

  1. krithika@2001

    krithika@2001 Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    I want to write a simple procedure that takes employee number as input and return empname and job

    create procedure emp_procedure(empno in number,empname out varchar2,job out varchar2)
    is
    begin
    select ename into empname,job into job from emp where eno=empno;
    end emp_procedure;

    Can anybody tell me how to execute the procedure [/B]in SQL * PLUS


    tHANKS
    KRITHIKA
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    :)

    You are kidding? nopes?

    ok first type this

    Code (Text):
    set serveroutput on
    and then use the execute command like this

    Btw don't forget a trailing / at the end of that create procedure code. And as another suggestion, you should be reading a beginner's sql book. :)
     
  3. krithika@2001

    krithika@2001 Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    Hey i am sorry if i had asked something stupid.But still i am not convinced.I have to return empname and job when i pass enumber.So i shud pass parameters to the procedure.How can i return them?



    regards
    Krithika
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    There were few mistakes in your piece code . Find then from the below code.
    There are basicallty two ways of doing it using Procedures.

    Code (SQL):

    SQL> DESC EMP
     Name                                      NULL?    TYPE
     ----------------------------------------- -------- ----------------------------

     EMPNO                                     NOT NULL NUMBER(4)
     ENAME                                              VARCHAR2(10)
     JOB                                                VARCHAR2(9)
     MGR                                                NUMBER(4)
     HIREDATE                                           DATE
     SAL                                                NUMBER(7,2)
     COMM                                               NUMBER(7,2)
     DEPTNO                                             NUMBER(2)

    SQL> CREATE OR REPLACE PROCEDURE emp_procedure1(emno IN NUMBER,emname OUT varcha
    r2,emjob OUT varchar2)
      2  IS
      3  BEGIN
      4   SELECT ename,job INTO emname,emjob FROM emp WHERE empno=emno;
      5   dbms_output.put_line('Inside emp_procedure1 ......' );
      6   dbms_output.put_line('NAME = '|| emname||' ; JOB = '||emjob );
      7  END emp_procedure1;
      8  /

    PROCEDURE created.

    SQL> CREATE OR REPLACE PROCEDURE emp_procedure2(emno IN NUMBER)
      2  IS
      3  emname varchar2(30);
      4  emjob  varchar2(30);
      5  BEGIN
      6  SELECT ename,job INTO emname,emjob FROM emp WHERE empno=emno;
      7  dbms_output.put_line('Inside emp_procedure2 ......' );
      8  dbms_output.put_line('NAME = '|| emname||' ; JOB = '||emjob );
      9  END emp_procedure2;
     10  /

    PROCEDURE created.

    SQL> SET SERVEROUTPUT ON
    SQL> DECLARE
      2   ename varchar2(30);
      3   ejob  varchar2(10);
      4  BEGIN
      5   -- Calling emp_procedure1 (Will return the o/p to OUT variable)
      6   emp_procedure1(7499,ename,ejob);
      7   dbms_output.put_line('Outside emp_procedure1 ......' );
      8   dbms_output.put_line('NAME = '|| ename||' ; JOB = '||ejob );
      9   -- Calling emp_procedure2 (Will not return the o/p )
     10   emp_procedure2(7499);
     11  END;
     12  /
    Inside emp_procedure1 ......
    NAME = ALLEN ; JOB = SALESMAN
    Outside emp_procedure1 ......
    NAME = ALLEN ; JOB = SALESMAN
    Inside emp_procedure2 ......
    NAME = ALLEN ; JOB = SALESMAN

    PL/SQL PROCEDURE successfully completed.
     
    There is another way of doing it in SQL*PLUS

    Code (SQL):

    SQL> EXECUTE  emp_procedure2(7499);
    Inside emp_procedure2 ......
    NAME = ALLEN ; JOB = SALESMAN

    PL/SQL PROCEDURE successfully completed.

    SQL>
     
     
  5. krithika@2001

    krithika@2001 Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    Thankssssss aaaaaaaaaaaa toooonnnnnnnnnnnnn.I am so happy.