Labels

2017 absence absence management Account accounting AIM aliases AME and API application application utilities lookups approval assignments ATO Australia Bank bi publisher budget business business groups CAGR candidates cartisian product case CEMLI Center Stage channels Classification competency concurrent Configuration configuration profile constants contextualization conversion correction cost costing coverage area customization data database date DateTracked deductions define design develop DFF diagnostics document earnings ebs EIT Element employee enhancements erp excel expression extension failure Fastformula FBT Flexfield FND fndload foreign key forms Formula fringe benefit FRM-40654 from FTE Functions fund fusion GL global transfer grade help hierarchy HR HRMS human resource management system implementation income information interfaces internet interview job join key flexfield KFF KPI language learning leave legal employer legislation links lists localization location management New Year obia obiee OLF onboarding oracle oracle applications oracle descriptive flex field oracle descriptive flexfield oracle ebs oracle erp oracle fusion HCM oracle hrms oracle hrms interview questions oracle hrms modules oracle hrms modules list oracle hrms organization oracle hrms table oracle hrms tables oracle hrms tutorial oracle irecruitment oracle legal entities oracle lookups oracle organization hierarchy oracle payroll oracle payroll interview questions oracle payroll tables oracle self service order by Organization organization type otbi package package body package specification patch payg Payment payroll people group perform person personalisation phase pl/sql position primary key process profile programs project qualifier Query question questions Recruiting Center Recruitment regex regular expression reporting tool reports requests requirement requisition resume retropay RICE salary schema security profile select SIT smartorg sql statutory stores STP Super Superannuation system systems Table Taleo taleo 15B Taleo Recruitment tax termination test testing trunc update user group user management user type value set variables View Views Web ADI webadi where work relationship

Thursday 13 November 2014

SQL vs. PL/SQL


It’s an interesting question, to be sure. Most people who are familiar with Oracle development wouldn't give it a thought but when you come down to it, it’s sometimes confusing to define the demarcation between SQL and PL/SQL. Through this note let us try to understand the differences between SQL and PL/SQL. You never know, this  might just be the starting point of your next oracle hrms interview questions or oracle payroll interview questions :)

SQL is a non-procedural language; users describe in SQL what they want, and the SQL language compiler automatically generates a procedure to navigate the database and perform the desired task. All operations performed on the information in an Oracle database are executed using SQL statements.

PL/SQL is Oracle's procedural language extension to SQL. PL/SQL allows you to mix SQL statements with procedural constructs. PL/SQL provides the capability to define and execute PL/SQL program units such as procedures, functions, and packages. PL/SQL program units generally are categorized as anonymous blocks and stored procedures.

In a nutshell SQL allows you to issue a single query or execute a single insert/update/delete whereas PL/SQL allows you to write a full program (loops, variables, etc.) to accomplish multiple selects/inserts/updates/deletes

Some other differences between the two are –

  • SQL is a limited language that allows you to directly interact with the database. You can manipulate objects (DDL) and data (DML) with SQL, but SQL doesn't include all the things that normal programming languages have such as loops and IF...THEN statements. That is what PL/SQL is for. PL/SQL is a normal programming language that includes all the features of most other programming languages. But it has one thing that other programming languages don't have, namely the easy ability to integrate with SQL
  • PL/SQL can be the application language just like Java or PHP can. PL/SQL might be the language we use to build, format and display those screens, web pages and reports. Whereas SQL may be the source of data for our screens, web pages and reports
  • SQL is executed one statement at a time. PL/SQL is executed as a block of code
  • SQL tells the database what to do (declarative), not how to do it. In contrast, PL/SQL tells the database how to do things (procedural)
  • SQL is used to code queries, DML and DDL statements. PL/SQL is used to code program blocks, triggers, functions, procedures and packages
  • We can embed SQL in a PL/SQL program, but we cannot embed PL/SQL within a SQL statement
More about pl/sql and sql will be discussed during my future posts on oracle hrms tables and oracle payroll tables
Labels: , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)