PROCEDURE TEST_PROC1 AS
TYPE ID_TABLE_TYPE IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER;
TYPE NAME_TABLE_TYPE IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
ID_TABLE ID_TABLE_TYPE;
NAME_TABLE NAME_TABLE_TYPE;
START_TIME NUMBER(10);
END_TIME NUMBER(10);
BEGIN
FOR I IN 1 .. 10000 LOOP
ID_TABLE(I) := I;
NAME_TABLE(I) := 'name' || TO_CHAR(I);
END LOOP;
START_TIME := DBMS_UTILITY.GET_TIME;
FOR I IN 1 .. ID_TABLE.COUNT LOOP
INSERT INTO DEMO VALUES (ID_TABLE(I), NAME_TABLE(I));
END LOOP;
END_TIME := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE((END_TIME - START_TIME) / 100);
END TEST_PROC1;
PROCEDURE TEST_PROC2 AS
TYPE ID_TABLE_TYPE IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER;
TYPE NAME_TABLE_TYPE IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
ID_TABLE ID_TABLE_TYPE;
NAME_TABLE NAME_TABLE_TYPE;
START_TIME NUMBER(10);
END_TIME NUMBER(10);
BEGIN
FOR I IN 1 .. 10000 LOOP
ID_TABLE(I) := I;
NAME_TABLE(I) := 'name' || TO_CHAR(I);
END LOOP;
START_TIME := DBMS_UTILITY.GET_TIME;
FORALL I IN 1 .. ID_TABLE.COUNT
INSERT INTO DEMO VALUES (ID_TABLE(I), NAME_TABLE(I));
END_TIME := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE((END_TIME - START_TIME) / 100);
END TEST_PROC2;
PROCEDURE TEST_PROC3 AS
/*
*功能:使用批量绑定实现表的备份
*
*/
TYPE DEPTRECTAB IS TABLE OF DEPT%ROWTYPE;
DEPT_RECS DEPTRECTAB;
CURSOR C1 IS
SELECT DEPTNO, DNAME, LOC FROM DEPT WHERE DEPTNO < 4444444444;
BEGIN
BEGIN
OPEN C1;
FETCH C1 BULK COLLECT
INTO DEPT_RECS;
FOR i IN DEPT_RECS.FIRST..DEPT_RECS.LAST LOOP
dbms_output.put_line(DEPT_RECS(i).deptno);
dbms_output.put_line(DEPT_RECS(i).dname);
dbms_output.put_line(DEPT_RECS(i).loc);
END LOOP;
FORALL i IN DEPT_RECS.FIRST..DEPT_RECS.LAST
INSERT INTO dept_bk VALUES DEPT_RECS(i);
COMMIT;
CLOSE c1;
END;
END TEST_PROC3;
PROCEDURE test_proc4 AS
/*
*功能:最简单的存储过程,没有任何参数
*
*/
BEGIN
dbms_output.put_line(SYSDATE);
END test_proc4;
PROCEDURE test_proc5(vno IN NUMBER,vname VARCHAR2)
/*
*功能:有IN参数的存储过程例子,主要说明应用程序的参数可以在过程中直接使用
*
*/
IS
BEGIN
INSERT INTO emp (empno,ename) VALUES (vno,vname);
COMMIT;
END test_proc5;
PROCEDURE test_proc6(vno IN NUMBER,vname OUT VARCHAR2,vsalary OUT NUMBER)
/*
*功能:演示有OUT参数的存储过程,输入eno,输出姓名,工资
*/
IS
BEGIN
SELECT ename,sal INTO vname,vsalary FROM emp WHERE empno=vno;
EXCEPTION WHEN no_data_found THEN
raise_application_error(-2000,'无数据');
END test_proc6;
PROCEDURE test_proc7(v1 IN OUT NUMBER,v2 IN OUT NUMBER)
/*
*功能:有inout参数的存储过程演示,注意在调用过程之前需要通过变量给参数传递值,结束后变量将过程结果传递给应用程序
*
*/
IS
p1 NUMBER;
p2 NUMBER;
BEGIN
p1:=v1-v2;
p2:=v1+v2;
v1:=p1;
v2:=p2;
END test_proc7;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15720542/viewspace-676822/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15720542/viewspace-676822/