WRITING PROCEDURE Teradata DataBase
|
|
|
Question / Problem
|
The problem is while writing the procedures .The procedure syntax is diffrent from ORACLE and TeraData. so I ahhve give here one example .How to write a Procedure in Teradata database.
|
Solution
|
|
CREATE PROCEDURE [DATABSE_NAME].[PROCEDURE Name]
PROCEDURE [DATABSE_NAME].[PROCEDURE Name]( IN {Parameter_name} {DATA TYPE OF parameter }, OUT [output paramaeter name ]{DataType OF parameter name))
BEGIN
[ DECLARE a INTEGER; ---Decalring variable name
{ SELECT |UPDATE|DELETE|INSERT} -----SQL Statements
END;
EXAMPLE:
CREATE PROCEDURE EMPLOYEE_db.EMP_PROC
( IN p_empno INTEGER , OUT p_empname VARCHAR(20),OUT p_sal DECIMAL(5,0))
BEGIN
SELECT EMPNAME ,SAL INTO :p_empname,p_sal FROM EMPLOYEE_db.EMP
WHERE EMPNO=:p_empno;
END;
Executing a procedure:
CALL B16909_db.EMP_PROC(1002,p_empname,p_sal);
---Output:
p_empname p_sal
RAMU 10790
----------------------The same Procedure we can write in Oracle like following this-------
CREATE OR REPLACE PROCEDURE V_RETRIEVE(V_EMPNO IN NUMBER,
OR REPLACE PROCEDURE V_RETRIEVE(V_EMPNO IN NUMBER, V_SAL OUT NUMBER(5,0),
OUT NUMBER(5,0), V_NAME OUT VARCHAR2)
OUT VARCHAR2)IS
BEGIN
SELECT ENAME, JOB INTO V_NAME, V_JOB
FROM EMP WHERE EMPNO=V_EMPNO;
END;
;DESC PROCEDURE V_RETRIEVE;
PROCEDURE V_RETRIEVE;
DECLARE
EMPNAME VARCHAR2(
|
Applies to |
|
Oracle 9i Sql
|
Rank It |
|
