SQL AND ORACLE PROCEDURE, TRIGGER AND FUNCTIONS DIFFERENCE

PROCEDURE:
===========

SQL:
------
CREATE OR ALTER PROCEDURE PROC_AVG_EMP_SAL (@IN_DEPTNO INT, @O_AVG_SAL INT)
AS
BEGIN
SELECT @O_AVG_SAL = AVG(SAL) FROM EMP WHERE DEPTNO=@IN_DEPTNO GROUP BY DEPTNO;
END;

ORACLE:
-------------
CREATE OR REPLACE PROCEDURE PROC_AVG_EMP_SAL (IN_DEPTNO IN INT, O_AVG_SAL OUT INT)
AS
BEGIN
SELECT AVG(SAL) INTO O_AVG_SAL FROM EMP WHERE DEPTNO=IN_DEPTNO GROUP BY DEPTNO;
END;
/

Note:
1) There is a difference in defining variable between SQL and Oracle.
2) There is a difference in assigning aggregations to variable between SQL and Oracle.

FUNCTION:-
=========

SQL:
------
CREATE OR ALTER FUNCTION FUN_AVG_SAL (@IN_DEPTNO INT)
RETURNS INT
AS
BEGIN
DECLARE @O_AVG_SAL INT;
SELECT @O_AVG_SAL = AVG(SAL)
FROM EMP
WHERE DEPTNO = @IN_DEPTNO
GROUP BY DEPTNO;
RETURN @O_AVG_SAL;
END;
GO

ORACLE:
-------------
CREATE OR REPLACE FUNCTION FUN_AVG_SAL (IN_DEPTNO IN NUMBER)
RETURN NUMBER
IS
O_AVG_SAL NUMBER;
BEGIN
SELECT AVG(SAL)
INTO O_AVG_SAL
FROM EMP
WHERE DEPTNO = IN_DEPTNO
GROUP BY DEPTNO;
RETURN O_AVG_SAL;
END;
/

NOTE:
1) SQL doesn't used 'IS' it used 'AS'

TRIGGER:
========

SQL:
-------
CREATE OR ALTER TRIGGER INSERT_TRIGGER
ON EMP
AFTER INSERT
AS
BEGIN
INSERT INTO TRIGGER_TABLE (EMPNO, ENAME, SAL, DEPTNO)
SELECT EMPNO, ENAME, SAL, DEPTNO FROM inserted;
END;
GO

ORACLE:
-------------
CREATE OR REPLACE TRIGGER INSERT_TRIGGER
BEFORE INSERT ON EMP
FOR EACH ROW
BEGIN
INSERT INTO TRIGGER_TABLE (EMPNO,ENAME,SAL,DEPTNO)
VALUES(:OLD.EMPNO,:OLD.ENAME,:OLD.SAL)
END;
/

NOTE:
1) SQL Server does not support BEFORE triggers