database programming

PL-SQL programming

1. Features of PL/SQL

PL(Procedure Language)/SQL) is a procedural programming language introduced by Oracle in the database.
PL/SQL is an extension to SQL: it has features designed for program development;
Variables can be used in PL/SQL for process control to realize more complex business logic;
PL/SQL embedded in the Oracle server can be regarded as an engine in the Oracle server, so it has high execution efficiency.

2.PL/SQL program structure

/
declare
   n number;
   result number;
begin
   n:=0; result:=0;
   while n<=100 loop
     result:=result+n;
     n:=n+1;
   end loop;
   dbms_output.put_line('turn out'||result);
end;
/
set serveroutput on;//system switch

save

/
create or replace procedure sp_calcsum
as
  n1 number; result number;
begin
   n1:=0; result:=0;
   while n1<=100 loop
     result:=result+n1;
     n1:=n1+1;
   end loop;
   dbms_output.put_line('turn out'||result);
end;
/

3. Special types of variables in PL/SQL

[field name]%TYPE type
Declare a variable of the same type as the specified column of the specified table to store the value of a field.
[table name] %ROWTYPE type
Declare a variable with the same row structure as the specified table to store a record.
CURSOR type
Declare a variable of table object type to store a query result set.

Notice:
1. In a PL/SQL program, when the data returned by the SELECT statement is one row, the SELECT statement always cooperates with INTO, and INTO is followed by the variable used to receive the query result, in the following form:

SELECT column name 1, column name 2...  

INTO variable 1, variable 2... 

 FROM Table Name WHERE condition;

2. When the program wants to receive multiple rows of results returned, it can use the cursor variable to store them.

✏Example 1: Query the student ID and name of the student whose ID is "001101".

set serveroutput on;
DECLARE
--explain this stuno table type and xs.sno same type
stuno xs.sno%type;
stuname xs.sname%type;
BEGIN
--Bundle sno,sname put in stuno,stuname inside
select sno,sname into stuno,stuname from  xs where sno='001101';
DBMS_OUTPUT.put_line(stuno||stuname);
END;
/

✏Example 2: Query the information of the student whose student ID is "001101".

set serveroutput on;
DECLARE
--explain this stu table type and xs The same type, can save a row of information
	Stu xs%rowtype;
BEGIN
--return a row
	select * into stu from  xs where sno='001101';
--stu.sno stu.snme quote	DBMS_OUTPUT.put_line(stu.sno||stu.sname);
END;
/

cursor

Cursor: It is used to query the database and obtain the pointer of the record set (result set), which allows developers to access one or more rows of result sets at a time and operate on each result set.
The cursor is a memory work area of SQL, which is defined by the system or the user in the form of variables. The function of the cursor is to temporarily store the data blocks extracted from the database. In some cases, it is necessary to transfer the data from the table stored on the disk to the computer memory for processing, and finally display the processing results or write them back to the database. Only in this way can the speed of data processing be improved, otherwise frequent disk data exchange will reduce efficiency.

1. The type of cursor

CURSOR is a method in PL/SQL to implement object-oriented operations on tables. There are two types:

Explicit cursor: When the query returns more than one row, the user cannot use the select into statement at this time, and a cursor is needed to process the result set.

Implicit cursor: When executing a SQL statement, the ORACLE system will automatically generate an implicit cursor, which is mainly used to process the execution results of data manipulation statements (INSERT and DELETE statements). When using the properties of an implicit cursor, add Default name for implicit cursors on SQL.

2. Definition of cursor

Cursor definition format: CURSOR cursor name is Select statement;

Explicit cursors can be used in two ways:

cursor variable loop;
Cursor for loop.

Cursor variable loop format:
  open cursor; 
  loop  
     fetch  cursor  into cursor variable; 
     -- processing statement; 
     exit   when cursor%notfound; 
  end loop; 
  close cursor;

FOR Cycle format:

  for cursor variable in cursor loop  
     -- processing statement; 
  end loop;

cursor variable

(1) %FOUND: Boolean, if the SQL statement affects at least one row, then %FOUND is equal to true, otherwise it is equal to false.
(2) %NOTFOUND: Boolean, opposite to %FOUND.
(3) %ROWCOUNT: Integer type, returns the number of rows affected by the SQL statement.
(4) %ISOPEN: Boolean, judge whether the cursor is opened, if open %ISOPEN is equal to true, otherwise it is equal to false.

🚩✏Example 1: Query the student ID and name of all students. (Loop with cursor variable)

DECLARE
	--kind stu type is xs type
	stu xs%rowtype;
	--(select * from xs)returns a collection (multiple values)
	--The returned object uses a cursor variable cur_stu storage 
	cursor cur_stu is select * from xs;
