Einführung in die grundlegende Syntax gespeicherter Oracle-Prozeduren

Grundlegende Syntax gespeicherter Oracle-Prozeduren. Gespeicherte Prozedur
　1 PROZEDUR ERSTELLEN ODER ERSETZEN. Name der gespeicherten Prozedur
　2 IS
　3 BEGIN
　4 NULL;
　5 END;

Zeile 1:
ERSTELLEN ODER ERSETZEN PROCEDURE ist eine SQL-Anweisung, die die Oracle-Datenbank anweist, eine gespeicherte Prozedur namens Skeleton zu erstellen und diese zu überschreiben, falls vorhanden
Zeile 2:
Das Schlüsselwort IS gibt an, dass ein PL/SQL-Körper folgen wird.
Zeile 3:
Das Schlüsselwort BEGIN gibt den Anfang des PL/SQL-Körpers an.
Zeile 4:
Die NULL PL/SQL-Anweisung gibt an, dass nichts getan werden soll. Dieser Satz kann nicht gelöscht werden, da mindestens ein Satz im PL/SQL-Körper vorhanden sein muss.
Zeile 5:
Das Schlüsselwort END gibt das Ende des PL/SQL-Körpers an.
Syntax zur Erstellung gespeicherter Prozeduren:
Prozedur erstellen oder ersetzen Name der gespeicherten Prozedur (Param1-Eingangstyp, Param2-Ausgangstyp)
als
Variable 1-Typ (Wertebereich); VARCHAR2(4000);
Variable 2 Typ (Wertebereich);

Begin 
Select count(*) into 变量1 from 表A where列名=param1； 

If (判断条件) then 
Select 列名 into 变量2 from 表A where列名=param1； 
Dbms_output。Put_line(‘打印信息'); 
Elsif (判断条件) then 
Dbms_output。Put_line(‘打印信息'); 
Else 
Raise 异常名（NO_DATA_FOUND）; 
End if; 
Exception 
When others then 
Rollback; 
End;

Hinweise:
1. out bedeutet, dass der Ausgabetyp
jeden zulässigen Typ in Oracle verwenden kann.
2, Die Variable hat einen Wertebereich, gefolgt von einem Semikolon
3. Bevor Sie die Anweisung beurteilen, verwenden Sie am besten die Funktion count (*), um festzustellen, ob der Vorgangsdatensatz vorhanden ist
4, und verwenden Sie select . . . hinein. . . Weisen Sie einer Variablen einen Wert zu
5. Um eine Ausnahme im Code auszulösen, verwenden Sie raise + Exception name

CREATE OR REPLACE PROCEDURE存储过程名 
( 
--定义参数 
is_ym IN CHAR(6) , 
the_count OUT NUMBER, 
) 
AS 
--定义变量 
vs_msg VARCHAR2(4000); --错误信息变量 
vs_ym_beg CHAR(6); --起始月份 
vs_ym_end CHAR(6); --终止月份 
vs_ym_sn_beg CHAR(6); --同期起始月份 
vs_ym_sn_end CHAR(6); --同期终止月份 
--定义游标(简单的说就是一个可以遍历的结果集) 

CURSOR cur_1 IS 
SELECT 。。。 
FROM 。。。 
WHERE 。。。 
GROUP BY 。。。; 
BEGIN 

--用输入参数给变量赋初值，用到了Oralce的SUBSTR TO_CHAR ADD_MONTHS 

TO_DATE 等很常用的函数。 
vs_ym_beg := SUBSTR(is_ym,1,6); 
vs_ym_end := SUBSTR(is_ym,7,6); 
vs_ym_sn_beg := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'), -12),'yyyymm'); 
vs_ym_sn_end := TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,'yyyymm'), -12),'yyyymm'); 

--先删除表中特定条件的数据。 

DELETE FROM 表名 WHERE ym = is_ym; 

--然后用内置的DBMS_OUTPUT对象的put_line方法打印出影响的记录行数，其中用到一个系统变量SQL%rowcount 

