pl/sql TWOD1

SQL> DECLARE
2 nSal NUMBER;
3 nMinSal NUMBER;
4 BEGIN
5 SELECT sal INTO nSal FROM employee WHERE empno=7698;
6 SELECT min(sal) INTO nMinSal FROM employee;
7 IF nSal set serveroutput on
SQL> /

PL/SQL procedure successfully completed.

SQL> DECLARE
2 vcAddress VARCHAR2(30);
3 BEGIN
4 sELECT Student_Address INTO vcAddress FROM student_masters
5 WHERE Student_Code =1003;
6 IF vcAddress IS NULL
7 THEN
8 UPDATE student_masters SET student_address=’Chennai’
9 WHERE student_code=1003;
10 ELSIF vcAddress=’Chennai’
11 THEN
12 DBMS_OUTPUT.PUT_LINE(‘Residing in chennai’);
13 ELSE
14 DBMS_OUTPUT.PUT_LINE(‘Residing in other city’);
15 END IF;
16 END;
17 /

PL/SQL procedure successfully completed.

SQL> sELECT Student_Address FROM student_masters WHERE Student_Code =1003;

STUDENT_ADDRESS
——————————————————————————–

Chennai

SQL>
SQL> BEGIN
2 LOOP
3 DBMS_OUTPUT.PUT_LINE(‘test’);
4 END LOOP;
5 END;
6 /
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
test
BEGIN
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at “SYS.DBMS_OUTPUT”, line 32
ORA-06512: at “SYS.DBMS_OUTPUT”, line 97
ORA-06512: at “SYS.DBMS_OUTPUT”, line 112
ORA-06512: at line 3

SQL> BEGIN
2 LOOP
3 DBMS_OUTPUT.PUT_LINE(‘test’);
4 EXIT;
5 END LOOP;
6 END;
7 .
SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 LOOP
3 DBMS_OUTPUT.PUT_LINE(‘test’);
4 EXIT;
5 END LOOP;
6* END;
SQL> /
test

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 nNum NUMBER:=1;
3 BEGIN
4 LOOP
5 DBMS_OUTPUT.PUT_LINE(‘test’);
6 nNum:=nNum+1;
7 EXIT WHEN nNum=5;
8 END LOOP;
9* END;
SQL> /
test
test
test
test

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 nNum NUMBER:=1;
3 BEGIN
4 LOOP
5 EXIT WHEN nNum=5;
6 DBMS_OUTPUT.PUT_LINE(‘test’);
7 nNum:=nNum+1;
8 END LOOP;
9* END;
10 /
test
test
test
test

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 nNum NUMBER:=1;
3 BEGIN
4 LOOP
5 EXIT WHEN nNum /

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 nNum NUMBER:=1;
3 BEGIN
4 LOOP
5 EXIT WHEN nNum>5;
6 DBMS_OUTPUT.PUT_LINE(‘test’);
7 nNum:=nNum+1;
8 END LOOP;
9* END;
SQL> /
test
test
test
test
test

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 nNum NUMBER:=1;
3 BEGIN
4 LOOP
5 DBMS_OUTPUT.PUT_LINE(‘test’);
6 nNum:=nNum+1;
7 EXIT WHEN nNum>5;
8 END LOOP;
9* END;
10 /
test
test
test
test
test

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 nNum NUMBER:=1;
3 BEGIN
4 WHILE nNUM /
test
test
test
test

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 FOR index1 IN 1..10
3 LOOP
4 DBMS_OUTPUT.PUT_LINE(‘test’);
5 END LOOP;
6* END;
SQL> /
test
test
test
test
test
test
test
test
test
test

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 FOR index1 IN REVERSE 5..1
3 LOOP
4 DBMS_OUTPUT.PUT_LINE(‘test’||index1);
5 END LOOP;
6* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 FOR index1 IN REVERSE 5..1
3 LOOP
4 DBMS_OUTPUT.PUT_LINE(‘test’);
5 END LOOP;
6* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 FOR index1 IN REVERSE 1..5
3 LOOP
4 DBMS_OUTPUT.PUT_LINE(‘test’);
5 END LOOP;
6* END;
SQL> /
test
test
test
test
test

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 FOR index1 IN REVERSE 1..5
3 LOOP
4 DBMS_OUTPUT.PUT_LINE(‘test’||index1);
5 END LOOP;
6* END;
SQL> /
test5
test4
test3
test2
test1

