PL/SQL for Beginners - PL/SQL Tutorial 1

PL/SQL for Beginners - PL/SQL Tutorial 1

• 1.
  P L /S Q L T u t o r i a l - B y E r G u r p r e e t S i n g h Visit: http://gsbprogramming.blogspot.in
• 2.
  P L /S Q L T u t o r i a l - B y E r G u r p r e e t S i n g h Visit: http://gsbprogramming.blogspot.in What is PL/SQL? PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in Oracle Database (since version 7). PL/SQL (Procedural Language extensions to SQL) is designed specifically for the seamless processing of SQL commands. PL/SQL stored and compiled in the database, runs within the Oracle executable and inherits the robustness, security, and portability of the Oracle Database. PL/SQL is a combination of SQL along with the procedural features of programming languages. Features of PL/SQL  block (modular) structure  flow-control statements and loops  variables, constants, and types  structured data  customized error handling  it allows you to store compiled code directly in the database.  once a given block of code is loaded into memory, any number of users can use the same copy of it simultaneously (although behavior is as though each user had her own copy), which is useful for the Oracle WebServer.  enables you to define triggers, which are subprograms that the database executes automatically in response to specified events.  is tightly integrated with SQL.  offers extensive error checking.  supports object-oriented programming.  It supports developing web applications and server pages.
• 3.
  P L /S Q L T u t o r i a l - B y E r G u r p r e e t S i n g h Visit: http://gsbprogramming.blogspot.in PL/SQL Syntax PL/SQL block consists of three sub-parts:  DECLARATIONS  EXECUTABLE COMMANDS  EXCEPTION HANDLING DECLARATIONS This section includes the declarations for variables, cursors, sub-programs, and other elements to be used in the program. This section starts with DECLARE keyword. This section is optional. EXECUTABLE COMMANDS This section includes executable PL/SQL statements of the program. It should have at least one executable line of code. This section starts with BEGIN keyword and ends with END; keyword. This section is mandatory. EXCEPTION HANDLING This section includes exception(s) that handle errors in the program. This section starts with EXCEPTION keyword and is placed between BEGIN and END; Basic structure: DECLARE <declarations section> BEGIN <executable command(s)> EXCEPTION <exception handling> END; Using Comments:  Single line comments: Single line comments can be written as: -- Single line Comment  Multi-line comments: Multi-line comments can be written as: /* Multi-line Comment */
• 4.
  P L /S Q L T u t o r i a l - B y E r G u r p r e e t S i n g h Visit: http://gsbprogramming.blogspot.in Declaring Variables: Syntax: Variable_name data_type; Or Variable_name data_type:=value; Or Variable_name data_type DEFAULT value; For example: vSalary number; or vSalary number:=1000; or vSalary number DEFAULT 1000; You can also use CONSTANT and NOT NULL as: Variable_name CONSTANT data_type NOT NULL :=value; For example: vMax CONSTANT number NOT NULL:=100; When you use CONSTANT or NOT NULL then you must initialize the variable. PL/SQL identifiers are constants, variables, exceptions, procedures, cursors, and reserved words. The identifiers consist of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs and should not exceed 30 characters. By default, identifiers are not case-sensitive. So you can use integer or INTEGER to represent a numeric value. You cannot use a reserved keyword as an identifier. Printing a message To print a message use: dbms_output.put_line(‘message’); To concatenate use || dbms_output.put_line(‘message1’||’message2’); dbms_output.put_line(‘Employee Id: ’||65489); To print value of a variable use: dbms_output.put_line(‘Value: ’||variable_name);
• 5.
  P L /S Q L T u t o r i a l - B y E r G u r p r e e t S i n g h Visit: http://gsbprogramming.blogspot.in Sample PL/SQL Program: DECLARE vMessage varchar2(50):=’Welcome to PL/SQL’; BEGIN dbms_output.put_line(vMessage); END; / The END; line signals the end of the PL/SQL block. To run the code from SQL command line, you may need to type / at the beginning of the first blank line after the last line of the code. When the above code is executed at SQL prompt, it produces the following result: Welcome to PL/SQL PL/SQL procedure successfully completed.