Database Programming CH4,5,6,8 PDF

CH4 What is the procedure? It is a partial software unit with a name, is of type (subprogram), and performs a specific function. It is similar in structure to anonymous software modules, with a slight difference. IS or As is used instead of Declare. It is written once; we...

CH4 What is the procedure? It is a partial software unit with a name, is of type (subprogram), and performs a specific function. It is similar in structure to anonymous software modules, with a slight difference. IS or As is used instead of Declare. It is written once; we can execute and call it more than once because it is stored in the database. It may or may not return a value. The ability of a stored procedure is not limited to a single query. Still, it can perform a group of commands, such as adding a record and then modifying data on Another record and doing calculations on another set of records. Stored procedures can contain parameters, which means they can use input and output values. These transactions can return more than one value or nothing. A stored procedure can be called inside another stored procedure. Where: Create Procedure: They are reserved words for creating an action Replace: It is an optional word used if the software unit is already defined, so it is deleted and replaced with the new version. Knowledge of the current unit. Procedure_name: The name of the procedure used, and the variable naming conditions apply to it Parameter: They are operands to pass values to and from the database, and the procedure takes zero or more of these operands. Mode: It is the state of transactions and has three values: IN, OUT, IN, OUT Datatype: The data type does not take a specific length. IS | AS: It is used to declare and define internal variables of the procedure and is used instead of the word declare. Begin: Start writing the procedure code. Exception: Optional for writing and error handling. End: End of procedure. Parameters: It is the means to transfer values to and from the environment that calls for the action. They are similar to variables, but only the value is passed inside them, while a variable stores the value inside them. Parameters are defined after the procedure name, and the data type does not take a specific size. There are three types of Parameters as follows: Entrance type: Description IN (default): Passes a fixed value from the calling environment to the procedure (receives). OUT: Passes a value from the procedure to the calling environment (sends). IN OUT: It passes a value from the calling environment to the procedure and then using the same parameter returns a value from Exit to the calling environment. (receives and sends) Stored functions: We previously considered stored procedures and types of defined programming blocks. Now we will discuss a second type, which is functions Stored. Stored functions are created and defined by the user and have the same properties as stored procedures, except that they... It returns a value using the return command. Stored functions: Stored functions are created and defined by the user and have the same properties as stored procedures, except that they... It returns a value using the return command. function can have zero or more parameters and must have one return value. The function consists of two main parts: 1- Header: This part comes before the word IS or AS and defines the name of the function, the parameters, and the data type of the returned value. Use the reserved word Return. The data type of the returned value must contain the size of the value, and it is sufficient to specify its type. 2- Body: ‡ It is everything after the word Is or AS that begins with the word Begin and ends with the word End ,followed by the name of the function. ‡ The function body must contain at least one return statement. Note: Functions cannot change data in tables, as they cannot execute Insert, update, and delete statements To call the function in the previous Note: Rollback cannot be performed for any DDL operation such as Drop Function, Drop Procedure When a function is deleted, it cannot be returned again Comparison between stored procedures and stored functions: Procedure Function Executed as part of an expression Inside Executes as a PL/SQL statement (Expression) It must contain a return statement with the It contains a return statement command Return It can have one or two return values More using the word OUT in the definition It must have only one return value Parameters. Benefits of using procedures and functions: Improve efficiency Improve the ability to maintain and monitor the program. Improve data protection opportunities CH5 Transaction: It is a set of sequential commands and operations that either take place together and are all fully implemented or do not take place at all. Therefore, when the operations occur, they are not done if their effect leads to loss or inconsistency in data. Otherwise, it is done and stored in the database. Examples of transaction movement include: The movement of withdrawals and deposits in banks Electronic bank transfer movement Online purchasing movements Online flight ticket reservations What is the transaction? It is a logical unit of work consisting of a sequential series of separate operations that transform the system from one stable state to another. Data at any stage of the transaction must be in a stable position. The transaction is considered successful if all the separate, sequential operations that comprise it succeed. Atransaction is considered a failure if one of the separate sequential operations that comprise it fails for any reason. All changes made since the start of the transaction are saved. If the transaction succeeds, these changes are confirmed, but if it fails as a result of failure In one of the operations, the system is restored to its state before the start of the transaction. All modifications caused by previous operations are undone within the same transaction.  This process of undoing the transaction is called rollback. When the modifications made by the transaction become permanent, we say that the transaction has been committed. A transaction can only be successful or unsuccessful, meaning there is no such thing as a partially successful transaction. Commands used with transaction? A transaction begins when a change is made to the database through DML instructions. The transaction ends if one of the following happens: Close the program. Save manual changes using the Commit command. Undo all changes in the deal using the Rollback command. Autosave when using a DDL or DCL language instruction. Program failure. The commands used for transactions include: Begin transaction: to start the transaction Commit: It saves the changes we make to the database. Rollback: It is used to undo all the changes we made before completing the transaction. Save point: During the transaction and after several changes, for example, we may need to save the point we have reached as follows: Save A point Then all changes after that point can be undone as follows. Rollback A To undo or cancel a transaction: When a problem arises while executing a transaction, we can roll it back to the starting point or a specified save point COMMIT command: which will tell the database to confirm and commit the changes created by the deal, After this point, the transaction cannot be rolled back using the ROLLBACK command. We use the AUTOCOMMIT command: to handle autosave To activate this command Determine the beginning of the transaction : BEGINTRANSACTION transaction_name; transaction called Checking: BEGINTRANSACTION Checking; To create a save point: SAVEPOINT savepoint_name; To create a save point named Before Change: SAVEPOINT BeforeChange; The general form of the execution order is: COMMIT transaction_name; To save and execute the transaction automatically : SET AUTOCOMMIT ON|OFF;  CH6 Building Integrated Database Applications in Oracle with Basic Elements and Models 1. Multi-Form Applications: are comprehensive database applications constructed by linking various forms to a central form, representing the main system screen. Steps to Build: ✓ Design the main form: Create a form that acts as the central hub for the application. ✓ Link additional forms: Establish connections between the main form and other forms to allow seamless navigation. ✓ Implement data sharing: Ensure effective communication between forms for integrated data handling. 1. Types of Models: 1. Bound Models: ✓ Characteristics: Directly tied to database tables, facilitating real-time synchronization. ✓ Use Cases: Suitable for data manipulation forms where changes need to reflect in the database instantly. 2. Unbound Models: ✓ Characteristics: Not directly associated with database tables, providing flexibility. Use Cases: Ideal for inquiry forms or reports where data manipulation is not the primary focus. 3. Building Models to Pass Data to Tables: Steps: ✓ Identify the target table: Determine the table to which data needs to be sent. ✓ Create a bound model: Develop a model associated with the target table. ✓ Define data bindings: Establish connections between form elements and model attributes ✓ Implement data validation: Ensure data adheres to table constraints. ✓ Enable data submission: Facilitate the process of submitting data to the database. 4. Building Models to Display Data from Tables: Steps: ✓ Identify the source table: Determine the table from which data needs to be displayed. ✓ Choose a model type: Select either a bound or unbound model based on requirements. ✓ Define data bindings: Connect form elements to model attributes for displaying data. ✓ Implement data retrieval: Develop mechanisms to retrieve data from the database. ✓ Design data presentation: Create an effective layout for presenting the retrieved data. 5. Using Basic Elements in Forms: Command Buttons: Utilize for triggering actions or navigation between forms. Text Fields: Capture and display textual data Drop-down Lists: Provide users with predefined options for data entry. 6. Building Lists Within Forms: Lists within forms allow users to select from a set of predefined options. Steps: 1. Create a list item: Define the list element within the form. 2. Populate the list: Specify the options available for selection. 7. Formatting forms: Importance: Formatting enhances user experience and ensures a polished interface. Formatting Tips: ✓ Use consistent colors and fonts ✓ organize elements logically for intuitive navigation. ✓ Ensure proper spacing for clarity. ✓ Implement error handling for a seamless user experience. Oracle Forms: 1. Graphical Development Environment: Oracle Forms provides a visual development environment where developers can design forms using drag-and-drop components. This includes various user interface elements such as text fields, buttons, lists, and more. 2. Integration with Oracle Database: Oracle Forms is tightly integrated with Oracle Database. Developers can create forms that interact with database tables, views, and stored procedures, allowing for seamless data manipulation and retrieval. 3. Client-Server Architecture: Oracle Forms applications typically follow a client-server architecture. The forms themselves run on the client side, interacting with the database on the server side. This architecture helps in distributing the processing load and improving performance. 4. Forms Runtime: Oracle Forms applications can be run in Forms Runtime mode, providing end-users with the ability to interact with the forms. Forms Runtime can be launched as a standalone application or embedded in a web browser. 5. Web Deployment: Oracle Forms applications can be deployed on the web using Oracle Application Server. This enables users to access forms through a web browser, extending the reach of applications to a broader audience. 6. Security Features: Oracle Forms provides security features such as authentication and authorization mechanisms. This ensures that only authorized users can access and manipulate data through the forms. 7. Integration with Other Oracle Technologies: Oracle Forms can be integrated with other Oracle technologies and products, such as Oracle Reports for generating printable reports, Oracle Discoverer for data exploration, and Oracle BI for business intelligence. CH8 What is Trigger: A block of code that is attached to an event. When that event occurs the trigger code is fired. A stored block with [Declare], Begin, End. Associated with a database table or action Fires automatically when certain DML action is carried out on the table Trigger Uses: 1. Auditing ✓ Write information about (sensitive) data modifications to an audit table ✓ May include old and new values, user, timestamp ✓ E.g. new and old salary 2. Data Integrity ✓ Implement checks on data against business rules ✓ Can compare with live database values ✓ NEW and OLD values can be compared ✓ E.g. prices must not go down 3. Referential integrity ✓ Allows implementation of a "cascade update" ✓ E.g. if author ID (aID) is changed, appropriately change authID in foreign key 4. Derived data ✓ Update any stored derived data when base data changes ✓ E.g. if total number of employees is stored, add 1 if new employee added 5. Security ✓ Logging of database access ✓ E.g. date and time each user logs on ✓ E.g. deny access at weekend Categorization on the trigger level: ROW Level trigger: It gets executed for each record that got updated by a DML statement. STATEMENT Level trigger: It gets executed only once by the event statement. Categorization of the trigger timing: BEFORE trigger: It gets executed prior to the specific event that has taken place. AFTER trigger: It gets executed post the specific event that has taken place. So 4 types can be triggered: Before Statement, Before Row After Statement, After Row Categorization of the trigger event: DML trigger: It gets executed if a DML event like an UPDATE, INSERT, or DELETE is performed. DDL trigger: It gets executed if a DDL event like a DROP, ALTER, or CREATE is performed. DATABASE trigger: It gets executed if a database event like SHUTDOWN ,STARTUP, LOGOFF, and LOGON has taken place. Conditional words (NEW, OLD): When a DML statement changes a column the old and new values are visible to the executing code This is done by prefixing the table column with :old or :new :new : is useful for INSERT and UPDATE :old : is useful for DELETE and UPDATE CREATE [OR REPLACE] This is for creating, replacing, or updating a trigger having a TRIGGER trigger_n name as trigger_n. {BEFORE | AFTER | INSTEAD This is for determining the time when the trigger will be fired. OF} The INSTEAD OF is for creating a trigger that has a view. This is for executing the DML actions. {INSERT [OR] | UPDATE [OR ] [OF column_n] This is for mentioning the column name that |DELETE} shall be modified. This is for mentioning the table name that is attached to the [ON table_n] trigger. [REFERENCING OLD AS o This is for referring to the old and new values by the DML NEW AS n] statement like UPDATE, INSERT or DELETE. This determines a row-level trigger, i.e., the trigger will be fired for each row that is modified, else the trigger will fire just once [FOR EACH ROW] when the SQL statement is executed, which is known as a table-level trigger. This gives a condition for rows for which the trigger would be WHEN (condition) executed. This is applicable only row-level triggers.