PL/SQL
1. What is PL/SQL?
PL/SQL (Procedure Language/SQL) is a procedural extension of Oracle to SQL language. It refers to the addition of process processing statements (such as branch / condition, loop, variable, type, etc.) in SQL command language, which makes SQL language have process processing ability. By combining the data manipulation ability of SQL with the data processing ability of process language, PLSQL is process oriented but simpler, more efficient, more flexible and more practical than process language.
Basic grammatical structure
[declare --Declare variables ] begin --Code logic [exception --exception handling ] end;
2. Variables
--Syntax for declaring variables: Variable name type (length); i varchar2(20); --Syntax of variable assignment Variable name: = variable value i: = 'Huang Huang'
For example:
--Introduction exercise 1;Printing'welcome!!' begin dbms_output.put_line('welcome!!!'); end; --Example 1;Defining variables i,And assign a value 'Hello',output --Variable type,Be sure to support the length of assignment declare i varchar2(20); begin i :='Hello'; dbms_output.put_line('The output is:' || i); end; --Multiple variables declare sname varchar2(20):='huangsir'; age int:=18; sex varchar2(20):='male'; begin dbms_output.put_line('full name:' ||sname || ',Age:' ||age || ',Gender:' ||sex); end;
How to perform the view?
Select into method assignment
Syntax structure: the into keyword can be used in the process
select column name 1, column name 2 into variable name 1, variable name 2 from table name where condition
give an example:
--Exercise 1;Print the name of the owner declare --Declare variables,For storage name Value of v_name varchar2(50); begin --Execute query statement,get name Value of,And assign variables select name into v_name from t_owners where id=11; --Print variables dbms_output.put_line(v_name); end;
3. Variable type% type
If the custom type size is set too small, there will be a number or value error: the character cache is too small
So you can use% type to reference the table type
Syntax:
--Grammar Variable name table name. Column name% type; --Examples v_name towners.name%type;
Case:
--Exercise 2;Print the name of the owner declare --Declare variables,For storage name Value of v_name t_owners.name%type; --use%type No need to customize data type,Get type directly from table data begin --Execute query statement,get name Value of,And assign variables select name into v_name from t_owners where id=11; --Print variables dbms_output.put_line(v_name); end;
practice:
--Exercise 3: Demand: calculate the water consumption amount of the current month for the user with No. 1 in the account table. --Required output: unit price, tonnage, amount, water consumption of last month, water consumption of this month declare v_price number; --Unit Price v_num0 number; --t_account.num0%type Accumulated water consumption of last month v_num1 number; --t_account.num1%type Accumulated water consumption of this month v_usenum number; --t_account.usenum%type Usage v_money number; --amount of money v_usenum2 number; --Tonnage begin v_price:=3.45; --Unit price assignment --Query data;Water consumption obtained,amount of money select num0,num1,usenum,money into v_num0,v_num1,v_usenum,v_money from t_account where id = 45; --Tons 19920 /1000 ->> 19.920 -->19.92 v_usenum2:=round(v_usenum/1000 ,2); v_money:=v_price*v_usenum2; --Printing dbms_output.put_line('Unit price'|| v_price ||'Tonnage'||v_usenum2|| 'amount of money'||v_money ||',Water consumption of last month'||v_num0 ||',Water consumption of this month'||v_num1); end;
%ROWTYPE record type
- Role: identify the row record type of a table
- Get all types of a row through% rowtype, which is equivalent to an object and holds a record
Declare variables:
--Grammar Variable table name% rowtype; --Examples v_account t_account%rowtype;
Assignment:
--Single column select column name, column name 2,... into variable. Column name, variable. Column name 2,... From table name; --All select * into variable from table name;
Example: Rewrite last exercise: use% rowtype
declare --Unit Price v_price number; --Declare line variables v_account t_account%rowtype; begin --Unit price assignment v_price :=3.45; --preservation-Number of lines select * into v_account from t_account where id=45; --Printing dbms_output.put_line('Unit Price'||v_price ||',amount of money'||v_account.money ||',Water consumption of last month' ||v_account.num0 ||',Water consumption of this month'||v_account.num1); end;
4. Abnormal
An error that occurs while running a program is called an exception
When an exception occurs, the statement will stop executing and control will be transferred to the exception handling part of the PL/SQL block
There are two types of exceptions:
- Predefined exception - implicitly raised when PL/SQL program violates Oracle rules or exceeds system limits
- User defined exception - the user can define an exception in the declaration part of the PL/SQL block, and the custom exception is explicitly raised through the RAISE statement
Predefined exception
Oracle predefined exceptions 21
Named system exception |
Causes |
ACCESS_INTO_NULL |
Object not defined |
CASE_NOT_FOUND |
WHEN CASE does not contain corresponding WHEN and ELSE is not set |
COLLECTION_IS_NULL |
Collection element not initialized |
CURSER_ALREADY_OPEN |
Cursor already open |
DUP_VAL_ON_INDEX |
Duplicate value on column corresponding to unique index |
INVALID_CURSOR |
Operation on illegal cursor |
INVALID_NUMBER |
Embedded SQL statements cannot convert characters to numbers |
NO_DATA_FOUND |
No rows returned using select into |
TOO_MANY_ROWS |
When executing select into, the result set has more than one row |
ZERO_DIVIDE |
Divide by 0 |
SUBSCRIPT_BEYOND_COUNT |
Element subscript exceeds the maximum value of nested table or VARRAY |
SUBSCRIPT_OUTSIDE_LIMIT |
Specify a negative subscript when using nested tables or VARRAY |
VALUE_ERROR |
When assigning, the variable length is not enough to hold the actual data |
LOGIN_DENIED |
PL/SQL application provided incorrect user name or password when connecting to oracle Database |
NOT_LOGGED_ON |
PL/SQL application accessing data without connecting to Oracle Database |
PROGRAM_ERROR |
PL/SQL internal problem, may need to reinstall Data Dictionary & pl./SQL system package |
ROWTYPE_MISMATCH |
The return type of the host cursor variable and PL/SQL cursor variable is not compatible |
SELF_IS_NULL |
Call object method on null object when using object type |
STORAGE_ERROR |
Out of memory while running PL/SQL |
SYS_INVALID_ID |
Invalid ROWID string |
TIMEOUT_ON_RESOURCE |
Oracle timed out waiting for resources |
Syntax:
--Grammar declare begin --Normal code --Abnormal block exception when exception type then handle when exception type then handle .... end; --Common exception types: no_date_found, no data exception, query result is null too_many_rows, too many rows, query result greater than 1
Exercise 4: query the account table, all the information with id 1, and print the part - exception - no data
-- exception handling -- Exercise 4: query the account table, id For 1 all information and print part declare v_account t_account%rowtype; begin --query select * into v_account from t_account where id = 999; --Printing dbms_output.put_line('Water consumption of last month' || v_account.num0 || 'Water consumption of this month' || v_account.num1); -- exception handling exception when no_data_found then dbms_output.put_line('No data found'); when too_many_rows then dbms_output.put_line('More than 1 query result'); end;
Exercise 4: query the account table, all the information with id 1, and print the part - exception - query multiple pieces of data
declare v_account t_account%rowtype; begin --query select * into v_account from t_account ; --less than where id=45 ,There are multiple pieces of data --Printing dbms_output.put_line('Water consumption of last month' || v_account.num0 || 'Water consumption of this month' || v_account.num1); -- exception handling exception when no_data_found then dbms_output.put_line('No data found'); when too_many_rows then dbms_output.put_line('More than 1 query result'); end;
5. Condition judgment
Basic grammar 1
if condition then
Business logic
end if;
Basic grammar 2
if condition then
Business logic
else
Business logic
end if;
Basic grammar 3
if condition then Business logic elsif condition then Business logic else Business logic end if;
Example: demand: set three levels of water fee: below 5 tons, 2.45 yuan / ton, 5 tons to 10 tons, 3.45 yuan / ton, and over 10 tons, 4.45 yuan / ton. Calculate the step water fee according to the amount of water fee
declare --Define 3 water prices v_price1 number; v_price2 number; v_price3 number; --Query account records v_account t_account%rowtype; --Usage(Tonnage) v_usenum2 number; --amount of money v_money number; begin --Determine water price v_price1:=2.45; v_price2:=3.45; v_price3:=4.45; --Query record select * into v_account from t_account where id=45; --Calculate usage v_usenum2:=round(v_account.usenum/1000,2); --According to usage,Calculation of step water charge if v_usenum2<=5 then --Less than 5 tons , Usage*2.45 v_money:=v_usenum2*2.45; elsif v_usenum2 <=10 then --Less than 10 tons 5* 2.45 +(Usage-5) *3.45 v_money:=5 *2.45 +(v_usenum2-5)*3.45; else --More than 10 tons 5*2.45 +5*3.45 +Usage-10) *4.45 v_money:=5 *2.45+5*3.45+(v_usenum2-10)*4.45; end if; --Print water charge dbms_output.put_line(v_money); end;
6. Circulation
(1) Unconditional cycle
Syntax:
--grammar loop -- code -- End cycle exit; end loop;
Exercise: output 100 numbers from 1
declare v_i int :=1; begin loop dbms_output.put_line(v_i); --accumulation v_i:=v_i+1; -- sign out if v_i > 100 then exit; end if; end loop; end;
(2) Conditional loop - while
Syntax:
--Grammatical structure
while condition
loop
...
end loop;
Example: find 1-100 sum and print
declare --Counter v_i int :=1; --Summation variable v_sum int :=0; begin while v_i <=100 loop --Summation v_sum :=v_sum+v_i; --accumulation v_i:=v_i +1; end loop; --Printing dbms_output.put_line(v_sum); end;
(3)for loop
Syntax:
for variable in Starting value..Termination value loop end loop;
give an example:
Find the sum of all even numbers 1-100 and print
declare --Count variable v_i int; --Summation variable v_sum int :=0; begin for v_i in 1..100 loop --mod()For redundancy if mod(v_i,2)= 0 then v_sum :=v_sum+v_i; end if; end loop; dbms_output.put_line(v_sum); end;
Before practice, you need to know:
declare begin --One line output dbms_output.put('123'); --Line feed output dbms_output.put_line('abc'); end;
Exercise: 99 multiplication table: step by step implementation
--1.Common cycle declare vi int; begin --loop for vi in 1..9 loop dbms_output.put_line(vi); end loop; end; --2.Nested loop declare vi int; vj int; begin --Nested loop for vi in 1..9 loop --Internal circulation for vj in 1..9 loop dbms_output.put_line(vi || vj); end loop; end loop; end; ---3.total declare v_i int; v_j int; begin --Nested loop for v_i in 1..9 loop --Internal circulation for v_j in 1..v_i loop dbms_output.put(v_j || '*' || v_i ||'=' ||(v_i*v_j) ); dbms_output.put(' '); end loop; dbms_output.put_line(''); end loop; end;
7. Cursor
(1) What is a cursor?
Cursor is a data buffer opened by the system for users to store the execution results of SQL statements. We can understand cursors as result sets in PL/SQL.
- Cursors are used to store query results in the cache area, through which data can be obtained in turn
grammar
--To declare a cursor in the declaration area, the syntax is as follows:
cursor name is SQL statement;
declare
-- declare cursor
cursor name is query statement;
begin
open cursor name;
loop;
-- name the cursor to the current row, so as to obtain the data of the current row
fetch cursor name into variable;
-- use current row data
-- end information
exit when cursor name% notfound;
end loop;
close cursor name;
end;
Case: demand: print price list with owner type 1
Implementation mode 1:
--Mode 1;Basic operation of cursor declare --declare cursor cursor cur_pricetable is select * from t_pricetable where ownertypeid=1; --Inside the statement,Can store a record v_pricetable t_pricetable%rowtype; begin --open open cur_pricetable; loop --Cursor points to row data,Print price(Name the data in the cursor,Easy access to row data) fetch cur_pricetable into v_pricetable; --no data,sign out exit when cur_pricetable%notfound; --Printing dbms_output.put_line(v_pricetable.price); end loop; --close close cur_pricetable; end;
Implementation 2.for loop (Simplified)
declare --declare cursor cursor cp is select * from t_pricetable where ownertypeid=1; --variable vp t_pricetable%rowtype; begin --for ergodic for vp in cp loop dbms_output.put_line(vp.price); end loop; end;
Cursor with parameters
Syntax:
-- curso r name (parameter name type) is SQL statement.. where id = parameter name;
-- in for VP in CP (parameter to be passed);
Case:
declare --declare cursor cursor cp(v_ownertypeid number) is select * from t_pricetable where ownertypeid=v_ownertypeid; --variable vp t_pricetable%rowtype; begin --for ergodic for vp in cp(3) loop dbms_output.put_line(vp.price); end loop; end;
After watching, Congratulations, and know a little bit!!!
The more you know, the more you don't know
~Thank you for reading. Your support is the biggest driving force for my study! Come on, strangers work together and encourage together!!