Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
This document about DBMS: Project Requirements , Programming with DBMS, What is PL/SQL, PL/SQL BLOCK STRUCTURE, PL/SQL Block Types, PL/SQL Variable Types.
Typology: Study notes
1 / 121
This page cannot be seen from the preview
Don't miss anything!
We have studies RDBMS Modeling, Querying and Design using Normalization
(^) SQL :SQL :
(^) Programming with DBMS (^) PLSQL:Introduction (^) Handling Software Projects, DataBase Connectivity (^) ODBCJDBC: Native Drivers (^) Embedded SQL
(^) AssertionsAssertions
(^) TriggersTriggers
(^) SecuritySecurity
(^) AuthorizationAuthorization
(^) Authorization in SQLAuthorization in SQL
(^) Despite designing and developing the data base , we are often
needed to program it for manipulating data.
(^) Most of the real life projects involve tasks, which needs
writing program codes for desired results from DBMS.
(^) Though large number of queries can be handled through SQL
Constructs but even then we are often required to write program codes in RDBMS Environment for the desired work. (^) Using PL/SQL (^) Using Other Programming Languages (Java, Visual Basic) (^) Database Connectivity issues : JDBCDatabase Connectivity issues : JDBC
DECLARE
BEGIN
statements
EXCEPTION
END;
Procedure
PROCEDURE
Function
FUNCTION
(^) Stored Procedures are PL/SQL code stored in the database and executed when called by the user. (^) Called by procedure name from another PL/SQL block or using EXECUTE from SQL+. For example EXEC SQR(50) (^) Example:
Create procedure SQR (v_num_to_square IN number)SQR AS v_answer number(10); BEGINBEGIN v_answer := v_num_to_square * v_num_to_square; dbms_output.put_line(v_answer); END;END /
(^) Triggers: What and How to write triggers
Triggering actions
When to use & when NOT to use.
(^) Assertions: What and How?
Assertions
Example Assertions
(^) Triggers are PL/SQL programs that automatically fire when:
(^) A DML statement, such as an UPDATE, INSERT or DELETE statement occurs on a table (^) A system event, such as SHUTDOWN, STARTUP or SERVERERROR occurs (^) A user event, such as LOGON or LOGOFF occurs (^) A DDL statement, such as CREATE, DROP or ALTER occurs
Trigger restrictions
(^) Triggers cannot be fired for a SELECT statement.
(^) Triggers can never be executed explicitly like procedures or functions.
(^) Triggers can call database procedures, functions and packages.
(^) A trigger is a statement that is executed automatically by the
system as a side effect of a modification to the database.
(^) To design a trigger mechanism, we must:
(^) Specify the conditions under which the trigger is to be executed. (^) Specify the actions to be taken when the trigger executes.
(^) Triggers introduced to SQL standard in SQL:1999, but supported
even earlier using nonstandard syntax by most databases.
(^) Suppose that instead of allowing negative account balances, the
bank deals with overdrafts by (^) setting the account balance to zero (^) creating a loan in the amount of the overdraft (^) giving this loan a loan number identical to the account number of the overdrawn account
(^) The condition for executing the trigger is an update to the
account relation that results in a negative balance value.
create trigger overdrafttrigger after update on account referencing new row as nrow for each row when nrow.balance < 0 begin atomic insert into borrower (select customername, accountnumber from depositor where nrow.accountnumber = depositor.accountnumber ); insert into loan values (n.row.accountnumber, nrow.branchname,
(^) We sometimes require external world actions, such as
(^) reordering an item whose quantity in a warehouse has become small, or (^) turning on an alarm light , to be triggered on a database update
(^) Triggers cannot be used to directly implement externalworld actions, BUT (^) Triggers can be used to record actionstobetaken in a separate table, and we can have an external process that repeatedly scans the table and carries out externalworld actions.
(^) E.g. Suppose a warehouse has the following tables
(^) inventory(item, level): How much of each item is in the warehouse minlevel(item, level) : What is the minimum desired level of each item reorder(item, amount): What quantity should we reorder at a time (^) orders(item, amount) : Orders to be placed (read by external process)
create trigger reordertrigger after update of amount on inventory referencing old row as orow, new row as nrow
for each row when nrow.level < = (select level from minlevel where minlevel.item = orow.item) and orow.level > (select level from minlevel where minlevel.item = orow.item) begin insert into orders (select item, amount from reorder where reorder.item = orow.item) end
(^) Triggers were used earlier for tasks such as
(^) maintaining summary data (e.g. total salary of each department) (^) Replicating databases by recording changes to special relations (called change or delta relations) and having a separate process that applies the changes over to a replica
(^) There are better ways of doing these now:
(^) Databases today provide built in materialized view facilities to maintain summary data (^) Databases provide builtin support for replication
(^) Encapsulation facilities can be used instead of triggers in many
cases (^) Define methods to update fields (^) Carry out actions as part of the update methods instead of through a trigger
(^) Triggers and Assertions both are very useful constructs in DBMS
which are used for controlling some behavior in a given Information system.
(^) Triggers are fired depending upon the triggering event and
should be used sparingly.
(^) Assertions can be told as extension of the concept of
Constraints, which impose some conditions on Database manipulations which needed to be followed before any otherbefore any other action action takes place.
(^) Assertions also should be used sparingly as it is expensive!
