Oracle的函数和存储过程的简单...-多表连接-PostgreSQL的用户、角色和权限管理

当前位置: 数据库>其它

本页文章导读:

▪Oracle的函数和存储过程的简单demo 最近的项目中用到了Oracle函数和存储过程，之前一直都是用的sql语句进行数据库操作，这一次用过存储过程之后，发现存储过程确实非常好用，功能也非常强大所以各写个简单.........

▪多表连接 *********************************************** 　ORACLE的连接（非SQL99的连接） *********************************************** 一、多表连接的起因 1、关系数据库的设计范式（１）第一范式（1NF）：属性不可分。.........

▪PostgreSQL的用户、角色和权限管理 Pg权限分为两部分，一部分是“系统权限”或者数据库用户的属性，可以授予role或user（两者区别在于login权限）；一部分为数据库对象上的操作权限。对超级用户不做权限检查，其它走acl。对.........

[1]Oracle的函数和存储过程的简单demo

来源: 互联网发布时间: 2013-11-07

最近的项目中用到了Oracle函数和存储过程，之前一直都是用的sql语句进行数据库操作，这一次用过存储过程之后，发现存储过程确实非常好用，功能也非常强大所以各写个简单的demo给大家分享：
一、函数
1.函数的创建
create or replace function SumResult(Param_One in number , Param_Two in number)
return Result number
is
Result number ;
begin
Result := Param_One + Param_two
return Result;
end SumResult;
2.函数的使用
select distinct sumresult(22,44) into sum from tablename;

二、存储过程
1.存储过程的创建
create or replace procedure PageQuery (
page in number ,
size in number ,
out_list out sysrefcursor)
is

sql_str varchar(500);
begin
sql_str := 'select rownum rn,t1.* from info t1 where rownum > ' ||
(param_page - 1) * param_size || ' and rownum <' ||
param_page * param_size;
--输出sql语句便于检查
dbms_output.put_line(sql_str);
--返回游标
open out_list for sql_str;

end PageQuery;

作者：liu00614 发表于2013-2-22 16:20:34 原文链接

阅读：0 评论：0 查看评论

[2]多表连接

来源: 互联网发布时间: 2013-11-07

***********************************************
　ORACLE的连接（非SQL99的连接）
***********************************************
一、多表连接的起因
1、关系数据库的设计范式
（１）第一范式（1NF）：属性不可分。
（２）第二范式（2NF）：非主属性完全依赖于码。
（３）第三范式（3NF）：符合2NF，并且，消除传递依赖。
　还有另外三个范式我就不多说了！

２、下面是范化的一个例子：我们来设一个EMPLOYEES(雇员表)，其中字段有:EMPLOYEE_ID,NAME,JOB_ID,DEPARTMENT_ID
EMPLOYEES这个表用于保存雇员的信息，而你想要删除其中的一个雇员，这时你就必须同时删除一个部门和职位。范化就是要解决这个问题，你可以将这个表化为三个表，一个用于存储雇员的信息（EMPLOYEES），一个用于存储每个雇员所在部门的信息（DEPARTMENTS），另一个用于存储雇员的职位信息（JOBS），这样对其中一个表做添加或删除操作就不会影响另一个表。

３、我们在查询信息的往往要显示比较全面的信息，比如就上面的例子，我要查雇员的所有信息(雇员号、姓名，部门名称，职位等)，这些信息分布在这三个表中，这时候就要把EMPLOYEES、DEPARTMENTS、JOBS这几个表连接起来显示全面的雇员信息。下面我们来讲讲各种连接。

二、笛卡尔乘积（以HR用户做测试）
１、要想从多个表中取数据，命令非常简单。我们可以在SELECT命令的FROM后，加多个表名，就是告诉Oracle我要从多个表中取数据。
select * from employees,departments；
显示的结果的总行数等于EMPLOYEES表的总行数乘以DEPARTMENTS表的总行数，如下操作：
hr@OCM> select count(*) from employees;

COUNT(*)
----------
105

hr@OCM> select count(*) from departments;

COUNT(*)
----------
28

hr@OCM> select count(*) from employees,departments;

