Oracle object table example

Source: Internet
Author: User
Source code:

1. Create an object type

CREATE OR REPLACE
Type ty_usernum as object
(
Seq_call_usernum number,
Calltype number,
Areacode varchar2 (10 ),
Usernum varchar2 (20)
)
/


2. Create a collection type

Create or replace type ty_tbl_usernum is table of ty_usernum
/


3. Process

Create or replace procedure p (
P_authid IN VARCHAR2,
R_called OUT VARCHAR2,
R_cursor OUT sys_refcursor,
R_code OUT INT
)
IS
V_areacode t1.areacode % TYPE;
V_rowcount INT: = 0;
V_sysdate CHAR (4 );
V_week CHAR (1 );
V_random INT;
V_calltype t1.calltype % TYPE;
V_seq_call_usernum t1.seq _ call_usernum % TYPE;
V_usernum t1.usernum % TYPE;
V_ty_usernum ty_usernum
: = Ty_usernum (v_seq_call_usernum, v_calltype, v_areacode, v_usernum );
V_ty_tbl_usernum ty_tbl_usernum: = ty_tbl_usernum ();
BEGIN
BEGIN
SELECT areacode, calltype
INTO v_areacode, v_calltype
FROM t2
WHERE seq_areacalledno =
(SELECT seq_areacallno
FROM t3
WHERE seq_threadstatus = p_authid AND status = 1 );
EXCEPTION
WHEN NO_DATA_FOUND
THEN
Delete from t3
WHERE seq_threadstatus = p_authid;

COMMIT;
R_code: = 2;
RETURN;
END;

Select count (*)
Into v_rowcount
From T1
Where areacode = v_areacode;

If v_rowcount = 0
Then
Delete from T3
Where seq_threadstatus = p_authid;

Commit;
R_code: = 2;
Return;
End if;

Open r_cursor
SELECT seq_call_usernum, calltype, areacode, usernum
FROM (SELECT seq_call_usernum, calltype, areacode, usernum
FROM t1
WHERE areacode = v_areacode
AND status = 0
AND calltype = v_calltype
Order by dbms_random.value)
Where rownum <11;

Loop
Fetch r_cursor
Into v_seq_call_usernum, v_calltype, v_areacode, v_usernum;

Exit when r_cursor % notfound;

Update T1
Set calldate = sysdate,
Status = 1
Where seq_call_usernum = v_seq_call_usernum;

V_ty_usernum.seq_call_usernum: = v_seq_call_usernum;
V_ty_usernum.calltype: = v_calltype;
V_ty_usernum.areacode: = v_areacode;
V_ty_usernum.usernum: = v_usernum;
V_ty_tbl_usernum.extend;
V_ty_tbl_usernum (v_ty_tbl_usernum.count): = v_ty_usernum;
End loop;

Close r_cursor;

Open r_cursor
Select seq_call_usernum, calltype, areacode, usernum
From table (CAST (v_ty_tbl_usernum AS ty_tbl_usernum ));

COMMIT;
R_code: = 1;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
R_code: = 0;
RAISE;
END p;
/

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.