PL SQL.pptx in computer language in database

Plsql

1. PL/SQL   PL/SQL is procedural language extension to SQL. PL/SQL is the superset of SQL . PL/SQL allows the user to mix SQL statements with procedural statements like if statement, looping structures etc. With PL/SQL, you can use SQL statements to manipulate Oracle data and flow of control statements to process the data. Features of PL/SQL   Block Structures: PL SQL consists of blocks of code , which can be nested within each other. Each block forms a unit of a task or a logical module . PL/SQL Blocks can be stored in the database and reused . Variables and Constants : pl / sql allows the user to declare variables and constants . Variables are used to store values temporarily . Variables and constants can be used in sql and pl / sql procedural statements just like expression.

2. Control Structures : PL/SQL allows control structures like if statement, for loop, while loop to be used in the block . Modularity: pl / sql allows the process can be divided into different modules. These modules can be named as subprograms ( procedures, functions ). Cursors : A cursor is private sql area used to execute sql statements and store processing information . Better Performance : PL SQL engine processes multiple SQL statements simultaneously as a single block , thereby reducing network traffic. Error Handling : PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program.

3. SQL PL/SQL It is a Structured Query Language used to issue a single query or execute a single insert/update/delete. It is a programming language of SQL, used to write full programs using variables, loops, and operators etc. to carry out multiple selects/inserts/updates/deletes. It is considered as the source of data for reports, web pages and screens. It can be considered as the application language. It might be the language used to build, format and display those reports, web pages and screens. It is a data oriented language used to select and manipulate sets of data. It is a procedural language used to create applications. It is used to write queries by using DDL and DML statements. It is used to write program blocks, functions, procedures triggers, and packages. It is executed one statement at a time. It is executed as a block of code. It is declarative, i.e., it tells the database what to do but not how to do it. It is procedural, i.e., it tells the database how to do things. It can be embedded within a PL/SQL program. It can’t be embedded within a SQL statement.   Differences between SQL and PL/SQL

4. The PL/SQL Identifiers Identifiers are nothing but a name that is given to a PL/SQL object . Examples of identifiers are constants, variables, exceptions names , procedures names , cursors etc . The PL/SQL Literals A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. For example, TRUE, 786, NULL, ' tutorialspoint ' are all literals of type Boolean, number, or string. PL/SQL, literals are case-sensitive. PL/SQL supports the following kinds of literals − Numeric Literals Character Literals String Literals BOOLEAN Literals Date and Time Literal s properties of PL/SQL identifiers are Up to 30 characters in length Must start with a letter Can include $ (dollar sign), _ (underscore), and # (hash sign) Cannot contain any “whitespace” characters If the only difference between You cannot use a reserved keyword as an identifier.

5. The PL/SQL Comments Program comments are explanatory statements that can be included in the PL/SQL code that you write and helps anyone reading its source code. All programming languages allow some form of comments. The PL/SQL supports single-line and multi-line comments . All characters available inside any comment are ignored by the PL/SQL compiler. The PL/SQL single-line comments start with the delimiter -- (double hyphen) and multi-line comments are enclosed by /* and */ . DECLARE -- variable declaration message varchar2 ( 20 ):= 'Hello, World!’ ; BEGIN /* PL/SQL executable statement(s) */ dbms_output . put_line ( message ); END ;

6.   Structure of PL/SQL Block : PL/SQL Block consists of three sections. They are Declaration section , Execution section , Exception Handling section .   DECLARE [Variable declaration] BEGIN Program Execution [EXCEPTION] Exception handling END;

7. Variable: T o store data temporarily during the execution of code. It helps the user to manipulate data in PL/SQL programs. Rules for define variable name : 1. The variable name must start with an alphabet. 2. The size of variable name must be less than 31 characters. 3. It does not allow spaces, hyphens but it allows under score as special character   Syntax : variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value ]   Assignment Operator : An assignment operator is used to assign a value to be stored in a variable or an expression. It can be represented by “:=’. The syntax is, variable_name := value/expression;

8. Displaying output from pl / sql : In order to display output from pl / sql block, we have to use dbms_output.put_line package. A package is a collection of procedures and functions.   Select … into statement : It retrieves data from one or more database tables, and assigns the selected values to variables or collections . Write a program to display your name   declare begin dbms_output.put_line (‘ srdc ’); end;

9. %type It is used to assign data type of tables column to the variable. Declare v_name emp.ename%type ; v_sal emp.sal%type ; Begin select ename,sal into v_name , v_sal from emp where empno =2; dbms_output.put_line (‘Name : ’ || v_name ); dbms_output.put_line (‘Salary : ’ || v_sal ); end; Write a program read empno and display details of the employ by using %type variable

10. declare emprec emp%rowtype ; Begin Select * into emprec from emp where empno =10; dbms_output.put_line (‘Name : ’ || emprec.ename ); dbms_output.put_line (‘Salary : ’ || emprec.salary ); End; It is used to declare variable to keep a single record, since a record is nothing but collection of column. This is also known as composite data type . Write a program read empno and display details of the employ by using % rowtype variable % rowtype

11. Conditional statements/ Control statements Conditional statements 1. if then statement 2. if then else statement 3. if then elsif statement if then statement : The if statement associated a condition with a sequence of statements enclosed by the keywords “then” and “ endif ”. If the condition is true, the statements get executed and if the condition is false or NULL then the if statement terminate automatically. The sytax is,   if<condition> then set of statements; end if;

12.   if then else statement: The if statement adds the keyword else followed by an alternative sequence of statements. If the condition is true, it will execute set of statements. If the condition is false or NULL, then only the alternative sequence of statements gets executed. It ensures that either of the sequence of statements is executed. The syntax is,   if<condition> then set of statements; else set of statements; end if;   Write a program to print big number between two numbers declare a number:=&a; b number:=&b; begin if a>b then dbms_output.put_line (a); else dbms_output.put_line (b); end if;

13. if then elsif statement : It allows the user to choose between several alternatives. The syntax is,   if<condition1> then set of statements; elsif <condition2> then set of statements; else set of statements; end if;   here first it will evaluate the condition1, if it is true it will execute the set of statements and comes out side of the program. If the condition1 is false, it will evaluate the condition2 if it is true, it will execute the set of statements and comes outside the program. If the given above all conditions are false, it will execute the set of statements which are enclose in else part. Write a program to print greatest number among three numbers declare a number:=&a; b number:=&b; c number:=&c; begin if a>b and a>c then dbms_output.put_line ('a is the greatest number'); elsif b>a and b>c then dbms_output.put_line ('b is the greatest number'); else dbms_output.put_line ('c is the greatest number'); end if; end;

14. Loops: A loop can repeat a statement or statements multiple times. The following are different types of loops available in oracle. They are 1.Basic loop 2.While loop 3.For loop   1.Basic loop : The simplest form of loop is basic loop, which encloses a sequences of statements between the keywords loop and end loop. The basic loop allows the sequence of statements at least once, even if the condition is false. The exit statement can terminate the loop. Without exit statement the loop can be treated as infinite loop. The syntax is loop statement-1; statement-2; . . statement-n; exit when <condition>; end loop;

15.   2.While loop : In while loop, before each iteration of the loop, the condition is evaluated. It the condition is true, the sequence of statements is executed. If the condition is false or null, the loop is terminated and control resumes after the end loop statement. The syntax is while<condition> loop sequence of statements; end loop; write a program to print even number from 1 to 10 using while loop. declare n number(3):=0; begin while n <=10 loop n:=n+2; dbms_output.put_line (n); end loop; end;

16.   3. for loop It is a repetition control structure. It allows the user to execute code repeatedly for a fixed number of times. The syntax is , for counter in [reverse] lower_bound .. upper_bound loop sequence of statement; end loop; Write a program to print first 10 natural numbers using for loop declare begin for i in 1..10 loop dbms_output.put_line ( i ); end loop; end

17. Functions: A function must return a value to the calling environment. A function must have return clause in the header.   Syntax: create or replace function < function_name > [(parameter1 [mode1] datatype,Parameter2 [mode2] datatype, . . . )] return datatype is/as Pl/ sql block;   Here function_name --- name of the function Parameter -- name of pl / sql variable Mode – the type of the parameter. Only IN parameter Datatype --- datatype of the parameter Return datatype -- datatype of the return value  

18. Removing function : using DROP command we can remove function from data base. Syntax: drop function < function_name >; Ex: drop function emp_fun ; -- return area of rectangle create or replace function area_fun (l in number, b in number) return number is a number; begin a:=l*b; return a; end;   output: select area_fun (4,3) from dual;

19. Procedures: A procedure is a subprogram that can performs an action. A procedure can be stored in database.   Syntax : create [or replace] procedure < procedure_name > [parameter1 [mode] datatype1,Parameter2 [mode] datatype2,. . . ]Is/as Pl/ sql block; End [ procedure_name ];   Here procedure_name --- name of the procedure Parameter --- name of pl / sql variable Mode ---- in/out/in out (in --- default) Datatype --- data type of the parameter Pl/ sql block --- procedural body of the program. IN OUT INOUT Default mode Must be specified Must be specified Value is passed into subprogram Returned to calling environment Passed into subprogram, returned to calling environment Formal parameter acts as a constant Unintialized variable Initialized variable Can be assigned a default value Cannot be assigned a default value Cannot be assigned a default value Actual parameter can be a literal, expression, constant, or initialized variable Must be a variable Must be a variable

20.   Create a procedure, which receives a number and display whether it is odd or even. Create or replace procedure oddeven ( num in number) is a number(3); Begin a := mod(num,2); If a = 0 then dbms_output.put_line ( num ||' is even number'); Else dbms_output.put_line ( num ||' is odd number'); End if; End; -- output SQL> execute oddeven (100); E xecution of procedure : Procedure is executed from a SQL prompt as per follows and One can execute procedure from caller program also. Syntax : exec[ ute ] procedure_name (parameter list)   Removing Procedure : using DROP command we can remove procedure from data base. Syntax : drop procedure < procedure_name >; Ex: drop procedure emp_proc ;

21. Exceptions : In PL/SQL, errors are called exceptions. All error handling statements are placed in the EXCEPTION program block. Predefined and user defined exceptions are available in oracle.   Predefined Exceptions : Predefined exceptions are errors which occur during the execution of the program. The predefined exceptions are internally defined exceptions. Exception Description INVALID_CURSOR It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor. INVALID_NUMBER It is raised when the conversion of a character string into a number fails because the string does not represent a valid number. NO_DATA_FOUND It is raised when a SELECT INTO statement returns no rows. PROGRAM_ERROR It is raised when PL/SQL has an internal problem. ZERO_DIVIDE It is raised when an attempt is made to divide a number by zero.

22. write a program for no_data_found exception Declare ere cemp%rowtype ; v_empno emp.empno%type :=& v_empno ; Begin SELECT * into erec from emp where empno = v_empno ; dbms_output.put_line ('Employee Salary : '|| erec.sal ); Exception When No_Data_Found then dbms_output.put_line (' Entredempno is not found'); When Others then Null; End; Syntax:   Declare ... Begin ... Exception When Exception_1 then <Statement(s)>; When Exception_2 then <Statement(s)>; End;

23.   User-Defined Exceptions: user-defined exceptions should be explicitly specified. The user-defined exception must be declared in the declaration part of the PL/SQL Block and it can explicitly raised with the RAISE Statement.   write a program for user defined exception declare a number:=&a; b number:=&b; c number; zerodivide exception; begin if b>0 then c:=a/b; dbms_output.put_line (c); else raise zerodivide ; end if; exception when zerodivide then dbms_output.put_line ('b must be greater than zero'); end;

24. Implicit cursors : implicit cursors are declared by pl / sql implicitly for all DML and pl / sql statement. These cursors can return only one row. -- implicit cursor example declare v_empnoemp.empno%type :=& v_empno ; begin update remp set sal =sal+1000 where empno = v_empno ; if sql%notfound then dbms_output.put_line ('record not found'); end if; end; Explicit cursor attributes: % isopen – evaluates to true if the cursor is open % notfound – evaluates to true if the most recent fetch does not return a row %found --- evaluates to true if the most recent fetch returns a row % rowcount – evaluates the total number of rows returned by the cursor   Cursors: Cursor is private sql work area. There are two types of cursors available in oracle. They are Implicit and Explicit cursors

25.   Syntax: declare Cursor < cursor_name > is select statement. begin Open <cursor> loop Fetch <cursor> into < userdefinedvar > Exit when condition End loop; Close cursor;   Here cursor_name --- name of the cursor Open cursor --- the open statement executes the query Fetch – fetch the data from the cursor Close cursor – the close statement releases the active set of rows E xplicit cursors These cursors are declared by the user explicitly in declaration section of the pl / sql block for their requirements.

26. Example Program of Explicit cursors   write a program to display top5 salries by using explicit cursor declare cursor v_cur is select *from emp order by sal desc ; v_rec emp%rowtype ; begin open v_cur ; ---- open cursor loop fetch v_cur into v_rec ; --- fetch cursor into variable exit when v_cur%rowcount >=5 ; --- exit when rowcount >5 dbms_output.put_line ( v_rec.empno ||'-'|| v_rec.ename ||'-'|| v_rec.sal ||'-'|| v_rec.job ); end loop; end;

27. Triggers: A trigger is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema, or the database. Executes implicitly whenever a particular event takes place. Triggers can be executed, or fired, in response to the following events: A row is inserted into a table A row in a table is updated A row in a table is deleted Type of triggers :   Application trigger: Fires whenever an event occurs with a particular application Database trigger : Fires whenever a data event (such as DML) or system event (such as logon or shutdown) occurs on a schema or database

28. DML Trigger components: Row-Level Triggers : Row-level triggers execute once for each row in a transaction. Row-level triggers are the most common type of trigger; they are often used in data auditing applications. Row-level triggers are also useful for keeping distributed data in sync. Row-level triggers are created using the for each row clause in the create trigger command.   Statement-Level Triggers: Statement-level triggers execute once for each transaction. For example, if a single transaction inserted 500 rows into a table, then a statement-level trigger on that table would only be executed once. Statement-level triggers therefore are not often used for data-related activities; they are normally used to enforce additional security measures on the types of transactions that may be performed on a table. Statement-level triggers are the default type of trigger created via the create trigger command.  

29. Before : Execute the trigger body before the triggering DML event on table.   After : execute the trigger body after the triggering DML event on table.   Instead of Trigger : Executes the trigger body instead of the triggering statement. This is used for views that are not otherwise modifiable. Syntax:   Create [ or replace ] trigger [user.] < trigger_name > { before | after | instead of } { delete | insert | update [ of column [, column] …] } on [user.]{ Table | View } for each { row | statement } [ when (condition) ] PL/SQL Block

30. --create a trigger to restrict user does not insert data into emp create or replace trigger ins_emp before insert on emp begin raise_application_error (-20300,'can not insert data into emp table'); end; output: insert into emp ( empno ) values(300);

31. Packages : Package is group logically related PL/SQL types, items, and subprograms (procedures, functions, cursors, exceptions). It consist of Package Specification andPackage Body parts. A package specification can exist without a package body, but a package body cannot exist without a package specification. Advantages of Packages Modularity Easier application design Hiding information Added functionality Better performance Overloading

32. Syntax for package specification:   CREATE [OR REPLACE] PACKAGE < package_name > IS|AS public type and item declarations subprogram specifications END [ package_name ]; Syntax for package body:   CREATE [OR REPLACE] PACKAGE BODY < package_name > IS|AS private type and item declarations subprogram bodies END [ package_name ]; Here The REPLACE option drops and recreates the package body. Identifiers defined only in the package body are private constructs. These are not visible outside the package body. All private constructs must be declared before they are used in the public constructs. Here REPLACE option drops and recreates the package specification. Variables declared in the package specification are initialized to NULL by default. All the constructs declared in a package specification are visible to users who are granted privileges on the package.

33. Execute package: -- create a package to display sal and dname for given empno and deptno create or replace package emp_pak as procedure emp_pro ( v_empno number); procedure dept_pro ( v_deptno number); end; / create or replace package body emp_pak as procedure emp_pro ( v_empno number) is v_salemp.sal%type ; begin select sal into v_sal from emp where empno = v_empno ; dbms_output.put_line (' sal is:'|| v_sal ); end; procedure dept_pro ( v_deptno number) is v_dnamedept.dname%type ; begin select dname into v_dname from dept where deptno = v_deptno ; dbms_output.put_line ('department name is:'|| v_dname ); end; end; ---output execute emp_pak.emp_pro (7369); -- it will display sal for given empno execute emp_pak.dept_pro (10); --- it will display departname for given deptno