COUNT(*)
----------
2940

hr@OCM> select 105*28 from dual;

105*28
----------
2940

２、就是将EMPLOYEES表的每一行，和DEPARTMENTS表的每一行，组合到一起。这种连接方式，叫笛卡尔乘积。也就是EMPLOYEES表有105行，DEPARTMENTS表有28行。连接结果有105*28，一共2940行。这样的连接结果，没有什么意义。

三、等值连接
１、操作如下：
EMPLOYEES表和DEPARTMENTS表有一个共同的列，就是DEPARTMENT_ID。
hr@OCM> select EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_NAME
2 from EMPLOYEES e,DEPARTMENTS d
3 where e.DEPARTMENT_ID=d.DEPARTMENT_ID;

EMPLOYEE_ID FIRST_NAME           DEPARTMENT_NAME
----------- -------------------- ------------------------------
        200 Jennifer             Administration
        201 Michael              Marketing
        202 Pat                  Marketing
        114 Den                  Purchasing
        119 Karen                Purchasing
        115 Alexander            Purchasin

hr@OCM> select EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_ID,DEPARTMENT_NAME
2         from EMPLOYEES e,DEPARTMENTS d
3         where e.DEPARTMENT_ID=d.DEPARTMENT_ID;
select EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_ID,DEPARTMENT_NAME
                              *
ERROR at line 1:
ORA-00918: column ambiguously defined

２、只对两个表中，某些列相等的行进行连接，就是等值连接。在连接条件中，列名前的表名，e.DEPARTMENT_ID=d.DEPARTMENT_ID，也就是e和d有时可以省略。但是，只有当列名不相同时，才可省略。

３、表别名的意义:我为EMPLOYEES表定义了别名e，也就是在表名后加空格，然后再输入的字符，就是表的别名。DEPARTMENTS的别名是d。然后，表名.DEPARTMENT_ID就可以打为e.DEPARTMENT_ID或d.DEPARTMENT_ID。

４、针对错误：ORA-00918: column ambiguously defined。就是Oracle无法确定DEPARTMENT_ID的值来自于FROM后的哪一个表。这时，必须将表名加在列名前，明确告诉ORACLE，此列的值，来值于哪一个表。命令如下：
hr@OCM> select EMPLOYEE_ID,FIRST_NAME,e.DEPARTMENT_ID,DEPARTMENT_NAME
2 from EMPLOYEES e,DEPARTMENTS d
3 where e.DEPARTMENT_ID=d.DEPARTMENT_ID;

EMPLOYEE_ID FIRST_NAME           DEPARTMENT_ID DEPARTMENT_NAME
----------- -------------------- ------------- ------------------------------
        200 Jennifer                        10 Administration
        201 Michael                         20 Marketing
        202 Pat                             20 Marketing
        114 Den                             30 Purchasing
        119 Karen                           30 Purchasing
        115 Alexander                       30 Purchasing

５、附加其他条件:在WHERE后的连接条件，也是普通条件，完全可以和其他条件混合使用。比如，我想将EMPLOYEES和DEPARTMENTS连接显示，但只显示名字Karen这个人的信息。两个表的等值连接，条件是e.DEPARTMENT_ID=d.DEPARTMENT_ID，现在还要有一个条件是FIRST_NAME='Karen'。多个条件的组合，可以使用AND、OR，此处应该使用AND，因为WHERE后的两个条件，是且的关系。我们要显示的是e.DEPARTMENT_ID=d.DEPARTMENT_ID，并在此条件基础上，FIRST_NAME='Karen'的行，命令如下：
hr@OCM> select EMPLOYEE_ID,FIRST_NAME,e.DEPARTMENT_ID,DEPARTMENT_NAME
2 from EMPLOYEES e,DEPARTMENTS d
3 where e.DEPARTMENT_ID=d.DEPARTMENT_ID and FIRST_NAME='Karen';

EMPLOYEE_ID FIRST_NAME           DEPARTMENT_ID DEPARTMENT_NAME
----------- -------------------- ------------- ------------------------------
        119 Karen                           30 Purchasing
        146 Karen                           80 Sales

