Order By clause on Alphanumeric data

SQL> select * from student;

data
----------------------
1
a
ssa
b
11
3
99

7 rows selected.

SQL> select * from student order by data desc;

data
----------------------
ssa
b
a
99
3
11
1

7 rows selected.

Note: Comparison starts with only first character of each row of a column
(for alphabets it takes ASCII values eg: for a 65,b 66,.....)
         

Displaying number of records without using count function

SQL> SELECT MAX(SAL) FROM (SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) SAL FROM EMP) EMPCOUNT;

MAX(SAL)
--------
      14

Note: You can use any column name of your table

Displaying N th record from Table

SQL> select * from(select rownum rn,e.* from emp e) where rn=&rn;
Enter value for rn: 1
old   1: select * from(select rownum rn,e.* from emp e) where rn=&rn
new   1: select * from(select rownum rn,e.* from emp e) where rn=1

   RN EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO
----- ----- ---------- --------- ----- --------- ----- ----- ------
    1  7369 SMITH      CLERK      7902 17-DEC-80   800           20

SQL> /
Enter value for rn: 5
old   1: select * from(select rownum rn,e.* from emp e) where rn=&rn
new   1: select * from(select rownum rn,e.* from emp e) where rn=5

   RN EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO
----- ----- ---------- --------- ----- --------- ----- ----- ------
    5  7654 MARTIN     SALESMAN   7698 28-SEP-81  1250  1400     30

SQL> /
Enter value for rn: 14
old   1: select * from(select rownum rn,e.* from emp e) where rn=&rn
new   1: select * from(select rownum rn,e.* from emp e) where rn=14

   RN EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO
----- ----- ---------- --------- ----- --------- ----- ----- ------
   14  7934 MILLER     CLERK      7782 23-JAN-82  1300           10


To display 3rd record from emp table

SQL> select * from emp where rownum<=3 minus select * from emp where rownum<3;

EMPNO ENAME      JOB         MGR HIREDATE    SAL  COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ------

 7521 WARD       SALESMAN   7698 22-FEB-81  1250   500     30

Display numbers 1 to 10 in SQL

SQL> select * from xmltable ('for $i in 1 to 10 return $i');

COLUMN_VALUE
---------------------------------------------------------------------------
1
2
3
4
5
6
7
8
9
10

10 rows selected.

SQL> select level from dual connect by level<=10;

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> select rownum from dual connect by level<=10;

    ROWNUM

----------

         1

         2

         3

         4

         5

         6

         7

         8

         9

        10

10 rows selected.

Packages VS stored procedures

Hi

Packages & stored procedures are database objects.

Packages are having so many features like..!!
Modularity
*************

Packages let you encapsulate logically related types, items, and subprograms in a named PL/SQL module.

Each package is easy to understand, and the interfaces between packages are simple, clear, and well

defined. This aids application development.

Easier Application Design
**************************

When designing an application, all you need initially is the interface information in the package

specs. You can code and compile a spec without its body. Then, stored subprograms that reference the

package can be compiled as well. You need not define the package bodies fully until you are ready to

complete the application.

Information Hiding
*******************

With packages, you can specify which types, items, and subprograms are public (visible and accessible)

or private (hidden and inaccessible). For example, if a package contains four subprograms, three might

be public and one private. The package hides the implementation of the private subprogram so that only

the package (not your application) is affected if the implementation changes. This simplifies

maintenance and enhancement. Also, by hiding implementation details from users, you protect the

integrity of the package.

Added Functionality
********************

Packaged public variables and cursors persist for the duration of a session. They can be shared by all

subprograms that execute in the environment. They let you maintain data across transactions without

storing it in the database.

Better Performance
*******************

When you invoke a packaged subprogram for the first time, the whole package is loaded into memory.

Later calls to related subprograms in the package require no disk I/O.

Packages stop cascading dependencies and avoid unnecessary recompiling. For example, if you change the

body of a packaged function, the database does not recompile other subprograms that invoke the

function; these subprograms only depend on the parameters and return value that are declared in the

spec, so they are only recompiled if the spec changes.

Function Overloading
*****************
you can use same procedure name more than one procedure with diff parameters and their datatypes.

can we create function/procedure in package body without their(function/procedure) declaration in package spec ?

can we create function/procedure in package body without their(function/procedure) declaration in package spec ?

Ans: Yes

CREATE OR REPLACE PACKAGE testperf AS
   FUNCTION pow(i number) RETURN number;
   FUNCTION pow(i number,j number) RETURN number;
END testperf

CREATE OR REPLACE PACKAGE BODY testperf AS
   FUNCTION pow(i number) RETURN number AS
   BEGIN
      RETURN i * i;
   END;
   FUNCTION pow(i number,j number) RETURN number AS
   BEGIN
      RETURN i * j;
   END;
   FUNCTION pow1(i number,j number) RETURN number AS
   BEGIN
      RETURN i * j;
   END
 
END;

(if we declare function/procedure in package spec it must be defined in package body.


CREATE OR REPLACE PACKAGE testperf AS
   FUNCTION pow(i number) RETURN number;
   FUNCTION pow(i number,j number) RETURN number;
END testperf


CREATE OR REPLACE PACKAGE BODY testperf AS
   FUNCTION pow(i number) RETURN number AS
   BEGIN
      RETURN i * i;
   END;
END;

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/13     PLS-00323: subprogram or cursor 'POW' is declared in a package
         specification and must be defined in the package body

Displaying single column data into multiple rows in sql

Displaying single column data into multiple rows in sql

For example employee table has a column enames contains data as

SQL> SELECT ENAMES FROM EMPLOYEE;

ENAMES
-----------------------
CLARK,KING,MILLER


then we want to split that data into multiple rows(based on delimeter(, or . or |)) as follows

SQL>  select regexp_substr(ENAMES,'[^,]+', 1, level) result from EMPLOYEE
  2     connect by regexp_substr(ENAMES, '[^,]+', 1, level) is not null;

result
--------------------------------------------------------------------------------
CLARK
KING
MILLER