[antdb@localhost ~]$ psql -U antdb -d postgres  -p 40009 
psql (12.3)
Type "help" for help.

postgres=# set grammar to oracle;
SET
postgres=# select now() from dual;
              NOW()                
------------------------------------
 2020-10-15 10:17:37.939801 +08:00
(1 row)

postgres=# set coordinator all (grammar=oracle);
SET PARAM
postgres=# show param cd1 grammar;
          type          | status |             message              
------------------------+--------+------------------------------------
 coordinator master cd1 | t      | debug_print_grammar = off       +
                        |        | grammar = oracle
(1 row)

show grammar ;

 grammar 
----------------
 oracle
(1 row)

select * from dual;

 DUMMY 
--------------
 X
(1 row)

show grammar ;
 grammar  
-----------------
 postgres
(1 row)

select * from dual;
ERROR:  relation "dual" does not exist
LINE 1: select * from dual;   

postgres=# set grammar to oracle;
SET
postgres=# show grammar ;
 grammar 
---------
 oracle
(1 row)
postgres=# select * from dual;
 DUMMY 
-------
 X
(1 row) 

postgres=# show grammar ;
 grammar  
----------
 postgres
(1 row)
postgres=# /*ora*/select * from dual;
 DUMMY 
-------
 X
(1 row)

postgres=# show grammar ;
 grammar 
---------
 oracle
(1 row)

SHOW SEARCH_PATH;

\set PLSQL_MODE ON

Oracle：create procedure name is ……
AntDB：create procedure name() is ……

set grammar to oracle;
BEGIN;
--
--  Create and load tables used in the documentation examples.
--
--  Create the 'dept' table
--
CREATE TABLE dept (
deptno  NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname   VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
loc VARCHAR2(13)
);
--
--  Create the 'emp' table
--
CREATE TABLE emp (
empno   NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename   VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate    DATE,
sal NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm    NUMBER(7,2),
deptno  NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);
--
--  Create the 'jobhist' table
--
CREATE TABLE jobhist (
empno   NUMBER(4) NOT NULL,
startdate   DATE NOT NULL,
enddate DATE,
job VARCHAR2(9),
sal NUMBER(7,2),
comm    NUMBER(7,2),
deptno  NUMBER(2),
chgdesc VARCHAR2(80),
CONSTRAINT jobhist_pk PRIMARY KEY (empno, startdate),
CONSTRAINT jobhist_ref_emp_fk FOREIGN KEY (empno)
REFERENCES emp(empno) ON DELETE CASCADE,
CONSTRAINT jobhist_ref_dept_fk FOREIGN KEY (deptno)
REFERENCES dept (deptno) ON DELETE SET NULL,
CONSTRAINT jobhist_date_chk CHECK (startdate <= enddate)
);
--
--  Create the 'salesemp' view
--
CREATE OR REPLACE VIEW salesemp AS
SELECT empno, ename, hiredate, sal, comm FROM emp WHERE job = 'SALESMAN';
--
--  Sequence to generate values for function 'new_empno'.
--
CREATE SEQUENCE next_empno START WITH 8000 INCREMENT BY 1;

--
--  Load the 'dept' table
--
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
--
--  Load the 'emp' table
--
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
--
--  Load the 'jobhist' table
--
INSERT INTO jobhist VALUES (7369,'17-DEC-80',NULL,'CLERK',800,NULL,20,'New Hire');
INSERT INTO jobhist VALUES (7499,'20-FEB-81',NULL,'SALESMAN',1600,300,30,'New Hire');
INSERT INTO jobhist VALUES (7521,'22-FEB-81',NULL,'SALESMAN',1250,500,30,'New Hire');
INSERT INTO jobhist VALUES (7566,'02-APR-81',NULL,'MANAGER',2975,NULL,20,'New Hire');
INSERT INTO jobhist VALUES (7654,'28-SEP-81',NULL,'SALESMAN',1250,1400,30,'New Hire');
INSERT INTO jobhist VALUES (7698,'01-MAY-81',NULL,'MANAGER',2850,NULL,30,'New Hire');
INSERT INTO jobhist VALUES (7782,'09-JUN-81',NULL,'MANAGER',2450,NULL,10,'New Hire');
INSERT INTO jobhist VALUES (7788,'19-APR-87','12-APR-88','CLERK',1000,NULL,20,'New Hire');
INSERT INTO jobhist VALUES (7788,'13-APR-88','04-MAY-89','CLERK',1040,NULL,20,'Raise');
INSERT INTO jobhist VALUES (7788,'05-MAY-90',NULL,'ANALYST',3000,NULL,20,'Promoted to Analyst');
INSERT INTO jobhist VALUES (7839,'17-NOV-81',NULL,'PRESIDENT',5000,NULL,10,'New Hire');
INSERT INTO jobhist VALUES (7844,'08-SEP-81',NULL,'SALESMAN',1500,0,30,'New Hire');
INSERT INTO jobhist VALUES (7876,'23-MAY-87',NULL,'CLERK',1100,NULL,20,'New Hire');
INSERT INTO jobhist VALUES (7900,'03-DEC-81','14-JAN-83','CLERK',950,NULL,10,'New Hire');
INSERT INTO jobhist VALUES (7900,'15-JAN-83',NULL,'CLERK',950,NULL,30,'Changed to Dept 30');
INSERT INTO jobhist VALUES (7902,'03-DEC-81',NULL,'ANALYST',3000,NULL,20,'New Hire');
INSERT INTO jobhist VALUES (7934,'23-JAN-82',NULL,'CLERK',1300,NULL,10,'New Hire');

COMMIT;

CREATE TABLE emp (
empno	NUMBER(4),
ename	VARCHAR2(10),
job	 VARCHAR2(9),
mgr	 NUMBER(4),
hiredate	DATE,
sal	NUMBER(7,2),
comm	NUMBER(7,2),
deptno	NUMBER(2)
);

DROP TABLE tablename; 

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30); 

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,20);

SELECT * FROM emp;

EMPNO | ENAME  |    JOB    | MGR   |      HIREDATE        | SAL  | COMM | DEPTNO 
------+--------+-----------+-------+----------------------+------+------+--------
 7369 | SMITH  | CLERK     | 7902  | 1980-12-17 00:00:00  |  800 |      |     20
 7499 | ALLEN  | SALESMAN  | 7698  | 1981-02-20 00:00:00  | 1600 |  300 |     30
 7521 | WARD   | SALESMAN  | 7698  | 1981-02-22 00:00:00  | 1250 |  500 |     30
 7566 | JONES  | MANAGER   | 7839  | 1981-04-02 00:00:00  | 2975 |      |     20
 7654 | MARTIN | SALESMAN  | 7698  | 1981-09-28 00:00:00  | 1250 | 1400 |     30
 7698 | BLAKE  | MANAGER   | 7839  | 1981-05-01 00:00:00  | 2850 |      |     30
 7782 | CLARK  | MANAGER   | 7839  | 1981-06-09 00:00:00  | 2450 |      |     10
 7788 | SCOTT  | ANALYST   | 7566  | 1987-04-19 00:00:00  | 3000 |      |     20
 7839 | KING   | PRESIDENT |       | 1981-11-17 00:00:00  | 5000 |      |     10
 7844 | TURNER | SALESMAN  | 7698  | 1981-09-08 00:00:00  | 1500 |    0 |     30
 7876 | ADAMS  | CLERK     | 7788  | 1987-05-23 00:00:00  | 1100 |      |     20
 7900 | JAMES  | CLERK     | 7698  | 1981-12-03 00:00:00  |  950 |      |     30
 7902 | FORD   | ANALYST   | 7566  | 1981-12-03 00:00:00  | 3000 |      |     20
 7934 | MILLER | CLERK     | 7782  | 1982-01-23 00:00:00  | 1300 |      |     10
(14 rows)

SELECT ename, sal, sal * 24 AS yearly_salary, deptno FROM emp;

 ENAME  | SAL  | YEARLY_SALARY | DEPTNO 
--------+------+---------------+--------
 SMITH  |  800 |         19200 |     20
 ALLEN  | 1600 |         38400 |     30
 WARD   | 1250 |         30000 |     30
 JONES  | 2975 |         71400 |     20
 MARTIN | 1250 |         30000 |     30
 BLAKE  | 2850 |         68400 |     30
 CLARK  | 2450 |         58800 |     10
 SCOTT  | 3000 |         72000 |     20
 KING   | 5000 |        120000 |     10
 TURNER | 1500 |         36000 |     30
 ADAMS  | 1100 |         26400 |     20
 JAMES  |  950 |         22800 |     30
 FORD   | 3000 |         72000 |     20
 MILLER | 1300 |         31200 |     10
(14 rows)

SELECT ename, sal, deptno FROM emp WHERE deptno = 20 AND sal > 1000;

 ENAME | SAL  | DEPTNO 
-------+------+--------
 JONES | 2975 |     20
 SCOTT | 3000 |     20
 ADAMS | 1100 |     20
 FORD  | 3000 |     20
(4 rows)

SELECT ename, sal, deptno FROM emp ORDER BY ename;

 ENAME  | SAL  | DEPTNO 
--------+------+--------
 ADAMS  | 1100 |     20
 ALLEN  | 1600 |     30
 BLAKE  | 2850 |     30
 CLARK  | 2450 |     10
 FORD   | 3000 |     20
 JAMES  |  950 |     30
 JONES  | 2975 |     20
 KING   | 5000 |     10
 MARTIN | 1250 |     30
 MILLER | 1300 |     10
 SCOTT  | 3000 |     20
 SMITH  |  800 |     20
 TURNER | 1500 |     30
 WARD   | 1250 |     30
(14 rows)

SELECT DISTINCT job FROM emp;

    JOB    
------------
 CLERK
 PRESIDENT
 MANAGER
 SALESMAN
 ANALYST
(5 rows)

SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM emp, dept WHERE emp.deptno = dept.deptno;

 ENAME  | SAL  | DEPTNO |   DNAME    |   LOC    
--------+------+--------+------------+----------
 SMITH  |  800 |     20 | RESEARCH   | DALLAS
 ALLEN  | 1600 |     30 | SALES      | CHICAGO
 WARD   | 1250 |     30 | SALES      | CHICAGO
 JONES  | 2975 |     20 | RESEARCH   | DALLAS
 MARTIN | 1250 |     30 | SALES      | CHICAGO
 BLAKE  | 2850 |     30 | SALES      | CHICAGO
 CLARK  | 2450 |     10 | ACCOUNTING | NEW YORK
 SCOTT  | 3000 |     20 | RESEARCH   | DALLAS
 KING   | 5000 |     10 | ACCOUNTING | NEW YORK
 TURNER | 1500 |     30 | SALES      | CHICAGO
 ADAMS  | 1100 |     20 | RESEARCH   | DALLAS
 JAMES  |  950 |     30 | SALES      | CHICAGO
 FORD   | 3000 |     20 | RESEARCH   | DALLAS
 MILLER | 1300 |     10 | ACCOUNTING | NEW YORK
(14 rows)

SELECT ename, sal, dept.deptno, dname, loc FROM emp, dept WHERE emp.deptno = dept.deptno; |

SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM emp INNER JOIN dept ON emp.deptno = dept.deptno; 

SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM dept LEFT OUTER JOIN emp ON emp.deptno = dept.deptno;

 ENAME  | SAL  | DEPTNO   |   DNAME    |   LOC    
--------+------+----------+------------+----------
 SMITH  |  800 |       20 | RESEARCH   | DALLAS
 ALLEN  | 1600 |       30 | SALES      | CHICAGO
 WARD   | 1250 |       30 | SALES      | CHICAGO
 JONES  | 2975 |       20 | RESEARCH   | DALLAS
 MARTIN | 1250 |       30 | SALES      | CHICAGO
 BLAKE  | 2850 |       30 | SALES      | CHICAGO
 CLARK  | 2450 |       10 | ACCOUNTING | NEW YORK
 SCOTT  | 3000 |       20 | RESEARCH   | DALLAS
 KING   | 5000 |       10 | ACCOUNTING | NEW YORK
 TURNER | 1500 |       30 | SALES      | CHICAGO
 ADAMS  | 1100 |       20 | RESEARCH   | DALLAS
 JAMES  |  950 |       30 | SALES      | CHICAGO
 FORD   | 3000 |       20 | RESEARCH   | DALLAS
 MILLER | 1300 |       10 | ACCOUNTING | NEW YORK
        |      |       40 | OPERATIONS | BOSTON
(15 rows)

SELECT emp.ename, emp.sal, dept.deptno, dept.dname, dept.loc FROM dept, emp WHERE emp.deptno(+) = dept.deptno;

 ENAME  | SAL  | DEPTNO |   DNAME    |   LOC    
--------+------+--------+------------+----------
 SMITH  |  800 |     20 | RESEARCH   | DALLAS
 ALLEN  | 1600 |     30 | SALES      | CHICAGO
 WARD   | 1250 |     30 | SALES      | CHICAGO
 JONES  | 2975 |     20 | RESEARCH   | DALLAS
 MARTIN | 1250 |     30 | SALES      | CHICAGO
 BLAKE  | 2850 |     30 | SALES      | CHICAGO
 CLARK  | 2450 |     10 | ACCOUNTING | NEW YORK
 SCOTT  | 3000 |     20 | RESEARCH   | DALLAS
 KING   | 5000 |     10 | ACCOUNTING | NEW YORK
 TURNER | 1500 |     30 | SALES      | CHICAGO
 ADAMS  | 1100 |     20 | RESEARCH   | DALLAS
 JAMES  |  950 |     30 | SALES      | CHICAGO
 FORD   | 3000 |     20 | RESEARCH   | DALLAS
 MILLER | 1300 |     10 | ACCOUNTING | NEW YORK
        |      |     40 | OPERATIONS | BOSTON
(15 rows)

SELECT e1.ename || ' works for ' || e2.ename AS "Employees and their Managers" FROM emp e1, emp e2 WHERE e1.mgr = e2.empno;

Employees and their Managers 
-----------------------------
 SMITH works for FORD
 ALLEN works for BLAKE
 WARD works for BLAKE
 JONES works for KING
 MARTIN works for BLAKE
 BLAKE works for KING
 CLARK works for KING
 SCOTT works for JONES
 TURNER works for BLAKE
 ADAMS works for SCOTT
 JAMES works for BLAKE
 FORD works for JONES
 MILLER works for CLARK
(13 rows)

SELECT e.ename, e.mgr, d.deptno, d.dname, d.loc FROM emp e, dept d WHERE e.deptno = d.deptno;

 ENAME  | MGR   |   DEPTNO |   DNAME    |   LOC    
--------+-------+----------+------------+-----------
 SMITH  |  7902 |       20 | RESEARCH   | DALLAS
 ALLEN  |  7698 |       30 | SALES      | CHICAGO
 WARD   |  7698 |       30 | SALES      | CHICAGO
 JONES  |  7839 |       20 | RESEARCH   | DALLAS
 MARTIN |  7698 |       30 | SALES      | CHICAGO
 BLAKE  |  7839 |       30 | SALES      | CHICAGO
 CLARK  |  7839 |       10 | ACCOUNTING | NEW YORK
 SCOTT  |  7566 |       20 | RESEARCH   | DALLAS
 KING   |       |       10 | ACCOUNTING | NEW YORK
 TURNER |  7698 |       30 | SALES      | CHICAGO
 ADAMS  |  7788 |       20 | RESEARCH   | DALLAS
 JAMES  |  7698 |       30 | SALES      | CHICAGO
 FORD   |  7566 |       20 | RESEARCH   | DALLAS
 MILLER |  7782 |       10 | ACCOUNTING | NEW YORK
(14 rows)

SELECT MAX(sal) highest_salary, MIN(sal) lowest_salary FROM emp;

HIGHEST_SALARY | LOWEST_SALARY 
---------------+-----------------
          5000 |          800
(1 row)

SELECT ename FROM emp WHERE sal = MAX(sal);

ERROR:  aggregate functions are not allowed in WHERE
LINE 1: SELECT ename FROM emp WHERE sal = MAX(sal);
                                          ^

SELECT ename FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);

 ENAME 
------------
 KING
(1 row)

SELECT deptno, MAX(sal) FROM emp GROUP BY deptno;

  DEPTNO | MAX(SAL)  
---------+-----------
      10 |      5000
      30 |      2850
      20 |      3000
(3 rows)

