What is PL/SQL? How to use it?

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

Tags: Database SQL Oracle less

Posted on Wed, 03 Jun 2020 13:02:44 -0400 by wkoneal