６、三个以上表的连接，有时可能会需要将多个表连接起来，比如上例的结果中,再加上雇员的职位信息（JOBS）。
hr@OCM> select EMPLOYEE_ID,FIRST_NAME,e.DEPARTMENT_ID,DEPARTMENT_NAME,JOB_TITLE
2 from EMPLOYEES e,DEPARTMENTS d,JOBS j
3 where e.DEPARTMENT_ID=d.DEPARTMENT_ID and e.JOB_ID=j.JOB_ID and FIRST_NAME='Karen';

EMPLOYEE_ID FIRST_NAME DEPARTMENT_ID DEPARTMENT_NAME JOB_TITLE

[3]PostgreSQL的用户、角色和权限管理

来源: 互联网发布时间: 2013-11-07

Pg权限分为两部分，一部分是“系统权限”或者数据库用户的属性，可以授予role或user（两者区别在于login权限）；一部分为数据库对象上的操作权限。对超级用户不做权限检查，其它走acl。对于数据库对象，开始只有所有者和超级用户可以做任何操作，其它走acl。在pg里，对acl模型做了简化，组和角色都是role，用户和角色的区别是角色没有login权限。

可以用下面的命令创建和删除角色，

CREATE ROLE name;

DROP ROLE name;

为了方便，也可以在 shell 命令上直接调用程序 createuser 和 dropuser，这些工具对相应命令提供了封装:

createuser name

dropuser name

数据库对象上的权限有：SELECT，INSERT， UPDATE，DELETE，RULE， REFERENCES，TRIGGER，CREATE， TEMPORARY，EXECUTE，和 USAGE等，具体见下面定义

typedefuint32AclMode; /* a bitmask of privilege bits */

#define ACL_INSERT (1<<0) /* forrelations */

#defineACL_SELECT (1<<1)

#defineACL_UPDATE (1<<2)

#defineACL_DELETE (1<<3)

#defineACL_TRUNCATE (1<<4)

#defineACL_REFERENCES (1<<5)

#defineACL_TRIGGER (1<<6)

#defineACL_EXECUTE (1<<7) /* for functions */

#defineACL_USAGE (1<<8) /* for languages, namespaces, FDWs, and

* servers */

#defineACL_CREATE (1<<9) /* for namespaces and databases */

#defineACL_CREATE_TEMP (1<<10) /* for databases */

#defineACL_CONNECT (1<<11) /* for databases */

#defineN_ACL_RIGHTS 12 /* 1plus the last 1<<x */

#defineACL_NO_RIGHTS 0

/*Currently, SELECT ... FOR UPDATE/FOR SHARE requires UPDATE privileges */

#defineACL_SELECT_FOR_UPDATE ACL_UPDATE

我们可以用特殊的名字 PUBLIC 把对象的权限赋予系统中的所有角色。在权限声明的位置上写 ALL，表示把适用于该对象的所有权限都赋予目标角色。

beigang=# grantall on schema csm_ca to public;

GRANT

beigang=# revoke all on schema csm_ca frompublic;

REVOKE

beigang=#

每种对象的all权限定义如下：

* Bitmasks defining "allrights" for each supported object type

#defineACL_ALL_RIGHTS_COLUMN (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_REFERENCES)

#defineACL_ALL_RIGHTS_SEQUENCE (ACL_USAGE|ACL_SELECT|ACL_UPDATE)

#defineACL_ALL_RIGHTS_DATABASE (ACL_CREATE|ACL_CREATE_TEMP|ACL_CONNECT)

#define ACL_ALL_RIGHTS_FDW (ACL_USAGE)

#defineACL_ALL_RIGHTS_FOREIGN_SERVER (ACL_USAGE)

#defineACL_ALL_RIGHTS_FUNCTION (ACL_EXECUTE)

#defineACL_ALL_RIGHTS_LANGUAGE (ACL_USAGE)

#defineACL_ALL_RIGHTS_LARGEOBJECT (ACL_SELECT|ACL_UPDATE)

#defineACL_ALL_RIGHTS_NAMESPACE (ACL_USAGE|ACL_CREATE)