Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit ProductionSQL> 

SQL> CREATE SEQUENCE sq_dept_seq START WITH 10 INCREMENT BY 10;
Sequence created.

SQL> CREATE SEQUENCE sq_emp_seq START WITH 8001;
Sequence created.

SQL> CREATE TABLE sq_dept(
  2  	deptno	NUMBER(2) DEFAULT sq_dept_seq.NEXTVAL
  3   , dname	VARCHAR2(14 CHAR)
  4   , loc	VARCHAR2(13 CHAR)
  5   , CONSTRAINT sq_dept#p
  6  	   PRIMARY KEY(deptno));
Table created.

SQL> CREATE TABLE sq_emp(
  2  	empno	  NUMBER(4) DEFAULT sq_emp_seq.NEXTVAL
  3   , ename	  VARCHAR2(10 CHAR)
  4   , job	  VARCHAR2(9 CHAR)
  5   , mgr	  NUMBER(4)
  6   , hiredate  DATE
  7   , sal	  NUMBER(7,2)
  8   , comm	  NUMBER(7,2)
  9   , deptno	  NUMBER(2) DEFAULT ON NULL sq_dept_seq.CURRVAL
 10   , CONSTRAINT sq_emp#p
 11  	   PRIMARY KEY(empno)
 12   , CONSTRAINT sq_deptno#R#sq_dept
 13  	   FOREIGN KEY(deptno)
 14  	   REFERENCES sq_dept(deptno));
Table created.

/*
при вставке данных в таблицы SQ_DEPT и SQ_EMP 
в соответствующих командах INSERT
не указываем столбец номера отдела DEPTNO - 
эти значения будут автоматически получены 
из последовательности с использованием
sq_emp_seq.NEXTVAL и sq_emp_seq.CURRVAL
*/

SQL> -- ACCOUNTING dept
SQL> INSERT INTO sq_dept(DNAME, LOC)
  2  VALUES('ACCOUNTING', 'NEW YORK');
1 row created.

SQL> INSERT INTO sq_emp(EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO)
  2  Values(
  3  	7839, 'KING', 'PRESIDENT', TO_DATE('11/17/1981', 'MM/DD/YYYY')
  4   , 5000, 10);
1 row created.

SQL> INSERT INTO sq_emp
  2  	(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL)
  3   Values
  4  	(7782, 'CLARK', 'MANAGER', 7839, TO_DATE('06/09/1981', 'MM/DD/YYYY'), 2450);
1 row created.

SQL> INSERT INTO sq_emp
  2  	(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL)
  3   Values
  4  	(7934, 'MILLER', 'CLERK', 7782, TO_DATE('01/23/1982', 'MM/DD/YYYY'), 1300);
1 row created.


SQL> -- RESEARCH dept
SQL> INSERT INTO sq_dept(DNAME, LOC)
  2  VALUES('RESEARCH', 'DALLAS');
1 row created.

SQL> INSERT INTO sq_emp
  2  	(EMPNO, ENAME, JOB, MGR, HIREDATE,
  3  	 SAL, DEPTNO)
  4   Values
  5  	(7566, 'JONES', 'MANAGER', 7839, TO_DATE('04/02/1981', 'MM/DD/YYYY'),
  6  	 2975, 20);
1 row created.

SQL> INSERT INTO sq_emp
  2  	(EMPNO, ENAME, JOB, MGR, HIREDATE,
  3  	 SAL, DEPTNO)
  4   Values
  5  	(7902, 'FORD', 'ANALYST', 7566, TO_DATE('12/03/1981', 'MM/DD/YYYY'),
  6  	 3000, 20);
1 row created.

SQL> INSERT INTO sq_dept(DNAME, LOC)
  2  VALUES('SALES', 'CHICAGO');
1 row created.

-- Явно задаем значение DEPTNO. В этом случае последовательность изменяться не будет.
SQL> INSERT INTO sq_dept(deptno, DNAME, LOC)
  2  VALUES(40, 'OPERATIONS', 'BOSTON');
1 row created.

SQL> COMMIT;
Commit complete.

SQL> SELECT * FROM sq_dept;

    DEPTNO DNAME          LOC                                                                                                                                                                           
---------- -------------- -------------                                                                                                                                                                 
        10 ACCOUNTING     NEW YORK                                                                                                                                                                      
        20 RESEARCH       DALLAS                                                                                                                                                                        
        30 SALES          CHICAGO                                                                                                                                                                       
        40 OPERATIONS     BOSTON   

-- Проверим текущее значение последовательности
SQL> SELECT sq_dept_seq.CURRVAL FROM dual;

   CURRVAL
----------
        30                                                                                                                                                                     

SQL> SELECT * FROM sq_emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                                                                                                   
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                                                                                                   
      7839 KING       PRESIDENT            17-NOV-81       5000                    10                                                                                                                   
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10                                                                                                                   
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10                                                                                                                   
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                                                                                                   
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                                                                                                   

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 12 14:30:18 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> WITH                                                                          
FUNCTION str_plus(p_str VARCHAR2) RETURN VARCHAR2
IS
BEGIN
   RETURN p_str || '+';
END;
--
SELECT tname, str_plus(tname) FROM tab WHERE ROWNUM <= 2;   
/

TNAME           STR_PLUS(TNAME)
---------------- -------------------------
ACCESS$     ACCESS$+
ACLMV$      ACLMV$+

SQL> WITH
  2  FUNCTION LOWER(p_str VARCHAR2) RETURN VARCHAR2
  3  IS
  4  BEGIN
  5     RETURN p_str || '+';
  6  END;
  7  --
  8  SELECT tname, LOWER(tname) FROM tab WHERE ROWNUM <= 2;
  9  /

TNAME       LOWER(TNAME)
------------- ----------------------
BONUS       bonus
DEPT        dept

SQL> with function blabla(p_str VARCHAR2) RETURN VARCHAR2
  2  IS
  3  BEGIN
  4     RETURN lpad(p_str, 20, '*');
  5  END;
  6  --
  7  SELECT tname, blabla(tname) FROM tab WHERE ROWNUM <= 2;
  8  /

TNAME           BLABLA(TNAME)
--------------- ----------------------------------
BONUS           ***************BONUS
DEPT            ****************DEPT