PL/SQL procedure successfully completed.

SQL> DECLARE
2 nOuter NUMBER:=10;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(‘Outer Block’);
5 DBMS_OUTPUT.PUT_LINE(nOuter);
6 DECLARE
7 nInner NUMBER:=10;
8 BEGIN
9 DBMS_OUTPUT.PUT_LINE(‘Inner Block’);
10 DBMS_OUTPUT.PUT_LINE(nInner);
11 END;
12 END;
13 /
Outer Block
10
Inner Block
10

PL/SQL procedure successfully completed.

SQL> DECLARE
2 nNum NUMBER:=10;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(‘Outer Block’);
5 DBMS_OUTPUT.PUT_LINE(nNum);
6 DECLARE
7 nNum NUMBER:=20;
8 BEGIN
9 DBMS_OUTPUT.PUT_LINE(‘Inner Block’);
10 DBMS_OUTPUT.PUT_LINE(nNum);
11 END;
12 END;
13 /
Outer Block
10
Inner Block
20

PL/SQL procedure successfully completed.

SQL> DECLARE
2 nNum NUMBER:=10;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(‘Outer Block’);
5 DBMS_OUTPUT.PUT_LINE(nNum);
6 DECLARE
7 nNum NUMBER:=20;
8 BEGIN
9 DBMS_OUTPUT.PUT_LINE(‘Inner Block’);
10 DBMS_OUTPUT.PUT_LINE(nNum);
11 END;
12 DBMS_OUTPUT.PUT_LINE(nNum);
13 END;
14
15 /
Outer Block
10
Inner Block
20
10

PL/SQL procedure successfully completed.

SQL> <>
2 DECLARE
3 nNum NUMBER:=10;
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE(‘Outer Block’);
6 DBMS_OUTPUT.PUT_LINE(nNum);
7 <>
8 DECLARE
9 nNum NUMBER:=20;
10 BEGIN
11 DBMS_OUTPUT.PUT_LINE(‘Inner Block’);
12 DBMS_OUTPUT.PUT_LINE(nNum);
13 DBMS_OUTPUT.PUT_LINE(OUTER.nNum);
14 END;
15 DBMS_OUTPUT.PUT_LINE(nNum);
16 END;
17 /
Outer Block
10
Inner Block
20
10
10

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 <>
2 DECLARE
3 nNum NUMBER:=10;
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE(‘Outer Block’);
6 DBMS_OUTPUT.PUT_LINE(nNum);
7 <>
8 DECLARE
9 nNum NUMBER:=20;
10 BEGIN
11 DBMS_OUTPUT.PUT_LINE(‘Inner Block’);
12 DBMS_OUTPUT.PUT_LINE(nNum);
13 DBMS_OUTPUT.PUT_LINE(OUTER.nNum);
14 DBMS_OUTPUT.PUT_LINE(INNER.nNum);
15 END;
16* END;
17 /
Outer Block
10
Inner Block
20
10
20

PL/SQL procedure successfully completed.

SQL> spool off

PL/Sql 3

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(‘Welcome’);
3 END;
4 /
Welcome

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(‘Welcome’||’ to PL/SQL session’);
3 END;
4 /
Welcome to PL/SQL session

PL/SQL procedure successfully completed.

SQL>

SQL> DECLARE
2 num NUMBER;
3 BEGIN
4 num:=25;
5 DBMS_OUTPUT.PUT_LINE(‘Result is ‘||num);
6 END;
7 /
Result is 25

PL/SQL procedure successfully completed.

