Created
April 3, 2013 07:16
-
-
Save benilovj/5299071 to your computer and use it in GitHub Desktop.
Example of Oracle collection types
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- | |
-- Copyright (c) Oracle Corporation 1988, 1999. All Rights Reserved. | |
-- | |
-- NAME | |
-- demobld.sql | |
-- | |
-- DESCRIPTION | |
-- This script creates the SQL*Plus demonstration tables in the | |
-- current schema. It should be STARTed by each user wishing to | |
-- access the tables. To remove the tables use the demodrop.sql | |
-- script. | |
-- | |
-- USAGE | |
-- SQL> START demobld.sql | |
-- | |
-- | |
SET TERMOUT ON | |
PROMPT Building demonstration tables. Please wait. | |
SET TERMOUT OFF | |
CREATE TABLE BONUS | |
(ENAME VARCHAR2(10), | |
JOB VARCHAR2(9), | |
SAL NUMBER, | |
COMM NUMBER); | |
CREATE TABLE EMP | |
(EMPNO NUMBER(4) NOT NULL, | |
ENAME VARCHAR2(10), | |
JOB VARCHAR2(9), | |
MGR NUMBER(4), | |
HIREDATE DATE, | |
SAL NUMBER(7, 2), | |
COMM NUMBER(7, 2), | |
DEPTNO NUMBER(2)); | |
INSERT INTO EMP VALUES | |
(7369, 'SMITH', 'CLERK', 7902, | |
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20); | |
INSERT INTO EMP VALUES | |
(7499, 'ALLEN', 'SALESMAN', 7698, | |
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30); | |
INSERT INTO EMP VALUES | |
(7521, 'WARD', 'SALESMAN', 7698, | |
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30); | |
INSERT INTO EMP VALUES | |
(7566, 'JONES', 'MANAGER', 7839, | |
TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20); | |
INSERT INTO EMP VALUES | |
(7654, 'MARTIN', 'SALESMAN', 7698, | |
TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30); | |
INSERT INTO EMP VALUES | |
(7698, 'BLAKE', 'MANAGER', 7839, | |
TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30); | |
INSERT INTO EMP VALUES | |
(7782, 'CLARK', 'MANAGER', 7839, | |
TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10); | |
INSERT INTO EMP VALUES | |
(7788, 'SCOTT', 'ANALYST', 7566, | |
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20); | |
INSERT INTO EMP VALUES | |
(7839, 'KING', 'PRESIDENT', NULL, | |
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10); | |
INSERT INTO EMP VALUES | |
(7844, 'TURNER', 'SALESMAN', 7698, | |
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, NULL, 30); | |
INSERT INTO EMP VALUES | |
(7876, 'ADAMS', 'CLERK', 7788, | |
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20); | |
INSERT INTO EMP VALUES | |
(7900, 'JAMES', 'CLERK', 7698, | |
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30); | |
INSERT INTO EMP VALUES | |
(7902, 'FORD', 'ANALYST', 7566, | |
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20); | |
INSERT INTO EMP VALUES | |
(7934, 'MILLER', 'CLERK', 7782, | |
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10); | |
CREATE TABLE DEPT | |
(DEPTNO NUMBER(2), | |
DNAME VARCHAR2(14), | |
LOC VARCHAR2(13) ); | |
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK'); | |
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS'); | |
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO'); | |
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON'); | |
CREATE TABLE SALGRADE | |
(GRADE NUMBER, | |
LOSAL NUMBER, | |
HISAL NUMBER); | |
INSERT INTO SALGRADE VALUES (1, 700, 1200); | |
INSERT INTO SALGRADE VALUES (2, 1201, 1400); | |
INSERT INTO SALGRADE VALUES (3, 1401, 2000); | |
INSERT INTO SALGRADE VALUES (4, 2001, 3000); | |
INSERT INTO SALGRADE VALUES (5, 3001, 9999); | |
COMMIT; | |
SET TERMOUT ON | |
PROMPT Demonstration table build is complete. |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create or replace type type_emp_details is object ( | |
EMPNO NUMBER(4), | |
ENAME VARCHAR2(10), | |
JOB VARCHAR2(9), | |
MGR NUMBER(4), | |
HIREDATE DATE, | |
SAL NUMBER(7,2), | |
COMM NUMBER(7,2), | |
DEPTNO NUMBER(2)); | |
/ | |
create or replace type tab_emp_details as table of type_emp_details; | |
/ | |
create or replace procedure p_insert_employee_dtls | |
(in_emp_dtl in type_emp_details, | |
out_result out integer) | |
as | |
begin | |
out_result := -1; | |
insert into emp | |
(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) | |
values | |
(in_emp_dtl.EMPNO,in_emp_dtl.ENAME,in_emp_dtl.JOB,in_emp_dtl.MGR,in_emp_dtl.HIREDATE,in_emp_dtl.SAL,in_emp_dtl.COMM,in_emp_dtl.DEPTNO); | |
commit; | |
out_result := 0; | |
exception | |
when others then | |
out_result := -1; | |
rollback; | |
end; | |
/ | |
create or replace procedure p_insert_employee_bulk | |
(in_emp_dtl in tab_emp_details, | |
out_result out integer) | |
as | |
begin | |
out_result := -1; | |
IF in_emp_dtl.COUNT > 0 THEN | |
for i in in_emp_dtl.first..in_emp_dtl.last | |
loop | |
insert into emp | |
(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) | |
values | |
(in_emp_dtl(i).EMPNO,in_emp_dtl(i).ENAME,in_emp_dtl(i).JOB,in_emp_dtl(i).MGR,in_emp_dtl(i).HIREDATE,in_emp_dtl(i).SAL,in_emp_dtl(i).COMM,in_emp_dtl(i).DEPTNO); | |
end loop; | |
end if; | |
commit; | |
out_result := 0; | |
exception | |
when others then | |
out_result := -1; | |
rollback; | |
end; | |
/ | |
create or replace procedure p_merge_employee | |
(in_emp_dtl in tab_emp_details, | |
out_result out integer) | |
as | |
begin | |
out_result := -1; | |
IF in_emp_dtl.COUNT > 0 THEN | |
merge into emp e | |
using table (cast (in_emp_dtl as tab_emp_details)) e2 | |
on ( e.empno = e2.empno) | |
when matched then | |
update set HIREDATE = e2.hiredate | |
when not matched then | |
insert (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) | |
values | |
(e2.EMPNO,e2.ENAME,e2.JOB,e2.MGR,e2.HIREDATE,e2.SAL,e2.COMM,e2.DEPTNO); | |
end if; | |
commit; | |
out_result := 0; | |
exception | |
when others then | |
out_result := -1; | |
rollback; | |
end; | |
/ | |
create or replace procedure p_get_all_emp_dtls | |
(out_emp_dtls out tab_emp_details, | |
out_result out integer) | |
as | |
begin | |
out_result := -1; | |
select type_emp_details(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) | |
bulk collect into out_emp_dtls | |
from emp; | |
commit; | |
out_result := 0; | |
exception | |
when others then | |
out_result := -1; | |
end; | |
/ | |
create or replace procedure p_get_emp_dtls | |
(in_empno in number, | |
out_emp_dtls out type_emp_details, | |
out_result out integer) | |
as | |
begin | |
out_result := -1; | |
select type_emp_details(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) | |
into out_emp_dtls | |
from emp where empno = in_empno; | |
out_result := 0; | |
exception | |
when others then | |
out_result := -1; | |
end; | |
/ | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment