sql lapd6

Problem 1

ALTER TABLE customer1
ADD accno Number;

INSERT INTO customer1(customerid, customername, address1, address2, gender, age, phoneno, accno)
VALUES (6000,’John’,’#115 Chicago’,’#115 Chicago’,’M’, 25, 7878776, 10000);

INSERT INTO customer1(customerid, customername, address1, address2, gender, age, phoneno, accno)
VALUES (6001,’Jack’,’#116 France’,’#116 France’,’M’,25,434524,20000);

INSERT INTO customer1(customerid, customername, address1, address2, gender, age, phoneno, accno)
VALUES (6002,’James’,’#114 New York’,’#114 New York’,’M’,45, 431525, 15000.50);

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Problem 2

SQL> savepoint sp1;

Savepoint created.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Problem 3

INSERT INTO customer1(customerid, customername, address1, address2, gender, age, phoneno, accno)
VALUES (6003,’John’,’#114 Chicago’,’#114 Chicago’,’M’, 45, 439525, 19000.60);

SQL> select * from customer1;

CUSTOMERID CUSTOMERNAME ADDRESS1 ADDRESS2 G AGE PHONENO ACCNO
———- —————————— —————————— —————————— – ———- ———- ———-
6000 John #115 Chicago #115 Chicago M 25 7878776 10000
6001 Jack #116 France #116 France M 25 434524 20000
6002 James #114 New York #114 New York M 45 431525 15000.5
6003 John #114 Chicago #114 Chicago M 45 439525 19000.6

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Problem 4

ROLLBACK TO SAVEPOINT sp1;

SQL> select * from customer1;

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

sql lapd6

Lab 6

Problem 1

ALTER TABLE customer1
ADD accno Number;

INSERT INTO customer1(customerid, customername, address1, address2, gender, age, phoneno, accno)
VALUES (6000,’John’,’#115 Chicago’,’#115 Chicago’,’M’, 25, 7878776, 10000);

INSERT INTO customer1(customerid, customername, address1, address2, gender, age, phoneno, accno)
VALUES (6001,’Jack’,’#116 France’,’#116 France’,’M’,25,434524,20000);

INSERT INTO customer1(customerid, customername, address1, address2, gender, age, phoneno, accno)
VALUES (6002,’James’,’#114 New York’,’#114 New York’,’M’,45, 431525, 15000.50);

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Problem 2

SQL> savepoint sp1;

Savepoint created.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Problem 3

INSERT INTO customer1(customerid, customername, address1, address2, gender, age, phoneno, accno)
VALUES (6003,’John’,’#114 Chicago’,’#114 Chicago’,’M’, 45, 439525, 19000.60);

SQL> select * from customer1;

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Problem 4

ROLLBACK TO SAVEPOINT sp1;

SQL> select * from customer1;

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

sql lapd5

Problem 1

SQL> CREATE table employee2
2 AS
3 SELECT *
4 FROM EMP
5 WHERE 1=3;

Table created.

SQL> desc employee2
Name

Null? Type
—————————————————————————————————————————————————————————————————————————————————————————————————– ——– —————————————————————————————————————————————————————————————————-
EMPNO

NOT NULL NUMBER(4)
ENAME

VARCHAR2(10)
JOB

VARCHAR2(9)
MGR

NUMBER(4)
HIREDATE

DATE
SAL

NUMBER(7,2)
COMM

NUMBER(7,2)
DEPTNO

NUMBER(2)

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Problem 2

SQL> select * from employee2
2 ;

no rows selected

INSERT INTO employee2
SELECT *
FROM table1;

SELECT *
FROM employee2;

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 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
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.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Problem 3

UPDATE employee2 e
SET e.job = (Select em.job
From employee2 em
where em.empno = 7788),
e.deptno = (Select em.deptno
From employee2 em
where em.empno = 7788)
WHERE e.empno = 7698;

1 row updated.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Problem 4

DELETE
FROM employee2 e
WHERE e.deptno = (SELECT d.deptid
FROM department d
WHERE d.deptname = ‘SALES’);

0 rows deleted.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Problem 5

UPDATE employee2 e
SET e.deptno = (Select em.deptno
From employee2 em
where em.empno = 7788)
WHERE e.empno = 7698;

1 row updated.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Problem 6

Insert INTO employee2(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES (1000,’Allen’,’Clerk’,1001,’12-jan-01′,3000, 2,10);

Insert INTO employee2(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES (1001,’George’,’analyst’,null,’08-Sep-92′,5000,0, 10);

Insert INTO employee2(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES (1002,’Becker’,’Manager’,1000,’4-Nov-92′,2800,4,20);

Insert INTO employee2(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES (1003, ‘Bill’,’Clerk’,1002,’4-Nov-92′,3000, 0, 20);

pl/sql thd5

SQL> declare
2 f utl_file.file_type;– File Handler
3 a varchar2(40);
4 begin
5 f :=utl_file.fopen(‘SAMPLE’,’sample.txt’,’R’);
6 utl_file.get_line(f,a);
7 utl_file.fclose(f);
8 dbms_output.put_line(a);
9 end;
10
11 /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
Computer Science

PL/SQL procedure successfully completed.

SQL> CREATE TABLE person(id number,
2 name varchar2(20),
3 photo blob,
4 resume clob);

Table created.

SQL> SELECT * fROM person;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL> SELECT id,name FROM person;

no rows selected

SQL> INSERT INTO person VALUES(1,’dd’,NULL,’dd’);

1 row created.

SQL> INSERT INTO person VALUES(1,’dd’,empty_blob(),’dd’);

1 row created.

SQL>

pl/sql thd4

SQL> clear screen

SQL> CREATE TRIGGER trgdemo1
2 AFTER INSERT ON dept
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(‘Trigger fired’);
5 END;
6 /

Trigger created.

SQL> INSERT INTO dept VALUES(32,’ff’,’tt’);
Trigger fired

1 row created.

SQL> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE TRIGGER trgdemo1
2 AFTER INSERT ON dept
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(‘Trigger fired’);
5* END;
SQL> /

Trigger created.

SQL> CREATE OR REPLACE TRIGGER trgdemo2
2 AFTER UPDATE OF sal ON employee
3 FOR EACH ROW
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE(‘Trigger fired’);
6 END;
7 /

Trigger created.

SQL> UPDATE employee SET sal=sal+.2*sal;
Trigger fired
Trigger fired
Trigger fired
Trigger fired
Trigger fired
Trigger fired
Trigger fired
Trigger fired
Trigger fired

9 rows updated.

SQL> CREATE OR REPLACE TRIGGER trgdemo2
2 AFTER UPDATE OF sal ON employee
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(‘Trigger fired’);
5 END;
6 /

Trigger created.

SQL> UPDATE employee SET sal=sal+.2*sal;
Trigger fired

9 rows updated.

SQL> CREATE OR REPLACE TRIGGER demo3
2 AFTER INSERT OR UPDATE OR DELETE ON dept
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(‘trigger fired2′);
5 END;
6 /

Trigger created.

SQL> INSERT INTO dept VALUES(34,’dd’,’yy’);
trigger fired2
Trigger fired

1 row created.

SQL> ALTER TRIGGER trgdemo1 DISABLE;

Trigger altered.

SQL> INSERT INTO dept VALUES(39,’dd’,’yy’);
trigger fired2

1 row created.

SQL> ALTER TRIGGER trgdemo1 ENABLE;

Trigger altered.

SQL> INSERT INTO dept VALUES(37,’dd’,’yy’);
trigger fired2
Trigger fired

1 row created.

SQL> DROP TRIGGER trgdemo1;

Trigger dropped.

SQL> CREATE OR REPLACE TRIGGER trgdemo2
2 AFTER UPDATE OF sal ON employee
3 FOR EACH ROW
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE(‘Trigger fired’);
6 DBMS_OUTPUT.PUT_LINE(‘Old Salary’||:old.sal||’ Revised Salary ‘||:new.sal);
7 END;
8 /

Trigger created.

SQL> UPDATE employee SET sal=sal+300;
Trigger fired
Old Salary7632 Revised Salary 7932
Trigger fired
Old Salary7776 Revised Salary 8076
Trigger fired
Old Salary7776 Revised Salary 8076
Trigger fired
Old Salary7776 Revised Salary 8076
Trigger fired
Old Salary2736 Revised Salary 3036
Trigger fired
Old Salary2232 Revised Salary 2532
Trigger fired
Old Salary4716 Revised Salary 5016
Trigger fired
Old Salary2232 Revised Salary 2532
Trigger fired
Old Salary4752 Revised Salary 5052

9 rows updated.

SQL> spool off

pl/sql thd3

SQL> clear screen

SQL> CREATE PACKAGE demo3
2 AS
3 TYPE details IS REF CURSOR;
4 PROCEDURE viewDept(deptCur IN OUT details);
5 END demo3;
6 /

Package created.

SQL> CREATE PACKAGE BODY demo3
2 AS
3 PROCEDURE viewDept(deptCur IN OUT details) IS
4 BEGIN
5 OPEN deptCur FOR sELECT * FROM dept;
6 END viewDept;
7 END demo3;
8 /

Package body created.

SQL> VARIABLE v REFCURSOR
SQL> set autoprint on
SQL> exec demo3.viewDept(:v);

PL/SQL procedure successfully completed.

DEPTNO DNAME LOC
———- ————– ————-
14 Test CHN
22 HR CHN
778 nb gj
45 gh hh
10 ACCOUNTING NEWYORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
33 dd dd
83 ee
84 ee

DEPTNO DNAME LOC
———- ————– ————-
77 tt uu
66 ee gg
12 s r
1 ee ff
2 sdf fsd

16 rows selected.

SQL> desc dept;
Name Null? Type
—————————————– ——– —————————-
DEPTNO NOT NULL NUMBER(4)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

SQL> INSERT INTO dept VALUES(NULL,’ee’,’rr’);
INSERT INTO dept VALUES(NULL,’ee’,’rr’)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (“RATHNA”.”DEPT”.”DEPTNO”)

SQL> desc errorlog
Name Null? Type
—————————————– ——– —————————-
ERRORNO NUMBER
DESCRIPTION VARCHAR2(80)
ERRORDATE DATE

SQL> BEGIN
2 INSERT INTO dept VALUES(44,’ee’,’rr’);
3 INSERT INTO dept VALUES(NULL,’ee’,’rr’);
4 EXCEPTION
5 WHEN OTHERS THEN
6 INSERT INTO errorlog VALUES(33,’dff’,sysdate);
7 END;
8 /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /

PL/SQL procedure successfully completed.

SQL> select * from dept WHERE deptno=44;

DEPTNO DNAME LOC
———- ————– ————-
44 ee rr

SQL> select * from errorlog WHERE errorno=44;

no rows selected

SQL> select * from errorlog WHERE errorno=33;

ERRORNO
———-
DESCRIPTION
——————————————————————————–
ERRORDATE
———
33
dff
27-AUG-14

33
dff
27-AUG-14

ERRORNO
———-
DESCRIPTION
——————————————————————————–
ERRORDATE
———

PL/SQL procedure successfully completed.

SQL> BEGIN
2 INSERT INTO dept VALUES(54,’ee’,’rr’);
3 INSERT INTO dept VALUES(NULL,’ee’,’rr’);
4 EXCEPTION
5 WHEN OTHERS THEN
6 INSERT INTO errorlog VALUES(34,’dff’,sysdate);
7 rollback;
8 END;
9 /

PL/SQL procedure successfully completed.

SQL> select * from dept WHERE deptno=54;

no rows selected

SQL> select * from errorlog WHERE errorno=34;

no rows selected

SQL> create sequence errorid;

Sequence created.

SQL> CREATE PROCEDURE logerror2
2 AS
3 BEGIN
4 INSERT INTO errorlog VALUES(errorid.nextval,’dff’,sysdate);
5 END;
6 /

Procedure created.

SQL> BEGIN
2 INSERT INTO dept VALUES(54,’ee’,’rr’);
3 INSERT INTO dept VALUES(NULL,’ee’,’rr’);
4 EXCEPTION
5 WHEN OTHERS THEN
6 logerror2;
7 rollback;
8 END;
9 /

PL/SQL procedure successfully completed.

SQL> SELECT errorno FROM errorlog WHERE description=’dff’;

no rows selected

SQL> SELECT * FROM dept WHERE deptno=54;

no rows selected

SQL> CREATE OR REPLACE PROCEDURE logerror2
2 AS
3 PRAGMA AUTONOMOUS_TRANSACTION;
4 BEGIN
5 INSERT INTO errorlog VALUES(errorid.nextval,’dff’,sysdate);
6 COMMIT;
7 END;
8 /

Procedure created.

SQL> BEGIN
2 INSERT INTO dept VALUES(54,’ee’,’rr’);
3 INSERT INTO dept VALUES(NULL,’ee’,’rr’);
4 EXCEPTION
5 WHEN OTHERS THEN
6 logerror2;
7 rollback;
8 END;
9 /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM dept WHERE deptno=54;

no rows selected

SQL> SELECT errorno FROM errorlog WHERE description=’dff’;

ERRORNO
———-
2

SQL> CREATE OR REPLACE PROCEDURE spSum(num1 IN NUMBER, num2 IN NUMBER, num3 IN NUMBER)
2 AS
3 result NUMBER;
4 BEGIN
5 result:=num1+num2+num3;
6 DBMS_OUTPUT.PUT_LINE(result);
7 END;
8 /

Procedure created.

SQL> exec spSum(5,4,6);
15

PL/SQL procedure successfully completed.

SQL> exec spSum(num2=>4,num3=>6,num1=>5);
15

PL/SQL procedure successfully completed.

SQL> exec spSum(5,num2=>4,num3=>6);
15

PL/SQL procedure successfully completed.

SQL> exec spSum(5,num2=>4,6);
BEGIN spSum(5,num2=>4,6); END;

*
ERROR at line 1:
ORA-06550: line 1, column 23:
PLS-00312: a positional parameter association may not follow a named
association
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> spool off

pl/sql th32

SQL> CREATE PACKAGE packdemo1
2 AS
3 PROCEDURE spDemo5;
4 FUNCTION fnDemo3 RETURN NUMBER;
5 END packdemo1;
6 /

Package created.

SQL> CREATE PACKAGE BODY packdemo1
2 AS
3 PROCEDURE spDemo5
4 BEGIN
5 DBMS_OUTPUT.PUT_LINe(‘Procedure in package’);
6 END;
7 FUNCTION fnDemo3 RETURN NUMBER IS
8 BEGIN
9 DBMS_OUTPUT.PUT_LINe(‘Function in package’);
10 RETURN 1;
11 END fnDemo3;
12 END packdemo1;
13 /

Warning: Package Body created with compilation errors.

SQL> ed
Wrote file afiedt.buf

1 CREATE PACKAGE BODY packdemo1
2 AS
3 PROCEDURE spDemo5 Is
4 BEGIN
5 DBMS_OUTPUT.PUT_LINe(‘Procedure in package’);
6 END spDemo5;
7 FUNCTION fnDemo3 RETURN NUMBER IS
8 BEGIN
9 DBMS_OUTPUT.PUT_LINe(‘Function in package’);
10 RETURN 1;
11 END fnDemo3;
12* END packdemo1;
SQL> /
CREATE PACKAGE BODY packdemo1
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE PACKAGE BODY packdemo1
2 AS
3 PROCEDURE spDemo5 Is
4 BEGIN
5 DBMS_OUTPUT.PUT_LINe(‘Procedure in package’);
6 END spDemo5;
7 FUNCTION fnDemo3 RETURN NUMBER IS
8 BEGIN
9 DBMS_OUTPUT.PUT_LINe(‘Function in package’);
10 RETURN 1;
11 END fnDemo3;
12* END packdemo1;
SQL> /

Package body created.

SQL> exec packdemo1.spDemo5;

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> exec packdemo1.spDemo5;
Procedure in package

PL/SQL procedure successfully completed.

SQL> SELECT packdemo1.fnDemo3 FROM dual;

FNDEMO3
———-
1

Function in package
SQL> CREATE OR REPLACE PACKAGE packdemo2
2 AS
3 PROCEDURE spDemo5;
4 FUNCTION fnDemo3 RETURN NUMBER;
5 END packdemo2;
6 /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY packdemo2
2 AS
3 PROCEDURE spDemo5 Is
4 BEGIN
5 DBMS_OUTPUT.PUT_LINe(‘Procedure in package’);
6 END spDemo5;
7
8 END packdemo2;
9 /

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY PACKDEMO2:

LINE/COL ERROR
——– —————————————————————–
4/13 PLS-00323: subprogram or cursor ‘FNDEMO3’ is declared in a
package specification and must be defined in the package body

SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY packdemo2
2 AS
3 PROCEDURE spDemo5 Is
4 BEGIN
5 DBMS_OUTPUT.PUT_LINe(‘Procedure in package’);
6 END spDemo5;
7 FUNCTION fnDemo3 RETURN NUMBER IS
8 BEGIN
9 DBMS_OUTPUT.PUT_LINe(‘Function in package’);
10 RETURN 1;
11 END fnDemo3;
12 END packdemo2;
13 /

Package body created.

SQL> CREATE OR REPLACE PACKAGE BODY packdemo2
2 AS
3 PROCEDURE spDemo5 Is
4 BEGIN
5 DBMS_OUTPUT.PUT_LINe(‘Procedure in package’);
6 END spDemo5;
7 PROCEDURE spDemo6 Is
8 BEGIN
9 DBMS_OUTPUT.PUT_LINe(‘Procedure2 in package’);
10 END spDemo6;
11 FUNCTION fnDemo3 RETURN NUMBER IS
12 BEGIN
13 DBMS_OUTPUT.PUT_LINe(‘Function in package’);
14 RETURN 1;
15 END fnDemo3;
16 END packdemo2;
17 /

Package body created.

SQL> exit

pl/sql thd1

BEGIN
INSERT INTO dept VALUES(54,’ee’,’rr’);
INSERT INTO dept VALUES(NULL,’ee’,’rr’);
EXCEPTION
WHEN OTHERS THEN
logerror2;
rollback;
END;
CREATE OR REPLACE PROCEDURE logerror2
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO errorlog VALUES(errorid.nextval,’dff’,sysdate);
COMMIT;
END;
/

pl/sql twod4

SQL> CREATE PROCEDURE spDemo1
2 AS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(‘Hello’);
5 END spDemo1;
6 /

Procedure created.

SQL> execute spDemo1;

PL/SQL procedure successfully completed.

SQL> set serveroutput on

SQL> execute spDemo1;
Hello

PL/SQL procedure successfully completed.

SQL> exec spDemo1;
Hello

PL/SQL procedure successfully completed.

SQL> BEGIN
2 spDemo1;
3 END;
4 /
Hello

PL/SQL procedure successfully completed.

SQL> SELECT PROCEDURE_NAME FROM USER_PROCEDURES;

PROCEDURE_NAME
——————————
VIEWDEPT
CALCSAL

VIEWDEPT
UPDSALARY

PROCEDURE_NAME
——————————
VIEWDETAILS
P
F

PROCEDURE_NAME
——————————

SPVIEWEMPS

PROCEDURE_NAME
——————————
SPADDEMP
CALCULATEREVISEDSAL
SPDELETEEMP
SPVIEWEMP
SPHELLO

GETEMPS

PROCEDURE_NAME
——————————

74 rows selected.

SQL>
SQL> SELECT Text FROM User_Source WHERE Name =’SPDEMO1′ ORDER BY Line;

TEXT
——————————————————————————–
PROCEDURE spDemo1
AS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Hello’);
END spDemo1;

SQL> CREATE OR REPLACE PROCEDURE spDemo1
2 AS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(‘Helloworld’);
5 END spDemo1;
6 /

Procedure created.

SQL> SELECT Text FROM User_Source WHERE Name =’SPDEMO1′ ORDER BY Line;

TEXT
——————————————————————————–
PROCEDURE spDemo1
AS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Helloworld’);
END spDemo1;

SQL> CREATE OR REPLACE PROCEDURE spAdd
2 AS
3 result NUMBER;
4 BEGIN
5 result:=10+10;
6 DBMS_OUTPUT.PUT_LINE(result);
7 END spAdd;
8 /

Procedure created.

SQL> exec spAdd
20

PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE PROCEDURE spNum
2 AS
3 num NUMBER;
4 BEGIN
5 num:=10;
6 IF num>0 THEN
7 DBMS_OUTPUT.PUT_LINE(‘Positive NUMBER’);
8 ELSE
9 DBMS_OUTPUT.PUT_LINE(‘Negative NUMBER’);
10 END IF;
11 END spNum;
12 /

Procedure created.

SQL> exec spNum
Positive NUMBER

PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE PROCEDURE spNum(num IN NUMBER)
2 AS
3 BEGIN
4 IF num>0 THEN
5 DBMS_OUTPUT.PUT_LINE(‘Positive NUMBER’);
6 ELSE
7 DBMS_OUTPUT.PUT_LINE(‘Negative NUMBER’);
8 END IF;
9 END spNum;
10 /

Procedure created.

SQL> exec spNum(30);
Positive NUMBER

PL/SQL procedure successfully completed.

SQL> exec spNum(-30);
Negative NUMBER

PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE PROCEDURE spAdd(num1 IN NUMBER,num2 IN NUMBER,num3 OUT NUMBER)
2 AS
3 BEGIN
4 num3:=num1+num2;
5 END;
6 /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE spAdd(num1 IN NUMBER,num2 IN NUMBER,num3 OUT NUMBER)
2 AS
3 BEGIN
4 num3:=num1+num2;
5 END;
6 /

Procedure created.

SQL> DECLARE
2 result NUMBER;
3 BEGIN
4 spAdd(4,5,result);
5 DBMS_OUTPUT.PUT_LINE(result);
6 END;
7 /
9

PL/SQL procedure successfully completed.

SQL> VARIABLE result NUMBER;
SQL> execute spAdd(7,3,:result);

PL/SQL procedure successfully completed.

SQL> print result

RESULT
———-
10

1 DECLARE
2 salno NUMBER;
3 BEGIN
4 salno:=&salno;
5 spEmp(&empno,salno);
6 DBMS_OUTPUT.PUT_LINE(salno);
7* END;
SQL> /
Enter value for salno: 1250
old 4: salno:=&salno;
new 4: salno:=1250;
Enter value for empno: 7521
old 5: spEmp(&empno,salno);
new 5: spEmp(7521,salno);
375

PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE PROCEDURE spEmp
2 (nEmpno IN employee.empno%TYPE,
3 nSal IN OUT NUMBER)
4 AS
5 nMinSal NUMBER;
6 BEGIN
7 SELECT min(sal) INTO nMinSal FROM employee;
8 IF nSal DECLARE
2 salno NUMBER;
3 BEGIN
4 salno:=&salno;
5 spEmp(&empno,salno);
6 DBMS_OUTPUT.PUT_LINE(salno);
7 END;
8 /
Enter value for salno: 1250
old 4: salno:=&salno;
new 4: salno:=1250;
Enter value for empno: 7521
old 5: spEmp(&empno,salno);
new 5: spEmp(7521,salno);
1625

PL/SQL procedure successfully completed.

SQL> CREATE OR REPLACE FUNCTION fnLength(str VARCHAR2)
2 RETURN NUMBER
3 AS
4 len NUMBER;
5 BEGIN
6 len:=LENGTH(str);
7 RETURN len;
8 END fnLength;
9 /

Function created.

SQL> SELECT fnLength(‘Test’) FROM dual;

FNLENGTH(‘TEST’)
—————-
4
SQL> VARIABLE len NUMBER
SQL> execute :len:=fnLength(‘Test’);

PL/SQL procedure successfully completed.

SQL> print len

LEN
———-
4

SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(fnLength(‘TEst’));
3 END;
4 /
4

PL/SQL procedure successfully completed.

SQL> spool off

pl/sql twod3

SQL> DECLARE
2 emp employee%ROWTYPE;
3 BEGIN
4 SELECT * INTO emp FROM employee WHERE deptno=77;
5 END;
6 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

SQL> DECLARE
2 emp employee%ROWTYPE;
3 BEGIN
4 SELECT * INTO emp FROM employee WHERE deptno=77;
5 EXCEPTION
6 WHEN NO_DATA_FOUND THEN
7 DBMS_OUTPUT.PUT_LINE(‘No employees exits’);
8 END;
9 /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
No employees exits

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 emp employee%ROWTYPE;
3 BEGIN
4 SELECT * INTO emp FROM employee WHERE deptno=10;
5 EXCEPTION
6 WHEN NO_DATA_FOUND THEN
7 DBMS_OUTPUT.PUT_LINE(‘No employees exits’);
8* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 emp employee%ROWTYPE;
3 BEGIN
4 SELECT * INTO emp FROM employee WHERE deptno=10;
5 EXCEPTION
6 WHEN NO_DATA_FOUND THEN
7 DBMS_OUTPUT.PUT_LINE(‘No employees exits’);
8 WHEN TOO_MANY_ROWS THEN
9 DBMS_OUTPUT.PUT_LINE(‘More than 1 employee are working in this department’);
10* END;
SQL> /
More than 1 employee are working in this department

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 emp employee%ROWTYPE;
3 result NUMBER;
4 BEGIN
5 SELECT * INTO emp FROM employee WHERE deptno=10;
6 result:=emp.sal/0;
7 EXCEPTION
8 WHEN NO_DATA_FOUND THEN
9 DBMS_OUTPUT.PUT_LINE(‘No employees exits’);
10 WHEN TOO_MANY_ROWS THEN
11 DBMS_OUTPUT.PUT_LINE(‘More than 1 employee are working in this department’);
12 WHEN OTHERS THEN
13 DBMS_OUTPUT.PUT_LINE(‘Other exception occurs’);
14* END;
SQL> /
More than 1 employee are working in this department

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 emp employee%ROWTYPE;
3 result NUMBER;
4 BEGIN
5 SELECT * INTO emp FROM employee WHERE deptno=10 and empno=7698;
6 result:=emp.sal/0;
7 EXCEPTION
8 WHEN NO_DATA_FOUND THEN
9 DBMS_OUTPUT.PUT_LINE(‘No employees exits’);
10 WHEN TOO_MANY_ROWS THEN
11 DBMS_OUTPUT.PUT_LINE(‘More than 1 employee are working in this department’);
12 WHEN OTHERS THEN
13 DBMS_OUTPUT.PUT_LINE(‘Other exception occurs’);
14 DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
15* END;
SQL> /
Other exception occurs
-1476ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

SQL> desc errorlog;
Name Null? Type
—————————————– ——– —————————-
ERRORNO NUMBER
DESCRIPTION VARCHAR2(80)
ERRORDATE DATE

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 emp employee%ROWTYPE;
3 result NUMBER;
4 BEGIN
5 SELECT * INTO emp FROM employee WHERE deptno=10 and empno=7698;
6 result:=emp.sal/0;
7 EXCEPTION
8 WHEN NO_DATA_FOUND THEN
9 DBMS_OUTPUT.PUT_LINE(‘No employees exits’);
10 WHEN TOO_MANY_ROWS THEN
11 DBMS_OUTPUT.PUT_LINE(‘More than 1 employee are working in this department’);
12 WHEN OTHERS THEN
13 DBMS_OUTPUT.PUT_LINE(‘Other exception occurs’);
14 DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
15 INSERT INTO errorlog VALUES(22,SQLERRM,sysdate);
16* END;
SQL> /
INSERT INTO errorlog VALUES(22,SQLERRM,sysdate);
*
ERROR at line 15:
ORA-06550: line 15, column 35:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 15, column 4:
PL/SQL: SQL Statement ignored

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 emp employee%ROWTYPE;
3 result NUMBER;
4 scode VARCHAR2(20);
5 msg VARCHAR2(40);
6 BEGIN
7 SELECT * INTO emp FROM employee WHERE deptno=10 and empno=7698;
8 result:=emp.sal/0;
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 DBMS_OUTPUT.PUT_LINE(‘No employees exits’);
12 WHEN TOO_MANY_ROWS THEN
13 DBMS_OUTPUT.PUT_LINE(‘More than 1 employee are working in this department’);
14 WHEN OTHERS THEN
15 DBMS_OUTPUT.PUT_LINE(‘Other exception occurs’);
16 DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
17 scode:=SQLCODE;
18 msg:=SQLERRM;
19 INSERT INTO errorlog VALUES(22,msg,sysdate);
20* END;
SQL> /
Other exception occurs
-1476ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

SQL> SELECT * FROM errorlog;

ERRORNO
———-
DESCRIPTION
——————————————————————————–
ERRORDATE
———
22
ORA-01476: divisor is equal to zero
26-AUG-14

1
rr
21-MAY-14

ERRORNO
———-
DESCRIPTION
——————————————————————————–
ERRORDATE
———

-1422
ORA-01422: exact fetch returns more than requested number of rows
21-MAY-14

SQL> DECLARE
2 CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
3 vcEname employee.ename%TYPE;
4 BEGIN
5 OPEN cname;
6 FETCH cname INTO vcEname;
7 LOOP
8 DBMS_OUTPUT.PUT_LINE(vcEname);
9 EXIT WHEN cname%NOTFOUND;
10 FETCH cname INTO vcEname;
11 END LOOP;
12 END;
13 /
Enter value for num: 10
old 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
new 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=10;
king
blake
clark
clark

PL/SQL procedure successfully completed.

SQL> /
Enter value for num: 33
old 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
new 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=33;

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
3 vcEname employee.ename%TYPE;
4 BEGIN
5 OPEN cname;
6 FETCH cname INTO vcEname;
7 IF cname%ROWCOUNT>0
8 THEN
9 LOOP
10 DBMS_OUTPUT.PUT_LINE(vcEname);
11 EXIT WHEN cname%NOTFOUND;
12 FETCH cname INTO vcEname;
13 END LOOP;
14 ELSE
15 DBMS_OUTPUT.PUT_LINE(‘msg’);
16 END IF;
17* END;
SQL> /
Enter value for num: 77
old 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
new 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=77;
msg

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
3 vcEname employee.ename%TYPE;
4 no_data EXCEPTION;
5 BEGIN
6 OPEN cname;
7 FETCH cname INTO vcEname;
8 IF cname%ROWCOUNT>0
9 THEN
10 LOOP
11 DBMS_OUTPUT.PUT_LINE(vcEname);
12 EXIT WHEN cname%NOTFOUND;
13 FETCH cname INTO vcEname;
14 END LOOP;
15 ELSE
16 –DBMS_OUTPUT.PUT_LINE(‘msg’);
17 RAISE no_data;
18 END IF;
19 EXCEPTION
20 WHEN no_data THEN
21 DBMS_OUTPUT.PUT_LINE(‘No data exists’);
22* END;
SQL> /
Enter value for num: 88
old 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
new 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=88;
No data exists

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
3 vcEname employee.ename%TYPE;
4 no_data EXCEPTION;
5 PRAGMA EXCEPTION_INIT(no_data,-20888);
6 BEGIN
7 OPEN cname;
8 FETCH cname INTO vcEname;
9 IF cname%ROWCOUNT>0
10 THEN
11 LOOP
12 DBMS_OUTPUT.PUT_LINE(vcEname);
13 EXIT WHEN cname%NOTFOUND;
14 FETCH cname INTO vcEname;
15 END LOOP;
16 ELSE
17 –DBMS_OUTPUT.PUT_LINE(‘msg’);
18 –RAISE no_data;
19 RAISE_APPLICATION_ERROR(-20888,’No data exists’);
20 END IF;
21 EXCEPTION
22 WHEN no_data THEN
23 DBMS_OUTPUT.PUT_LINE(‘No data exists’);
24 DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
25* END;
SQL> /
Enter value for num: 77
old 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=&num;
new 2: CURSOR cname IS SELECT ename FROM employee WHERE deptno=77;
No data exists
-20888ORA-20888: No data exists

PL/SQL procedure successfully completed.