Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Question Unanswered: About storedprocedure, urgent!

    I am a beginner Oracle, and i uesed to MS SQL Server.
    So i dont know how to write and ues the storedprocedure in oracle !

    Would anyone show me a example code (has parameters)?

  2. #2
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    Guessing that you're using Oracle 8i....

    http://download-east.oracle.com/docs.../doc/index.htm

  3. #3
    Join Date
    Mar 2004
    Posts
    9

    Re: About storedprocedure, urgent!

    Originally posted by JamesL
    I am a beginner Oracle, and i uesed to MS SQL Server.
    So i dont know how to write and ues the storedprocedure in oracle !

    Would anyone show me a example code (has parameters)?
    Hi!

    CREATE OR REPLACE procedure proc_1(param1 in integer,param_out out varchar2)
    as
    cnt integer;
    begin
    select count(*)
    into cnt
    from block_ip where active=param1;
    if cnt>1 then
    param_out := 'More than once. Count= '||cnt;
    else
    param_out := 'Once or Zero.Count= '||cnt;
    end if;
    end;
    /


    To check how function works:

    declare
    my_var varchar2(50);
    begin
    proc_1(1,my_var);
    dbms_output.PUT_LINE(my_var);
    end;
    /

  4. #4
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    CREATE PROCEDURE raiseanddelete(
    p_raise_empid IN VARCHAR2,
    p_delete_empid IN OUT VARCHAR2
    ) AS
    my_employee_rec employee%ROWTYPE;
    BEGIN
    SELECT *
    INTO my_employee_rec
    FROM employee
    WHERE empid = p_raise_empid;

    INSERT INTO process_emp_raise (empid, lastname, firstname, salary)
    VALUES (p_raise_empid, my_employee_rec.lastname,
    my_employee_rec.firstname, my_employee_rec.salary+10000);

    DELETE FROM employee
    WHERE empid = p_delete_empid;

    p_delete_empid := 00000;
    END;
    SATHISH .

  5. #5
    Join Date
    Feb 2004
    Posts
    5

    thanks

    but i execute one sql in pl/sql a SQL window:
    "mms.p_ad_insert('test', 'none', 'test', 'none', sysdate, 1)",
    p_ad_insert is a procedure of mine, always prompt : ORA-00900 Invalid sql statement!

    why? thanks

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Post your proc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •