5 Replies Latest reply on Mar 9, 2011 7:30 AM by Hemant K Chitale

    what is  Mian Difference between Sql and Plsql

      Hi all,

      Can any one please tell me,why we have to go for plsql.all though we have sql.
      what is main advantages of pl/sql.please guide me...

        • 1. Re: what is  Mian Difference between Sql and Plsql
          Ganesh Srivatsav
          Please read this,


          • 2. Re: what is  Mian Difference between Sql and Plsql
            Main difference:
            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.

            • 3. Re: what is  Mian Difference between Sql and Plsql
              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:
              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;
               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          );
               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 );
               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 );
               27          --// we need to execute the cursor and fetch
               28          --// the row
               29          r := DBMS_SQL.execute_and_fetch( cur );
               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 );
               36          --// we have found what we're looking for (unlike Bono)
               37          --// and can now close the cursor
               38          DBMS_SQL.close_cursor( cur );
               40          --// the name that we found is..
               41          DBMS_OUTPUT.put_line( 'Employee ID '||empID||' is '||empName );
               42  end;
               43  /
              Procedure created.
              SQL> exec NoSQLIntegration( 7698 );
              Employee ID 7698 is BLAKE
              PL/SQL procedure successfully completed.
              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?

              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.
              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;
                9          DBMS_OUTPUT.put_line( 'Employee ID '||empID||' is '||empName );
               10  end;
               11  /
              Procedure created.
              SQL> exec SQLIntegration( 7698 );
              Employee ID 7698 is BLAKE
              PL/SQL procedure successfully completed.
              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.

              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.
              • 4. Re: what is  Mian Difference between Sql and Plsql
                Hi Friend,

                Thanks alot,for your good information.

                • 5. Re: what is  Mian Difference between Sql and Plsql
                  Hemant K Chitale
                  (I) LIKE THIS ! (I) DIGGIT ! DEL.ICIO.US ! and whatever else

                  Hemant K Chitale