SQL> DECLARE
2 num NUMBER:=20;
3 BEGIN
4 num:=25;
5 DBMS_OUTPUT.PUT_LINE(‘Result is ‘||num);
6 END;
7 /
Result is 25

PL/SQL procedure successfully completed.

SQL> DECLARE
2 num NUMBER:=20;
3 doj DATE DEFAULT SYSDATE;
4 BEGIN
5 –num:=25;
6 DBMS_OUTPUT.PUT_LINE(‘Result is ‘||num);
7 DBMS_OUTPUT.PUT_LINE(‘Result is ‘||doj);
8 END;
9 /
Result is 20
Result is 25-AUG-14

PL/SQL procedure successfully completed.

SQL> DECLARE
2 vcName VARCHAR2(20) NOT NULL;
3 id NUMBER;
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE(vcName||id);
6 END;
7 /
vcName VARCHAR2(20) NOT NULL;
*
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00218: a variable declared NOT NULL must have an initialization assignment

SQL> DECLARE
2 vcName VARCHAR2(20) NOT NULL:=’Test’;
3 id NUMBER;
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE(vcName||id);
6 END;
7 /
Test

PL/SQL procedure successfully completed.

SQL> DECLARE
2 vcName VARCHAR2(20) NOT NULL;
3 id NUMBER;
4 BEGIN
5 vcName:=’sample’;
6 DBMS_OUTPUT.PUT_LINE(vcName||id);
7 END;
8 /
vcName VARCHAR2(20) NOT NULL;
*
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00218: a variable declared NOT NULL must have an initialization assignment

SQL> DECLARE
2 vcName VARCHAR2(20) NOT NULL DEFAULT ‘Example’;
3 BEGIN
4 vcName:=’sample’;
5 DBMS_OUTPUT.PUT_LINE(vcName);
6 END;
7 /
sample

PL/SQL procedure successfully completed.

SQL> DECLARE
2 vcName VARCHAR2(20) NOT NULL DEFAULT ‘Example’;
3 nPF CONSTANT NUMBER;
4 BEGIN
5 vcName:=’sample’;
6 DBMS_OUTPUT.PUT_LINE(vcName);
7 END;
8 /
nPF CONSTANT NUMBER;
*
ERROR at line 3:
ORA-06550: line 3, column 2:
PLS-00322: declaration of a constant ‘NPF’ must contain an initialization
assignment
ORA-06550: line 3, column 6:
PL/SQL: Item ignored

SQL> DECLARE
2 vcName VARCHAR2(20) NOT NULL DEFAULT ‘Example’;
3 nPF CONSTANT NUMBER:=23;
4 BEGIN
5 vcName:=’sample’;
6 DBMS_OUTPUT.PUT_LINE(vcName);
7 END;
8 /
sample

PL/SQL procedure successfully completed.

SQL> DECLARE
2 vcName VARCHAR2(20) NOT NULL DEFAULT ‘Example’;
3 nPF CONSTANT NUMBER;
4 BEGIN
5 vcName:=’sample’;
6 nPF:=25;
7 DBMS_OUTPUT.PUT_LINE(vcName||nPF);
8 END;
9 /
nPF CONSTANT NUMBER;
*
ERROR at line 3:
ORA-06550: line 3, column 2:
PLS-00322: declaration of a constant ‘NPF’ must contain an initialization
assignment
ORA-06550: line 3, column 6:
PL/SQL: Item ignored
ORA-06550: line 6, column 2:
PLS-00363: expression ‘NPF’ cannot be used as an assignment target
ORA-06550: line 6, column 2:
PL/SQL: Statement ignored

SQL> DECLARE
2 vcName VARCHAR2(20) NOT NULL DEFAULT ‘Example’;
3 nPF CONSTANT NUMBER:=20;
4 BEGIN
5 vcName:=’sample’;
6 nPF:=25;
7 DBMS_OUTPUT.PUT_LINE(vcName||nPF);
8 END;
9 /
nPF:=25;
*
ERROR at line 6:
ORA-06550: line 6, column 2:
PLS-00363: expression ‘NPF’ cannot be used as an assignment target
ORA-06550: line 6, column 2:
PL/SQL: Statement ignored

