[Oracle Study Notes], oracle Study Notes
The content mainly includes:
(1) three cycles and their simplification
(2) Use of cursors
(3) Exception Handling
(4) Stored Procedure
(5) storage functions
(6) triggers
(7) Other pl/SQL operations
--------------- Loop definition variable --------------------
Declare
Cursor c1 is select * from emp;
#
Rec emp % rowtype;
#
I numeber: = 1;
#
V_count number;
Begin
#
Select count (*) into v_count from emp;
# (1)
Open c1;
Loop
Fetch c1 into rec. EMPNO, rec. JOB;
Insert into emp1 (EMPNO, JOB) values (rec. EMPNO, rec. JOB)
I: = I + 1;
Exit when I> v_count;
End loop;
Close c1;
End;
--------------- Loop --------------------
Declare
Cursor c1 is select * from emp;
#
Rec emp % rowtype;
Begin
# (1)
Open c1;
Loop
Fetch c1 into rec. EMPNO, rec. JOB;
Exit when c1 % notfound;
Insert into emp1 (EMPNO, JOB) values (rec. EMPNO, rec. JOB)
End loop;
Close c1;
End;
--------------- Simplified loop --------------------
Declare
Cursor c1 is select * from emp;
#
Rec c1 % rowtype;
Begin
# (1)
Open c1;
Loop
Fetch c1 into rec;
Exit when c1 % notfound;
Insert into emp1 values rec
End loop;
Close c1;
End;
--------------- While LOOP --------------------
Declare
Cursor c1 is select * from emp;
#
Rec emp % rowtype;
Begin
Open c1;
Fetch c1 into rec. EMPNO, rec. JOB;
While c1 % found loop
Insert into emp1 (EMPNO, JOB) values (rec. EMPNO, rec. JOB)
Fetch c1 into rec. EMPNO, rec. JOB;
End loop;
Close c1;
End;
--------------- For loop, which is the most concise and not easy to generate an endless loop. Close it by yourself --------------------
Declare
# Record set
Cursor c1 is select * from emp;
# One record
Rec emp % rowtype;
Begin
# For default open cursor
For rec in c1 loop
Insert into emp1 (EMPNO, JOB) values (rec. EMPNO, rec. JOB)
End loop;
End;
--------------- For loop, simplified --------------------
Declare
# Record set
Cursor c1 is select * from emp;
Begin
# For default open cursor
# For implicitly declares rec, so you do not need to declare
For rec in c1 loop
Insert into emp1 (EMPNO, JOB) values (rec. EMPNO, rec. JOB)
End loop;
End;
--------------- For loop, and then simplified --------------------
Declare
Begin
# For default open cursor
# For implicitly declares rec, so you do not need to declare
For rec in (select * from emp) loop
Insert into emp1 values rec
End loop;
End;
--------------- The cursor with parameters --------------------
Declare
Cursor c1 (p_deptno number) is select * from emp;
#
Rec emp % rowtype;
Begin
# (1)
Open c1 (10 );
Loop
Fetch c1 into rec. EMPNO, rec. JOB;
Exit when c1 % notfound;
Insert into emp1 (EMPNO, JOB) values (rec. EMPNO, rec. JOB)
End loop;
Close c1;
End;
--------------- Print --------------------
Dbms_output.put_line (v_val );
--------------- Exception --------------------
Oracle pre-defined exceptions
Oracle Error
User-defined exceptions
--------------- Use exceptions ----------------
Define exceptions
Capture exceptions
Handling exceptions
EXCEPTION
WHEN TOO_MANY_ROWS THEN
NULL;
WHEN NO_DATA_FOUND THEN
NULL;
END;
--------------- Add a primary key ----------------
Alter table
Add constraint EMP_PK primary key;
--------------- Custom exception --------------
DECLARE
MY_EXCEPTION EXCEPTION
PRAGMA EXCEPTION_INIT (MY_EXCEPTION,-00001 );
BEGIN
Insert into department () values ();
EXCEPTION
WHEN MY_EXCEPTION THEN
# DBMS_OUTPUT.PUT_LINE ('error MESSAGES ');
RAISE_APPLICATION_ERROR (-1111111, 'query ');
END;
--------------- Define the variable --------------
V_1 data: = sysdata
--------------- Capture exceptions --------------
Raise el;
DECLARE
V_ddrq data: = sysdata;
V_fhrq data: = sysdata-1;
MY_EXCEPTION EXCEPTION
BEGIN
Insert into item (id, ddrq, fhrq) values (v_ddrq, v_fhrq );
IF v_ddrq> v_fhrq THEN
RAISE MY_EXCEPTION;
End if;
EXCEPTION
WHEN MY_EXCEPTION THEN
# DBMS_OUTPUT.PUT_LINE ('error MESSAGES ');
ROLLBACK;
RAISE_APPLICATION_ERROR (-1111111, 'query ');
END;
--------------- Procedure --------------
CREATE or replace PROCEDURE get_sal (p_empno number)
Is
V_sal emp. sal % type;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = p_empno;
DBMS_OUTPUT.PUTLINE (v_sal );
EDN;
Sqlplus call (each language has its own call method)
Execute get_sal (7269)
Pl/SQL call
Declare
Begin
Get_sal (0, 7369 );
End;
----------- Difference between process and function ------------
The function has only one output.
There are multiple processes
----------- Process, output parameter ------------
CREATE or replace PROCEDURE get_info
(P_empno number,
P_ename out varchar2,
P_deptno out number,
P_sal out emp. sal % type)
Is
V_deptno emp. depyno % type;
V_ename emp. ename % type;
V_sal emp. sal % type;
BEGIN
SELECT sal, ename, deptno
INTO v_sal, v_ename, v_deptno
FROM emp
WHERE empno = p_empno;
P_ename: = v_ename;
P_deptno: = v_deptno;
P_sal: = v_sal;
EDN;
Call pl/SQL
Variable v_ename varchar2 (20)
Variable v_sal int;
Variable v_deptno
Execure get_info (7360,: v_ename,: v_deptno,: v_sal)
Print v_sal ;...
----------- Process, mixed parameter ------------
CREATE or replace PROCEDURE chang_no
(P_no in out varchar2)
Is
BEGIN
P_no: = substr (p_no, 1, 3 | '0' | substr (p_no, 4 ));
EDN;
Call pl/SQL
Variable v_no varchar2 (20)
Declare
Begin
: V_no: = '000000 ';
Execure get_no (: v_no)
Print v_no;
----------- Parameter call ------------
Sequence
Name: Real Parameter
Variable v_name varchar2 (20)
Variable v_emptno
(P_name =>: v_name, p_empno =>: v_empno)
----------- Storage function (which can be called in SQL, pl/SQL statements and cannot be part of a statement )------------
Create or replace function add_sal (p_empno number)
RETURN number
IS
V_sal emp. sal % type;
BEGIN
Select sal into v_sal from emp
Where empno = p_empno;
RETURN v_sal * 1.1;
END;
Pl/SQL call
Declare
V_sal: = emp. sal % type;
Begin
V_sal: = add_sal (7369 );
Dbms_output.put_line (v_sal );
End;
SQL call
Select ename, sal, add_sal (empno) from emp;
----------- Trigger ------------
Verify beforehand
Post-event Auditing
Statement level
Row-level
DML
----------- Statement-Level Trigger -----------
Create or replace trigger trig1
Before insert or UPDATE or delete on emp
DECLARE
V_string varchar (20): = "you can't option data ";
BEGIN
IF (TO_CHAR (SYSYDATE, 'dy ') IN ('sat', 'sun') OR (TO_CHAR (SYSDATE, 'hh24: MI '))
Not between '08: 00' AND '18: 00') THEN
RAISE_APPLICATION_ERROE (-20500, v_error );
End if;
END;
----------- Row-Level Trigger -----------
Create or replace trigger trig1
Before insert or UPDATE or delete on emp
FOR EACH ROW
WHEN (OLD. depto = 10)
DECLARE
V_string varchar (20): = "you can't option data ";
BEGIN
IF (TO_CHAR (SYSYDATE, 'dy ') IN ('sat', 'sun') OR (TO_CHAR (SYSDATE, 'hh24: MI '))
Not between '08: 00' AND '18: 00') THEN
RAISE_APPLICATION_ERROE (-20500, v_error );
End if;
END;
Download complete Oracle learning notes txt
Oracle learning notes txt complete novel attachment has been uploaded to Baidu online storage, click to download for free:
It should be full.
Download complete Oracle learning notes txt
Oracle learning notes txt complete novel attachment has been uploaded to Baidu online storage, click to download for free:
It should be full.
