— NB.Sc, COMPUTER ———eerer Introduction, Structure of PL/SQL | @ PL/SQL Language Elements, Data types. Operators precedence, Control Structure. | # Create a PL/SQL Program, Iterative Control. @ Procedure, Function Database Triggers. SHORT ANSWER TYPE QUESTIONS | What is PL/SQL? | PL/SQL stands for Procedural Language/Structured Query naan Is | provided by Oracle asa procedural extension to SQL. SQL isa declarative language, In SQL, the statements have no control to the program and can be executed in any order. PL/SQL, on the other hand, is a procedural language that makes up for all the - missing elements in SQL. PL/SQL arose from the desire of programmers to have a language structure that wes more familiar than SQL's purely deciarative nature, Write about Advantages of PL/SQL? WV? 1. Procedural language support : PL/SQL is a development tools not only for data manipulation futures but also provide the conditional checking, looping or branching operations same as like other programming language. 2. Error handling : PL/SQL is dealing with error handling, it's permits the smart | way handling the errors and giving user friendly error messages, when the errors are encountered 3. Declare variable : PL/SQL gives you control to declare variables and access them within the block. The declared variables can be used at the time of query processing. | 4. Intermediate Calculation : Calculations in PL/SQL done quickly and efficiently without using Oracle engines. This improves the transaction performance. | 5. Portable application : Applications are written in PL/SQL are portable ii any Operating system. PL/SQL applications are independence program to run any computer. Write about short comings in SQL? SQL isa powerful tool for accessing the database but it suffers from some deficiencies | as follows a) SQL statements can be executed only one at a time. Every time to execute a SQL statement, a call s made to Oracle engine, thus it results in an increase in database overheads.y (je GOMPUTER SCIENCE. seit i SS : ee ee hile processing an § SS b) While Provessing an SQL statement If an error accurs, oracle generates ts his some times ants other meaningful ery orn own erTor Message, whic} to display some for that. difficult to under stand. [fa user wv nessage, SQL dows not have provision ¢) SQL is not able to do the Conditional ever means one cannot use the control are staten vy on RDBMS, this nents in SQL statements, ‘eedence in PL/SQL? The operations within an ex) their precedence (priority), from top to bottom. Operato, Write about operator pre; Pression are done in a particular order depending on Table below lists the operator's level of precedence ts listed in the same row have equal precedence Operators with higher precedence are applied first, but if parentheses are used, expression within innermost Parenthesis is evaluated first Order of Operations od operation ** NOT Ce 4 ‘ponentiation, logical negation | identity, negation multiplication, division addition, subtraction, concatenation comparison LIKE, BETWEEN, IN AND conjunction -| OR disjunction What is Procedure in PL/SQL? Write advantages of procedures and functions. A procedure is a subprogram that performs some specific task, and stored in the data dictionary. A procedure must have a name so that it can be invoked or called by any PL/SQL program that appears within an application, Procedures can take | Parameters from the calling program and perform the specific task. Before the procedure or function is stored, the Oracle engine parses and compiles the procedure or function. | Benefits of Procedures and Functions : Stored procedures and functions have Many benefits in addition to modularizing application development. | - Itmodifies one routine to affect multiple applications, Itmodifies one routine to eliminate duplicate testing. Itensures that related actions are performed together. Or not at all, by doing the activity through a single path. Ttavoids PL/SQL parsing at runtime by parsing at compile time Itreduces the number of calls to the database and database network traffic by bundling the commands. —I1B.Sc, COMPUTER SCIENCE - SEM3 102 $a | 6., What is the diff A aon) rence between procedure and function Function Procedure = subprogram that | A function is a subprogram that can accept parameters, compute a value and 1. A procedure ts can accept parameters, perform an action and return parameters return that value to caller A procedure may return no value } 2. A function must return only one value _ A function can return a value as direct output of the function call 4, Generally we use a function to compute 3. A procedure cannot return a value as direct output 4. Generally we use a procedure to perform an action a value | What are Packages? Write advantages of packages? ‘an be defined as a collection of related pro: sociated cursors and variables together as a unit in the package is a group of related procedures and functions as well as local procedures and A package ci gram objects such as procedures, functions, and a: database. In simpler term, a stored together and sharing common variables, functions. Advantages of packages : _ You can create package to store all related functions and procedures are grouped | together into single unit called packages. _ Package are reliable to granting a privileges. All function and procedure within a package can share variable among them. . Package are support overloading to overload functions and procedures. . Package are improve the performance to loading the multiple object into memory at once, therefore, subsequent calls to related program doesn't required to calling | physically I/O. 6. Package are reduce the traffic because all block execute all at once. er aes _ LONG ANSWER TYPE QUESTIONS Explain about Basic Structure of PL/SQL program? Structure of PL/SQL: PL/SQL is a 4GL (fourth generation) programming language. Itoffers all features of advanced programming language such as portability, security, data encapsulation, information hiding, etc. PL/SQL isa block structured language. This means a PL/SQL program ts made up of blocks, where block is a smallest piece of PL/SQL code having logically related statements and declarations. A block consists of three sections namely : Declare, Begin, and Exception followed by an End statement. We will see the different sections of PL/SQL block.gq, COMPUTER SCIEN — ee SCIENCE - SEM3 DECLARE Declarations of variables, constant: Ss elc., 10 be use in PL/SQL BEGIN f PL/SQL and SQL Executable statements EXCEPTION PL/SQL code to handle errors during execution period. END; . Declare Section : 1 tion : Declare section declares the variables, constants, Processes, functions, etc., to be used in the other parts of program. It is an optional section. | 2, Begin Section : It is the executable section. It consists of a set of SQL and PL’ SQL statements, which is executed when PL/SQL block runs It is a compulsory section. | 3. Exception Section : This section handles the errors. which occurs during | execution of the PL/SQL block. This section allows the user to define his/her own error messages. This section executes only when an error occurs. It is an optional section, 4. End Section : This section indicates the end of PL/SQL block. Every PL/SQL program must consist of at least one block, which may consist of | any number of nested sub-blocks. E.g. ‘om the user. PL/SQL code to print sum cof two numbers taken fr | SQL > SET SERVEROUTPUT ON: SQL > DECLARE ~- taking input for variable a ainteger : = & a: --taking input for variable b b integer := &b: c integer : BEGIN c:zatb; dbms_output. put_line ('Sum of a |'and | biis=‘e):A [WB.se. compuTER SCIENCE - SEM a |[104]| 2 9 ] Briefly explain abottt Lexical units in PL/SQL | characte | Lexical Units: A tine of PL/SQL program contains groups oF eharacters knoys, | as lexteal units, which can be classified as follows 1. Delimiters, 2. Identifiers, 3, Literals, 4, Comments 1. Detimiters A delimiter isa simple or compound symbol pea Special Meaning to PL/SQL. Simple symbol consists of one character, while compoung symbol consists of more than one character. PL/SQL supports following simple symbol delimite t—#1=> <5 %' (@i" Compound symbol delimiters legal in PL/SQL are as follows : >! a => 2. Identifiers : Identifiers are used in the PL/SQL programs to name the PL SQi Program items as constants, variables, cursors, cursor variables. subprograms, ete PL/SQL Identifiers follow the following rules : 1. Identifiers can consists of alphabets, numerals, dollar signs, underscores and number signs only. An identifier must begin with an alphabetic letter optionally followed by one or more characters (permissible in identifier). - An identifier cannot contain more than 30 characters. 4. An identifier cannot be reserved word. Some of the valid identifiers are as follows : A, Al and so on 3. Literals : A literal is an explicity defined character, string, numeric, or Boolean value, which is not represented by an identifier. i) Numeric Literals ; A numeric literal is an integer or a real value. An inte literal may be a positive, negative, or urisigned whole number without a decimal point. Some examples of integer numeric literals are as follows : 100 006 -10 0 +10 Areal lieral is a positive, negative, or unsigned whole or fractional number with | a decimal point. Some examples of real integer literals are as follows 0.0 -19.0 3.56219 +43.99 © 7 456 Numeric literals can also contain exponential numbers. Some examples of | exponential numeric literals are as follows : | 7E3 2.0E-3 3.141591 -2E33 -8.3e-2 | Where, E stands for "times ten to the power of” ii) Character Literals + A character literal is an individual character enclosed bY single quotes. Character literals include all the printable characters in the PL/SQL | character set: letters, numerals, spaces, and special symbols. Some examples 0! | character literals are as:follows | aqp.Se. COMPUTER SCIENCE -SEM3 |[t05 (9S SEM IL 105 : - Ae SG — PI. SQL 1s case ser sive within character literals A string literal is enclos clers, Some examples ‘Good Moming!' 'TATA INFOTI iii) String Literals of one or more char: consist ed within single quotes and may of String literals are as follows ECH LID! All string literals are of character data type. PL/SQL is case sensitive within str ing literals. iv) Boolean Literals : Boolean literals are the predefined values TRUF, FALSE. | and NULL. Keep in mind Boolean literals are values, not strings. For example a condition: 1f (x=10) is TRUE only for the value of x equal to 10. for any other value of x it is FALSE and for no value of x it is NULL. 4. Comments : Comments are used in the PL/SQL program to improve the readability and understandability of a program. A comment can appear anywhere 1n the program code. The compiler ignores comments. A PL/SQL comment may be a | single-line or multiline. i Single-Line Comments Single-line comments begin with a double hyphen (-) anywhere on a line and extend to the end of the line E.g. — start calculations i) Multiline Comments Multiline comments begin with a slash-asterisk (/*) and end with an asterisk-slash (*/), and can span multiple lines. E. * Hello World! This is an example of multiline comments in PL/SQL * :xplain about various datatypes using in PL/SQL? ~ MO © Every constant and variable has a datatype. A datatype specifies the space to be reserved in the memory, type of operations that can be performed, and valid range of values. PL/SQL supports all the built-in SQL datatypes. Apart from those datatypes, PL/SQL provides some other datatypes. Some commonly used PL/SQI | datatypes are as follows : 1. NUMBER : The NUMBER datatype to store fixed or floating point number of Virtually any size. You can specify precision, which is the total number of digits, and | scale, which determines where rounding occurs. | Syntax : NUMBER [(precision, scale)] | You cannot use constants or variables to specify precision and se ale; you must use an integer literals. | 2. CHAR: You use the CHAR datatype to store fixed-length character d | CHAR datatype takes an optional parameter that lets you specify | up to 32767 bytes. | Symray » CHAR[(maximum_length)] ata. The amaximum length[WB.Se. COMPUTER SCIENCE A. ~*~ PUSaL 106 3. VARCHAR 2: You use the VARCHAR2 datatype to store variable-lengy character data. The VARCHAR2 pe takes a required parameter that lets You specify a maximum length up to 32767 bytes: Syntax » VARCHAR2 (maximum _length) i 4. DATE: You use the DATE datatype to store fixed-length data values. The DATE datatype takes no parameters. Valid dates for DATE variables include January 1.4712 BC to December 31, 4712 AD. S. BOOLEAN : One of the mostly used datatype is BOOLEAN. A BOOLEAy, | datatype is assigned to those variables, which are required for logical operations, 4 BOOLEAN datatype variable can store only logical values, i.e., TRUE, FALSE, NULI 6. “Type: The %TYPE attribute provides the datatype of a variable or database | column. In the following example, %TYPE provides the datatype of a variable balance NUMBER(8,2): minimum_balance balance% TYPE; In the above example PL/SQL will treat the minimum_balance of the same datatype as that of balance. i.e, NUMBER(8.2). 7. YRowtype : The YROWTYPE attribute provides a record type that represents a row in a table (or view). The record can store an entire row of data selected from the table. E.g. emp_rec is declared as a record datatype of emp table. emp_rec can store a row selected from the emp table. emp_rec emp%ROWTYPE; Briefly explain about Variables and constants in PL/SQL? Variables and Constants : Variables and constants can be used within PL/SQL | block, in procedural statements and in SQL statements. These are used to store the values. As the program executes, the values of variables can change, but the values of constants cannot. Variables and constants are declared in the Declaration section of PL/SQL block. These can be any of the SQL data type like CHAR, NUMBER, DATE, ete. 1. Variables Declaration : The syntax for declaring a variable is as follows identifier datatype; E.g. DECLARE Name VARCHAR2(10), Age NUMBER(2); Joining date DATE;- ‘cg SONPUTER SCIENCE. SEM |[T07][ pusal_ nitializing the V; — : wt 8 ariable : By default variables are antialized t@ NUE rime fo declaration. If we want toin 7e + sy nlx jyould be as follows; itl the yale y same ae at the dentifier datatype = value; Or: jgentitier datatype DEFAULT value; Eg: Joining_date DA’ ‘OLIULY-99§ (ar) DEFAULT ‘01 JULY-99', 3, Declaring Constants : Declaration of constant is similar 10 declaration of variable, except the key-word CONSTANT precedes the datatype and it must be initialized by some value. The syntax for declaring a constant 18 aS follows: identifier CONSTANT datatype := value; Eg. Age_limit CONSTANT NUMBER 5, Explain about Conditional control statements in PL/SQL? Joining_date DAI 4, Conditional Control : A conditional control structure tests a condition to find out whether it is true or false and accordingly executes the different blocks of SQL statements. Conditional control is generally performed by IF statement. There are three forms of IF statements. 1. IF-THEN 2, IF-THEN-ELSE 3. IF-THEN-ELSIF 1. IF-THEN : Itis the simplest form of IF condition. The syntax for this statement isas follows : IF condition THEN Sequence of statements END IF; E.g. DECLARE AGE integer: IF AGE DBMS_OUTPUT. PUT_LINE (‘you are eligible to vote’); END IF: END; 1 2, [R-THEN-ELSE : The second form of IF statement adds the keyword E followed by an alternative sequence of statements, as followsB.Sc. COMPUTER SCIENCE - SEM 3 ———— LWB.Se. COMPUTER SCIENCE -SEM3 |[108 ][____ | IF condition THEN | sequence_of statements | ELSE sequence_of_statements2 END IF, E.g. DECLARE A integer; B integer; | BEGIN B= &B: IF A>B THEN DBMS_OUTPUT.PUT_LINE (‘A is Big’): ELSE | DBMS_OUTPUT.PUT_LINE ('B is Big’); END IF; additional conditions, as follows : IF condition] THEN sequence_of_statements] ELSIF condition? THEN sequence_of_statements2 ELSE sequence_of_statements3 i END IF; E.g. DECLARE Qty integer; | Pcode _ integer; | amt real; | BEGIN | Qty = &Qty; Pcode := &productcode; | IF Peode = | THEN amt := Qty * 17.50; ELSIF Peode = 2 THEN amt *= Qty * 2.75; ELSIF Pcode = 3 THEN LSIF : The third form of IF statement selects an action from several mutually exclusive alternatives. It uses the keyword ELSIF (not ELSEIF) to introduceSe COMPUTER SCIENCE. SEM3 |[To9 a | amt = Qty *4.80; ELSIF Peode = 4 THEN amt: = Qty * 6.20; ELSE amt := Qty * 12.00; END IF; DBMS_OUTI DBMS. PUT.PUT_LINE (‘Amount =" |j amt); Explain about CASE statement in PL/SQL with examples? onditions. CASE PL/SQL CASE statement comparing one by one sequencing © WHEN statement attempt to match expression that is specified in one or more condition. Syntax : CASE selector WHEN value-1 THEN statement-1; WHEN value-2 THEN statement-2; ELSE statement-3; END CASE Example Code : SQL>DECLARE anumber := 7; BEGIN CASE a WHEN | THEN DBMS_OUTPUT.PUT_LINE(‘value ips WHEN 2 THEN DBMS_OUTPUT.PUT_LINE(‘value 2); ELSE DBMS_OUTPUT.PUT_LINE(no matching CASE found’); END CASE; END; i Example Result no matching CASE foundCOMPUTER SCIENCE - SEM3 a) END Loop DECLARI no NUMBER 0. SNUMBER 0 BEGIN WHILE no 10 LOOP no not] Sostno. END Loop, DBMS OUFPULPUT EINECSum 2" || s): END. Example Result Sum: 45 3. FOR-LOOP : FOR loops iterate over a specified range of integers. The range 1S part of izerarion selieme, which 1s enclosed by the keywords FOR and LOOP. A double dot(..) serves as the range operator. Syntax : FOR counter IN [REVERSE] lower value ..upper_value LOOP sequence_of_statements END LOOP: The range is evaluated when the FOR loop is first entered and is never re-evaluated. The sequence of statements is executed once for each integer in the range. After every iteration. the loop counter is incremented. The REVERSE clause is used if starts from upper_value and decremented by 1 until it reaches the lower_value. E.g. : DECLARE no integer: BEGIN FOR no IN 1 .. 5 LOOP DBMS_OUTPUT.PUT_LINE(‘Iteration :' || no); END LOOP: END Example Result Iteration : ation ay 8 Write about Foie ee sat nts in PL/SQ Sequential con Sequen s Heontrol state a I Statements are control yeu! iteration Loop. i) goto statement 1) continue statement i) goto Statement : The sequential control unconditionally passes the contra! (° specified unique label; i ean be inthe forward direction or inthe backward direction For sequential control GOTO statement is used, Overuse brGO FO statement may merease the complexity, thus as far as possible avoid the use of GOO statement The syntax is as follows GOTO label; <> Statement DECLARE iinteger; BEGIN FOR i IN 1..5 LOOP dbms_output.put_line(i); IF i=4 THEN GOTO label]: END IF; END LOOP: <> DBMS_OUTPUT.PUT_LINE (‘END of the loop’): END; / Example Result 1 2 3 4 End of the loop ii) CONTINUE Statement : CONTINUE § current loop iteration and next iteration iterate as normal only skip matched condition I atement unconditionally skip the Syntax IF condition THEN CONTINUI oe —_— |ut | » —_— — -- Bsa) [WB.Sc. COMPUTER SCIENCE -SEM9 |[174 ]| = END IF; Example Code DECLARE no NUMBER :=0; BEGIN FOR no IN 1... 5 LOOP IF i=4 THEN CONTINUE; END IF: DBMS_OUTPUT.PUT_LINE(Iteration : ‘|| no); END LOoP; END; / Example Result Iteration 1 Iteration 2 Iteration 3 Iteration 5 Briefly explain about procedure in PL/SQL with examples? Procedure : A procedure is a subprogram that performs some specific task, and stored in the data dictionary. A procedure must have a name, so that it can be invoked or called by any PL/SQL program that appears within an application. Defining and Creating Procedures : A procedure consists of two parts : specification and body. The specification starts with keyword PROCEDURE and ends with parameter list or procedure name. The procedures may accept parameters or may not. Procedures that do not accept parameters are written parentheses. The procedure body starts with the keyword IS and ends with keyword END The procedure body is further subdivided into three parts: 1, Declarative part which consists of local declarations placed between keywords IS and BEGIN. 2. Executable part, which consists of actual logic of the procedure, included between keywords BEGIN and EXCEPTION. At least one executable statement is a must in the executable portion of a procedure. Even a single NULL statement will do the job. 3. Error/Exception handling part, an optional part placed between EXCEPTION and END. ‘The syntax for creating a procedure is follows : CREATE OR REPLACE PROCEDURE procedure_name [(argument {IN, OUT. IN OUT} data type.....)] {ISAS}i B.Sc. COMPUTER SCIENCE - SEM3 |[115 — [local variable declarations] BEGIN executable statements EXCEPTION exception handlers END [procedure name]: Create : Creates a new procedure, if a procedure of same name already exists, it gives an error. Replace : Creates a procedure, if a procedure of same name already exists, 1 replace the older one by the new procedure definition Argument : It is the name of the argument to the procedure. IN : Specifies that a value for the argument must be specified when calling the procedure. OUT : Specifies that the procedure pass a value for this argument back to Its calling environment after execution. IN OUT : Specifies that a value for the argument must be specified when calling the procedure and that the procedure passes a value, for this argument back to its calling environment after execution. Datatype : It is the unconstrained datatype of an argument. It supports any data type supported by PL/SQL. Procedure example : In this example the procedure to raise the salary of the employee the name of the procedure is raise_sal we have emp table having employee information. . SQL> select * from emp: EMPID _EMPNAME SAL 101 KARTHIKEYAN — 5000 102 ANAND 2500 103 RAJA 1900 1. Creating a Procedure : SQL> edit raise_sal CREATE OR REPLACE PROCEDURE raise_sal [EID IN EMP EMPID%TYPE] Is BEGIN UPDATE EMP SET SAL = SAL * 1.25 WHERE EMPID END raise_sal; SQL > @raise_sal procedure created.[WB.se. COMPUTER scieNcE.sema |[1t6][ PUsaL . Briefly explain about Function in PL/SQL with example? 2. Executing / Invoking a Procedure : The syntax used to execute a procedure depends on the environment from which the procedure is being called. From within SQL PLUS. a procedure can be executed by using the EXEC UTE command SQLSEXCUTE ratse_sal (101). PL/SQL procedure successfully completed. SQL> SELECT * FROM EMP: EMPID — EMPNAME SAL 101 KARTHIKEYAN 6250. | 102 ANAND 2500 103 RAJA 1900 3. Removing a Procedure : To remove a procedure completely from the database. following command is used DROP PROCEDURE: SQL> DROP PROCEDURE raise_sal; Procedure dropped. A function isa set of PL/SQL statements you call by name. ‘A function is similar to procedure except that it must return one and only one value to the calling program. Besides this a function can be used as part of SQL expression, whereas the procedure cannot. The syntax for defining a function is given below: CREATE OR REPLACE FUNCTION functionname [(argument {IN, data type, .n)] RETURN datatype {IS.AS} {local variable declarations] BEGIN | executable statements; EXCEPTION | exception handlers; END [functionnitme-— Create : Creates a new function, if a function of same name already exists, It gives an error. Replace : It replace the older one by the new function definition. Function name : The name of the function you want to create. Argument : The name of an argument to the function. : IN ; Specifies that the procedure pass a value for the argument must be specified when calling the function. Datatype : Datatype is the type of data of a argument the data type.IL B.Sc. COMPUTER SCIENCE - SEM3 [ 117 PL/s@L Return datatype : It is the datatype of function's return value. It can be any PL/ SQL datatype. Is : When you want to declare local variables As: When you want to declare other variables. A function has two parts : function specification and function body. The function specification begins with keyword FUNCTION and ends with RETURN clause which indicates the datatype of the value returned by the function. Function body 1s enclosed between the keywords IS and END. Function example : We have emp table having employee information. SQL> select * from emp; . | EMPID | EMPNAME SAL 101 ~ KARTHIKEYAN 5000 102 ANAND 2500 103 RAJA 1900 1. Creating a function: SQL> edit get_sal CREATE OR REPLACE FUNCTION get_sal [EID IN EMP.EMPID%TYPE] RETURN NUMBER IS EMPSAL EMP.SAL%TYPE : BEGIN SELECT SAL INTO EMPSAL FROM EMP WHERE EMP ID=EID; RETURN (EMPSAL); END; / 2. Execute a function : After write the PL/SQL function you need to execute the function. SQL> @ get_sal Function created. SQL> SELECT GET_SAL(102) FROM DUAL; GET_SAL(102); > 2500 3. PL/SQL program to Calling Function : This program call the above define function with pass employee number and get that employee name. SQL> edit funWess. conPuren science: se [118 DECLARI no number -=&no. PLS, | salary number; | BEGIN salary = get sal(no); | dbms_output.put_hine (salary "||" "| salary): end, SQL ACH Enter value forno=102 salary = 2500 PL/SQL procedure suece 4. Removing a Funetion : To remove a function, use following command DROP FUNCTION : E.g. SQL>DROP FUNCTION funl; Funetion dropped. {ually completed Explain about parameters in subprogram? Parameters : Parameters are the link between a subprogram code and the code | calling the subprogram. It depends on how the parameters are passed to a subprogram, Hence it is absolutely necessary to know more about parameters, theit | modes, their default values, and how subprograms can be called without passing all the parameters. Parameter Modes : Parameter modes define the behavior of normal parameters of subprograms. There are three types of parameter modes : 1. IN 2. OUT 3. IN/OUT 1. IN Mode : IN mode is used to pass values to the called subprogram. In short this is an input to the called subprogram. Inside the called subprogram, an IN parameter acts like a constant and hence itcannot be assigned a new value Itis important to note that IN mode is thg default mode of the formal parameters. 2. OUT Mode : An OUT parameter returns a value back to the caller subprogram. Inside the subprogram, the parameter specified with OUT mode acts just like any locally declared variable. Its value can be changed or referenced in expressions. just like any other local variables, 3. IN/OUT : An IN/OUT parameter performs the duty of both IN parameter as well as OUT parameter. It first passes input value (thorugh actual argument) 0 the called subprogram and then inside subprogram it receives a new value which will be assigned finally to the actual parameter118:Se. COMPUTER SCIENCE. SEM [si9]] eae : PL/SQL a PLISQL Procedure Eyam, oe le: We have eny p table having employee information, SQL> SELECT * FROM 1 — >MP; EMPID EMPNAME. gay, 101 SREE 5000 102 ANAND 9509 103 RAJA 1900 1. Create PROCEDURE : SQL> Edit pro! emp1%rowtype) IS BEGIN SELECT * INTO temp FROM emp! WHERE empid = no; END; 2. Execute PROCEDURE + After write the PL/SQL Procedure you need to execute the procedure, SQL>@prol Procedure created. 3. PL/SQL Program to Calling Procedure : This Program call the above define procedure with pass employee number and get that employee information. SQL> Edit pro DECLARE temp emp%rowtype; no number; BEGIN prol (no, temp); dbms_output.put_line(temp.empid ||' ‘ll | temp.empname ||' ‘l temp.sal |’ |); END; / SQL>@pro Enter value for no :101 1 SREE 5000| | | | Ff ee [WB.Se. com UTER SCIENCE - SEM |[120 ||_ Par) What ts a Pack: ” aves with exd ss? ge? Briefly explam about packages with exampl Packages : \ package can be defined as a collection of related be pram objet ‘i variables together a such as procedures, functions, and associated cursors and variables together as a unit the database Parts of Package : A Package has (wo parts. They are 1. Package specification 2. Package body 1, Package Specification : This contain the list of variables, constants, functions, Procedure names which are the part of the package. PL/SQL specification are pubhie declaration and visible toa program Defining Package Specification Syntax CREATE [OR REPLACE] PACKAGE package_name IS | AS {variable_declaration...] [constant_declaration...] [exception _declaration...] [cursor_specification...] [PROCEDURE procedure_name [(argument {IN, OUT, IN OUT} datatype ...)] ] [FUNCTION function_name [(argument IN datatype ...)] | RETURN retumm_datatype ] END [package_name]; Package Body : This contains the actual PL/SQL statement code implementing | the logics of functions, procedures which ate you already before declare in specification”. "Package | Creating Package Body CREATE [ OR REPLAC IS | AS yntax PACKAGE BODY package_name | | [private_variable_declaration ...] | [private_constant_declaration ...] BEGIN | [PROCEDURE procedure_name [Clist of arguinent)| IS | AS variable declarations Heer eeeEmp_No EMP_NAME __EMP_SAL 101 Rohith 5000 102 Rohan 6000 103 Sree 7000 program 1U" OMPUTER SCIENCE. SEM |[ 137 fee constant declarations; BEGIN code of procedure | END ! | [FUNCTION function name [list of arguments} RETURN retum datatype IS | AS variable declarations: constant declarations: BEGIN Function code END ] [Similarly other functions & Procedures] ] END packagename: Referencing Package Subprograms : Once the package body is created with all members as public, we can access them from outside the program. The syntax for referencing any member object is as follows: . To reference procedures we have to use the syntax as follows: EXECUTE .; Removing a Package : A package can be dropped from the database just like any other table or database object. The exact syntax of the command to be used for dropping a package is DROP PACKAGE < PACKAGE_NAME>; PL/SQL Package example: We are create our own package using this reference example, We have emp table having employee information SQL> select * from emp, i) Package Specification Code : Create Package specification code for defining procedure, function IN or OUT parameter and execute package specification——p fr [NB.8c. compusspace sE 1 a, [COMPUTER SCIENCE Sema J[122 ][____PUsai) PEE eee eee eee eee eee eee CREATE or RELACE PACKAGE pkg! IS | AS PROCEDURE pro! (no in number, info out emp%rowtype): FUNCTION fun! (no in number) RETURN varchar2; END: il) Package Body Code : Create Package body code for implementing procedure or funetion that are defined package specification. Once you implement execute this program, CREATE or REPLACE PACKAGE BODY pkg] | Is PROCEDURE prol (no in number, info out emp%rowtype) Is BEGIN SELECT * INTO temp FROM empl WHERE eno = no: END; FUNCTION funI(no in number) return varchar? Is name varchar2(20); BEGIN SELECT ename INTO name FROM emp] WHERE eno = no; RETURN name; END; END; / iii) PL/SQL. Program calling Package : Now we have a one package pkgl, to call package defined function, procedures also Pass the parameter and get the retum result, pkg_prg.sq] DECLARE no number := &no; name varchar2(20); BEGIN pkg l.prol(no, info); dbms_output.put_line(‘procedure Result’); dbms_output.put_line (info. emp_no|| 4] infoemp_name ||") info,emp_sal ||" "| \);71.Sc. COMPUTER SCIENCE -SEMS |[123 PL/SQL 13. dbms_output. 7 x put_line('Function Result); name := pkg fun! (no); 2 dbms_output.put_line(name); END, i, iv) PL/SQL Program Res : ult : Now execut e cree program to asking which user information ean eS eae acannon 10 get, you put user id and give SQL>@pkg_pre Enter value for no =102 Proceduure Result 102 Rohan 6000 Function Result Rohan PL/SQL procedure successfully completed. vy) PL/SQL Package Drop You can drop package using package DROP statement, Package Drop Syntax : DROP PACKAGE package_name; E.g. SQL > DROP PACKAGE pkg]; package dropped. rsors in PL/SQL with examples? je reserves for intel for oracles reserved are called cur: ¢ work areas and to called the Briefly explain about cut Cursor is the work area which Oracl statements. This work area is private Cursor is a PL/SQL construct that al access their stored information. The da Active dataset. These are two types of cursors in o! 1, Implicit cursor mal processing of SQL ‘SOF. llows us to name thes: ta i.e., stored in the cursor 1S racle. 2, Explicit cursor 1. Implicit Cursors ¢ PL/SQL implicitly declares a cursor for every SQL DML INSERT, DELETE, UPDATE, and SELECT statement that is nif the statement processes 2 Sin gle 'y declared cursor, ve! g the most recent cursor or the cursor associated d SQL statement, as the "SQL" cursor. Cursor tion about the most recently executed SQL statement, such as not a part of an explicit row. PL/SQL allows referencin: with the most recently execute attributes are used to access informa! statement, USINE SQL cursor. Jicit Cursor Attributes ¢ These attributes are associated with the implicit ROL cursor and can be accessed by appending the attribute name to the implicit r name (SQL) Syntax to use cursor attribute 1s as follows = ur:[nB.se. COMPUTER SCIENCE -SEM9 |[124 ]| | SQL “ In PLISQL impheit cursor has 4 attributes. Phey are | i Attribute Deseription | ol "ol SOPEN tas used to determine if a cursor is already open. Hers alway a false because oracle automatically closes implicit cursor | after executing its associated SQL statements | SQL FOUND Itis true if the most recently executed DME. statement as successful SQL"oNOTFOUND — Itis true of the most re Was not successful. rows that SQL°%SROWCOUNT «This attribute is used to determine the number of rows tha z are processed by an SQL statements. E.g. Consider a PL/SQL. code to display message to check whether the record is delete or not DECLARE BEGIN Delete from emp where empno = &empno: IF SQL%FOUND then DBMS output.put_Line (‘Record deleted’); ently executed DML statement Else DBMS output.put_Line (‘Record is not deleted’); END IF; END / 2. Explicit Cursor : Explicit cursors are declared by the user and used to proc query results that return multiple rows. Multiple rows retummed froma query forma. set called an active set. A PL/SQL program opens a cursor to process rows returned b then closes the cursor. query and Steps to create a cursor : | 1. Declare the cursor 2. Open the cursor 3. Passing parameters to cur: | 4 5 . Fetch data from the cursor Close the cursor. 1. Declare the cursor : In PL/SQL a cursor, like a variable, is declared in the LARE section of a PL/SQL block or subprogram. A cursor must be deckared! hefore it can be referenced in other statements, A cursor is defined in the declarative part by naming itand specifying a SELECT query to define the aetive set~_ Syntax : CURSOR < mBSe.COMPUTER SCIENCE SEMa |[125]/ _——SCSCSC« [nB.Se: COMPUTER SCIENCE. Sema |[125 ][ - Cursor name> IS. — SELECT - statement» OR | CURSOR < cursor name [parameter 18 “SELECT statement WHERE < column name> = parameter | 2. Open the Cursor : After declaration, the cursor 1s opened with an OPEN | Statement for processing rows in the cursor. The SELECT statement associated | with the cursor is executed when the cursor is opened, and the active set associated | with the cursor is created, Syntax of OPEN statement is OPEN < cursor_name: as follows, 3. Passing Parameter to Cursor : Parameters to a parameterized cursor can be | Passed when the cursor is opened, For example. given the cursor declaration CURSOR Mem detail 4. Fetch Data from the Cursor : After a cursor has been opened, the SE. (MType VARCHAR2) IS SELECT... statement associated with the cursor is executed and the active set is created. To | retrieve the rows in the active set one row at a time, the rows must be fetched | individually from the cursor. After each FETCH statement, the cursor advances to the next row in the active set and retrieves it. Syntax of FETCH is: FETCH INTO. , . Where variable_name is the name ofa variable to which a column value is assigned 5. Close the Cursor : Afier processing the rows in the cursor, it is released with the CLOSE statement. CLOSE ; Explicit Cursor Attributes : It is used to access useful information about the Status of an explicit cursor. These attributes can be accessed in PL/SQL statements only, not in SQL statements. Syntax to access an explict cursor attributes: % In PL/SQL explicit cursor has 4 attributes. They are Attribute “ISOPEN %FOUND “%NOTFOUND %ROWCOUNT E.g. DECLARE Description It Evaluates to true if the cursor is opened otherwise evaluates false. It evaluates to true if the last fetch retumed a row or to false if no row is returned, It evaluated to true if the last fetch failed to retum a row because the active set is empty or evaluates to false if, the last fetch returned a row. Returns the no.of rows fetched,be 'B.Se. COMPUTER SCIENCE - SEM 3 [#28] ~ : ere deptnoe — 10: Cursor CLs select empnw, ename, pob tron enyp where dep Ree Clos rowtype BEGIN open CL; Loop Fotoh Ch into Ree: Ext when CL sNOTEOUND, DEMS OUTPULPU LT LINECEMPNO™* |] Ree.cmpno); DBMS OUTPULPUT LINECENAME®" |] Ree.cname); DBMS _OULPUPPUT LINECIOB "|| Ree job): END Loop: close CL: END; Ne Vokes automatically whenever a specified event oc s stored into database and invokes repeatedly, when specific condition T automatically assoer ted with DME. statement, when DML. st eXecule tr Timplicity execute © trigger using the CREATE TR hieitly fire DML statement parts of Trigger : A trigger Atr fatement Rstatement, I trigger activated, and 1f trigger deactivated can't fire. three baste parts ering event or statement eT restriction ger action |. Trigger Event or Statement : s t, database \ triggering event or statement is the SQL event, or user event like update, delete, insert, etc. that causes ‘0 be fired. It also specifies the t able to which the tr igger Is associated. > Trigger Restriction : We ean speeity the condition inside trigger to when aver ts fire. *. Trigger Action : When the trigging SQL statement is execute, trigger tomatically call and PL/SQL eae | Create Trigger Syntax t ¢ trigger syntax is as follows: ATE (OR REPLACE] TRIGGER s-- 1] ON Stable_name> AS} “old_name> | NEW TAS] ‘Fi EACH Row] EIOR REPLACE] TRGGER given name, already have Overwrit name, - BEFORE) jy PER» before, AFTER Indicates when the trigger get fire, BEFORE trigger execute Te When Statement execute before. AFTER tigger execute after the Statement execute, trigger name : Create a trigger with the le the existing trigger with defined same NR we B [INS ERT, UPDATE, DELETE [COLUMN NAME...] : Performing trigger event. You can di by OR keyword ON table_name : Define the table name t 5. Referencing [OLD AS OLD | NEW AS NEW ]: Give referencing to a old new values of the data, ‘old means use existing row to perform event and : new Means use executing new row to Perform event, 6. FOR EACH ROW | FOR EACH STAT EMENT: ‘Trigger must fire when each Tow gets Affected (ROW Trigger). and fire only once when the entires sql statement is execute (STATEMENT. Trigger) 7. WHEN Condition ; Optional . Use only for row level trigger. Trigger fire when specified condition is satisfy. PL/SQL Trigger Example To create a trigger for the emp table which makes the entry in ename column in | upper case. ; Create or replace trigger upper-trigger before insert or update of ename On Emp For each row BEGIN 2 new.ename END: Determines the lefine more than one Miggering event separated = UPPER ( :new. name); ing Triggers : Triggers can be dropped like tables using the drop trigger eee ee trigger command removes the trigger structure from the command, The d 8 data base’)1 B.Sc, COMPUTER SCIENCE -SEM3 |/1 28 |[_ Syntax DROP TRIGGER « trigger name Example ie SQL> DROP Trigger Upper Trigger Trigger dropped Briefly explain various types of Triggers in PL/SQL? Types of Triggers : Type of trigger firing, level at which a trigger Is oe and the types of events form the basis classification of triggers into different categories, The broad classification of triggers is as shown below. 1. On the Basis of Type of Events 1. Triggers on System events 2. Trigger on User events Il. Onthe Basis of the Level at which Triggers are Executed 3. Row Level Triggers 4. Statement Level Triggers IIL On the Basis of Type of Trigger/Firing or Triggering Transaction 5. BEFORE Triggers 6. AFTER Triggers 7. INSTEAD OF + Triggers on System Events : System events that can fire triggers are related to instance startup and shutdown and error messages. STARTUP triggers fire when the database is opened by an instance SHUTDOWN triggers fire just before the server starts shutting down an instance. + SERVERERROR triggers fire when a s pecified error occurs, or when any error occurs if no error number is specified. DB_ROLE_CHANGE triggers fire when a role transition (failover or switchover) | occurs in a Data Guard configuration. | The DBMS_AQ package is one example of using database triggers to perform certain actions. For example, a database shutdown trigger is defined at the database level: CREATE TRIGGER register_shutdown | ON DATABASE SHUTDOWN |- eee (GSE SOMPUTER SCIENCE. SEM 3 [aze7 pasa BEGIN DBMS_AQ-ENQUEUFY END 2. Triggers on user events : User events th + User logon and « DDL ments (CREATE. ALTER. and DROP) « DMI INSERT. DEL, 3. Row Level Triggers : A row trae. update H set grade = NULL where New ID =H end: ement statement affects. eve: deletes several rows tr once. E.g. CREATE OR REPLACE TRIGGER bet BEFORE DELETE OR UPDATE OR INSERT ed only ON customer begin IF to_char (syscate 08" OR to_char (sysdate. 8’ THEN pplicatro7_¢Migger te any Updates he 8AM or after 6PM, r. e trigger act 5. Before Triggers : BEFORE triggers execute the trigeer 3° Miggering statement is executed Trigger Type Combinations : You can create 2 ion before the types of row and statement + BEFORE statement trigger Before executing the triggering statement, the trigger action Is TUN + BEFORE row trigger Before modifying each row affected by the triggering statement and before checking appropriate integrity constriants, the trigger action Is Tun E.g. We can define a BEFORE trigger on the passengers, detail table tha ie fired before deletion of any row. The trigger will check the system date and if the date is Sunday, it will not allow any deletion on the table. SQL> select * from passenger_det; SQL> CREATE OR REPLACE TRIGGER passeneger_bef_del BEFORE DELETE 2 ON passenger_det 3. DECLARE 4 SUNDAY_EXP EXCEPTION; 5 BEGIN 6 IF TO_CHAR(SYSDATE. 'DY’)..'SUN' THEN ti 8 . PASSNo NAME ciTy | 1 sree Amaravathi | 2 anand Madurai | 3 Raja Nagercoil RAISE SUNDAY_EXP; END IF; EXCEPTION 10 WHEN SUNDAY_EXP THEN 1] RAISE_APPLICATION ERROR(-20001, ‘can not DEL 12 SUNDAY’); 13 END; 14/ Trigger cree TE on |ao 6. AFTER Trigers : AFTER tri statement Is executed, PL/sal | Bers execute the trigger action after the triBEerIne | Trigger type combinations + AFTER statement trigge applying any deferred integ You can crate 2 types of row and statement (1EBET> tr: Afler executing the triggering statement and rity Constraints, the trigger action 1s run + AFTER row trigger : Afier modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is run for the current row provided the trigger restriction was not violated. Unlike | BEFORE row triggers, AFTER row triggers lock rows. | E.g. We can define an AFTER trigger on the reserv_det table that gets fired every time one row is deleted from the table. This trigger will determine the passenger_id of the deleted row and subsequently delete the corresponding row | from the passengers_det table with same passenger_id. SQ> select*from RESERV_DET: PASS FLIGHT RESERVE_STATVS CLA | 17 R17 RESERVED F | 101 R101 WAITING F | 130 R130 CANCELLED Ss SQL> CREATE OR REPLACE TRIGGER PASSENGER_DEL_AFT AFTER DELETE ON RESERV_DET 2 FOR EACH ROW 3 BEGIN 4 DELETE FROMPASSENGER_DET WHERE PASSENGER_DET .PASSNO.. : OLD.PASSNO; 5 END: 6 / Trigger created. | 7. INSTEAD-OF Triggers : INSTEAD-OF triggers are used to tell Oracle | what to do instead of performing the actions that executed the trigger. It is applicable to both object views and standard relational database. This trigger can be used to redirect table inserts into a different table or to update different tables that are the part of the view. This trigger 1s used to perform any action instead of the action that executes the trigger. In simpler words if the task associated with this trigger fails, the trigger is fired. It is used mostly for object views rather than tables. This trigger is used to manipulate the tables through the views.