PL/SQL Blocks: Name, Birthday, and Age

• PL/SQL (Procedural Language/SQL) is a programming language that extends SQL with procedural capabilities.
• PL/SQL blocks are fundamental units of code, that can be named or anonymous.
• Blocks consist of a DECLARE section, a BEGIN section, and an optional EXCEPTION section.

Printing Name, Birthday, and Age

• A PL/SQL block can be created to print name, birthday, and age.
• Variables are declared with VARCHAR2 for the name and DATE for the birthday.
• The age is computed via TRUNC(MONTHS_BETWEEN(SYSDATE, Birthday) / 12) which determines the integer part of the age based on months since the birthday

Calculating Age in Future Years

• A PL/SQL block determines future age in 10, 20, and 25 years.
• It calculates the current age and subsequently adds 10, 20, and 25 years to that value.

Determining Day of the Week for a Given Birthday

• A PL/SQL block outputs the day of the week someone was born.
• It coverts the birthday date to a day string using TO_CHAR(v_birthday, 'Day').

Performing Addition and Subtraction

• A PL/SQL block calculates the sum and difference of two numbers.
• It declares two number variables, calculates their sum and difference and presents the results.

Calculating Employee Incentive

• A PL/SQL block calculates an employee's incentive based on their salary.
• It uses the employees table with the salary%TYPE attribute.
• Incentives are calculated by multiplying the salary by 0.10.

Using Reserved Words as Identifiers

• A PL/SQL block is created to show that reserved words can be used as user defined identifiers by surrounding them in double quotes.
• Dubble quotes are valid “BEGIN” number := 10 .

Explaining Comments

• Single-line comments start with --.
• Multi-line comments are enclosed within /* and */.

Declaring Variables

• Different datatypes are declared like VARCHAR2, NUMBER, and DATE.
• Date are initialized with SYSDATE.

Adjusting Employee Salary

• A PL/SQL block updates an employee's salary by 10%
• The HR schema employees table is used.
• The updated salary is displayed.

Usage of NULL Values

• NULL is not equal to any value, including itself, during comparison.
• IS NULL should be used to detect NULL values.

Declaring Character Type Variables

• CHAR and VARCHAR2 variables are declared.
• CHAR can be fixed-length, VARCHAR2 is variable-length.

Inserting Data with Character Type Variable

• A PL/SQL block that inserts a new employee into the employees table using a VARCHAR2 variable for the first name.

Inserting Data into Departments Table

• A PL/SQL code block that inserts a new department using VARCHAR2 for the department name.

Upper Limit of PLS_INTEGER

• The maximum value for PLS_INTEGER is 2147483647.

Arranging Numbers

• IF/ELSE statement arranges two numbers into num_small and num_large.

Procedure for Incentive Status

• A procedure calculates and updates employee incentives.
• SQL%ROWCOUNT checks for successful updates.

Even or Odd Check

• The MOD function is used to check if a number is even or odd.
• MOD(number, 2) = 0 indicates an even number.

Procedure for Incentive By Target

• A procedure calculates bonus based on target.
• It calculates a bonus based on whether an employee's target exceeds 5000.

Weekday Check Procedure

• A procedure determines if a date is a weekend or a weekday
• Built-in IN operator is used
• Day converted using TO_CHAR

Incentive on Sales Limit

• Incentive amounts are computed based on different sales thresholds, with varying percentages applied to sales figures.

Vacancies Check

• A COUNT(*) statement determines the number of employees in department 50.

Display Grade Description

• A CASE statement is used to output a grade description based on a grade.
• It assigns descriptions such as 'Excellent', 'Good', 'Average', or 'Fail' based on the value of v_grade.

Vacancy Check in Department

• Counts employee in department, then indicates if there are vacancies available in that department.

Display Grade with CASE

• The CASE keyword allows you to SELECT results in the THEN part of the statement

Display Grade with CASE and Exception

• Handled exceptions by calling RAISE_APPLICATION_ERROR.

Positive or Negative Check

• An ELSIF statement can check if a number is negative.

Letter or Digit Check

• BETWEEN can check for either a letter or digit.

Temperature Conversion

• Used ROUND(temp,2) to give 2 DP

Specific Date Check

• Built in TO_CHAR with parameter Day displays day of week that date falls on

Loop EXIT

• EXIT WHEN command lets the loop end when the expression equates to true