SQL> DECLARE
2 vcName VARCHAR2(20);
3 vcLoc VARCHAR2(20);
4 nDeptno NUMBER;
5 BEGIN
6 vcName:=’&name’;
7 vcLoc:=’&loc’;
8 nDeptno:=93;
9 INSERT INTO dept VALUES(94,vcName,vcLoc);
10 END;
11 /
Enter value for name: Sales
old 6: vcName:=’&name’;
new 6: vcName:=’Sales’;
Enter value for loc: CHN
old 7: vcLoc:=’&loc’;
new 7: vcLoc:=’CHN’;

PL/SQL procedure successfully completed.

SQL> SELECT dname,loc FROM dept WHERE deptno=10;

DNAME LOC
————– ————-
ACCOUNTING NEWYORK

SQL> DECLARE
2 vcName VARCHAR2(20);
3 vcLoc VARCHAR2(20);
4 BEGIN
5 SELECT dname,loc INTO vcName,vcLoc FROM dept WHERE deptno=10;
6 DBMS_OUTPUT.PUT_LINE(‘Department Name: ‘||vcName);
7 DBMS_OUTPUT.PUT_LINE(‘Department Location: ‘||vcLoc);
8 END;
9 /
Department Name: ACCOUNTING
Department Location: NEWYORK

PL/SQL procedure successfully completed.

SQL> DECLARE
2 vcName VARCHAR2(20);
3 vcLoc VARCHAR2(20);
4 nDeptno NUMBER;
5 BEGIN
6 SELECT * INTO nDeptno,vcName,vcLoc FROM dept WHERE deptno=10;
7 DBMS_OUTPUT.PUT_LINE(‘Department No: ‘||nDeptno);
8 DBMS_OUTPUT.PUT_LINE(‘Department Name: ‘||vcName);
9 DBMS_OUTPUT.PUT_LINE(‘Department Location: ‘||vcLoc);
10 END;
11 /
Department No: 10
Department Name: ACCOUNTING
Department Location: NEWYORK

PL/SQL procedure successfully completed.

SQL> DECLARE
2 vcName VARCHAR2(20);
3 vcLoc VARCHAR2(20);
4 BEGIN
5 SELECT dname,loc FROM dept WHERE deptno=10;
6 DBMS_OUTPUT.PUT_LINE(‘Department Name: ‘||vcName);
7 DBMS_OUTPUT.PUT_LINE(‘Department Location: ‘||vcLoc);
8 END;
9 /
SELECT dname,loc FROM dept WHERE deptno=10;
*
ERROR at line 5:
ORA-06550: line 5, column 2:
PLS-00428: an INTO clause is expected in this SELECT statement

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

SQL> DECLARE
2 vcName dept.dname%TYPE;
3 vcLoc dept.loc%TYPE;
4 BEGIN
5 SELECT dname,loc INTO vcName,vcLoc FROM dept WHERE deptno=10;
6 DBMS_OUTPUT.PUT_LINE(‘Department Name: ‘||vcName);
7 DBMS_OUTPUT.PUT_LINE(‘Department Location: ‘||vcLoc);
8 END;
9 /
Department Name: ACCOUNTING
Department Location: NEWYORK

PL/SQL procedure successfully completed.

SQL> DECLARE
2 deptDetail dept%ROWTYPE;
3 BEGIN
4 SELECT * INTO deptDetail FROM dept WHERE deptno=10;
5 DBMS_OUTPUT.PUT_LINE(deptDetail.deptno);
6 DBMS_OUTPUT.PUT_LINE(deptDetail.dname);
7 DBMS_OUTPUT.PUT_LINE(deptDetail.loc);
8 END;
9 /
10
ACCOUNTING
NEWYORK

