REM dynamicznyDML.sql
REM Rozdział 12., Scott Urman - Oracle9i Programowanie w języku PL/SQL
REM Ta procedura demonstruje pakiet DBMS_SQL.
CREATE OR REPLACE PROCEDURE UaktualnijGrupy(
/* Procedura wykorzystuje pakiet DBMS_SQL do aktualizacji tabeli grupy, ustawienia
* zaliczeń dla wszystkich grup na określonym wydziale
* na daną wartość.
*/
p_Wydzial IN grupy.Wydzial%TYPE,
p_NoweZaliczenia IN grupy.liczba_zaliczen%TYPE,
p_UaktualnionoWierszy OUT INTEGER) AS
z_IDKursora INTEGER;
z_InstrUpdate VARCHAR2(100);
BEGIN
-- Otwarcie kursora do przetwarzania.
z_IDKursora := DBMS_SQL.OPEN_CURSOR;
-- Określenie ciągu instrukcji SQL.
z_InstrUpdate :=
'UPDATE grupy
SET liczba_zaliczen = :lz
WHERE Wydzial = :wydz';
-- Analiza instrukcji.
DBMS_SQL.PARSE(z_IDKursora, z_InstrUpdate, DBMS_SQL.NATIVE);
-- Powiązanie parametru p_NoweZaliczenia z symbolem zastępczym :lz. Ta przeciążona
-- wersja zmiennej BIND_VARIABLE powiąże parametr p_NoweZaliczenia jako daną typu
-- NUMBER, ponieważ tak ją zadeklarowano.
DBMS_SQL.BIND_VARIABLE(z_IDKursora, ':lz', p_NoweZaliczenia);
-- Powiązanie parametru p_Wydzial do symbolu zastępczego :wydz. Ta przeciążona
-- wersja zmiennej BIND_VARIABLE powiąże parametr p_Wydzial jako daną typu
-- CHAR, ponieważ tak ją zadeklarowano.
DBMS_SQL.BIND_VARIABLE_CHAR(z_IDKursora, ':wydz', p_Wydzial);
-- Wykonanie instrukcji.
p_UaktualnionoWierszy := DBMS_SQL.EXECUTE(z_IDKursora);
-- Zmknięcie kursora.
DBMS_SQL.CLOSE_CURSOR(z_IDKursora);
EXCEPTION
WHEN OTHERS THEN
-- Zamknięcie kursora, a następnie ponowne zgłoszenie błędu.
DBMS_SQL.CLOSE_CURSOR(z_IDKursora);
RAISE;
END UaktualnijGrupy;
/
REM execImmediate.sql
REM Rozdział 12., Scott Urman - Oracle9i Programowanie w języku PL/SQL
REM Ten blok demonstruje kilka poleceń EXECUTE IMMEDIATE
set serveroutput on
DECLARE
z_CiagSQL VARCHAR2(200);
z_BlokPLSQL VARCHAR2(200);
BEGIN
-- Najpierw utworzono tabelę tymczasową, wykorzystując literał. Proszę zauważyć,
-- że w ciągu znaków nie ma kończącego znaku średnika.
EXECUTE IMMEDIATE
'CREATE TABLE tabela_wykonania (col1 VARCHAR(10))';
-- Wstawienie kilku wierszy za pomocą ciągu. Ponownie ciąg nie zawiera
-- kończącego znaku średnika.
FOR z_Licznik IN 1..10 LOOP
z_CiagSQL :=
'INSERT INTO tabela_wykonania
VALUES (''Wiersz ' || z_Licznik || ''')';
EXECUTE IMMEDIATE z_CiagSQL;
END LOOP;
-- Wyświetlenie zawartości tabeli za pomocą anonimowego bloku
-- PL/SQL. Wstawia się cały blok do ciągu znaków
-- (włącznie ze średnikiem).
z_BlokPLSQL :=
'BEGIN
FOR z_Rek IN (SELECT * FROM tabela_wykonania) LOOP
DBMS_OUTPUT.PUT_LINE(z_Rek.col1);
END LOOP;
END;';
-- A teraz wykonuje anonimowy blok.
EXECUTE IMMEDIATE z_BlokPLSQL;
-- Usunięcie tabeli.
EXECUTE IMMEDIATE 'DROP TABLE tabela_wykonania';
END;
/
REM WbudowDynSQL.sql
REM Rozdział 12., Scott Urman - Oracle9i Programowanie w języku PL/SQL
REM Ten skrypt ilustruje zastosowanie wbudowanego dynamicznego SQL do
REM przetwarzania zapytań.
CREATE OR REPLACE PACKAGE WbudowDynSQL AS
TYPE t_KurOdw IS REF CURSOR;
-- Pobranie danych z tabeli studenci za pomocą podanej klauzuli WHERE
-- i zwrócenie otwartej zmiennej kursora.
FUNCTION StudenciZapytanie(p_KlauzulaWhere IN VARCHAR2)
RETURN t_KurOdw;
-- Pobranie danych z tabeli studenci na podstawie podanej specjlaności
-- i zwrócenie otwartej zmiennej kursora.
FUNCTION StudenciZapytanie2(p_Specjalnosc IN VARCHAR2)
RETURN t_KurOdw;
END WbudowDynSQL;
/
show errors
CREATE OR REPLACE PACKAGE BODY WbudowDynSQL AS
-- Selects from studenci using the supplied WHERE clause,
-- and returns the opened cursor variable.
FUNCTION StudenciZapytanie(p_KlauzulaWhere IN VARCHAR2)
RETURN t_KurOdw IS
z_KursorWynikowy t_KurOdw;
z_InstrukcjaSQL VARCHAR2(500);
BEGIN
-- Utworzenie zapytania za pomocą dostarczonej klauzuli WHERE
z_InstrukcjaSQL := 'SELECT * FROM studenci ' || p_KlauzulaWhere;
-- Otwarcie zmiennej kursora i zwrócenie jej.
OPEN z_KursorWynikowy FOR z_InstrukcjaSQL;
RETURN z_KursorWynikowy;
END StudenciZapytanie;
-- Pobranie danych z tabeli studenci na podstawie podanej specjalności
-- i zwrócenie otwartej zmiennej kursora.
FUNCTION StudenciZapytanie2(p_Specjalnosc IN VARCHAR2)
RETURN t_KurOdw IS
z_KursorWynikowy t_KurOdw;
z_InstrukcjaSQL VARCHAR2(500);
BEGIN
z_InstrukcjaSQL := 'SELECT * FROM studenci WHERE specjalnosc = :m';
-- Otwarcie zmiennej kursora i zwrócenie jej.
OPEN z_KursorWynikowy FOR z_InstrukcjaSQL USING p_Specjalnosc;
RETURN z_KursorWynikowy;
END StudenciZapytanie2;
END WbudowDynSQL;
/
show errors
set serveroutput on format wrapped
DECLARE
z_Student studenci%ROWTYPE;
z_StudentKur WbudowDynSQL.t_KurOdw;
BEGIN
-- Wywołanie procedury StudenciZapytanie w celu otwarcia kursora dla
-- studentów z parzystymi identyfikatorami.
z_StudentKur :=
WbudowDynSQL.StudenciZapytanie('WHERE MOD(id, 2) = 0');
-- Pętla dla otwartego kursora i wyświetlenie wyników.
DBMS_OUTPUT.PUT_LINE('Parzyste identyfikatory mają następujący studenci:');
LOOP
FETCH z_StudentKur INTO z_Student;
EXIT WHEN z_StudentKur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(' ' || z_Student.id || ': ' ||
z_Student.imie || ' ' ||
z_Student.nazwisko);
END LOOP;
CLOSE z_StudentKur;
-- Wywołanie StudenciZapytanie2 w celu otwarcia kursora dla specjalności Muzyka.
z_StudentKur :=
WbudowDynSQL.StudenciZapytanie2('Muzyka');
-- Pętla dla otwartego kursora i wyświetlenie wyników.
DBMS_OUTPUT.PUT_LINE(
'Na specjalności Muzyka studiują następujący studenci:');
LOOP
FETCH z_StudentKur INTO z_Student;
EXIT WHEN z_StudentKur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(' ' || z_Student.id || ': ' ||
z_Student.imie || ' ' ||
z_Student.nazwisko);
END LOOP;
CLOSE z_StudentKur;
END;
/
-- Ten blok ilustruje zastosowanie instrukcji EXECUTE IMMEDIATE dla
-- zapytań zwracających pojedynczy wiersz.
DECLARE
z_ZapytanieSQL VARCHAR2(200);
z_Grupa grupy%ROWTYPE;
z_Opis grupy.opis%TYPE;
BEGIN
-- Najpierw pobrano dane do zmiennej.
z_ZapytanieSQL :=
'SELECT opis ' ||
' FROM grupy ' ||
' WHERE wydzial = ''EKN''' ||
' AND kurs = 203';
EXECUTE IMMEDIATE z_ZapytanieSQL
INTO z_Opis;
DBMS_OUTPUT.PUT_LINE('Pobrano ' || z_Opis);
-- Teraz pobrano dane do rekordu za pomocą zmiennej dowiązanej.
z_ZapytanieSQL :=
'SELECT * ' ||
' FROM grupy ' ||
' WHERE opis = :opis';
EXECUTE IMMEDIATE z_ZapytanieSQL
INTO z_Grupa
USING z_Opis;
DBMS_OUTPUT.PUT_LINE(
'Pobrano ' || z_Grupa.wydzial || ' ' || z_Grupa.kurs);
-- Pobranie więcej niż jednego wiersza. Spowoduje to powstanie błędu ORA-1422.
z_ZapytanieSQL := 'SELECT * FROM grupy';
EXECUTE IMMEDIATE z_ZapytanieSQL
INTO z_Grupa;
END;
/
3