SQL>
SQL> select salary,employee_id from employees
SALARY EMPLOYEE_ID
---------- -----------
2900 190
2500 191
4000 192
3900 193
3200 194
2800 195
3100 196
3300 197
107 rows selected.
SQL> CREATE OR REPLACE PROCEDURE raise_salary (p_id IN employees.employee_id%type) IS
2 begin
3 update employees set salary=salary*1.10 where employee_id=p_id;
4 end raise_salary;
5 /
Procedure created.
SQL> execute raise_salary(197)
PL/SQL procedure successfully completed.
SQL> select salary,employee_id from employees;
SALARY EMPLOYEE_ID
---------- -----------
2900 190
2500 191
4000 192
3900 193
3200 194
2800 195
3100 196
3630 197
107 rows selected.
SQL>
SQL> CREATE OR REPLACE PROCEDURE raise_sal
2 (p_id IN employees.employee_id%type,
3 p_name OUT employees.last_name%type,
4 p_sal OUT employees.salary%type)
5 IS
6 begin
7 select last_name,salary into p_name,p_sal from employees where employee_id=p_id;
8 end raise_sal;
9 /
Procedure created.
SQL> variable a varchar2(25)
SQL> variable b number
SQL> EXECUTE raise_sal(171, :a, :b)
PL/SQL procedure successfully completed.
SQL> print a b
A
--------------------------------
Smith
B
----------
7400
SQL>
SQL>
SQL> create or replace procedure format_phone (p_phone in out varchar2) is
2 begin
3 p_phone :='('||substr(p_phone,1,3)||')'||substr(p_phone,4,3)||'--'||substr(p_phone,7);
4 end format_phone;
5 /
Procedure created.
SQL>
SQL> variable phone varchar2(15)
SQL> begin
2 :phone:='0123456789';
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> print phone
PHONE
--------------------------------
0123456789
SQL>
SQL> EXECUTE format_phone(:phone)
PL/SQL procedure successfully completed.
SQL> print phone
PHONE
--------------------------------
(012)345--6789
SQL>
SQL>
SQL> select * from departments;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
210 IT Support 1700
220 NOC 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
27 rows selected.
SQL>
SQL>
SQL> desc departments;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
SQL>
SQL>
SQL>
SQL> create or replace procedure add_dept
2 (p_name in departments.department_name%type DEFAULT 'tinku',
3 p_loc in departments.location_id%type DEFAULT 1000)
4 IS
5 begin
6 insert into departments (department_id,department_name,location_id)
7 values (290,p_name,p_loc);
8 end add_dept;
9 /
Procedure created.
SQL>
SQL>
SQL> begin
2 add_dept;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select * from departments;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
290 tinku 1000
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
110 Accounting 205 1700
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
210 IT Support 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
220 NOC 1700
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
28 rows selected.
SQL>
SQL>
SQL> create table dept(department_id number(4),department_name varchar2(20),location_id number(5));
Table created.
SQL> SELECT * FROM DEPT;
no rows selected
SQL> DESC DEPT;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPARTMENT_ID NUMBER(4)
DEPARTMENT_NAME VARCHAR2(20)
LOCATION_ID NUMBER(5)
SQL>
SQL>
SQL> create or replace procedure add_dept
2 (p_name in departments.department_name%type DEFAULT 'tinku',
3 p_loc in departments.location_id%type DEFAULT 1000)
4 IS
5 begin
6 insert into dept (department_id,department_name,location_id)
7 values (290,p_name,p_loc);
8 end add_dept;
9 /
Procedure created.
SQL>
SQL> SELECT * FROM DEPT;
no rows selected
SQL> begin
2 add_dept;
3 add_dept ('TRAINING',5000);
4 add_dept (P_LOC =>6000, P_NAME=>'MADMAS');
5 add_dept (P_LOC =>7000);
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM DEPT;
DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID
------------- -------------------- -----------
290 tinku 1000
290 TRAINING 5000
290 MADMAS 6000
290 tinku 7000
SQL>
SQL> execute add_dept ('ganesh',400)
PL/SQL procedure successfully completed.
SQL> SELECT * FROM DEPT;
DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID
------------- -------------------- -----------
290 tinku 1000
290 TRAINING 5000
290 MADMAS 6000
290 tinku 7000
290 ganesh 400
SQL> execute add_dept ('ganesh')
PL/SQL procedure successfully completed.
SQL> SELECT * FROM DEPT;
DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID
------------- -------------------- -----------
290 tinku 1000
290 TRAINING 5000
290 MADMAS 6000
290 tinku 7000
290 ganesh 400
290 ganesh 1000
6 rows selected.
SQL> execute add_dept (500)
PL/SQL procedure successfully completed.
SQL> SELECT * FROM DEPT;
DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID
------------- -------------------- -----------
290 tinku 1000
290 TRAINING 5000
290 MADMAS 6000
290 tinku 7000
290 ganesh 400
290 ganesh 1000
290 500 1000
7 rows selected.
SQL>
==============================================================================
SQL> CREATE OR REPLACE PROCEDURE raise_salary (p_id IN employees.employee_id%type) IS
2 begin
3 update employees set salary=salary*1.10 where employee_id=p_id;
4 end raise_salary;
5 /
Procedure created.
SQL> execute raise_salary(197)
PL/SQL procedure successfully completed.
SQL> create or replace procedure proc
2 is
3 cursor emp_cur is select employee_id from employees;
4 begin
5 for emp_rec in emp_cur loop
6 raise_salary(emp_rec.employee_id);
7 end loop;
8 commit;
9 end proc;
10 /
Procedure created.
====================================================================================
SQL> create table log_table (user_id varchar2(20),log_date date);
Table created.
SQL> select * from log_table;
no rows selected
SQL> create table t1 (empno number);
Table created.
SQL> insert into t1 values (100);
1 row created.
SQL> select * from t1;
EMPNO
----------
100
SQL>
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
LOG_TABLE TABLE
T1 TABLE
10 rows selected.
SQL>
SQL>
SQL>
SQL> select * from log_table;
no rows selected
SQL> create or replace procedure leave_table
2 (p_id number)
3 IS
4 procedure log_ex
5 IS
6 begin
7 insert into log_table(user_id,log_date)
8 values (user,sysdate);
9 end log_ex;
10 begin
11 delete from t1 where empno=p_id;
12 log_ex;
13 end leave_table;
14 /
Procedure created.
SQL>
SQL>
SQL> execute leave_table(100);
PL/SQL procedure successfully completed.
SQL> select * from log_table;
USER_ID LOG_DATE
-------------------- ---------
HR 07-JUL-10
SQL>
================================================ HANDLE EXCEPTION =========================================
SQL> set serveroutput on
SQL> create or replace procedure p2_inn(p_loc number) is
2 v_id number(4);
3 begin
4 dbms_output.put_line('procedue p2_inn started');
5 insert into departments values (5,'dept_5',145,p_loc);
6 select department_id into v_id from employees where employee_id=999;
7 end ;
8 /
Procedure created.
SQL>
SQL> set serveroutput on
SQL> create or replace procedure p1_inn(p_id number,p_city varchar2) is
2 v_city varchar2(30);
3 v_dname varchar2(30);
4 begin
5 dbms_output.put_line('main procedure p1_inn');
6 insert into locations (location_id,city) values (p_id,p_city);
7 select city into v_city from locations where location_id=p_id;
8 dbms_output.put_line('inserted city '||v_city);
9 dbms_output.put_line('invoking the procedure p2_inn');
10 p2_inn(p_id);
11 EXCEPTION
12 WHEN NO_DATA_FOUND THEN
13 dbms_output.put_line('NO SUCH DEPT/LOC FOR ANY EMPLOYEE');
14 END;
15 /
Procedure created.
SQL>
SQL>
SQL> EXECUTE P1_INN(1,'REDWOOD SHORES')
main procedure p1_inn
inserted city REDWOOD SHORES
invoking the procedure p2_inn
procedue p2_inn started
NO SUCH DEPT/LOC FOR ANY EMPLOYEE
PL/SQL procedure successfully completed.
SQL> SELECT LOCATION_ID,CITY FROM LOCATIONS WHERE LOCATION_ID=1;
LOCATION_ID CITY
----------- ------------------------------
1 REDWOOD SHORES
SQL> SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID=5;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
5 dept_5 145 1
SQL> SELECT LOCATION_ID,CITY FROM LOCATIONS;
LOCATION_ID CITY
----------- ------------------------------
1 REDWOOD SHORES
1000 Roma
1100 Venice
1200 Tokyo
1300 Hiroshima
1400 Southlake
1500 South San Francisco
1600 South Brunswick
1700 Seattle
1800 Toronto
1900 Whitehorse
LOCATION_ID CITY
----------- ------------------------------
2000 Beijing
2100 Bombay
2200 Sydney
2300 Singapore
2400 London
2500 Oxford
2600 Stretford
2700 Munich
2800 Sao Paulo
2900 Geneva
3000 Bern
LOCATION_ID CITY
----------- ------------------------------
3100 Utrecht
3200 Mexico City
24 rows selected.
SQL>
SQL> SELECT * FROM DEPARTMENTS;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
5 dept_5 145 1
290 tinku 1000
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
210 IT Support 1700
220 NOC 1700
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
29 rows selected.
SQL>
================================================ UNHANDLED EXCEPTIONS ============================================
SQL> set serveroutput on
SQL> create or replace procedure p2_inn(p_loc number) is
2 v_id number(4);
3 begin
4 dbms_output.put_line('procedue p2_inn started');
5 insert into departments values (6,'dept_5',150,p_loc);
6 select department_id into v_id from employees where employee_id=999;
7 end ;
8 /
Procedure created.
SQL> set serveroutput on
SQL> create or replace procedure p1_inn(p_id number,p_city varchar2) is
2 v_city varchar2(30);
3 v_dname varchar2(30);
4 begin
5 dbms_output.put_line('main procedure p1_inn');
6 insert into locations (location_id,city) values (p_id,p_city);
7 select city into v_city from locations where location_id=p_id;
8 dbms_output.put_line('inserted city '||v_city);
9 dbms_output.put_line('invoking the procedure p2_inn');
10 p2_inn(p_id);
11 END;
12 /
Procedure created.
SQL> EXECUTE P1_INN(3,'NEW DELHI')
main procedure p1_inn
inserted city NEW DELHI
invoking the procedure p2_inn
procedue p2_inn started
BEGIN P1_INN(3,'NEW DELHI'); END;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "HR.P2_INN", line 6
ORA-06512: at "HR.P1_INN", line 10
ORA-06512: at line 1
SQL> SELECT LOCATION_ID,CITY FROM LOCATIONS WHERE LOCATION_ID=3;
no rows selected
SQL> SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID=6;
no rows selected
SQL>
SQL>
SQL> DROP PROCEDURE P1_INN;
Procedure dropped.
SQL> DROP PROCEDURE P2_INN;
Procedure dropped.
SQL> select salary,employee_id from employees
SALARY EMPLOYEE_ID
---------- -----------
2900 190
2500 191
4000 192
3900 193
3200 194
2800 195
3100 196
3300 197
107 rows selected.
SQL> CREATE OR REPLACE PROCEDURE raise_salary (p_id IN employees.employee_id%type) IS
2 begin
3 update employees set salary=salary*1.10 where employee_id=p_id;
4 end raise_salary;
5 /
Procedure created.
SQL> execute raise_salary(197)
PL/SQL procedure successfully completed.
SQL> select salary,employee_id from employees;
SALARY EMPLOYEE_ID
---------- -----------
2900 190
2500 191
4000 192
3900 193
3200 194
2800 195
3100 196
3630 197
107 rows selected.
SQL>
SQL> CREATE OR REPLACE PROCEDURE raise_sal
2 (p_id IN employees.employee_id%type,
3 p_name OUT employees.last_name%type,
4 p_sal OUT employees.salary%type)
5 IS
6 begin
7 select last_name,salary into p_name,p_sal from employees where employee_id=p_id;
8 end raise_sal;
9 /
Procedure created.
SQL> variable a varchar2(25)
SQL> variable b number
SQL> EXECUTE raise_sal(171, :a, :b)
PL/SQL procedure successfully completed.
SQL> print a b
A
--------------------------------
Smith
B
----------
7400
SQL>
SQL>
SQL> create or replace procedure format_phone (p_phone in out varchar2) is
2 begin
3 p_phone :='('||substr(p_phone,1,3)||')'||substr(p_phone,4,3)||'--'||substr(p_phone,7);
4 end format_phone;
5 /
Procedure created.
SQL>
SQL> variable phone varchar2(15)
SQL> begin
2 :phone:='0123456789';
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> print phone
PHONE
--------------------------------
0123456789
SQL>
SQL> EXECUTE format_phone(:phone)
PL/SQL procedure successfully completed.
SQL> print phone
PHONE
--------------------------------
(012)345--6789
SQL>
SQL>
SQL> select * from departments;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
210 IT Support 1700
220 NOC 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
27 rows selected.
SQL>
SQL>
SQL> desc departments;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
SQL>
SQL>
SQL>
SQL> create or replace procedure add_dept
2 (p_name in departments.department_name%type DEFAULT 'tinku',
3 p_loc in departments.location_id%type DEFAULT 1000)
4 IS
5 begin
6 insert into departments (department_id,department_name,location_id)
7 values (290,p_name,p_loc);
8 end add_dept;
9 /
Procedure created.
SQL>
SQL>
SQL> begin
2 add_dept;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select * from departments;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
290 tinku 1000
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
110 Accounting 205 1700
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
210 IT Support 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
220 NOC 1700
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
28 rows selected.
SQL>
SQL>
SQL> create table dept(department_id number(4),department_name varchar2(20),location_id number(5));
Table created.
SQL> SELECT * FROM DEPT;
no rows selected
SQL> DESC DEPT;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPARTMENT_ID NUMBER(4)
DEPARTMENT_NAME VARCHAR2(20)
LOCATION_ID NUMBER(5)
SQL>
SQL>
SQL> create or replace procedure add_dept
2 (p_name in departments.department_name%type DEFAULT 'tinku',
3 p_loc in departments.location_id%type DEFAULT 1000)
4 IS
5 begin
6 insert into dept (department_id,department_name,location_id)
7 values (290,p_name,p_loc);
8 end add_dept;
9 /
Procedure created.
SQL>
SQL> SELECT * FROM DEPT;
no rows selected
SQL> begin
2 add_dept;
3 add_dept ('TRAINING',5000);
4 add_dept (P_LOC =>6000, P_NAME=>'MADMAS');
5 add_dept (P_LOC =>7000);
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM DEPT;
DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID
------------- -------------------- -----------
290 tinku 1000
290 TRAINING 5000
290 MADMAS 6000
290 tinku 7000
SQL>
SQL> execute add_dept ('ganesh',400)
PL/SQL procedure successfully completed.
SQL> SELECT * FROM DEPT;
DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID
------------- -------------------- -----------
290 tinku 1000
290 TRAINING 5000
290 MADMAS 6000
290 tinku 7000
290 ganesh 400
SQL> execute add_dept ('ganesh')
PL/SQL procedure successfully completed.
SQL> SELECT * FROM DEPT;
DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID
------------- -------------------- -----------
290 tinku 1000
290 TRAINING 5000
290 MADMAS 6000
290 tinku 7000
290 ganesh 400
290 ganesh 1000
6 rows selected.
SQL> execute add_dept (500)
PL/SQL procedure successfully completed.
SQL> SELECT * FROM DEPT;
DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID
------------- -------------------- -----------
290 tinku 1000
290 TRAINING 5000
290 MADMAS 6000
290 tinku 7000
290 ganesh 400
290 ganesh 1000
290 500 1000
7 rows selected.
SQL>
==============================================================================
SQL> CREATE OR REPLACE PROCEDURE raise_salary (p_id IN employees.employee_id%type) IS
2 begin
3 update employees set salary=salary*1.10 where employee_id=p_id;
4 end raise_salary;
5 /
Procedure created.
SQL> execute raise_salary(197)
PL/SQL procedure successfully completed.
SQL> create or replace procedure proc
2 is
3 cursor emp_cur is select employee_id from employees;
4 begin
5 for emp_rec in emp_cur loop
6 raise_salary(emp_rec.employee_id);
7 end loop;
8 commit;
9 end proc;
10 /
Procedure created.
====================================================================================
SQL> create table log_table (user_id varchar2(20),log_date date);
Table created.
SQL> select * from log_table;
no rows selected
SQL> create table t1 (empno number);
Table created.
SQL> insert into t1 values (100);
1 row created.
SQL> select * from t1;
EMPNO
----------
100
SQL>
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
LOG_TABLE TABLE
T1 TABLE
10 rows selected.
SQL>
SQL>
SQL>
SQL> select * from log_table;
no rows selected
SQL> create or replace procedure leave_table
2 (p_id number)
3 IS
4 procedure log_ex
5 IS
6 begin
7 insert into log_table(user_id,log_date)
8 values (user,sysdate);
9 end log_ex;
10 begin
11 delete from t1 where empno=p_id;
12 log_ex;
13 end leave_table;
14 /
Procedure created.
SQL>
SQL>
SQL> execute leave_table(100);
PL/SQL procedure successfully completed.
SQL> select * from log_table;
USER_ID LOG_DATE
-------------------- ---------
HR 07-JUL-10
SQL>
================================================ HANDLE EXCEPTION =========================================
SQL> set serveroutput on
SQL> create or replace procedure p2_inn(p_loc number) is
2 v_id number(4);
3 begin
4 dbms_output.put_line('procedue p2_inn started');
5 insert into departments values (5,'dept_5',145,p_loc);
6 select department_id into v_id from employees where employee_id=999;
7 end ;
8 /
Procedure created.
SQL>
SQL> set serveroutput on
SQL> create or replace procedure p1_inn(p_id number,p_city varchar2) is
2 v_city varchar2(30);
3 v_dname varchar2(30);
4 begin
5 dbms_output.put_line('main procedure p1_inn');
6 insert into locations (location_id,city) values (p_id,p_city);
7 select city into v_city from locations where location_id=p_id;
8 dbms_output.put_line('inserted city '||v_city);
9 dbms_output.put_line('invoking the procedure p2_inn');
10 p2_inn(p_id);
11 EXCEPTION
12 WHEN NO_DATA_FOUND THEN
13 dbms_output.put_line('NO SUCH DEPT/LOC FOR ANY EMPLOYEE');
14 END;
15 /
Procedure created.
SQL>
SQL>
SQL> EXECUTE P1_INN(1,'REDWOOD SHORES')
main procedure p1_inn
inserted city REDWOOD SHORES
invoking the procedure p2_inn
procedue p2_inn started
NO SUCH DEPT/LOC FOR ANY EMPLOYEE
PL/SQL procedure successfully completed.
SQL> SELECT LOCATION_ID,CITY FROM LOCATIONS WHERE LOCATION_ID=1;
LOCATION_ID CITY
----------- ------------------------------
1 REDWOOD SHORES
SQL> SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID=5;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
5 dept_5 145 1
SQL> SELECT LOCATION_ID,CITY FROM LOCATIONS;
LOCATION_ID CITY
----------- ------------------------------
1 REDWOOD SHORES
1000 Roma
1100 Venice
1200 Tokyo
1300 Hiroshima
1400 Southlake
1500 South San Francisco
1600 South Brunswick
1700 Seattle
1800 Toronto
1900 Whitehorse
LOCATION_ID CITY
----------- ------------------------------
2000 Beijing
2100 Bombay
2200 Sydney
2300 Singapore
2400 London
2500 Oxford
2600 Stretford
2700 Munich
2800 Sao Paulo
2900 Geneva
3000 Bern
LOCATION_ID CITY
----------- ------------------------------
3100 Utrecht
3200 Mexico City
24 rows selected.
SQL>
SQL> SELECT * FROM DEPARTMENTS;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
5 dept_5 145 1
290 tinku 1000
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
210 IT Support 1700
220 NOC 1700
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
29 rows selected.
SQL>
================================================ UNHANDLED EXCEPTIONS ============================================
SQL> set serveroutput on
SQL> create or replace procedure p2_inn(p_loc number) is
2 v_id number(4);
3 begin
4 dbms_output.put_line('procedue p2_inn started');
5 insert into departments values (6,'dept_5',150,p_loc);
6 select department_id into v_id from employees where employee_id=999;
7 end ;
8 /
Procedure created.
SQL> set serveroutput on
SQL> create or replace procedure p1_inn(p_id number,p_city varchar2) is
2 v_city varchar2(30);
3 v_dname varchar2(30);
4 begin
5 dbms_output.put_line('main procedure p1_inn');
6 insert into locations (location_id,city) values (p_id,p_city);
7 select city into v_city from locations where location_id=p_id;
8 dbms_output.put_line('inserted city '||v_city);
9 dbms_output.put_line('invoking the procedure p2_inn');
10 p2_inn(p_id);
11 END;
12 /
Procedure created.
SQL> EXECUTE P1_INN(3,'NEW DELHI')
main procedure p1_inn
inserted city NEW DELHI
invoking the procedure p2_inn
procedue p2_inn started
BEGIN P1_INN(3,'NEW DELHI'); END;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "HR.P2_INN", line 6
ORA-06512: at "HR.P1_INN", line 10
ORA-06512: at line 1
SQL> SELECT LOCATION_ID,CITY FROM LOCATIONS WHERE LOCATION_ID=3;
no rows selected
SQL> SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID=6;
no rows selected
SQL>
SQL>
SQL> DROP PROCEDURE P1_INN;
Procedure dropped.
SQL> DROP PROCEDURE P2_INN;
Procedure dropped.
No comments:
Post a Comment