PL/SQL procedure successfully completed.

SQL> DECLARE
2 SUBTYPE vc IS VARCHAR2;
3 vcName vc(20);
4 vcLoc vc(20);
5 BEGIN
6 SELECT dname,loc FROM dept WHERE deptno=10;
7 DBMS_OUTPUT.PUT_LINE(‘Department Name: ‘||vcName);
8 DBMS_OUTPUT.PUT_LINE(‘Department Location: ‘||vcLoc);
9 END;
10 /
SELECT dname,loc FROM dept WHERE deptno=10;
*
ERROR at line 6:
ORA-06550: line 6, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 SUBTYPE vc IS VARCHAR2;
3 vcName vc(20);
4 vcLoc vc(20);
5 BEGIN
6 SELECT dname,loc INTO vcName,vcLoc FROM dept WHERE deptno=10;
7 DBMS_OUTPUT.PUT_LINE(‘Department Name: ‘||vcName);
8 DBMS_OUTPUT.PUT_LINE(‘Department Location: ‘||vcLoc);
9* END;
SQL> /
Department Name: ACCOUNTING
Department Location: NEWYORK

PL/SQL procedure successfully completed.

SQL> DECLARE
2 SUBTYPE deptD IS dept%ROWTYPE;
3 deptDetail deptD;
4 BEGIN
5 SELECT * INTO deptDetail FROM dept WHERE deptno=10;
6 DBMS_OUTPUT.PUT_LINE(deptDetail.deptno);
7 DBMS_OUTPUT.PUT_LINE(deptDetail.dname);
8 DBMS_OUTPUT.PUT_LINE(deptDetail.loc);
9 END;
10 /
10
ACCOUNTING
NEWYORK

PL/SQL procedure successfully completed.

SQL> DECLARE
2 vcEname employee.ename%TYPE;
3 vcDname dept.dname%TYPE;
4 BEGIN
5 SELECT ename,dname INTO vcEname,vcDname FROM employee e,dept d
6 WHERE d.deptno=e.deptno;
7 DBMS_OUTPUT.PUT_LINE(vcEname);
8 DBMS_OUTPUT.PUT_LINE(vcDname);
9 END;
10 /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 vcEname employee.ename%TYPE;
3 vcDname dept.dname%TYPE;
4 BEGIN
5 SELECT ename,dname INTO vcEname,vcDname FROM employee e,dept d
6 WHERE d.deptno=e.deptno and empno=7689;
7 DBMS_OUTPUT.PUT_LINE(vcEname);
8 DBMS_OUTPUT.PUT_LINE(vcDname);
9* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 vcEname employee.ename%TYPE;
3 vcDname dept.dname%TYPE;
4 BEGIN
5 SELECT ename,dname INTO vcEname,vcDname FROM employee e,dept d
6 WHERE d.deptno=e.deptno and empno=7698;
7 DBMS_OUTPUT.PUT_LINE(vcEname);
8 DBMS_OUTPUT.PUT_LINE(vcDname);
9* END;
SQL> /
blake
ACCOUNTING

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 DECLARE
2 TYPE details IS RECORD(
3 vcEname employee.ename%TYPE,
4 vcDname dept.dname%TYPE);
5 rec details;
6 BEGIN
7 SELECT ename,dname INTO rec FROM employee e,dept d
8 WHERE d.deptno=e.deptno and empno=7698;
9 DBMS_OUTPUT.PUT_LINE(rec.vcEname);
10 DBMS_OUTPUT.PUT_LINE(rec.vcDname);
11* END;
SQL> /
blake
ACCOUNTING

PL/SQL procedure successfully completed.

SQL> DECLARE
2 TYPE EmpTable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 Emp_tab EmpTable;
4 BEGIN
5 Emp_tab(1):=200;
6 Emp_tab(2):=300;
7 DBMS_OUTPUT.PUT_LINE(Emp_tab(1) || ‘ ‘|| Emp_tab(2));
8 END;
9 /
200 300

