Oracle for B.sc Student part 9

Unit V:

Oracle for B.SC Final Year Student

PL-SQL: In SQL oracle procedure language is a super set of structure query language (SQL).you can use PL-SQL language to modify pure fussiness rule through creation of stored procedure &package, trigger database event to occur to add programming logic to the execution of SQL commands .PL-SQL code is grouped into structure called block. If you create a stored procedure or packet you give the block of PL/SQL a name. If a block of PL/SQL code is not name given a name, than it is called an undefined block. A block of PL/SQL code constants three section. (1) Declare (2) Begin & (3) Exception handlers (handling). The first section called declaration section used to define or declares the variables & cursor that are used in the block. The declaration section starts with the keyword declare. The section called the Begin the contains the executable commands i.e. the complete programming logic. & the third section called exception handling provides custom handling of errors conditions. The PL/SQL block is terminated by keyword END.

*PL/SQL DATA TYPES: Inside a stored procedure of function it can define one or more variable whose scope is limited. In the some way i.e. other language to define PL/SQL variable in the declarative section of the block at the beginning of the procedure. We cover the variable of data type for column in database table when defining a table. The same data type used in data base are also used in PL/SQL. The data types used in PL/SQL are defined in two ways:
(1) Scalar data type: The first & simplest data type variable to PL/SQL developed is the scalar data type. The term scalar is given to simple data type that has only one value the alternatives to scalar data type are composite to scalar or user defined data types same popularly used scalar data type in PL/SQL:

2) USER DEFINED VARIABLE (COMPOSITE): User defined data type s is data type that you define in your code &include more than one value (scalar).Ex:- A record is equivalent to user defined structure. There are three user defined data types;
(1) Cursor: An explicit pointer to a record in result.
(2) Record: A record type allows user to define your own combination of data type it is same as structure used in c & c++.
(3) PL/SQL table or a collection: This is recent addition to the data type & solves the deficiency in PL/SQL.
*EXECUTABLE SECTION: In the executable command section, you manipulate the variable & cursor declared in the declaration section of your PL/SQL clause. The executable command section always starts with the keyword ‘begin’. In the following listing the first PL/SQL block ex: from the declaration section is related: the area of circle is calculated & the results are inserted into the area table.
declare
Pi constant number (7,4):=3.14;
radius number(7,4);
or number(7,4);
begin
radius = 5;
or:= Pi*radius*radius insert into area values (radius, or); end;    // set sever output.
Select * from area; create table area (radius number (7,4),or number(7,4)); In the above example, the first part declared is used to declare variable or constant i.e. constant Pi & variable or’ radius. And the second part start with ‘begin’ is called executable command section. This part is always used to define & execute the command. An executable part is always ends with end statement.
*CONDITION LOGIC: Within PL/SQL to you can use ‘if else & else if’ commands to control the flow of the commands within the executable command section the formats of the available conditional logic commands are as shown below:

In the above syntax it is clear that if the condition one is true then the true part i.e. statement 1 is executed. Otherwise the false part i.e. statement 2 is executed the ‘if’ always ends with the end statement end if with semicolon (;). The examples find the greatest of three the numbers.
* Loop: You can use the loop to process the multiple record within a simple PL/SQL block. In the type of statement the statement or group a statement are repeatedly executed until the given condition is satisfied if the given condition false the program pointer immediately exit from loop & executed the statement just after the end of the loop. PL/SQL support three type of loops: 1) simple loop 2) for loop 3) while loop
(1) SIMPLE LOOP: In the following listing (e.g.), a simple loop is used to generate multiple rows in area table. The loop is started by the key loop & the exit when the clause is used to determine when the loop should be executed & the end loop clause signal of the loop.
Example: declare
Pi constant number (7,4):=3.14;
radius number (7,4);
or number (7,4);
begin
radius:= 3;
loop
or:= pi * power(radius,2);
insert into area value(radius, or);
radius:= radius + 1;
exit when radius>6;
end loop;
end;
(2) FOR LOOP: In simple loop, the loop executed until an exit condition is meeting. In a for loop, the loop executed a specified number of times.
Syntax: for identifiers in (exp 1_ _)(exp 2_ _) loop end loop;
From the above syntax identifiers is the loop variable exp 1 is the initial value of the loop, exp 2 is the ending value of the loop. The value of identifier i.e. the loop variable is alter form exp 1, to exp 2. Each for statement must be ended with a keyword end loop ending with semicolon.
Example: declare
Pi constant number (7,4);
or number  (7,4);
begin
for radius in 1:7 loop
or:= pi* power (radius,2) insert into area values (radius, or);
end loop;
end;
SQL> select * from area;
 o/p
