PL/SQL Loops: FOR Loop and Control Statements

• Chapter 5 is about Advanced SQL, including PL/SQL structure, functions, procedures, and triggers.

Is SQL Powerful Enough?

• It's important to determine when raw SQL falls short, such as needing multiple SQL statements for a single task.
• SQL lacks native variables, loops, and conditional logic (if-then-else).
• Considerations should include how to enable reusability of SQL statements and handle errors during query execution.

Why PL/SQL?

• Embedding SQL into procedural languages like Java using JDBC/ODBC requires the client program to compute intermediate credit scores which can be solved with PL/SQL.
• Oracle Procedure Language/SQL extends SQL with procedural language features and can be stored or used by other users.
• Benefits include reduced communication costs, increased portability and security, and a manageable learning curve.
• Downsides involve DBMS choice, lack of a GUI, and memory usage considerations.

Structure of PL/SQL

• PL/SQL code is structured in blocks that are the fundamental building units
• A block can be named for functions or procedures or can be anonymous.

Anonymous PL/SQL program:

• Lacks a name
• Is not stored in the database
• Code is lost after execution.
• Can be saved as a local script file but requires manual saving and loading.
• Cannot accept/pass parameter values.
• Structures include optional declare and exception sections, and a mandatory begin...end; section.
• The subprogram is unnamed and can be used only once

Named PL/SQL Programs

• Named PL/SQL programs which include functions and procedures are stored as compiled database objects.
• They accept parameters for input and can be called and executed by other programs/users.

PL/SQL Code

• PL/SQL code can span multiple lines.
• Each line must end with a semicolon.
• PL/SQL text is not case-sensitive.

PL/SQL Comments:

• Multiline comments use /* */ delimiters.
• Single line comments use -- at the start of the line.

Displaying PL/SQL Output:

• PL/SQL is generally used with other Oracle tools.
• To enable output from PL/SQL programs in SQL Developer use, SET SERVEROUTPUT ON.
• serveroutput must be enabled each login.
• The DBMS_OUTPUT package is used for output.
  • DBMS_OUTPUT.PUT(item) outputs without an end-of-line.
  • DBMS_OUTPUT.PUT_LINE(item) outputs with an end-of-line.
  • Item can be VARCHAR2, NUMBER, or INTEGER.

Variables in PL/SQL

• Variables store values within PL/SQL programs.
• Steps include declaring the variable name and data type then using the variable, either assigning or using the values in queries.
• Variables are declared using variable_name data_type; and assigned values with variable_name := value;

Variable Scope

• Scope refers to the range in which a variable is considered valid, defined by the begin … end block.
• Variables are valid within the begin … end block after it's declared.

Assignment Statements

• Assign values to variables using the := operator.
• Put the receiving variable on the left, and the value being assigned to the right such as: student_name := 'John Miller';

Referring to Column Data Types

• Use the %type to assign a variable the same data type and length of a table column automatically.
• The syntax used is table_name.column_name%type.

Row Type Variables

• Use %rowtype to declare a variable that represents an entire row of a database table.
• Access columns within the row with variable_name.column_name.
• Often used with the insert command to store results of an SQL query.

Common Errors

• Forgetting a % between table name and row type.
• Using . instead of % between table name and column name.
• Using dbms_output.put_line() directly with a row type variable.
• Solution: Access individual columns or use other methods to display the row_type output

PL/SQL Control Structures

• PL/SQL Control Structures includes conditional if and case statements.
• PL/SQL Control Structures includes loop and exit statements.

PL/SQL Selection Structures

• IF/END IF: IF condition THEN program statements END IF;
• IF/ELSIF: includes IF condition1 THEN program statements; ELSIF condition2 THEN alternate program statements; ELSE alternate program statements; END IF;

PL/SQL Comparison Operators

• Comparison operators includes =, <>, >=, <=, <, and >.
• Logical operators include AND, OR, and NOT.
• Use IF x is NULL THEN for checking NULL values, because IF x = NULL THEN is not supported
• Avoid “IF x = NULL THEN “ and instead check “IF x is NULL THEN”

PL/SQL Loops

• Loops allow you to repeat one or more program statements multiple times until an exit condition is reached.
• They are useful when processing SQL query results.
• Use the basic loop construct with an exit when clause to control termination.
  • Example: loop total := total + i; i := i + 1; exit when i > 10; end loop;

Numeric FOR Loop

• The loop repeats end_value – start_value +1 times
• FOR counter_variable IN [reverse ] start_value .. end_value LOOP program statements END LOOP;
• When to jump out of loop, use exit when
• Counter variable can not be modified inside the loop

Important Note

• Be sure that start_value is lower than (or equal to) end_value

Using SQL Commands in PL/SQL Programs

