Microsoft® SQL Server™ does not support the use of user-defined PL/SQL functions that can be called from SQL statements. This functionality can often be achieved in other ways.
In the following example, the user-defined function get_sum_major is used to obtain a sum of tuition paid by major. It can be replaced by using a query as a table.
| Oracle | SQL Server |
|---|---|
| SELECT ssn, fname, lname, tuition_paid, tuition_paid/get_sum_major(major) as percent_major FROM student_admin.student |
SELECT ssn, fname, lname, tuition_paid, tuition_paid/sum_major as percent_major FROM student_admin.student, (SELECT major, sum(tuition_paid) sum_major FROM student_admin.student GROUP BY major) sum_student WHERE student.major = sum_student.major |
| CREATE OR REPLACE FUNCTION get_sum_major (inmajor varchar2) RETURN NUMBER AS sum_paid number; BEGIN SELECT sum(tuition_paid) into sum_paid FROM student_admin.student WHERE major = inmajor; RETURN(sum_paid); END get_sum_major; |
No CREATE FUNCTION syntax is required; use CREATE PROCEDURE syntax. |