程序代写 LNPLS411

Les07 PL/SQL part 1 intro

Week changes depending on semester length
PL/SQL part 1Document in a PowerPoint format is almost the same

Copyright By PowCoder代写 加微信 powcoder

Lecture 07
https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm
https://docs.oracle.com/database/121/LNPLS/controlstatements.htm#LNPLS411

Go to Les07-PL/SQL-extra notes has how to do it with screen shots for the SQL Developer GUI interface
Stored Procedures

Agenda(2) – what we cover today will be

· PL/SQL Overview

· Creating Standalone Procedures and Functions

· Variable and Constraints

· General Comparison Functions

Overview notes
Programming Language

Like C or Java

You already know how to interact with the database using SQL, it is not enough.
Need more to build entire enterprise applications.
Remember SQL basically is to get information from tables, change it and insert it.

Suppose you want to
1 Give some employees based on job title a 20% raise
2 Other employees a 10% raise
3 Other will be fired

Could use a CASE statement

INSERT into Fired employees table

Rather than executing a series of statements, PL?SQL allows for combining these into a block of code

PL/SQL is a third-generation language that has the procedures etc like other languages, but integrates well with SQL

PL/SQL makes it possible to build complex and powerful applications.
– Because PL/SQL is executed in the database, you can include SQL statements in your code without having to establish a separate connection.

The main types of program unitsThe three are known as
STORED PROCEDURES

– standalone procedures
– functions, and
– packages.

Once stored in the database, — can be used again as building blocks for several different applications.

You can have standalone procedures, but it is recommended to place your code into a package. More about that later

Basic Procedure BLOCKS

Declarative (optional)
– Variables and constants are identified by keyword DECLARE.

Executable (mandatory)
– Contains the application logic. These are KEYWORDS

Starts with BEGIN
Ends with END;

Exception handling (optional)
– Starts with keyword EXCEPTION and
– handles error conditions that may occur in the executable part.

/ do not forget this on the first blank line to execute the procedure

JUMP TO 4 on slide

CREATE PROCEDURES/FUNCTIONS

SYNTAX or general format

CREATE OR REPLACE PROCEDURE schema.procedure_name(arg1 datatype, …) AS
END procedure_name;

CREATE OR REPLACE FUNCTION schema.function_name(arg1 datatype, …) AS
END function_name;

MORE PROCEDURES
Arguments in Procedures

A procedure/function may receive arguments.

Argument has the following elements:
· Datatype
Can be any datatype supported by PL/SQL.

· IN / OUT / IN OUT
IN indicates that the procedure must receive a value for the argument.

OUT indicate that the procedure/function passes a value for the argument back to the calling program.

IN OUT indicates that procedure must receive a value for the argument and passes a value back to the calling program.

Using DEFAULT keyword, you can define a value for an argument.

Sample – simple PL/SQL Block
PURPOSE: To output a simple line of text

Try this – watch quotes might be a problem – have students try it

SET SERVEROUTPUT ON you are going to forget to do this

BEGIN — no ending like a semi colon

DBMS_OUTPUT.PUT_LINE (‘ Welcome to 2nd half of DBS311’); — watch single quotes still

What is dbms_output.put_line?
The Oracle dbms_output is a package (more later) that allows us to write data to direct our PL/SQL output to a screen. It has a procedure called put_line that displays the information in a line. The package is particularly useful for displaying debugging information. 
What is a package

A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in the database, where many applications can share its contents. A package always has a specification, which declares the public items that can be referenced from outside the package.

DBMS_OUTPUT.PUT_LINE (‘ Welcome to 2nd half of DBS311’); — watch single quotes still

Welcome to 2nd half of DBS311
PL/SQL procedure successfully completed.

NOTE: This is often called an ANONYMOUS BLOCK because it was not named.

A block without a name is an anonymous block. An anonymous block is not saved in the Oracle Database server, so it is just for one-time use. However, PL/SQL anonymous blocks can be useful for testing purposes.

Sample Procedure to try
Run this then jump to next page to show meaning
DECLARE SectionCopy this piece

— To define variables and constants

value_1 NUMBER := 20; — declaring the variable and assigning a value
value_2 NUMBER := 5;
addition NUMBER;
subtraction NUMBER; — defining a variable with no initial value
multiplication NUMBER;
division NUMBER;

addition := value_1 + value_2;

subtraction := value_1 – value_2;

multiplication := value_1 * value_2;

division := value_1 / value_2;

DBMS_OUTPUT.PUT_LINE (‘addition: ‘ || addition);
DBMS_OUTPUT.PUT_LINE (‘subtraction: ‘ || subtraction);
DBMS_OUTPUT.PUT_LINE (‘multiplication: ‘ || multiplication);
DBMS_OUTPUT.PUT_LINE (‘division: ‘ || division);

