Oracle PL/SQL

SQL

Distinct

NULL

||连接符

Oracle数据库中大小写敏感

要求找出含有%的记录

这需要使用ESCAPE标识转义字符

select * from t_charwhere a like ‘%\%%‘ escape ‘\‘;

select * fromt_char where a like ‘%K%%‘ escape ‘K‘;

PLSQL中日期比较 

1.格式需要一致，可以通过字符串比较。

2.TO_CHAR(CREATE_DATE,‘yyyy-mm-dd‘)>=NVL(P_CREATE_FROMDATE,TO_CHAR(CREATE_DATE,‘yyyy-mm-dd‘))

2.  START_DATE <= TO_date(p_trans_date,‘YYYY-MM-DD‘)

--不易发现的错误

select * from test1where column1 = ‘1234‘;--将所有行转换为字符串

select * from test1where column1 = 1234;--将所有行转换为数字，包含字符的行出错

--连接

-oracle全外外连接

select e.last_name,e.department_id,d.department_namefrom employees e,departments dwhere e.department_id=d.department_id(+)union

select e.last_name,e.department_id,d.department_namefrom employees e,departments dwhere e.department_id(+)=d.department_id;

--sql1999标准

SELECT e.last_name, e.department_id, d.department_name

FROM employees e

FULLOUTERJOIN departments d

ON (e.department_id =d.department_id) ;

分组计算函数和group by子句

备注：MIN, MAX 可用于任何数据类型，但AVG， SUM，STDDEV,VARIANCE仅适用于数值型字段。

使用GROUP BY 子句进行分组:

l 可以按照某一个字段分组，也可以按照多个字段的组合进行分组

l SELECT查询语句中同时选择分组计算函数表达式和其他独立字段时，其他字段必须出现在Group By子句中，否则不合法。

l 不能在Where条件中使用分组计算函数表达式，当出现这样的需求的时候，使用Having 子句。

l 分组计算函数也可嵌套使用。

事务控制

隐式的事务提交或回滚动作：

Commit,rollback 是显式的提交和回滚语句，还有一些隐式的提交和回滚是大家需要知道并引起注意的：

当如下事件发生是，会隐式的执行Commit动作：

1、数据定义语句被执行的时候，比如新建一张表：Create Table …

2、数据控制语句被执行的时候，比如赋权GRANT …( 或者DENY)

3、正常退出iSQL*Plus 或者PLSQLDEVELOPER, 而没有显式的执行COMMIT 或者ROLLBACK 语句。

当如下事件发生时，会隐式执行Rollback 动作：

1、非正常退出iSQL*Plus, PLSQL DEVELOPER, 或者发生系统错误。

在Commit 或者Rollback前后数据的状态：

1、在数据已经被更改，但没有Commit前，被更改记录处于被锁定状态，其他用户无法进行更改；

2、在数据已经被更改，但没有Commit前，只有当前Session的用户可以看到这种变更，其他Session的用户

看不到数据的变化。

3、在数据已经被更改，并且被Commit后，被更改记录自动解锁，其他用户可以进行更改；

4、在数据已经被更改，并且被Commit后，其他Session的用户再次访问这些数据时，看到的是变化后的数据。

那么同理可知Rollback前后数据的状态及锁的变化。

锁

表的命名要求和表中列的命名要求：

1、必须以字母开头

2、长度不能超过30个字符

3、只能包含A–Z,a–z, 0–9,_, $, and #

4、不能与数据库中的已有对象重名

5、不能使用Oracle 数据库的保留字

注意TRUNCATE 与DELETE FROM table 的区别： 1）没有Rollback机会2）HWM标记复位（HWM高水位线）都不会删除表结构

约束

更改表的语法：

添加列：

ALTERTABLE table

ADD(column datatype [DEFAULT expr]

[, columndatatype]...);

更改列：

ALTERTABLE table

MODIFY(column datatype [DEFAULT expr]

[, columndatatype]...);

删除列：

ALTERTABLE table

DROP(column);

ALTER TABLE tablenameADD CONSTRAINT constraintname

