Sql-server – How to Determine the input and output values of stored procedures

Officially, PostgreSQL only has "functions". Trigger functions are sometimes referred to as "trigger procedures", but that usage has no distinct meaning. Internally, functions are sometimes referred to as procedures, such as in the system catalog pg_proc. That's a holdover from PostQUEL. Any features that some people (possibly with experience in different database systems) might associate with procedures, such as their relevance to preventing SQL injections or the use of output parameters, also apply to functions as they exist in PostgreSQL.

Now, when people in the PostgreSQL community talk about "stored procedures" or "real stored procedures", however, they often mean a hypothetical feature of a function-like object that can start and stop transactions in its body, something that current functions cannot do. The use of the term "stored procedure" in this context appears to be by analogy to other database products. See this mailing list thread for a vague idea.

In practice, however, this distinction of function versus procedure in terms of their transaction-controlling capabilities is not universally accepted, and certainly many programmers without database bias will take a Pascal-like interpretation of a procedure as a function without return value. (The SQL standard appears to take a middle ground, in that a procedure by default has a different transaction behavior than a function, but this can be adjusted per object.) So in any case, and especially when looking at questions on Stack Exchange with a very mixed audience, you should avoid assuming too much and use clearer terms or define the properties that you expect.

Stored Procedures and External Procedure are distinct ones.

Stored procedures are stored in database and used from there. You can write them in different languages. Also note that you can pre-compile PL/SQL instead of interpreting

• SQL/PL-SQL , most common
• Java
• .NET only in windows environment.

External Procedures are external functions/procedures in Operating System. You write a stored procedure in PL/SQL, use call specification to call other languages in operating System. This potentially gives you power to call any function in other languages. Following usages come to my mind.

• Use a Data Mining algorithm which is not implemented in Oracle Data Mining
• Use ftp function to transfer files to another server
• Use a graphic functions of Gnu Plot to create graphics and store them in database for later use.

These usages in my opinion are extreme and unnecessary. All of them can be accomplished with application programs. Nowadays nobody uses database as it is. There are always applications built using Java, .NET, PHP etc. Whenever possible, these other languages should be used in application.