Next screen demo with STRING

Another using a string output
(no slide)

When using character literals in PL/SQL, remember:
1 Character literals are case-sensitive. For example, ‘Z’ and ‘z’ are different.
2 Whitespace characters are significant.
Significance example of whitespace

Show this example …
DBMS_OUTPUT.PUT_LINE(‘This string breaks

STOP HERE ———–

Notice the output is on 2 lines
Significance of whitespace

This string breaks

How to fix it (assuming you did not want 2 lines)

??????????

This section handles errors that occur when a PL/SQL block executes

value_1 NUMBER := 20;
value_2 NUMBER := 0;
division NUMBER;

division := value_1 / value_2; — divide 20 by zero generates an error

DBMS_OUTPUT.PUT_LINE (‘division: ‘ || division);

=======================================

RUN IT you get this error message

Error report –
ORA-06512: at line 8
01476. 00000 – “divisor is equal to zero”

FIXING NEXT 2 PAGES

Here is a fix

Run this code and see the results

value_1 NUMBER := 20;
value_2 NUMBER := 0;
division NUMBER;

division := value_1 / value_2;
DBMS_OUTPUT.PUT_LINE (‘division: ‘ || division);

WHEN OTHERSNot a very specific fix.

DBMS_OUTPUT.PUT_LINE (‘Error!’);

OUTPUT… must look closely to see it

Fixing it more

value_1 NUMBER := 20;
value_2 NUMBER := 0;
division NUMBER;

division := value_1 / value_2;
DBMS_OUTPUT.PUT_LINE (‘division: ‘ || division);

WHEN ZERO_DIVIDE — caught by this error handling
DBMS_OUTPUT.PUT_LINE (‘Divider is zero!’);
WHEN OTHERS — WHEN OTHERS must be last
DBMS_OUTPUT.PUT_LINE (‘Error!’);

ASIDE: Use indents. Many assignments, labs and tests not indented

Divider is zero!

PL/SQL procedure successfully completed.

SELECT INTO — one row retrieved from SELECT

Using a procedure, but getting the data from a table

The following PL/SQL code searches for a specific product by its product ID and displays the product ID and the product name for that product

Using a SELECT from a table to load the defined variables.

Put up this code and explain
This is 2020 summer code – next page for 2020-3 and later

DECLARE — define variables

productId NUMBER := 2;
productName VARCHAR2(255 BYTE);
price NUMBER(9,2);

SELECT product_name, List_price — select data from these columns
INTO productName, price — insert them into the above declared variables
FROM products
WHERE product_id = productID; — will get 1 row or none

— now ouput the findings – assumed example worked
DBMS_OUTPUT.PUT_LINE (‘Product Name: ‘ || productName);
DBMS_OUTPUT.PUT_LINE (‘Product Price: ‘ || price);

===========================

Product Name: Intel Xeon E5-2697 V4
Product Price: 2554.99

PL/SQL procedure successfully completed.

DECLARE — define variables

Product_id NUMBER := 2;
ProductName VARCHAR2(255 BYTE);
Price NUMBER(9,2);

SELECT prod_name, Prod_sell — select data from these columns
INTO productName, price — insert them into the above declared variables
FROM products
WHERE product_id = prod_no; — will get 1 row or none

— now ouput the findings – assumed example worked
DBMS_OUTPUT.PUT_LINE (‘Product Name: ‘ || productName);
DBMS_OUTPUT.PUT_LINE (‘Product Price: ‘ || price);

What is the result????

FIX IT – it isn’t wrong but it does not show what we wanted to demonstrate

Fixing it to demo it working

DECLARE — define variables

Product_id NUMBER := 40100; — specific value
ProductName VARCHAR2(255 BYTE);
Price NUMBER(9,2);

SELECT prod_name, Prod_sell — select data from these columns
INTO productName, price — insert them into the above declared variables
FROM products
WHERE product_id = prod_no; — will get 1 row or none

— now output the findings – assumed example worked
DBMS_OUTPUT.PUT_LINE (‘Product Name: ‘ || productName);
DBMS_OUTPUT.PUT_LINE (‘Product Price: ‘ || price);

Product Name: Star Lite
Product Price: 165

PL/SQL procedure successfully completed.

SELECT INTO with more than one row retrieved
Creates an error.
Need to handle it

Change the problem
We change the condition to search for products with product type ‘Tents”
Since, we have many products in this type, the SELECT INTO statement generates an error

START WITH (using 20203 data)

ProductType VARCHAR2(20):= ‘Tents’;
productName VARCHAR2(255 BYTE);
price NUMBER(9,2);
SELECT prod_name, prod_sell INTO productName, price
FROM products
WHERE Prod_type = ProductType;

DBMS_OUTPUT.PUT_LINE (‘Product Name: ‘ || productName);
DBMS_OUTPUT.PUT_LINE (‘Product Price: ‘ || price);

ORA-01422: exact fetch returns more than requested number of rows

Fixing it(15)
ProductType VARCHAR2(20):= ‘Tents’;
productName VARCHAR2(255 BYTE);
price NUMBER(9,2);
SELECT prod_name, prod_sell INTO productName, price
FROM products
WHERE Prod_type = ProductType;

DBMS_OUTPUT.PUT_LINE (‘Product Name: ‘ || productName);
DBMS_OUTPUT.PUT_LINE (‘Product Price: ‘ || price);

WHEN TOO_MANY_ROWS
DBMS_OUTPUT.PUT_LINE (‘Too Many Rows Returned!’);

Too Many Rows Returned!

Change problem to NO DATA FOUND
And using an exception handler

productId NUMBER := 300;
productName VARCHAR2(255 BYTE);
price NUMBER(9,2);

SELECT prod_name, prod_sell INTO productName, price
FROM products
WHERE prod_no = productId;

DBMS_OUTPUT.PUT_LINE (‘Product Name: ‘ || productName);
DBMS_OUTPUT.PUT_LINE (‘Product Price: ‘ || price);

WHEN NO_DATA_FOUND
DBMS_OUTPUT.PUT_LINE (‘No Data Found!’);

No Data Found!
PL/SQL procedure successfully completed.

Pre-defined Exceptions
PL/SQL provides many pre-defined exceptions, which are executed when any database rule is violated by a program. For example, the predefined exception NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows. The following table lists few of the important pre-defined exceptions −
Oracle Error
Description

ACCESS_INTO_NULL
It is raised when a null object is automatically assigned a value.

CASE_NOT_FOUND
It is raised when none of the choices in the WHEN clause of a CASE statement is selected, and there is no ELSE clause.

COLLECTION_IS_NULL
It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.

DUP_VAL_ON_INDEX
It is raised when duplicate values are attempted to be stored in a column with unique index.

INVALID_CURSOR
It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.

INVALID_NUMBER
It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.

LOGIN_DENIED
It is raised when a program attempts to log on to the database with an invalid username or password.

NO_DATA_FOUND
It is raised when a SELECT INTO statement returns no rows.

NOT_LOGGED_ON
It is raised when a database call is issued without being connected to the database.

PROGRAM_ERROR
It is raised when PL/SQL has an internal problem.

ROWTYPE_MISMATCH
It is raised when a cursor fetches value in a variable having incompatible data type.

SELF_IS_NULL
It is raised when a member method is invoked, but the instance of the object type was not initialized.

STORAGE_ERROR
It is raised when PL/SQL ran out of memory or memory was corrupted.

TOO_MANY_ROWS
It is raised when a SELECT INTO statement returns more than one row.

VALUE_ERROR
It is raised when an arithmetic, conversion, truncation, or size constraint error occurs.

ZERO_DIVIDE
It is raised when an attempt is made to divide a number by zero.

Anonymous Blocks your first procedure

If a code is used multiple times or by different applications, then you need to store the block in the database.

Storing it is known as a stored procedure or stored function

#1 create a table called NEW_EMPLOYEE from employees table

Create table NEW_EMPLOYEES AS
(select * from employees);

Check data loaded

SIMPLE SAMPLE

Will remove employee 1

CREATE OR REPLACE PROCEDURE remove_employee AS — gave procedure a name
employeeId NUMBER;

employeeId := 1;

DELETE FROM new_employees
WHERE employee_id = employeeId;

WHEN OTHERS
DBMS_OUTPUT.PUT_LINE (‘Error!’);
Stored the procedure

OUTPUT: Procedure REMOVE_EMPLOYEE compiled

Check on left panel for procedure

Test it next page

Look for employee 1

select * from new_employees where employee_id = 1;

Bjorn is there

Run the procedure to remove the employee

remove_employee();

Test it again …

select * from new_employees where employee_id = 1;

CONTROL STATEMENTS (19)

Normal output of a procedure to update, delete etc indicates it was successful.
But we want to know the number of rows.

Leads to CONDITIONAL STATEMENTS

Here are 3 types

1 – Conditional selection statementsSame logic is found in other programming languages

2 – Loop statements (later)

3 – Sequential Control statements

Will discuss what it means

Look at examples

IF THEN Statement

CREATE the procedure with the new info

Recreate table first

DROP table new_employees;

Create table NEW_EMPLOYEES AS
(select * from employees);

Check that 2 exists

CREATE OR REPLACE PROCEDURE remove_employee AS
employeeId NUMBER;

employeeId := 2; — using employee 2

DELETE FROM new_employees
WHERE employee_id = employeeId;

IF SQL%ROWCOUNT = 0
DBMS_OUTPUT.PUT_LINE (‘Employee with ID ‘ || employeeId || ‘ does not exist’);

WHEN OTHERS
DBMS_OUTPUT.PUT_LINE (‘Error!’);

Then run it

remove_employee(); — removes employee 2

Run it again now that the employee is deleted

OUTPUT: Employee with ID 2 does not exist

IF THEN ELSE

Put the employees back together

DROP table new_employees;

Create table NEW_EMPLOYEES AS
(select * from employees);

select * from new_employees
order by employee_id; — note there is no employee 2

Create a procedure to remove employee 2
If it was removed, state employee 2 is deleted

CREATE OR REPLACE PROCEDURE remove_employee AS
employeeId NUMBER;

employeeId := 2;
DELETE FROM new_employees
WHERE employee_id = employeeId;

IF SQL%ROWCOUNT = 0
DBMS_OUTPUT.PUT_LINE (‘Employee with ID ‘ || employeeId || ‘ does not exist’);
DBMS_OUTPUT.PUT_LINE (‘Employee with ID ‘ || employeeId || ‘ DELETED!’);

WHEN OTHERS
DBMS_OUTPUT.PUT_LINE (‘Error!’);

Procedure REMOVE_EMPLOYEE compiled

remove_employee();

Employee with ID 2 does not exist

IF THEN — ELSE

But testing the other IF or else

Do NOT ROLLBACK — leave employee_id 2 as deleted.

CREATE OR REPLACE PROCEDURE remove_employee AS
employeeId NUMBER;
employeeId := 2;
DELETE FROM new_employees
WHERE employee_id = employeeId;

IF SQL%ROWCOUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE (‘Employee with ID ‘ || employeeId || ‘ does not exist’);
DBMS_OUTPUT.PUT_LINE (‘Employee with ID ‘ || employeeId || ‘ DELETED!’);

WHEN OTHERS
DBMS_OUTPUT.PUT_LINE (‘Error!’);

Now run it again

remove_employee();

Employee with ID 2 does not exist

IF THEN ELSIF

Run this code notice manager id of 124 … there are several of them

CREATE OR REPLACE PROCEDURE remove_employee AS
managerId NUMBER;

managerId := 124;
DELETE FROM new_employees
WHERE manager_id = managerId;

IF SQL%ROWCOUNT = 0
DBMS_OUTPUT.PUT_LINE (‘No employee is deleted’);
SQL%ROWCOUNT = 1
DBMS_OUTPUT.PUT_LINE (‘One employee is deleted.’);
DBMS_OUTPUT.PUT_LINE (‘More than one employee is deleted!’);

WHEN OTHERS
DBMS_OUTPUT.PUT_LINE (‘Error!’);

More than one employee is deleted!

NESTING – IF THEN ELSE

Sample syntax

IF condition THENAgain, the logic is like any other language

IF condition THEN
statements
ELSE condition
statements
IF condition THEN
statements
statements

CASE selector
WHEN value_1 THEN statements
WHEN value_2 THEN statements
WHEN value_n THEN statements

statements ]

