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

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

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

Basic grammar 2

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:

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

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:

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!!