SELECT deptno, MAX(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 2000;

  DEPTNO | MAX(SAL)  
---------+-----------
      10 |      5000
      20 |      3000
(2 rows)

SELECT deptno, MAX(sal) FROM emp WHERE job = 'ANALYST' GROUP BY deptno HAVING AVG(sal) >
2000;

  DEPTNO | MAX(SAL)  
---------+-----------
      20 |      3000
(1 row)

SELECT ename, sal FROM emp WHERE job = 'MANAGER';

 ENAME | SAL  
-------+------
 JONES | 2975
 BLAKE | 2850
 CLARK | 2450
(3 rows)

UPDATE emp SET sal = sal * 1.1 WHERE job = 'MANAGER';

SELECT ename, sal FROM emp WHERE job = 'MANAGER';

 ENAME |  SAL   
-------+--------
 JONES | 3272.5
 BLAKE |   3135
 CLARK |   2695
(3 rows)

SELECT ename, deptno FROM emp;

 ENAME  | DEPTNO 
--------+--------
 SMITH  |     20
 ALLEN  |     30
 WARD   |     30
 MARTIN |     30
 SCOTT  |     20
 KING   |     10
 TURNER |     30
 ADAMS  |     20
 JAMES  |     30
 FORD   |     20
 MILLER |     10
 JONES  |     20
 BLAKE  |     30
 CLARK  |     10
(14 rows)

DELETE FROM emp WHERE deptno = 20;

SELECT ename, deptno FROM emp;

 ENAME  | DEPTNO 
--------+---------------
 ALLEN  |     30
 WARD   |     30
 MARTIN |     30
 KING   |     10
 TURNER |     30
 JAMES  |     30
 MILLER |     10
 BLAKE  |     30
 CLARK  |     10
(9 rows)

DELETE FROM tablename; 

SELECT ename, sal, sal * 24 AS yearly_salary, deptno FROM emp;

 ENAME  | SAL  | YEARLY_SALARY | DEPTNO 
--------+------+---------------+--------
 SMITH  |  800 |         19200 |     20
 ALLEN  | 1600 |         38400 |     30
 WARD   | 1250 |         30000 |     30
 JONES  | 2975 |         71400 |     20
 MARTIN | 1250 |         30000 |     30
 BLAKE  | 2850 |         68400 |     30
 CLARK  | 2450 |         58800 |     10
 SCOTT  | 3000 |         72000 |     20
 KING   | 5000 |        120000 |     10
 TURNER | 1500 |         36000 |     30
 ADAMS  | 1100 |         26400 |     20
 JAMES  |  950 |         22800 |     30
 FORD   | 3000 |         72000 |     20
 MILLER | 1300 |         31200 |     10
(14 rows)

CREATE VIEW employee_pay AS SELECT ename, sal, sal * 24 AS yearly_salary, deptno FROM emp; 

SELECT * FROM employee_pay;

 ENAME  | SAL  | YEARLY_SALARY | DEPTNO 
--------+------+---------------+---------
 SMITH  |  800 |         19200 |     20
 ALLEN  | 1600 |         38400 |     30
 WARD   | 1250 |         30000 |     30
 JONES  | 2975 |         71400 |     20
 MARTIN | 1250 |         30000 |     30
 BLAKE  | 2850 |         68400 |     30
 CLARK  | 2450 |         58800 |     10
 SCOTT  | 3000 |         72000 |     20
 KING   | 5000 |        120000 |     10
 TURNER | 1500 |         36000 |     30
 ADAMS  | 1100 |         26400 |     20
 JAMES  |  950 |         22800 |     30
 FORD   | 3000 |         72000 |     20
 MILLER | 1300 |         31200 |     10
(14 rows)

CREATE TABLE emp (
empno	NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename	VARCHAR2(10),
job	VARCHAR2(9),
mgr	NUMBER(4),
hiredate	DATE,
sal	NUMBER(7,2),
comm	NUMBER(7,2),
deptno	NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);

INSERT INTO emp VALUES (8000,'JONES','CLERK',7902,'17-AUG-07',1200,NULL,50);

ERROR:	insert or update on table "emp" violates foreign key constraint "emp_ref_dept_fk"
DETAIL:	Key (deptno)=(50) is not present in table "dept".

default_with_rowids = on

set default_with_rowids = on

create table tt(id int, sal int) with rowid;
insert into tt values(1, 1000),(2, 4000),(4, 8000);
select rowid, * from tt;
    ROWID     | ID | SAL  
--------------+----+------
 ZAAAAAAAQAA= |  1 | 1000
 ZQAAAAAAQAA= |  2 | 4000
 ZgAAAAAAQAA= |  4 | 8000
(3 rows)

SELECT empno, ename, job FROM emp WHERE ROWNUM < 5;

 EMPNO | ENAME |   JOB    
-------+-------+----------
  7369 | SMITH | CLERK
  7499 | ALLEN | SALESMAN
  7521 | WARD  | SALESMAN
  7566 | JONES | MANAGER
(4 rows)

SELECT ROWNUM, empno, ename, job FROM emp WHERE ROWNUM < 5 ORDER BY ename;

 ROWNUM |  EMPNO  | ENAME |   JOB    
--------+---------+-------+----------
      1 |    7876 | ADAMS | CLERK
      2 |    7499 | ALLEN | SALESMAN
      3 |    7698 | BLAKE | MANAGER
      4 |    7782 | CLARK | MANAGER
(4 rows)

ALTER TABLE jobhist ADD column seqno NUMBER(3);
UPDATE jobhist SET seqno = ROWNUM;

SELECT seqno, empno, TO_CHAR(startdate,'DD-MON-YY') AS start, job FROM jobhist;

 SEQNO | EMPNO |   START   |    JOB    
-------+-------+-----------+-------------------
     1 |  7369 | 17-DEC-80 | CLERK
     2 |  7499 | 20-FEB-81 | SALESMAN
     3 |  7521 | 22-FEB-81 | SALESMAN
     4 |  7566 | 02-APR-81 | MANAGER
     5 |  7654 | 28-SEP-81 | SALESMAN
     6 |  7698 | 01-MAY-81 | MANAGER
     7 |  7782 | 09-JUN-81 | MANAGER
     8 |  7788 | 19-APR-87 | CLERK
     9 |  7788 | 13-APR-88 | CLERK
    10 |  7788 | 05-MAY-90 | ANALYST
    11 |  7839 | 17-NOV-81 | PRESIDENT
    12 |  7844 | 08-SEP-81 | SALESMAN
    13 |  7876 | 23-MAY-87 | CLERK
    14 |  7900 | 03-DEC-81 | CLERK
    15 |  7900 | 15-JAN-83 | CLERK
    16 |  7902 | 03-DEC-81 | ANALYST
    17 |  7934 | 23-JAN-82 | CLERK
(17 rows)

SELECT ename, empno, mgr
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

 ENAME  | EMPNO | MGR  
--------+-------+------
 KING   |  7839 |     
 CLARK  |  7782 | 7839
 BLAKE  |  7698 | 7839
 JONES  |  7566 | 7839
 MILLER |  7934 | 7782
 JAMES  |  7900 | 7698
 TURNER |  7844 | 7698
 MARTIN |  7654 | 7698
 WARD   |  7521 | 7698
 ALLEN  |  7499 | 7698
 FORD   |  7902 | 7566
 SCOTT  |  7788 | 7566
 SMITH  |  7369 | 7902
 ADAMS  |  7876 | 7788
(14 rows)

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr
FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

 LEVEL |  employee   | EMPNO | MGR  
-------+-------------+-------+------
     1 | KING        |  7839 |     
     2 |   CLARK     |  7782 | 7839
     2 |   BLAKE     |  7698 | 7839
     2 |   JONES     |  7566 | 7839
     3 |     MILLER  |  7934 | 7782
     3 |     JAMES   |  7900 | 7698
     3 |     TURNER  |  7844 | 7698
     3 |     MARTIN  |  7654 | 7698
     3 |     WARD    |  7521 | 7698
     3 |     ALLEN   |  7499 | 7698
     3 |     FORD    |  7902 | 7566
     3 |     SCOTT   |  7788 | 7566
     4 |       SMITH |  7369 | 7902
     4 |       ADAMS |  7876 | 7788
(14 rows)

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr
FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;

 LEVEL |  employee   | EMPNO | MGR  
-------+-------------+-------+------
     1 | KING        |  7839 |     
     2 |   BLAKE     |  7698 | 7839
     3 |     ALLEN   |  7499 | 7698
     3 |     JAMES   |  7900 | 7698
     3 |     MARTIN  |  7654 | 7698
     3 |     TURNER  |  7844 | 7698
     3 |     WARD    |  7521 | 7698
     2 |   CLARK     |  7782 | 7839
     3 |     MILLER  |  7934 | 7782
     2 |   JONES     |  7566 | 7839
     3 |     FORD    |  7902 | 7566
     4 |       SMITH |  7369 | 7902
     3 |     SCOTT   |  7788 | 7566
     4 |       ADAMS |  7876 | 7788
(14 rows)

SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr
FROM emp WHERE mgr IN (7839, 7782, 7902, 7788)
START WITH ename IN ('BLAKE','CLARK','JONES')
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;

 LEVEL | employee  | EMPNO | MGR  
-------+-----------+-------+------
     1 | BLAKE     |  7698 | 7839
     1 | CLARK     |  7782 | 7839
     2 |   MILLER  |  7934 | 7782
     1 | JONES     |  7566 | 7839
     3 |     SMITH |  7369 | 7902
     3 |     ADAMS |  7876 | 7788
(6 rows)

SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP (loc, dname, job)
ORDER BY 1, 2, 3;

   LOC    |   DNAME    |    JOB      | employees 
----------+------------+-------------+------------
 CHICAGO  | SALES      | CLERK       |          1
 CHICAGO  | SALES      | MANAGER     |          1
 CHICAGO  | SALES      | SALESMAN    |          4
 CHICAGO  | SALES      |             |          6
 CHICAGO  |            |             |          6
 DALLAS   | RESEARCH   | ANALYST     |          2
 DALLAS   | RESEARCH   | CLERK       |          2
 DALLAS   | RESEARCH   | MANAGER     |          1
 DALLAS   | RESEARCH   |             |          5
 DALLAS   |            |             |          5
 NEW YORK | ACCOUNTING | CLERK       |          1
 NEW YORK | ACCOUNTING | MANAGER     |          1
 NEW YORK | ACCOUNTING | PRESIDENT   |          1
 NEW YORK | ACCOUNTING |             |          3
 NEW YORK |            |             |          3
          |            |             |         14
(16 rows)

 SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP ((loc, dname), job)
ORDER BY 1, 2, 3;

   LOC    |   DNAME    |    JOB    | employees 
----------+------------+-----------+-----------
 CHICAGO  | SALES      | CLERK     |         1
 CHICAGO  | SALES      | MANAGER   |         1
 CHICAGO  | SALES      | SALESMAN  |         4
 CHICAGO  | SALES      |           |         6
 DALLAS   | RESEARCH   | ANALYST   |         2
 DALLAS   | RESEARCH   | CLERK     |         2
 DALLAS   | RESEARCH   | MANAGER   |         1
 DALLAS   | RESEARCH   |           |         5
 NEW YORK | ACCOUNTING | CLERK     |         1
 NEW YORK | ACCOUNTING | MANAGER   |         1
 NEW YORK | ACCOUNTING | PRESIDENT |         1
 NEW YORK | ACCOUNTING |           |         3
          |            |           |        14

SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY CUBE (loc, dname, job)
ORDER BY 1, 2, 3;

  LOC     |   DNAME    |    JOB    | employees 
----------+------------+-----------+-------------
 CHICAGO  | SALES      | CLERK     |         1
 CHICAGO  | SALES      | MANAGER   |         1
 CHICAGO  | SALES      | SALESMAN  |         4
 CHICAGO  | SALES      |           |         6
 CHICAGO  |            | CLERK     |         1
 CHICAGO  |            | MANAGER   |         1
 CHICAGO  |            | SALESMAN  |         4
 CHICAGO  |            |           |         6
 DALLAS   | RESEARCH   | ANALYST   |         2
 DALLAS   | RESEARCH   | CLERK     |         2
 DALLAS   | RESEARCH   | MANAGER   |         1
 DALLAS   | RESEARCH   |           |         5
 DALLAS   |            | ANALYST   |         2
 DALLAS   |            | CLERK     |         2
 DALLAS   |            | MANAGER   |         1
 DALLAS   |            |           |         5
 NEW YORK | ACCOUNTING | CLERK     |         1
 NEW YORK | ACCOUNTING | MANAGER   |         1
 NEW YORK | ACCOUNTING | PRESIDENT |         1
 NEW YORK | ACCOUNTING |           |         3
 NEW YORK |            | CLERK     |         1
 NEW YORK |            | MANAGER   |         1
 NEW YORK |            | PRESIDENT |         1
 NEW YORK |            |           |         3
          | ACCOUNTING | CLERK     |         1
          | ACCOUNTING | MANAGER   |         1
          | ACCOUNTING | PRESIDENT |         1
          | ACCOUNTING |           |         3
          | RESEARCH   | ANALYST   |         2
          | RESEARCH   | CLERK     |         2
          | RESEARCH   | MANAGER   |         1
          | RESEARCH   |           |         5
          | SALES      | CLERK     |         1
          | SALES      | MANAGER   |         1
          | SALES      | SALESMAN  |         4
          | SALES      |           |         6
          |            | ANALYST   |         2
          |            | CLERK     |         4
          |            | MANAGER   |         3
          |            | PRESIDENT |         1
          |            | SALESMAN  |         4
          |            |           |        14
(42 rows)

SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY GROUPING SETS (loc, dname, job)
ORDER BY 1, 2, 3;

   LOC    |   DNAME    |    JOB    | employees 
----------+------------+-----------+-----------
 CHICAGO  |            |           |         6
 DALLAS   |            |           |         5
 NEW YORK |            |           |         3
          | ACCOUNTING |           |         3
          | RESEARCH   |           |         5
          | SALES      |           |         6
          |            | ANALYST   |         2
          |            | CLERK     |         4
          |            | MANAGER   |         3
          |            | PRESIDENT |         1
          |            | SALESMAN  |         4
(11 rows)

SELECT NULL, dname, NULL, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY dname
UNION ALL
SELECT NULL, NULL, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY job
ORDER BY 1, 2, 3;

SELECT loc, dname, job, COUNT(*) AS "employees" FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY GROUPING SETS (loc, ROLLUP (dname, job), CUBE (job, loc))
ORDER BY 1, 2, 3;

   LOC    |   DNAME    |    JOB      | employees 
----------+------------+-------------+------------
 CHICAGO  |            | CLERK       |         1
 CHICAGO  |            | MANAGER     |         1
 CHICAGO  |            | SALESMAN    |         4
 CHICAGO  |            |             |         6
 CHICAGO  |            |             |         6
 DALLAS   |            | ANALYST     |         2
 DALLAS   |            | CLERK       |         2
 DALLAS   |            | MANAGER     |         1
 DALLAS   |            |             |         5
 DALLAS   |            |             |         5
 NEW YORK |            | CLERK       |         1
 NEW YORK |            | MANAGER     |         1
 NEW YORK |            | PRESIDENT   |         1
 NEW YORK |            |             |         3
 NEW YORK |            |             |         3
          | ACCOUNTING | CLERK       |         1
          | ACCOUNTING | MANAGER     |         1
          | ACCOUNTING | PRESIDENT   |         1
          | ACCOUNTING |             |         3
          | RESEARCH   | ANALYST     |         2
          | RESEARCH   | CLERK       |         2
          | RESEARCH   | MANAGER     |         1
          | RESEARCH   |             |         5
          | SALES      | CLERK       |         1
          | SALES      | MANAGER     |         1
          | SALES      | SALESMAN    |         4
          | SALES      |             |         6
          |            | ANALYST     |         2
          |            | CLERK       |         4
          |            | MANAGER     |         3
          |            | PRESIDENT   |         1
          |            | SALESMAN    |         4
          |            |             |        14
          |            |             |        14
(34 rows)

SELECT loc, dname, job, COUNT(*) AS "employees",
  GROUPING(loc) AS "gf_loc",
  GROUPING(dname) AS "gf_dname",
  GROUPING(job) AS "gf_job"
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY ROLLUP (loc, dname, job)
ORDER BY 1, 2, 3;

   LOC    |   DNAME    |    JOB    | employees | gf_loc | gf_dname | gf_job 
----------+------------+-----------+-----------+--------+----------+--------
 CHICAGO  | SALES      | CLERK     |         1 |      0 |        0 |      0
 CHICAGO  | SALES      | MANAGER   |         1 |      0 |        0 |      0
 CHICAGO  | SALES      | SALESMAN  |         4 |      0 |        0 |      0
 CHICAGO  | SALES      |           |         6 |      0 |        0 |      1
 CHICAGO  |            |           |         6 |      0 |        1 |      1
 DALLAS   | RESEARCH   | ANALYST   |         2 |      0 |        0 |      0
 DALLAS   | RESEARCH   | CLERK     |         2 |      0 |        0 |      0
 DALLAS   | RESEARCH   | MANAGER   |         1 |      0 |        0 |      0
 DALLAS   | RESEARCH   |           |         5 |      0 |        0 |      1
 DALLAS   |            |           |         5 |      0 |        1 |      1
 NEW YORK | ACCOUNTING | CLERK     |         1 |      0 |        0 |      0
 NEW YORK | ACCOUNTING | MANAGER   |         1 |      0 |        0 |      0
 NEW YORK | ACCOUNTING | PRESIDENT |         1 |      0 |        0 |      0
 NEW YORK | ACCOUNTING |           |         3 |      0 |        0 |      1
 NEW YORK |            |           |         3 |      0 |        1 |      1
          |            |           |        14 |      1 |        1 |      1
(16 rows)

SELECT * FROM MY_TABLE;
UPDATE MY_TABLE SET A = 5;
INSERT INTO MY_TABLE VALUES (3, 'hi there');

UPDATE MY_TABLE SET A = 5;

uPDaTE my_TabLE SeT a = 5; 

UPDATE my_table SET a = 5; 

UPDATE "my_table" SET "a" = 5; 

create table TEST1(ID int, NAME varchar(5));
insert into test1(name) values('Tom');
select * from test1;

create table TEST2("ID" int, "NAME" varchar(5));
insert into TEST2("NAME") values('Tom');
select * from test2;

create table "TesT3"(ID int, NAME varchar(5));
insert into "TesT3" (NAME) values('Tom');
select * from "TesT3";

 ID | NAME 
----+------
    | Tom
(1 row)

digits
digits.[digits][e[+-]digits]
[digits].digits[e[+-]digits]
digitse[+-]digits

42
3.5
4.
0.001
5e2
1.925e-3

-- This is a standard SQL comment

/* multiline comment
* block
*/

postgres=# create table test(id int, a number(10,4),b binary_float, c float(4), d binary_double, e double precision, f real, g  decimal(5,3), h numeric);
CREATE TABLE
postgres=# \d+ test
                                      Table "public.test"
 Column |     Type      | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------------+-----------+----------+---------+---------+--------------+---------------------
 id     | integer       |           |          |         | plain   |              | 
 a      | numeric(10,4) |           |          |         | main    |              | 
 b      | numeric       |           |          |         | main    |              | 
 c      | numeric       |           |          |         | main    |              | 
 d      | numeric       |           |          |         | main    |              | 
 e      | numeric       |           |          |         | main    |              | 
 f      | numeric       |           |          |         | main    |              | 
 g      | numeric(5,3)  |           |          |         | main    |              | 
 h      | numeric(38,0) |           |          |         | main    |              | 
Access method: heap

postgres=#  create table test(id int, a blob);
CREATE TABLE
postgres=# insert into test values(1, 'abcd');
INSERT 0 1
postgres=# select * from test;
 ID |     A      
----+------------
  1 | \x61626364
(1 row)
postgres=# set bytea_output = 'escape';
SET
postgres=#  select * from test;
 ID |  A   
----+------
  1 | abcd
(1 row)

create table test(id int, dt date);
insert into test values(1,'January 8, 1999');
insert into test values(2,'1999-01-08');
insert into test values(3,'1999-Jan-08');
insert into test values(4,'Jan-08-1999');
insert into test values(5,'08-Jan-1999');
insert into test values(6,'08-Jan-99');
insert into test values(7,'Jan-08-99');
insert into test values(8,'19990108');
insert into test values(9,'990108');

postgres=# select * from test;
 ID |         DT          
----+---------------------
  1 | 1999-01-08 00:00:00
  2 | 1999-01-08 00:00:00
  3 | 1999-01-08 00:00:00
  4 | 1999-01-08 00:00:00
  5 | 1999-01-08 00:00:00
  6 | 1999-01-08 00:00:00
  7 | 1999-01-08 00:00:00
  8 | 1999-01-08 00:00:00
  9 | 1999-01-08 00:00:00
(9 rows)

create table test(id int, ts timestamp(6));
insert into test values(1,'1999-01-08 04:05:06.789');
insert into test values(2,'1999-Jan-08 04:05');
insert into test values(3,'January 8, 1999 040506');
insert into test values(4,'19990108 04:05 AM');
insert into test values(5,'08-Jan-99 04:05 PM');
postgres=# select * from test;
 ID |           TS            
----+-------------------------
  1 | 1999-01-08 04:05:06.789
  2 | 1999-01-08 04:05:00
  3 | 1999-01-08 04:05:06
  4 | 1999-01-08 04:05:00
  5 | 1999-01-08 16:05:00
(5 rows)

08-JAN-99 04:05:06

1999-01-08 04:05:06

INTERVAL 'integer [- integer ]' { YEAR \| MONTH } [ TO { YEAR \| MONTH } ]

INTERVAL 'integer [- integer ]' { YEAR \| MONTH } [ (precision) ]

postgres=#  select interval '100' year(2) from dual;
 INTERVAL  
------------------
       100 years
(1 row)

INTERVAL '{ integer \| integer time_expr \| time_expr }'



[ TO { DAY \| HOUR \| MINUTE \| SECOND [ (fractional_seconds_precision) ] } ]

INTERVAL '{ integer \| integer time_expr \| time_expr }'

{ { DAY \| HOUR \| MINUTE \| SECOND} [ (leading_precision) ] }

select INTERVAL '4 5:12:10.222' DAY TO SECOND(3) from dual;
select INTERVAL '4 5:12' DAY TO MINUTE  from dual;
select INTERVAL '400' DAY(3)  from dual;
select INTERVAL '11:12:10.2222222' HOUR TO SECOND(6)  from dual;
select INTERVAL '11:20' HOUR TO MINUTE  from dual;
select INTERVAL '10' HOUR  from dual;
select INTERVAL '10:22' MINUTE TO SECOND  from dual;
select INTERVAL '10' MINUTE  from dual;
select INTERVAL '4' DAY  from dual;
select INTERVAL '25' HOUR  from dual;
select INTERVAL '40' MINUTE  from dual;
select INTERVAL '120' HOUR(3) from dual; 
select INTERVAL '20' DAY - INTERVAL '240' HOUR = INTERVAL '10-0' DAY TO SECOND  from dual;

select INTERVAL '400 5' DAY(3) TO HOUR from dual;
select INTERVAL '30.12345' SECOND(2,4)  from dual;
select INTERVAL '10:22' MINUTE(1) TO SECOND  from dual;

select to_char(sysdate,'DD-MON-MM hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'DD-MON-MM HH24:MI:SS')  
------------------------------------------
 03-SEP-09 15:51:47
(1 row)

ALTER INDEX name_idx RENAME TO empname_idx; 

MODIFY column type [column type] 

MODIFY (column type [,column type][, ... ]) 

ADD column type [ column_constraint [ ... ] ]

DROP COLUMN column

ADD table_constraint

DROP CONSTRAINT constraint_name [ CASCADE ]

ALTER TABLE emp ADD COLUMN address VARCHAR2(30); 

ALTER TABLE emp DROP COLUMN address; 

ALTER TABLE emp RENAME COLUMN address TO city; 

ALTER TABLE emp RENAME TO employee; 

ALTER TABLE emp ADD CONSTRAINT sal_chk CHECK (sal \> 500); 

ALTER TABLE emp DROP CONSTRAINT sal_chk; 

COMMENT ON TABLE emp IS 'Current employee information'; 

COMMENT ON COLUMN emp.empno IS 'Employee identification number';

COMMENT ON TABLE emp IS ''; COMMENT ON COLUMN emp.empno IS '';

\set PLSQL_MODE ON

\set PLSQL_MODE ON
CREATE OR REPLACE FUNCTION emp_comp (
p_sal	NUMBER,
p_comm	NUMBER
) RETURN NUMBER
IS
BEGIN
RETURN (p_sal + NVL(p_comm, 0)) * 24;
END;
/
\set PLSQL_MODE OFF
SELECT ename "Name", sal "Salary", comm "Commission", emp_comp(sal, comm)
"Total Compensation"	FROM emp;

  Name  | Salary | Commission | Total Compensation 
--------------+--------+-------------------+---------------------------------
 SMITH  |    800 |            |              19200
 ALLEN  |   1600 |        300 |              45600
 WARD   |   1250 |        500 |              42000
 JONES  |   2975 |            |              71400
 MARTIN |   1250 |       1400 |              63600
 BLAKE  |   2850 |            |              68400
 CLARK  |   2450 |            |              58800
 SCOTT  |   3000 |            |              72000
 KING   |   5000 |            |             120000
 TURNER |   1500 |          0 |              36000
 ADAMS  |   1100 |            |              26400
 JAMES  |    950 |            |              22800
 FORD   |   3000 |            |              72000
 MILLER |   1300 |            |              31200
(14 rows)

\set PLSQL_MODE ON
CREATE OR REPLACE FUNCTION sal_range (
p_sal_min	NUMBER DEFAULT 0,
p_sal_max	NUMBER DEFAULT 10000
) RETURN INTEGER
IS
v_count	INTEGER;
BEGIN
SELECT COUNT(*) INTO v_count FROM emp
WHERE sal BETWEEN p_sal_min AND p_sal_max;
RETURN v_count;
END;
/
\set PLSQL_MODE OFF

select sal_range();
SAL_RANGE 
--------------------
        14
(1 row)

CREATE INDEX name_idx ON emp (ename); 

CREATE INDEX name_idx ON emp (ename) TABLESPACE index_tblspc; 

\set PLSQL_MODE ON
CREATE OR REPLACE PROCEDURE list_emp()
IS
v_empno	NUMBER(4);
v_ename	VARCHAR2(10);
CURSOR emp_cur IS
SELECT empno, ename FROM emp ORDER BY empno;
BEGIN
OPEN emp_cur;
DBMS_OUTPUT.PUT_LINE('EMPNO	ENAME');
DBMS_OUTPUT.PUT_LINE('-----	-------');
LOOP
FETCH emp_cur INTO v_empno, v_ename;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || '	' || v_ename);
END LOOP;
CLOSE emp_cur;
END;
/
\set PLSQL_MODE OFF
select list_emp();

NOTICE:  7369   SMITH
NOTICE:  7499   ALLEN
NOTICE:  7521   WARD
NOTICE:  7566   JONES
NOTICE:  7654   MARTIN
NOTICE:  7698   BLAKE
NOTICE:  7782   CLARK
NOTICE:  7788   SCOTT
NOTICE:  7839   KING
NOTICE:  7844   TURNER
NOTICE:  7876   ADAMS
NOTICE:  7900   JAMES
NOTICE:  7902   FORD
NOTICE:  7934   MILLER
 LIST_EMP 
----------
 
(1 row)

\set PLSQL_MODE ON
CREATE OR REPLACE PROCEDURE emp_job (
p_empno	IN OUT numeric,
p_ename	IN OUT varchar2,
p_job	OUT	varchar2
)
IS
v_empno	emp.empno%TYPE; --函数参数不支持此种格式的定义
v_ename	emp.ename%TYPE;
v_job	emp.job%TYPE;
BEGIN
SELECT ename, job INTO v_ename, v_job FROM emp WHERE empno = p_empno;
p_ename := v_ename;
p_job	:= v_job;
DBMS_OUTPUT.PUT_LINE('Found employee # ' || p_empno);
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
SELECT empno, job INTO v_empno, v_job FROM emp
WHERE ename = p_ename;
p_empno := v_empno;
p_job	:= v_job;
DBMS_OUTPUT.PUT_LINE('Found employee ' || p_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Could not find an employee with ' ||
'number, ' || p_empno || ' nor name, '	|| p_ename);
p_empno := NULL;
p_ename := NULL;
p_job	:= NULL;
END;
END;
/

DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_job   emp.job%TYPE;
re record;
BEGIN
v_empno := 7782;
v_ename := 'CLARK';
re := emp_job(v_empno, v_ename);
DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno);
DBMS_OUTPUT.PUT_LINE('Name  : ' || v_ename);
DBMS_OUTPUT.PUT_LINE('Job   : ' || re.p_job);
END;
/
\set PLSQL_MODE OFF