As soon as a value matches, the statement is executed

If no match occurs, then the ELSE executes …. If there is an ELSE

Example next page

Example of CASE

semester CHAR(1);
semester := ‘S’;

CASE semester
WHEN ‘F’ THEN DBMS_OUTPUT.PUT_LINE(‘Fall Term’);
WHEN ‘W’ THEN DBMS_OUTPUT.PUT_LINE(‘Winter Term’);
WHEN ‘S’ THEN DBMS_OUTPUT.PUT_LINE(‘Summer Term’);
ELSE DBMS_OUTPUT.PUT_LINE(‘Wrong Value’);

Summer Term

PL/SQL procedure successfully completed.

Same using IF ELSEIF

Look on slide 27

EXAMPLE of INPUT from user

/* begin comments with this will save the – every line
Write a store procedure that gets an integer number and prints
the number is even……..
*/ end with this

set serveroutput ON; — DO NOT forget to do this at start of session

CREATE OR REPLACE PROCEDURE evenodd (instuff IN number)

if mod(instuff, 2) = 0
then dbms_output.put_line(‘The number is even!’);
dbms_output.put_line(‘The number is odd!’);

END evenodd;

–execution statement taking an input from user and passing it to the procedure
evenodd(&input); — asks for input from user

Les06 PLSQL part 1 intro by rt — 8 March 2022 1 of 1

程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com