DBMS_OUTPUT.put_line('del上月记录='||SQL%rowcount||'条'); 
INSERT INTO表名(area_code,ym,CMCODE,rmb_amt,usd_amt) 
SELECT area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000 
FROM BGD_AREA_CM_M_BASE_T 
WHERE ym >= vs_ym_beg 
AND ym <= vs_ym_end 
GROUP BY area_code,CMCODE; 
DBMS_OUTPUT.put_line(&#39;ins当月记录=&#39;||SQL%rowcount||&#39;条&#39;); 
--遍历游标处理后更新到表。遍历游标有几种方法，用for语句是其中比较直观的一种。 

FOR rec IN cur_1 LOOP 
UPDATE 表名 
SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn 
WHERE area_code = rec.area_code 
AND CMCODE = rec.CMCODE 
AND ym = is_ym; 
END LOOP; 
COMMIT; 

--错误处理部分。OTHERS表示除了声明外的任意错误。SQLERRM是系统内置变量保存了当前错误的详细信息。 

EXCEPTION 

WHEN OTHERS THEN 
vs_msg := &#39;ERROR IN xxxxxxxxxxx_p(&#39;||is_ym||&#39;):&#39;||SUBSTR(SQLERRM,1,500); 

ROLLBACK; 

--把当前错误记录进日志表。 

INSERT INTO LOG_INFO(proc_name,error_info,op_date) 
VALUES(&#39;xxxxxxxxxxx_p&#39;,vs_msg,SYSDATE); 
COMMIT; 
RETURN; 

END;

Syntax der gespeicherten Oracle-Prozedur
1 >if-Vergleich, dann begin end ; end if;
(1) Schleife durch den Cursor

create or replace procedure test(x in number) is 
begin 
if x >0 then 
begin 
x := 0 - x; 
end; 
end if; 
if x = 0 then 
begin 
x: = 1; 
end; 
end if; 
end test;

(2) Schleife durch das Array


3, While-Schleife
while-Bedingungsanweisung LOOP

create or replace procedure test() as 
Cursor cursor is select name from student; name varchar(20); 
begin 
for name in cursor LOOP 
begin 
dbms_output.putline(name); 
end; 
end LOOP; 
end test;

4. Array

Lassen Sie uns zunächst ein Konzept klären: Oracle hat kein Array-Konzept. Jedes Array-Element ist ein Datensatz in der Tabelle.

create or replace procedure test(varArray in myPackage.TestArray) as 
--( 输入参数varArray 是自定义的数组类型，定义方式见标题6) 
i number; 
begin 
i := 1; -- 存储过程数组是起始位置是从1 开始的，与java 、C 、C++ 等语言不同。因为在Oracle 中本是没有数组的概念的，数组其实就是一张 
-- 表(Table), 每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历 
for i in 1..varArray.count LOOP 
dbms_output.putline('The No.'|| i || 'record in varArray is:'||varArray(i)); 
end LOOP; 
end test;

Bei der Verwendung von Arrays können Benutzer die bereits von Oracle definierten Array-Typen verwenden oder Array-Typen entsprechend ihren eigenen Anforderungen definieren.

(1) Verwenden Sie Oracles eigenen Array-Typ
x-Array. Bei Verwendung ist eine Initialisierung erforderlich

z. B.:

begin 
end; 
end LOOP; 
E.g 
create or replace procedure test(i in number) as 
begin 
while i < 10 LOOP 
begin 
i:= i + 1; 
end; 
end LOOP; 
end test;

erstellen oder ersetzen procedure test(y out array) is

x array;
y := x;
end test;
(2) Benutzerdefinierter Array-Typ ( Beim Anpassen von Datentypen wird empfohlen, zur einfacheren Verwaltung ein Paket zu erstellen.)
Paket erstellen oder ersetzen, das mein Paket ist
Typinformationen für öffentliche Typdeklarationen sind record( name varchar(20), y number);
Typ TestArray ist Tabelle mit Informationsindex nach Binary_Integer; Hier werden Daten vom Typ TestArray deklariert. Tatsächlich handelt es sich nur um eine Tabelle, die den Datentyp Info speichert, und TestArray ist eine Tabelle mit zwei Feldern, eines ist der Name. , einer ist y. Es ist zu beachten, dass hier der Index von „binary_integer“ verwendet wird, um das Indexelement der Tabelle zu kompilieren. Sie können es auch direkt als Typ schreiben, ohne es zu schreiben. TestArray ist eine Tabelle mit Informationen. Wenn Sie sie nicht schreiben, müssen Sie sie initialisieren, wenn Sie das Array verwenden: varArray myPackage.TestArray; varArray := new myPackage.TestArray();
5. Verwendung von Cursor Cursor in Oracle Ist sehr nützlich für die Iteration über Abfrageergebnisse in einer temporären Tabelle. Es gibt auch viele verwandte Methoden und Attribute. Jetzt werde ich nur die allgemeine Verwendung vorstellen:
(1) Cursor vom Typ Cursor (kann nicht für die Parameterübertragung verwendet werden)


Beispiel
Schreiben Sie unten Ein einfaches Beispiel für die Anwendung der oben genannten gespeicherten Prozeduren:
Nehmen wir nun an, dass es zwei Tabellen gibt, eine davon ist die Schülerleistungstabelle (studnet). , die Felder sind: stdId, Mathematik, Artikel, Sprache, Musik, Sport, Gesamt, Durchschnitt, Schritt
Eine davon ist die außerschulische Leistungstabelle der Schüler (out_school) mit den Feldern: stdId, parctice, comment
Die Gesamtpunktzahl und die Durchschnittspunktzahl jedes Schülers werden automatisch durch das gespeicherte Verfahren berechnet. Wenn der Schüler im außerschulischen Kurs eine Eins erhält, werden gleichzeitig 20 Punkte zur Gesamtpunktzahl addiert.