The content mainly includes:
(1) Three kinds of cycles and their simplification
(2) Use of cursors
(3) Exception handling
(4) Stored procedures
(5) Storage function
(6) Trigger
(7) Other PL/SQL operations
Define variables---------------Loop loop--------------------
Declare
Cursor C1 is a 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 Loop--------------------
Declare
Cursor C1 is a 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
---------------Loop Loop simplifies--------------------
Declare
Cursor C1 is a 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 a 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, the most concise, not easy to produce a dead loop, self-closing--------------------
Declare
#记录集
Cursor C1 is a SELECT * from EMP;
#一行记录
Rec Emp%rowtype;
Begin
#for默认打开游标
For Rec in C1 loop
Insert into EMP1 (empno,job) VALUES (rec. Empno,rec. JOB)
End Loop;
End
---------------for Loop simplifies--------------------
Declare
#记录集
Cursor C1 is a SELECT * from EMP;
Begin
#for默认打开游标
#for隐含声明rec, so don't declare it.
For Rec in C1 loop
Insert into EMP1 (empno,job) VALUES (rec. Empno,rec. JOB)
End Loop;
End
---------------for loop, and then simplify--------------------
Declare
Begin
#for默认打开游标
#for隐含声明rec, so don't declare it.
For rec in (SELECT * from emp) loop
INSERT INTO EMP1 values Rec
End Loop;
End
---------------a cursor with parameters--------------------
Declare
Cursor C1 (P_DEPTNO number) is a 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
---------------Printing--------------------
Dbms_output.put_line (V_val);
---------------Exception--------------------
Oracle Pre-defined exceptions
Oracle Error
User-defined exceptions
---------------Use Exceptions----------------
Defining exceptions
Catch 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;
---------------the 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, ' STH ');
END;
---------------Define Variables--------------
V_1 Data:=sysdata
---------------Catch 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, ' STH ');
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 calls (each language has its own method of invocation)
Execute Get_sal (7269)
PL/SQL Calls
Declare
Begin
Get_sal (7369);
End
-----------the difference between a procedure and a function------------
function has only one output
Process has multiple
-----------process, output parameters------------
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:= ' 1234567890 ';
Execure get_no (: v_no)
Print v_no;
-----------parameter Call------------
Order
Name: formal parameter argument
Variable v_name varchar2 (20)
Variable V_emptno
(P_name=>:v_name,p_empno=>:v_empno)
-----------stored functions (which can be called in sql, PL/t statements, not as part of a statement)------------
CREATE OR REPLACE FUNCTION add_sal (p_empno number)
RETURN number
Is
V_sal Emp.sal%type;
BEGIN
Select Sal to v_sal from EMP
where Empno=p_empno;
RETURN v_sal*1.1;
END;
PL/SQL Calls
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------------
Pre-calibration
Ex post Audit
Statement-level
Row level
Dml
-----------statement-level triggers-----------
CREATE OR REPLACE TRIGGER trig1
Before INSERT or UPDATE or DELETE on EMP
DECLARE
v_string varchar: = "You can ' t option data";
BEGIN
IF (To_char (sysydate, ' DY ') in (' SAT ', ' SUN ')) OR (To_char (sysdate, ' Hh24:mi '))
Not between ' "and ' 18:00 ') then
Raise_application_erroe ( -20500,v_error);
END IF;
END;
-----------row-level triggers-----------
CREATE OR REPLACE TRIGGER trig1
Before INSERT or UPDATE or DELETE on EMP
For each ROW
When (old.depto=10)
DECLARE
v_string varchar: = "You can ' t option data";
BEGIN
IF (To_char (sysydate, ' DY ') in (' SAT ', ' SUN ')) OR (To_char (sysdate, ' Hh24:mi '))
Not between ' "and ' 18:00 ') then
Raise_application_erroe ( -20500,v_error);
END IF;
END;
