Lecture 1 Introduction to PL/SQL

1. Lecture 1Introduction to PL/SQL

2. Objectives • After completing this lesson, you should be able to define the following • What is Procedure Language PL? • Types of PL/SQL Blocks • Features of PL/SQL • Basic Block Structure of PL/SQL • Execution of PL/SQL Language Fundamentals • Character Set In PL/SQL • Lexical Units • Delimiters • Identifiers • Data Types • Literals • Constraints on Variable • Comments • Variable Scope

3. Introduction to PL/SQL • What is PL In PL/SQL? • PL Stands for Procedural Language • What is Procedure ? • A procedure is collection of statements which takes Inputs and produce output • Properties of Procedure • Procedure have a Begin and End • Easy to Learn • Easy to Debug • Increase reusability • What is PL/SQL • PL/SQL is procedure Language which combines the SQL Statements in the form of a Block or procedure i.e. PL • Extension to SQL

4. Types of PL/SQL Blocks • Anonymous Block • “Anonymous block is PL/SQL Procedure without any name” • Note • Anonymous can’t be reused • Procedure Block • “A procedure Block is the collection of statements with a name “ • Note • Procedure blocks can be reused • Procedure block may or may not return a value to the calling environment • Functions • “A Function is also collection of statements with a name and is used to return a value” • Note • Functions can be reused • Functions must return a value

5. Types of PL/SQL Blocks Procedure Block Callingenvironment Call Procedure Procedure may or may not return value Procedure Function Block Callingenvironment Call Function Function must return value Functions

6. Introduction to PL/SQL • Features of PL/SQL • Support for SQL • SQL is non-procedural i.e. it states what to do and do not tell how to done • SQL is Simple • PL/SQL combines the Easy what part of SQL with procedures and describes how to do it • Better Performance • Without PL/SQL, Oracle must process SQL statements one ata • time. • Every time a SQL statement is issued, it must be sent over the network, creating more traffic. • With PL/SQL, an entire block of statements can be sent to Oracle at one time.