Found employee CLARK
Employee No: 7782
Name	: CLARK
Job	: MANAGER

CREATE ROLE admins;

CREATE SEQUENCE serial START WITH 101; 

SELECT serial.NEXTVAL FROM DUAL;

SERIAL.NEXTVAL  
-----------------
             101
(1 row)

CREATE SEQUENCE supplier_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
NOCACHE;

SELECT supplier_seq.NEXTVAL FROM DUAL;

SUPPLIER_SEQ.NEXTVAL  
---------------------------------------
                     1
(1 row)

CREATE TABLE dept (
deptno	NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname	VARCHAR2(14),
loc	VARCHAR2(13)
);

CREATE TABLE emp (
empno	NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename	VARCHAR2(10),
job	VARCHAR2(9),
mgr	NUMBER(4),
hiredate	DATE,
sal	NUMBER(7,2),
comm	NUMBER(7,2),
deptno	NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);

CREATE TABLE dept (
deptno	NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname	VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
loc	VARCHAR2(13)
);

CREATE TABLE emp (
empno	NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename	VARCHAR2(10),
job	VARCHAR2(9),
mgr	NUMBER(4),
hiredate	DATE,
sal	NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm	NUMBER(7,2),
deptno	NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);

CREATE TABLE emp (
empno	NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename	VARCHAR2(10),
job	VARCHAR2(9),
mgr	NUMBER(4),
hiredate	DATE,
sal	NUMBER(7,2),
comm	NUMBER(7,2),
deptno	NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno),
CONSTRAINT new_emp_ck CHECK (ename IS NOT NULL AND empno > 7000)
);

CREATE TABLE jobhist (
empno	NUMBER(4) NOT NULL,
startdate	DATE NOT NULL,
enddate	DATE,
job	VARCHAR2(9),
sal	NUMBER(7,2),
comm	NUMBER(7,2),
deptno	NUMBER(2),
chgdesc	VARCHAR2(80),
CONSTRAINT jobhist_pk PRIMARY KEY (empno, startdate)

CREATE TABLE emp (
empno	NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename	VARCHAR2(10),
job	VARCHAR2(9) DEFAULT 'SALESMAN',
mgr	NUMBER(4),
hiredate	DATE DEFAULT SYSDATE,
sal	NUMBER(7,2),
comm	NUMBER(7,2),
deptno	NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);

CREATE TABLE dept (
deptno	NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
dname	VARCHAR2(14),
loc	VARCHAR2(13)
) TABLESPACE diskvol1;

CREATE TABLE CUSTOMER 
( 
    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
    FIRST_NAME  VARCHAR2(30) NOT NULL, 
    LAST_NAME   VARCHAR2(30) NOT NULL, 
    PHONE        VARCHAR2(15) NOT NULL, 
    EMAIL        VARCHAR2(80), 
    STATUS       CHAR(1) 
) 
PARTITION BY RANGE (CUSTOMER_ID) 
( 
    PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE custs01, 
    PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE custs01 
);

CREATE TABLE CUSTOMER 
( 
    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
    FIRST_NAME  VARCHAR2(30) NOT NULL, 
    LAST_NAME   VARCHAR2(30) NOT NULL, 
    PHONE        VARCHAR2(15) NOT NULL, 
    EMAIL        VARCHAR2(80), 
    STATUS       CHAR(1) 
) 
PARTITION BY LIST (CUSTOMER_ID) 
( 
    PARTITION CUS_PART1 VALUES(100000,200000) TABLESPACE custs01, 
    PARTITION CUS_PART2 VALUES(300000) TABLESPACE custs01 
);

CREATE VIEW dept_30 AS SELECT * FROM emp WHERE deptno = 30; 

DELETE FROM jobhist WHERE empno = 7900; 

DELETE FROM jobhist; 

CREATE OR REPLACE FUNCTION delete_sal (eno in int) return void
IS
type t_table is table of emp;
var_sal t_table;
temp int;
BEGIN
delete from emp where empno = eno returning * bulk collect into var_sal ;
for i in 1..var_sal.count loop
    DBMS_OUTPUT.PUT_LINE(var_sal[i].empno); 
	DBMS_OUTPUT.PUT_LINE(var_sal[i].ENAME); 
	DBMS_OUTPUT.PUT_LINE(var_sal[i].JOB);
	DBMS_OUTPUT.PUT_LINE(var_sal[i].HIREDATE);
end loop;

END;
/

| DROP FUNCTION emp_comp(NUMBER, NUMBER); 

DROP INDEX name_idx; 

DROP PROCEDURE select_emp;

DROP SEQUENCE serial; 

DROP TABLE emp;

DROP VIEW dept_30;

INSERT INTO emp VALUES (8021,'JOHN','SALESMAN',7698,'22-FEB-07',1250,500,30); 

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, deptno) VALUES (8022,'PETERS','CLERK',7698,'03-DEC-06',950,30); 

INSERT INTO emp VALUES (8023,'FORD','ANALYST',7566,NULL,3000,NULL,20);

CREATE TABLE deptnames (
deptname	VARCHAR2(14)
);
INSERT INTO deptnames SELECT dname FROM dept;

--session1：
create table test(id int, num int);
insert into test values(1,2);
begin;
lock table test in SHARE mode nowait;

--session2执行下列命令会一直等待锁释放
update test set num = 3 where id = 1;

ROLLBACK;

INSERT INTO dept VALUES (50, 'HR', 'NEW YORK');
SAVEPOINT depts;
INSERT INTO emp (empno, ename, deptno) VALUES (9001, 'JONES', 50);
INSERT INTO emp (empno, ename, deptno) VALUES (9002, 'ALICE', 50);
ROLLBACK TO SAVEPOINT depts;

BEGIN;
INSERT INTO dept VALUES (50, 'HR', 'NEW YORK');
SAVEPOINT depts;
INSERT INTO emp (empno, ename, deptno) VALUES (9001, 'JONES', 50);
INSERT INTO emp (empno, ename, deptno) VALUES (9002, 'ALICE', 50);
SAVEPOINT emps;
INSERT INTO jobhist VALUES (9001,'17-SEP-07',NULL,'CLERK',800,NULL,50,'New Hire');
INSERT INTO jobhist VALUES (9002,'20-SEP-07',NULL,'CLERK',700,NULL,50,'New Hire');
ROLLBACK TO depts;
COMMIT;

select job,sum(SAL) from jobhist group by job;

      JOB    | SUM(SAL)  
-------------+--------------
 CLERK       |     7140
 PRESIDENT   |     5000
 MANAGER     |     8275
 SALESMAN    |     5600
 ANALYST     |     6000
(5 rows)

create table test1(id int， sal int);
Insert into test1 values(1, 20000);
Insert into test1 values(2, 30000);
create table test2(id int， sal int);
Insert into test2 values(1, 30000);
Insert into test2 values(2, 40000);
select * from test1 union select * from test2;
ID  |  SAL  
----+----------
  1 | 30000
  2 | 40000
  1 | 20000
  2 | 30000
(4 rows)

create table test1(id int, sal int);
Insert into test1 values(1, 20000);
Insert into test1 values(2, 30000);
create table test2(id int, sal int);
Insert into test2 values(1, 30000);
Insert into test2 values(2, 40000);
select sal from test1 minus select sal from test2;

       SAL
---------------------
     20000

SELECT ename FROM emp ORDER BY empno; 

SELECT d.deptno, d.dname, e.empno, e.ename, e.mgr, e.hiredate
FROM emp e, dept d
WHERE d.deptno = e.deptno;

 deptno |   dname    | empno | ename  | mgr  |      hiredate       
--------+------------+-------+--------+------+---------------------
     30 | SALES      |  7499 | ALLEN  | 7698 | 1981-02-20 00:00:00
     30 | SALES      |  7521 | WARD   | 7698 | 1981-02-22 00:00:00
     20 | RESEARCH   |  7566 | JONES  | 7839 | 1981-04-02 00:00:00
     30 | SALES      |  7654 | MARTIN | 7698 | 1981-09-28 00:00:00
     30 | SALES      |  7698 | BLAKE  | 7839 | 1981-05-01 00:00:00
     10 | ACCOUNTING |  7782 | CLARK  | 7839 | 1981-06-09 00:00:00
     20 | RESEARCH   |  7788 | SCOTT  | 7566 | 1987-04-19 00:00:00
     10 | ACCOUNTING |  7839 | KING   |      | 1981-11-17 00:00:00
     30 | SALES      |  7844 | TURNER | 7698 | 1981-09-08 00:00:00
     20 | RESEARCH   |  7876 | ADAMS  | 7788 | 1987-05-23 00:00:00
     30 | SALES      |  7900 | JAMES  | 7698 | 1981-12-03 00:00:00
     20 | RESEARCH   |  7902 | FORD   | 7566 | 1981-12-03 00:00:00
     10 | ACCOUNTING |  7934 | MILLER | 7782 | 1982-01-23 00:00:00
     20 | RESEARCH   |  7369 | SMITH  | 7902 | 1980-12-17 00:00:00
(14 rows)

SELECT deptno, SUM(sal) AS total
FROM emp
GROUP BY deptno;

 deptno |  total   
--------+----------------
     10 |  8750.00
     30 |  9400.00
     20 | 11075.00
(3 rows)

SELECT deptno, SUM(sal) AS total
FROM emp
GROUP BY deptno
HAVING SUM(sal) < 10000;

 deptno |  total  
--------+---------
     10 | 8750.00
     30 | 9400.00
(2 rows)

SELECT * FROM dept ORDER BY dname;

 deptno |   dname    |   loc    
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     40 | OPERATIONS | BOSTON
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
(4 rows)

SELECT * FROM dept ORDER BY 2;

 deptno |   dname    |   loc    
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     40 | OPERATIONS | BOSTON
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
(4 rows)

TRUNCATE TABLE bigtable; 

UPDATE dept SET loc = 'AUSTIN' WHERE deptno = 20; 

UPDATE emp SET sal = sal \* 1.1, comm = comm + 500 WHERE job = 'SALESMAN'; 

\set PLSQL_MODE ON
CREATE OR REPLACE FUNCTION update_sal (eno in int) return int
IS
type t_table is table of emp;
var_sal t_table;
BEGIN
update emp set sal = sal + 1000  where empno = eno returning * bulk collect into var_sal;

for i in 1..var_sal.count loop
 DBMS_OUTPUT.PUT_LINE(var_sal[i].empno); 
	DBMS_OUTPUT.PUT_LINE(var_sal[i].ENAME); 
	DBMS_OUTPUT.PUT_LINE(var_sal[i].JOB);
	DBMS_OUTPUT.PUT_LINE(var_sal[i].HIREDATE);
end loop;
return var_sal.count;
END;
/
select update_sal(7876);
NOTICE:  7876
NOTICE:  ADAMS
NOTICE:  CLERK
NOTICE:  1987-05-23 00:00:00
 UPDATE_SAL 
------------
          1
(1 row)

CREATE TABLE sales_range
(
salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
sales_date    DATE
)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2010 VALUES LESS THAN(TO_DATE('01/02/2010','DD/MM/YYYY')),
PARTITION sales_feb2010 VALUES LESS THAN(TO_DATE('01/03/2010','DD/MM/YYYY')),
PARTITION sales_mar2010 VALUES LESS THAN(TO_DATE('01/04/2010','DD/MM/YYYY')),
PARTITION sales_apr2010 VALUES LESS THAN(TO_DATE('01/05/2010','DD/MM/YYYY')),
PARTITION sales_may2010 VALUES LESS THAN(TO_DATE('01/06/2010','DD/MM/YYYY'))
);

CREATE TABLE CUSTOMER 
( 
    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
    FIRST_NAME  VARCHAR2(30) NOT NULL, 
    LAST_NAME   VARCHAR2(30) NOT NULL, 
    PHONE        VARCHAR2(15) NOT NULL, 
    EMAIL        VARCHAR2(80), 
    STATUS       CHAR(1) 
) 
PARTITION BY LIST (CUSTOMER_ID) 
( 
    PARTITION CUS_PART1 VALUES(100000,200000) TABLESPACE custs01, 
    PARTITION CUS_PART2 VALUES(300000) TABLESPACE custs01 
);

create table test_part(
  id        number,
  pro_num   varchar2(40), 
  app_type  varchar2(40)
)
partition by hash(pro_num)
(
 partition part_01,
 partition part_02,
 partition part_03,
 partition part_04,
 partition part_05
);

CREATE TABLE sales
(
  dept_no     number,   
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST(country)
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);
 ALTER TABLE sales DROP PARTITION americas;