constrainttype(column1,…);

CONSTRAINT emp_email_ukUNIQUE(email))

CONSTRAINT dept_id_pk PRIMARYKEY(department_id))

CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)

REFERENCESdepartments(department_id) 

CONSTRAINT emp_salary_min CHECK (salary > 0)

索引、序列、同义词

函数索引

CREATE INDEX upper_dept_name_idx

ON departments(UPPER(department_name));

控制用户权限

GRANT object_priv [(columns)]

ON object

TO {user|role|PUBLIC}

[WITHGRANT OPTION];

如果你想让其他用户也有权把你赋给他的权限进一步赋予给别人，那么需要带WITH GRANT OPTION;

GRANT select, insert

ON departments

TO scott

WITH GRANT OPTION;

撤销权限

REVOKE {privilege [, privilege...]|ALL}

ON object

FROM{user[, user...]|role|PUBLIC}

创建DB-LINK，通过DB-LINK访问另一数据库中的表

CREATE PUBLIC DATABASE LINK hq.acme.com

USING‘sales‘;

SELECT*

[email protected];

递归查询

SELECTlast_name||‘ reports to ‘||

PRIORlast_name "Walk Top Down"

FROMemployees

STARTWITH last_name = ‘King‘

CONNECTBY PRIOR employee_id = manager_id ;

使用LEVEL关键字和LPAD函数，在OUTPUT中显示树形层次。

SELECT LPAD(last_name,LENGTH(last_name)+(LEVEL*2)-2,‘_‘)

AS org_chart

FROM employees

START WITH last_name=‘King‘

CONNECT BYPRIOR employee_id=manager_id

Group By 子句的增强

在Group By 中使用Rollup 产生常规分组汇总行以及分组小计

在Group By 中使用Cube 产生Rollup结果集+ 多维度的交叉表数据源

GROUPING函数：Rollup 和Cube有点抽象，他分别相当于n+1 和2的n次方常规Group by 运

算；那么在Rollup 和Cube的结果集中如何很明确的看出哪些行是针对那些列或者列的组合进行

分组运算的结果的？ 答案是可以使用Grouping 函数； 没有被Grouping到返回1，否则返回0

使用Grouping Set 来代替多次UNION

Exercise

--14.  Showall employees who have managers with a salary higher than $15,000.

--Show the following data: employeename, manager name, manager salary, and salary grade of the manager.

createtable job_grades(grade_levelvarchar(20),lowest_salnumber,highest_salnumber);

insertinto job_gradesvalues(‘E‘,10000,1000000);

insertinto job_gradesvalues(‘E‘,9000,900000);

SELECT e.last_name, m.last_name manager,m.salary, j.grade_level

 FROM employees e,employees m, job_grades j

 WHERE e.manager_id = m.employee_id

  AND m.salaryBETWEEN j.lowest_salAND j.highest_sal

   AND m.salary >15000;

--15.  Showthe department number, name, number of employees, and average salary of alldepartments,

--together with the names, salaries,and jobs of the employees working in each department.

SELECT d.department_id,

      d.department_name,

      count(e1.employee_id)employees,

      NVL(TO_CHAR(AVG(e1.salary),‘99999.99‘), ‘No average‘) avg_sal,

      e2.last_name,

      e2.salary,

      e2.job_id

 FROM departments d,employees e1, employees e2

 WHERE d.department_id = e1.department_id(+)

  AND d.department_id =e2.department_id(+)

 GROUPBY d.department_id,

          d.department_name,

          e2.last_name,

          e2.salary,

          e2.job_id

 ORDERBY d.department_id, employees;

--19.  Showthe employee number, last name, salary, department number, and the averagesalary in their department for all employees.

select e.employee_id, e.last_name,e.department_id,avg(d.salary)

 from employees d,employees e

 where d.department_id = e.department_id

 groupby e.employee_id, e.last_name, e.department_id;

--31. Write a query to delete the oldestJOB_HISTORY row of  an employee bylooking up the

--JOB_HISTORY table for theMIN(START_DATE) for the employee.

