zmienne bindowane ORACLE
geos
x varchar2 := 'ala ma :1 a kot ma :2';
chciałbym, aby zawartość zmiennej x uległa rozwinięciu do 'ala ma kota a
kot ma fioła' z wykorzystaniem execute immediate i klauzuli using
'kota', 'fioła'.
czy jest to możliwe do zrobienia? jeśli tak to jak? jeśli nie to
dlaczego? proszę o wskazówki lub wyjaśnienia.
pozdrawiam,
geos
Piotr M Kuć
Proszę
x := 'ala ma kota a kot ma fioła';
Po co Ci tu zmienne wiązane? Przedstaw jakiś bardziej
realistyczny przykład tego co chcesz osiągnąć.
--
Pozdrawiam, Piotr.Kuc-(szympans)-kuciak.net
Piotr Kuć
geos
> Proszę
>
> x := 'ala ma kota a kot ma fioła';
>
> Po co Ci tu zmienne wiązane? Przedstaw jakiś bardziej
> realistyczny przykład tego co chcesz osiągnąć.
chciałbym się dowiedzieć czy można wykorzystać zmienne bindowane oraz
execute immediate do rozwijania wartości zmiennych zapisanych "w
konwencji" zmiennych bindowanych. proste zastosowanie jakie przychodzi
mi do głowy to szablon korespondencji wypełniany danymi klienta.
można to potraktować funkcją lub sklejać ale chcę się dowiedzieć czy z
użyciem execute immediate i zmiennych bindowanych można redefiniować
wartości zmiennych. czyli dopuścić składnię podobną do:
x varchar2(50) := 'ala ma :1 a kot ma :2';
execute immediate x := x using 'kota', 'fiola';
na wzór działającego:
x number := 1234;
execute immediate 'update emp set ename = 'DOE' where empno=:1' using x;
przy okazji będę wdzięczny za wyjaśnienie dlaczego zmiennych bindowanych
nie można wykorzystać do przekazywania nazw tabel, schematów itp. który
mechanizm oracle nie dozwala takiego użycia?
pozdrawiam,
geos
Sławomir Szyszło
>chciałbym się dowiedzieć czy można wykorzystać zmienne bindowane oraz
>execute immediate do rozwijania wartości zmiennych zapisanych "w
>konwencji" zmiennych bindowanych. proste zastosowanie jakie przychodzi
>mi do głowy to szablon korespondencji wypełniany danymi klienta.
>
>można to potraktować funkcją lub sklejać ale chcę się dowiedzieć czy z
>użyciem execute immediate i zmiennych bindowanych można redefiniować
>wartości zmiennych. czyli dopuścić składnię podobną do:
>
>x varchar2(50) := 'ala ma :1 a kot ma :2';
>execute immediate x := x using 'kota', 'fiola';
Czyli za pomocą execute immediate chcesz sobie podstawiać wartości w określone
miejsca stringa? No to żeś wymyślił. :) To polecenie służy do wykonywania
zapytań/bloków anonimowych PL/SQL itd.
Po co angażować silnik SQL, skoro chcesz operować tylko na zmiennych w pamięci?
Masz za mocne CPU i chcesz je podgrzać? :)
Zrób swoją funkcję, która w stringu za znak specjalny (#, $, & czy co tam
chcesz) będzie podstawiać podane wartości.
>przy okazji będę wdzięczny za wyjaśnienie dlaczego zmiennych bindowanych
>nie można wykorzystać do przekazywania nazw tabel, schematów itp. który
>mechanizm oracle nie dozwala takiego użycia?
Dlatego, że podstawienie wartości do *zmiennych* jest wykonywane po parsowaniu
zapytania. A w momencie parsowania jego treść (czyli schemat, tabele, ...) musi
być w pełni znana.
--
Sławomir Szyszło
Primus inter FAQires & Grand Inquisitor no.0 of pl.comp.bazy-danych
FAQ pl.comp.bazy-danych http://www.dbf.pl/faq/
Archiwum http://groups.google.com/groups?group=pl.comp.bazy-danych
geos
> Dnia Tue, 23 Aug 2011 12:24:36 +0200, geos <ge...@nowhere.invalid> wklepał(-a):
>> execute immediate x := x using 'kota', 'fiola';
>
> Czyli za pomocą execute immediate chcesz sobie podstawiać wartości w określone
> miejsca stringa? No to żeś wymyślił. :) To polecenie służy do wykonywania
> zapytań/bloków anonimowych PL/SQL itd.
:) no tak, chciałem się dowiedzieć czy to jest możliwe. jestem świeżo po
kursie pl/sql i eksperymentuję.
>> przy okazji będę wdzięczny za wyjaśnienie dlaczego zmiennych bindowanych
>> nie można wykorzystać do przekazywania nazw tabel, schematów itp. który
>> mechanizm oracle nie dozwala takiego użycia?
>
> Dlatego, że podstawienie wartości do *zmiennych* jest wykonywane po parsowaniu
> zapytania. A w momencie parsowania jego treść (czyli schemat, tabele, ...) musi
> być w pełni znana.
rozumiem. a ponieważ z parsowania wychodzi m.in. plan wykonania to bez
nazw kluczowych elementów nie mógłby być opracowany.
dzięki serdeczne za odpowiedź.
pozdrawiam,
geos
Maseł
> Sławomir Szyszło wrote:
>> Dnia Tue, 23 Aug 2011 12:24:36 +0200, geos <ge...@nowhere.invalid>
>> wklepał(-a):
>>> x varchar2(50) := 'ala ma :1 a kot ma :2';
>>> execute immediate x := x using 'kota', 'fiola';
>>
>> Czyli za pomocą execute immediate chcesz sobie podstawiać wartości w
>> określone
>> miejsca stringa? No to żeś wymyślił. :) To polecenie służy do wykonywania
>> zapytań/bloków anonimowych PL/SQL itd.
>
> :) no tak, chciałem się dowiedzieć czy to jest możliwe. jestem świeżo po
> kursie pl/sql i eksperymentuję.
execute immediate 'select funkcja_slawka(''twoj string ze zmiennymi
pseudobindowania'', ''co dobindowac'') from dual'
Kuna - chyba rozstrzelalbym programiste, jak bym cos takiego zobaczyl :-)
>>> przy okazji będę wdzięczny za wyjaśnienie dlaczego zmiennych
>>> bindowanych nie można wykorzystać do przekazywania nazw tabel,
>>> schematów itp. który mechanizm oracle nie dozwala takiego użycia?
>>
>> Dlatego, że podstawienie wartości do *zmiennych* jest wykonywane po
>> parsowaniu
>> zapytania. A w momencie parsowania jego treść (czyli schemat, tabele,
>> ...) musi
>> być w pełni znana.
>
> rozumiem. a ponieważ z parsowania wychodzi m.in. plan wykonania to bez
> nazw kluczowych elementów nie mógłby być opracowany.
Dokladne tak
Pozdro
Maseł
P.S. co to byl za kurs, ze sie nie dopytales takich rzeczy?
geos
>> W dniu 2011-08-23 21:27, geos pisze:
>> kursie pl/sql i eksperymentuję.
>
> execute immediate 'select funkcja_slawka(''twoj string ze zmiennymi
> pseudobindowania'', ''co dobindowac'') from dual'
>
> Kuna - chyba rozstrzelalbym programiste, jak bym cos takiego zobaczyl :-)
>> rozumiem. a ponieważ z parsowania wychodzi m.in. plan wykonania to bez
>> nazw kluczowych elementów nie mógłby być opracowany.
>
> Dokladne tak
> P.S. co to byl za kurs, ze sie nie dopytales takich rzeczy?
ogólnie na szkoleniach pytam jak najęty, ale tak się złożyło, że
poruszane przykłady i zagadnienia w kontekście execute immediate nie
podsunęły mi takiego pytania. dopiero podczas przeglądania notatek
dostrzegłem pewne podobieństwa z np. funkcją eval z javascript i
zacząłem się zastanawiać jak "głęboko" zaimplementowana jest
funkcjonalność execute immediate. do układanki brakowało mi informacji,
że podstawienie wartości do zmiennych jest wykonywane po parsowaniu.
dalej jednak zastanawia mnie czy teoretycznie implementacja w przypadku
execute immediate nie mogłaby być bardziej elastyczna: skoro i tak
parsowanie odroczone jest do chwili wykonania, a tam już znane są
parametry, dlaczego nie wykonać parsowania po podstawieniu *niezbędnych*
wartości? że nie wspomnę o tym co chciałem uzyskać, skoro i tak pl/sql
alokuje całą szerokość zadeklarowanego varchar2 i nie musiałby
realokować pamięci jeśli wynik operacji dalej mieściłby się w niej.
no ale to takie rozmyślania na marginesie. jeśli chodzi o szkolenie to
było zorganizowane przez comarch, ale zwracam uwagę nie tyle na firmę co
na trenera.
mam jeszcze jedną zagwozdkę. na szkoleniu był przykład, w którym w pętli
wykonywane było execute immediate 'update emp set deptno = :1 where
empno = :2' using x, y. chciałem przerobić trochę kod, tak aby w pętli
"zbierać" wartości empno dla których ma nastąpić update i doprowadzić do
wykonania pojedynczego update po pętli. czyli:
execute immediate
'update emp set deptno = :1 where empno in (:2)' using x, ids;
ids to varchar2. czy można osiągnąć coś takiego? bo niby komunikat błędu
jest dla mnie jasny (ORA-01722 invalid number) ale może wystarczy
zmienić coś w kodzie aby jednak zadziałało (bo jeśli ids składa się
tylko z jednego id to działa).. im więcej eksperymentowania tym lepiej.
pełny kod jest tutaj:
http://geos2005.republika.pl/budowanie_listy.sql
pozdrawiam,
geos
Sławomir Szyszło
>ogólnie na szkoleniach pytam jak najęty, ale tak się złożyło, że
>poruszane przykłady i zagadnienia w kontekście execute immediate nie
>podsunęły mi takiego pytania. dopiero podczas przeglądania notatek
>dostrzegłem pewne podobieństwa z np. funkcją eval z javascript i
>zacząłem się zastanawiać jak "głęboko" zaimplementowana jest
>funkcjonalność execute immediate. do układanki brakowało mi informacji,
>że podstawienie wartości do zmiennych jest wykonywane po parsowaniu.
>
>dalej jednak zastanawia mnie czy teoretycznie implementacja w przypadku
>execute immediate nie mogłaby być bardziej elastyczna: skoro i tak
>parsowanie odroczone jest do chwili wykonania, a tam już znane są
>parametry, dlaczego nie wykonać parsowania po podstawieniu *niezbędnych*
>wartości? że nie wspomnę o tym co chciałem uzyskać, skoro i tak pl/sql
>alokuje całą szerokość zadeklarowanego varchar2 i nie musiałby
>realokować pamięci jeśli wynik operacji dalej mieściłby się w niej.
Nie, parsowanie jest wykonywane najpierw. Podstawienie wartości do zmiennych
jest w trakcie fazy wykonania. Jeśli używasz tego samego, statycznego zapytania,
to parsowanie jest wykonywane raz, potem wykonywane jest wielokrotnie z różnymi
wartościami zmiennych.
>
>no ale to takie rozmyślania na marginesie. jeśli chodzi o szkolenie to
>było zorganizowane przez comarch, ale zwracam uwagę nie tyle na firmę co
>na trenera.
>
>mam jeszcze jedną zagwozdkę. na szkoleniu był przykład, w którym w pętli
>wykonywane było execute immediate 'update emp set deptno = :1 where
>empno = :2' using x, y. chciałem przerobić trochę kod, tak aby w pętli
>"zbierać" wartości empno dla których ma nastąpić update i doprowadzić do
>wykonania pojedynczego update po pętli. czyli:
>
>execute immediate
> 'update emp set deptno = :1 where empno in (:2)' using x, ids;
Dobrze kombinujesz, że powinien być jeden update. Generalnie to nie wiem po co w
ogóle tu dynamiczny kod, skoro można to zrobić:
UPDATE emp
SET deptno = n
WHERE deptno = o;
I skutek będzie ten sam.
No ale jeśli już ktoś chce budować tabelkę to tworzy się typ tablicowy:
TYPE t_tab IS TABLE OF emp.deptno%type INDEX BY PLS_INTEGER;
tab_identyfikatory t_tab;
BEGIN
SELECT e.empno BULK COLLECT INTO tab_identyfikatory
FROM emp e
WHERE name like 'KOWAL%';
FORALL k IN tab_identyfikatory.FIRST .. tab_identyfikatory.LAST
UPDATE emp
SET deptno = 10
WHERE empno = tab_identyfikatory(k);
tab_identyfikatory.DELETE;
commit;
END;
http://psoug.org/reference/array_processing.html
Można też sobie utworzyć typ tablicowy w bazie (CREATE TYPE), uzupełniać zmienną
tego typu i użyć podzapytania:
UPDATE cos
SET a=b
WHERE id IN (select column_value from table(cast (tab_wyniki as
moj_typ_obiektowy))
);
>ids to varchar2. czy można osiągnąć coś takiego? bo niby komunikat błędu
>jest dla mnie jasny (ORA-01722 invalid number) ale może wystarczy
>zmienić coś w kodzie aby jednak zadziałało (bo jeśli ids składa się
>tylko z jednego id to działa).. im więcej eksperymentowania tym lepiej.
>pełny kod jest tutaj:
>
>http://geos2005.republika.pl/budowanie_listy.sql
To jest taki przykład "edukacyjny", nie jest on zbyt szczęśliwy, ale pewnie
dlatego, że nie jest to jakiś zaawansowany poziom.
Sławomir Szyszło
wklepał(-a):
>Nie, parsowanie jest wykonywane najpierw. Podstawienie wartości do zmiennych
>jest w trakcie fazy wykonania. Jeśli używasz tego samego, statycznego zapytania,
>to parsowanie jest wykonywane raz, potem wykonywane jest wielokrotnie z różnymi
>wartościami zmiennych.
Miało być:
Jeśli używasz tego samego, statycznego zapytania, to parsowanie jest wykonywane
raz, potem to zapytanie wykonywane jest wielokrotnie z różnymi wartościami
zmiennych.
geos
> Nie, parsowanie jest wykonywane najpierw. Podstawienie wartości do zmiennych
> jest w trakcie fazy wykonania. Jeśli używasz tego samego, statycznego zapytania,
> wartościami zmiennych.
statycznie tak, ale miałem na myśli dynamiczny z execute immediate.
wtedy parsowanie polecenia następuje dopiero w chwili uruchomienia?
wnioskuję tak też pośrednio np. po zachowaniu poniższego kodu:
create or replace procedure x
is
y emp.empno%type;
begin
execute immediate
'select empno into y from t where empno = :2' using 7345;
end;
procedura się kompiluje ale samo zapytanie jest parsowane dopiero przy
próbie uruchomienia, podczas której wyrzuca błąd parsowania: tabela nie
istnieje. może źle się wyraziłem wcześniej ale chodziło mi o execute
immediate, nie o statyczny. i tak sobie dywaguję, że w sumie gdyby było:
execute immediate
'select empno into y from :1 where empno = :2' using 'emp', 7345;
to dla zmodyfikowanego parsera, który podstawiłby sobie tylko :1 nie
byłoby różnicy. zapytanie wylądowałoby z obszarze współdzielonym, a
gdyby ktoś inny chciał je wywołać to "sprytny parser" po ponownym
podstawieniu :1 wiedziałby (po haszu), że już takie tam ma. no ale tak
jak wspomniałem to są wyłącznie rozmyślania, bo jaki koń jest każdy widzi :)
> Dobrze kombinujesz, że powinien być jeden update. Generalnie to nie wiem po co w
> ogóle tu dynamiczny kod, skoro można to zrobić:
>
> UPDATE emp
> SET deptno = n
> WHERE deptno = o;
>
> I skutek będzie ten sam.
wiem :) ale w ramach eksperymentu na materiałach szkoleniowych robię
różne rzeczy.
> No ale jeśli już ktoś chce budować tabelkę to tworzy się typ tablicowy:
było na szkoleniu, jeszcze nie doszedłem w notatkach do tego. super,
dzięki za wskazówkę, prawdopodobnie jeszcze do tego wrócę.
>> http://geos2005.republika.pl/budowanie_listy.sql
>
> To jest taki przykład "edukacyjny", nie jest on zbyt szczęśliwy, ale pewnie
> dlatego, że nie jest to jakiś zaawansowany poziom.
zgadza się, to są raczej podstawy ale od czegoś trzeba zacząć. bez
dobrych fundamentów daleko się nie zajdzie. interesują mnie też dobre
praktyki ale książki o tym na razie nie znalazłem.
pozdrawiam,
geos
Sławomir Szyszło
>statycznie tak, ale miałem na myśli dynamiczny z execute immediate.
>wtedy parsowanie polecenia następuje dopiero w chwili uruchomienia?
>wnioskuję tak też pośrednio np. po zachowaniu poniższego kodu:
>
>create or replace procedure x
>is
> y emp.empno%type;
>begin
> execute immediate
> 'select empno into y from t where empno = :2' using 7345;
>end;
Ale sam proces przetwarzania zapytania jest identyczny jak dla zapytania
statycznego. Jest parsowanie, wykonanie, pobranie (w uproszczeniu).
Tylko, że parsowanie odbywa się z chwilą wykonania polecenia, a nie przy
kompilacji. Dlatego dynamiczny kod jest podatny na błędy - dopóki nie
uruchomisz, to nie wiesz czy zapytanie poprawne. To jest też przyczyna gorszej
wydajności zapytań dynamicznych - za każdym razem trzeba je parsować.
>zgadza się, to są raczej podstawy ale od czegoś trzeba zacząć. bez
>dobrych fundamentów daleko się nie zajdzie. interesują mnie też dobre
>praktyki ale książki o tym na razie nie znalazłem.
Po polsku trudno znaleźć, po angielsku niezmiennie polecam:
Thomas Kyte - Effective Oracle by Design
oraz
Thomas Kyte - Expert Oracle Database Architecture.