CREATE TABLE sales
(
  dept_no     number,   
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST(country)
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);
INSERT INTO sales VALUES
(10, '4519b', 'FRANCE', '17-Jan-2012', '45000'),
(20, '3788a', 'INDIA', '01-Mar-2012', '75000'),
(40, '9519b', 'US', '12-Apr-2012', '145000'),
(20, '3788a', 'PAKISTAN', '04-Jun-2012', '37500'),
(40, '4577b', 'US', '11-Nov-2012', '25000'),
(30, '7588b', 'CANADA', '14-Dec-2012', '50000'),
(30, '9519b', 'CANADA', '01-Feb-2012', '75000'),
(30, '4519b', 'CANADA', '08-Apr-2012', '120000'),
(40, '3788a', 'US', '12-May-2012', '4950'),
(10, '9519b', 'ITALY', '07-Jul-2012', '15000'),
(10, '9519a', 'FRANCE', '18-Aug-2012', '650000'),
(10, '9519b', 'FRANCE', '18-Aug-2012', '650000'),
(20, '3788b', 'INDIA', '21-Sept-2012', '5090'),
(40, '4788a', 'US', '23-Sept-2012', '4950'),
(40, '4788b', 'US', '09-Oct-2012', '15000'),
(20, '4519a', 'INDIA', '18-Oct-2012', '650000'),
(20, '4519b', 'INDIA', '2-Dec-2012', '5090');
--删除分区america中的数据
ALTER TABLE sales TRUNCATE PARTITION americas;

postgres=# \d+ sales
                                    Partitioned table "public.sales"
 Column  |        Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
---------+---------------------+-----------+----------+---------+----------+--------------+-------------
 dept_no | numeric             |           |          |         | main     |              | 
 part_no | oracle.varchar2     |           |          |         | extended |              | 
 country | oracle.varchar2(20) |           |          |         | extended |              | 
 date    | oracle.date         |           |          |         | plain    |              | 
 amount  | numeric             |           |          |         | main     |              | 
Partition key: LIST (country)
Partitions: americas FOR VALUES IN ('US', 'CANADA'),
            asia FOR VALUES IN ('INDIA', 'PAKISTAN'),
            europe FOR VALUES IN ('FRANCE', 'ITALY')

SELECT tableoid::regclass, * FROM sales;
 TABLEOID | DEPT_NO | PART_NO | COUNTRY  |        DATE         | AMOUNT 
----------+---------+---------+----------+---------------------+---------------
 europe   |      10 | 4519b   | FRANCE   | 2012-01-17 00:00:00 |  45000
 europe   |      10 | 9519b   | ITALY    | 2012-07-07 00:00:00 |  15000
 europe   |      10 | 9519a   | FRANCE   | 2012-08-18 00:00:00 | 650000
 europe   |      10 | 9519b   | FRANCE   | 2012-08-18 00:00:00 | 650000
 asia     |      20 | 3788a   | INDIA    | 2012-03-01 00:00:00 |  75000
 asia     |      20 | 3788a   | PAKISTAN | 2012-06-04 00:00:00 |  37500
 asia     |      20 | 3788b   | INDIA    | 2012-09-21 00:00:00 |   5090
 asia     |      20 | 4519a   | INDIA    | 2012-10-18 00:00:00 | 650000
 asia     |      20 | 4519b   | INDIA    | 2012-12-02 00:00:00 |   5090
(9 rows)

-- Insert Record
insert into sales_range values
(4205, 'stone', 876234, '2010-01-12'),
(7126, 'data', 648295, '2010-05-06'),
(3891, 'base', 237491, '2010-03-25'),
(5018, 'haha', 230329, '2010-02-09'),
(9201, 'hello', 239429, '2010-05-15'),
(2816, 'world', 457219, '2010-04-18');

delete from sales_range where salesman_name = ‘base’;

update sales_range set sales_amount = 520746 where salesman_name = ‘hello’;

select * from sales_range where salesman_name = ‘stone’;

-- Scan Single Partition
select * from sales_range PARTITION(sales_may2010);

'abc' LIKE 'abc'	true
'abc' LIKE 'a%'	true
'abc' LIKE '_b_'	true
'abc' LIKE 'c'	false

SELECT ADD_MONTHS('13-JUN-07',4) FROM DUAL;

ADD_MONTHS('13-JUN-07',4)  
----------------------------
 2007-10-13 00:00:00
(1 row)

SELECT ADD_MONTHS('31-DEC-06',2) FROM DUAL;

ADD_MONTHS('31-DEC-06',2)  
----------------------------
 2007-02-28 00:00:00
(1 row)


SELECT ADD_MONTHS('31-MAY-04',-3) FROM DUAL;

ADD_MONTHS('31-MAY-04',-3)  
-----------------------------
 2004-02-29 00:00:00
(1 row)

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40')  
-------------------------------------------------------
                                                2001
(1 row)

SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40')  
----------------------------------------------------------
                                                    2
(1 row)

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40')  
-------------------------------------------------------
                                                 16
(1 row)

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40')  
-----------------------------------------------------
                                                  20
(1 row)

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40') FROM DUAL;

EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40')  
---------------------------------------------------------
                                                    38
(1 row)

SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40.45') FROM DUAL;

 EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40.45')  
-----------------------------------------------------------
                                                    40.45
(1 row)

SELECT SYS_EXTRACT_UTC(TIMESTAMP '2020-03-28 11:30:00.00 -08:00') FROM DUAL;

SYS_EXTRACT_UTC(TIMESTAMP '2020-03-28 11:30:00.00 -08:00')  
-------------------------------------------------------------
 2020-03-28 03:30:00
(1 row)

SELECT MONTHS_BETWEEN('15-DEC-06','15-OCT-06') FROM DUAL;

MONTHS_BETWEEN('15-DEC-06','15-OCT-06')  
------------------------------------------
                                        2
(1 row)


SELECT MONTHS_BETWEEN('15-OCT-06','15-DEC-06') FROM DUAL;

MONTHS_BETWEEN('15-OCT-06','15-DEC-06')  
------------------------------------------
                                       -2
(1 row)


SELECT MONTHS_BETWEEN('31-JUL-00','01-JUL-00') FROM DUAL;

MONTHS_BETWEEN('31-JUL-00','01-JUL-00')  
------------------------------------------
                        0.967741935483871
(1 row)


SELECT MONTHS_BETWEEN('01-JAN-07','01-JAN-06') FROM DUAL;

MONTHS_BETWEEN('01-JAN-07','01-JAN-06')  
------------------------------------------
                                       12
(1 row)

SELECT NEXT_DAY(TO_DATE('13-AUG-07','DD-MON-YY'),'SUNDAY') FROM DUAL;

NEXT_DAY(TO_DATE('13-AUG-07','DD-MON-YY'),'SUNDAY')  
-----------------------------------------------------
 2007-08-19 00:00:00
(1 row)

SELECT NEXT_DAY(TO_DATE('13-AUG-07','DD-MON-YY'),'MON') FROM DUAL;

NEXT_DAY(TO_DATE('13-AUG-07','DD-MON-YY'),'MON')  
---------------------------------------------------
 2007-08-20 00:00:00
(1 row)

SELECT NEXT_DAY(current_timestamp,'SUNDAY') FROM DUAL;

 NEXT_DAY(CURRENT_TIMESTAMP,'SUNDAY')  
---------------------------------------
 2020-10-04 17:08:05
(1 row)

SELECT SYSDATE, LAST_DAY(SYSDATE)Last, LAST_DAY(SYSDATE)-SYSDATE "Left" FROM DUAL;
     ORA_SYS_NOW          |        Last          | Left 
--------------------------+----------------------+------
   2020-09-29 17:12:29    | 2020-09-30 17:12:29  |   1
(1 row)

SELECT NEW_TIME(TO_DATE('08-13-07 10:35:15','MM-DD-YY HH24:MI:SS'),'AST', 'PST') "Pacific Standard Time" FROM DUAL;

