stored procedure :
In some large database systems . A group of SQL Statements set , Stored in a database , A compilation , Permanent validity
This is a SQL The sentence is from 0-50, Every time 2, The code is as follows :
-- This is a SQL The sentence is from 0-50, Every time 2
--/
CREATE OR REPLACE PROCEDURE x1
IS
i INT :=0;
BEGIN
LOOP
dbms_output.put_line(i);
i :=i+2;
EXIT WHEN i>50;
END LOOP;
END;
/
CALL x1();
The figures are as follows :
according to x2( value ) Delete from the table deptno by 50 The line of , The code is as follows :
-- according to x2( value ) Delete from the table deptno by 50 The line of
--/
CREATE OR REPLACE PROCEDURE x2(i INT)
IS
NAME VARCHAR2(20);
BEGIN
DELETE FROM SCOTT.DEPT WHERE DEPTNO=i;
END;
/
CALL x2(50);
The figures are as follows :
according to deptno To find the corresponding name , The code is as follows :
-- according to deptno To find the corresponding name
--/
CREATE OR REPLACE PROCEDURE x3(i INT)
IS
NAME VARCHAR2(20);
BEGIN
SELECT dname INTO NAME FROM DEPT WHERE deptno=i;
dbms_output.put_line(NAME);
END;
/
CALL x3(20);
Storage function :
We use... When we query AVG,SUM,COUNT They're all storage functions , Is a convenient function for us to count or operate on data types
(nvl : see comm If there is a value , If it is null, It becomes 0)
Calculate the sum of salary and bonus , The code is as follows :
--a,b They correspond to each other sal,comm.
--/
CREATE OR REPLACE FUNCTION mySUM(a NUMBER,b NUMBER) RETURN NUMBER
IS
i NUMBER :=0;
BEGIN
i :=nvl(a,0)+nvl(b,0);
RETURN i;
END;
/
SELECT mySUM(sal,comm) FROM EMP;
The figures are as follows :
According to the transmission DEPTNO To find the corresponding department number , name , And position , The code is as follows :
-- plsql Create a composite function type
--/
CREATE OR REPLACE PROCEDURE x4(s NUMBER)
IS
TYPE DINFO IS RECORD(
deptno NUMBER,
dname VARCHAR2(20),
loc VARCHAR2(20)
);
d DINFO;
BEGIN
SELECT deptno,dname,loc INTO d.deptno,d.dname,d.loc FROM DEPT WHERE deptno=s;
dbms_output.put_line(' Department number '||d.deptno||' Department name '||d.dname||' Department position '||d.loc);
END;
/
CALL x4(10)
The figures are as follows :
The cursor
It's a SQL A workspace of memory , Defined by the system or user in the form of variables , There are two main types of using cursors
( One ). Explicit cursors
In the database DML operation , And one-way DQL operation , An explicit cursor is automatically created
SQL%ROWCOUNT You can get SQL The number of data rows affected
SQL%FOUND Used to determine SQL Does it affect , If there is a return true, If there is no return false
SQL%NOTFOUND Used to determine SQL Does it affect , If there is a return false, If there is no return true
According to the value passed in EMPNO Contrast , Then delete more than the incoming s The value of the big data , And then use "SQL%ROWCOUNT" To calculate the functions affected , The code is as follows :
--/
CREATE OR REPLACE PROCEDURE x5(s NUMBER)
IS
BEGIN
DELETE FROM EMP WHERE EMPNO>s;
IF SQL%FOUND THEN
dbms_output.put_line(' Successfully deleted the employee '|| SQL%ROWCOUNT||' individual ');
ELSE
dbms_output.put_line(' No employees have been deleted '||SQL%ROWCOUNT||' individual ');
END IF;
END;
/
INSERT INTO EMP(EMPNO) VALUES(8908);
CALL x5(8887);
The figures are as follows :
( Two ). Implicit cursors
You need to create it manually , Specifically for SELECT sentence
Find out the employee's name , The code is as follows :
--/
DECLARE
CURSOR YB IS SELECT * FROM EMP; -- to SELECT * FROM EMP Create an implicit cursor
E EMP%ROWTYPE; -- Extract table structure from cursor , And then create an object that is the same as the table structure
BEGIN
OPEN YB; -- Open implicit cursor
FETCH YB INTO E; -- Assign the result of the query in the cursor to E
WHILE YB%FOUND LOOP
dbms_output.put_line(' The employee's name is :'||E.ENAME);
FETCH YB INTO E; -- Once again, assign the value in the cursor to E
END LOOP; -- close loop
CLOSE YB; -- Close implicit cursor ( If you don't close it, it will always take up memory )
END;
/
The figures are as follows :
two_many_rows We got multiple records
no_data_found No records found
OTHERS All the other anomalies , You need to combine other exceptions to use
-- Query according to the department number passed in ,( This is a handwriting anomaly )
--/
CREATE OR REPLACE PROCEDURE x6(i NUMBER)
IS
n VARCHAR(20);
BEGIN
SELECT ename INTO n FROM EMP WHERE deptno=i;
EXCEPTION
WHEN too_many_rows THEN
dbms_output.put_line(' More than one record was found , Please amend SQL Or parameters ');
WHEN no_data_found THEN
dbms_output.put_line(' No records found ');
WHEN OTHERS THEN
dbms_output.put_line(' There's a wonderful anomaly ');
END;
/
CALL x6(20);
The figures are as follows :
trigger
Line level triggers ( Yours SQL How many lines are affected , How many times has the trigger been executed )
Statement triggers ( Yours SQL No matter how many times it's affected , The trigger is executed only once )
Line level triggers , The example code is as follows :
Statement triggers , The example code is as follows :
-- Statement triggers
CREATE TABLE M(
ID INT
)
INSERT INTO M VALUES(S1.nextval);
SELECT * FROM M
--/
CREATE OR REPLACE TRIGGER MM
AFTER DELETE ON M
BEGIN
dbms_output.put_line(' Liang Jinfeng touches the wire , Electric shock again ');
END;
/
DELETE FROM M WHERE M.ID>6
The figures are as follows :
