PL/SQL PL/SQL is not a standalone programming language; it is a tool within the Oracle programming environment. SQL* Plus is an interactive tool that allows you to type SQL and PL/SQL statements at the command prompt. These commands are then sent to the database for processing. Once the statements are processed, the results are sent back and displayed on screen. It is also defined as Procedure language/Structured query language.it is a significant member of Oracle Programming tool set. PL/SQL code is extensively used for server-side programming. It is case sensitive programming language. Characteristics of PL/SQL: • • It is a highly structured, readable and accessible language It is an easy language and provides a familiar environment to interact with program using some standard keyword. It is a standard, portable and high-performance transaction processing language Which means write once run everywhere concept can easily work with it. If user write a PL/SQL procedure or function to execute from within the Oracle database, they can move that same procedure to a database on their other network and execute it there without any changes. If you write a PL/SQL procedure or function to execute from within the Oracle database, they can move that same procedure to a database on their other network and execute it there without any changes. It is an embedded language It means PL/SQL was not designed to be used as a standalone language, but to be invoked from within a host environment. So, you can run PL/SQL programs from within the database (such as SQL*Plus interface). But we cannot, create a PL/SQL executable that runs all by itself. It is a highly integrated Data base language. • PL/SQL provides a built-in, interpreted and OS independent programming environment. • PL/SQL can also directly be called from the command-line SQL*Plus interface. • Direct call can also be made from external programming language calls to database. • PL/SQL's general syntax is based on that of ADA and Pascal programming language. • Apart from Oracle, PL/SQL is available in TimesTen in-memory database and IBM DB2. • • Note: - Oracle TimesTen In-Memory Database is a relational database in the application tier storing data in main memory to reduce latency and increase throughput. Generally, a program return in PL/SQL language is divided in ‘Blocks’. BLOCKS are a basic programming unit in PL/SQL programming language. There are two types of Blocks in PL/SQL: -
BLOCKS Named Block Anonymous Block The Execution Section (Mandatory) The Declaration Section (optional) The Exception Handling section (optional) Prototype of Anonymous Block: DECLARE Declaration Statement BEGIN Executable Statement Exception Exception handling Statement End 1) Declaration Statement: This is the first section of PL/SQL block which contains definition of PL/SQL identifier such as variables. Constant, cursor etc. We can say, this is a place where all local variable used in program are defined and documented. Declaration Section start with a keyword ‘DECLARE’. 2) Execution Section: This section also contains executable statement that allow us to manipulate the variable that have been declared in declaration section. The execution section of any PL/SQL block begin with keyword ‘BEGIN’ and end with keyword ‘END’. This is the only mandatory section in PL/SQL block. This section supports all DML Commands and SQL*PLUS built-in functions. It also supports DDL Commands using native dynamic SQL (NDS) or DMBS SQL built-in package. 3) Exception Handling Section: This is the last section of PL/SQL block which is optional just like declaration section. This section contains the statement that are executed when a run time error occurs within the block. All exception handling code goes here.
Comment Code: PL/SQL supports two types of comment code 1) Single line comment: -It begins with double hyphen sign (--) placed anywhere on a line and extend to end of the line. Example 1: SET SERVEROUTPUT ON; DECLARE BEGIN -- Display the output DBMS_OUTPUT.PUT_LINE (‘Hello world’); END; 2) Multi-line comments: - begin with a slash-asterisk (/*), end with an asterisk-slash (*/), and can span multiple lines. Example2: DECLARE V_salary NUMBER (8); BEGIN /*Ignore the statement by multi line comment code SELECT salary INTO V_salary FROM employee; //error*/ END; Variables of PL/SQL: Variables are nothing but a place holder in computer memory that holds some data, we call them variables because the value they hold is of variable nature (or vary in nature) which can be changed at any point of our program every variable must have a valid name, a data type and a data width like various other programming language. In PL/SQL variables must be declared prior to its use. Example: SET SERVEROUTPUT ON; DECLARE V_test VARCHAR (15); // Here is variable declaration Variable declaration means announcement of variable to the compiler, as soon as compiler comes across to the signature of our variable, it first check for the validity and allocates the memory according to the data type of variable. Note: Explanation of above Example SET SERVEROUTPUT ON: - This is the special PL/SQL command which allow the server to communicate with you and enable to display the result through default output device. DECLARE: - In second line we have a keyword DECLARE which indicates the declaration section of our block. In declaration section, we have a variable ‘V_test’ which data type is VARCHAR and data width is (15) followed by semicolon (;), which indicates the end of line.
Initialization of Variables: Now after the successful declaration of variable, next we have to initialize it in PL/SQL language like various other languages. When we declare a variable, its completely empty and no any data holds on it. Just to make a variable useful we need to put some data into it and the process of assigning value to the variable is called variable initialization. Unlike variable declaration, which can only be declare in Declaration section of PL/SQL block, the initialization of variable can be done anywhere inside the program. We can initialize it either in declaration section while declaring it or execution section or even in exception handling section. For Example: 1) Initialize a variable in Declaration section: V_test VARCHAR (15): =’Vandana’; That’s how we initialize the variable in declaration section. Note: - Assignment operator is slightly different from other languages which is a combination of: = (colon and equal to sign) 2) Initialize a variable in Execution section: SET SERVEROUTPUT ON; DECLARE V_test VARCHAR (15); BEGIN V_test: = ‘Vandana’; End; To initialize the variable in execution section we just write the name of variable followed by assignment operator (: =) then the value (‘Vandana’). There is no need to use data type and data width at this point. Just make sure the variable which are initialize must be declare in declaration section. 3) Initialize a variable by SELECT INTO statement/by fetching data from any table of Database: Example: DECLARE V_salary NUMBER (8); BEGIN SELECT salary INTO V_salary FROM employee; //error END; Note: - while declaring a variable the datatype and data width of variable and column who’s value we want to fetch must watch.