15. 查询数据14SELECT pn, vn, sum(prc*qty)
FROM sale
GROUP BY ROLLUP(pn, vn)
ORDER BY 1,2,3;SELECT pn, vn, sum(prc*qty)
FROM sale
GROUP BY pn, vn
UNION ALL
SELECT pn, null, sum(prc*qty)
FROM sale
GROUP BY pn
UNION ALL
SELECT null, null, sum(prc*qty)
FROM SALE
ORDER BY 1,2,3;优化代替
16. 15
优化代替
SELECT pn, vn, sum(prc*qty)
FROM sale
GROUP BY GROUPING SETS ( (pn, vn), (pn), (vn), () )
ORDER BY 1,2,3;SELECT pn, vn, sum(prc*qty)
FROM sale
GROUP BY CUBE(pn, vn)
ORDER BY 1,2,3;
18. 存储过程17定义方式：
调用方式
CREATE FUNCTION FUNC_NAME(IN day_id character varying,
OUT v_retcode character varying)
RETURNS record AS $$
DECLARE
BENIN
……
END
$$ LANGUAGE SQL;SELECT FUNC_NAME(‘20110930’);
20. 19创建用户：CREATE ROLE john WITH LOGIN
修改用户权限：ALTER ROLE john WITH CREATEDB;
修改用户密码：ALTER ROLE john WITH PASSWORD b;
对象权限赋予角色：
CREATE ROLE admin CREATEROLE CREATEDB;
GRANT admin TO john, sally;
REVOKE admin FROM bob;
继承权限：SET ROLE admin;
22. 开发注意事项21数据类型转换，必须显式转换
CAST ( ’string ’ AS type ) CAST(‘2.117902’ AS REAL)
不支持NOT IN，可以用左连接代替
尽量使用外连接的方式代替嵌套子查询
查询记录，限制记录条数，如limit 100.
TRIGGER不支持，foreign key无效。
数据类型与ORACLE之间的对应关系
Date-> timestamp without time zone
Varchar2-> varchar
CLOB-> TEXT
NUMBER-> NUMBER
BLOB-> BYTEA
