FALLSEM2025-26_VL_CSI1001_00100_ELA_2025-09-29_Lab-6.pptx

fhdhdfhdf

1. PL/SQL

2. PL/SQL PL/SQL is a block structured language that enables developers to combine the power of SQL with procedural statements. All the statements of a block are passed to oracle engine at once which increases processing speed and decreases the traffic.

3. Architecture of PL/SQL The PL/SQL architecture mainly consists of following three components: PL/SQL block PL/SQL Engine Database Server

4. PL/SQL block: This is the component which has the actual PL/SQL code. This consists of different sections to divide the code logically (declarative section for declaring purpose, execution section for processing statements, exception handling section for handling errors) It also contains the SQL instruction that used to interact with the database server. All the PL/SQL units are treated as PL/SQL blocks, and this is the starting stage of the architecture which serves as the primary input.

5. PL/SQL Engine PL/SQL engine is the component where the actual processing of the codes takes place. PL/SQL engine separates PL/SQL units and SQL part in the input (as shown in the image below). The separated PL/SQL units will be handled by the PL/SQL engine itself. The SQL part will be sent to database server where the actual interaction with database takes place. It can be installed in both database server and in the application server.

6. Database Server: This is the most important component of Pl/SQL unit which stores the data. The PL/SQL engine uses the SQL from PL/SQL units to interact with the database server. It consists of SQL executor which parses the input SQL statements and execute the same.

7. PL/SQL block: Following are the different type of PL/SQL units. Anonymous Block Function Library Procedure Package Body Package Specification Trigger Type Type Body

8. Advantage of Using PL/SQL Better performance, as SQL is executed in bulk rather than a single statement High Productivity Tight integration with SQL Full Portability Tight Security Support Object Oriented Programming concepts.

9. Structure of PL/SQL Block:

10. Structure of PL/SQL DECLARE declaration statements; BEGIN executable statements EXCEPTIONS exception handling statements END;

11. Basic PL/SQL Programming SET SERVEROUTPUT ON; DECLARE var varchar2(40) := ‘Hello World' ; BEGIN dbms_output.put_line ( var ); END; /

12. Some Basic Operators Assignment operator := It is used to assign a value to a variable. dbms_output.put_line () : This command is used to direct the PL/SQL output to a screen. Single Line Comment : To create a single line comment , the symbol – – is used. Multi Line Comment : To create comments that span over several lines, the symbol /* and */ is used. Input From User : &variable

13. Write a PL/SQL to add two number SET SERVEROUTPUT ON; DECLARE a integer := 10 ; b integer := 20 ; c integer ; BEGIN c := a + b ; dbms_output.put_line ('Sum of '||a||' and '||b||' is = '||c); END; /

14. The PL/SQL Delimiters Delimiter Description +, -, *, / Addition, subtraction/negation, multiplication, division % Attribute indicator ' Character string delimiter . Component selector (,) Expression or list delimiter : Host variable indicator , Item separator " Quoted identifier delimiter = Relational operator

15. @ Remote access indicator ; Statement terminator := Assignment operator => Association operator || Concatenation operator ** Exponentiation operator <<, >> Label delimiter (begin and end) /*, */ Multi-line comment delimiter (begin and end) -- Single-line comment indicator .. Range operator <, >, <=, >= Relational operators <>, !=, ~=, ^= Different versions of NOT EQUAL

16. PL/SQL Program Units A PL/SQL unit is any one of the following − PL/SQL block Function Package Package body Procedure Trigger Type Type body

17. Operator in PL/SQL Arithmetic operators (+, -, *, /, **) ( ** for power, 10**2=1000 ) Relational operators (=, (!=, <>, ~=), < , > , >=, <=) Comparison operators (Like, Between, In, Is null) Logical operators (AND, OR, NOT) String operators

18. Conditional Operator IF-THEN Statement IF-THEN-ELSE Statement IF-THEN-ELSIF Statement NESTED-IF Statement

19. IF THEN Statement IF <condition: returns Boolean> THEN < action_block > END if; Example: DECLARE a NUMBER :=10; BEGIN dbms_output.put_line (‘Program started.' ); IF( a > 100 ) THEN dbms_output.put_line ('a is greater than 100'); END IF; dbms_output.put_line (‘Program completed.'); END;

