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.