advantages of storing SQL in stored procs

advantages of storing SQL in stored procs

Post by NetComrad » Sun, 31 Dec 1899 09:00:00



I ask/make my developers to write SQL using bind vars, are there any
clear advantages in doing it in PL/SQL, performance wise?

Thanx.

 
 
 

advantages of storing SQL in stored procs

Post by Dogan Cibicel » Wed, 23 Aug 2000 09:12:23


since you use the same sql, parsing done is decreased to a minimum... I
guess... Well maybe not... or....


Quote:> I ask/make my developers to write SQL using bind vars, are there any
> clear advantages in doing it in PL/SQL, performance wise?

> Thanx.


 
 
 

advantages of storing SQL in stored procs

Post by Connor McDonal » Sun, 31 Dec 1899 09:00:00



> I ask/make my developers to write SQL using bind vars, are there any
> clear advantages in doing it in PL/SQL, performance wise?

> Thanx.

Using pl/sql should improve the likelihood of sharing common SQL's (thus
lowering your parsing)...

In reality, this is more dependant on the discipline you can install on
your developers...I've seen many places where they have 37 versions of
what is basically the same SQL, all in a single "shared code" library..

HTH

--
===========================================
Connor McDonald
http://www.veryComputer.com/

We are born *, wet and hungry...then things get worse

 
 
 

1. Stored Outlines For Queries In PL/SQL Stored Procs

Hi,

Oracle 8.1.7.3
SunOS 5.7

I would like to know how to create stored outlines for some queries I
have in stored procedures. The queries may be explicit or implicit.
For example:

DECLARE
  TYPE VectorId_Type IS TABLE OF
   cii_array_vector.vector_id%TYPE INDEX BY NATURAL;
  var_VectorIds VectorId_Type;
  varcur_datapoint cur_datapoint%ROWTYPE;

  CURSOR cur_datapoint IS
  SELECT b.*
  FROM ciiw_array_vector a, datapoint b
  WHERE a.array_id = ArrayId_in
  AND a.vector_id = b.vector_id
  AND b.version = pdII.cWorkingVersion
  AND a.vector_id NOT IN (SELECT vector_id FROM matrix_vector);

BEGIN
  FOR varcur_datapoint IN cur_datapoint LOOP
    ...
  END LOOP;

  SELECT ciiw_array_vector.vector_id
  BULK COLLECT INTO var_VectorIds
  FROM ciiw_array_vector, vector
  WHERE ciiw_array_vector.array_id = ArrayId_in
  AND ciiw_array_vector.vector_id = vector.vector_id
  AND vector.version = pdII.cWorkingVersion;
END;

Thanks,
Salaam

2. Mystery database space reclaimation!!

3. storing/accessing binary data with stored procs.

4. Phantom errors with a DOS based application

5. Error calling stored procs from within stored proc

6. IIS and SQL licenses query

7. Debugging Java Stored Procs in Stored Procedure Builder

8. Q: Generate Reports in SQL Server

9. ADO, Parameters and SQL Strings (not stored procs) in SQL Server

10. Calling SQL server stored procs by access report

11. The BDE and MS-SQL Extended Stored Procs

12. Distributing SQL Server programs (stored procs)?