Übersicht über gespeicherte Prozeduren
Gespeicherte Prozeduren sind eine Art Unterroutine, die einige Aufgaben ausführen kann und als Schemaobjekte in der Datenbank gespeichert wird. Es handelt sich um einen benannten PL/SQL-Codeblock, der das Empfangen oder Nichtannehmen von Parametern sowie die Parameterausgabe unterstützt. Eine gespeicherte Prozedur enthält normalerweise einen Definitionsteil, einen Ausführungsteil und einen Ausnahmeteil. Sie kann von anderen Unterprogrammen aufgerufen und auch wiederverwendet werden.
Prozedurdefinition
VERFAHREN ERSTELLEN [ODER ERSETZEN] prozedurname
[(argument_name [IN | OUT | IN OUT] argument_type)]
AS | 🎜> END [procedure_name];
Typ des Parameters in der gespeicherten Prozedur
IN: Zeigt an, dass es sich um einen Eingabeparameter handelt und ein Standardwert angegeben werden kann. Wenn der Parametertyp weggelassen wird, ist der Standardwert der Typ „in“.
OUT: Zeigt einen Ausgabeparameter an.
IN OUT: Kann als Eingabeparameter oder Ausgabeparameter zur Ausgabe des Ergebnisses verwendet werden.
Prozeduraufruf
EXECUTE |CALL procedure_name [(argument_list)]
Beispiel: Definieren Sie eine Prozedur, die JOB als Parameter verwendet, um das maximale Gehalt, das minimale Gehalt und das durchschnittliche Gehalt des JOB abzufragen.
as
v_min_sal emp.sal%type; v_max_sal emp.sal%type;
v_avg_sal emp. sal%type;
begin
select min(sal) into v_min_sal from emp where job = v_job;
select max(sal) into v_max_sal from emp where job = v_job;
select avg(sal) in v_avg_sal von emp, wobei job = v_job;
dbms_output.put_line('Dieser Job hat das Mindestgehalt ' || v_min_sal);
dbms_output.put_line('Dieser Job hat das maximale Gehalt ' || v_max_sal);
dbms_output.put_line('Dieser Job hat ein durchschnittliches Gehalt von ' || v_avg_sal);
Ausnahme
wenn keine_Daten gefunden wurden, dann
dbms_output.put_line('Kein Datensatz gefunden');
Ende;
SQL> set serveroutput on
SQL> ist 1400
Parameter und ihre Übermittlungsmethoden
Beim Einrichten einer Prozedur sind die übergebenen Parameter optional. Wenn die Parameteroption weggelassen wird, ist die Prozedur eine Parameterlose Prozedur (Beim Definieren werden keine Parameter angegeben und beim Aufruf sind keine Parameter erforderlich.) Wenn die Parameteroption angegeben ist, handelt es sich bei dem Prozess um einen parametrisierten Prozess (Parametername, Modus und Datentyp müssen beim Definieren angegeben werden, und der entsprechende Parameterwert muss beim Anpassen angegeben werden. Die Parameter beim Definieren werden als formal bezeichnet). Parameter, und die Parameter beim Aufruf werden als formale Parameter bezeichnet.
Keine Parameterprozedur
Prozedur display_systime erstellen oder ersetzen
begin
dbms_output.put_line('Current Time is ' || sysdate);
SQL> exec display_systime;
Aktuelle Zeit ist der 24.02.13
Parameterprozedur
Sie müssen den Namen, den Modus und den Datentyp des Parameters angeben, wenn Sie ihn definieren
Beispiel: Definieren Sie eine Methode zum Hinzufügen von Datensätzen Prozedur (alle Eingabeparameter)
Erstellen oder ersetzen Sie die Prozedur add_emp
(
v_name in emp.ename%type,
v_dept Geben Sie in emp.deptno% den Standardwert 20 ein --Standardabteilungsnummer
)
as
begin
insert into emp (empno,ename,deptno) Values(v_no,v_name,v_dept);
Ausnahme
when dup_val_on_index then
dbms_output.put_line('Record Exists');
end ;
SQL> --Call
Datensatz existiert
Beispiel: Definieren Sie eine eingegebene Mitarbeiternummer, ändern Sie den Datensatz und geben Sie dann das geänderte Ergebnis (Name und Gehalt) zurück.
(
v_no in emp.empno%type, -- definiert einen In-Typ- und zwei Out-Typ-Parameter
v_sal out emp.sal%type)
as begin
update emp set sal = sal + 100 where empno = v_no;
select ename,sal into v_name,v_sal from emp where empno = v_no ;
Ausnahme
wenn keine_Daten gefunden wurden, dann
dbms_output.put_line('Not Data Found');
end;
/
Prozedur erstellt.
SQL> ;VARIABLE t_name varchar2(20);
SQL>VARIABLE t_sal number;
SQL> exec ed_emp(7369,:t_name,:t_sal);
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> print t_name
---------------- --- ------------------------------------
SMITH
SQL> t_sal
T_SAL
----------
900
Beispiel: Verwendung von Parametern vom Typ IN OUT
Prozedur comp erstellen oder ersetzen
(num1 in out number,num2 in out number)
as
v1 number;
v2 number;
begin
v1 := num1 + num2;
v2 := num1 * num2;
num1 := v1;
num2 := v2;
end;
SQL> var v1 number
SQL> var v2 number
SQL> exec :v1 := 3
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> exec :v2 := 5
SQL exec comp(: v1,:v2);
SQL> print v1 v2
V1
----------
8
V2
-------- --
15
SQL> exec comp(:v1,:v2);
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> print v1 v2
---- ------
23
V2
----------
120
Sie können sehen, dass die In-Out-Typparameter beide Eingabeparameter sind und als Ausgabeparameter.
Übertragungsmethode der gespeicherten Prozedurparameter:
Übertragung nach Position:Die tatsächlichen Parameter werden in der Reihenfolge an die formalen Parameter übergeben
EXECUTE ED_EMP(7900,:t_name,:t_sal);
EXECUTE ED_EMP(8000,'TEST2',20);
Nach Namen übergeben
EXECUTE ED_EMP(v_name=>'ABCDE',v_dept=>10,v_no=>8003);
Gemischter Durchgang
EXECUTE ED_EMP(8005,v_dept=>20,v_name=>'TEST5');
Prozessverwaltung
Systemprozessinformationen anzeigen
DBA_OBJECTS
DBA_PROCEDURES
DBA_SOURCE
Verwenden Sie desc procedure_name, um Parameterinformationen gespeicherter Prozeduren anzuzeigen
SQL>desc ed_emp;
PROCEDURE ed_emp
Argument Name Type ---------------- ------ --------
V_NO NUMBER(4) IN
V_NAME VARCHAR2 (10) OUT
V_SAL NUMBER (7,2) OUT
Die Informationen des Speichervorgangs von DBA_Objects
SQL & GT Select Owner, Object_name, Object_type, Status from DBA_OBJECTS WHERE OONECT_NAME = 'ED_EMP';
Eigentümer Objektname Objekttyp Status
---------------------------- ----------- ------- - --------------- -------
SCOTT ED_EMP PROCEDURE VALID
SQL>select object_name,procedure_name,interface,authid from user_procedures;
OBJECT_NAME PROCEDURE_NAME INT AUTHID
-------------------------- ----------- ------------- ------ --- ------------
DISPLAY_SAL ER
Sehen Sie sich den Quellcode der gespeicherten Prozedur an
SQL>select line, text from user_source where name='ED_EMP';
LINE TEXT
---------- -------------- --------- --------------------------- --------- --------
1 PROCEDURE ed_emp
2 (
3 v_no IN emp.empno%TYPE,
4 v_name OUT emp.ename % TYPE,
5 v_sal OUT emp .sal%TYPE
6 )
7 AS
8 BEGIN
9 UPDATE emp SET sal=sal+100 WHER E empno=v_no;
10 SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empno=v_no;
11 EXCEPTION
12 WHEN NO_DATA_FOUND THEN
13 DBMS_OUTPUT.PUT_LINE('NOT FOUND RECO RD!');
14 END ed_emp;
Fehlermeldung anzeigen
FEHLER ANZEIGEN
Funktionsübersicht
Funktionen werden normalerweise verwendet, um bestimmte Daten zurückzugeben. Sein Kern ist ein benannter PL/SQL-Block, der als Schemaobjekt in der Datenbank gespeichert wird und wiederholt ausgeführt werden kann. Eine Funktion wird normalerweise als Ausdruck oder Parameter einer gespeicherten Prozedur aufgerufen und hat einen Rückgabewert.
1. Syntax zum Erstellen einer Funktion
CREATE [ OR REPLACE ] FUNCTION function_name
(argument1 [mode1] datatype1,
argument2 [mode2] datetype2,
...)
RETURN Datentyp
IS |. AS
[local_variable_declarations;...]
BEGIN
--actions;
RETURN expression;
END [function_name];
Einige Hinweise zu Das Einrichten von Funktionen ist wichtig
1. Bei der Angabe des Parameterdatentyps (Arguments) kann die Länge nicht angegeben werden
2 Der Funktionsheader muss eine Rückgabeklausel angeben und der Funktionskörper muss mindestens eine Rückgabeanweisung enthalten
3. Der In-Parameter kann angegeben werden, Sie können auch Out-Parameter angeben und In-Out-Parameter
4 Sie können Standardwerte für Parameter angeben. Verwenden Sie das Schlüsselwort default, wenn Sie einen Standardwert angeben. Beispiel: arg1 varchar2 default 'SCOTT'
Vorteile der Verwendung von Funktionen:
1. Es erhöht die Flexibilität des Codes und kann einige komplexere Aufgaben sowie Aufgaben erledigen, die nicht nur über SQL erledigt werden können
2. Es kann direkt die Funktion in der where-Klausel verwenden, um Daten zu filtern
3 Es kann als Parameter der gespeicherten Prozedur verwendet werden, die eine Ergänzung zur gespeicherten Prozedur darstellt
Erstellen Sie eine Funktion
Eine Funktion ohne Parameter erstellen
Funktion get_user erstellen oder ersetzen
varchar2 zurückgeben
as
v_user varchar2(20);
begin
Benutzernamen in v_user auswählen aus user_users;
return v_user;
end;
Verwenden Sie globale Variablen, um den Rückgabewert der Funktion zu empfangen
SQL> exec :v1 := get_user;
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> print v1
V1
------------------- ------------- -----
SCOTT
Verwenden Sie lokale Variablen, um den Rückgabewert der Funktion zu erhalten
4 dbms_output.put_line('Aktueller Benutzer: ' || Benutzername);
5 end;
6 /
Aktueller Benutzer: SCOTT
PL/SQL-Prozedur erfolgreich abgeschlossen.
Funktion direkt in der SQL-Anweisung aufrufen
GET_USER
----------------- --------- -SCOTT
Verwenden Sie dbms_output, um die Funktion aufzurufen (dieser Aufruf erfolgt als Parameter der gespeicherten Prozedur). )
SQL> set serveroutput on;
SQL> exec dbms_output.put_line('Aktueller Benutzer: '||get_user);
Aktueller Benutzer: SCOTT
Erstellen Sie eine Funktion mit in-Parametern
Rückgabenummer
as new_sal emp.sal%type;
begin
select sal * 1.2 into new_sal from emp
where Upper(ename) = Upper( name);
return new_sal;
Exception
when no_data_found then
raise_application_error(-20000,'Aktueller Mitarbeiter existiert nicht');
end ;
SAL RAISE_SAL('SCOTT')
---- ------ ---- -------------- 3000 3600
select sal,raise_sal('SCOTTT') from emp where ename='SCOTT'
*ERROR at line 1:
ORA-20000 : Aktueller Mitarbeiter existiert nicht
ORA- 06512: bei „SCOTT.RAISE_SAL“, Zeile 11
Erstellen Sie eine Funktion ohne Parameter
Erstellen oder ersetzen Sie die Funktion get_info
return varchar2
as
deptname dept.dname%type;
begin
select e.job,d.dname into titile,deptname
from emp e,dept d
wobei e.deptno = d. deptno
und Upper(e.ename) = Upper(name);
return deptname;
Exception
when no_data_found then
raise_application_error(-20000 ,'Aktueller Mitarbeiter existiert nicht');
end;
/
Beachten Sie, dass Funktionen, die Out-Parameter verwenden, nicht mit SQL-Anweisungen aufgerufen werden können. Stattdessen müssen Variablen definiert werden, um Ausgangsparameter und den Rückgabewert der Funktion zu erhalten.
Der Aufruf lautet wie folgt
SQL> var job varchar2(20);
SQL> var dname varchar2(20);
SQL> exec :dname := get_info('scott',:job);
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> dname-Auftrag drucken
DNAME
------------------------------------------------------ ---------
FORSCHUNG
JOB
------------------------------ -------------------
ANALYST
SQL> select get_info('scott') from dual
2 ;
select get_info('scott') from dual
*
FEHLER in Zeile 1:
ORA-06553: PLS-306: falsch Anzahl oder Arten von Argumenten im Aufruf von 'GET_INFO'
建立带有in out参数的函数
Funktion comp erstellen oder ersetzen
(num1 number,num2 in out number)
return number
as
v_result number(6);
v_remainder number;
begin
v_result := num1 * num2;
v_remainder := mod(num1,num2);
num2 := v_remainder;
return v_result;
Exception
when zero_divide then
raise_application_error(-20000,'Division by zero');
end;
/
SQL> var n1 Zahl
SQL> var n2 Zahl
SQL> exec :n2 := 10;
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> exec :n1 := comp(16,:n2);
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> print n1 n2
N1
----------
160
N2
----------
6
函数的调用及限制
1.函数的调用(其具体调用方法参照上面的演示)
a.使用全局变量接收函数的返回值
b.使用本地变量接受函数的返回值
c.在SQL语句中直接调用函数
d.使用dbms_output调用函数
注：函数在调用的时候需要按位置指定参数，没有存储过程参数传递灵活必须具有execute 函数的权限
2 .函数在SQL中调用的主要场合
由于函数必须要返回数据,因此只能作为表达式的一部分调用。此外函数可以在SQL语句的以下部分调用
a. Wählen Sie 命令的选择列表或子查询中
b. 条件表达式wo, mit子句中
c. verbinden nach, beginnen mit, ordnen nach 以及, gruppieren nach 子句中
d.命令的Werte子句中 einfügen
f. update 命令的set 子句中
3.函数在SQL中调用的限制
a. SQL语句中只能调用存储在服务器端的函数，而不能调用存储于客户端的函数
b. SQL语句中调用的函数只能带有输入参数IN,而不能带有输出参数OUT 以及输入输出参数IN OUT
c. SQL语句中调用的函数只能使用SQL支持的标准数据类型,不能使用PL/SQL特有的类型，如boolean,table,record等
d. SQL语句中调用的函数不能包含einfügen, aktualisieren und löschen 语句
创建一张表tb_emp
SQL> Tabelle tb_emp erstellen, indem Sie * aus emp auswählen;
Tabelle erstellt.
薪资
SQL> Funktion erstellen oder ersetzen del_emp
2 (keine Zahl)
3 Rückgabenummer
4 as
5 v_sal emp.sal%type;
6 begin
7 select sal into v_sal from emp where empno = no;
8 delete tb_emp where empno = no;
9 return v_sal;
10 end;
11 /
Funktion erstellt.
使用SQL语句调用时,收到了错误信息,在内部查询内不能完成DML操作
SQL> select del_emp(7788) from dual;
select del_emp(7788) from dual
*
FEHLER in Zeile 1:
ORA-14551: Eine DML-Operation innerhalb einer Abfrage kann nicht ausgeführt werden
ORA- 06512: bei „SCOTT.DEL_EMP“, Zeile 8
使用exec执行时函数被成功执行
SQL> var n1 Zahl
SQL> exec :n1 := del_emp(7788);
PL/SQL-Prozedur erfolgreich abgeschlossen.
SQL> print n1
N1
----------
3000
函数的管理
函数使用了与存储过程相关的视图, 可以从系统视图中获得函数的相关信息
DBA_OBJECTS
DBA_SOURCE
USER_OBJECTS
USER_SOURCE
查看函数的源码
SQL>Zeile auswählen, Text aus Benutzerquelle, wobei Name ='DEL_EMP' Reihenfolge nach Zeile
ZEILENTEXT
-------------------------------------------------------- ----------------------------
1 Funktion del_emp
2 (keine Zahl)
3 Rückgabenummer
4 as
5 v_sal emp.sal%type;
6 begin
7 select sal into v_sal from emp where empno = no;
8 delete tb_emp where empno = no;
9 return v_sal; 10 Zeilen ausgewählt;
SQL & GT; Funktion Del_Emp Gibt Nummer zurück /Out Default?
NEIN Funktion
--------------------- ------------- -----------------不能被作为表达式调用 只能作为表达式被调用
声明头部关键字为prozedur 声明头部关键字为function
Der Deklarationsheader muss das Rückgabeschlüsselwort enthalten, um den Rückgabetyp zu beschreiben, und der PL/SQL-Block enthält mindestens ein gültiges Schlüsselwort return-Anweisung.
Sie können durch out, in out einen Wert zurückgeben, der mit dem Typ in der Head-Deklaration übereinstimmt, und Sie können auch in, in the out of SQL-Anweisung, die Speicherprozedur SQL-Anweisung kann aufgerufen werden. Operationen wie Löschen, Aktualisieren, Einfügen und andere DML-Operationen werden hauptsächlich für bestimmte Daten wie Auswahl usw. verwendet.
![[Web-Frontend] Node.js-Schnellstart](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
