Source

sandbox / oracle-plsql /

Filename Size Date modified Message
..
java-pl-sql-jdbc
java-pl-sql-spring
10.6 KB
NOTES
=====
* PL/SQL is a stored procedure language specific to Oracle!

== Setup Java Project ==
OracleXE jdbc jar: 
  C:\apps\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib
JDBC driver download:
  http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

== Doc and References ==
More doc here on Oracle Database Programming:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/toc.htm

References:
www.oracle.com/technology/tech/pl_sql
tahiti.oracle.com

Quick start:
DESC DUAL
SELECT sysdate FROM dual

== Some random PL/SQL Notes ==

10:23 AM 06/06/2011
==================
Chapter2:
CREATE TABLE pay_change_log
(
id NUMBER,
old_amount NUMBER(9,2),
new_amount NUMBER(9,2),
date_changed DATE,
changed_by VARCHAR2(30)
);

CREATE OR REPLACE TRIGGER pay_change
  AFTER UPDATE OF pay_amount
  ON employee
  FOR EACH ROW
DECLARE
  chdate DATE;
  chuser VARCHAR2(30);
BEGIN
END;

NOTE: I get "Warning: Trigger created with compilation errors.", try
SHOW ERRORS

* This means the BEGIN can NOT have empty statement. Try adding a "NULL;" statement to the grigger after BEGIN.
CREATE OR REPLACE TRIGGER pay_change
  AFTER UPDATE OF pay_amount
  ON employee
  FOR EACH ROW
DECLARE
  chdate DATE;
  chuser VARCHAR2(30);
BEGIN
NULL;
END;

Verify:
select table_name, trigger_name from user_triggers;

select * from user_triggers;

DESC user_trigger;

--
CREATE OR REPLACE TRIGGER pay_change
  AFTER UPDATE OF pay_amount
  ON employee
  FOR EACH ROW
DECLARE
  chdate DATE;
  chuser VARCHAR2(30);
BEGIN
  chdate := SYSDATE;
  chuser := USER;
  
  INSERT INTO pay_change_log
  VALUES (:old.id, :old.pay_amount, :new.pay_amount, chdate, chuser);
END;

--
select id, pay_amount from employee where rownum < 5;
select * from employee where id = 6434;

update employee
  set pay_amount = pay_amount * 1.1
  where id = 6434;
  
select * from pay_change_log;

--
CREATE OR REPLACE TRIGGER pay_change
  AFTER INSERT OR UPDATE OF pay_amount
  ON employee
  FOR EACH ROW
DECLARE
  chdate DATE;
  chuser VARCHAR2(30);
BEGIN
  chdate := SYSDATE;
  chuser := USER;
  
  IF INSERTING THEN
	INSERT INTO pay_change_log
	VALUES (:old.id, NULL, :new.pay_amount, chdate, chuser);
  ELSE
    INSERT INTO pay_change_log
	VALUES (:old.id, :old.pay_amount, :new.pay_amount, chdate, chuser);
  END IF;
END;

--
CREATE OR REPLACE TRIGGER minimum_hourly_wage
  BEFORE INSERT OR UPDATE OF pay_amount
  ON employee
  FOR EACH ROW
  WHEN (new.pay_type_code = 'H')
DECLARE
  min_wage NUMBER(9,2);
BEGIN
  SELECT minimum_wage INTO min_wage
    FROM pay_type WHERE pay_type_code = 'H';
  IF :new.pay_amount < min_wage THEN
	/* :new.pay_amount := min_wage; */
	raise_application_error(-20002, 'Hourly pay must meet or exeed minimum wage, ' || min_wage);
  END IF;
END;

--
CREATE SEQUENCE person_id START WITH 10000;
CREATE OR REPLACE TRIGGER populate_id
  BEFORE INSERT
  ON person
  FOR EACH ROW
DECLARE
BEGIN
  SELECT person_id.NEXTVAL INTO :new.id FROM dual;
END;

--
set LONG 4000;

select trigger_body from user_triggers where trigger_name = 'MINIMUM_HOURLY_WAGE';

-- 
select * from user_sequences;
select * from all_sequences;

-- 2LABS #1
select * from pay_change_log;
select * from employee where STORE_NUMBER=4;
// employee.id = 9926 // store manager
update employee
  set pay_amount = pay_amount * 1.1
  where id = 9926;
 
