Stored procedure syntax in oracle

Ken H

14y7.2kOracle

hi all,

my stored procedure original SQL Server syntax is as follows:

--insert data

CREATE PROC AddTranData

@Tran_Num nchar(20),

@Start_STATION NCHAR(20),

@END_STATION NCHAR(20),

@PIECE INT,

@CONSIGNEE NCHAR(20),

@SHIPPING_ADDRESS NCHAR(50),

@EXPLAINED NCHAR(30)

INSERT INTO TRANSPORT_INFO

VALUES(@TRAN_NUM,@START_STATION,@END_STATION,@PIECE,@CONSIGNEE,@SHIPPING_ADDRESS,@EXPLAINED)

--updated data

CREATE PROC UpdateTranData

@Tran_Num nchar(20),

@Start_STATION NCHAR(20),

@END_STATION NCHAR(20),

@PIECE INT,

@CONSIGNEE NCHAR(20),

@SHIPPING_ADDRESS NCHAR(50),

@EXPLAINED NCHAR(30)

UPDATE TRANSPORT_INFO SETPIECE=@PIECE,CONSIGNEE=@CONSIGNEE,SHIPPING_ADDRESS=@SHIPPING_ADDRESS,

TRAN_NUM=@TRAN_NUM

Now, I want to change it to Stored procedure syntax in oracle.

thanks.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Senthilkumar14y ago
Accepted answer
Hi,

Its very similar in the syntax format.

You can see this example

Procedure for SELECT:

http://www.sqlinfo.net/oracle/oracle_stored_procedure_SELECT.php

Procedure for UPDATE:

http://www.sqlinfo.net/oracle/oracle_stored_procedure_UPDATE.php
pundaleek channal14y ago
CREATE OR REPLACE
PROCEDURE AddTranData(
    t_TRAN_NUM         IN TRANSPORT_INFO.TRAN_NUM%type,
    t_Start_STATION    IN TRANSPORT_INFO.START_STATION%type,
    t_END_STATION      IN TRANSPORT_INFO.END_STATION%type,
    t_PIECE            IN TRANSPORT_INFO.PIECE%type,
    t_CONSIGNEE        IN TRANSPORT_INFO.CONSIGNEE%type,
    t_SHIPPING_ADDRESS IN TRANSPORT_INFO.SHIPPING_ADDRESS%type,
    t_EXPLAINED        IN TRANSPORT_INFO.EXPLAINED%type )
IS
BEGIN
INSERT
INTO TRANSPORT_INFO
    (
      TRAN_NUM,
      START_STATION,
      END_STATION,
      PIECE,
      CONSIGNEE,
      SHIPPING_ADDRESS,
      EXPLAINED
    )
    VALUES
    (
      t_TRAN_NUM,
      t_START_STATION,
      t_END_STATION,
      t_PIECE,
      t_CONSIGNEE,
      t_SHIPPING_ADDRESS,
      t_EXPLAINED
    );
END AddTranData;
CREATE OR REPLACE
PROCEDURE UpdateTranData(
    t_TRAN_NUM         IN TRANSPORT_INFO.TRAN_NUM%type,
    t_Start_STATION    IN TRANSPORT_INFO.START_STATION%type,
    t_END_STATION      IN TRANSPORT_INFO.END_STATION%type,
    t_PIECE            IN TRANSPORT_INFO.PIECE%type,
    t_CONSIGNEE        IN TRANSPORT_INFO.CONSIGNEE%type,
    t_SHIPPING_ADDRESS IN TRANSPORT_INFO.SHIPPING_ADDRESS%type,
    t_EXPLAINED        IN TRANSPORT_INFO.EXPLAINED%type )
IS
BEGIN
UPDATE TRANSPORT_INFO
SET TRAN_NUM      = t_TRAN_NUM,
    START_STATION   =t_START_STATION,
    END_STATION     =t_END_STATION,
    PIECE           =t_PIECE,
    CONSIGNEE       =t_CONSIGNEE,
    SHIPPING_ADDRESS=t_SHIPPING_ADDRESS,
    EXPLAINED       =t_EXPLAINED;
/*add where condition as per ur logic*/
END UpdateTranData;
Ken H14y ago
CREATE OR REPLACE PROCEDURE AddTranData
(
t_TRAN_NUM IN TRANSPORT_INFO.TRAN_NUM,
t_Start_STATION OUT TRANSPORT_INFO.START_STATION,
t_END_STATION OUT TRANSPORT_INFO.END_STATION,
t_PIECE OUT TRANSPORT_INFO.PIECE,
t_CONSIGNEE OUT TRANSPORT_INFO.CONSIGNEE,
t_SHIPPING_ADDRESS OUT TRANSPORT_INFO.SHIPPING_ADDRESS,
t_EXPLAINED OUT TRANSPORT_INFO.EXPLAINED
)
AS
BEGIN
SELECT
START_STATION,
END_STATION,
PIECE,
CONSIGNEE,
SHIPPING_ADDRESS,
EXPLAINED
INTO
t_START_STATION,
t_END_STATION,
t_PIECE,
t_CONSIGNEE,
t_SHIPPING_ADDRESS,
t_EXPLAINED
FROM TRANSPORT_INFO
WHERE TRAN_NUM=t_TRAN_NUM;
END AddTranData;

but,it error:
Error(4,15): PLS-00488: 'TRANSPORT_INFO.TRAN_NUM' Must be a type

why?
TRAN_NUM is primary key.
thanks