1.1. fejezet, Oracle praktikák

ALTER package dbname.packagename compile body;

CREATE OR REPLACE PACKAGE xSzamla AS
 PROCEDURE InsSzFej(aVNev varchar2,aSzFejID OUT NUMBER);
 PROCEDURE DelSzTet(aSzTetID NUMBER);
 FUNCTION getNextSzTkID RETURN NUMBER;
 PROCEDURE TMasol(ASzFejIDF NUMBER,ASzFejIDC NUMBER, CDone IN OUT NUMBER, PMESSAGE OUT VarChar2);
END xSzamla;

CREATE OR REPLACE PACKAGE BODY xSzamla AS
 PROCEDURE InsSzFej(aVNev varchar2,aSzFejID OUT NUMBER) IS
 BEGIN
   SELECT xsqHeadID.nextval INTO aSzFejID FROM dual; --Trigger elvégzi ezt, viszont nem adja vissza az értéket. Ora8-ason ez már megoldott
   INSERT INTO xSzFej (SzFejID,VNev) VALUES(aSzFejID,aVNev);
 END;
 PROCEDURE DelSzTetel(aSzTetelID NUMBER) IS
 BEGIN
   DELETE xSzTetel WHERE SzTetelID=aSzTetelID;
 END;
 FUNCTION getNextSzTkID RETURN NUMBER IS
  val NUMBER;
 BEGIN
   SELECT xsqSzTkID.nextval INTO val FROM dual;
   RETURN val;
 END;
 PROCEDURE TMasol(ASzFejIDF NUMBER,ASzFejIDC NUMBER, CDone IN OUT NUMBER, PMESSAGE OUT VarChar2) IS
  cursor c1 IS SELECT * FROM xSzTetel WHERE SzFejID = ASzFejIDF;
  wc1 c1%rowtype;
 BEGIN
  CDone := 0;
  OPEN c1;
  loop
    fetch c1 INTO wc1;
    exit WHEN c1%notfound;
    IF wc1.TOssz>0 THEN
      INSERT INTO xSzTetel VALUES (xsqRecordID.nextval,wc1.TJCim,wc1.TOssz,ASzFejIDC);
      CDone:=CDone+1;
    END IF;
  END loop;
  close c1;
  exception
    WHEN others THEN
    IF c1%isopen THEN
     close c1;      
    END IF;
    pmessage:=sqlerrm;
 END;
END xSzamla;

CREATE OR REPLACE PACKAGE REF_CURSOR_TEST IS
	TYPE T_ACCOUNTS_CURSOR IS REF CURSOR;
	PROCEDURE GET_ACCOUNTS_PROCEDURE (P_ACCOUNTS OUT T_ACCOUNTS_CURSOR);
END REF_CURSOR_TEST;
/
CREATE OR REPLACE PACKAGE BODY REF_CURSOR_TEST IS
	PROCEDURE GET_ACCOUNTS_PROCEDURE (
			P_ACCOUNTS OUT T_ACCOUNTS_CURSOR
	) AS
	BEGIN
	OPEN P_ACCOUNTS FOR 
			SELECT
				Account_ID AS Id,
				Account_FirstName AS FirstName,
				Account_LastName AS LastName,
				Account_Email AS EmailAddress
			FROM Accounts
			ORDER BY Account_ID;
	END GET_ACCOUNTS_PROCEDURE;
END REF_CURSOR_TEST;

SELECT
to_char
(
trunc(
to_date('2001.02.07. PM 6:25','YYYY.MM.DD PM HH:MI')
)
,'YYYY.MM.DD HH24:MI:SS'
) FROM dual;

SET serveroutput ON;
SET linesize 2000;
 
DECLARE x varchar2(200);
BEGIN
  x:='Put out a line...';
  dbms_output.put_line(x);
END;
SHOW err;

DECLARE id NUMBER;
BEGIN
 id:=NLS_CHARSET_ID('CHAR_CS');
 dbms_output.put_line(NLS_CHARSET_NAME(id));
END;

sqlplus -S >temp <<EOF
dbname/passwd@servername
SET feed off;
SET serveroutput ON;
DECLARE w NUMBER;
BEGIN
  SELECT COUNT(*) INTO w FROM notes;
  dbms_output.put_line(w);
END;
/
EOF

DECLARE ufile UTL_FILE.FILE_TYPE;
wPath varchar2(256);
wFName varchar2(256);
BEGIN
  wPath:='/usr/var/files';
  wFName:='temp';
  ufile:=UTL_FILE.FOPEN('/usr/var/files','temp','a');
  UTL_FILE.PUT (ufile, 'Helló');
  UTL_FILE.NEW_LINE (ufile,1);
  UTL_FILE.FCLOSE (ufile);
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
  DBMS_OUTPUT.PUT_LINE('UTL_INVALID_PATH');