-- 2LABS #2
// employee.id = 7319 // hourly worker.
-- Valid person id, invalid pay:
update employee
  set pay_amount = pay_amount * 1.1
  where id = 7319;
/

-- Invalid person id, valid pay:
INSERT INTO employee (id, pay_type_code, pay_amount)
       VALUES (99999, 'H', 5.50)
/

-- Invalid person id, invalid pay:
INSERT INTO employee (id, pay_type_code, pay_amount)
       VALUES (99999, 'H', 4.50)
/
  
select * from pay_type;

select * from pay_change_log;

8:36 AM 06/07/2011
==================
-- Anonymous block can allow EXCEPTION block!
     DECLARE 
	 ...
	 BEGIN
	 END;

-- size of literal number?
declare
  my_num NUMBER;
begin
 my_num := 9876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210987654321098765432109876543210;
end;

-- Literal single quote is double quote (''): I''am a programmer.
--   or using the q'Xi am a stringX'
--   or using the q'{i am a string}'
-- Literal joining string is ||

-- Oracle reserved keywords
--   http://www.cs.umbc.edu/portal/help/oracle8/server.815/a42525/apb.htm

-- 3LABS#1
DECLARE
	employee_title employee.title%TYPE;
BEGIN
	employee_title := 'Sales Representative';
	-- employee_title := 'Sales Rep';
END;

-- 3LABS#2
desc employee;
desc person;

declare
  id employee.id%type;
  store_number employee.store_number%type;
  pay_type_code employee.pay_type_code%type;
  pay_amount employee.pay_amount%type;
  title employee.title%type;
  supervisor_id employee.supervisor_id%type;
  first_name person.firstname%type;
begin
  id := 9999;
  store_number := 123;
  pay_type_code := 'S';
  pay_amount := 200000.00;
  title := 'Developer';
  supervisor_id := 9998;
  first_name := 'John';
end;

-- 3LABS#3
declare
  id employee.id%type;
  store_number employee.store_number%type;
  pay_type_code employee.pay_type_code%type;
  pay_amount employee.pay_amount%type;
  title employee.title%type;
  supervisor_id employee.supervisor_id%type;
  first_name person.firstname%type;
begin
  select title, store_number, firstname
  into title, store_number, first_name
  from employee
  inner join person on employee.id = person.id
  where employee.id = 7881;
end;

-- LABS Notes:
--   People who are NOT employee: 6397, 6403, 6404, 6406
--   Functions to use: NUMTODSINTERVAL()

-- 4LABS#1
CREATE OR REPLACE TRIGGER employee_raises_limit
	AFTER UPDATE OF pay_amount
ON employee
	FOR EACH ROW
DECLARE
	pay_type_code employee.pay_type_code%type;
BEGIN
	pay_type_code := :old.pay_type_code;
	CASE pay_type_code
		WHEN 'T' THEN
			IF (:new.pay_amount - :old.pay_amount) > 20.00 THEN
				raise_application_error(-20001, 'Temporary employee can not increase pay more than 20.00');
			END IF;
		WHEN 'H' THEN
			IF (:new.pay_amount - :old.pay_amount) > 20.00 THEN
				raise_application_error(-20001, 'Hourly employee can not increase pay more than 20.00');
			END IF;
		WHEN 'S' THEN
			IF (:new.pay_amount - :old.pay_amount) > 10000.00 THEN
				raise_application_error(-20001, 'Salary employee can not increase pay more than 10000.00');
			END IF;
		WHEN 'C' THEN
			IF (:new.pay_amount - :old.pay_amount) > 5000.00 THEN
				raise_application_error(-20001, 'Salary employee can not increase pay more than 5000.00');
			END IF;
	END CASE;
END;

select * from employee where store_number = 7
-- manager id: 6517
update employee set pay_amount = pay_amount * 1.5 where id = 6517

-- 4LABS#2
CREATE TABLE emp_history
(
id NUMBER,
start_date DATE,
end_date DATE,
ending_salary NUMBER(9,2),
pay_type_code CHAR(1),
length_of_employment INTERVAL DAY TO SECOND
);

CREATE OR REPLACE TRIGGER emp_history_trigger
	AFTER INSERT OR DELETE
ON employee
	FOR EACH ROW
DECLARE
	employee_count NUMBER;