BEGIN
	--open this cursor variable cur_stu
	open cur_stu;

----use loop-----
	loop
	--each extraction cur_stu line of the , put in stu middle
   		fetch cur_stu into stu;
   	--when cur_stu Points to the end of the table and exits the loop
   		exit when cur_stu%notfound;
   	--cur_stu Did not point to the end of the table, output this row stu.sno，stu.sname	
   		DBMS_OUTPUT.put_line(stu.sno||stu.sname);
	end loop;
	--close cursor
	close cur_stu;
END;

✏Example 2: Query the student ID and name of all students. (using for loop)

DECLARE
	--define a cursor cur_stu
	cursor cur_stu is select * from xs;
BEGIN
	--for cycle, each time cur_stu line of
	for stucur in cur_stu loop
		--output
    	DBMS_OUTPUT.put_line(stucur.sno||stucur.sname);
	end loop;
END;

----Omit the cursor definition, use directly(cur_stu is select * from xs)as a cursor
BEGIN
	for stucur in select * from xs loop
    	DBMS_OUTPUT.put_line(stucur.sno||stucur.sname);
	end loop;
END;

exception handling

When writing PL/SQL programs, it is inevitable that some errors will occur. For these errors, ORACLE usually uses exceptions to handle them. The exception handling code is usually placed in the EXCEPTION code block of the PL/SQL block. According to the mechanism of exception generation and According to the principle, the ORACLE system exceptions can be divided into two categories:

1. Predefined exceptions:
The ORACLE system itself provides for the user, in order to check the general cause of user code failure.
2. Custom exception:
User-defined exceptions.

✏Example 1: Query the information of the student whose student ID is "001101".

DECLARE
	stuno xs.sno%type;
	stuname xs.sname%type;
BEGIN
	--This sentence is abnormal
	select sno,sname into stuno,stuname from  xs ;
	DBMS_OUTPUT.put_line(stuno||stuname);
	
EXCEPTION
    WHEN no_data_found then
      dbms_output.put_line('data not found');
    --
    WHEN too_many_rows then
      dbms_output.put_line('result set with more than one row');
END;

✏Example 2: The GRADE column in the CJ table cannot insert NULL values.

DECLARE
	--Define new exception types
	null_exp exception;
	stucj cj%rowtype;
BEGIN
	stucj.sno:='001241'; stucj.cno:='206';
	--did not give stucj.grade assignment
	insert into cj values(stucj.sno,stucj.cno,stucj.grade);
	-- raise null_exp  to throw null_exp abnormal
	if stucj.grade is null then   raise null_exp;   end if;
EXCEPTION
    WHEN null_exp then
      dbms_output.put_line('Grade cannot be empty');
      --rollback
      rollback;
    WHEN others then
      dbms_output.put_line('other abnormalities');
END;

stored procedure

Stored procedures and functions are also PL/SQL blocks, which are PL/SQL blocks stored in the database. However, stored procedures and functions are different from the PL/SQL programs that have been introduced. We usually refer to PL/SQL programs as unnamed blocks, while stored procedures and functions are stored in the database in a named manner. Its advantages are as follows:

The stored procedure runs on the server side, and the execution speed is fast;
To ensure the security of the database, only authorized users or creators can execute stored procedures;
simplified input;
Can be executed repeatedly.

function

1. Create a function

create or replace function <Function name>(parameter datatype, ...)
as
  [variable declaration;]
begin
  execute statement; 
 [exception  error handling section]  
end;
/

✏Example 1: Calculate the total credits of the student based on the student number

Analysis: Look at which courses the student has taken (schedule), find the grades of each subject (results table), credits can only be obtained if the grade is greater than 60

(totalcredit should not be placed in the field of the table, it is redundant, because it can be calculated from other fields)

plsql:

--Function name count_Credit，Formal parameter stuno char(student number), return value number(Total credits)
create or replace function count_Credit(stuno char)return number
as
--Variable declaration:
cred number:=0;--Used to store the credits taken in each cycle
total_cred number:=0;--Store accumulated credits
begin
  --Define a cursor to fetch related records in the score table
  ----where sno=stuno Use parameters to find the course number (grade table: student number, course, grade)
  for stucur in (select sno,cno,grade from cj where sno=stuno and grade>=60)loop
  --Take this credit out and put it in a variable cred Medium (schedule: course number, credits)
  select credit into cred from kc where cno=stucur.cno;
  --accumulate
  total_cred:=total_cred+cred;
end loop
  return total_cred;
end;

After compiling, call:

select sno,count_credit(sno) from xs;

Change: (only one record can be changed at a time, low efficiency)

update xs set totalcredit('001101') where sno='001101';

mysql: (go in from the object)

stored procedure

The difference between stored procedures and functions:

Stored procedures can have no return value, but functions must have a return value
The parameters of the stored procedure can be input and output, and the function only has input parameters

in( input)|out(output)|in out( input\output)
The calling method is different, the function can be called directly

Create stored procedure

create or replace procedure <process name>(parameter [in|out|in out] type of data,…)
as
  [variable declaration;]
begin
  execute statement; 
 [exception  error handling section]  
end;

✏Example 1: Create a stored procedure to update the total credits of all students using the count_Credit function

CREATE OR REPLACE PROCEDURE PRO_COUNT_CREDIT AS 
BEGIN
  for xs_cur in(select sno from xs)loop
  update xs set totalcredit=count_credit(xs_cur.sno)where sno=xs_cur.sno;
  end loop; 
END PRO_COUNT_CREDIT;

After compiling, call to execute storage:

execute pro_count_credit;

✏Example 2: Calculate the student's credit score based on the student number
credit score = ∑ credit ∗ score Total credits Credit score=\sum{\frac{credit*grade}{total credits}} Credit score=∑total credits∗grade

----stored procedure name REPLACE PROCEDURE
--incoming parameters stuno(student number) outgoing parameter creditj(credit score)
CREATE OR REPLACE PROCEDURE PRO_COUNT_CREDITJ (STUNO IN VARCHAR2 
, CREDITJ OUT NUMBER ) 
AS
  cred number:=0;--credit
  toltal_cred number:=0; --Multiply credits and grades
BEGIN
  --cursor cjcur
  for cjcur in (select sno,cno,grade from cj where stuno=sno and grade>=60)loop
  --Earn credits from class schedule
  select credit into cred from kc where cno=cjcur.cno;
  --credit*score: cred*cjcur.grade total credits*score: toltal_cred Accumulated each time
  toltal_cred:=toltal_cred+cred*cjcur.grade;
  end loop;
  --If the person's total credits are less than zero, the calculation will be wrong
  if count_credit(stuno)>0 then
  creditj:=toltal_cred/count_credit(stuno);
  end if;
END PRO_COUNT_CREDITJ;

After compiling, call

execute PRO_COUNT_CREDITJ;
declare
--statement creditj Used to store output credits
creditj number;
begin
--begin call inside
PRO_COUNT_CREDITJ('001101',creditj);
--output
dbms_output.put_line(creditj);
end;

execute stored procedure
method 1:

EXECUTE schema name.stored procedure name[(parameter 1,…)];
Method 2:

BEGIN schema name. stored procedure name [(parameter 1,...)]; END;

trigger

Trigger (Trigger) is a special type of stored procedure.
Triggers are different from stored procedures. Triggers are mainly triggered by events and automatically invoked by the system, while stored procedures are directly invoked through stored procedure names.
Triggers are usually closely related to the basic table and can be regarded as a part of the basic table definition. Triggers are defined on a specific table, also known as the trigger table. When there is an operation for the trigger table, for example, when inserting (Insert), deleting (Delete), modifying (Update) data in the table, the trigger is automatically triggered and executed.

Trigger type (this course only introduces DML triggers)

2. Trigger events

Create DML triggers

BEFORE|AFTER|INSTEAD OF (the trigger is defined before/after/in the view before/after the event)

on table name: on which table to add, delete, modify and query

[FOR EACH ROW]: every row is changed, the trigger must be run

  CREATE [OR REPLACE] TRIGGER trigger name  
  {BEFORE|AFTER|INSTEAD OF} trigger event 1 [OR trigger event 2...]       
  ON Table Name   
  WHEN Triggering conditions   
  [FOR EACH ROW]   
  DECLARE      
    declaration part   
  BEGIN      
    main part   
  END [trigger name];

Notice:

Trigger events: insert (Insert), delete (Delete), modify (Update);
Trigger timing: before the event (before), after the event (after);
.Trigger level: table level (default), row level (for each row) for each row
Trigger table: new record table (new) old record table (old):

Insert: new record → (:new);

delete: original record → (:old);

Modification: new record → (:new), original record → (:old)
The predicates in the trigger: INSERTING, DELETING and UPDATING, they are actually 3 internal logic variables in the trigger, which are used to judge the event type.

✏Example 1: Create a table cj_log to save the operation log in the STUDENT scheme, which is used to record the addition, deletion and modification of the grade table.