--Delete the records of only those employees who have changed at leasttwo jobs.

--If your query executes correctly,    you will get the feedback:

DELETEFROM job_history JH

 WHERE employee_id = (SELECT employee_id

                        FROM employees E

                       WHERE JH.employee_id = E.employee_id

                         AND START_DATE =

                             (SELECTMIN(start_date)

                                FROM job_history JH

                               WHERE JH.employee_id =E.employee_id)

                         AND3 > (SELECTCOUNT(*)

                                    FROM job_history JH

                                   WHERE JH.employee_id =E.employee_id

                                   GROUPBY EMPLOYEE_ID

                                  HAVINGCOUNT(*) >=2));

--41. Write a SQL script file to dropall objects (tables, views, indexes, sequences, synonyms, and so on) that youown.

SET HEADINGOFF ECHO OFF FEEDBACKOFF

SET PAGESIZE0

SELECT‘DROP‘ || object_type ||‘ ‘ || object_name ||‘;‘

FROM user_objects

ORDERBY object_type

SET HEADINGON ECHO ON FEEDBACKON

SET PAGESIZE24

PL/SQL

用到的数据库Student1/student    ora1/ora2/ora3         oracle         system/manager

DECLARE

v_variable VARCHAR2(5);

BEGIN

SELECT column_name

INTO v_variable

FROM table_name;

EXCEPTION

WHEN exception_name THEN

...

END;

SQL不能select into PL/SQL可以。

PLSQL的块包括三种：匿名块、存储过程、函数；

PLSQL特有的%TYPE属性来声明与XX类型一致的变量类型：

identifier Table.column_name%TYPE;

记录类型的定义语法：

TYPE type_name IS RECORD

(field_declaration[, field_declaration]…);

identifier type_name;

这是一个简单数组

TYPE ename_table_type IS TABLE OF

employees.last_name%TYPE

INDEX BY BINARY_INTEGER;

ename_table ename_table_type;

隐式游标的几个有用属性：

SQL%ROWCOUNT 受最近的SQL语句影响的行数

SQL%FOUND 最近的SQL语句是否影响了一行以上的数据

SQL%NOTFOUND 最近的SQL语句是否未影响任何数据

SQL%ISOPEN 对于隐式游标而言永远为FALSE

显式游标的相关函数可以做到：

1、一行一行的处理返回的数据。

2、保持当前处理行的一个跟踪，像一个指针一样指示当前的处理的记录。

3、允许程序员在PLSQL块中人为的控制游标的开启、关闭、上下移动；

游标推荐写法

DECLARE

CURSOR emp_cursor IS

SELECT last_name, department_id

FROM employees;

BEGIN

FOR emp_record IN emp_cursor LOOP

-- implicit open and implicit fetch occur

IF emp_record.department_id = 80 THEN

...

END LOOP; -- implicit close occurs

END;

异常处理

BEGIN

. . .

EXCEPTION

WHEN NO_DATA_FOUND THEN

statement1;

WHEN TOO_MANY_ROWS THEN

statement1;

WHEN OTHERS THEN

statement1;

statement2;

END;

OTHERS

DECLARE

v_error_code NUMBER;

v_error_message VARCHAR2(255);

BEGIN

...

EXCEPTION

...

WHEN OTHERS THEN

ROLLBACK;

v_error_code := SQLCODE ;

v_error_message := SQLERRM ;

INSERT INTO errors

VALUES(v_error_code, v_error_message);

END;

想要在SQL语句中可以使用用户自定义的函数，那么这样的用户定义函数有哪些限制？

答: 有如下限制：

 必须是个函数（不能是过程-Procedure）

 只能用IN 模式的参数（不能有OUT, IN OUT 模式的参数）

 只能接收SQL数据类型的参数，不能接收PLSQL 中特有的参数（比如记录、PLSQL内

存表）

 函数返回的数据类型也必须是有效的数据类型，而不能是PLSQL特有的数据类型

 在SQL中使用的函数，其函数体内部不能有DML语句。

 在UPDATE/DELETE语句中调用的函数，其函数体内部不能有针对同一张表的查询语句

 在SQL中调用的函数，其函数体内部不能有事务结束语句（比如Commit，Rollback)