(3) WHILE LOOP: It is a loop statement in this statement the loop is process until the given condition is satisfied i.e. the statement or group of statement are repeatedly executed until the given condition is satisfied instead of specifying the exit condition by an exit command begin the loop, the exit condition is specified in the while command that identifiers the loop.
Syntax: while (condition) loop (statement) End loop From above syntax the statement or a group of statement are repeatedly executed until the above given condition is satisfied Each while statement must end with the keyword End loop
Example: declare
Pi constant number (7,4);
or number (7,4);
begin radius:= 3;
while (radius <= 7) loop
or:= pi * power (radius, 2);
insert into area values (radius, or);
radius:= radius + 1;
End loop;
End ;/
GO TO STATEMENT: A go to statement force the flow of the command to immediately be diverted to another set of command. In order to use the go to you must first create label for sets of command. A go to statement cannot transfer control to a block nested within the current block or inside for loop, or within on if condition. A label is created by enclosing the label name in angle brackets.
Syntax: <> go to label name;
*TRIGGERS: A trigger defines an action. The database should be taken when some database related an event occurs. Triggers may be used to supplement declarative refreshential integrity to enforce complex business rule, or to audit changes to data. The code within a trigger called trigger body is made up of PL/SQL block. The execution of trigger is transparent to the user. Triggers are executed by the database or view which is specific type of datamanipulation commands are reformed on specific table. Such command includes insert, update & delete.
REQURIED SYSTEM PRIVILAGES: In order to create trigger on a table, you must be able to alter that table. Therefore you must either hold the table, Have altered privilege for the table or have altered any system privilege. In addition, you must have the create trigger system privilege to create trigger in another user account; you must have create any trigger privilege system. The create trigger system privilege is a part of the resource roll provided with oracle. In order t alter a trigger you must either own the trigger or have the altered any trigger system privilege.
REQUIRED TABLE PRIVILEGE: Trigger may reference table other than the one that initiated the triggering event.
For example: If you use triggers to audit changes to data in the larger table. Then you may insert a record into a different table, every time the record is changed in larger. In order to do this you will need to have privilege to insert into larger.
TYPES OF TRIGGERS: There are 14 types of trigger. Triggers types are defined by the type of triggering transaction & by the label at which the trigger is executed.
(1) ROW LEVEL TRIGGER: Row level trigger executed once for each row in a transaction. For the larger table auditing, each row i.e. change in the larger table may be processed by the trigger. The row level triggers are the most common. Type of triggers. They are often used in data audit they are also useful for keeping distributed data. They are created using the for each row clause in the created trigger command.
(2) STATEMENT LEVEL TRIGGER: It executed ones for each transaction. For example, if a single transaction inserted five hundred rows into the larger table, then statement level trigger on that table would only be executed once. Statement level trigger there are not often used for data related activities they are normally used to impose additional security measures on the type of transaction that may be performed on table. It is a default trigger type.
(3) BEFORE & AFTER TRIGGER: Since trigger occurs because of events they may be set to occur immediately before or after those events. Since the events that executes triggers, are database transaction trigger can be executed immediately before or after insert, update & delete within the trigger you will be able to referred the old & new values in valued in the transaction. The access required for the old & new data may determine which type of trigger you need “old” refers to the data as exited prior to the transaction. Update & delete usually refer old value “new” values are the data value that transaction creates. If you need to set a column value in an inserted row via your trigger, then you will need to use a before insert trigger in order to access new value. Using an after insert trigger will not allow you to set the inserted value since the row will already have been inserted into the table. After row level trigger are frequently use with auditing application.
(4) INSTEAD OF TRIGGER: You can use instead of trigger to tell oracle what to do instead of performing the action that executed that table. For e.g. you would use an instead of trigger to redirect the table insert into a different table or to update multiple table that are part of a view. For e.g. if a view in values join of two tables your ability to use the update command on records in the view is limited. However if you use an instead of trigger you tell oracle how to update delete or insert record in the views table when a user attain s to change values via the view the code in the instead of trigger is executed in the place of insert, update or delete command you enter
VALID TRIGGER TYPES: There are 14 possible following trigger configurations
Before     insert     row
Before     insert     statement
After        insert    row
After       insert    statement 
Before     update     row
Before     update     statement
After update row
After update statement
Before delete row
Before delete statement
After delete row
After delete statement
Before instead of
After instead of 
Syntax: Create trigger [user] trigger
{before| after| instead of}
{Delete| insert | update [of column [column]…]
[Or {delete| insert | update [of column [column]…..]}].
ON [schema] {Table| view}
[Referencing {OLD [as] old | New [as] New}…….]
For each {Row | statement} [when (condition)]
PL/SQL block
Where:
OR REPLACE: Recreate an existing trigger you use this option to change the definition of existing trigger without having to delete it first.
SCHEMA: that will contain a trigger. If a schema is omitted oracle will create a trigger is the user trigger.
Before: Makes oracle activate the trigger before executing the trigger in command. Row trigger, this is the individual trigger before each deflected row changed. You can specify a before trigger in a view or object view.
After: Make oracle activate the trigger the execution of trigger in command. You can not specify an after trigger in a view or object view.
Instead of: Make oracle activate the trigger instead of executing the trigger in command. As the default, the instead of trigger are enable in each row. Instead of is a valid option only for views. You can not specify instead of trigger in a table.
Delete: Make oracle activate the trigger when ever delete command eliminate row in the table.
Insert: Make oracle activate the trigger whenever an insert command adds a row to the table.
Update: Make oracle activate the trigger value whenever an update command change the use in one of the column specified in the clause. If omitted, oracle will activate the trigger whenever an update command changes value in any column of the table. You can not specify of clause with an instead of trigger.
On: Specifies the schema & the name of the table or the view of one of the following parameters when the trigger crates tables, object table, object view. If schema is omitted oracle assume the table is in its own schema.
Table: The name of the table or an object table.
View: The name of the view or an object view.
Referencing: Specify the co-relational name. You can use the co-relational name in the PL/SQL block & in the even clause in row trigger, specifically referencing the new & old value of the current row. The default co-relation name is old & new. If your row trigger is associated to a table called old or new, use this clause to specify different names for the co-relation if a trigger is defined or a  view table, old and new refer to the object instance.
For each row: Designates a trigger such as a row triggers. Oracle triggers are row trigger for each row that is affected by triggering commands which meets the optional constraints condition defined in the when clause. Except for the instead of trigger in this clause is command trigger a triggering command.
When (condition): Specify the trigger restriction which is SQL condition that must meet before oracle can active the trigger. You can specify are trigger restriction only for a row trigger. Oracle evaluated this condition for every row affected by the triggering command.
Example: Create trigger ledger _ bef_ upd_ row
Before update on ledger
For each row.
When(New amount / old . amount > 1.1)
Begin
Insert into ledger_ Audit
Values (: old. Action date, : old. Action,:old. item, :old.qty, :old . qty type, :old. rate, :old. amount , :old. person)
End;
* MODIFYING THE TRIGGER: A trigger can not be directly modified change it you must recreate it with the creation command. If a trigger has its privilege granted to the user, they remain valid as long as the trigger exists.
ENABLING & DISABLING THE TRIGGER: when a trigger is created it is automatically enable  & this trigger, whenever the trigger command & the execution command is true to disable the execution of a trigger; use the alter trigger command with the disable clause  to enable it again use the enable clause
Syntax: alter trigger name enable/ disable;
Example:
alter trigger ledger_bef _upd _ row disable;

DELETING A TRIGGER: If you want to delete a trigger created on a particular table use the drop trigger command
Syntax drop trigger Name_ of _ trigger;
Example: Drop trigger Led ger _ bef _ upd_ row;
*PROCEDURE & FUNCTION: unlike procedure function can return a value to the column, procedure can not return value. The values in a function are returned through the uses of return keyword using the function.
* CREATING A PROCEDURE:  The procedure can be created by using following syntax.
Create [or replace] procedure [user.] procedure
[(argument [in/out | in out] data type, [, argument [in/out | in out] data type]…)]
{is/as}{Block/ External program};
Example: create procedure new _worker
(Person_Name in varchar 2) as begin Insert into worker (Name, age, loading)
Values (person name, null, null),
Exit;
The new worker procedure shown in above example will accept a person name as its input. it can be called from any application. It inserts a record into the worker table with null values age & loading column. If the procedure already exist, you can replace it by the create or replace procedure command. The benefit of using this command is that the executed grant previously made on the procedure will remain in place. The ‘In’ qualifier signifies that the procedure passes a value back to the caller through this argument. the’ In put’ qualifier signifies that the argument is both an’ in’ &’Out’. A value must be specified for this argument when the procedure is called & the procedure will return a value to the caller. In this argument if no qualifier type is specified then default values is ‘in’.
The block refers to the PL/SQL block. That the procedure will executed when called. In the above example the block is shown by the begin. In the PL/SQL block within the procedure, only include any DML command, they can not use DDL command.
Once the procedure has been created, it may be executed. When the procedure is executed, it release on the privilege table of it owner, not those of the user who is executing. A user executes a procedure does not need to have been granted access to the table that the procedure access. To allow other users to execute your procedure grant they execute privilege.
Grant executed on my – procedure to Dora;
The user Dora will know be able to execute the procedure name my procedure even if it does not privilege on any of the table that my procedure user. If you do not grant executed privilege to user, then they must have any execute the procedure. System privilege in order to execute the procedure. After granting the permission user can execute the procedure by simple execute command.
Example: Execute new_ worker (. xyz);
The above command will execute new worker procedure passing to it the value xyz.
*DECLARING VARIABLE CONSTANTS: All language along developed to define a constant for properly handling the value to define constant in oracle PL/SQL it along word keyword called constant. The method of declaring variable accepts the keyword constant is added. Pi constant number (6:2):= 3.14
Statement & assignment: A PL/SQL consists of statement like any other procedure language. Each statement is terminated by semicolon so naturally they can scar more than one line of code. A side effect is that this method of making the end of statement. Lets you format the code so it easy to read. The assignment like a little more getting use to be assignment statement use to simple : = assign a right hand value to left hand variable.
E.g: a: =10;
Stat: =’False’;
V_ name: = unknown;
V_ name: = v_ name;
Flow control statement: Once to you have your variables define you need to become familiar with flow control before you can street more than a simple program. To basic concepts are missing row SQL but absolutely necessary for procedure programming colon. Decision flow control & loop. This concept are the flow control statement.
CUSTOMISING ERROR CONDITION: Within a single trigger, you may establish different error condition. For each of error condition you define, you select and error message that appears when the error occur. The error numbers & message that a display to the user are set via the raise application error.
Procedure: This procedure may be called within any trigger. The following show the statement level before delete trigger on ledger table. When the user attempts a delete a record from the ledger table, this trigger is executed & checks two system conditions. That the day of the week is neither Saturday on Sunday & that the oracle user name of the account performing the delete begin with the letter Fin. The trigger components will be describe following the history create trigger ledger_bef_del before delete an ledger.

*Creating table with the contents of another Table :
Using the as option, we can create a table with the select command to recover the structure & data of another table.
SQL> create table new emp
 2    as select * from emp
3    where dept no = 20;
Table created.
SQL> select * from new emp;

Using parallel option: To create or read a table that uses all the parallel execution servers possible, use the parallel option. SQL> create table new emp 2
 2     parallel
 3     as select * from emp;
Table created.
After table is created, searches in the table are also faster, because the game parallelism rate is used to access it.
*Constraint Disabling: The following table is created with a primary key. However, it will remain disabled. This is an interesting resource, use when the table is created from another table. The data is ready & the creation of the table takes less time, since the checking is omitted.
SQL> create table dept1
2    (dept no Number (2) primary key disable,
3    D name varchar2 (10),
4    Loc varchar2 (9);
This option will allow the creation of duplicate keys:
SQL> insert into dept1 values (10, aaa, aaaa); 1 row created.
SQL> insert into dept1 values (10, bbb, bbbb);
1 row created.
There are parts to PL/SQL block:
(1) Declaration section/declare, in which all the objects.
(2) Execution section/Begin, in which the PL/SQL commands are placed
(3) Execution section/exception, in which the error are handled
*DECLARATION SECTION: The pt section of the block is called the declaration. It is optional, but if the block uses variables or constants, all of them must be declared before they are used in a command. This section begins with the keyword declare. In this section, developer can perform the following.
(1) Declare the name of identifiers.
(2) Declare the type of identifiers.
(3) Declare the type of the identifier
(4) Assign contents to the identifiers.
*Variable & constants: The PL/SQL language allows the declaration of variables & constants which can be used in the SQL commands contained in the procedure & functions. All the variable & constants used must be declared.
Variables: The variables can contain any data type is valid for SQL & oracle. (Such as char, number, long, varchar2, & date) in additions to these types:
(1) Boolean can be assigned the value true, false or Null.
(2) Binary integer:Accepts number between-2.14,7,83,647 & 2.147.483, 647.
(3) Positive: Accepts no from 1 to 2147 483647
(4) Natural: Accept no from 0 to 2147 483647
(5) % type: Assign to the variable that is beginning created the same data types used by the column that begin used.
(6) % row type: Declare the composed variable that is equivalent to the row of the table.
There are two way to assign values to a variable. The 1st is to use the assignment operator:”: =”:
Ex:        Total: = price * margin;
Increase: = sal * 1.10;
Ok: = False;
The 2nd way to assign the value to variable is to use the select command to assign the contents the fields of a table to a variable
Ex: select sal * 0.10 INTO increased From emp where emp no = emp_ id;
Constants: The declaration of constant is similar to the declaration of a variable. All you hare to do is to add the keyword constant after the name of the constant:
Vr_ fixo constant real: 4000.00;
Each variable or constant must be specified with its name, type, & optionally, initial value. All the rows must end with a semicolon;
Declare
Cust_ name varchar2 (20);
Credit number (5, 2): = 100;
*EXECUTABLE SECTION: This section begins with the begin declaration. They must always end with a semicolon. This section can contain SQL commands, logic control commands, & assignment commands, as well as other commands
*EXCEPTION SECTION: In this section, the developer can use commands to handle an error that occurs during the execution of a PL/SQL program. You can create routine that executes corrective procedure when detecting an error, thus preventing an interruption of the system.
*Integrating SQL in a PL/SQL program: SQL command can be inserted in the execution section of a PL/SQL block. The developer can use. A variable or constant declared in the declaration section. Inside a PL/SQL program the select command uses a new clause called INTO that allows transferring the contents of the field in a row to the variable in memory. This clause must be placed between the list of field & from clause:
Declare
Var_ name. Varchar2(20);
Begin
Select ename. INTO var_ name
From emp for ename = ‘shweta’;
Note that to attribute. Contents to a field in a variable, the select command must return only one row; otherwise an error will occur, since several values are being assigned to only one variable. Although powerful in its ability to recover information, PL/SQL is a dinosaur in term of user interface. Even when executed in a graphical environment, it seems absolute. It has no support for direct communication with the user. For example, when PL/SQL block queries a database or make available the obtained results.
The result of PL/SQL processing are presented in two ways: the creation of a temporary table to be used by other applications, or a package called DBMS_ OUTPUT , which among other things, allows the display of a row with the contents of a variable or previously defined contents we will see some example later. Inside the DBMS_ OUTPUT package there are two procedure used to display the contents of field/variables. Once is called enable, & the other is put_ line. For them to work correctly, it is necessary to end;
10 * end;
I int: = 0;
Error at line 3;
ORA – 06550. line 3, column y.
Pls-00103: encountered the symbol “ :” when excepting one the following:
:= (% ; not null rang default character);
SQL>
If you have previously created a table with this name, & want to execute these examples delete the records inserted in the table with ‘Delete From TEMPTASLE’ command before executing the next example name.
Testing the PL/SQL blocks in SQL * Plus requires at least two specific commands GET & @ to Configure a PL/SQL block that was previously written as a text file to the execution buffer of SQL * Plus, use the following command:
Syntax: Get name_ of_ block
Example: SQL>GET all program SQL to execute loaded block, use the slash key(/) or the RUN command.
PURPOSE OF DATABASE SYSTEM:
Consider part of a savings bank enterprise that keeps information about all customers & saving accounts. One way to keep the information on a computer is to store it in permanent system files to allow user to manipulate the information, the system has a number of application programs that manipulate the files, including:
(1) A program to debit or credit an account.
(2) A program to add a new account.
(3) A program to find the balance of an account.
(4) A program to generate monthly statements.
These application programs have been written by system program in response to needs of blank organization. New application program are added to the system as the need arise.
For example: Suppose that new government regulation allows the saving bank effort checking account. As a result, new permanent files are created that contain information about all the checking accounts maintained in the blank, & new application program may need to be written to deal with situations that do not arise in savings accounts, such as handling overleaf.

UNIT V ENDS HERE

                                   <<<<<  Previous