20. IF-THEN-ELSE Statement IF <condition: returns Boolean> THEN < action_blockl > ELSE <action_block2> END if;

21. Example DECLARE a NUMBER:=11; BEGIN dbms_output.put_line ('Program started'); IF( mod(a,2)=0) THEN dbms_output.put_line ('a is even number' ); ELSE dbms_output.put_line ('a is odd number1'); END IF; dbms_output.put_line ('Program completed'); END;

22. Looping in Pl/SQL LOOP <execution block starts> <EXIT condition based on developer criteria> < execution_block_ends > END LOOP;

23. Example DECLARE a NUMBER:=1; BEGIN dbms_output.put_line ('Program started.'); LOOP dbms_output.put_line (a); a:=a+1; EXIT WHEN a>5; END LOOP; dbms_output.put_line ('Program completed'); END;

24. For Loop FOR < loop_variable > in < lower_limit > .. < higher_limit > LOOP <execution block starts> . . . < execution_block_ends > END LOOP;

25. Example BEGIN dbms_output.put_line ('for loop program started.' ); FOR a IN 1 .. 5 LOOP dbms_output.put_line (a); END LOOP; dbms_output.put_line ('Program completed.'); END;

26. While Loop WHILE <EXIT condition> LOOP <execution block starts> . . . < execution_block_ends > END LOOP;

27. Example DECLARE a NUMBER :=1; BEGIN dbms_output.put_line ('Program started'); WHILE (a < 5) LOOP dbms_output.put_line (a); a:=a+1; END LOOP; dbms_output.put_line ('Program completed'); END;

28. CASE Statement CASE (expression) WHEN < valuel > THEN action_blockl ; WHEN <value2> THEN action_block2; WHEN <value3> THEN action_block3; ELSE action_block_default ; END CASE;

29. Example DECLARE a NUMBER :=55; b NUMBER :=5; arth_operation VARCHAR2(20) :='MULTIPLY'; BEGIN dbms_output.put_line ('Program started.' ); CASE ( arth_operation ) WHEN 'ADD' THEN dbms_output.put_line ('Addition of the numbers are '|| a+b ); WHEN 'SUBTRACT' THEN dbms_output.put_line ('Subtraction of the numbers are '||a-b ); WHEN 'MULTIPLY' THEN dbms_output.put_line ('Multiplication of the numbers are'|| a*b); WHEN 'DIVIDE' THEN dbms_output.put_line ('Division of the numbers are'|| a/b); ELSE dbms_output.put_line ('No operation action defined. Invalid operation'); END CASE; dbms_output.put_line ('Program completed.'); END;

30. Stored Procedure & Functions CREATE OR REPLACE PROCEDURE < procedure_name > ( < parameterl IN/OUT < datatype > .. . ) [ IS | AS ] < declaration_part > BEGIN <execution part> EXCEPTION <exception handling part> END;

31. DECLARE a number; b number; c number; PROCEDURE findMin (x IN number, y IN number, z OUT number) IS BEGIN IF x < y THEN z:= x; ELSE z:= y; END IF; END; BEGIN a:= 23; b:= 45; findMin (a, b, c); dbms_output.put_line (' Minimum of (23, 45) : ' || c); END;

