This content has been marked as final. Show 17 replies
SQL*Plus is an application that you use to connect to Oracle databases.
PL/SQL is a programming language that works very well with SQL. You
can write SQL statements in SQL*Plus. You can write PL/SQL programs
user566585, thank you.
As you pointed out, SQL*plus is used to connect to database and so provide a SQL prompt for PL/SQL blocks.
Are there situations at all where SQL*plus is preferable or where PL/SQL cannot be used at all?.
Thanks a lot,
You are not comparing apple to apple.
As other mentioned, SQL*Plus is an Oracle utility, PL/SQL is a programing language.
yingkuan, thanks, yes as you can see i'm no expert. Reason for asking :-)
Whe we say that "SQL*Plus is an Oracle utility, PL/SQL is a programing language" are we saying that we can do things either with SQL*plus or PL/SQL, so we choose as we please, or that there are things we can do only with SQL*plus or preferably so because they are more efficient? are PL/SQL and SQLplus overlapping? i.e. we can do things with Sql*plus but we can also do them with PL/SQL so it is our choice?.
I have learned SQL*plus and im now trying to enter into the area of PL/SQL, and these questions started to get into my mind. One example I found striking is the use of cursors: I can formulate a statement with SQL+ to retrieve data but can also achieve the same thing with PL/SQL cursors?. Then i noticed a heated debate between programmers as to which is more efficient.
It was explained to me that with sqlplus you retrieve data all at once while with PL/SQL i can retrieve row by row and, therefore, can exert control over these rows. I did not get a clear example of how i can exert and what???
This led me to think that SQLplus and PL/SQL are probably more in competition than working together.
I couldn't think of other things, apart from cursors, that i could do both with SQLplus and PL/SQL.
See? a bit of confusion here, and as you said, i seem to be comparing aplpes with elephants :-)
I think you have learned SQL and executed your SQL statements in SQL*Plus. Using SQL*Plus you can execute both SQL and PL/SQL!
Hi -a (nice name by the way ;)),
What exactly do you mean whith your statement 'I learned SQL/PLUS'. As posted before, it is a tool. When you have learned to use it, you should know about
- SQL/PLUS commands (SET...)
- SQL statements (SELECT...)
- PL/SQL blocks (BEGIN...- END...)
- Execution of scripts (can contain PL/SQL blocks and SQL statements).
Your comparison is like 'should I use MS Word or the alphabet?'. Answer: It depends on what you want to achieve. If you just need to do SQL: SQL/PLUS is the tool of your choice. If you're about to produce complex business logic inside of server side procedures and / or functions: you have to use PL/SQL.
One golden rule that I like to follow: Don't use PL/SQL when the task can be done in SQL.
your golden rule 'Don't use PL/SQL when the task can be done in SQL' is nice one; i may follow it too.
What i meant by learning SQLplus is that i learned how to use it as a stand alone query language, meaning NO [Begin ... END] blocks were involved. This means that i can retrieve data using select statement, update records, create tables etc. but these statements were not wrapped up with PL/SQL.
Seems that i need to do bit more research about 'complex business logic inside of server side procedures' as the answer appears to be within that statement.
Thanks for your explanations,
I see, your mistaken SQL*PLUS with SQL.
Like said, SQL*PLUS is a tool used to connect to database so you can run SQL statement or call PL/SQL blocks or procedures/functions etc.
The key difference between SQL and PL/SQL is the PL. PL/SQL is procedural language. The PL/SQL language is tightly integrated with SQL, it's an extension of SQL. Because with SQL statement you can only do one task at a time, either SELECT or INSERT or UPDATE etc.
Check more information here,
Another way of looking at this:
SQLPlus is equivalent to TOAD, JDeveloper, SQLDeveloper, and so on. These can all run SQL and PL/SQL
PL/SQL is equivalent to VB, C#, Java, PERL, and so on. The can all call SQL.
yingkuan - thank you very much indeed; the link turned out to be helpful in clearing things up.
Your statement "Because with SQL statement you can only do one task at a time, either SELECT or INSERT or UPDATE etc" almost eliminated all the other questions i had. The only one left comes from a book i'm reading:
This says the only difference between a procedure and a function is that the function returns a value while a procedure doesn't. Return a value to where?. To the screen of a computer?.
Can i not wrap any SQL, e.g. SELECT, statement either in procedure or function and both return values if i set the "set serveroutput on + DBMS_Output.put_line" on?.
Thank you very much,
Hans - thanks a lot for your clarification. I did experiment with TOAD and seems to provide some nicer work environment than sql*plus prompt.
As for PL/SQL being equivalent to VB, C#, Java, PERL ( and now i guess PHP), i actually had the same question hanging for a while.
The trigger for that question was a statement in a SQL book saying that there were rumors that Oracle might discontinue PL/SQL because Java was taking over. I said to myself these two must be doing the same thing then.
Well, thanks for anticipating my question:-)
and have a great Friday,
You can return to wherever you wanted to. Depends on how you called the function, you can return the value to a variable or to the screen.
This says the only difference between a procedure and a function is that the >>function returns a value while a procedure doesn't. Return a value to where?. To the >>screen of a computer?.
You can output line in both procedure and function and technically that's not called "return value". That's output to screen.
Can i not wrap any SQL, e.g. SELECT, statement either in procedure or function >>and both return values if i set the "set serveroutput on + DBMS_Output.put_line" on?.
Just read through the document and compose a few sample procedures and functions by yourself then you will get better idea.
yingkuan - thank you very much for the explanations.
Agree, practice makes perfect. In fact i am upgrading my laptop, it has only 128 mb of RAM. I once downloaded Oracle and suddenly could see nothing but few leaves from a tree and the other half of my screen was all dark black. This dark matter was supposed to be the rest of the tree on my laptop's wallpaper :-) everything froze. Was advised to have at least some 1GB of memory when working with Oracle stuff...
need to do some work on loops and arrays too; i started to like them :-))
Thanks for help,
The trigger for that question was a statement in aThey do exactly the same kind of thing.
SQL book saying that there were rumors that Oracle
might discontinue PL/SQL because Java was taking
over. I said to myself these two must be doing the
same thing then.
They are slightly different in the way they do the job. Because of the strengths of PL/SQL and Java, I see both staying around for a long time.
You may have noticed that you can buy hammers with several different shapes - some will a ball on one end, others with a claw, and others with a rubber or plastic end. Each one can do the basic job of hammering stuff, but they also have their won strength. A professional will probably have several.