WHEN UTL_FILE.INVALID_MODE THEN
  DBMS_OUTPUT.PUT_LINE('UTL_INVALID_MODE');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
  DBMS_OUTPUT.PUT_LINE('UTL_INVALID_FILEHANDLE');
WHEN UTL_FILE.INVALID_OPERATION THEN
  DBMS_OUTPUT.PUT_LINE('UTL_INVALID_OPERATION');
WHEN UTL_FILE.READ_ERROR THEN
  DBMS_OUTPUT.PUT_LINE('UTL_READ_ERROR');
WHEN UTL_FILE.WRITE_ERROR THEN
  DBMS_OUTPUT.PUT_LINE('UTL_WRITE_ERROR');
WHEN UTL_FILE.INTERNAL_ERROR	THEN
  DBMS_OUTPUT.PUT_LINE('UTL_INTERNAL_ERROR');
END;

SET serveroutput ON;
DECLARE s1 CONSTANT NUMBER := 5000;
 cursor c1 IS SELECT * FROM notes;
BEGIN
  FOR nrec IN c1 loop
    dbms_output.put_line(nrec.value);
  END loop;
END;
/

ALTER TABLE notes ADD CONSTRAINT value_len CHECK (LENGTH(VALUE)>1);
ALTER TABLE notes MODIFY CONSTRAINT value_len disable; --enable
ALTER TABLE notes DROP CONSTRAINT value_len;

CREATE TABLE xSzFej(
 SzFejID NUMBER(10) PRIMARY KEY,
 VNev varchar2(30) NOT NULL,
 VDatum DATE DEFAULT SYSDATE
);
 
CREATE TABLE xSzTukor(
 SzTukorID NUMBER(10) PRIMARY KEY,
 SzTukorMegnev varchar2(20),
 SzAdoSz NUMBER(2) DEFAULT 0
);
 
CREATE TABLE xSzTetel(
 SzTetelID NUMBER(10) PRIMARY KEY,
 TJCim NUMBER(10) REFERENCES xSzTukor(SzTukorID) NOT NULL,
 TOssz NUMBER(8) DEFAULT 0,
 SzFejID NUMBER(10) CONSTRAINT fk_SzFejID REFERENCES xSzFej(SzFejID) ON DELETE CASCADE
);
 
CREATE INDEX idx_jogc ON xSzTetel(SzFejID,TJCim);
CREATE INDEX idx_SzTukor ON xSzTukor(SzTukorMegnev);

CREATE SEQUENCE xsqHeadID;

CREATE OR REPLACE TRIGGER before_xSzFej_insert
 BEFORE
 INSERT
 ON xSzFej
	FOR EACH ROW
	WHEN (NEW.SzFejID IS NULL)
DECLARE nxid NUMBER;
BEGIN
  SELECT xsqHeadID.nextVal INTO nxid FROM dual;
  :NEW.SzFejID:=nxid;
END;

DECLARE
  TYPE nested_type IS TABLE OF VARCHAR2(30);
  TYPE varray_type IS VARRAY(5) OF INTEGER;
  TYPE assoc_array_num_type
  IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  TYPE assoc_array_str_type
  IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
  TYPE assoc_array_str_type2
  IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
  v1 nested_type;
  v2 varray_type;
  v3 assoc_array_num_type;
  v4 assoc_array_str_type;
  v5 assoc_array_str_type2;
BEGIN
  -- an arbitrary number of strings can be inserted v1
  v1 := nested_type('Shipping','Sales','Finance','Payroll');
  v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
  v3(99) := 10; -- Just start assigning to elements
  v3(7) := 100; -- Subscripts can be any integer values
  v4(42) := 'Smith'; -- Just start assigning to elements
  v4(54) := 'Jones'; -- Subscripts can be any integer values
  v5('Canada') := 'North America';
  -- Just start assigning to elements
  v5('Greece') := 'Europe';
  -- Subscripts can be string values
END;
/

DECLARE
  TYPE DeptRecTyp IS RECORD (
  deptid NUMBER(4) NOT NULL := 99,
  dname departments.department_name%TYPE,
  loc departments.location_id%TYPE,
  region regions%ROWTYPE );
  dept_rec DeptRecTyp;
BEGIN
  dept_rec.dname := 'PURCHASING';
END;
/

SELECT * FROM all_views
SELECT * FROM all_tab_columns
SELECT * FROM all_db_links
SELECT * FROM all_objects
SELECT * FROM dba_tablespaces
SELECT * FROM v$tablespace
SELECT * FROM user_tablespaces
SELECT * FROM all_constraints WHERE TABLE_NAME = 'MCOMMSISDN'