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