This content has been marked as final. Show 3 replies
946207 wrote:PL/SQL is
there will be a way with which i can see all the data(select * from test_sh) as the output of the plsql procedure
a) nothing at all like T-SQL
b) two different languages, PL (Programming Logic) and SQL
SQL is used to access data in the Oracle database. So select * from test_sh is the correct statement to access the data.
The client can make execute this SQL statement itself. And view the data. However, this requires the client to understand the SQL language, and the database model. Which is typically not that you want. You want to abstract that from the client.
Why would you want the client to deal with the complexities of the SQL language? Or deal with the complexities of your database model in Oracle?
So instead of the client doing SQL, PL/SQL can do it on behalf of the client. The client uses PL/SQL as an API - in a similar fashion as the client would use any other API.
The PL/SQL code determines the SQL statement - and returns a SQL cursor handle to the client. The client then simply uses the SQL cursor that the PL/SQL code created for it (and the client needs to close the SQL cursor when done, so as to not cause server resource leakage).
E.g. (using PL/SQL as an API to abstract SQL and the data model)
The client then calls TestProc and gets a reference cursor handle. E.g. (the client call to Oracle):
create or replace procedure TestProc( c out sys_refcursor ) is begin open c for select * from test_sh order by 1; end;
The client needs to supply a host (client language) bind variable of type cursor, as value for bind variable +:1+.
begin TestProc( c => :1 ); end;
This is the correct way to deal with PL/SQL and cursors from a client GUI/language perspective. Any other method, like using PL/SQL to cache SQL data as a collection, is usually very wrong.