常见问题1000例Oracle(九)
_gary values(1,'qu',10000,003);
insert into employee_gary values(1,'q1',50,001);
insert into employee_gary values(2,'q2',100,002);
insert into employee_gary values(3,'q3',100,003);
delete from employee_gary;
select * from employee_gary;
select e.department_id, employee_id, last_name, min(salary)
from employee_gary e
where e.department_id = (select distinct department_id from employee_gary group by department_id);
--example--
select e.employee_id, e.last_name, e.salary
from employee_gary e,
(select department_id, min(salary) m
from employee_gary
group by department_id) d
where e.department_id = d.department_id
and e.salary = d.m;
---create package and procdure--
-----------------------
--Creating the object type
------------------------
DROP TYPE CLOANS_RPT01345_TYPE_TABLE;
DROP TYPE CLOANS_RPT01345_TYPE;
CREATE OR REPLACE TYPE CLOANS_RPT01345_TYPE AS OBJECT
(
FORACID VARCHAR2(16),
ADDRESS_LINE1 VARCHAR2(200),
ADDRESS_LINE2 VARCHAR2(200),
ADDRESS_LINE3 VARCHAR2(200),
CITY VARCHAR2(200),
STATE VARCHAR2(200),
COUNTRY VARCHAR2(200),
ZIP VARCHAR2(200),
PEG_REVIEW_DATE DATE
);
-------------------------------------------------
-- Creating the TABLE for the TYPE object
-------------------------------------------------
CREATE OR REPLACE TYPE CLOANS_RPT01345_TYPE_TABLE AS TABLE OF CLOANS_RPT01345_TYPE;
----------------------------------
--Creating the Function
-----------------------------------
CREATE OR REPLACE FUNCTION CLOANS_RPT01345_FUNC(lv_asondate IN VARCHAR2)
RETURN CLOANS_RPT01345_TYPE_TABLE
PIPELINED AS
ADDRESS_LINE1 VARCHAR2(200);
ADDRESS_LINE2 VARCHAR2(200);
ADDRESS_LINE3 VARCHAR2(200);
CITY VARCHAR2(200);
STATE VARCHAR2(200);
COUNTRY VARCHAR2(200);
ZIP VARCHAR2(200);
ENTITY_ID VARCHAR2(12);
INT_TBL_CODE_SRL_NUM VARCHAR2(3);
INT_TBL_CODE VARCHAR2(5);
PEG_REVIEW_DATE DATE;
FORACID VARCHAR2(16);
CIF_ID VARCHAR2(50);
cursor CLOANS_PRT01345_cur01 is
SELECT a.entity_id,
a.INT_TBL_CODE_SRL_NUM,
a.INT_TBL_CODE,
a.peg_review_date,
g.foracid,
g.cif_id,
ad.address_line1,
ad.address_line2,
ad.address_line3,
ad.CITY,
ad.state,
ad.country,
ad.zip
FROM tbaadm.ITC a,
tbaadm.gam g,
crmuser.address ad,
crmuser.accounts ac
WHERE a.entity_id IN
(SELECT acid FROM tbaadm.lam WHERE entity_cre_flg = 'Y')
AND g.acid = a.entity_id