(1)Store Procedure/Function
OPEN UP ORACLE
Copyright By PowCoder代写 加微信 powcoder
Then you can cut and paste and try these code examples
AGENDA – topics
1 Conditional Statements
2 Iteration Statements
4 User-defined Functions
Conditional Statements — Repeat/Review
(3)(4 syntax)
The search CASE statement executes the statement of the first true condition.
The remaining conditions are not evaluated after the first true condition.
WHEN condition_1 THEN statements
WHEN condition_2 THEN statements
WHEN condition_n THEN statements
statements ]
Previous example:
SET SERVEROUTPUT ON; reminder
semester CHAR(1);
semester := ‘S’; — defined semester with value S
WHEN semester = ‘F’ THEN DBMS_OUTPUT.PUT_LINE(‘Fall Term’);
WHEN semester = ‘W’ THEN DBMS_OUTPUT.PUT_LINE(‘Winter Term’);
WHEN semester = ‘S’ THEN DBMS_OUTPUT.PUT_LINE(‘Summer Term’);
ELSE DBMS_OUTPUT.PUT_LINE (‘Wrong Value’);
BUT … what if it is none of the choices Do an exception
semester CHAR(1);
semester := ‘J’; — change choice
WHEN semester = ‘F’ THEN DBMS_OUTPUT.PUT_LINE(‘Fall Term’);
WHEN semester = ‘W’ THEN DBMS_OUTPUT.PUT_LINE(‘Winter Term’);
WHEN semester = ‘S’ THEN DBMS_OUTPUT.PUT_LINE(‘Summer Term’);
–ELSE DBMS_OUTPUT.PUT_LINE (‘Wrong Value’); remove this line
WHEN CASE_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No Semester Found’);
2 Iteration Statements
LOOP Statements
Same as any other programming language
Same as SELECT in SQL in what it does — it loops
LOOP and variations to loop processing
· A LOOP statements runs a series of statements multiple times.
· Basic LOOP
· FOR LOOP
· Cursor FOR LOOP
· WHILE LOOP
· Statement or conditions to exit a loop:
· EXIT WHEN
· The statements that exits the current iteration of a loop only and skips to the next iteration.
· CONTINUE
· CONTINUE WHEN
Basic LOOP
The loop executes the statements until an EXIT statement terminates the loop execution or an exception is raised.
Just like other programming languages
The EXIT statement terminates the loop and transfers the control to the end of the current loop
Look at a sample
counter NUMBER := 3; — setting counter to 3
DBMS_OUTPUT.PUT_LINE (‘—- Count Down —–‘); — put up a title
DBMS_OUTPUT.PUT_LINE (‘COUNTER #: ‘ || counter); — shows loop value
counter := counter – 1;
IF counter < 1 THENExiting out of a loop when you know the condition was considered bad form
DBMS_OUTPUT.PUT_LINE('End of the LOOP!');
---- Count Down -----EXTRA NOTE: If procedure is not "created" and compiled it will run. Other wise you need a BEGIN run it and END
COUNTER #: 3
COUNTER #: 2
COUNTER #: 1
End of the LOOP!
The test is at the end, so it always enters the loop once
counter NUMBER := 5;
DBMS_OUTPUT.PUT_LINE ('---- Count Down -----');
DBMS_OUTPUT.PUT_LINE ('counter: ' || counter);
counter := counter - 1;
EXIT WHEN counter < 3;
DBMS_OUTPUT.PUT_LINE('End of the LOOP!');
---- Count Down -----
counter: 5
counter: 4
counter: 3
End of the LOOP!
C equivalent – do … while
NESTED LOOPS
A LOOP statement can be inside another LOOP statement.
The EXIT statement inside the inner LOOP exits the inner LOOP
- and transfers the control to the outer loop.
My favourite i and j variables
i NUMBER := 0;
j NUMBER := 2;
DBMS_OUTPUT.PUT_LINE('Beginning of the Code!');
i := i + 1;
DBMS_OUTPUT.PUT_LINE ('---- i: ' || i); -- I is now 1 first time
LOOP -- enter the inner loop
DBMS_OUTPUT.PUT_LINE ('-- j: ' || j); -- j is 1 the first time
j := j - 1; -- j increments by 1 and is now 2
EXIT WHEN j < 0; -- it is not less than one , stay in inner loop
EXIT WHEN i > 1;
DBMS_OUTPUT.PUT_LINE(‘End of the Code!’);
The output
Beginning of the Code!
—- i: 1There is nothing new here. Once you learn one or two languages the others are vey similar.
End of the Code!
The CONTINUE statement exits the current iteration of the loop and goes to the next iteration.
In the example: The following code does not output value 2 for the variable counter.
counter NUMBER := 4;
DBMS_OUTPUT.PUT_LINE (‘—- Count Down —–‘);
counter := counter – 1;
IF counter = 2 THEN
DBMS_OUTPUT.PUT_LINE (‘counter: ‘ || counter);
EXIT WHEN counter < 1;
DBMS_OUTPUT.PUT_LINE('End of the LOOP!');
---- Count Down -----Drops out of the rest of the loop but continues back in the loop
counter: 3
counter: 1
counter: 0
End of the LOOP!
CONTINUE WHEN
Looks to do the same thing.
counter NUMBER := 4;
DBMS_OUTPUT.PUT_LINE ('---- Count Down -----');
counter := counter - 1; IF counter = 2 THEN
CONTINUE WHEN counter = 2;
DBMS_OUTPUT.PUT_LINE ('counter: ' || counter);
EXIT WHEN counter < 1;
DBMS_OUTPUT.PUT_LINE('End of the LOOP!');
counter: 3
counter: 1
counter: 0
End of the LOOP!
Again similar
The FOR LOOP statement executes the statements inside the loop while the value of the loop index is in a given range.
starts at lower number and increments by 1 until upper condition met.
IF you include the REVERSE keyword, the value of index starts from the upper bound value and deceases by one until it becomes equal to the lower bound value.
Of course, the upper bound value must be greater than or equal to the lower bound value.
Index is the local variable of the FOR loop.
FOR index IN [ REVERSE ] lower_bound … upper_bound LOOP
statements
EXAMPLE: FOR LOOP
Can space it 1 .. 4
FOR i IN 1..4 LOOP
IF i < 2 THEN
DBMS_OUTPUT.PUT_LINE ( i || ' is less than 2');
ELSIF i > 2 THEN
DBMS_OUTPUT.PUT_LINE ( i || ‘ is greater than 2’);
DBMS_OUTPUT.PUT_LINE ( i || ‘ is equal to 2’);
1 is less than 2
2 is equal to 2
3 is greater than 2
4 is greater than 2
NESTED FOR LOOPS
Same idea as any language
FOR x IN 1 .. 2 LOOP
DBMS_OUTPUT.PUT_LINE (‘—- x: ‘ || x );
FOR y IN REVERSE 1 .. 4 LOOP
DBMS_OUTPUT.PUT_LINE (‘– y: ‘ || y );
OUTPUT because I and j are harder to see I switched to x and y
WHILE LOOP
The WHILE executes if the condition is TRUE.
It stops when FALSE or an EXIT
Control passes to the statement after the WHILE loop
run BOOLEAN := true;
round NUMBER := 1;
DBMS_OUTPUT.PUT_LINE (‘– First WHILE LOOP –‘);
WHILE run LOOP
DBMS_OUTPUT.PUT_LINE (’round ‘ || round);
round := round + 1;
IF round = 4 THEN
run := false;
DBMS_OUTPUT.PUT_LINE (‘– Second WHILE LOOP –‘);
WHILE NOT run LOOP
DBMS_OUTPUT.PUT_LINE (’round ‘ || round);
round := round – 1;
IF round = 0 THEN
run := true;
Can also use an ordinary loop control instead of boolean
round NUMBER := 1;
DBMS_OUTPUT.PUT_LINE (‘– First WHILE LOOP –‘);
WHILE round <5 LOOP
DBMS_OUTPUT.PUT_LINE ('round ' || round);
round := round + 1;
-- First WHILE LOOP --
-- Second WHILE LOOP --
Cursors are used to process multiple rows in PL/SQL blocks.
In this course, we learn fundamentals about cursors.
We use cursors to return multiple rows from a PL/SQL procedure to a caller procedure or program.
Lots of words…. Let us see what it means
PL/SQL CURSORS
A cursor is a pointer to a context area that includes the result of a processed SQL statement.
Translation: Simply, a cursor contains the rows of a select statement.
In PL/SQL, cursors are used to access and process the rows returned by a SELECT statement.
There are two types of cursors:
· Implicit cursors
· Explicit cursors
IMPLICIT CURSOR (22)
One that is not defined … implied
We do not have this table. Will need to be improved
Go to next page
Implicit Cursor Attributes
Following are implicit cursor attributes,
Cursor Attribute
Cursor Variable
Description
SQL%ISOPEN
Oracle engine automatically open the cursor
If cursor open return TRUE otherwise return FALSE.
If SELECT statement return one or more rows or DML statement (INSERT, UPDATE, DELETE) affect one or more rows
If affect return TRUE otherwise return FALSE.
If not execute SELECT or DML statement return NULL.
SQL%NOTFOUND
If SELECT INTO statement return no rows and fire no_data_found PL/SQL exception before you can check SQL%NOTFOUND.
If not affect the row return TRUE otherwise return FALSE.
SQL%ROWCOUNT
Return the number of rows affected by a SELECT statement or DML statement (insert, update, delete).
If not execute SELECT or DML statement return NULL.
to page 20
Using EMP table if loaded in week 6
EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID HIREDATE SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------- ---------- ---------- -------------
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7782 CLARK MANAGER 7839 81-06-09 2450 10
7788 SCOTT ANALYST 7566 87-04-19 3000 20
7839 KING PRESIDENT 81-11-17 5000 10
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7876 ADAMS CLERK 7788 87-05-23 1100 20
7900 JAMES CLERK 7698 81-12-03 950 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7934 MILLER CLERK 7782 82-01-23 1300 10
14 rows selected.
set serveroutput on
UPDATE emp
SET job = 'Web Dev'
WHERE employee_name='MILLER';
IF SQL%FOUND THEN
dbms_output.put_line('Updated - If Found employee');
IF SQL%NOTFOUND THEN
dbms_output.put_line('NOT Updated - If employee NOT Found');
IF SQL%ROWCOUNT>0 THEN
dbms_output.put_line(SQL%ROWCOUNT||’ Rows Updated’);
dbms_output.put_line(‘NO Rows were found Updated Found’);
Updated – If Found employee
1 Rows Updated
EXPLICIT CURSOR
The explicit cursors are defined in the declaration section of a PL/SQL block
Defined by user … programmers.
It is used to process the multi-row results from a SELECT statement.
Define cursor:
CURSOR cursor_name IS select_statement;
Go to specifics PP 24
DECLARE A CURSOR step 1
Cursors can be defined in the DECLARE section
CURSOR cursor_name IS select_statement;
CURSOR cursor_1 ISTest run just the SQL
This is 20203 script
SELECT last_name, job_id
FROM employees
WHERE job_id LIKE ‘A%’
ORDER BY last_name;
LAST_NAME JOB_ID
————————- ———-
Flertjan AC_REP
Gietz AC_ACCOUNT
Higgins AC_MGR
King AD_PRES
Kochhar AD_VP
Whalen AD_ASST
7 rows selected.
OPEN A CURSOR step 2
Done in the executable portion. After the BEGIN.
e_last_name employees.last_name%type;
e_job_tile employees.job_id%type;
CURSOR emp_cursor IS
SELECT last_name, job_id
FROM employees
WHERE job_title LIKE ‘A%’
ORDER BY last_name;
OPEN emp_cursor;
CLOSE A CURSOR step 3
(27 and 28 example)
See example
e_last_name employees.last_name%type;
e_job_tile employees.job_id%type;
CURSOR emp_cursor IS
SELECT last_name, job_id
FROM employees
WHERE job_id LIKE ‘A%’
ORDER BY last_name;
OPEN emp_cursor;
FETCH emp_cursor into e_last_name, e_job_tile;
EXIT WHEN emp_cursor%notfound;
dbms_output.put_line(e_last_name || ‘ ‘ || e_job_tile);
CLOSE emp_cursor;
Flertjan AC_REP
Gietz AC_ACCOUNT
Higgins AC_MGR
King AD_PRES
Kochhar AD_VP
Whalen AD_ASST
EXPLICIT CURSORS with parametersThe %ROWTYPE attribute lets you declare a record that represents a row in a table or view
DECLAREDefined with 2 parameters
p_product products%rowtype;
CURSOR product_cursor (price_1 NUMBER, price_2 NUMBER)
FROM products
WHERE prod_sell BETWEEN price_1 AND price_2;
OPEN product_cursor (100, 500); — parameters
FETCH product_cursor INTO p_product;
EXIT WHEN product_cursor%notfound;
dbms_output.put_line(p_product.prod_name || ‘: ‘ ||p_product.prod_sell);
CLOSE product_cursor;
Star Lite: 200
MoonBeam: 120
MoonGlow: 129
Pack n’ Hike: 131
Dover-2: 111
Pro-Lite Water Filter: 165
Pocket Water Filter: 270
EXPLICIT CURSORS with parameters
FOR LOOPS will open cursor and close the cursor
when no more rows found
e_last_name employees.last_name%type;
e_job_tile employees.job_id%type;
CURSOR emp_cursor IS
SELECT last_name, job_id
FROM employees
WHERE job_id LIKE ‘A%’
ORDER BY last_name;
FOR item IN emp_cursor — begins a FOR loop
DBMS_OUTPUT.PUT_LINE
(‘NAME = ‘ || item.last_name || ‘, JOB = ‘ || item.job_id);
IF emp_cursor%ISOPEN THEN
CLOSE emp_cursor;
NAME = , JOB = AD_VP
NAME = Flertjan, JOB = AC_REP
NAME = Gietz, JOB = AC_ACCOUNT
NAME = Higgins, JOB = AC_MGR
NAME = King, JOB = AD_PRES
NAME = Kochhar, JOB = AD_VP
NAME = Whalen, JOB = AD_ASST
EXPLICIT CURSOR ATTRIBUTES
Look over these later
Attributes
TRUE: if the cursor is open
FALSE: if the cursor is not open
INVALID_CURSOR: if the cursor is not open
NULL: before we fetch the first row
FALSE: if the fetch row is successfully
TRUE: if no row is fetched in the fetch statement
INVALID_CURSOR: if the cursor is not open.
NULL: before we fetch the first row
INVALID_CURSOR: if the cursor is not open
Otherwise: It returns the number of rows returned from the cursor
Adding a bit more
Put a counter in to see how many rows generated.
cnt NUMBER :=0; — start a counter
e_last_name employees.last_name%type;
e_job_tile employees.job_id%type;
CURSOR emp_cursor IS
SELECT last_name, job_id
FROM employees
WHERE job_id LIKE ‘A%’
ORDER BY last_name;
FOR item IN emp_cursor — begins FOR loop
DBMS_OUTPUT.PUT_LINE
(‘NAME = ‘ || item.last_name || ‘, JOB = ‘ || item.job_id);
cnt := cnt + 1;
IF cnt >0 THEN
dbms_output.put_line(cnt || ‘ Rows Updated’);
dbms_output.put_line(‘NO Rows were found Updated Found’);
IF emp_cursor%ISOPEN THEN
CLOSE emp_cursor;
NAME = , JOB = AD_VP
NAME = Flertjan, JOB = AC_REP
NAME = Gietz, JOB = AC_ACCOUNT
NAME = Higgins, JOB = AC_MGR
NAME = King, JOB = AD_PRES
NAME = Kochhar, JOB = AD_VP
NAME = Whalen, JOB = AD_ASST
7 Rows Updated
USER-DEFINED FUNCTIONS
Near the end
Create a PL/SQL Function
CREATE [OR REPLACE] FUNCTION function_name (parameter_list)
RETURN return_type
[declarative section]
[executable section]
[EXCEPTION]
[exception-handling section]
RETURN return_value
PL/SQL Function Example
CREATE OR REPLACE FUNCTION find_max_price
RETURN NUMBER
max_price NUMBER := 0;
— get the maximum prod_sell price
SELECT MAX(prod_sell)
INTO max_price
FROM products;
— return the max price
RETURN max_price;
Run the above just compiles it
It returns maximum list price, but we never showed what it was.
Using Functions in Assignment Statements
A function returns a value
Assign the value to a variable and use it
highest_price products.prod_sell%type := 0.0;
highest_price := find_max_price(); — call the function
dbms_output.put_line(‘The maximum price is ‘ || highest_price); — output the results
The maximum price is 8867.99
Using Functions in Conditional Statements
new_price products.prod_sell%type := 9;
IF (new_price < find_max_price()) THEN -- used the function for comparison dbms_output.put_line('The new price is lower than the maximum price.'); dbms_output.put_line('The new price is higher than the maximum price.'); Use PL/SQL Functions in SQL Statements PROBLEM: Company wished to double the price of each product. Return a list of products where the new doubled price is greater than the current maximum price EXAMPLE SQL SELECT prod_no, prod_name, prod_sell, (prod_sell * 2) as " " FROM products WHERE (prod_sell * 2) > find_max_price();Once you built it, you can use it in just SQL only
PROD_NO PROD_NAME PROD_SELL
———- —————————— ———- ———-
40101 Star Gazer-2 553 1106
40102 Star Gazer-3 590 1180
40103 StarDome 650 1300
DROP FUNCTION
DROP FUNCTION function_name;
Extra sample:
Showing control over output to get another layout
cursor XX is select *
where employee_id <=7600;
tmp emp%rowtype;
-- OPEN X; -- opened and close by FOR loop
FOR tmp IN XX
dbms_output.put_line('No: '||tmp.employee_id);
dbms_output.put_line('Name: '||tmp.employee_name);
dbms_output.put_line('Job: '||tmp.job);
dbms_output.put_line('Salary:'||tmp.salary);
dbms_output.put_line(' --------------------');
-- CLOSE X;
No: 7369
Name: SMITH
Job: CLERK
Salary:800
--------------------
No: 7499
Name: ALLEN
Job: SALESMAN
Salary:1600
--------------------
No: 7521
Name: WARD
Job: SALESMAN
Salary:1250
--------------------
No: 7566
Name: JONES
Job: MANAGER
Salary:2975
--------------------
Document1 by rt -- 8 November 2020 1 of 1
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com