• Includes insert, update, delete, commit, and rollback.
• To handle select statements, use cursors to hold.
• Data Definition Statements (DDL) can be used through dynamic SQL.
  • DDL includes create, drop, alter, grant, and revoke statements.

Using Insert, Update, Delete

• Can be used SQL commands like insert, update, and delete without referring to any variables.
• Can use the insert into Emp values statement to write variables in PL/SQL.

Using a Row-type Variable

• Use a row-type variable to write multiple values to the table at once, and the program only has to declare the table name.
• Example: insert into Emp values x;

Implicit Cursors

• Implicit Cursors require only one line of code to execute select statements, and retrieve results.
• The output must fetch one and only one record, or there will be an error.
• Variables must have been declared beforehand and have a data type that matches the query result.
• The command used for results is "SELECT column1, column2...INTO variable1, variable2,..FROM tablename WHERE search_condition;"

Implicit Cursors, Common Errors

• A common is no data found which means your selection returned no rows in the table
• Variables do not match the row type or column type, and you need to test your statements first

Exception Handling

• Use the exception block to handle errors like no data found or too many rows.
  • Example: when no data found then dbms_output.put_line('no rows found');

Exception Syntax

• When exception name, the handle
• List out all of the handled variables and their corresponding output message.
• Use for implicit cursor exceptions no_data_found and too_many_rows, but they are case insensitive. Example: dbms_output.put_line('No rows found')
• Handle exceptional conditions for null values.
• Use if x is null then… or if x is not null then

Tips for Implicit Cursor

• To perform the "where" condition on primary key with (zero or one rows).
• To perform aggregations without grouping by (1 row)
• Otherwise use explicit cursor (which is next).

Explicit Cursors

• Explicit cursors are to be declared in program's DECLARE section.
• It can be used if query returns multiple records or no records.
• To implement it, you have to declare, open, fetch cursor result into PL/SQL, check exit condition, and close cursor.

Common Errors in Explicit Cursor

• Ensure the SQL statement has been tested before defining the cursor with variables
• Ensure the tables or names are the same, and ensure all conditions are checked

Correct Order

1. Always fetch
2. Exit test
3. Process results

Functions and Procedures

• Functions and procedures allow "business logic" for SQL statements to be stored in the database and executed.
• These can be defined either by the procedural component of SQL or by an external programming language such as Java, C, or C++.

SQL functions:

• The syntax to follow for functions is
• Syntax includes CREATE [OR REPLACE] FUNCTION function_name (parameter datatype) RETURN datatype IS BEGIN function body RETURN return_value; END;

Declaring SQL Function:

• When declaring SQL functions, name the id of the instructor, and return the salary for the instructor
• Syntax includes: create or replace function return_salary (instructor_id number) return number is sal number; begin end;

Invoking SQL Functions

• If you have a function "name", then invoke the function by using the name() within SQL to execute Syntax is name + return + select where id

Declaring SQL Procedures

• Use the instructor table to create and declare SQL Procedures "get_salary" function, with the id of the instructor
• Syntax includes create or replace procedure get_salary (instructor_id int) is instr_salary int; begin end;

Invoking SQL Procedures:

• Procedures can be invoke on the selected salary and the id through SQL and embedding it on the SQL Example: begin get_salary(10101); end;

Triggers

• Triggers represent statements executed automatically in the database.
• It's important to specify the conditions to be executed with the trigger
• To design a trigger, specify the actions.
• Useful for enforcing complex constraints or business rules, auditing changes to a table but CANNOT have parameters.

Triggering Events

• The triggering event can be insert, deleted or update
• A trigger could be Before(trigger is executed before the action), or After (trigger is executed after the action)

Types of Triggers

• Statement Level: triggers the statement with how many rows are modified
• Row Level: trigger the statement with number of rows, and will NOT other rows

Statement Level

• Triggers fire once, regardless of how many rows are modifie
• Audits what triggers being implemented

Row Level

• For most other situations.
• Trigger fires once for each row that is affected.
• Through two system variables: new and old.

Creating A Statement-Level Trigger:

• Syntax requires a name and event with Before or After
• Insert is a must for all -Example: CREATE OR REPLACE TRIGGER trigger_name {BEFORE AFTER} {INSERT or UPDATE or DELETE} ON table_name [DECLARE declare local variables here] BEGIN trigger body END;

Row-Level Trigger Example:

• "Print when department names (dept_name) gets changed. Print: “name has changed dep from”+department + “to” + department
• Must Declare the table on a trigger

Test your knowledge of PL/SQL loops, focusing on numeric FOR loops, REVERSE keyword, loop control using EXIT WHEN, and NULL value handling. Learn about iterating with specific steps and the implications of modifying loop counters.