7. Introduction to PL/SQL DECLARE BEGIN • Structure of PL/SQL Block • DECLARE (Declare is optional and is used to declare the variables) • BEGIN (Begin is mandatory contains executable statements i.e. statements that effects the database” • EXCEPTION (Exception is Optional used to trap errors) • END; (END is mandatory used to show end of PL/SQL block) EXCEPTION END;

8. Introduction to PL/SQL 1 2 • How PL/SQL Program Execute • PL/SQL Block consist of SQL and PL/SQL statements passed to Server • PL/SQL Block statements are divided in to tow parts • PL/SQL • SQL • SQL Statements are given to SQL Statement Executor • PL/SQL Statements are given to PL/SQL Engine for Execution 3 4

9. Introduction to PL/SQL • Language Fundamentals • Character Set • “PL/SQL program consist of Lines written by using a specific set of Alphabets and symbols etc known as the character set” • Example • The upper- and lower-case letters A .. Z and a .. z • The numerals 0 .. 9 • The symbols ( ) + - * / < > = ! ~ ^ ; : . ’ @ % , " # $ & _ | { } ? [ ] • Tabs, spaces, and carriage returns

10. Introduction to PL/SQL • Lexical Units • A line of PL/SQL Statement or text consist of group of Characters known as Lexical Units • Example • Spaces • Delimiters (simple and compound symbols) • Identifiers, which include reserved words • Literals • Comments • Spaces • Spaces are used to increase readability of Lexical Units I IF x > y THEN high := x; ENDIF; -- not allowed because ENDIF is combined I IF x > y THEN high := x; END IF; -- Correct

11. Introduction to PL/SQL • Delimiters • A delimiteris a simple or compound symbol that has a special meaning to PL/SQL • Simple Symbols • Simple symbols consist single character • Compound Symbols • Compound symbols consist of combination of two characters

12. Introduction to PL/SQL

13. Introduction to PL/SQL • Identifiers • An Identifier is a combination of Alphabets and is used to identify a value • Types of Value • Number • VARCHAR2 • String (a-z,A-Z with special characters- , ) * & ^ # @ !) • Date • Boolean (Consist of True/False) • Rules Declare Identifier • An identifier cannot exceed 30 characters. • Every character, including dollar signs, underscores, and number signs, is significant. • For example, v_index_nr and vindex_nr represent two different things.

14. Introduction to PL/SQL • An identifier consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs. • For example,v_index_nr , v$index_nr , v index_nr, 4_index_nr, v-index_nr • By default, identifiers are not case sensitive • For example,v_index_nr and V_Index_NR are the same. • Identifiers may not be the same as reserved words • For example, you cannot use the word END , BEGIN, DECLARE as a variable name • How to DECLARE Identifier Variable in PL/SQL • Structure of PL/SQL Block • DECLARE (Declare is optional and is used to declare the variables) • BEGIN (Begin is mandatory contains executable statements i.e. statements that effects the database” • EXCEPTION (Exception is Optional used to trap errors) • END; (END is mandatory used to show end of PL/SQL block)

15. Introduction to PL/SQL • Syntax • Note • Keyword DECLARE • Variable_name is the name of variable • Datatype specifies type of value variable will contain • Example • . • Analyze • v_sal_nu is variable will point numeric type value i.e 0-9 • v_name_tx variable will point string type value i.e. Hello, Pakistan, NIE

16. Introduction to PL/SQL • Example • Note • Keyword DECLARE • Analyze • DECLARE Key word shows start of Program • v_hiredate variable will contain Date • v_deptno is of type NUMBER will contain numeric value is assigned value of 10 • v_location is of type VARCHAR2 contain string value of Pakistan • BEGINEND execution block start and end • DBMS_OUTPUT.PUT_LINE used to out put the variable values

17. Introduction to PL/SQL • Data types • Data type defines the storage Format of variables • Categories of Data type • Scalar Type • Represent a single value • Type of Scalar Data Type • NUMBER • VARCHAR2 • DATE/TIME • BOOLEAN • Composite Type • Composite type consist of items that can be manipulated individually • Types of Scalar Data Type • Reference Type • Object Type

18. Introduction to PL/SQL • NUMBER • Number type consist of Numeric value i.e. 0-9. • Syntax • Precision is the total number of digits in the number. The value of precision can be between 1 and 38. • Scale is the number of digits to the right of the decimal point. The value of scale can be between – 84 and 127

19. Introduction to PL/SQL • NUMBER • Number type consist of Numeric value i.e. 0-9. • Syntax • Precision is the total number of digits in the number. The value of precision can be between 1 and 38. • Scale is the number of digits to the right of the decimal point. The value of scale can be between – 84 and 127

20. Introduction to PL/SQL • VARCHAR2 • VARCHAR2 data type consist of string values. • Syntax • variable1_tx is • Scale is the number of digits to the right of the decimal point. The value of scale can be between – 84 and 127

21. Introduction to PL/SQL • Date • Date type variable is used to hold the date • Syntax • variable1_dt is is name of variable • DATE isdata type

22. Introduction to PL/SQL • BOOLEAN • BOOLEAN type is used to hold true and false value • Syntax • variable1_dt is is name of variable • BOOLEAN is data type Example

23. Introduction to PL/SQL • Constraints • A constraint is the limitation on any object • Types of Constraints • DEFAULT • A value given to variable immediately after its declaration • Example • Analyze • c_commision is NUMBER type variable • Default value of this variable is set to 7839

24. Introduction to PL/SQL • NOT NULL • When a variable is declared as NOT NULL we can not leave that variable empty • Example • Analyze • v_deptno is declared as NOT NULL • Initialize variable immediately it is declared

25. Introduction to PL/SQL • CONSTANT • When a variable is declared as NOT NULL we can not leave that variable empty • Example • Analyze • c_commision is declared as CONSTANT

26. Introduction to PL/SQL • % Type Variable • Variable inherits the data type from other variables • Example

27. Introduction to PL/SQL • Literals • A literal is an explicit numeric, character string, or Boolean assigned to a variable • Types of Literals • NUMERIC Literal • NUMERIC Literal consist of numbers 0-9 e.g. 1,23,90,8976 etc • CHARACTER Literal • Character Literal consist of characters a-z,A-Z e.g ‘r’ , ‘t’, ‘h’ etc • STRING Literal • String Literal consist of combination of alphabets with special characters • e.g. ‘Pakistan’, ‘hello_from_pakistan’, ‘National Institute of Electronics:’ ect

28. Introduction to PL/SQL • Comments • Comments are used to increase the readability of PL/SQL Program • Types of Comments • Single-Line comment • Single Line Comment • Multi Line Comment

29. Nested Blocks and Variable Scope Example • ... • x BINARY_INTEGER; • BEGIN • ... • DECLARE • y NUMBER; • BEGIN • ... • END; • ... • END; Scope of x Scope of y

30. Starting PL/SQL • How to Start PL/SQL DOS Prompt

31. Starting PL/SQL • How to Start PL/SQL From windows

32. Starting PL/SQL • How to Start PL/SQL From windows

33. Starting PL/SQL • iSQLPLUS

34. Summary • Today we complete the following topics • What is Procedure Language PL? • Types of PL/SQL Blocks • Features of PL/SQL • Basic Block Structure of PL/SQL • Execution of PL/SQL Language Fundamentals • Character Set In PL/SQL • Lexical Units • Delimiters • Identifiers • Data Types • Literals • Constraints on Variable • Comments • Variable Scope

35. Any Question • ?