定义者权限：函数执行时，对表的访问默认使用定义者权限。

那么什么情况会使用调用者权限呢？这需要在写函数的时候有特殊语句标识：AUTHID CURRENT_USER

Package好处：

1、模块化：一般把有相关性的函数和过程放到一个Package中；

2、易设计：可以把包说明和包体分别编写和编译，先编写和编译包说明部分，在编写和说明包体部分；这有利

于分工合作；

3、信息隐藏：包体中函数可以部分出现在包说明中，只有出现在包说明中的函数和过程才是该Package的公有

函数和过程，可以被其他包中的函数调用，否则对其他包中的函数是不可见的，未在包说明部分出现的函数

和过程相当于私有的。

4、加载性能提高：当Package中有一个函数或过程被调用时，整个Packege就被加载到内存中，这样当该

Package中其他函数被调用时，就直接从内存读取了，可以减少磁盘IO,从而提高性能。这个特性也提醒

我们不要去搞巨无霸的Package,把你用到的任何函数都写到一个Package中，这会导致严重的内存浪费。

5、重载：一个package中可以定义同名、不同参数的函数或过程。

存储过程

FOR UPDATE NOWAIT语句：有的时候我们打开一个游标是为了更新或者删除一些记录，这种情况下我们希望

在打开游标的时候即锁定相关记录，应该使用for update nowait语句，倘若锁定失败我们就停止不再继续，以免出现长时间等待资源的死锁情况。

DECLARE

CURSOR sal_cursor IS

SELECT e.department_id, employee_id, last_name, salary

FROM employees e, departments d

WHERE d.department_id = e.department_id

and d.department_id = 60

FOR UPDATE OF salary NOWAIT;

BEGIN

FOR emp_record IN sal_cursor

LOOP

IF emp_record.salary < 5000 THEN

UPDATE employees

SET salary = emp_record.salary * 1.10

WHERE CURRENT OF sal_cursor;

END IF;

END LOOP;

END;

动态SQL：不是在Designer Time写的SQL,而是可以在运行时临时拼接起来的SQL语句；

动态SQL可以使用Oracle内置包DBMS_SQL来执行，也可以使用EXECUTE IMMEDIATE语句来执行：

v_filehandle := UTL_FILE.FOPEN (p_filedir, p_filename,‘w‘);

UTL_FILE.PUTF (v_filehandle,‘SALARY REPORT: GENERATED ON

%s\n‘, SYSDATE);

UTL_FILE.NEW_LINE(v_filehandle);

Oracle数据库里面的LOB有四种类型：

1、CLOB：字符大对象，存储在数据库内部；

2、NCLOB：多字节字符大对象，存储在数据库内部；

3、BLOB：二进制大对象，存储在数据库内部；

4、BFILE：二进制文件，存储在数据库外部；

创建Trigger：Trigger的定义语句里面涉及到如下关键因素：

时机：Before或者After或Instead of

事件：Insert或Update或Delete

对象：表名（或视图名）

类型：Row或者Statement级；

条件：满足特定Where条件才执行；

内容：通常是一段PLSQL块代码；

重点注意：

Instead of : 用Trigger的内容替换事件本身的动作

Row级：SQL语句影响到的每一行都会引发Trigger

Statement级：一句SQL语句引发一次，不管它影响多少行（甚至0行）

PLSQL

1.       Oracle 同义词

相当于alias(别名),比如把user1.table1在user2中建一个同义词table1

create synonym table1 for user1.table1;

这样当你在user2中查select * from table1时就相当于查select * from user1.table1;

优点自己总结吧。

2.       Oracle 受权 ORA-00990: 权限缺失或无效

创建一个用户：

create user xxx identified by yyy

 当密码里面有特殊字符时  windows平台下面应该用 "yyy"  其它平台未测试

刚创建好的用户是没有任何权限的  连登录数据库的权限都没有

这时，就要给用户授予权限：