--log table
create table cj_log(
   operate_type varchar2(20),--Operation type (addition, deletion, modification)
   operate_date date,--The time the operation was executed
   operate_user varchar2(20)--user performing the action
);

--create trigger tri_cj
----on trigger event DELETE OR INSERT OR UPDATE trigger after
----ON cj Operate on the score sheet
CREATE OR REPLACE TRIGGER TRI_CJ AFTER DELETE OR INSERT OR UPDATE ON cj
  for each row--each line
declare
  operate_type varchar2(20);
BEGIN
  --operate_type:  Operation type declaration variable if statement get
  --operate_date:  The operating time is the current time sysdate
  --operate_user:  The operating user is the user obtained by the system variable ora_login_user
  if deleting then operate_type:='delete operation';
  elsif updating then operate_type:='modify operation'; 
  elsif inserting then operate_type:='insert operation';
  end if;
  insert into cj_log values(operate_type,sysdate,ora_login_user);
END;

After compiling, call

--show some two tables look at
select * from cj;
select * from cj_log;
--delete, modify, insert
delete from cj where sno='001101';
update cj set grade=80 where sno='001102';
insert into cj valuse('001101','101',90);

✏Example 2: Create a delete trigger to delete the xs table, delete a student in the xs table, then delete all records corresponding to the student number in the cj table.

Premise: The grade table is defined as cascading according to the student table

It is equivalent to cascade delete operation on the student table, delete the grade table

The trigger is called before delete on xs, because the information on the grade table must be deleted before the information on the student table can be deleted.

create or replace trigger tri_del_cj before delete on xs
	for each row
begin
    --The record to be deleted is temporarily stored in old in the table
 	delete from  cj where sno=old.sno;
end;

transfer:

delete from xs where sno='001241';

✏Exercise: According to the book borrowing relationship, there are book table, student table, and borrowing table. When a new record is inserted in the borrowing table (that is, a book is borrowed in the library table), change the status of the book in the book table to "borrowing Out"; when updating the return date in the borrowing table (that is, the book is returned), change the status of the book in the book table to "in library", using a trigger to achieve.

Analysis: operate in the lendbook table to trigger an action - modify the state in the book table

First create three tables and insert the statement:

----------------book list-------------------------
create table Book(
    bookid number(10) primary key,
    bookname varchar2(50),
    states varchar2(20)
);
--status has'in library, lent, lost'
insert into book values(1112221111,'java programming','In the museum');
insert into book values(1112221112,'vb programming','In the museum');
-------------student form---------------------------------
create table Student(
    stuid number(10) primary key;
    stuname varchar2(50)
);
insert into student values(169074264,'Liu Han');
insert into student values(169074108,'Li Mengmeng');
-------------borrowing form--------------------------
create table booklend(
lendid number(10) primary key,
 stuid number(10) not null,
 bookid number(10) not null,
 lenddate date,
 returndate date
);
-------------Fill out the borrowing form in order lendid-----------------
create sequence lendid_seq
start with 1 --start from 1
increment by 1 --increase by 1 each time
maxvalue 9999999999  --maximum
cycle; --Loop (after the maximum value is used up, start from 1)

Trigger for insert operation - change book.state to 'check out'

create or replace trigger lend_trig after insert on booklend 
	for each row
begin
    update book set states='Lend' where bookid=:new.bookid;   
end;

Trigger for update operation - change book.state to 'in library'

create or replace trigger return_tring before update on booklend
	for each row
begin
     update book set states='In the museum' where bookid=:old.bookid;--use new can also
end;

Debug after compilation

insert into booklend values(lendid_seq.nextval,'169074264','1112221111',sysdate,null);

update booklend set returndate=sysdate where stuid='169074264' and bookid='1112221111';

pragma autonomous_transaction

Compiling autonomous transactions, sub-transactions will not affect the main transaction, and sub-transactions will not affect the main transaction; the main transaction and sub-transactions are independent of each other and do not affect each other;

If there is uncommitted data in the main transaction, it cannot be queried in the sub-transaction; at the same time, the submission of the sub-transaction does not affect the main transaction, and the transaction still needs to be submitted;

Features of autonomous affairs

First, this program does not depend on the original Main program. For example, if there is uncommitted data in the Main program, it cannot be found in the autonomous transaction.

Second, in an autonomous transaction, commit or rollback will only submit or roll back the DML in the current autonomous transaction, and will not affect the DML in the Main program.

Tags: Database SQL Oracle

Posted by jobs on Sun, 26 Mar 2023 03:31:08 +0530

Programs Team

database programming

PL-SQL programming

cursor

exception handling

stored procedure

function

stored procedure

trigger

Hot Tags