Loop WHEN EXIT

• A LOOP can be exited using the EXIT WHEN statement

Nested Loop

• Inner and outer loops each repeat as indicated by 1..3 LOOP.

Update Salary with Mid-Range Check

• Updates salaries based on conditions related to mid-salary range
• v_mid_salary determined by (v_min_salary + v_max_salary) / 2

Nested Quit

• Nested loops can check for specific values, can call Exit When command.

CONTINUE Statement

• The CONTINUE statement skips the rest of the current loop iteration and proceeds to the next iteration.

CONTINUE WHEN Statement

• The CONTINUE WHEN statement skips the rest of the statements in the loop iteration if the condition specified after WHEN.

Print First N Number

• A WHILE statement increments and displays values.

Addition N numbers

• The loop iterates until the counter i reaches the specified limit v_n, printing output.

Local Variable

• Assigning a local variable lets you override the global variable in the scope of the inner block.

Nested-FOR-Loop

• The outer and inner loop have labels of outer_loop and inner_loop.
• Used loop commands within for loops.

Prime Numbers

• Check for prime numbers to a given number by using

GOTO PrimeNumber

• A GOTO command can be used to go to a specific line, use <<label>> to set.

Insert from Table

• Inserting from one table to another
• Insert data into one table from another.

Insert Row

• Checking it the salary is not null or salary is not valid.

Cursors

• Cursors can select from a table with WHERE and other commands
• Cursors can include %TYPE in the DECLARE statement
• Shows total employee and displays result

CURVAL and NEXTVAL

• A sequence increments and outputs value
• employee_seq.NEXTVAL - selects the next in sequence
• employee_seq.CURVAL – selects the current value
• Displays two different output lines

SQL%ROWCOUNT

• SQL%ROWCOUNT returns the number of rows affected by the most recent SQL statement.

Implicit Cursor Without Attribute

• Updates or does not update rows, based on if it find an entry in the table.
• Confirmed by Update executed successfully messages.

SQL%FOUND

• The SQL%FOUND attribute used after DELETE determines if a delete executed.

SQL%NOTFOUND

• The SQL%NOTFOUND used after UPDATE determines if a entry was updated.

Table-Based Record

• Can select direct in to v_employee variable in employees%ROWTYPE
• Outputs name of employee

Details of employee 149

• Selecting direct from table gives you access to data
• Easy to display output from table

Details with cursor

• Create cursors to select from data
• Display that data

Details cursor and retrieve display

• Can create variable on the fly in SQL SELECT to display

Cursor Datatype

• Creating a datatype is a good way to re-use code.

Implicit Cursor

• Loops though table or cursor
• Sets the record from the table, then select data
• Outputs data

Explicit Cursor

• Loops through table or cursor
• Sets the record from the cursor, then select data
• Outputs data

Salary with CURRENTOF

• Select data and select from loop
• Use CURRENTOF to update table as it loops through the selected data.

Fetch Single Record and Column

• Fetch a single record and single column from a table is an easy and fast way to extract data and display it.

Fetch Single Column

• Displays a column
• Great way to display column

Multiple Records and Columns

• Displays multiple values
• Cursors are effective in pulling the data from the table.

Different Table Records

• A join can select from different rows
• A LOOP can display the data from a database join

Nested Cursors

• Displays department ID
• Displays employee connected to department ID

Cursor for Loops

• Nestles functions and loops for use
• Displays table information and labels them

Managers and the Department Name

• Selects departments and the associated users
• Displays the managers

Multiple Tables

• Can select from multiple tables to insert information
• Effective way to populate database.

Two Tables

• Select data to join
• Insert new data from joined table.

Create Cursor

• Select first name, last name , salary
• Fetch, process, output.

Fetch Statement

• The code selects from the table
• Creates cursor and then fetch though

First Three

• Can select three from table
• Using command num <= 3

Query cursor and variables

• Assign value as var
• Select that var, display output

List data

• Creates view with managers last name table
• Loops though output

Table select

• Display table and values
• Join statements use

Display Name Column

• Data select, where condition, display output

Display records

• Use sub query for department name.

Record in Table

• Select employee id and employee name
• Use “Record” parameter for types

Query in Cursor

• Select information from sub query
• Add on the value table on the on statement

Salary Increase

• Calculate increase bases on date hire
• Use math to calc