ORACLE里面的权限有系统权限和对象权限两种

可以通过

select distinct p.privilege

 from user_sys_privs p

 order by p.privilege asc；

 来查询ORACLE里面的所有系统权限；

常用的系统权限有 ：

           CREATE SESSION                     创建会话 登录数据库

       CREATE SEQUENCE                     创建序列

       CREATE SYNONYM                      创建同名对象

       CREATE TABLE                        在用户模式中创建表

       CREATE ANY TABLE                    在任何模式中创建表

       DROP ANY TABLE                      在任何模式中删除表

       CREATE PROCEDURE                    创建存储过程

       EXECUTE ANY PROCEDURE               执行任何模式的存储过程

       CREATE USER                         创建用户

       DROP USER                           删除用户

       CREATE VIEW                         创建视图

通过下面的SQL可以查询所有的对象权限：

select distinct t.privilege fromuser_tab_privs t;

 结果如下：

              FLASHBACK
             EXECUTE
             ONCOMMIT REFRESH
             ALTER
             DEQUEUE
             UPDATE
             DELETE
             DEBUG
             UNDER
             QUERY REWRITE
             SELECT
             READ
             INSERT
              INDEX
              WRITE
              REFERENCES
              MERGE VIEW

另外还有角色授权  可以通过下面的SQL来查询ORALCE里面内置有哪些角色：

select distinct r.granted_role role_name

 from user_role_privs r

 where r.default_role = ‘YES‘

 order by role_name asc;

 授权语句：grant 权限

系统授权直接写：grant 权限名称  to  用户名；

对象权限则要写成：grant 权限名称  on schema. 对象名 to 用户名

 还要注意一点是：授予权的时候系统权限和对象权限不能同时写，也就是说不能写成下面这个样子：

GRANT CREATE TABLE, SELECT ON scott.testTO scott1

 这样写会报  ORA-00990: 权限缺失或无效

但是系统权限和角色是可以同时写在一起授予给某一个用户的

3.       oracle之报错:ORA-00054: 资源正忙，要求指定NOWAIT

解决方案：

select session_id from v$locked_object; --首先得到被锁对象的session_id

SELECT sid, serial#, username, osuser FROM v$sessionwhere sid = session_id; --通过上面得到的session_id去取得v$session的sid和serial#，然后对该进程进行终止。

ALTER SYSTEM KILL SESSION ‘sid,serial‘;

example:

ALTER SYSTEM KILL SESSION ‘13, 8‘;

--ora0054

--查找锁定的会话

 select session_id fromv$locked_object;

 SELECTsid, serial#,username, osuser

   FROM v$session

  wheresidin (4908, 4913, 4955);

--杀死会话

  ALTERSYSTEMKILLSESSION‘4908,2223‘;

  ALTERSYSTEMKILLSESSION‘4913,937‘;

  ALTERSYSTEMKILLSESSION‘4955,3079‘;

--查找锁 

select * from v$lock whereblock=1;

select * from v$locked_object;

--查看当前用户的一些权限

selectdistinct p.privilegefromuser_sys_privs p;

4.       层次查询实例也可以利用两个嵌套的游标实现

PLSQL\PACKAGE_GET_AUDIT_DESC.pck

selectdistinct x.form_kind, x.form_no, auditm

  from (selectt.FORM_KIND,

              t.FORM_NO,

               substr(sys_connect_by_path(t.AUDITM,‘,‘), 2) auditm,

               level lv,

               max(level) over(partitionbyt.FORM_KIND, t.FORM_NO) max_level

          fromdw_tes_flow_audit_fs_v t

         where1 = 1

              /*andt.FORM_KIND = ‘SYS.FORM.009‘

              andt.FORM_NO = ‘277721‘*/

           andconnect_by_isleaf = 1

        connectbyprior t.FORM_KIND= t.FORM_KIND

               andprior t.FORM_NO =t.FORM_NO

               andprior t.AUDITM< t.AUDITM) x

 where lv =max_level;

Oracle PL/SQL,古老的榕树,5-wow.com