programming in oracle with pl/sql
DESCRIPTION
PL/SQL PL/SQL Blocks Anonymous blocks Using select statement Using conditions Loops Printing output Named blocks Functions Procedures TriggersTRANSCRIPT
n PL/SQL
n PL/SQL Blocks
n Anonymous blocksn Using select statement
n Using conditions
n Loops
n Printing output
n Named blocksn Functions
n Procedures
n Triggers
PL/SQL
n Allows using general programming tools with SQL, for example: loops, conditions, functions, etc.
n This allows a lot more freedom than general SQL, and is lighter-weight than JDBC.
n We write PL/SQL code in a regular file, for example PL.sql, and load it with @PL in the sqlplus console.
PL/SQL Blocks
n PL/SQL code is built of Blocks, with a unique
structure.
n There are two types of blocks in PL/SQL:
1. Anonymous Blocks: have no name (like scripts)
n can be written and executed immediately in SQLPLUS
n can be used in a trigger
2. Named Blocks:
n Procedures
n Functions
Anonymous Block Structure:
DECLARE (optional)/* Here you declare the variables you will use in this block */
BEGIN (mandatory)/* Here you define the executable statements (what the block
DOES!)*/
EXCEPTION (optional)/* Here you define the actions that take place if an exception is
thrown during the run of this block */
END; (mandatory)/
A correct completion of a block will generate the following message:
PL/SQL procedure successfully completed
DECLARE
Syntax
Examples
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];
Declarebirthday DATE;age NUMBER(2) NOT NULL := 27;name VARCHAR2(13) := 'Levi';magic CONSTANT NUMBER := 77;valid BOOLEAN NOT NULL := TRUE;
SELECT Statements
n INTO clause is required.
n Query must return exactly one row.
n Otherwise, a NO_DATA_FOUND or TOO_MANY_ROWS exception is thrown
DECLAREv_sname VARCHAR2(10);v_rating NUMBER(3);
BEGINSELECT sname, ratingINTO v_sname, v_ratingFROM SailorsWHERE sid = '112';
END;/
Conditional logic
If <cond>
then <command>
elsif <cond2>
then <command2>
else
<command3>
end if;
If <cond>then
if <cond2>then
<command1>end if;
else <command2>end if;
Condition: Nested conditions:
IF-THEN-ELSIF Statements
. . .IF rating > 7 THENv_message := 'You are great';
ELSIF rating >= 5 THEN v_message := 'Not bad';
ELSEv_message := 'Pretty bad';
END IF;. . .
Suppose we have the following table:
n Want to keep track of how many times someone logged on to the DB
n When running, if user is already in table, increment logon_num. Otherwise, insert user into table
create table mylog(
who varchar2(30),
logon_num number
);
2Moshe
4John
3Peter
logon_num
who
mylog
Solution
DECLAREcnt NUMBER;
BEGINselect count(*)into cntfrom mylogwhere who = user;
if cnt > 0 thenupdate mylogset logon_num = logon_num + 1
where who = user;else insert into mylog values(user, 1);
end if;commit;
end;/
Loops: FOR Loop
DECLAREi number_table.num%TYPE;
BEGINFOR i IN 1..10 LOOPINSERT INTO number_table VALUES(i);
END LOOP;END;
Notice that i is incremented automatically
Loops: WHILE Loop
DECLARETEN number:=10;i number_table.num%TYPE:=1;BEGINWHILE i <= TEN LOOP
INSERT INTO number_table VALUES(i);i := i + 1;
END LOOP;END;
Printing Output
n You need to use a function in the DBMS_OUTPUT
package in order to print to the output
n If you want to see the output on the screen, you
must type the following (before starting):
set serveroutput on format wrapped size 1000000
n Then print using
n dbms_output. put_line(your_string);
n dbms_output.put(your_string);
Functions and Procedures
n Up until now, our code was in an anonymous block
n It was run immediately
n It is useful to put code in a function or procedure so it can be called several times
n Once we create a procedure or function in a Database, it will remain until deleted (like a table).
CREATE [OR REPLACE] PROCEDURE procedure_name[(parameter1 [mode1] datatype1,parameter2 [mode2] datatype2,. . .)]
IS|ASPL/SQL Block;
Creating Procedures
n Modes:n IN: procedure must be called with a value for the parameter.
Value cannot be changed
n OUT: procedure must be called with a variable for the parameter. Changes to the parameter are seen by the user (i.e., call by reference)
n IN OUT: value can be sent, and changes to the parameter are seen by the user
n Default Mode is: IN
create or replace procedure num_logged(person IN mylog.who%TYPE,num OUT mylog.logon_num%TYPE)ISBEGIN
select logon_num into numfrom mylogwhere who = person;
END;/
Example- what does this do?
2Joe
4John
3Pete
logon_
numwho
Table mylog
declarehowmany mylog.logon_num%TYPE;
beginnum_logged(‘John',howmany);dbms_output.put_line(howmany);
end;/
Calling the Procedure
Errors in a Procedure
n When creating the procedure, if there are errors in its definition, they will not be shown
n To see the errors of a procedure called myProcedure, type
SHOW ERRORS PROCEDURE myProcedure
in the SQLPLUS prompt
n For functions, type
SHOW ERRORS FUNCTION myFunction
Creating a Function
n Almost exactly like creating a procedure, but you supply a return type
CREATE [OR REPLACE] FUNCTIONfunction_name[(parameter1 [mode1] datatype1,parameter2 [mode2] datatype2,. . .)]
RETURN datatypeIS|ASPL/SQL Block;
create or replace function rating_message(rating IN NUMBER)return VARCHAR2ASBEGINIF rating > 7 THENreturn 'You are great';
ELSIF rating >= 5 THEN return 'Not bad';
ELSEreturn 'Pretty bad';
END IF;END;/
A Function
NOTE THAT YOU DON'T SPECIFY THE
SIZE
create or replace function squareFunc(num in number)
return number
is
BEGIN
return num*num;
End;
/
BEGINdbms_output.put_line(squareFunc(3.5));END;/
Creating a function:
Using the function:
Triggers
n A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database.
n To design a trigger mechanism, we must:
n Specify the conditions under which the trigger is to be executed.
n Specify the actions to be taken when the trigger executes.
Trigger Example
n Suppose that instead of allowing negative account balances, the bank deals with overdrafts by n setting the account balance to zero
n creating a loan in the amount of the overdraft
n giving this loan a loan number identical to the account number of the overdrawn account
n The condition for executing the trigger is an update to the account relation that results in a negative balance value.
Trigger Example
create trigger overdraft-trigger after update on account referencing new row as nrow for each rowwhen nrow.balance < 0begin atomic
insert into borrower(select customer-name, account-numberfrom depositorwhere nrow.account-number =
depositor.account-number);insert into loan values
(n.row.account-number, nrow.branch-name, – nrow.balance);
update account set balance = 0where account.account-number = nrow.account-number
end