PL/SQL procedure successfully completed.

SQL> spool off

Pl/Sql D2

SQL> BEGIN
2 UPDATE employee SET sal=sal+100;
3 DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
4 END;
5 /
9
PL/SQL procedure successfully completed.
SQL> DECLARE
2 CURSOR cName IS SELECT * FROM dept;
3 deptDetail dept%rowtype;
4 BEGIN
5 OPEN cName; — Opening a cursor
6 FETCH cName INTO deptDetail;
7 CLOSE cName; –Closing cursor
8 DBMS_OUTPUT.PUT_LINE(deptDetail.dname);
9 END;
10 /
Test
PL/SQL procedure successfully completed.
SQL> DECLARE
2 CURSOR cName IS SELECT * FROM dept;
3 deptDetail dept%rowtype;
4 BEGIN
5 IF cname%ISOPEN
6 THEN
7 null;
8 ELSE
9 OPEN cname;
10 END IF;
11 FETCH cname INTO deptDetail;
12 LOOP
13 FETCH cname INTO deptDetail;
14 EXIT WHEN cname%NOTFOUND;
15 DBMS_OUTPUT.PUT_LINE(deptDetail.dname);
16 END LOOP;
17 END;
18 /
HR
nb
gh
ACCOUNTING
RESEARCH
SALES
OPERATIONS
dd
ee
ee
tt
ee
s
ee
sdf
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR cName IS SELECT * FROM dept;
3 deptDetail dept%rowtype;
4 BEGIN
5 IF cname%ISOPEN
6 THEN
7 null;
8 ELSE
9 OPEN cname;
10 END IF;
11 FETCH cname INTO deptDetail;
12 LOOP
13 DBMS_OUTPUT.PUT_LINE(deptDetail.dname);
14 FETCH cname INTO deptDetail;
15 EXIT WHEN cname%NOTFOUND;
16 END LOOP;
17 CLOSE cname;
18* END;
SQL> /
Test
HR
nb
gh
ACCOUNTING
RESEARCH
SALES
OPERATIONS
dd
ee
ee
tt
ee
s
ee
sdf
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR cName IS SELECT * FROM dept;
3 deptDetail dept%rowtype;
4 BEGIN
5 IF cname%ISOPEN
6 THEN
7 null;
8 ELSE
9 OPEN cname;
10 END IF;
11 FETCH cname INTO deptDetail;
12 WHILE cname%NOTFOUND
13 LOOP
14 DBMS_OUTPUT.PUT_LINE(deptDetail.dname);
15 FETCH cname INTO deptDetail;
16 END LOOP;
17 CLOSE cname;
18* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR cName IS SELECT * FROM dept;
3 deptDetail dept%rowtype;
4 BEGIN
5 IF cname%ISOPEN
6 THEN
7 null;
8 ELSE
9 OPEN cname;
10 END IF;
11 FETCH cname INTO deptDetail;
12 WHILE cname%FOUND
13 LOOP
14 DBMS_OUTPUT.PUT_LINE(deptDetail.dname);
15 FETCH cname INTO deptDetail;
16 END LOOP;
17 CLOSE cname;
18* END;
SQL> /
Test
HR
nb
gh
ACCOUNTING
RESEARCH
SALES
OPERATIONS
dd
ee
ee
tt
ee
s
ee
sdf
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR cName IS SELECT * FROM employee WHERE deptno=&no;
3 empDetail employee%rowtype;
4 BEGIN
5 IF cname%ISOPEN
6 THEN
7 null;
8 ELSE
9 OPEN cname;
10 END IF;
11 FETCH cname INTO empDetail;
12 IF cname%ROWCOUNT>0
13 THEN
14 WHILE cname%FOUND
15 LOOP
16 DBMS_OUTPUT.PUT_LINE(empDetail.ename);
17 FETCH cname INTO empDetail;
18 END LOOP;
19 CLOSE cname;
20 ELSE
21 DBMS_OUTPUT.PUT_LINE(‘Employees not working in this department’);
22 END IF;
23* END;
SQL> /
Enter value for no: 10
old 2: CURSOR cName IS SELECT * FROM employee WHERE deptno=&no;
new 2: CURSOR cName IS SELECT * FROM employee WHERE deptno=10;
king
blake
clark
PL/SQL procedure successfully completed.
SQL> /
Enter value for no: 90
old 2: CURSOR cName IS SELECT * FROM employee WHERE deptno=&no;
new 2: CURSOR cName IS SELECT * FROM employee WHERE deptno=90;
Employees not working in this department
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR cname IS SELECT * FROM dept;
3 BEGIN
4 FOR rec IN cname
5 LOOP
6 DBMS_OUTPUT.PUT_LINE(rec.dname);
7 END LOOP;
8* END;
SQL> /
Test
HR
nb
gh
ACCOUNTING
RESEARCH
SALES
OPERATIONS
dd
ee
ee
tt
ee
s
ee
sdf
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 CURSOR cname IS SELECT ename,dname FROM employee e,dept d WHERE e.deptno=d.deptno;
3 BEGIN
4 FOR rec IN cname
5 LOOP
6 DBMS_OUTPUT.PUT_LINE(rec.dname);
7 DBMS_OUTPUT.PUT_LINE(rec.ename);
8 END LOOP;
9* END;
SQL> /
ACCOUNTING
clark
ACCOUNTING
blake
ACCOUNTING
king
RESEARCH
scott
RESEARCH
jones
SALES
martin
SALES
ward
SALES
allen
PL/SQL procedure successfully completed.
SQL> spool off