Pacific Standard Time 
-----------------------
 2007-08-13 06:35:15
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('1950','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;

   Century   
------------------
 01-JAN-2001
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('1951','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;

   Century   
-------------------
 01-JAN-2001
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('30-JUN-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL;

    Year     
-------------
 01-JAN-1999
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('01-JUL-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL;

Year
-------------
01-JAN-2000
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('30-JUN-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL;

ISO Year
-------------
29-DEC-2003
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('01-JUL-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL;

ISO Year
-------------
03-JAN-2005
(1 row)

SELECT ROUND(TO_DATE('15-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL;

       Quarter       
---------------------
 2007-01-01 00:00:00
(1 row)


SELECT ROUND(TO_DATE('16-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL;

       Quarter       
---------------------
 2007-04-01 00:00:00
(1 row)

SELECT ROUND(TO_DATE('15-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL;

        Month        
---------------------
 2007-12-01 00:00:00
(1 row)

SELECT ROUND(TO_DATE('16-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL;

        Month        
---------------------
 2008-01-01 00:00:00
(1 row)

SELECT ROUND(TO_DATE('18-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL;

        Week         
---------------------
 2007-01-15 00:00:00
(1 row)

SELECT ROUND(TO_DATE('19-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL;

        Week         
---------------------
 2007-01-22 00:00:00
(1 row)

SELECT ROUND(TO_DATE('01-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;

      ISO Week       
---------------------
 2003-12-29 00:00:00
(1 row)

SELECT ROUND(TO_DATE('02-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;

      ISO Week       
---------------------
 2004-01-05 00:00:00
(1 row)

SELECT ROUND(TO_DATE('05-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL;

        Week         
---------------------
 2007-03-08 00:00:00
(1 row)


SELECT ROUND(TO_DATE('04-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL;

        Week         
---------------------
 2007-03-01 00:00:00
(1 row)

SELECT ROUND(TO_DATE('04-AUG-07 11:59:59 AM','DD-MON-YY HH:MI:SS AM'),'J') "Day" FROM DUAL;

         Day         
---------------------
 2007-08-04 00:00:00
(1 row)

SELECT ROUND(TO_DATE('04-AUG-07 12:00:00 PM','DD-MON-YY HH:MI:SS AM'),'J') "Day" FROM DUAL;

         Day         
---------------------
 2007-08-05 00:00:00
(1 row)

SELECT ROUND(TO_DATE('08-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL;

     Day of Week     
---------------------
 2007-08-05 00:00:00
(1 row)

SELECT ROUND(TO_DATE('09-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL;

     Day of Week     
---------------------
 2007-08-12 00:00:00
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:29','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL;

Hour
--------------------
09-AUG-07 08:00:00
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL;

Hour
--------------------
09-AUG-07 09:00:00
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30:29','DD-MON-YY HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL;

Minute
--------------------
09-AUG-07 08:30:00
(1 row)

SELECT TO_CHAR(ROUND(TO_DATE('09-AUG-07 08:30:30','DD-MON-YY HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL;

Minute
--------------------
09-AUG-07 08:31:00
(1 row)

SELECT TO_CHAR(TRUNC(TO_DATE('1951','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;

   Century   
-------------
 01-JAN-1901
(1 row)

SELECT TO_CHAR(TRUNC(TO_DATE('01-JUL-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL;

    Year     
-------------
 01-JAN-1999
(1 row)

SELECT TO_CHAR(TRUNC(TO_DATE('01-JUL-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL;

  ISO Year   
-------------
 29-DEC-2003
(1 row)

SELECT TRUNC(TO_DATE('16-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL;

       Quarter       
---------------------
 2007-01-01 00:00:00
(1 row)

SELECT TRUNC(TO_DATE('16-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL;

        Month        
---------------------
 2007-12-01 00:00:00
(1 row)

SELECT TRUNC(TO_DATE('19-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL;

        Week         
---------------------
 2007-01-15 00:00:00
(1 row)

SELECT TRUNC(TO_DATE('02-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;

      ISO Week       
--------------------------------
 2003-12-29 00:00:00
(1 row)

SELECT TRUNC(TO_DATE('21-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL;

        Week         
--------------------------------
 2007-03-15 00:00:00
(1 row)

SELECT TRUNC(TO_DATE('04-AUG-07 12:00:00 PM','DD-MON-YY HH:MI:SS AM'),'J') "Day" FROM DUAL;

         Day         
--------------------------------
 2007-08-04 00:00:00
(1 row)

SELECT TRUNC(TO_DATE('09-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL;

     Day of Week     
--------------------------------
 2007-08-05 00:00:00
(1 row)

SELECT TO_CHAR(TRUNC(TO_DATE('09-AUG-07 08:30','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL;

Hour
--------------------------------
09-AUG-07 08:00:00
(1 row)

SELECT TO_CHAR(TRUNC(TO_DATE('09-AUG-07 08:30:30','DD-MON-YY HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL;

Minute
------------------------------
09-AUG-07 08:30:00
(1 row)

SELECT CURRENT_DATE FROM DUAL;

        DATE         
---------------------
 2020-09-29 17:28:28
(1 row)

SELECT LOCALTIMESTAMP FROM DUAL;

         TIMESTAMP          
----------------------------
 2020-09-29 17:28:41.485867
(1 row)

SELECT LOCALTIMESTAMP(2) FROM DUAL;

       TIMESTAMP        
------------------------
 2020-09-29 17:30:27.93
(1 row)

SELECT SYSDATE FROM DUAL;

     ORA_SYS_NOW     
---------------------
 2020-09-29 17:29:11
(1 row)

SELECT SYSTIMESTAMP FROM DUAL;
        ORA_SYS_NOW         
-----------------------------------------
 2020-09-29 17:30:02.186407
(1 row)

SELECT SESSIONTIMEZONE FROM DUAL;
 ORA_SESSION_TIMEZONE 
--------------------------------------------
 +08:00
(1 row)

SELECT CURRENT_TIMESTAMP FROM DUAL;
        CURRENT_TIMESTAMP         
-----------------------------------------------------
 2020-09-29 17:31:41.56663 +08:00
(1 row)

SELECT DBTIMEZONE FROM DUAL;
 ORA_DBTIMEZONE 
--------------------------------
 +08:00
(1 row)

SELECT deptno, MIN(t.mgr)  KEEP (DENSE_RANK FIRST ORDER BY t.sal) a   
from emp t  group by deptno;

 DEPTNO |  A   
--------+------
     10 | 7782
     30 | 7698
     20 | 7902
(3 rows)

SELECT MGR , COVAR_POP ( SYSDATE - HIREDATE , sal )  AS covar_pop ,
COVAR_SAMP ( SYSDATE - HIREDATE , sal )  AS covar_samp
FROM emp GROUP BY MGR ORDER BY MGR , covar_pop , covar_samp ;

 MGR  |     COVAR_POP     |     COVAR_SAMP     
------+-------------------+---------------------
 7566 |                 0 |                  0
 7698 |             43976 |              54970
 7782 |                 0 |                   
 7788 |                 0 |                   
 7839 | 10547.22222222222 | 15820.833333333332
 7902 |                 0 |                  0
      |                 0 |                   
(7 rows)

SELECT MGR , COVAR_SAMP( SYSDATE - HIREDATE , sal )  AS covar_pop ,
COVAR_SAMP ( SYSDATE - HIREDATE , sal )  AS covar_samp
FROM emp GROUP BY MGR ORDER BY MGR , covar_pop , covar_samp ;

 MGR  |     COVAR_POP      |     COVAR_SAMP     
------+--------------------+---------------------
 7566 |                  0 |                  0
 7698 |              54970 |              54970
 7782 |                    |                   
 7788 |                    |                   
 7839 | 15820.833333333332 | 15820.833333333332
 7902 |                  0 |                  0
      |                    |                   
(7 rows)

SELECT deptno , ename , sal, cume_dist ( ) over ( partition BY deptno ORDER BY sal DESC ) "RANK" FROM emp WHERE sal>2000;
 DEPTNO | ENAME  |  SAL  |        RANK         
--------+--------+-------+---------------------
     10 | KING   | 10000 |  0.3333333333333333
     10 | CLARK  |  7450 |  0.6666666666666666
     10 | MILLER |  6300 |                   1
     20 | ADAMS  |  8100 | 0.16666666666666666
     20 | FORD   |  8000 |                 0.5
     20 | SCOTT  |  8000 |                 0.5
     20 | JONES  |  7975 |  0.6666666666666666
     20 | SMITH  |  6800 |  0.8333333333333334
     20 | SMITH  |  4800 |                   1
     30 | BLAKE  | 11850 | 0.16666666666666666
     30 | ALLEN  |  7600 |  0.3333333333333333
     30 | TURNER |  6500 |                 0.5
     30 | WARD   |  6250 |  0.8333333333333334
     30 | MARTIN |  6250 |  0.8333333333333334
     30 | JAMES  |  5950 |                   1
(15 rows)

select empno,ename,sal, deptno, rank() over (partition by deptno order by sal)  from emp;
 EMPNO | ENAME  |  SAL  | DEPTNO | RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL)  
-------+--------+-------+--------+---------------------------------------------------
  7934 | MILLER |  6300 |     10 |                                              1
  7782 | CLARK  |  7450 |     10 |                                              2
  7839 | KING   | 10000 |     10 |                                              3
  7469 | SMITH  |  4800 |     20 |                                              1
  7369 | SMITH  |  6800 |     20 |                                              2
  7566 | JONES  |  7975 |     20 |                                              3
  7902 | FORD   |  8000 |     20 |                                              4
  7788 | SCOTT  |  8000 |     20 |                                              4
  7876 | ADAMS  |  8100 |     20 |                                              6
  7900 | JAMES  |  5950 |     30 |                                              1
  7521 | WARD   |  6250 |     30 |                                              2
  7654 | MARTIN |  6250 |     30 |                                              2
  7844 | TURNER |  6500 |     30 |                                              4
  7499 | ALLEN  |  7600 |     30 |                                              5
  7698 | BLAKE  | 11850 |     30 |                                              6
(15 rows)

postgres=# SELECT deptno, ename, sal, DENSE_RANK ( ) OVER ( partition BY deptno ORDER BY sal DESC ) "RANK" FROM emp WHERE sal>6500;
 DEPTNO |  ENAME |  SAL  | RANK 
--------+--------+-------+---------
     10 | KING   | 10000 |    1
     10 | CLARK  |  7450 |    2
     20 | ADAMS  |  8100 |    1
     20 | SCOTT  |  8000 |    2
     20 | FORD   |  8000 |    2
     20 | JONES  |  7975 |    3
     20 | SMITH  |  6800 |    4
     30 | BLAKE  | 11850 |    1
     30 | ALLEN  |  7600 |    2
(9 rows)

select empno,job, sal, percent_rank()  over (partition by job order by sal)  from emp;
 EMPNO |    JOB    |  SAL  | PERCENT_RANK() OVER (PARTITION BY JOB ORDER BY SAL)  
-------+-----------+-------+-------------------------------------------------------
  7902 | ANALYST   |  8000 |                                                    0
  7788 | ANALYST   |  8000 |                                                    0
  7469 | CLERK     |  4800 |                                                    0
  7900 | CLERK     |  5950 |                                                 0.25
  7934 | CLERK     |  6300 |                                                  0.5
  7369 | CLERK     |  6800 |                                                 0.75
  7876 | CLERK     |  8100 |                                                    1
  7782 | MANAGER   |  7450 |                                                    0
  7566 | MANAGER   |  7975 |                                                  0.5
  7698 | MANAGER   | 11850 |                                                    1
  7839 | PRESIDENT | 10000 |                                                    0
  7521 | SALESMAN  |  6250 |                                                    0
  7654 | SALESMAN  |  6250 |                                                    0
  7844 | SALESMAN  |  6500 |                                   0.6666666666666666
  7499 | SALESMAN  |  7600 |                                                    1
(15 rows)

create table test(id int, num int);
insert into test values(1, 298),(2, 368),(3, 458),(4, 328),(5, 108),(6, 667),(7, 123),(8, 555);
select count(*),regr_slope(t.num,test.num) from 
(select id-1 as id, num from test) as t,test where t.id=test.id ;
 COUNT(*) | REGR_SLOPE(T.NUM,TEST.NUM)  
----------+-----------------------------
       28 |         -0.8425517064826854
(1 row)

select count(*),regr_intercept(t.num,test.num) from 
(select id-1 as id, num from test) as t,test where t.id=test.id ;
 COUNT(*) | REGR_INTERCEPT(T.NUM,TEST.NUM)  
----------+---------------------------------
       28 |               655.2852157477586
(1 row)

create table test(id int, num int);
insert into test values(1, 298),(2, null),(3, null),(null, 328),(null, 108),(6, 667);
select regr_count(id, num) from test;

REGR_COUNT(ID, NUM)  
----------------------
                    2
(1 row)

create table test(id int, num int);
insert into test values(1, 298),(2, 368),(3, 458),(4, 328),(5, 108),(6, 667),(7, 123),(8, 555);

select count(*),regr_r2(t.num,test.num) from 
(select id-1 as id, num from test) as t,test where t.id=test.id ;

 COUNT(*) | REGR_R2(T.NUM,TEST.NUM)  
----------+--------------------------
       28 |       0.6088521468006012
(1 row)

select ename,hiredate,sal,stddev(sal) over(order by hiredate) a from emp where deptno=20;
ENAME |      HIREDATE       | SAL  |         A         
-------+---------------------+------+-------------------
 SMITH | 1980-12-17 00:00:00 | 4800 | 1414.213562373095
 SMITH | 1980-12-17 00:00:00 | 6800 | 1414.213562373095
 JONES | 1981-04-02 00:00:00 | 7975 | 1605.264775667865
 FORD  | 1981-12-03 00:00:00 | 8000 | 1503.935808692202
 SCOTT | 1987-04-19 00:00:00 | 8000 |  1393.24262065155
 ADAMS | 1987-05-23 00:00:00 | 8100 | 1309.428946780491
(6 rows)

select deptno,ename,sal,stddev_pop(sal) over (partition by deptno) as pop from emp;
 DEPTNO | ENAME  |  SAL  |        POP        
---------------+------------+------------+-------------------
     10 | KING   | 10000 | 1546.142152441216
     10 | CLARK  |  7450 | 1546.142152441216
     10 | MILLER |  6300 | 1546.142152441216
     20 | SCOTT  |  8000 | 1195.339619336511
     20 | SMITH  |  6800 | 1195.339619336511
     20 | JONES  |  7975 | 1195.339619336511
     20 | FORD   |  8000 | 1195.339619336511
     20 | ADAMS  |  8100 | 1195.339619336511
     20 | SMITH  |  4800 | 1195.339619336511
     30 | ALLEN  |  7600 | 2057.506581601462
     30 | MARTIN |  6250 | 2057.506581601462
     30 | TURNER |  6500 | 2057.506581601462
     30 | JAMES  |  5950 | 2057.506581601462
     30 | BLAKE  | 11850 | 2057.506581601462
     30 | WARD   |  6250 | 2057.506581601462
(15 rows)

begin;
CREATE TABLE employees (manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT);
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(100, 'De Haan', '2018-05-01',11000);      
INSERT INTO employees VALUES(100, 'Errazuriz', '2017-07-21', 1400);
INSERT INTO employees VALUES(100, 'Hartstein', '2019-05-01',14000);     
INSERT INTO employees VALUES(100, 'Raphaely', '2017-07-22', 1700);
INSERT INTO employees VALUES(100, 'Weiss',  '2019-07-11',13500);     
INSERT INTO employees VALUES(100, 'Russell', '2019-10-05', 13000);
INSERT INTO employees VALUES(100, 'Partners',  '2018-12-01',14000);     
INSERT INTO employees VALUES(200, 'Ross',  '2019-06-11',13500);     
INSERT INTO employees VALUES(200, 'Bell', '2019-05-25', 13000);
INSERT INTO employees VALUES(200, 'Part',  '2018-08-11',14000);
COMMIT;
SELECT manager_id, last_name, hiredate, salary,STDDEV_SAMP(salary) OVER (PARTITION BY manager_id 
ORDER BY hiredate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev FROM employees ORDER BY manager_id, last_name, hiredate, salary, cum_sdev;

 MANAGER_ID | LAST_NAME |  HIREDATE  | SALARY |     CUM_SDEV      
------------+-----------+------------+--------+--------------------
        100 | De Haan   | 2018-05-01 |  11000 | 4702.127178203499
        100 | Errazuriz | 2017-07-21 |   1400 |  212.132034355964
        100 | Hartstein | 2019-05-01 |  14000 | 6340.346993658943
        100 | Partners  | 2018-12-01 |  14000 | 6064.899009876422
        100 | Raphaely  | 2017-07-01 |   1700 |                  
        100 | Raphaely  | 2017-07-22 |   1700 |  173.205080756888
        100 | Russell   | 2019-10-05 |  13000 | 6026.474330580265
        100 | Weiss     | 2019-07-11 |  13500 |  6244.31169717116
        200 | Bell      | 2019-05-25 |  13000 |  707.106781186548
        200 | Part      | 2018-08-11 |  14000 |                  
        200 | Ross      | 2019-06-11 |  13500 |               500
(11 rows)

SELECT empno, sal, VAR_pop(sal) OVER (ORDER BY hiredate) as "var_pop"
FROM emp ORDER BY ename, sal, "var_pop" ;

EMPNO |  SAL  |       var_pop        
------------+----------+----------------------
  7876 |  8100 | 2799183.333333333333
  7499 |  7600 | 1386666.666666666667
  7698 | 11850 | 4748003.472222222222
  7782 |  7450 | 4070841.836734693878
  7902 |  8000 | 3328381.076388888889
  7900 |  5950 | 3328381.076388888889
  7566 |  7975 |              1251400
  7839 | 10000 |           3729306.25
  7654 |  6250 | 3401111.111111111111
  7934 |  6300 | 3166597.633136094675
  7788 |  8000 | 2967286.352040816327
  7469 |  4800 |              1000000
  7369 |  6800 |              1000000
  7844 |  6500 |       3678505.859375
  7521 |  6250 |           1044218.75
(15 rows)

SELECT empno, sal, VAR_samp(sal)  OVER (ORDER BY hiredate) as "var_samp"
FROM emp ORDER BY ename, sal, "var_samp" ;

 EMPNO |  SAL  |       var_samp       
-------+-------+-----------------------
  7876 |  8100 |              2999125
  7499 |  7600 |              2080000
  7698 | 11850 | 5697604.166666666667
  7782 |  7450 |  4749315.47619047619
  7902 |  8000 | 3630961.174242424242
  7900 |  5950 | 3630961.174242424242
  7566 |  7975 |              1564250
  7839 | 10000 | 4143673.611111111111
  7654 |  6250 |              3826250
  7934 |  6300 | 3430480.769230769231
  7788 |  8000 | 3195539.148351648352
  7469 |  4800 |              2000000
  7369 |  6800 |              2000000
  7844 |  6500 | 4204006.696428571429
  7521 |  6250 | 1392291.666666666667
(15 rows)

SELECT empno, sal, VARIANCE(sal) OVER (ORDER BY hiredate) "Variance"
FROM emp ORDER BY ename, sal, "Variance";
 EMPNO |  SAL  |       Variance       
-------+-------+------------------------
  7876 |  8100 |              2999125
  7499 |  7600 |              2080000
  7698 | 11850 | 5697604.166666666667
  7782 |  7450 |  4749315.47619047619
  7902 |  8000 | 3630961.174242424242
  7900 |  5950 | 3630961.174242424242
  7566 |  7975 |              1564250
  7839 | 10000 | 4143673.611111111111
  7654 |  6250 |              3826250
  7934 |  6300 | 3430480.769230769231
  7788 |  8000 | 3195539.148351648352
  7469 |  4800 |              2000000
  7369 |  6800 |              2000000
  7844 |  6500 | 4204006.696428571429
  7521 |  6250 | 1392291.666666666667
(15 rows)

postgres=# select corr(t.num,test.num) from 
postgres-# (select id-1 as id, num from test) as t,test where t.id=test.id ;
 CORR(T.NUM,TEST.NUM)  
------------------------------
   -0.7802897838627655
(1 row)

SELECT * FROM test;

a
---
1
2
3
(3 rows)

SELECT a,
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 'other'
END
FROM test;

a | case
---+-------
1 | one
2 | two
3 | other
(3 rows)

SELECT a,
CASE a WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END
FROM test;

a | case
--+-------
1 | one
2 | two
3 | other
(3 rows)

SELECT ... 	WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

create table test(id int, math int,chinese int, english int);
insert into test(id, math) values(1,88);
insert into test(id, english) values(2,98);
insert into test(id, chinese) values(3,97);
insert into test(id) values(4);

SELECT COALESCE(math, chinese, english, 0) from test;
COALESCE 
-------------------
       88
       98
       97
        0
(4 rows)

SELECT COALESCE(math, chinese, english) from test; 
COALESCE 
------------------
       88
       98
       97
         
(4 rows)

create table test(id int, math int);
insert into test values(1,88),(2,89),(3,100),(4,99);
select nullif(math, 100) from test;
 NULLIF 
----------------
     88
     89
       
     99
(4 rows)

select lnnvl(math = 100) from test;
 LNNVL(MATH = 100)  
-----------------------------------
 t
 t
 f
 t
(4 rows)

select nanvl('NaN',-1) from dual;
NANVL('NAN',-1)  
-----------------------
            -1
(1 row)

select nanvl(2,-1) from dual;
 NANVL(2,-1)  
---------------------
            2
(1 row)

select NVL(null, 1) from dual;
 NVL(NULL, 1)  
---------------
 1
(1 row)

select NVL(2, null) from dual;
 NVL(2, NULL)  
---------------
 2
(1 row)

select NVL(null, null) from dual;
 NVL(NULL, NULL)  
------------------
 
(1 row)

select math, NVL2(math, 'not null','is null') from test;
 MATH | NVL2(MATH, 'NOT NULL','IS NULL')  
------+-----------------------------------
   88 | not null
      | is null
      | is null
      | is null
(4 rows)

select EMPNO, decode(DEPTNO, 10, 1, 20, 2, 30,3, 0) from emp;
 EMPNO | DECODE(DEPTNO, 10, 1, 20, 2, 30,3, 0)  
-------+----------------------------------------
  7521 |                                      3
  7566 |                                      2
  7654 |                                      3
  7782 |                                      1
  7788 |                                      2
  7839 |                                      1
  7844 |                                      3
  7900 |                                      3
  7902 |                                      2
  7934 |                                      1
  7876 |                                      2
  7469 |                                      2
  7698 |                                      3
  7369 |                                      2
  7499 |                                      3
(15 rows)

create table test(id int, name varchar2(10), chinese number, english number, math number, physical number);
insert into test values(1,'Tom', 91, 89, 69, 88);
insert into test values(2,'Alice', 88, 81, 77, 100);
select name, greatest(chinese, english, math, physical) from test;

SELECT GREATEST('HAPPY', 'HAPPEN', 'HAPPINESS') "Greatest" FROM DUAL;

SELECT GREATEST (1, '3.935', '2.4') "Greatest" FROM DUAL;

create table test(id int, name varchar2(10), chinese number, english number, math number, physical number);
insert into test values(1,'Tom', 91, 89, 69, 88);
insert into test values(2,'Alice', 88, 81, 77, 100);
select name, least(chinese, english, math, physical) from test;

SELECT LEAST('HAPPY', 'HAPPEN', 'HAPPINESS')  "Least"  FROM DUAL;

SELECT LEAST (1, '3.925', '2.4')  "Least"  FROM DUAL;

SELECT dname FROM dept WHERE EXISTS (SELECT 1 FROM emp WHERE emp.deptno = dept.deptno);

dname
--------------
ACCOUNTING
RESEARCH
SALES
(3 rows)

dbms_output.put_line('Hello World');
Dbms_Output.Put_Line('Hello World');
DBMS_output.Put_line('Hello World');

Hello World!

HELLO WORLD!

x
last___name
a_$_Sign
Many$$$$$$$$signs_____
THIS_IS_AN_EXTREMELY_LONG_NAME
A1

BEGIN
NULL;
END;

DECLARE
v_numerator	NUMBER(2);
v_denominator	NUMBER(2);
v_result	NUMBER(5,2);
BEGIN
v_numerator := 75;
v_denominator := 14;
v_result := v_numerator / v_denominator;
DBMS_OUTPUT.PUT_LINE(v_numerator || ' divided by ' || v_denominator ||
' is ' || v_result);
END;

DECLARE
v_numerator	NUMBER(2);
v_denominator	NUMBER(2);
v_result	NUMBER(5,2);
BEGIN
v_numerator := 75;
v_denominator := 0;
v_result := v_numerator / v_denominator;
DBMS_OUTPUT.PUT_LINE(v_numerator || ' divided by ' || v_denominator ||
' is ' || v_result);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An exception occurred');
END;

An exception occurred

\set PLSQL_MODE ON
CREATE OR REPLACE PROCEDURE simple_procedure()
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('That''s all folks!');
END;

BEGIN
simple_procedure;
END;

That's all folks!

DROP PROCEDURE simple_procedure;

\set PLSQL_MODE ON
CREATE OR REPLACE FUNCTION simple_function()
RETURN VARCHAR2
IS
BEGIN
RETURN 'That''s All Folks!';
END simple_function;
/

\set PLSQL_MODE ON
CREATE OR REPLACE FUNCTION emp_comp (
p_sal      NUMBER,
p_comm   NUMBER
) RETURN NUMBER
IS
BEGIN
RETURN (p_sal + NVL(p_comm, 0)) * 24;
END emp_comp;
/

select simple_function();

  SIMPLE_FUNCTION  
-------------------
 That's All Folks!
(1 row)

SELECT empno "EMPNO", ename "ENAME", sal "SAL", comm "COMM",
emp_comp(sal, comm) "YEARLY COMPENSATION" FROM emp;

 EMPNO | ENAME  | SAL  | COMM | YEARLY COMPENSATION 
-------+--------+------+------+----------------------
  7369 | SMITH  |  800 |      |               19200
  7499 | ALLEN  | 1600 |  300 |               45600
  7521 | WARD   | 1250 |  500 |               42000
  7566 | JONES  | 2975 |      |               71400
  7654 | MARTIN | 1250 | 1400 |               63600
  7698 | BLAKE  | 2850 |      |               68400
  7782 | CLARK  | 2450 |      |               58800
  7788 | SCOTT  | 3000 |      |               72000
  7839 | KING   | 5000 |      |              120000
  7844 | TURNER | 1500 |    0 |               36000
  7876 | ADAMS  | 1100 |      |               26400
  7900 | JAMES  |  950 |      |               22800
  7902 | FORD   | 3000 |      |               72000
  7934 | MILLER | 1300 |      |               31200
(14 rows)

DROP FUNCTION simple_function;

\set PLSQL_MODE ON
CREATE OR REPLACE PROCEDURE emp_query (
p_deptno	IN	NUMBER,
p_empno	IN OUT NUMBER,
p_ename	IN OUT VARCHAR2,
p_job	OUT	VARCHAR2,
p_hiredate	OUT	DATE,
p_sal	OUT	NUMBER
)
IS
BEGIN
SELECT empno, ename, job, hiredate, sal
INTO p_empno, p_ename, p_job, p_hiredate, p_sal
FROM emp
WHERE deptno = p_deptno
AND (empno = p_empno
OR	ename = UPPER(p_ename));
END;
/

                  EMP_QUERY                   
-----------------------------------------------
 (7900,JAMES,CLERK,"1981-12-03 00:00:00",950)
(1 row)

REVOKE EXECUTE ON PROCEDURE list_emp FROM PUBLIC;

GRANT EXECUTE ON PROCEDURE list_emp TO john;

SHOW search_path;
search_path
----------------------
$user,public,sys,dbo
(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE dept_salary_rpt (p_deptno NUMBER)
IS
todays_date DATE := SYSDATE;
rpt_title VARCHAR2(60) := 'Report For Department # ' || p_deptno|| ' on ' || todays_date;
base_sal INTEGER := 35525;
base_comm_rate NUMBER := 1.33333;
base_annual NUMBER := ROUND(base_sal * base_comm_rate, 2);
BEGIN
DBMS_OUTPUT.PUT_LINE(rpt_title);
DBMS_OUTPUT.PUT_LINE('Base Annual Salary: ' || base_annual);
END;
/
\set PLSQL_MODE off

postgres=# select dept_salary_rpt(20);
NOTICE:  Report For Department # 20 on 2020-09-01 10:31:00
NOTICE:  Base Annual Salary: 47366.55
 DEPT_SALARY_RPT
-----------------

(1 row)

CREATE TABLE emp (
	empno NUMBER(4),
	ename VARCHAR2(10),
	job VARCHAR2(9),
	mgr NUMBER(4),
	hiredate DATE,
	sal NUMBER(7,2),
	comm NUMBER(7,2),
	deptno NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,20);

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_sal_query (p_empno IN NUMBER)
IS
	v_ename VARCHAR2(10);
	v_job VARCHAR2(9);
	v_hiredate DATE;
	v_sal NUMBER(7,2);
	v_deptno NUMBER(2);
	v_avgsal NUMBER(7,2);
BEGIN
	SELECT ename, job, hiredate, sal, deptno
		INTO v_ename, v_job, v_hiredate, v_sal, v_deptno
		FROM emp WHERE empno = p_empno;
	DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
	DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename);
	DBMS_OUTPUT.PUT_LINE('Job : ' || v_job);
	DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate);
	DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal);
	DBMS_OUTPUT.PUT_LINE('Dept # : ' || v_deptno);

	SELECT AVG(sal) INTO v_avgsal
		FROM emp WHERE deptno = v_deptno;
	IF v_sal > v_avgsal THEN
		DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the '|| 'department average of ' || v_avgsal);
	ELSE
		DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the '|| 'department average of ' || v_avgsal);
END IF;
END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_sal_query (p_empno IN NUMBER)
IS 
	v_ename emp.ename%TYPE;
	v_job emp.job%TYPE;
	v_hiredate emp.hiredate%TYPE;
	v_sal emp.sal%TYPE;
	v_deptno emp.deptno%TYPE;
	v_avgsal v_sal%TYPE;
BEGIN
	SELECT ename, job, hiredate, sal, deptno
		INTO v_ename, v_job, v_hiredate, v_sal, v_deptno
		FROM emp WHERE empno = p_empno;
	DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
	DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename);
	DBMS_OUTPUT.PUT_LINE('Job : ' || v_job);
	DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate);
	DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal);
	DBMS_OUTPUT.PUT_LINE('Dept # : ' || v_deptno);

	SELECT AVG(sal) INTO v_avgsal
		FROM emp WHERE deptno = v_deptno;
	IF v_sal > v_avgsal THEN
		DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the '|| 'department average of ' || v_avgsal);
	ELSE
		DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the '|| 'department average of ' || v_avgsal);
END IF;
END ;

/
\set PLSQL_MODE off

postgres=# select emp_sal_query(7369);
NOTICE:  Employee # : 7369
NOTICE:  Name : SMITH
NOTICE:  Job : CLERK
NOTICE:  Hire Date : 1980-12-17 00:00:00
NOTICE:  Salary : 800
NOTICE:  Dept # : 20
NOTICE:  Employee's salary does not exceed the department average of 800
 EMP_SAL_QUERY
---------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_sal_query (p_empno IN NUMBER)
IS
	r_emp emp%ROWTYPE;
	v_avgsal emp.sal%TYPE;
BEGIN
	SELECT ename, job, hiredate, sal, deptno
		INTO r_emp.ename, r_emp.job, r_emp.hiredate, r_emp.sal, r_emp.deptno
		FROM emp WHERE empno = p_empno;
	DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
	DBMS_OUTPUT.PUT_LINE('Name : ' || r_emp.ename);
	DBMS_OUTPUT.PUT_LINE('Job : ' || r_emp.job);
	DBMS_OUTPUT.PUT_LINE('Hire Date : ' || r_emp.hiredate);
	DBMS_OUTPUT.PUT_LINE('Salary : ' || r_emp.sal);
	DBMS_OUTPUT.PUT_LINE('Dept # : ' || r_emp.deptno);

	SELECT AVG(sal) INTO v_avgsal
		FROM emp WHERE deptno = r_emp.deptno;
	IF r_emp.sal > v_avgsal THEN
		DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the '|| 'department average of ' || v_avgsal);
	ELSE
		DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the '|| 'department average of ' || v_avgsal);
END IF;
END;
/
\set PLSQL_MODE off

BEGIN  
  NULL;
END;

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE divide_it (
	p_numerator IN NUMBER,
	p_denominator IN NUMBER,
	p_result OUT NUMBER
)
IS
BEGIN
	IF p_denominator = 0 THEN
		NULL;
	ELSE
		p_result := p_numerator / p_denominator;
END IF;
END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE dept_salary_rpt (p_deptno NUMBER)
IS
	todays_date DATE;
	rpt_title VARCHAR2(60);
	base_sal INTEGER;
	base_comm_rate NUMBER;
	base_annual NUMBER;
BEGIN
	todays_date := SYSDATE;
	rpt_title := 'Report For Department # ' || p_deptno || ' on '|| todays_date;
	base_sal := 35525;
	base_comm_rate := 1.33333;
	base_annual := ROUND(base_sal * base_comm_rate, 2);

	DBMS_OUTPUT.PUT_LINE(rpt_title);
	DBMS_OUTPUT.PUT_LINE('Base Annual Salary: ' || base_annual);
END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_sal_query (p_empno IN NUMBER)
IS
	r_emp emp%ROWTYPE;
	v_avgsal emp.sal%TYPE;
BEGIN
	SELECT ename, job, hiredate, sal, deptno
		INTO r_emp.ename, r_emp.job, r_emp.hiredate, r_emp.sal, r_emp.deptno
		FROM emp WHERE empno = p_empno;
	DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
	DBMS_OUTPUT.PUT_LINE('Name : ' || r_emp.ename);
	DBMS_OUTPUT.PUT_LINE('Job : ' || r_emp.job);
	DBMS_OUTPUT.PUT_LINE('Hire Date : ' || r_emp.hiredate);
	DBMS_OUTPUT.PUT_LINE('Salary : ' || r_emp.sal);
	DBMS_OUTPUT.PUT_LINE('Dept # : ' || r_emp.deptno);

	SELECT AVG(sal) INTO v_avgsal
		FROM emp WHERE deptno = r_emp.deptno;
	IF r_emp.sal > v_avgsal THEN
		DBMS_OUTPUT.PUT_LINE('Employee''s salary is more than the '|| 'department average of ' || v_avgsal);
	ELSE
		DBMS_OUTPUT.PUT_LINE('Employee''s salary does not exceed the '|| 'department average of ' || v_avgsal);
	END IF;

END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_insert (
	p_empno IN NUMBER(4),
	p_ename IN VARCHAR2(10),
	p_job IN VARCHAR2(9),
	p_mgr IN NUMBER(4),
	p_hiredate IN DATE,
	p_sal IN NUMBER(7,2),
	p_comm IN NUMBER(7,2),
	p_deptno IN NUMBER(2)
)
IS
BEGIN
	INSERT INTO emp VALUES (
		p_empno,
		p_ename,
		p_job,
		p_mgr,
		p_hiredate,
		p_sal,
		p_comm,
		p_deptno);

	DBMS_OUTPUT.PUT_LINE('Added employee...');
	DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
	DBMS_OUTPUT.PUT_LINE('Name : ' || p_ename);
	DBMS_OUTPUT.PUT_LINE('Job : ' || p_job);
	DBMS_OUTPUT.PUT_LINE('Manager : ' || p_mgr);
	DBMS_OUTPUT.PUT_LINE('Hire Date : ' || p_hiredate);
	DBMS_OUTPUT.PUT_LINE('Salary : ' || p_sal);
	DBMS_OUTPUT.PUT_LINE('Commission : ' || p_comm);
	DBMS_OUTPUT.PUT_LINE('Dept # : ' || p_deptno);
	DBMS_OUTPUT.PUT_LINE('----------------------');

END;
/
\set PLSQL_MODE off

postgres=# select emp_insert(9503,'PETERSON','ANALYST',7902,'31-MAR-05',5000,NULL,40);
NOTICE:  Added employee...
NOTICE:  Employee # : 9503
NOTICE:  Name : PETERSON
NOTICE:  Job : ANALYST
NOTICE:  Manager : 7902
NOTICE:  Hire Date : 2005-03-31 00:00:00
NOTICE:  Salary : 5000
NOTICE:  Commission :
NOTICE:  Dept # : 40
NOTICE:  ----------------------
 EMP_INSERT
------------

(1 row)

postgres=# select * from emp WHERE empno = 9503;
 EMPNO |  ENAME   |   JOB   | MGR  |      HIREDATE       | SAL  | COMM | DEPTNO
-------+----------+---------+------+---------------------+------+------+--------
  9503 | PETERSON | ANALYST | 7902 | 2005-03-31 00:00:00 | 5000 |      |     40
(1 row)

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (7389,'SMITH','CLERK',7902,'17-DEC-80',800,20);

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_comp_update (
	p_empno IN NUMBER,
	p_sal IN NUMBER(7,2),
	p_comm IN NUMBER(7,2)
)
IS
BEGIN
	UPDATE emp SET sal = p_sal, comm = p_comm WHERE empno = p_empno;

	IF SQL%FOUND THEN
		DBMS_OUTPUT.PUT_LINE('Updated Employee # : ' || p_empno);
		DBMS_OUTPUT.PUT_LINE('New Salary : ' || p_sal);
		DBMS_OUTPUT.PUT_LINE('New Commission : ' || p_comm);
	ELSE
		DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
	END IF;
END;
/
\set PLSQL_MODE off

postgres=# select emp_comp_update(7369, 6540, 1200);
NOTICE:  Updated Employee # : 7369
NOTICE:  New Salary : 6540
NOTICE:  New Commission : 1200
 EMP_COMP_UPDATE
-----------------

(1 row)

postgres=# select * from emp where empno=7369;
 EMPNO | ENAME |  JOB  | MGR  |      HIREDATE       | SAL  | COMM | DEPTNO
-------+-------+-------+------+---------------------+------+------+--------
  7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 6540 | 1200 |     20
(1 row)

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (7389,'SMITH','CLERK',7902,'17-DEC-80',800,20);

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_delete (p_empno IN NUMBER)
IS
BEGIN
	DELETE FROM emp WHERE empno = p_empno;
	IF SQL%FOUND THEN
		DBMS_OUTPUT.PUT_LINE('Deleted Employee # : ' || p_empno);
	ELSE
		DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
END IF;
END;
/
\set PLSQL_MODE off

postgres=# select emp_delete(7369);
NOTICE:  Deleted Employee # : 7369
 EMP_DELETE
------------

(1 row)
postgres=# select * from emp where empno=7369;
 EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (7389,'SMITH','CLERK',7902,'17-DEC-80',800,20);

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_comp_update (
	p_empno IN NUMBER,
	p_sal IN  NUMBER(7,2),
	p_comm IN NUMBER(7,2)
)
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	v_job emp.job%TYPE;
	v_sal emp.sal%TYPE;
	v_comm emp.comm%TYPE;
	v_deptno emp.deptno%TYPE;
BEGIN
	UPDATE emp SET sal = p_sal, comm = p_comm WHERE empno = p_empno
		RETURNING
			empno,
			ename,
			job,
			sal,
			comm,
			deptno
		INTO
			v_empno,
			v_ename,
			v_job,
			v_sal,
			v_comm,
			v_deptno;

	IF SQL%FOUND THEN
		DBMS_OUTPUT.PUT_LINE('Updated Employee # : ' || v_empno);
		DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename);
		DBMS_OUTPUT.PUT_LINE('Job : ' || v_job);
		DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno);
		DBMS_OUTPUT.PUT_LINE('New Salary : ' || v_sal);
		DBMS_OUTPUT.PUT_LINE('New Commission : ' || v_comm);
	ELSE
		DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
	END IF;
END;
/
\set PLSQL_MODE off

postgres=# select  emp_comp_update(7369, 6540, 1200);
NOTICE:  Updated Employee # : 7369
NOTICE:  Name : SMITH
NOTICE:  Job : CLERK
NOTICE:  Department : 20
NOTICE:  New Salary : 6540
NOTICE:  New Commission : 1200
 EMP_COMP_UPDATE
-----------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_delete (p_empno IN NUMBER)
IS
	r_emp emp%ROWTYPE;
BEGIN
	DELETE FROM emp WHERE empno = p_empno
		RETURNING
			empno,
			ename,
			job,
			sal,
			comm,
			deptno
		INTO
			r_emp.empno,
			r_emp.ename,
			r_emp.job,
			r_emp.sal,
			r_emp.comm,
			r_emp.deptno;

	IF SQL%FOUND THEN
		DBMS_OUTPUT.PUT_LINE('Deleted Employee # : ' || r_emp.empno);
		DBMS_OUTPUT.PUT_LINE('Name : ' || r_emp.ename);
		DBMS_OUTPUT.PUT_LINE('Job : ' || r_emp.job);
		DBMS_OUTPUT.PUT_LINE('Salary : ' || r_emp.sal);
		DBMS_OUTPUT.PUT_LINE('Commission : ' || r_emp.comm);
		DBMS_OUTPUT.PUT_LINE('Department : ' || r_emp.deptno);
	ELSE
		DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
	END IF;
END;
/
\set PLSQL_MODE off

postgres=# select  emp_delete(7369);
NOTICE:  Deleted Employee # : 7369
NOTICE:  Name : SMITH
NOTICE:  Job : CLERK
NOTICE:  Salary : 6540
NOTICE:  Commission : 1200
NOTICE:  Department : 20
 EMP_DELETE
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_insert() 
IS
BEGIN
	INSERT INTO emp (empno,ename,job,sal,deptno) VALUES (9001, 'JONES', 'CLERK', 850.00, 40);
	IF SQL%FOUND THEN
		DBMS_OUTPUT.PUT_LINE('Row has been inserted');
	END IF;
END;
/
\set PLSQL_MODE off

postgres=# select emp_insert();
NOTICE:  Row has been inserted
 EMP_INSERT
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_update() 
IS
BEGIN
	UPDATE emp SET hiredate = '03-JUN-07' WHERE empno = 9001;
	DBMS_OUTPUT.PUT_LINE('# rows updated: ' || SQL%ROWCOUNT);
END;

/
\set PLSQL_MODE off

postgres=# select emp_update();
NOTICE:  # rows updated: 1
 EMP_UPDATE
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_update() 
IS
BEGIN
	UPDATE emp SET hiredate = '03-JUN-07' WHERE empno = 9000;
	IF SQL%NOTFOUND THEN
		DBMS_OUTPUT.PUT_LINE('No rows were updated');
	END IF;
END;
/
\set PLSQL_MODE off

postgres=# select emp_update();
NOTICE:  No rows were updated
 EMP_UPDATE
------------

(1 row)

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (1001,'SMITH','CLERK',7902,'17-DEC-80',800,855.90,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (1002,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (1003,'SMITH','CLERK',7902,'17-DEC-80',800,20);
INSERT INTO emp VALUES (1004,'JACK','CLERK',7922,'18-DEC-80',800,null,20);
INSERT INTO emp VALUES (1005,'JANE','CLERK',7912,'19-DEC-80',800,null,20);
INSERT INTO emp VALUES (1006,'JANE','CLERK',7912,'19-DEC-80',800,null,20);
INSERT INTO emp VALUES (1007,'JANE','CLERK',7912,'19-DEC-80',800,452,20);
INSERT INTO emp VALUES (1008,'JANE','CLERK',7912,'19-DEC-80',800,2500,20);
INSERT INTO emp VALUES (1009,'JANE','CLERK',7912,'19-DEC-80',800,1500,20);

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_1()
IS
	v_empno emp.empno%TYPE;
	v_comm emp.comm%TYPE;
	CURSOR emp_cursor IS SELECT empno, comm FROM emp order by 1;
BEGIN
	OPEN emp_cursor;
	DBMS_OUTPUT.PUT_LINE('EMPNO COMM');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_cursor INTO v_empno, v_comm;
		EXIT WHEN emp_cursor%NOTFOUND;
		--
		--  Test whether or not the employee gets a commission
		--
		IF v_comm IS NOT NULL AND v_comm > 0 THEN
			DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_comm,'$99999.99'));
		END IF;
	END LOOP;
	CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

postgres=# select emp_test_1();
NOTICE:  EMPNO COMM
NOTICE:  ----- -------
NOTICE:  1001 $   855.90
NOTICE:  1002 $   300.00
NOTICE:  1007 $   452.00
NOTICE:  1008 $  2500.00
NOTICE:  1009 $  1500.00
 EMP_TEST_1
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_2()
IS
	v_empno emp.empno%TYPE;
	v_comm emp.comm%TYPE;
	CURSOR emp_cursor IS SELECT empno, comm FROM emp order by 1;
BEGIN
	OPEN emp_cursor;
	DBMS_OUTPUT.PUT_LINE('EMPNO COMM');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_cursor INTO v_empno, v_comm;
		EXIT WHEN emp_cursor%NOTFOUND;
		--
		--  Test whether or not the employee gets a commission
		--
		IF v_comm IS NOT NULL AND v_comm > 0 THEN
			DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_comm,'$99999.99'));
		ELSE
			DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || 'Non-commission');
		END IF;
	END LOOP;
	CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

postgres=# select emp_test_2();
NOTICE:  EMPNO COMM
NOTICE:  ----- -------
NOTICE:  1001 $   855.90
NOTICE:  1002 $   300.00
NOTICE:  1003 Non-commission
NOTICE:  1004 Non-commission
NOTICE:  1005 Non-commission
NOTICE:  1006 Non-commission
NOTICE:  1007 $   452.00
NOTICE:  1008 $  2500.00
NOTICE:  1009 $  1500.00
 EMP_TEST_2
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_3()
IS
	v_empno emp.empno%TYPE;
	v_sal emp.sal%TYPE;
	v_comm emp.comm%TYPE;
	v_avg NUMBER(7,2);
	CURSOR emp_cursor IS SELECT empno, sal, comm FROM emp order by 1;
BEGIN
--
--  Calculate the average yearly compensation in the company
--
	SELECT AVG((sal + NVL(comm,0)) * 24) INTO v_avg FROM emp;
	DBMS_OUTPUT.PUT_LINE('Average Yearly Compensation: ' ||TO_CHAR(v_avg,'$999,999.99'));
	OPEN emp_cursor;
	DBMS_OUTPUT.PUT_LINE('EMPNO YEARLY COMP');
	DBMS_OUTPUT.PUT_LINE('----- -----------');
	LOOP
		FETCH emp_cursor INTO v_empno, v_sal, v_comm;
		EXIT WHEN emp_cursor%NOTFOUND;
		--
		--  Test whether or not the employee gets a commission
		--
		IF v_comm IS NOT NULL AND v_comm > 0 THEN
			--
			--  Test if the employee's compensation with commission exceeds the average
			--
			IF (v_sal + v_comm) * 24 > v_avg THEN
				DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR((v_sal + v_comm) * 24,'$999,999.99') ||' Exceeds Average');
			ELSE
				DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR((v_sal + v_comm) * 24,'$999,999.99') ||' Below Average');
			END IF;
		ELSE
			--
			--  Test if the employee's compensation without commission exceeds the average
			--
			IF v_sal * 24 > v_avg THEN
				DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_sal * 24,'$999,999.99') || ' Exceeds Average');
			ELSE
				DBMS_OUTPUT.PUT_LINE(v_empno || ' ' ||TO_CHAR(v_sal * 24,'$999,999.99') || ' Below Average');
			END IF;
		END IF;
	END LOOP;
	CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

postgres=# select emp_test_3();
NOTICE:  Average Yearly Compensation: $  36,287.73
NOTICE:  EMPNO YEARLY COMP
NOTICE:  ----- -----------
NOTICE:  1001 $  39,741.60 Exceeds Average
NOTICE:  1002 $  45,600.00 Exceeds Average
NOTICE:  1003 $  19,200.00 Below Average
NOTICE:  1004 $  19,200.00 Below Average
NOTICE:  1005 $  19,200.00 Below Average
NOTICE:  1006 $  19,200.00 Below Average
NOTICE:  1007 $  30,048.00 Below Average
NOTICE:  1008 $  79,200.00 Exceeds Average
NOTICE:  1009 $  55,200.00 Exceeds Average
 EMP_TEST_3
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_test_4()
IS
	v_empno emp.empno%TYPE;
	v_comp NUMBER(8,2);
	v_lt_25K SMALLINT := 0;
	v_25K_50K SMALLINT := 0;
	v_50K_75K SMALLINT := 0;
	v_75K_100K SMALLINT := 0;
	v_ge_100K SMALLINT := 0;
	CURSOR emp_cursor IS SELECT empno, (sal + NVL(comm,0)) * 24 FROM emp order by 1;
BEGIN
	OPEN emp_cursor;
	LOOP
		FETCH emp_cursor INTO v_empno, v_comp;
		EXIT WHEN emp_cursor%NOTFOUND;
		IF v_comp < 25000 THEN
			v_lt_25K := v_lt_25K + 1;
		ELSIF v_comp < 50000 THEN
			v_25K_50K := v_25K_50K + 1;
		ELSIF v_comp < 75000 THEN
			v_50K_75K := v_50K_75K + 1;
		ELSIF v_comp < 100000 THEN
			v_75K_100K := v_75K_100K + 1;
		ELSE
			v_ge_100K := v_ge_100K + 1;
		END IF;
	END LOOP;
	CLOSE emp_cursor;
	DBMS_OUTPUT.PUT_LINE('Number of employees by yearly compensation');
	DBMS_OUTPUT.PUT_LINE('Less than 25,000 : ' || v_lt_25K);
	DBMS_OUTPUT.PUT_LINE('25,000 - 49,9999 : ' || v_25K_50K);
	DBMS_OUTPUT.PUT_LINE('50,000 - 74,9999 : ' || v_50K_75K);
	DBMS_OUTPUT.PUT_LINE('75,000 - 99,9999 : ' || v_75K_100K);
	DBMS_OUTPUT.PUT_LINE('100,000 and over : ' || v_ge_100K);
END;

/
\set PLSQL_MODE off

postgres=# select emp_test_4();
NOTICE:  Number of employees by yearly compensation
NOTICE:  Less than 25,000 : 4
NOTICE:  25,000 - 49,9999 : 3
NOTICE:  50,000 - 74,9999 : 1
NOTICE:  75,000 - 99,9999 : 1
NOTICE:  100,000 and over : 0
 EMP_TEST_4
------------

(1 row)

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (1001,'SMITH','CLERK',7902,'17-DEC-80',800,855.90,10);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (1002,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (1003,'SMITH','CLERK',7902,'17-DEC-80',800,20);
INSERT INTO emp VALUES (1004,'JACK','CLERK',7922,'18-DEC-80',800,null,20);
INSERT INTO emp VALUES (1005,'JANE','CLERK',7912,'19-DEC-80',800,null,40);
INSERT INTO emp VALUES (1006,'MILLER','CLERK',7912,'19-DEC-80',800,null,20);
INSERT INTO emp VALUES (1007,'ADAMS','CLERK',7912,'19-DEC-80',800,452,30);
INSERT INTO emp VALUES (1008,'JONES','CLERK',7912,'19-DEC-80',800,2500,10);
INSERT INTO emp VALUES (1009,'FORD','CLERK',7912,'19-DEC-80',800,1500,30);

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_1()
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	v_deptno emp.deptno%TYPE;
	v_dname VARCHAR2(20);
	CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp order by 1;
BEGIN
	OPEN emp_cursor;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME');
	DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------');
	LOOP
		FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
		EXIT WHEN emp_cursor%NOTFOUND;
		v_dname :=
			CASE v_deptno
				WHEN 10 THEN 'Accounting'
				WHEN 20 THEN 'Research'
				WHEN 30 THEN 'Sales'
				WHEN 40 THEN 'Operations'
				ELSE 'unknown'
			END;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||' ' || v_deptno || ' ' || v_dname);
	END LOOP;
	CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

postgres=# select emp_case_1();
NOTICE:  EMPNO ENAME DEPTNO DNAME
NOTICE:  ----- ------- ------ ----------
NOTICE:  1001 SMITH      10 Accounting
NOTICE:  1002 ALLEN      30 Sales
NOTICE:  1003 SMITH      20 Research
NOTICE:  1004 JACK       20 Research
NOTICE:  1005 JANE       40 Operations
NOTICE:  1006 MILLER     20 Research
NOTICE:  1007 ADAMS      30 Sales
NOTICE:  1008 JONES      10 Accounting
NOTICE:  1009 FORD       30 Sales
 EMP_CASE_1
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_2()
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	v_deptno emp.deptno%TYPE;
	v_dname VARCHAR2(20);
	CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp order by 1;
BEGIN
	OPEN emp_cursor;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME');
	DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------');
	LOOP
		FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
		EXIT WHEN emp_cursor%NOTFOUND;
		v_dname :=
			CASE
				WHEN v_deptno = 10 THEN 'Accounting'
				WHEN v_deptno = 20 THEN 'Research'
				WHEN v_deptno = 30 THEN 'Sales'
				WHEN v_deptno = 40 THEN 'Operations'
				ELSE 'unknown'
			END;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||' ' || v_deptno || ' ' || v_dname);
	END LOOP;
	CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

postgres=# select emp_case_2();
NOTICE:  EMPNO ENAME DEPTNO DNAME
NOTICE:  ----- ------- ------ ----------
NOTICE:  1001 SMITH      10 Accounting
NOTICE:  1002 ALLEN      30 Sales
NOTICE:  1003 SMITH      20 Research
NOTICE:  1004 JACK       20 Research
NOTICE:  1005 JANE       40 Operations
NOTICE:  1006 MILLER     20 Research
NOTICE:  1007 ADAMS      30 Sales
NOTICE:  1008 JONES      10 Accounting
NOTICE:  1009 FORD       30 Sales
 EMP_CASE_2
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_3()
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	v_deptno emp.deptno%TYPE;
	v_dname VARCHAR2(20);
	v_loc VARCHAR2(20);
	CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp order by 1;
BEGIN
	OPEN emp_cursor;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME '|| ' LOC');
	DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------'|| ' ---------');
	LOOP
		FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
		EXIT WHEN emp_cursor%NOTFOUND;
		CASE v_deptno
			WHEN 10 THEN v_dname := 'Accounting';
			v_loc := 'New York';
			WHEN 20 THEN v_dname := 'Research';
			v_loc := 'Dallas';
			WHEN 30 THEN v_dname := 'Sales';
			v_loc := 'Chicago';
			WHEN 40 THEN v_dname := 'Operations';
			v_loc := 'Boston';
			ELSE v_dname := 'unknown';
			v_loc := '';
		END CASE;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||' ' || v_deptno || ' ' || RPAD(v_dname, 14) || ' ' ||v_loc);
	END LOOP;
	CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

postgres=# select emp_case_3();
NOTICE:  EMPNO ENAME DEPTNO DNAME  LOC
NOTICE:  ----- ------- ------ ---------- ---------
NOTICE:  1001 SMITH      10 Accounting     New York
NOTICE:  1002 ALLEN      30 Sales          Chicago
NOTICE:  1003 SMITH      20 Research       Dallas
NOTICE:  1004 JACK       20 Research       Dallas
NOTICE:  1005 JANE       40 Operations     Boston
NOTICE:  1006 MILLER     20 Research       Dallas
NOTICE:  1007 ADAMS      30 Sales          Chicago
NOTICE:  1008 JONES      10 Accounting     New York
NOTICE:  1009 FORD       30 Sales          Chicago
 EMP_CASE_3
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_case_4()
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	v_deptno emp.deptno%TYPE;
	v_dname VARCHAR2(20);
	v_loc VARCHAR2(20);
	CURSOR emp_cursor IS SELECT empno, ename, deptno FROM emp ORDER BY 1;
BEGIN
	OPEN emp_cursor;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME DEPTNO DNAME '|| ' LOC');
	DBMS_OUTPUT.PUT_LINE('----- ------- ------ ----------'|| ' ---------');
	LOOP
		FETCH emp_cursor INTO v_empno, v_ename, v_deptno;
		EXIT WHEN emp_cursor%NOTFOUND;
			CASE
				WHEN v_deptno = 10 THEN v_dname := 'Accounting';
					v_loc := 'New York';
				WHEN v_deptno = 20 THEN v_dname := 'Research';
					v_loc := 'Dallas';
				WHEN v_deptno = 30 THEN v_dname := 'Sales';
					v_loc := 'Chicago';
				WHEN v_deptno = 40 THEN v_dname := 'Operations';
					v_loc := 'Boston';
				ELSE v_dname := 'unknown';
					v_loc := '';
			END CASE;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || RPAD(v_ename, 10) ||' ' || v_deptno || ' ' || RPAD(v_dname, 14) || ' ' ||v_loc);
	END LOOP;
	CLOSE emp_cursor;
END;
/
\set PLSQL_MODE off

postgres=# select emp_case_4();
NOTICE:  EMPNO ENAME DEPTNO DNAME  LOC
NOTICE:  ----- ------- ------ ---------- ---------
NOTICE:  1001 SMITH      10 Accounting     New York
NOTICE:  1002 ALLEN      30 Sales          Chicago
NOTICE:  1003 SMITH      20 Research       Dallas
NOTICE:  1004 JACK       20 Research       Dallas
NOTICE:  1005 JANE       40 Operations     Boston
NOTICE:  1006 MILLER     20 Research       Dallas
NOTICE:  1007 ADAMS      30 Sales          Chicago
NOTICE:  1008 JONES      10 Accounting     New York
NOTICE:  1009 FORD       30 Sales          Chicago
 EMP_CASE_4
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE LOOP_PRO_1 ()
IS
	v_counter NUMBER(2);
BEGIN
	v_counter := 1;
	LOOP
		EXIT WHEN v_counter > 10;
		DBMS_OUTPUT.PUT_LINE('Iteration # ' || v_counter);
		v_counter := v_counter + 1;
	END LOOP;
END;
/
\set PLSQL_MODE off

postgres=# select LOOP_PRO_1();
NOTICE:  Iteration # 1
NOTICE:  Iteration # 2
NOTICE:  Iteration # 3
NOTICE:  Iteration # 4
NOTICE:  Iteration # 5
NOTICE:  Iteration # 6
NOTICE:  Iteration # 7
NOTICE:  Iteration # 8
NOTICE:  Iteration # 9
NOTICE:  Iteration # 10
 LOOP_PRO_1
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE LOOP_PRO_2 ()
IS
	v_counter NUMBER(2);
BEGIN
	v_counter := 0;
	LOOP
		v_counter := v_counter + 1;
		EXIT WHEN v_counter > 10;
		CONTINUE WHEN MOD(v_counter,2) = 1;
		DBMS_OUTPUT.PUT_LINE('Iteration # ' || v_counter);
	END LOOP;
END;
/
\set PLSQL_MODE off

postgres=# select LOOP_PRO_2();
NOTICE:  Iteration # 2
NOTICE:  Iteration # 4
NOTICE:  Iteration # 6
NOTICE:  Iteration # 8
NOTICE:  Iteration # 10
 LOOP_PRO_2
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE LOOP_PRO_3 ()
IS
	v_counter NUMBER(2);
BEGIN
	v_counter := 1;
	WHILE v_counter <= 10 LOOP
		DBMS_OUTPUT.PUT_LINE('Iteration # ' || v_counter);
		v_counter := v_counter + 1;
	END LOOP;
END;
/
\set PLSQL_MODE off

postgres=# select LOOP_PRO_3();
NOTICE:  Iteration # 1
NOTICE:  Iteration # 2
NOTICE:  Iteration # 3
NOTICE:  Iteration # 4
NOTICE:  Iteration # 5
NOTICE:  Iteration # 6
NOTICE:  Iteration # 7
NOTICE:  Iteration # 8
NOTICE:  Iteration # 9
NOTICE:  Iteration # 10
 LOOP_PRO_3
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE LOOP_PRO_4 ()
IS
BEGIN
	FOR i IN 1 .. 10 LOOP
		DBMS_OUTPUT.PUT_LINE('Iteration # ' || i);
	END LOOP;
END;
/
\set PLSQL_MODE off

postgres=# select LOOP_PRO_4();
NOTICE:  Iteration # 1
NOTICE:  Iteration # 2
NOTICE:  Iteration # 3
NOTICE:  Iteration # 4
NOTICE:  Iteration # 5
NOTICE:  Iteration # 6
NOTICE:  Iteration # 7
NOTICE:  Iteration # 8
NOTICE:  Iteration # 9
NOTICE:  Iteration # 10
 LOOP_PRO_4
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE LOOP_PRO_5 ()
IS
BEGIN
	FOR i IN 10 .. 1 LOOP
		DBMS_OUTPUT.PUT_LINE('Iteration # ' || i);
	END LOOP;
END;
/
\set PLSQL_MODE off

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (1001,'SMITH','CLERK',7902,'17-DEC-80',800,855.90,20);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES (1002,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno) VALUES (1003,'SMITH','CLERK',7902,'17-DEC-80',800,20);
INSERT INTO emp VALUES (1004,'JACK','CLERK',7922,'18-DEC-80',800,null,20);

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE EXCEP_PRO_1 ()
IS
	v_ename emp.ename%TYPE;
BEGIN
	SELECT ename INTO STRICT v_ename FROM emp WHERE deptno = 20 ORDER BY ename;
		DBMS_OUTPUT.PUT_LINE('employee is ' || v_ename);
	EXCEPTION
		WHEN TOO_MANY_ROWS THEN
		DBMS_OUTPUT.PUT_LINE('More than one employee found');
		DBMS_OUTPUT.PUT_LINE('First employee returned is ' || v_ename);
END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE EXCEP_PRO_2 (p_empno IN NUMBER)
IS
    r_emp emp%ROWTYPE;
BEGIN
    SELECT * INTO STRICT r_emp FROM emp WHERE empno = p_empno;
    DBMS_OUTPUT.PUT_LINE('Employee # : ' || p_empno);
    DBMS_OUTPUT.PUT_LINE('Name : ' || r_emp.ename);
    DBMS_OUTPUT.PUT_LINE('Job : ' || r_emp.job);
    DBMS_OUTPUT.PUT_LINE('Hire Date : ' || r_emp.hiredate);
    DBMS_OUTPUT.PUT_LINE('Salary : ' || r_emp.sal);
    DBMS_OUTPUT.PUT_LINE('Dept # : ' || r_emp.deptno);

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Employee # ' || p_empno || ' not found');
END;
/
\set PLSQL_MODE off

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (1001,'SMITH','CLERK',7902,'17-DEC-80',800,855.90,20);
INSERT INTO emp VALUES (1004,'JOHN','CLERK',null,'17-DEC-80',800,855.90,20);

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE verify_emp (p_empno NUMBER)
IS
	v_ename emp.ename%TYPE;
	v_job emp.job%TYPE;
	v_mgr emp.mgr%TYPE;
	v_hiredate emp.hiredate%TYPE;
BEGIN
	SELECT ename, job, mgr, hiredate INTO v_ename, v_job, v_mgr, v_hiredate FROM emp WHERE empno = p_empno;
	
	IF v_ename IS NULL THEN
		RAISE_APPLICATION_ERROR(-20010, 'No name for ' || p_empno);
	END IF;
	IF v_job IS NULL THEN
		RAISE_APPLICATION_ERROR(-20020, 'No job for' || p_empno);
	END IF;
	IF v_mgr IS NULL THEN
		RAISE_APPLICATION_ERROR(-20030, 'No manager for ' || p_empno);
	END IF;
	IF v_hiredate IS NULL THEN
		RAISE_APPLICATION_ERROR(-20040, 'No hire date for ' || p_empno);
	END IF;
		DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno ||' validated without errors');
		
	EXCEPTION
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE('OTHER EXCEPTION.');
			DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
			DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);

END;
/
\set PLSQL_MODE off

postgres=# select verify_emp(1004);
NOTICE:  OTHER EXCEPTION.
NOTICE:  SQLCODE: 16777248
NOTICE:  SQLERRM: ORA-20030: No manager for 1004
 VERIFY_EMP
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE imme_pro_1 ()
IS
	v_sql VARCHAR2(50);
BEGIN
	EXECUTE IMMEDIATE 'CREATE TABLE imme_job (jobno NUMBER(3), jname VARCHAR2(9))';
	v_sql := 'INSERT INTO imme_job VALUES (100, ''ANALYST'')';
	EXECUTE IMMEDIATE v_sql;
	v_sql := 'INSERT INTO imme_job VALUES (200, ''CLERK'')';
	EXECUTE IMMEDIATE v_sql;
END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE imme_pro_2 ()
IS
	v_sql VARCHAR2(50) := 'INSERT INTO imme_job VALUES ($1, $2)';
	v_jobno imme_job.jobno%TYPE;
	v_jname imme_job.jname%TYPE;
BEGIN
	v_jobno := 300;
	v_jname := 'MANAGER';
	EXECUTE IMMEDIATE v_sql USING v_jobno, v_jname;
	v_jobno := 400;
	v_jname := 'SALESMAN';
	EXECUTE IMMEDIATE v_sql USING v_jobno, v_jname;
	v_jobno := 500;
	v_jname := 'PRESIDENT';
	EXECUTE IMMEDIATE v_sql USING v_jobno, v_jname;
END;

/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE imme_pro_3 ()
IS
	v_sql VARCHAR2(60);
	v_jobno imme_job.jobno%TYPE;
	v_jname imme_job.jname%TYPE;
BEGIN
	DBMS_OUTPUT.PUT_LINE('JOBNO JNAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	v_sql := 'SELECT jobno, jname FROM imme_job WHERE jobno = $1';
	EXECUTE IMMEDIATE v_sql INTO v_jobno, v_jname USING 100;
	DBMS_OUTPUT.PUT_LINE(v_jobno || ' ' || v_jname);
	EXECUTE IMMEDIATE v_sql INTO v_jobno, v_jname USING 200;
	DBMS_OUTPUT.PUT_LINE(v_jobno || ' ' || v_jname);
	EXECUTE IMMEDIATE v_sql INTO v_jobno, v_jname USING 300;
	DBMS_OUTPUT.PUT_LINE(v_jobno || ' ' || v_jname);
	EXECUTE IMMEDIATE v_sql INTO v_jobno, v_jname USING 400;
	DBMS_OUTPUT.PUT_LINE(v_jobno || ' ' || v_jname);
END;
/
\set PLSQL_MODE off

postgres=# select imme_pro_1 ();
 IMME_PRO_1
------------

(1 row)
postgres=# select imme_pro_2 ();
 IMME_PRO_2
------------

(1 row)
postgres=# select imme_pro_3 ();
NOTICE:  JOBNO JNAME
NOTICE:  ----- -------
NOTICE:  100 ANALYST
NOTICE:  200 CLERK
NOTICE:  300 MANAGER
NOTICE:  400 SALESMAN
 IMME_PRO_3
------------

(1 row)

CREATE TABLE emp (
empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp VALUES (7319,'JACK','CLERK',7922,'18-DEC-80',800,null,20);

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example
IS
	CURSOR emp_cur_1 IS SELECT * FROM emp;
	CURSOR emp_cur_2 IS SELECT empno, ename FROM emp;
	CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10 ORDER BY empno;
BEGIN
...
END;

/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example
IS
	CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10 ORDER BY empno;
BEGIN
	OPEN emp_cur_3;
...
END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example
IS
	v_empno	NUMBER(4);
	v_ename	VARCHAR2(10);
	CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10 ORDER BY empno;
BEGIN
	OPEN emp_cur_3;
	FETCH emp_cur_3 INTO v_empno, v_ename;
	...
END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example
IS
	v_empno	emp.empno%TYPE;
	v_ename	emp.ename%TYPE;
	CURSOR emp_cur_3 IS SELECT empno, ename FROM emp WHERE deptno = 10 ORDER BY empno;
BEGIN
	OPEN emp_cur_3;
	FETCH emp_cur_3 INTO v_empno, v_ename;
	...
END;

/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example
IS
	v_emp_rec emp%ROWTYPE;
	CURSOR emp_cur_1 IS SELECT * FROM emp;
BEGIN
	OPEN emp_cur_1;
	FETCH emp_cur_1 INTO v_emp_rec;
	DBMS_OUTPUT.PUT_LINE('Employee Number: ' || v_emp_rec.empno);
	DBMS_OUTPUT.PUT_LINE('Employee Name	: ' || v_emp_rec.ename);
	...
END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_example()
IS
	v_emp_rec emp%ROWTYPE;
	CURSOR emp_cur_1 IS SELECT empno, ename FROM emp order by 1;
BEGIN
	OPEN emp_cur_1;
	FETCH emp_cur_1 INTO v_emp_rec.empno,v_emp_rec.ename;
	DBMS_OUTPUT.PUT_LINE('Employee Number: ' || v_emp_rec.empno);
	DBMS_OUTPUT.PUT_LINE('Employee Name : ' || v_emp_rec.ename);
	CLOSE emp_cur_1;
END;
/
\set PLSQL_MODE off

postgres=# select cursor_example();
NOTICE:  Employee Number: 7319
NOTICE:  Employee Name : JACK
 CURSOR_EXAMPLE
----------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE emp_info()
IS
	CURSOR empcur IS SELECT ename, deptno FROM emp order by 2;
	myvar empcur%ROWTYPE;
BEGIN
	OPEN empcur;
	LOOP
		FETCH empcur INTO myvar.ename,myvar.deptno;
		EXIT WHEN empcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE( myvar.ename || ' works in department '|| myvar.deptno );
	END LOOP;
	CLOSE empcur;
END;
/
\set PLSQL_MODE off

postgres=# select emp_info();
NOTICE:  SMITH works in department 20
NOTICE:  JACK works in department 30
NOTICE:  JANE works in department 40
 EMP_INFO
----------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_1()
IS
	CURSOR empcur IS SELECT ename, deptno FROM emp order by 2;
BEGIN
	OPEN empcur;
	
	IF empcur%ISOPEN THEN
		DBMS_OUTPUT.PUT_LINE( 'empcur is open ');
	ELSE
		DBMS_OUTPUT.PUT_LINE( 'empcur is close ');
	END IF;
	
	CLOSE empcur;

END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_2()
IS
	v_emp_rec emp%ROWTYPE;
	CURSOR emp_cur_1 IS SELECT empno, ename FROM emp order by 1;
BEGIN
	OPEN emp_cur_1;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	FETCH emp_cur_1 INTO v_emp_rec.empno,v_emp_rec.ename;
	WHILE emp_cur_1%FOUND LOOP
		DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || ' ' || v_emp_rec.ename);
		FETCH emp_cur_1 INTO v_emp_rec.empno,v_emp_rec.ename;
	END LOOP;
	CLOSE emp_cur_1;
END;

/
\set PLSQL_MODE off

postgres=# select cursor_test_2();
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7319 JACK
NOTICE:  7369 SMITH
NOTICE:  7389 JANE
 CURSOR_TEST_2
---------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_3()
IS
	v_emp_rec emp%ROWTYPE;
	CURSOR emp_cur_1 IS SELECT empno, ename FROM emp order by 1;
BEGIN
	OPEN emp_cur_1;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_cur_1 INTO v_emp_rec.empno,v_emp_rec.ename;
		EXIT WHEN emp_cur_1%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || ' ' || v_emp_rec.ename);
	END LOOP;
	CLOSE emp_cur_1;
END;
/
\set PLSQL_MODE off

postgres=# select cursor_test_3();
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7319 JACK
NOTICE:  7369 SMITH
NOTICE:  7389 JANE
 CURSOR_TEST_3
---------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_4()
IS
	v_emp_rec emp%ROWTYPE;
	CURSOR emp_cur_1 IS SELECT empno, ename FROM emp order by 1;
BEGIN
	OPEN emp_cur_1;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_cur_1 INTO v_emp_rec.empno,v_emp_rec.ename;
		EXIT WHEN emp_cur_1%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || ' ' || v_emp_rec.ename);
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('**********************');
	DBMS_OUTPUT.PUT_LINE(emp_cur_1%ROWCOUNT || ' rows were retrieved');
	CLOSE emp_cur_1;
END;
/
\set PLSQL_MODE off

postgres=# select cursor_test_4();
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7319 JACK
NOTICE:  7369 SMITH
NOTICE:  7389 JANE
NOTICE:  **********************
NOTICE:  0 rows were retrieved
 CURSOR_TEST_4
---------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_5()
IS
	CURSOR emp_cur_1 IS SELECT empno, ename FROM emp order by 1;
BEGIN
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	FOR v_emp_rec IN emp_cur_1 LOOP
		DBMS_OUTPUT.PUT_LINE(v_emp_rec.empno || ' ' || v_emp_rec.ename);
	END LOOP;
END;
/
\set PLSQL_MODE off

postgres=# select cursor_test_5();
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7319 JACK
NOTICE:  7369 SMITH
NOTICE:  7389 JANE
 CURSOR_TEST_5
---------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE cursor_test_6(v_sal in number)
IS
	my_record emp%ROWTYPE;
	CURSOR c1 (max_wage NUMBER) IS SELECT ename,sal FROM emp WHERE sal < max_wage;
BEGIN
	OPEN c1(v_sal);
	LOOP
		FETCH c1 INTO my_record.ename,my_record.sal;
		EXIT WHEN c1%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE('Name = ' || my_record.ename || ', salary = '|| my_record.sal);
	END LOOP;
	CLOSE c1;
END;
/
\set PLSQL_MODE off

postgres=# select cursor_test_6(2001);
NOTICE:  Name = SMITH, salary = 1000
NOTICE:  Name = JANE, salary = 2000
 CURSOR_TEST_6
---------------

(1 row)

IS
TYPE emp_cur_type IS REF CURSOR ;
my_rec emp_cur_type;
...

CREATE OR REPLACE PROCEDURE emp_by_dept (
p_deptno	emp.deptno%TYPE
)
IS
type emp_cur is ref cursor;
emp_refcur emp_cur;
BEGIN
OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno;
...

CREATE OR REPLACE PROCEDURE emp_by_dept (
p_deptno	emp.deptno%TYPE
)
IS
type emp_cur is ref cursor;
emp_refcur emp_cur;
v_empno	emp.empno%TYPE;
v_ename	emp.ename%TYPE;
BEGIN
OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno;
DBMS_OUTPUT.PUT_LINE('EMPNO	ENAME');
DBMS_OUTPUT.PUT_LINE('-----	-------');
LOOP
FETCH emp_refcur INTO v_empno, v_ename;
EXIT WHEN emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || '	' || v_ename);
END LOOP;
...

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE ref_cursor_test_1 (p_deptno NUMBER)
IS
	type emp_cur is ref cursor;
	emp_refcur emp_cur;
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
BEGIN
	OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno order by 1;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_refcur INTO v_empno, v_ename;
		EXIT WHEN emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
	END LOOP;
	CLOSE emp_refcur;
END;

/
\set PLSQL_MODE off

postgres=# select ref_cursor_test_1(20);
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7319 JACK
NOTICE:  7329 AMY
NOTICE:  7369 SMITH
 REF_CURSOR_TEST_1
-------------------

(1 row)

CREATE TABLE emp (
 empno NUMBER(4),
 ename VARCHAR2(10),
 job VARCHAR2(9),
 mgr NUMBER(4),
 hiredate DATE,
 sal NUMBER(7,2),
 comm NUMBER(7,2),
 deptno NUMBER(2)
);

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',1000,NULL,20);
INSERT INTO emp VALUES (7389,'JANE','CLERK',7912,'17-DEC-80',2000,NULL,40);
INSERT INTO emp VALUES (7319,'JACK','CLERK',7922,'18-DEC-80',5000,null,20);
INSERT INTO emp VALUES (7329,'AMY','MASTER',7722,'18-DEC-80',3000,null,20);

\set PLSQL_MODE on
CREATE OR REPLACE FUNCTION emp_by_job (p_job VARCHAR2)
RETURN REFCURSOR
IS
	type emp_cur is ref cursor;
	emp_refcur emp_cur;
BEGIN
	OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE job = p_job;
	RETURN emp_refcur;
END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE test()
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	v_job emp.job%TYPE := 'CLERK';
	type emp_cur is ref cursor;
	v_emp_refcur emp_cur;
BEGIN
	DBMS_OUTPUT.PUT_LINE('EMPLOYEES WITH JOB ' || v_job);
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	v_emp_refcur := emp_by_job (v_job);
	LOOP
		FETCH v_emp_refcur INTO v_empno, v_ename;
		EXIT WHEN v_emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
	END LOOP;
	CLOSE v_emp_refcur;
END;
/
\set PLSQL_MODE off

postgres=# select test();
NOTICE:  EMPLOYEES WITH JOB CLERK
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7369 SMITH
NOTICE:  7389 JANE
NOTICE:  7319 JACK
B019_TEST
-----------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE FUNCTION open_all_emp (p_emp_refcur REFCURSOR) RETURN REFCURSOR
IS
BEGIN
	OPEN p_emp_refcur FOR SELECT empno, ename FROM emp;
	return p_emp_refcur;
END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE FUNCTION open_emp_by_dept (p_emp_refcur REFCURSOR,p_deptno NUMBER)
IS
BEGIN
	OPEN p_emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno;
	return p_emp_refcur;
END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE FUNCTION open_dept (p_dept_refcur REFCURSOR) RETURN REFCURSOR
IS
	v_dept_refcur REFCURSOR;
BEGIN
	v_dept_refcur := p_dept_refcur;
	OPEN v_dept_refcur FOR SELECT deptno, dname FROM dept;
	RETURN v_dept_refcur;
END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE fetch_emp (p_emp_refcur REFCURSOR)
IS
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
BEGIN
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
		FETCH p_emp_refcur INTO v_empno, v_ename;
		EXIT WHEN p_emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE fetch_dept ( p_dept_refcur REFCURSOR)
IS
	v_deptno dept.deptno%TYPE;
	v_dname dept.dname%TYPE;
BEGIN
	DBMS_OUTPUT.PUT_LINE('DEPT DNAME');
	DBMS_OUTPUT.PUT_LINE('---- ---------');
LOOP
	FETCH p_dept_refcur INTO v_deptno, v_dname;
	EXIT WHEN p_dept_refcur%NOTFOUND;
	DBMS_OUTPUT.PUT_LINE(v_deptno || ' ' || v_dname);
END LOOP;
END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE close_refcur (p_refcur REFCURSOR)
IS
BEGIN
	CLOSE p_refcur;
END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE test()
IS
	gen_refcur REFCURSOR;
BEGIN
	DBMS_OUTPUT.PUT_LINE('ALL EMPLOYEES');
	gen_refcur:=open_all_emp(gen_refcur);
	fetch_emp(gen_refcur);
	DBMS_OUTPUT.PUT_LINE('****************');
	close_refcur(gen_refcur);
	
	DBMS_OUTPUT.PUT_LINE('EMPLOYEES IN DEPT #10');
	gen_refcur:=open_emp_by_dept(gen_refcur, 10);
	fetch_emp(gen_refcur);
	DBMS_OUTPUT.PUT_LINE('****************');
	close_refcur(gen_refcur);
	
	DBMS_OUTPUT.PUT_LINE('DEPARTMENTS');
	fetch_dept(open_dept(gen_refcur));
	DBMS_OUTPUT.PUT_LINE('*****************');

	close_refcur(gen_refcur);

END;
/
\set PLSQL_MODE off

postgres=# select test();
NOTICE:  ALL EMPLOYEES
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7369 SMITH
NOTICE:  7499 ALLEN
NOTICE:  7521 WARD
NOTICE:  7566 JONES
NOTICE:  7654 MARTIN
NOTICE:  7698 BLAKE
NOTICE:  7782 CLARK
NOTICE:  7788 SCOTT
NOTICE:  7839 KING
NOTICE:  7844 TURNER
NOTICE:  7876 ADAMS
NOTICE:  7900 JAMES
NOTICE:  7902 FORD
NOTICE:  7934 MILLER
NOTICE:  ****************
NOTICE:  EMPLOYEES IN DEPT #10
NOTICE:  7782 CLARK
NOTICE:  7839 KING
NOTICE:  7934 MILLER
NOTICE:  ****************
NOTICE:  DEPARTMENTS
NOTICE:  DEPT DNAME
NOTICE:  ---- ---------
NOTICE:  10 ACCOUNTING
NOTICE:  20 RESEARCH
NOTICE:  30 SALES
NOTICE:  40 OPERATIONS
NOTICE:  *****************
 TEST
------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE dept_query()
IS
	emp_refcur REFCURSOR;
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
BEGIN
	OPEN emp_refcur FOR 'SELECT empno, ename FROM emp WHERE deptno = 30' ||' AND sal >= 1500';
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_refcur INTO v_empno, v_ename;
		EXIT WHEN emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
	END LOOP;
	CLOSE emp_refcur;
END;
/
\set PLSQL_MODE off

postgres=# select dept_query();
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7499 ALLEN
NOTICE:  7698 BLAKE
NOTICE:  7844 TURNER
DEPT_QUERY
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE dept_query (p_deptno NUMBER,p_sal NUMBER)
IS
	emp_refcur REFCURSOR;
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
BEGIN
	OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno AND sal >= p_sal;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_refcur INTO v_empno, v_ename;
		EXIT WHEN emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
	END LOOP;
	CLOSE emp_refcur;
END;

/
\set PLSQL_MODE off

postgres=# select dept_query(30,1500);
NOTICE:  EMPNO ENAME
NOTICE:  ----- -------
NOTICE:  7499 ALLEN
NOTICE:  7698 BLAKE
NOTICE:  7844 TURNER
DEPT_QUERY
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE dept_query (p_deptno NUMBER,p_sal NUMBER)
IS
	emp_refcur REFCURSOR;
	v_empno emp.empno%TYPE;
	v_ename emp.ename%TYPE;
	p_query_string VARCHAR2(100);
BEGIN
	p_query_string := 'SELECT empno, ename FROM emp WHERE ' ||'deptno = $1 AND sal >= $2';
	OPEN emp_refcur FOR p_query_string USING p_deptno, p_sal;
	DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
	DBMS_OUTPUT.PUT_LINE('----- -------');
	LOOP
		FETCH emp_refcur INTO v_empno, v_ename;
		EXIT WHEN emp_refcur%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
	END LOOP;
	CLOSE emp_refcur;
END;
/
\set PLSQL_MODE off

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE test()
IS
	TYPE dname_tbl_typ IS TABLE OF VARCHAR2(14);
	dname_tbl dname_tbl_typ;
	CURSOR dept_cur IS SELECT dname FROM b021_dept ORDER BY dname;
	i INTEGER := 1;
BEGIN
	FOR r_dept IN dept_cur LOOP
		dname_tbl(i) := r_dept.dname;
		i := i + 1;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('DNAME');
	DBMS_OUTPUT.PUT_LINE('----------');
	FOR j IN 1..i-1 LOOP
		DBMS_OUTPUT.PUT_LINE(dname_tbl(j));
	END LOOP;
END;
/
\set PLSQL_MODE off

postgres=# select test();
NOTICE:  DNAME
NOTICE:  ----------
NOTICE:  ACCOUNTING
NOTICE:  OPERATIONS
NOTICE:  RESEARCH
NOTICE:  SALES
 TEST
------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE test()
IS
	TYPE dname_tbl_typ IS TABLE OF VARCHAR2(14);
	dname_tbl dname_tbl_typ;
	CURSOR dept_cur IS SELECT dname FROM dept ORDER BY dname;
	i INTEGER := 1;
BEGIN
	FOR r_dept IN dept_cur LOOP
		dname_tbl(i) := r_dept.dname;
		i := i + 1;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('COUNT='||dname_tbl.count);

END;
/
\set PLSQL_MODE off

postgres=# select test();
NOTICE:  COUNT=4
 TEST
------------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE test()
IS
	TYPE dname_tbl_typ IS TABLE OF VARCHAR2(14);
	dname_tbl dname_tbl_typ;
	TYPE deptno_tbl_typ IS TABLE OF NUMBER;
	deptno_tbl deptno_tbl_typ;
	TYPE loc_tbl_typ IS TABLE OF VARCHAR2(14);
	loc_tbl loc_tbl_typ;
BEGIN
	SELECT deptno BULK COLLECT INTO deptno_tbl FROM dept;
	SELECT dname BULK COLLECT INTO dname_tbl FROM dept;
	SELECT loc BULK COLLECT INTO loc_tbl FROM dept;
	
	DBMS_OUTPUT.PUT_LINE('DEPTNO DNAME LOC');
	DBMS_OUTPUT.PUT_LINE('-----  -------  ---------');
	
	FOR i IN 1..deptno_tbl.COUNT LOOP
		DBMS_OUTPUT.PUT_LINE(deptno_tbl(i) || ' ' ||RPAD(dname_tbl(i),8) || ' ' ||RPAD(loc_tbl(i),10));
	END LOOP;

END;
/
\set PLSQL_MODE off

postgres=# select test();
NOTICE:  DEPTNO DNAME LOC
NOTICE:  -----  -------  ---------
NOTICE:  10 ACCOUNTI NEW YORK
NOTICE:  20 RESEARCH DALLAS
NOTICE:  30 SALES    CHICAGO
NOTICE:  40 OPERATIO BOSTON
 TEST
------

(1 row)

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE test()
IS
	TYPE dname_tbl_typ IS TABLE OF VARCHAR2(14);
	dname_tbl dname_tbl_typ;
BEGIN
	delete from dept where deptno > 20 RETURNING dname BULK COLLECT INTO dname_tbl;
	DBMS_OUTPUT.PUT_LINE('DNAME');
	DBMS_OUTPUT.PUT_LINE('-----');
	
	FOR i IN 1..dname_tbl.COUNT LOOP
		DBMS_OUTPUT.PUT_LINE(dname_tbl[i]);
	END LOOP;
END;
/
\set PLSQL_MODE off

postgres=# select test();
NOTICE:  DNAME
NOTICE:  -----
NOTICE:  SALES
NOTICE:  OPERATIONS
TEST
--------

(1 row)

set grammar to postgres;
create type dept_type as(
deptno integer,
dname VARCHAR(14),
loc VARCHAR(14)
);

set grammar to oracle;

\set PLSQL_MODE on
CREATE OR REPLACE PROCEDURE test()
IS
	TYPE dept_tbl_typ IS TABLE OF dept_type;
	dept_tbl dept_tbl_typ;
BEGIN
	update dept set loc=loc||'_2' where deptno <= 20 RETURNING deptno,dname,loc BULK COLLECT INTO dept_tbl;

	FOR i IN 1..dept_tbl.COUNT LOOP
		DBMS_OUTPUT.PUT_LINE('DNAME='||dept_tbl[i].dname);
		DBMS_OUTPUT.PUT_LINE('DEPTNO='||dept_tbl[i].deptno);
		DBMS_OUTPUT.PUT_LINE('LOC='||dept_tbl[i].loc);
		DBMS_OUTPUT.PUT_LINE('-----------------');
	END LOOP;
END;
/
\set PLSQL_MODE off

postgres=# select test();
NOTICE:  DNAME=ACCOUNTING
NOTICE:  DEPTNO=10
NOTICE:  LOC=NEW YORK_2
NOTICE:  -----------------
NOTICE:  DNAME=RESEARCH
NOTICE:  DEPTNO=20
NOTICE:  LOC=DALLAS_2
 TEST
--------

(1 row)

DBMS_OUTPUT.PUT_LINE('My name is John');

\set PLSQL_MODE ON
CREATE OR REPLACE PROCEDURE simple_procedure()
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('That''s all folks!');
END;

select DBMS_JOB.BROKEN(1,true);

select DBMS_JOB.BROKEN(1,false);

select DBMS_JOB.CHANGE(2,NULL,TO_DATE('28-OCT-2020','DD-MON-YYYY'),NULL, NULL, NULL);

select DBMS_JOB.INTERVAL(1,'SYSDATE + 7');

select DBMS_JOB.NEXT_DATE(1, TO_DATE('28-OCT-2020','DD-MON-YYYY'));

select DBMS_JOB.REMOVE(1);

select dbms_job.run(1);
NOTICE:  execute job [1]
NOTICE:  set next time [1] [2020-10-27 14:11:14.126901]
 RUN 
-----
 
(1 row)

select dbms_job.run(1);
NOTICE:  execute job [1]
NOTICE:  set next time [1] [2020-10-27 14:11:14.126901]
 RUN 
-----
 
(1 row)

-- 立即执行，并且每隔 1 分钟执行一次
set grammar to postgres;
select dbms_job.submit( 'select p_insert(1)'
                      , clock_timestamp()::timestamp
                      , 'clock_timestamp() + interval ''1 minutes''');
submit 
-----------
      1
(1 row)