BEGIN
	IF INSERTING THEN
		INSERT INTO emp_history VALUES (:new.id, SYSDATE, NULL, NULL, :new.pay_type_code, NULL);		
	ELSIF DELETING THEN
		/* Check to see if employee exists or not. */
		SELECT COUNT(*) INTO employee_count FROM emp_history WHERE id = :old.id;
		IF employee_count > 0 THEN
			UPDATE emp_history SET
				end_date = SYSDATE, 
				ending_salary = :old.pay_amount,
				length_of_employment = NUMTODSINTERVAL(SYSDATE - start_date, 'day')
			WHERE id = :old.id;
		ELSE
			INSERT INTO emp_history VALUES (:new.id, SYSDATE, SYSDATE, :old.pay_amount, :old.pay_type_code, NULL);			
		END IF;
	END IF;
END;
-- 
insert into employee values
(6397, 1, 'T', 50000.00, 'Developer', NULL);
delete employee where id=6397;
select * from emp_history;

-- 4LABS#5
delete employee where id=6403;


8:33 AM 06/08/2011
==================
* Exeption Handling *

-- Select Join Using ID
SELECT COUNT(ID) FROM employee INNER JOIN person USING (ID)

-- You can use PRINT statement in code block!
PRINT 'Hello'

-- Get Current Error, YOU CAN NOT DO THIS! Because statements are modifying tables.
-- You have to use it in procedure code.
select SQLCODE from dual
select SQLERRM from dual
select SQLERRM(-2714) from dual
-- Do it like this.
DECLARE
	err_msg VARCHAR(400)
BEGIN
	err_msg := SQLERRM(-2714);
END;

* Colection Types *
-- creating collection type
declare
type record_cost_of_living is record (
  city person.city%type,
  state person.city%type,
  cost_of_index number(2,2)
);
type map_cost_of_living is table of record_cost_of_living index by pls_integer;
cost_of_living map_cost_of_living;
begin
cost_of_living(1).city := 'Oviedo';
cost_of_living(1).state := 'FL';
cost_of_living(1).cost_of_index  := .06;
end;

9:44 AM 06/09/2011
==================
Java custom classes that uses Oracle "objects" with JDBC
http://www.lorentzcenter.nl/awcourse/oracle/java.920/a96654/oraoot.htm#1068983
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm

== Bulk Operations ==

== Oracle Packages - Oracle DBMS (database management system?) ==
-- DESC DBMS_UTILITY;
SELECT DBMS_UTILITY.GET_TIME FROM DUAL;

-- DIR/FIlE (UTL_FILE)
select * from all_directories

8:30 AM 06/10/2011
==================
-- Share lock? You are looking at the record and other can see it. You can't get an exclusive lock until all other
-- free their share lock.

-- Questions
-- Does StoreProc can not return a value in PL/SQL?
-- Does Always return a value in PL/SQL?
-- Does PL/SQL support any Unit Testing?

-- Create a trigger with error
CREATE OR REPLACE TRIGGER populate_id
  BEFORE INSERT 
  ON person
  FOR EACH ROW
DECLARE
BEGIN
END;
/

-- This will show trigger that is compiled, but has errors.
/* this will not show status! */
select * from user_triggers;

/* Notice that trigger name MUST be captialize! */
select status from user_objects where object_name = 'POPULATE_ID';
show errors trigger POPULATE_ID;

-- If you want to create DB package using Java, you need a static method.
public class test {
	public static x(String name) {
		return "Hello " + name;
	}
}

-- Load Java Class, note that it use the java class name as namespace.
CREATE OR REPLACE JAVA CLASS 
	USING BFILE(dir, 'test.class')
	
-- Create function with java test.x
CREATE OR REPLACE FUNCTION javatest (s IN VARCHAR2)
	RETURN VARCHAR2 AS LANAGUAGE JAVA
		NAME 'test.x(java.lang.String) return java.lang.String';

-- YOU MUST COMPILE JAVA PACKAGE USING ORACLE's JDK!!!  ???

* Reloading OracleServiceXE on Windows NT services *
C:> net stop OracleServiceXE
C:> net start OracleServiceXE

* Working with BLOB *
=====================
select * from all_directories;
create diretory tempfile_dir as '/tmp';
grant read,write on tempfile_dir to public;


-- SGA system global area

-- Exploring Oracle DB. See PUBLIC Synonyms in SQLDeveloper
   Eg: 
   select * from v$session;