32. Function CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN ('Welcome '|| p_name ); END; DECLARE lv_msg VARCHAR2(250); BEGIN lv_msg := welcome_msgJune ('Student'); dbms_output.put_line ( lv_msg ); END; SELECT welcome_msgJune (‘Student') FROM DUAL;

33. Program to Print Fibonacci series Declare first number := 0; second number := 1; temp number; n number := 5; i number; begin dbms_output.put_line ('Series:'); dbms_output.put_line (first); dbms_output.put_line (second); for i in 2..n loop temp:= first+second ; first := second; second := temp; dbms_output.put_line (temp); end loop; end;

34. Reverse the number Declare num varchar2(5):='12345'; len number(2); revnum varchar2(5); begin len := length( num ); for i in reverse 1.. len loop revnum := revnum || substr (num,i,1); end loop; dbms_output.put_line ('given number ='|| num ); dbms_output.put_line ('reverse number ='|| revnum ); end;

35. Factorial of Number declare n number; fac number:=1; i number; begin n:=:n; for i in 1..n loop fac := fac * i ; end loop; dbms_output.put_line ('factorial='|| fac ); end;

36. Data Insertion and Updation BEGIN INSERT INTO < table_name >(<column1>,<column2>, ...< column_n >) VALUES(< valuel ><value2>,..., < value_n >); END; BEGIN UPDATE < table_name > SET < columnl >=<VALUE1>, <column2>=<Yalue2>,< column_n >=< value_n > WHERE <condition that uniquely identifies the record that needs to be update>; END;

37. Data Selection and Deletion BEGIN SELECT < columnl >,..< column_n > INTO < vanable 1 >,. .< variable_n > FROM < table_name > WHERE <condition to fetch the required records>; END; BEGIN DELETE FROM < table_name > WHERE <condition that uniquely identifies the record that needs to be update>; END;

38. Cursor To execute SQL statements, a work area is used by the Oracle engine for its internal processing and storing the information. The ‘Cursor’ is the PL/SQL constructor that allows the user to name the work area and access the stored information in it.

39. Use of Cursor The major function of a cursor is to retrieve data, one row at a time, from a result set, unlike the SQL commands which operate on all the rows in the result set at one time. Cursors are used when the user needs to update records in a singleton fashion or in a row by row manner, in a database table. The Data that is stored in the Cursor is called the Active Data Set. Oracle DBMS has another predefined area in the main memory Set, within which the cursors are opened. Hence the size of the cursor is limited by the size of this pre-defined area.

40. Cursor Actions Declare Cursor : A cursor is declared by defining the SQL statement that returns a result set. Open : A Cursor is opened and populated by executing the SQL statement defined by the cursor. Fetch : When the cursor is opened, rows can be fetched from the cursor one by one or in a block to perform data manipulation. Close : After data manipulation, close the cursor explicitly. Deallocate : Finally, delete the cursor definition and release all the system resources associated with the cursor.

41. Types of Cursors Cursors are classified depending on the circumstances in which they are opened. Implicit Cursor : If the Oracle engine opened a cursor for its internal processing it is known as an Implicit Cursor. It is created “automatically” for the user by Oracle when a query is executed and is simpler to code. Explicit Cursor : A Cursor can also be opened for processing data through a PL/SQL block, on demand. Such a user-defined cursor is known as an Explicit Cursor.

42. Cursor Attributes %FOUND: It returns the Boolean result 'TRUE' if the most recent fetch operation fetched a record successfully, else it will return FALSE. %NOTFOUND : This works oppositely to %FOUND it will return 'TRUE' if the most recent fetch operation could not able to fetch any record. %ISOPEN : It returns Boolean result 'TRUE' if the given cursor is already opened, else it returns 'FALSE' %ROWCOUNT : It returns the numerical value. It gives the actual count of records that got affected by the DML activity.

43. Cursor to Fetch student_id DECLARE CURSOR stu_details IS SELECT id FROM mark_details ; stu_id mark_details.id%type ; BEGIN OPEN stu_details ; LOOP FETCH stu_details INTO stu_id ; IF stu_details%NOTFOUND THEN EXIT; END IF; Dbms_output.put_line (' Student_id Fetched:'|| stu_id ); END LOOP; Dbms_output.put_line ('Total rows fetched is'|| stu_details%ROWCOUNT ); CLOSE stu_details ; END;

44. DECLARE CURSOR stu_details IS SELECT id, m1 FROM mark_details ; stu_id mark_details.id%type ; stu_m1 mark_details.m1%type; BEGIN OPEN stu_details ; LOOP FETCH stu_details INTO stu_id , stu_m1; IF stu_details%NOTFOUND THEN EXIT; END IF; Dbms_output.put_line (' Student_id :'|| stu_id ||'marks:'||stu_m1); END LOOP; Dbms_output.put_line ('Total rows fetched is'|| stu_details%ROWCOUNT ); CLOSE stu_details ; END;