SQL> create or replace procedure prc_exp(vb in number) is
2 e1 exception;
3 e2 exception;
4 begin
5 if vb > 10 then
6 raise e1;
7 elsif vb < 10 then
8 raise e2;
9 else
10 dbms_output.put_line(vb);
11 end if;
12 exception
13 when others then
14 dbms_output.put_line(sqlcode||' '||sqlerrm);
15 end;
16 /
Procedure created.
SQL> begin
2 prc_exp(10);
3 end;
4 /
10
PL/SQL procedure successfully completed.
SQL> begin
2 prc_exp(50);
3 end;
4 /
1 User-Defined Exception
PL/SQL procedure successfully completed.
SQL> begin
2 prc_exp(5);
3 end;
4 /
1 User-Defined Exception
PL/SQL procedure successfully completed.
SQL> create or replace procedure prc_exp(vb in number) is
2 e1 exception;
3 e2 exception;
4 pragma exception_init(e1,-20001);
5 pragma exception_init(e2,-20002);
6 begin
7 if vb > 10 then
8 raise e1;
9 elsif vb < 10 then
10 raise e2;
11 else
12 dbms_output.put_line(vb);
13 end if;
14 exception
15 when others then
16 dbms_output.put_line(sqlcode||' '||sqlerrm);
17 end;
18 /
Procedure created.
SQL> begin
2 prc_exp(50);
3 end;
4 /
-20001 ORA-20001:
PL/SQL procedure successfully completed.
SQL> begin
2 prc_exp(5);
3 end;
4 /
-20002 ORA-20002:
PL/SQL procedure successfully completed.
SQL> create or replace procedure prc_exp(vb in number) is
2 begin
3 if vb > 10 then
4 raise_application_error(-20001,'value more than 10');
5 elsif vb < 10 then
6 raise_application_error(-20001,'value less than 10');
7 else
8 dbms_output.put_line(vb);
9 end if;
10 exception
11 when others then
12 dbms_output.put_line(sqlcode||' '||sqlerrm);
13 end;
14 /
Procedure created.
SQL> begin
2 prc_exp(15);
3 end;
4 /
-20001 ORA-20001: value more than 10
PL/SQL procedure successfully completed.
SQL> begin
2 prc_exp(1);
3 end;
4 /
-20001 ORA-20001: value less than 10
PL/SQL procedure successfully completed.
SQL> create or replace procedure prc_ref(vb out sys_refcursor) is
2 begin
3 open vb for select * from dept;
4 end;
5 /
Procedure created.
SQL> set pagesize 120 linesize 120
SQL> declare
2 a refcursor;
3 begin
4 prc_ref(a);
5 end;
6 /
a refcursor;
*
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00201: identifier 'REFCURSOR' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Item ignored
ORA-06550: line 4, column 9:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 4, column 1:
PL/SQL: Statement ignored
SQL> var a refcursor;
SQL> exec prc_ref(:a);
PL/SQL procedure successfully completed.
SQL> print a;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
21 b c
12 a b
22 b c
11 a b
8 rows selected.
SQL> create or replace procedure prc_ref(vb out sys_refcursor) is
2 begin
3 open vb for select * from emp;
4 end;
5 /
Procedure created.
SQL> var b refcursor;
SQL> exec prc_ref(:b);
PL/SQL procedure successfully completed.
SQL> print b;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
SQL> create or replace procedure prc_weak is
2 type ty is ref cursor;
3 ty_w ty;
4 vb emp%rowtype;
5 begin
6 open ty_w for select * from emp;
7 loop
8 fetch ty_w into vb;
9 dbms_output.put_line([Link]||' '||[Link]||' '||[Link]);
10 exit when ty_w%notfound;
11 end loop;
12 close ty_w;
13 end;
14 /
Procedure created.
SQL> ed
Wrote file [Link]
1* exec prc_weak
SQL> /
exec prc_weak
*
ERROR at line 1:
ORA-00900: invalid SQL statement
SQL> exec prc_weak;
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
7934 MILLER CLERK
PL/SQL procedure successfully completed.
SQL> create or replace procedure prc_weak is
2 type ty is ref cursor;
3 ty_w ty;
4 vb emp%rowtype;
5 va dept%rowtype;
6 begin
7 open ty_w for select * from emp;
8 loop
9 fetch ty_w into vb;
10 dbms_output.put_line([Link]||' '||[Link]||' '||[Link]);
11 exit when ty_w%notfound;
12 end loop;
13 open ty_w for select * from dept;
14 loop
15 fetch ty_w into va;
16 dbms_output.put_line([Link]||' '||[Link]||' '||[Link]);
17 exit when ty_w%notfound;
18 end loop;
19 close ty_w;
20
21 end;
22 /
Procedure created.
SQL> exec prc_weak;
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
7934 MILLER CLERK
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
21 b c
12 a b
22 b c
11 a b
11 a b
PL/SQL procedure successfully completed.
SQL> create or replace procedure prc_weak is
2 type ty is ref cursor return emp%rowtype;
3 ty_w ty;
4 vb emp%rowtype;
5 begin
6 open ty_w for select * from emp;
7 loop
8 fetch ty_w into vb;
9 dbms_output.put_line([Link]||' '||[Link]||' '||[Link]);
10 exit when ty_w%notfound;
11 end loop;
12 close ty_w;
13 end;
14 /
Procedure created.
SQL> exec prc_weak;
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
7902 FORD ANALYST
7934 MILLER CLERK
7934 MILLER CLERK
PL/SQL procedure successfully completed.
SQL> ed
Wrote file [Link]
1 create or replace procedure prc_strong is
2 type ty is ref cursor return emp%rowtype;
3 ty_w ty;
4 vb emp%rowtype;
5 begin
6 open ty_w for select * from dept;
7 loop
8 fetch ty_w into vb;
9 dbms_output.put_line([Link]||' '||[Link]||' '||[Link]);
10 exit when ty_w%notfound;
11 end loop;
12 close ty_w;
13* end;
SQL> /
Warning: Procedure created with compilation errors.
SQL> shoe errors;
SP2-0734: unknown command beginning "shoe error..." - rest of line ignored.
SQL> show errors;
Errors for PROCEDURE PRC_STRONG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1 PL/SQL: SQL Statement ignored
6/15 PLS-00382: expression is of wrong type
9/1 PL/SQL: Statement ignored
9/42 PLS-00302: component 'DNAME' must be declared
SQL>
