In der letzten Serie wurden nur feste SQL-Anweisungen behandelt, aber in der Realität gibt es viele Situationen, in denen Sie Bindungsvariablen (Platzhalter) verwenden möchten. Dieses Mal werde ich erklären, wie eine Abfrage mithilfe von Bindungsvariablen ausgegeben wird.
Dieses Mal verwenden wir eine Beispiel-SH-Schematabelle. Wenn Sie keine autonome Datenbank verwenden, wählen Sie bitte [Manuell](https://docs.oracle.com/cd/F19136_01/comsc/installing-sample-schemas.html#GUID-A2C3DC59-CDA1-47C0- Sie müssen eine Beispieltabelle des SH-Schemas gemäß BA6B-F6EA6395A85F erstellen. Es ist auch erforderlich, die Beispiel-SQL-Anweisungen und -Berechtigungen entsprechend der Umgebung anzupassen. Sie können das Beispiel ändern, um eine andere Tabelle zu verwenden.
Wenn Sie wissen, wie wichtig es ist, Bindungsvariablen in anderen Entwicklungsumgebungen zu verwenden, überspringen Sie dies bitte, da dies eine langweilige Erklärung ist.
Abhängig von der Logik der Anwendung kann die SQL-Anweisung mit genau demselben Inhalt bis auf den Bedingungswert viele Male ausgeführt werden, während die Anwendung ausgeführt wird. Oracle Database speichert Informationen zu einmal ausgegebenen SQL-Anweisungen zwischen. Wenn dieselbe SQL-Anweisung ausgegeben wird, wird SQL nicht von Grund auf neu überprüft, sondern die zwischengespeicherten Informationen werden verwendet, um die Leistung zu steigern. Das Ziel des Cache-Treffers ist jedoch dasselbe SQL im gesamten Satz. Daher unterscheiden sich beispielsweise "SELECT ... WHEWE COL1 = 1" und "SELECT ... WHEWE COL1 = 2" nur im letzten Bedingungswert, jedoch in unterschiedlichem SQL. Wird behandelt als. Es handelt sich in der Regel um SQL, für das der Primärschlüssel als Bedingung erforderlich ist. Wenn jedoch viel SQL ausgegeben wird, besteht ein Leistungsproblem, dass die SQL-Analyse (Syntaxprüfung, Berechtigungsprüfung usw.) jedes Mal durchgeführt werden muss. Sie müssen viel SQL zwischenspeichern, was den Speicher unter Druck setzt. Um eine solche Situation zu vermeiden, aktiviert Oracle Database eine solche SQL-Freigabe, indem Bedingungswerte als Bindungsvariablen bezeichnet werden (einige DBMS werden als Platzhalter bezeichnet). Beispielsweise werden "SELECT ... WHEWE COL1 =: B01" und die Teile "1" und "2" durch die Variable ": B01" ersetzt, und der tatsächliche Wert wird zur Laufzeit festgelegt (gebunden). Die Teile, die für die Bindevariable angegeben werden können, sind die Teile, die sich auf den Dateninhalt in der Tabelle beziehen. Beispielsweise können Sie Spaltennamen oder Tabellennamen nicht durch Bindungsvariablen ersetzen. Bindungsvariablen können für Spaltenwerte und SELECT-Listen angegeben werden.
Bevor wir die SELECT-Anweisung mit Bindevariablen erläutern, stellen wir ein Muster vor, das keine Bindevariablen verwendet. Ich werde dies in Form einer Überarbeitung erklären.
sample07a.py
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL1 = """
select prod_id, prod_name from sh.products
where prod_category = 'Photo' and prod_subcategory = 'Camera Batteries'
"""
SQL2 = """
select prod_id, prod_name from sh.products
where prod_category = 'Software/Other' and prod_subcategory = 'Bulk Pack Diskettes'
"""
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION, encoding="UTF-8") as connection:
with connection.cursor() as cursor:
cursor.execute(SQL1)
cursor.execute(SQL2)
SQL1 und SQL2 werden in Codierungen, die keine Bindevariablen verwenden, wie sample07a.py, nicht gemeinsam genutzt. Es gibt zwei Möglichkeiten, Bindungsvariablen für die gemeinsame Nutzung zu verwenden.
sample07b.py
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
select prod_id, prod_name from sh.products
where prod_category = :pc and prod_subcategory = :ps
"""
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION, encoding="UTF-8") as connection:
with connection.cursor() as cursor:
cursor.execute(SQL, pc="Photo", ps="Camera Batteries")
cursor.execute(SQL, pc="Software/Other", ps="Bulk Pack Diskettes")
Erstens wird die Anzahl der SQL-Anweisungen auf eins reduziert, da SQL gemeinsam genutzt wird. ": Pc" und ": ps" in der SQL-Anweisung sind Bindungsvariablen. Beginnen Sie mit ":" und geben Sie dann einen Namen an, der den Namenskonventionen von Python entspricht. Der Inhalt der Bindevariablen: pc,: ps wird als Argument der Methode execute () angegeben.
sample07c.py
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
select prod_id, prod_name from sh.products
where prod_category = :pc and prod_subcategory = :ps
"""
bind_variables1 = {"pc":"Photo", "ps":"Camera Batteries"}
bind_variables2 = {"pc":"Software/Other", "ps":"Bulk Pack Diskettes"}
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION, encoding="UTF-8") as connection:
with connection.cursor() as cursor:
cursor.execute(SQL, bind_variables1)
cursor.execute(SQL, bind_variables2)
Erstellen Sie in der Mitte des Beispiels (dem Teil des Satzes von bind_variables1 und 2) ein Wörterbuch, das aus dem Namen der Bindungsvariablen und dem entsprechenden Wert besteht, und geben Sie den Wörterbuchnamen zum Zeitpunkt von execute () an.
Wenn Sie SQL mithilfe von Bindevariablen ausgeben, wird es mithilfe des Anweisungscaches schneller ausgeführt. Einfaches SQL wie das Beispiel in diesem Artikel macht keinen großen Unterschied, aber das [Handbuch] von cx_Oracle (https://cx-oracle.readthedocs.io/en/latest/api_manual/connection.html#Connection.stmtcachesize) ) Sagt bis zu 100 Mal, es gibt also keinen Grund, es nicht zu verwenden.
sample07d.py
import cx_Oracle
USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL = """
select prod_id, prod_name from sh.products
where prod_category = :pc and prod_subcategory = :ps
"""
bind_variables1 = {"pc":"Photo", "ps":"Camera Batteries"}
bind_variables2 = {"pc":"Software/Other", "ps":"Bulk Pack Diskettes"}
with cx_Oracle.connect(USERID, PASSWORD, DESTINATION, encoding="UTF-8") as connection:
with connection.cursor() as cursor:
cursor.prepare(SQL)
cursor.execute(None, bind_variables1)
cursor.execute(None, bind_variables2)
Die prepare () -Methode in der dritten Zeile von unten fügt die SQL in den Anweisungscache ein. Obwohl in der Quelle nicht ausdrücklich erwähnt, beträgt die Standardgröße des Anweisungscaches 20 (20 SQL-Anweisungen). Es ist möglich, auf die Cache-Größe zu verweisen und diese zu ändern, indem auf den Attributwert stmtcachesize des Verbindungsobjekts verwiesen oder dieser geändert wird. Geben Sie beim Ausführen von SQL None als erstes Argument der Methode execute () an, da die entsprechende SQL-Anweisung bereits vorbereitet wurde. Dies funktioniert auch dann, wenn Sie anstelle von None eine vorbereitete SQL-Typvariable angeben. Persönlich denke ich, dass es einfacher ist zu verstehen, dass diejenigen, die None angeben, prep verwenden, aber bitte verwenden Sie das, das Sie mögen.
Ich sehe oft Fälle, in denen SQL-Anweisungen direkt in Form von "f" erstellt werden ... wobei prod_category = {pc} "" oder "... wo prod_category =" + pc ", aber dies ist nicht sehr gut. Es gibt zwei Hauptgründe.
--Die von diesen Logiken generierten SQL-Anweisungen sind Nachdrucke der zuvor gegebenen Erklärungen. Sie können jedoch die gemeinsame Nutzung von SQL-Anweisungen verhindern, die in Bindungsvariablen umgewandelt werden können, was zu Leistungseinbußen und Speicherauslastung führt. es gibt.
Ein solches SQL ist jedoch nicht immer schlecht. Es sei denn, Sie haben eine SQL, die nur einmal am Tag ausgeführt wird und für das Caching keinen Sinn ergibt, oder eine SQL, bei der Sie sich keine Gedanken über die SQL-Injection machen müssen, und Sie haben einen schnelleren Ausführungsplan, wenn Sie keine Bindevariablen verwenden. Ist nicht. Beachten Sie die flexible Implementierung von Fall zu Fall, basierend auf den in diesem Artikel enthaltenen Richtlinien.
Recommended Posts