http://wenku.baidu.com/view/42f3ed22bcd126fff7050b79.html
---basic table
select * from tbaadm.gam
--group by
select ACID,DEL_FLG,ACCT_NAME,count(DR_BAL_LIM) FROM tbaadm.gam group by ACID; --wrong suach as count min max avg do not toghter with three or three blow column
select ACID, count(DR_BAL_LIM) FROM tbaadm.gam group by ACID;--right
---group by
select ACID,count(DEL_FLG),count(TOT_MOD_TIMES), sum(TOT_MOD_TIMES) FROM tbaadm.gam group by ACID;
select ACID,count(DEL_FLG),count(TOT_MOD_TIMES), sum(TOT_MOD_TIMES) FROM tbaadm.gam group by FORACID;--WRONG group by must following the select field
-- having condition group by
select ACID,count(DEL_FLG),count(TOT_MOD_TIMES) as tot_mod_count, sum(TOT_MOD_TIMES) FROM tbaadm.gam having count(TOT_MOD_TIMES)>1 group by ACID;
-- ordery by
select ACID,DEl_FLG,TOT_MOD_TIMES FROM tbaadm.gam ORDER BY ACID
select ACID,DEl_FLG,TOT_MOD_TIMES FROM tbaadm.gam ORDER BY FORACID
--MIN MAX
select ACID,DEl_FLG,MIN(TOT_MOD_TIMES) MIN,TOT_MOD_TIMES FROM tbaadm.gam ORDER BY ACID --wrong must have two column
select ACID,MIN(TOT_MOD_TIMES) MIN FROM tbaadm.gam group BY ACID
select ACID,MAX(TOT_MOD_TIMES) MAX1 FROM tbaadm.gam group BY ACID
--DISTINCT
select distinct ACID,DEl_FLG,TOT_MOD_TIMES FROM tbaadm.gam ORDER BY ACID;
select ACID,distinct DEl_FLG,TOT_MOD_TIMES FROM tbaadm.gam ORDER BY ACID --wrong the distinct must be put in the first postion.
--group by must be put in front of the order by and limit
--avg(distinct|all)
select ACID,avg(TOT_MOD_TIMES) FROM tbaadm.gam group BY ACID;
select ACID,avg(distinct TOT_MOD_TIMES) FROM tbaadm.gam group BY ACID;
select ACID,avg(all TOT_MOD_TIMES) FROM tbaadm.gam group BY ACID;
--- userenv
select userenv('language') from dual;
select userenv('sessionid') from dual;
select user from dual;
--type use guide
create or replace type bartype as object(
name varchar2(20),
addr varchar2(20)
);
/
create table Bars of BarType;
-- create a procedure to farmat the string
CREATE OR REPLACE PACKAGE basp0099 AS
TYPE ArrayType IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
PROCEDURE formInputArr (inputStr IN VARCHAR2,
inputArr OUT ArrayType);
END basp0099;
CREATE OR REPLACE PACKAGE BODY basp0099 AS
-- ***********************************************************
PROCEDURE formInputArr (inputStr IN VARCHAR2,
inputArr OUT ArrayType
) AS
inpArr ArrayType;
tempStr VARCHAR2(500 char);
i NUMBER := 0;
delimPos NUMBER := 0;
BEGIN --{
-- DBMS_OUTPUT.PUT_LINE ('basp0099 : inside formInputArr');
-- DBMS_OUTPUT.PUT_LINE ('basp0099 : inputStr [' || inputStr || ']');
tempStr := inputStr;
LOOP -- {
delimPos := INSTR (tempStr, '!');
IF ((delimPos = 0) OR (delimPos IS NULL)) THEN
inpArr(i) := tempStr ;
EXIT;
ELSE
inpArr(i) := SUBSTR (tempStr, 1, delimPos - 1);
tempStr := SUBSTR (tempStr, delimPos + 1);
END IF;
i := i + 1;
END LOOP; -- }
-- for j in 0..i LOOP
-- DBMS_OUTPUT.PUT_LINE ('basp0099 : inpArr(' || j || ') [' || inpArr(j) || ']');
-- END LOOP;
inputArr := inpArr;
END formInputArr;
-- ***********************************************************
END basp0099 ;
--------------------------------------------------------------------------------
-- Function : getCountOfChar()
-- Desc : Returns the no of times a character exists in a string(string, char)
--------------------------------------------------------------------------------
FUNCTION getCountOfChar(lv_string VARCHAR2,lv_char CHAR )
RETURN NUMBER;
FUNCTION getCountOfChar(lv_string VARCHAR2,lv_char CHAR)
RETURN NUMBER AS
lv_input_length NUMBER;
lv_index NUMBER := 1;
lv_count_of_char NUMBER := 0;
BEGIN
BEGIN
lv_input_length := LENGTH(lv_string);
WHILE lv_index <= lv_input_length
LOOP
IF(SUBSTR(lv_string, lv_index, 1) = 'U') THEN
--{
lv_count_of_char := lv_count_of_char +1 ;
--}
END IF;
lv_index := lv_index + 1;
END LOOP;
END;
RETURN lv_count_of_char;
END getCountOfChar;
-----------------------------------------------------------------------------------------------------------
--- Function : eabBal()
--- Desc : Takes Acid and As On Date as input and returns
--- : Outstanding Balance of the Acid based on the input Dates
-----------------------------------------------------------------------------------------------------------
FUNCTION eabBal(lv_acid varchar2,
lv_as_on_date DATE,bankId varchar2) RETURN NUMBER is
outstanding_amount number;
BEGIN
--{
BEGIN
SELECT nvl(tran_date_bal,0)
INTO outstanding_amount
FROM tbaadm.EAB
WHERE EAB.acid = lv_acid
AND eod_date <= lv_as_on_date
AND end_eod_date >= lv_as_on_date
AND bank_id = bankId ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
outstanding_amount := 0;
END;
RETURN outstanding_amount;
--}
END eabBal;
--create a test table by gary qu
drop table test_gary1;
create table test_gary1
(
id number,
name varchar2(20),
password varchar2(20),
account number(10,2),
emailaddress varchar2(20),
introduce varchar2(50)
);
-- insert into data for table test_gary1
insert into test_gary1 values(1,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(2,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(3,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(4,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(5,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(6,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(7,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(8,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(9,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(10,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
insert into test_gary1 values(11,'gary','123','1000.00','garyqu@infosys.com','i will become a rich man!');
--inquiry record from table
select * from test_gary1;
--create oracle procedure
drop procedure gary_test1;
create or replace procedure gary_test1 (student_id in number,
student_name out varchar2
)
IS
--student_name:='kevin_xiong';
x number:=1;
Cursor cursor is select * from test_gary1;
--name varchar2(20);
i number:=0;
begin
if x>0 then
begin
DBMS_OUTPUT.put_line('holl world!');
DBMS_OUTPUT.put_line('holl baby!');
DBMS_OUTPUT.put_line('hello infosys!');
end;
end if;
for test in cursor LOOP
begin
DBMS_OUTPUT.put_line(test.id);
DBMS_OUTPUT.put_line(test.name);
DBMS_OUTPUT.put_line(test.password);
DBMS_OUTPUT.put_line(test.emailaddress);
DBMS_OUTPUT.put_line(test.introduce);
end;
end LOOP;
while i<20 LOOP
begin
DBMS_OUTPUT.put_line(i);
i:=i+1;
end;
end LOOP;
DBMS_OUTPUT.put_line(x);
end gary_test1;
----------reserch the record through the student id
create or replace procedure gary_test1 (student_id in number,
student_name out varchar2
)
IS
--student_name:='kevin_xiong';
x number:=1;
Cursor cursor is select name from test_gary1 where test_gary1.id=student_id;
--name varchar2(20);
i number:=0;
id number;
name varchar2(20);
begin
open cursor;
loop
begin
fetch cursor into gary_name
-- DBMS_OUTPUT.put_line(test.id);
DBMS_OUTPUT.put_line('teeesttt------stesrt');
-- DBMS_OUTPUT.put_line(test.password);
--DBMS_OUTPUT.put_line(test.emailaddress);
-- DBMS_OUTPUT.put_line(test.introduce);
exit when cursor%notfound;
DBMS_OUTPUT.put_line(gary_name.name);
end;
end loop;
if cursor%isopen then
close cursor;
end if;
for test in cursor LOOP
begin
DBMS_OUTPUT.put_line(test.id);
DBMS_OUTPUT.put_line(test.name);
DBMS_OUTPUT.put_line(test.password);
DBMS_OUTPUT.put_line(test.emailaddress);
DBMS_OUTPUT.put_line(test.introduce);
end;
end LOOP;
end gary_test1;
create or replace procedure gary_test1 (student_id in number,
student_name out varchar2
)
IS
--student_name:='kevin_xiong';
x number:=1;
Cursor cursor is select name from test_gary1 where test_gary1.id=student_id;
--name varchar2(20);
gary_name varchar2(20);
cur_gary cursor%rowtype;
begin
open cursor;
loop
begin
fetch cursor into cur_gary;
-- DBMS_OUTPUT.put_line(test.id);
-- DBMS_OUTPUT.put_line('teeesttt------stesrt');
-- DBMS_OUTPUT.put_line(test.password);
--DBMS_OUTPUT.put_line(test.emailaddress);
-- DBMS_OUTPUT.put_line(test.introduce);
exit when cursor%notfound;
DBMS_OUTPUT.put_line(cur_gary.name);
end;
end loop;
if cursor%isopen then
close cursor;
end if;
end gary_test1;
-- write a research procedure to search all data from test_gary1 with loop 1
create or replace procedure gary_test2 (student_id in number,
student_name out varchar2
)
IS
--student_name:='kevin_xiong';
x number:=1;
Cursor cursor is select id,name,password,account,emailaddress,introduce from test_gary1 where test_gary1.id=student_id;
--name varchar2(20);
gary_name varchar2(20);
cur_gary cursor%rowtype;
begin
open cursor;
loop
begin
fetch cursor into cur_gary;
-- DBMS_OUTPUT.put_line(test.id);
-- DBMS_OUTPUT.put_line('teeesttt------stesrt');
-- DBMS_OUTPUT.put_line(test.password);
--DBMS_OUTPUT.put_line(test.emailaddress);
-- DBMS_OUTPUT.put_line(test.introduce);
exit when cursor%notfound;
DBMS_OUTPUT.put_line(cur_gary.id);
DBMS_OUTPUT.put_line(cur_gary.name);
DBMS_OUTPUT.put_line(cur_gary.password);
DBMS_OUTPUT.put_line(cur_gary.account);
DBMS_OUTPUT.put_line(cur_gary.emailaddress);
DBMS_OUTPUT.put_line(cur_gary.introduce);
student_name:=cur_gary.name;
--DBMS_OUTPUT.put_line(student_name);
end;
end loop;
if cursor%isopen then
close cursor;
end if;
end gary_test2;
-- use for loop to resaerch data
create or replace procedure gary_test3 (student_id in number,
student_name out varchar2
)
IS
--student_name:='kevin_xiong';
x number:=1;
Cursor cursor is select * from test_gary1 where id=student_id;
--name varchar2(20);
i number:=0;
begin
if x>0 then
begin
DBMS_OUTPUT.put_line('holl world!');
DBMS_OUTPUT.put_line('holl baby!');
DBMS_OUTPUT.put_line('hello infosys!');
end;
end if;
for test in cursor LOOP
begin
DBMS_OUTPUT.put_line(test.id);
DBMS_OUTPUT.put_line(test.name);
DBMS_OUTPUT.put_line(test.password);
DBMS_OUTPUT.put_line(test.emailaddress);
DBMS_OUTPUT.put_line(test.introduce);
end;
end LOOP;
end gary_test3;
-- use while loop to resaerch data
create or replace procedure gary_test4 (student1_id in number,
student1_name out varchar2
)
IS
--student_name:='kevin_xiong';
x number:=1;
Cursor cursor1 is select * from test_gary1 where id=student1_id;
--name varchar2(20);
i number:=0;
student_id test_gary1.id%type;
student_name test_gary1.name%type;
student_password test_gary1.password%type;
student_account test_gary1.account%type;
student_emailaddress test_gary1.emailaddress%type;
student_introduce test_gary1.introduce%type;
begin
open cursor1;
fetch cursor1 into student_id,
student_name,
student_password,
student_account,
student_emailaddress,
student_introduce;
if x>0 then
begin
DBMS_OUTPUT.put_line('holl world!');
DBMS_OUTPUT.put_line('holl baby!');
DBMS_OUTPUT.put_line('hello infosys!');
end;
end if;
/*if cursor1%found then
begin
DBMS_OUTPUT.put_line('======='||'-cursor found-');
end;
end if;
if cursor1%notfound then
begin
DBMS_OUTPUT.put_line('---==----'||'-cursor does not found --');
end;
end if;*/
while cursor1%found LOOP
begin
--DBMS_OUTPUT.enable(500000);
DBMS_OUTPUT.put_line('----------');
DBMS_OUTPUT.put_line(student_id);
DBMS_OUTPUT.put_line(student_name);
DBMS_OUTPUT.put_line(student_password);
DBMS_OUTPUT.put_line(student_account);
DBMS_OUTPUT.put_line(student_emailaddress);
DBMS_OUTPUT.put_line(student_introduce);
-- DBMS_OUTPUT.put_line(i);
-- i:=i+1;
fetch cursor1 into student_id,
student_name,
student_password,
student_account,
student_emailaddress,
student_introduce;
end;
end LOOP;
end gary_test4;
--execute the procedure
--execute proc_name;
set serveroutput on;
--execute gary_test1
declare
student_name varchar2(20);
begin
gary_test1(1,student_name);
end;
--execute gary_test2
declare
student_name varchar2(20);
begin
gary_test2(2,student_name);
DBMS_OUTPUT.put_line('----studnet name-----');
DBMS_OUTPUT.put_line(student_name);
end;
--execute gary_test3
declare
student_name varchar2(20);
begin
gary_test3(3,student_name);
end;
--execute gary_test4
set serveroutput on;
declare
student_name varchar2(20);
begin
gary_test4(1,student_name);
end;
-- trigger
--create table
drop table test;
drop table test_log;
CREATE TABLE test ( t_id NUMBER(4), t_name VARCHAR2(20), t_age NUMBER(2), t_sex CHAR );
CREATE TABLE test_log ( l_user VARCHAR2(15), l_type VARCHAR2(15), l_date VARCHAR2(30) );
---create trigger
drop trigger test_trigger;
create or replace trigger test_trigger
after delete or insert or update on test
declare v_type test_log.l_type%type:='none';
begin
--insert
if INSERTING then
v_type:='insert';
--insert into test_log values('007','||||','||sysdate||');
Dbms_Output.put_line('¼Ç¼Òѳɹ¦²åÈ룬²¢¼Ç¼µ½ÈÕÖ¾');
---upate
elsif UPDATING then
v_type:='update';
--insert into test_log values('007','||v_type||','||sysdate||');
Dbms_Output.put_line('¼Ç¼Òѳɹ¦Ð޸ģ¬²¢¼Ç¼µ½ÈÕÖ¾');
--delete
elsif DELETING then
v_type:='delete';
--insert into test_log values('007',v_type,'||sysdate||');
Dbms_Output.put_line('¼Ç¼Òѳɹ¦É¾³ý£¬²¢¼Ç¼µ½ÈÕÖ¾');
end if;
insert into test_log values(user,v_type,TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end;
--execute the trigger
insert into test values(0001,'garyqu','20','f');
INSERT INTO test VALUES(101,'zhao',22,'M');
UPDATE test SET t_age = 30 WHERE t_id = 101;
DELETE test WHERE t_id = 101;
select * from test;
select * from test_log;
------trigger example 2 summary ---
drop table emp;
create table emp(empno varchar2(20),deptno varchar2(20),sal number(20,2));
drop table dept_sal;
create table dept_sal AS
select deptno,count(empno) AS total_emp,sum(sal) AS total_sal from emp group by deptno;
DESC dept_sal;
--create trigger--
drop trigger emp_info;
create or replace trigger emp_info
after insert or update or delete on emp
declare Cursor cur_emp is
select deptno,count(empno) AS total_emp,sum(sal) AS total_sal from emp group by deptno;
begin
delete dept_sal;
for v_emp in cur_emp LOOP
DBMS_OUTPUT.put_line(v_emp.deptno||' '||v_emp.total_emp||' '||v_emp.total_sal);
insert into dept_sal values(v_emp.deptno,v_emp.total_emp,v_emp.total_sal);
end LOOP;
end;
--execute dml statement
INSERT INTO emp(empno,deptno,sal) VALUES('123','10',10000);
SELECT * FROM dept_sal;
DELETE EMP WHERE empno=123;
SELECT * FROM dept_sal;
---trigger example 3--
drop table employee;
CREATE TABLE employee ( id VARCHAR2(4) NOT NULL, name VARCHAR2(15) NOT NULL, age NUMBER(2) NOT NULL, sex CHAR NOT NULL );
DESC employee;
--²åÈëÊý¾Ý
INSERT INTO employee VALUES('e101','zhao',23,'M');
INSERT INTO employee VALUES('e102','jian',21,'F');
INSERT INTO employee VALUES('e103','jian',21,'F');
--´´½¨¼Ç¼±í
CREATE TABLE old_employee AS SELECT * FROM employee;
DESC old_employee;
--trigger--
drop trigger save_delete_date;
create or replace trigger save_delete_date
after delete on employee
for each row
begin
insert into old_employee values(:old.id,:old.name,:old.age,:old.sex);
end;
---delete opreation--
DELETE employee;
delete old_employee;
SELECT * FROM old_employee
select * from employee;
----trigger exampel4---
--ʵÀý6------------------------
--´´½¨´¥·¢Æ÷£¬½«²Ù×÷CREATE¡¢DROP´æ´¢ÔÚlog_info±í
--´´½¨±í
CREATE TABLE log_info ( manager_user VARCHAR2(15), manager_date VARCHAR2(15), manager_type VARCHAR2(15), obj_name VARCHAR2(15), obj_type VARCHAR2(15) );
--´´½¨´¥·¢Æ÷
drop trigger trig_log_info;
CREATE OR REPLACE TRIGGER trig_log_info
AFTER CREATE OR DROP ON SCHEMA
BEGIN INSERT INTO log_info
VALUES(USER,SYSDATE,SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_OWNER, SYS.DICTIONARY_OBJ_TYPE);
END;
/
CREATE OR REPLACE TRIGGER trig_log_info
AFTER CREATE OR DROP ON SCHEMA
BEGIN INSERT INTO log_info
VALUES(USER,SYSDATE,SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_OWNER, SYS.DICTIONARY_OBJ_TYPE);
END;
/
--²âÊÔÓï¾ä
CREATE TABLE a(id NUMBER);
CREATE TYPE aa AS OBJECT(id NUMBER);
SELECT * FROM log_info;
---study package--
create or replace package test_package
as
type outlist is ref cursor;
procedure getSeniorHighSchool(
maxrow in number,
minrow in number,
return_list out outlist);
end test_package;
----create package body---
create or replace body test_package
is
procedure getSeniorHighSchool(
maxrow in number,
minrow in number,
return_list out outlist)
as
begin
for
¡¡¡¡¡¡¡¡
¡¡¡¡¡¡¡¡¡¡ select * from (select a.*,rownum rnum from
(
¡¡¡¡¡¡¡¡¡¡¡¡--ÒµÎñÓï¾ä
¡¡¡¡¡¡¡¡¡¡) a where rownum<=maxrow) where rnum >=minrow;
end;
---test
create table employee_gary(employee_id number, last_name varchar2(2),salary number(10,2),department_id number);
insert into employee_gary values(1,'qu',10000,001);
insert into employee_gary values(1,'qu',10000,002);
insert into employee_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
and ad.accountid = ac.accountid
and ac.orgkey = g.cif_id
and ad.ADDRESSCATEGORY = 'Mailing'
AND a.entity_type = 'ACCNT'
AND a.start_date <= to_date(lv_asondate, 'mm-dd-yyyy')
AND a.end_date >= to_date(lv_asondate, 'mm-dd-yyyy')
AND a.del_flg != 'Y'
AND a.entity_cre_flg = 'Y'
AND a.bank_id = '01'
AND add_months(a.peg_review_date, -1) =
to_date(lv_asondate, 'mm-dd-yyyy')
AND a.INT_TBL_CODE_SRL_NUM =
(SELECT MAX(INT_TBL_CODE_SRL_NUM)
FROM tbaadm.ITC b
WHERE b.entity_id = a.entity_id
AND b.entity_type = 'ACCNT'
AND b.del_flg != 'Y'
AND b.entity_cre_flg = 'Y'
AND b.bank_id = '01');
BEGIN
IF (NOT CLOANS_PRT01345_cur01%ISOPEN) THEN
OPEN CLOANS_PRT01345_cur01;
END IF;
IF (CLOANS_PRT01345_cur01%ISOPEN) THEN
LOOP
FETCH CLOANS_PRT01345_cur01
INTO ENTITY_ID,
INT_TBL_CODE_SRL_NUM,
INT_TBL_CODE,
PEG_REVIEW_DATE,
FORACID,
CIF_ID,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
CITY,
STATE,
COUNTRY,
ZIP;
IF (CLOANS_PRT01345_cur01%NOTFOUND) THEN
CLOSE CLOANS_PRT01345_cur01;
RETURN;
END IF;
PIPE ROW(CLOANS_RPT01345_TYPE(
FORACID,
ADDRESS_LINE1,
ADDRESS_LINE2,
ADDRESS_LINE3,
CITY,
STATE,
COUNTRY,
ZIP,
PEG_REVIEW_DATE
)
);
END LOOP;
END IF;
END CLOANS_RPT01345_FUNC;
-------------------------------------------
-- test the Function
------------------------------------------
--SELECT * FROM TABLE(CLOANS_RPT01345_FUNC('11-30-2099'))where rownum<50
