ORACLE programming
1. PL/SQL
1.1 what is pl/sql
- Procedure processing statements are added to sql language processing
--Syntax:
[declare
--Declare variable
]
begin
--Logic code
[exception
--exception handling
]
end;
1.2 variables
Syntax for declaring variables:
Variable name type (length);
Syntax of variable assignment:
Variable name:=Variable value;
Case:
Declare the variable water tariff unit price, water tariff words, tons and amount.
Assign values to the unit price, number of words and of water charges.
The tonnage is converted according to the number of words of water charge. The rule is that the number of words of water charge is divided by 1000 and rounded to two decimal places.
Calculate amount, amount = unit price * tons.
Output unit price, quantity and amount.
declare
--Declare variable
v_waterprice number(10,2);--Unit price of water charge
v_usenum number;--Water words
v_usenum2 number(10,2);--Water tonnage
v_money number(10,2);--amount of money
begin
--Variable assignment
v_waterprice:=2.45;
v_usenum:=8012;
v_usenum2:=round(v_usenum/1000,2);
v_money:=round(v_waterprice*v_usenum2,2);
--output
dbms_output.put_line('Unit Price:'||v_waterprice
||' number:'||v_usenum2||' amount of money:'||v_money);
end;
Select into assignment:
--Syntax: select Column name 1,Column name 2 into Variable name 1, variable name 2 from Table name where condition
Note: the result must be one record. Errors will be reported if there are multiple records or no records
--select into
declare
--Declare variable
v_waterprice number(10,2);--Unit price of water charge
v_usenum number;--Water words
v_usenum2 number(10,2);--Water tonnage
v_money number(10,2);--amount of money
begin
--Variable assignment
v_waterprice:=3.45;
--select into assignment
select t.usenum into v_usenum from t_account t where t.year='2012' and t.month='01' and t.owneruuid='1';
v_usenum2:=round(v_usenum/1000,2);
v_money:=round(v_waterprice*v_usenum2,2);
--output
dbms_output.put_line('Unit Price:'||v_waterprice
||' number:'||v_usenum2||' amount of money:'||v_money);
end;
1.3 attribute type
1.% type reference type: refers to the field type of a column in a table
--%type Reference type (Table name.Field name%type)
declare
--Declare variable
v_waterprice number(10,2);--Unit price of water charge
v_usenum t_account.usenum%type;--Water words
v_usenum2 number(10,2);--Water tonnage
v_money number(10,2);--amount of money
begin
--Variable assignment
v_waterprice:=3.45;
--select into assignment
select t.usenum into v_usenum from t_account t where t.year='2012' and t.month='01' and t.owneruuid='1';
v_usenum2:=round(v_usenum/1000,2);
v_money:=round(v_waterprice*v_usenum2,2);
--output
dbms_output.put_line('Unit Price:'||v_waterprice
||' number:'||v_usenum2||' amount of money:'||v_money);
end;
2.% rowtype record type: identifies the row record type of a table
--%rowtype Record type (Table name%rowtype)
declare
--Declare variable
v_waterprice number(10,2);--Unit price of water charge
v_account t_account%rowtype;--Water words
v_usenum2 number(10,2);--Water tonnage
v_money number(10,2);--amount of money
begin
--Variable assignment
v_waterprice:=3.45;
--select into assignment
select * into v_account from t_account t where t.year='2012' and t.month='01' and t.owneruuid='1';
v_usenum2:=round(v_account.usenum/1000,2);
v_money:=round(v_waterprice*v_usenum2,2);
--output
dbms_output.put_line('Unit Price:'||v_waterprice
||' number:'||v_usenum2||' amount of money:'||v_money);
end;
1.4 abnormality
-
After an exception occurs, the statement will stop execution and control will be transferred to the exception handling part of the PL/SQL block
-
There are two types of exceptions: predefined exceptions and user-defined exceptions
-
Predefined exception - implicitly thrown when the PL/SQL program violates Oracle rules or exceeds system limits
NO_DATA_FOUND: use select into to return no rows
TOO_MANY_ROWS: the result set exceeds one row when select into is executed
-
User defined Exceptions - users can define exceptions in the declaration part of the PL/SQL block, and custom exceptions are explicitly raised through the RAISE statement
--Syntax: exception when Exception type then Exception handling logic
--abnormal
declare
--Declare variable
v_waterprice number(10, 2); --Unit price of water charge
v_account t_account%rowtype; --Water words
v_usenum2 number(10, 2); --Water tonnage
v_money number(10, 2); --amount of money
begin
--Variable assignment
v_waterprice := 3.45;
--select into assignment
select *
into v_account
from t_account t
where t.year = '2012'
and t.month = '01' and t.owneruuid='1';
v_usenum2 := round(v_account.usenum / 1000, 2);
v_money := round(v_waterprice * v_usenum2, 2);
--output
dbms_output.put_line('Unit Price:' || v_waterprice || ' number:' || v_usenum2 ||
' amount of money:' || v_money);
exception
when too_many_rows then
dbms_output.put_line('Multiple rows of records were returned');
when no_data_found then
dbms_output.put_line('No records found');
end;
1.5 condition judgment
--Syntax 1: if condition then logic end if; --Syntax 2: if condition then logic else logic end if; --Syntax 3: if condition then logic elsif condition then logic else logic end if;
Case:
Three levels of water charges are set: 2.45 yuan / ton below 5 tons, 3.45 yuan / ton for 5 tons to 10 tons and 4.45 yuan / ton for more than 10 tons. The stepped water charge is calculated according to the amount of water charge.
--Condition judgment:
--Set three levels of water charges below 5 tons 2.45 element/Tons,
--5 3 tons to 10 tons.45 element/Tons,
--Part exceeding 10 tons 4.45 ，
--The step water charge is calculated according to the amount of water charge used.
declare
v_waterprice1 number(10,2);--Unit Price
v_waterprice2 number(10,2);--Unit Price
v_waterprice3 number(10,2);--Unit Price
v_usenum t_account.usenum%type;
v_usenum2 number(10,2);--ton
v_money number(10,2);--amount of money
begin
v_waterprice1:=2.45;
v_waterprice2:=3.45;
v_waterprice3:=4.45;
select a.usenum into v_usenum from t_account a where a.year='2012' and a.month='02' and a.owneruuid='1';
v_usenum2:=round(v_usenum/1000,2);
if v_usenum2<=5 then
v_money:=round(v_usenum2*v_waterprice1,2);
elsif v_usenum2<=10 then
v_money:=round((5*v_waterprice1+(v_usenum2-5)*v_waterprice2),2);
else
v_money:=round((5*v_waterprice1+5*v_waterprice2+(v_usenum2-10)*v_waterprice3),2);
end if;
dbms_output.put_line('water consumption:'||v_usenum2||' amount of money:'||v_money);
end;
1.6 cycle
1.6.1 unconditional cycle
--Syntax: loop --Circular statement end loop;
--Unconditional cycle
declare
v_num number;
begin
v_num:=1;
loop
exit when v_num>100;
dbms_output.put_line(v_num);
v_num:=v_num+1;
end loop;
end;
1.6.2 conditional cycle
--Syntax: while condition loop --Circular statement end loop;
--Conditional cycle
declare
v_num number:=1;
begin
while v_num<=100
loop
dbms_output.put_line(v_num);
v_num:=v_num+1;
end loop;
end ;
1.6.3 for cycle
--Syntax: for variable in Starting value..Termination value loop --Circular statement end loop;
--for loop
begin
for x in 1 .. 100
loop
dbms_output.put_line(x);
end loop;
end;
1.7 cursor
1.7.1 what is a cursor
- Cursor is a data buffer set up by the system for users to store the execution results of SQL statements.
- We can understand cursors as result sets in PL/SQL
1.7.2 cursor syntax structure and examples
-
Declare the cursor in the declaration area. The syntax is as follows
cursor Cursor name is sql sentence;
-
Use cursor syntax
open Cursor name; loop fetch Cursor name into variable; exit when Cursor name%notfound; end loop; close Cursor name;
Print price list with owner type 1:
--cursor
--Print price list with owner type 1
declare
v_pricetable t_pricetable%rowtype;--Price line object
cursor cur_price is select * from t_pricetable a where a.ownertypeid='1';--Define cursor
begin
open cur_price;--Open cursor
loop
fetch cur_price into v_pricetable;--Extract cursor to variable
exit when cur_price%notfound;--Exit the loop when the cursor is below the last line
dbms_output.put_line('Price:'||v_pricetable.price
||' Power consumption:' ||v_pricetable.minnum||'-'||v_pricetable.maxnum);
end loop;--End cycle
close cur_price;--Close cursor
end;
1.7.3 cursor with parameters
- The condition value of the query statement may be determined at run time. For example, the owner type may be determined at run time
--Cursor with parameters
--Print price list with owner type 1
declare
v_pricetable t_pricetable%rowtype;--Price line object
cursor cur_price(v_ownertype t_pricetable.ownertypeid%type) is
select * from t_pricetable a where a.ownertypeid=v_ownertype;--Define cursor
begin
open cur_price(1);--Open cursor
loop
fetch cur_price into v_pricetable;--Extract cursor to variable
exit when cur_price%notfound;--Exit the loop when the cursor is below the last line
dbms_output.put_line('Price:'||v_pricetable.price
||' Power consumption:' ||v_pricetable.minnum||'-'||v_pricetable.maxnum);
end loop;--End cycle
close cur_price;--Close cursor
end;
1.7.4 for loop fetching cursor value
for loop fetching cursor value: there is no need to define variables, write open cursors, close cursors, fetch cursors, and control the exit of the loop
-- for Loop fetch cursor value
--Print price list with owner type 1
declare
cursor cur_price(v_ownertype t_pricetable.ownertypeid%type) is
select * from t_pricetable a where a.ownertypeid=v_ownertype;--Define cursor
begin
for v_pricetable in cur_price(1)--for Variable name in Cursor name (parameter)
loop
dbms_output.put_line('Price:'||v_pricetable.price
||' Power consumption:' ||v_pricetable.minnum||'-'||v_pricetable.maxnum);
end loop;--End cycle
end;
2. Storage function
- Storage functions, also known as user-defined functions, can receive one or more parameters, return a result, and write logic using pl/sql.
- Stored functions are similar to methods in Java.
Storage function:
create [or replace] function Function name (Parameter name, parameter type, parameter name, parameter type...) return Result variable data type is --Variable declaration section begin --Logical part return result; [exception] end;
Case:
--Storage function
--Requirements: create a storage function according to the address ID Query address name.
create or replace function fun_address
(v_addressid number)--The parameter only specifies the type, not the length
return varchar2
is
v_name varchar2(30);
begin
select a.name into v_name from t_address a where a.id=v_addressid;
return v_name;
end ;
--Test storage function
select fun_address(3) from dual
Query the owner ID, owner name, owner address and owner address using the function we just created.
--Query owner ID，The owner name, owner address and owner address are implemented using the function we just created. select a.id owner id,a.name Name of owner,fun_address(a.addressid) Employer's address from t_owners a ;
3. Stored procedure
3.1 what is a stored procedure
Stored procedure is a named PL/SQL block, which is stored in the database. It is a kind of database object.
Applications can call stored procedures and execute the corresponding logic.
Both stored procedures and stored functions can encapsulate certain business logic and return results. The differences are as follows:
1. There is a return value in the storage function, which must be returned, and there is only one return value; The stored procedure has no return value. You can return multiple values through outgoing parameters.
2. Stored functions can be used directly in select statements, but stored procedures cannot. Most procedures are called by applications.
3. Stored functions generally encapsulate a query result, while stored procedures generally encapsulate a section of transaction code.
--Stored procedure syntax structure create [or replace] procedure Stored procedure name (Parameter name, parameter type,...)--The parameter only specifies the type, not the length is|as --Declare variable begin --logic [exception exception handling] end;
Three modes of process parameters:
-
IN incoming parameters (default)
-
OUT outgoing parameter, which is mainly used to return the running result of the program
-
IN OUT incoming and outgoing parameters
3.2 creating stored procedures without outgoing parameters
--stored procedure
--Create stored procedure without outgoing parameters: add owner information
--Create sequence
create sequence seq_owners
start with 11;
--Create stored procedure
create or replace procedure pro_owners_add
(v_name varchar2,
v_addressid number,
v_housenumber varchar2,
v_watermeter varchar2,
v_ownertypeid number)
is
begin
insert into t_owners
values(seq_owners.nextval,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_ownertypeid);
commit;
end;
--PL/SQL Call stored procedure method 1:
call pro_owners_add('careless',1,222,45353,1);
--PL/SQL Call stored procedure method two:
begin
pro_owners_add('Ma erha',1,22552,45766,1);
end;
3.3 creating stored procedures with outgoing parameters
--Demand: add owner information, and the outgoing parameters are those of the new owner ID
create or replace procedure pro_owners_add2
(v_name varchar2,
v_addressid number,
v_housenumber varchar2,
v_watermeter varchar2,
v_ownertypeid number,
v_id out number)
is
begin
select seq_owners.nextval into v_id from dual;
insert into t_owners
values(v_id,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_ownertypeid);
end;
--test
declare
v_id number(30);
begin
pro_owners_add2('Zhang ha',1,4365676,669,1,v_id);
dbms_output.put_line('Successfully inserted, ID: '||v_id);
end;
4. Trigger
4.1 what is a trigger
-
Database trigger is a pl/sql program related to tables.
-
Whenever a specific data operation statement (Insert,update,delete) is issued on the specified table, Oracle automatically executes the statement defined in the trigger.
Trigger classification:
Pre trigger (BEFORE)
Post trigger (AFTER)
Syntax for creating triggers:
create [or replace] trigger Trigger Name BEFORE | AFTER [UPDATE][[OR] INSERT][[OR] DELETE [OF Listing]] ON Table name [FOR EACH ROW][when condition] declare begin end;
FOR EACH ROW is used to mark that this trigger is a row level trigger, as well as a statement level trigger
4.2 pre trigger cases
--Pre trigger
--Demand: after the user enters the cumulative table quantity of this month, the usage quantity of this month will be calculated automatically.
create or replace trigger tri_account1
before
update of num1
on t_account
for each row
declare
begin
:new.usenum:=:new.num1-:new.num0;
end;
4.3 post trigger cases
--Post trigger
--Requirement: when the user modifies the data in the owner information table, record the values before and after modification
--Create log table
create table owners_log(
updatetime date,
owners_id number,
newname varchar2(30),
oldname varchar2(30)
);
--Create post trigger
create or replace trigger tri_owners
after
update of name
on t_owners
for each row
declare
begin
insert into owners_log values(sysdate,:old.id,:new.name,:old.name);
end;
--test
select * from owners_log;
update t_owners a set a.name='Zhang Feng' where a.id='17';
5. Comprehensive cases
5.1 prepare PL/SQL, use 12 tons of water, the owner type is 1, and calculate the step water fee.
--to write PL/SQL ，The tonnage of water consumption is 12 tons, the owner type is 1, and the step water fee is calculated.
declare
v_usenum number;
v_money number(10,2);
v_ownertypeid number;
cursor cur_price(ownertype varchar2)
is
select * from t_pricetable a where a.ownertypeid=ownertype order by a.minnum;
v_pricetable t_pricetable%rowtype;--Can be omitted
begin
v_usenum:=12;
v_ownertypeid:=1;
v_money:=0;
for v_pricetable in cur_price(v_ownertypeid)
loop
if v_pricetable.maxnum is null or v_usenum <=v_pricetable.maxnum then
v_money:=v_money+v_pricetable.price*(v_usenum-v_pricetable.minnum);
exit;
else
v_money:=v_money+v_pricetable.price*(v_pricetable.maxnum-v_pricetable.minnum);
end if;
end loop;
dbms_output.put_line('Total water charge amount:'||v_money);
end;
--to write PL/SQL ，The tonnage of water consumption is 12 tons, the owner type is 1, and the step water fee is calculated.
declare
v_usenum number;
v_money number(10,2);
v_ownertypeid number;
cursor cur_price(ownertype varchar2)
is
select * from t_pricetable a where a.ownertypeid=ownertype;
v_pricetable t_pricetable%rowtype;
begin
v_usenum:=12;
v_ownertypeid:=1;
v_money:=0;
open cur_price(v_ownertypeid);
loop
fetch cur_price into v_pricetable;
exit when cur_price%notfound;
if v_pricetable.maxnum is null or v_usenum <=v_pricetable.maxnum then
v_money:=v_money+v_pricetable.price*(v_usenum-v_pricetable.minnum);
else
v_money:=v_money+v_pricetable.price*(v_pricetable.maxnum-v_pricetable.minnum);
end if;
end loop;
close cur_price;
dbms_output.put_line('Total water charge amount:'||v_money);
end;
5.2 comprehensive case of storage function: create a function for calculating step water charge, and the parameters are owner type and tonnage.
--2.Comprehensive case of storage function: create a function for calculating step water charge, with parameters of owner type and tonnage.
create or replace function fn_watermoney
(v_ownertype number,
v_usenum number)
return number
is
cursor cur_price(v_ownertype number) is
select * from t_pricetable a where a.ownertypeid=v_ownertype order by a.minnum;
v_pricetable t_pricetable%rowtype;
v_money number(10,2);
begin
v_money:=0;
for v_pricetable in cur_price(v_ownertype)
loop
if v_pricetable.maxnum is null or v_usenum<=v_pricetable.maxnum then
v_money:=v_money+v_pricetable.price*(v_usenum-v_pricetable.minnum);
exit;
else
v_money:=v_money+v_pricetable.price*(v_pricetable.maxnum-v_pricetable.minnum);
end if;
end loop;
return v_money;
end;
--test
select fn_watermoney(1,12) from dual
5.3 trigger comprehensive case: when the user inputs the cumulative amount of this month, the step water charge will be calculated automatically.
--3.Trigger comprehensive case: when the user enters the cumulative amount of this month, the step water charge will be calculated automatically.
create or replace trigger tri_waterprice
before
update of num1
on t_account
for each row
declare
v_usenum2 number(10,2);--Tonnage
begin
:new.usenum:=:new.num1-:new.num0;
v_usenum2:=round(:new.usenum/1000,2);
:new.money:=fn_watermoney(:new.ownertype,v_usenum2);
end;
5.4 integrated case of stored procedure
Demand: when adding owner information, add a record in the account at the same time. The year and month are the year and month of the current date, the initial value (num0) is 0, and other field information (region) and t_ The owners table is consistent
--4.Demand: when adding the owner's information, it shall be in the accounting table at the same time( account)Add a record, year and month
--Is the month and year of the current date, initial value( num0)Is 0,Other field information (area) and t_owners Table consistent
create or replace procedure pro_owners_account
(v_name varchar2,
v_addressid number,
v_housenumber varchar2,
v_watermeter varchar2,
v_ownertypeid number,
v_owneruuid out number)
is
v_num0 number;
v_year char(4);--Attention type
v_month char(2);
v_areaid number;
begin
select seq_owners.nextval into v_owneruuid from dual;
select ad.areaid into v_areaid from t_address ad where ad.id=v_addressid;
v_num0:=0;
v_year:=to_char(sysdate,'yyyy');
v_month:=to_char(sysdate,'mm');
insert into t_owners values(v_owneruuid,v_name,v_addressid,v_housenumber,
v_watermeter,sysdate,v_ownertypeid) ;
insert into t_account (id,owneruuid,ownertype,areaid,year,month,num0)
values(seq_account.nextval,v_owneruuid,v_ownertypeid,v_areaid,v_year,v_month,v_num0);
commit;
end;
--test
declare
v_uuid number(10);
begin
pro_owners_account('Li Da',1,'52342','4556353',1,v_uuid);
end;