Home » SQL & PL/SQL » SQL & PL/SQL » How to convert simple SQL into Stored Procedures? (PL/SQL)
How to convert simple SQL into Stored Procedures? [message #422589] Thu, 17 September 2009 09:57 Go to next message
wajidjafri
Messages: 6
Registered: September 2009
Junior Member
Hi,

How can I convert following SELECT statement into a Stored Procedure?

SELECT a.empno, b.deptno
FROM emp a, dept b
WHERE a.deptno=b.deptno;

Thanking in advance.

Wajid

Re: How to convert simple SQL into Stored Procedures? [message #422590 is a reply to message #422589] Thu, 17 September 2009 10:07 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
What have you tried? What are you actually trying to do?
Re: How to convert simple SQL into Stored Procedures? [message #422619 is a reply to message #422590] Thu, 17 September 2009 23:03 Go to previous messageGo to next message
Ora_works
Messages: 6
Registered: April 2008
Junior Member
If your question is how to use "Select" statement in a procedure,

then you need to use "select col1, col2 into v_col1, v_vol2 from table where condition"
Re: How to convert simple SQL into Stored Procedures? [message #422632 is a reply to message #422589] Fri, 18 September 2009 00:30 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
why do you want that?
Re: How to convert simple SQL into Stored Procedures? [message #422712 is a reply to message #422619] Fri, 18 September 2009 08:30 Go to previous messageGo to next message
wajidjafri
Messages: 6
Registered: September 2009
Junior Member
Hi,

I tried using reference cursor but I did not get output. I mean in SQL Plus I did not see query return.

Using COLNAME INTO VARIABLE method, I need to declare all variables and then use DBMS_OUTPUT. I want to avoid that as my queries are huge and many in number.

Why I want to do that:
I want to convert all SQL statements into stored procedures for performance reasons. It will improve my parse to execute ratio. Queries will run faster as PLSQL will keep cursors open implicitly.

I appreciate you guys assistanct in this matter. Thanks.

Wajid

I tried following but it didn't work:
CREATE OR REPLACE PROCEDURE CMDB.page1_proc (EMPCURSOR OUT T_CURSOR)
IS
TYPE T_CURSOR IS REF CURSOR;
V_CURSOR1 T_CURSOR;
V_CURSOR2 T_CURSOR;
BEGIN
OPEN V_CURSOR1 FOR sELECT a.HOSTNAME, a.SYSTEM_CONFIG, a.MEMORY_SIZE_IN_MB, a.LOCAL_DISK_SPACE_IN_GB, a.CPU_COUNT, b.VENDOR_NAME, b.BASE_VERSION, b.UPDATE_LEVEL, b.MAX_SWAP_SPACE_IN_MB, a.DOMAIN
FROM ECM$HIST_HARDWARE@WGRIDP_LNK A, MGMT$OS_SUMMARY@WGRIDP_LNK B
WHERE A.HOSTNAME =b.host;
OPEN V_CURSOR2 FOR SELECT * FROM DEPT;
EMPCURSOR := V_CURSOR1;
DEPTCURSOR := V_CURSOR2;
END page1_proc;
END CURSPKG;
/
Re: How to convert simple SQL into Stored Procedures? [message #422717 is a reply to message #422712] Fri, 18 September 2009 08:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
I want to convert all SQL statements into stored procedures for performance reasons

Doing your processing row by row from a cursor rather than all at once from a piece of SQL will indeed affect your performance - it will make things much slower.

Quote:
It will improve my parse to execute ratio.

No it won't.
An SQL Query that uses bind variables will be kept in the shared pool and have it's plan reused in exactly the same way that one used in Pl/SQl will

Quote:
Queries will run faster as PLSQL will keep cursors open implicitly.

If the cursor is kept open, then you can't start fetching rows from the start of it again.
I'm not quite sure what you're trying to say here.
Re: How to convert simple SQL into Stored Procedures? [message #422721 is a reply to message #422712] Fri, 18 September 2009 08:59 Go to previous messageGo to next message
wajidjafri
Messages: 6
Registered: September 2009
Junior Member
Hi Jrowbottom:

Thanks for your input. I was following a suggestion from an Oracle Guru, Tom Kyte. Author of asktom.oracle.com. He wrote this in current Oracle magazine.

Tom Kyte writes:

"My preferred way to improve your execute-to-parse ratio, however, is to move all the SQL out of the client application and into stored procedures. PL/SQL is a statement caching machine--it has, from its very beginning, kept a cache of cursors open for us. When you say "close this cursor" in PL/SQL, PL/SQL tells you, "OK, it's closed," but it really isn't. The PL/SQL engine smartly keeps the cursor open, knowing that you are just going to call that stored procedure and execute that SQL again."

For details on this artical about 'Execute to Parse', kindly check out September 2009 Oracle Magazine - Page 80.

Wajid
Re: How to convert simple SQL into Stored Procedures? [message #422724 is a reply to message #422721] Fri, 18 September 2009 09:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What he is talking about is (I guess, not having the article to hand) the difference between SQL using hardcoded values, and Sql using bind variables - all Pl/Sql variable references use bind variables, making it much easier to write performant pl/sql.

In most client applications, you can still do this - it is just sometimes harder to do, and easier to do badly.

Without seeing the article, it's hard to put the second part in context - the SQL execution plan stays in the shared pool after the cursor is closed, and the blocks read in are still in the buffer cache, but the query itself will have to be re-executed and the data re-read (either from cache or disk) when you re-open a cursor - otherwise you'd never spot any changes to the data made by other people.

Given the problems that you are having writing basic pl/sql, I would hesitate to recommend this course for you until you are a little more experienced.
Re: How to convert simple SQL into Stored Procedures? [message #422753 is a reply to message #422724] Fri, 18 September 2009 12:33 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Here you go...

http://www.oracle.com/technology/oramag/oracle/09-sep/o59asktom.html
Re: How to convert simple SQL into Stored Procedures? [message #422754 is a reply to message #422753] Fri, 18 September 2009 13:07 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yep. the way I thought. Not the blind "take SQL, make procedure", but the "take complex application logic and move it from the client code to PL/SQL code", basically.

Which is pretty much an (at least partial) re-write of the application.

Previous Topic: BLOB records deletion
Next Topic: How to replace Row_number and generate the same result set
Goto Forum:
  


Current Time: Fri Dec 09 13:49:37 CST 2016

Total time taken to generate the page: 0.07068 seconds