Python Unlimited Anaconda

Thanks for joining me!

1.

print("Please Enter your name")
x = input()
print("Hello,", x)

==========================================================================================================================
2.

print("Enter two numbers")
x, y =map(int,input().split()) #x, y = [int(x) for x in input().split()] can also be taken
suma = x+y
if(suma>0):
    print("Pos")
else:
    print("Negative")

==========================================================================================================================
3.

x= 1234
a = input("Enter the 4 digit PIN:\n")

num = int(a)

while(num is not x ):
    print("Incorrect PIN: Try Again")
    a = input()
    num = int(a)
    if(num == x):
        print("Correct")
        break
    else:
        continue

==============================================================================================================================
4.

FirstName = input("Enter your first name\n")
LastName = input("Enter your Last Name\n")
print("FullName:  "+FirstName+"."+LastName)

==========================================================================================================================
5.

name = input("Enter a string\n")
print(name.swapcase()) 

-----------------------------------------------------------------------------------------------------------------------

#the following program is for toggle case #chutzpah from StackOverflow
name=input("Enter your Chutzpah naam")
for i in name:
    if i.isupper():
        print( i.lower(),sep='',end='')
    else:
        print( i.upper(),sep='',end='')

    
===========================================================================================================================    
6.

a = list(map(str, input("Say\n").split()))
print(a)
b = sorted(a, key = len)

print("The longest %s",(b[-1], len(b[-1])))
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
a = list(map(int, input("Give the list\n").split()))
b=1
c=0

for i in a:
    b *= i #b has to be 0(zero) as anything multiplied by zero is zero
    c += i #have to initialize
print(a)
print(b)
print(c)




===========================================================================================================================
8.


a = list(map(int, input("list 1\n").split()))
b = list(map(int, input("list 2\n").split()))
x=0
print(any(true for x in a for x in b))
        
---------------------------------------------------------------------------------------           
            
a = list(map(int, input("list 1\n").split()))
b = list(map(int, input("list 2\n").split()))
c = []
for i in a:
    if i in b:
        c.append(i)
if c:
    print("These are the letters",c)
else:
    print("Not similar")
        

==============================================================================================================
9.


a = list(map(int, input("list").split()))
for i in a:
        print("*" *i)

Good company in a journey makes the way seem shorter. — Izaak Walton

post