This content has been marked as final. Show 5 replies
SQL = Structured Query Language = database access.
PL/SQL is Oracle's Procedural Language extension to SQL.
Calling SQL from PL/SQL is fine.
Calling PL/SQL from SQL should be avoided usually.
PL/SQL is 2 languages.
It is the PL (Programming Logic) language, based on Ada (close relative of Pascal). And this language is tightly integrated with SQL.
In Java and other languages, how do you make a SQL call? You need to pass the SQL statement as a string variable to the local interface (usually a class method). This code then creates the SQL statement handle for parsing and executes the SQL text in the string.
You have no idea whether that SQL text string is valid - until the time it is executed. Java (or C# or whatever) does not syntax check SQL text string for you. It does not automatically bind your program variables to the SQL variables inside that SQL text string.
So this is the basic approach that is used by Java, C#, C/C++, Delphi, Visual Basic and others - as they are not integrated with the actual SQL engine:
So you can see that this is a lot of code to write to manipulate and deal with the SQL cursor. And this approach does not tell us whether the SQL string itself is valid. Does the table exist? Are the columns correctly named?
SQL> create or replace procedure NoSQLIntegration( empID number ) is 2 cur number; 3 empName varchar2(10); 4 r number; 5 begin 6 --// we call our SQL interface and tell it want to create 7 --// a cursor 8 cur := DBMS_SQL.open_cursor; 9 10 --// we pass the SQL text to parse as the cursor 11 DBMS_SQL.parse( 12 cur, 13 'select ename from emp where empno = :0', 14 DBMS_SQL.native 15 ); 16 17 --// we need to bind the variable to tell SQL 18 --// exactly which employee id we want 19 DBMS_SQL.Bind_Variable( cur, ':0', empID ); 20 21 --// we need to define the structure to receive the 22 --// SQL column(s) projected - our cursor projects a single 23 --// column ENAME and that will be fetched using a string 24 --// variable of 10 bytes in size 25 DBMS_SQL.define_column( cur, 1, empName, 10 ); 26 27 --// we need to execute the cursor and fetch 28 --// the row 29 r := DBMS_SQL.execute_and_fetch( cur ); 30 31 --// we process the fetched row - by retrieving the 1st 32 --// column from it as a string and storing this in 33 --// our empName string variable 34 DBMS_SQL.column_value( cur, 1, empName ); 35 36 --// we have found what we're looking for (unlike Bono) 37 --// and can now close the cursor 38 DBMS_SQL.close_cursor( cur ); 39 40 --// the name that we found is.. 41 DBMS_OUTPUT.put_line( 'Employee ID '||empID||' is '||empName ); 42 end; 43 / Procedure created. SQL> SQL> exec NoSQLIntegration( 7698 ); Employee ID 7698 is BLAKE PL/SQL procedure successfully completed. SQL>
PL/SQL integrates the PL and SQL languages so that it looks like a single language. You can code native SQL inside PL. The PL/SQL engine is clever enough to determine what is PL and what is SQL and how to create the glue to integrated these too. Using PL/SQL's integration, we can now code SQL directly inside PL/SQL.
So - our SQL statement is now parsed with the PL/SQL code. We know whether the SQL statement is valid or not - as it is parsed. We do not need to manually bind variables into the SQL statement. We use our PL/SQL variables in it. We do not need to prep the fetch structure by defining the columns. The PL/SQL engine does all that for us behind the scenes and transparently.
SQL> create or replace procedure SQLIntegration( empID number ) is 2 empName varchar2(10); 3 begin 4 select 5 ename into empName 6 from emp 7 where empno = empID; 8 9 DBMS_OUTPUT.put_line( 'Employee ID '||empID||' is '||empName ); 10 end; 11 / Procedure created. SQL> SQL> exec SQLIntegration( 7698 ); Employee ID 7698 is BLAKE PL/SQL procedure successfully completed. SQL>
This PL/SQL example uses an implicit cursor. We also have explicit cursor definitions in PL/SQL. We can also use SQL in PL/SQL as Java would use it - as the example using DBMS_SQL above shows.
So in PL/SQL we not only have tight integration, we have a number of ways to use the SQL the language. We can use it as implicit cursors, explicit cursors and even via DBMS_SQL (a very powerful interface when dealing with the dynamic side of SQL processing).
So the bottom line is. One does not compare PL/SQL with SQL. They are not in competition with one another. SQL is the language to use to crunch and process SQL data. The PL part provides us with a formal procedural and declarative language to do the things that we need to do that the SQL engine does not. The PL part supports procedural programming, supports object orientation and provides us with the standard set of 3GL features we need to design and write very sophisticated and complex applications - but inside Oracle.
So it is not PL/SQL versus SQL. It is PL/SQL versus Java and versus C# and so on. And PL/SQL is better than any of these languages, because it has this tight integration with the SQL language. And not only that, it also provides better performance (the PL/SQL engine and SQL engine are not separated via a network, but reside in the same physical process). It also provides better scalability as it runs inside a very scalable environment - the Oracle database instance.