• 关于

    nvl数据库

    的搜索结果

回答

1.  检查数据库在安全组是否已经开放了端口 实例控制台-》安全组规则 2. 检查数据库在防火墙是否已经开放了端口 iptables -Nvl 3. 检查数据库是否已经对该安装DEDECMS的主机进行授权 mysql命令下show grants

叶康铭 2019-12-02 00:52:47 0 浏览量 回答数 0

回答

换成多个查询条件是对的,如果一定要连接查询的话,要注意 || 只要其中一个字段为null结果为null,所以每个字段要给他一个默认值: SELECT * FROM [表名] WHERE ifnull(name,'') || ifnull(phone,'') || ifnull(adress,'') || ifnull(qq,'') LIKE '%某值%' ######棒,用函数,学习了######nvl?######ifnull等同于nvl 新学数据库不懂知识勿见怪###### nvl 是函数 不是SQL 语句 ######如果WHERE 字段每个都有索引,建议分开查,然后数据合并######回复 @菲菲OSC : 通过SQL可以合并,程序也可以。######怎么分开查然后数据合并呢? sql 语句怎么写?######@  行者GC 怎么分开查 然后数据合并呢?######用 or######+10086######or 我也想过,只是觉得语句长了点。。。###### 数据量大得话,应该用全文检索了, 毕竟  %% 用不到索引,效率很低。 数据量一般的话, 可以考虑 or,union。concat_ws 可能会有 误查得情况 ######谢###### 过滤后有2中类型数据 1. 姓名和地址 不是全数字 2. QQ和手机是全数字 然后针对查询就好 ###### 引用来自“烧土豆”的答案 过滤后有2中类型数据 1. 姓名和地址 不是全数字 2. QQ和手机是全数字 然后针对查询就好 不是很明白,新手,sql语句不是很熟,写一下怎么实现好吗 ######回复 @烧土豆 : 谢谢指教######一个输入框是四种类型关键词的入口 如果or查询肯定很慢 所以给出思路就是劲量分拆这四种类型的关键词 回复中我简单的分拆成2中类型的 一个全数字 一个不全数字 你可以再细分 当然能分成一种最好 原则是不要把压力给数据库

kun坤 2020-06-09 22:32:23 0 浏览量 回答数 0

问题

jfinal oracle分页的问题?报错

爱吃鱼的程序员 2020-06-14 21:32:13 0 浏览量 回答数 1

Quick BI 数据可视化分析平台

2020年入选全球Gartner ABI魔力象限,为中国首个且唯一入选BI产品

回答

Ubuntu通过apt安装LAMP环境#安装下面这几个包后就可以跑Discuz!这些常用PHP程序了 sudo apt-get install apache2 php5 php5-gd php5-mysqlnd mysql-server sudo service apache2 {status|start|stop|restart|reload} sudo service mysql {status|start|stop|restart|reload} Apache网站根目录 /var/www Apache主目录 /etc/apache2 Apache主配置文件 /etc/apache2/apache2.conf Apache日志 /var/log/apache2 PHP主目录 /etc/php5 PHP配置文件 /etc/php5/apache2/php.ini PHP模块位置 /usr/lib/php5 MySQL配置文件 /etc/mysql/my.cnf MySQL数据库文件 /var/lib/mysql 运行 mysql_secure_installation 设置mysql根用户密码 防火墙只开放ssh(22端口),http(80端口): Ubuntu使用ufw可以这样做: sudo ufw enable && \ sudo ufw default deny && \ sudo ufw allow 22/tcp && \ sudo ufw allow 80/tcp && \ sudo ufw status 可以这样删除,比如80端口: sudo ufw delete allow 80/tcp Ubuntu使用iptables可以这样做: sudo ufw disable && sudo ufw default allow 设置INPUT/FORWARD/OUTPUT为ACCEPT,如果为DENY,下一步骤将使系统的所有网络访问中断,包括ssh. sudo iptables -F && sudo iptables -X && sudo iptables -Z && sudo iptables -L sudo iptables -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT sudo iptables -A INPUT -i lo -j ACCEPT sudo iptables -A INPUT -p tcp -i eth0 --dport 22 -j ACCEPT sudo iptables -A INPUT -p tcp -i eth0 --dport 80 -j ACCEPT sudo iptables -A INPUT -j DROP iptables-save > /etc/iptables.up.rules 切换到root用户执行,sudo会提示无权限.Redhat的保存操作为service iptables save. sudo nano /etc/network/interfaces 在末尾添加一行,在网络启动时应用防火墙规则: pre-up iptables-restore < /etc/iptables.up.rules 查看设置的规则: sudo iptables -nvL --line-numbers 插入一条规则到INPUT链第6的位置: sudo iptables -I INPUT 6 -j DROP 修改INPUT链的第6条规则: sudo iptables -R INPUT 6 -j ACCEPT 删除INPUT链第6条规则: sudo iptables -D INPUT 6 ssh使用密钥验证登录: #生成密钥对(私钥id_rsa和公钥id_rsa.pub) ssh-keygen cd ~/.ssh cat id_rsa.pub >> authorized_keys #使用密钥登录后禁止密码认证 sudo nano /etc/ssh/sshd_config PasswordAuthentication no #重启服务生效 sudo service ssh restart 如果你要使用最新的PHP7,最好还是学习一下自行编译,比如: Ubuntu上Nginx/Apache/PHP编译打包教程

a123456678 2019-12-02 02:59:05 0 浏览量 回答数 0

问题

阿里云数据库优化SQL调优

oracledbawyq 2019-12-01 21:13:44 11418 浏览量 回答数 1

回答

详细解答可以参考官方帮助文档 通过本文档中的示例,Oracle 用户可以快速了解 PPAS 数据库中的术语及概念,以便在迁移及开发过程中提高效率。 以下所有操作基于一个基础模型,通过此模型用户可以看到 RDS for PPAS 中最基本的创建数据库、创建数据表、管理用户等操作,基础数据模型如下: 同时,为了模拟 Oracle 上类似的环境,我们会建立一名字为 orcl_ppas 的数据库(database),在此数据库中建立名为 scott 的用户,并建立与这个用户同名的 schema 用户空间。 PPAS兼容性手册 关于PPAS兼容性说明的完整内容,请参见阿里云PPAS兼容性手册。 连接数据库 psql psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U myuser -d template1 用户 myuser 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. template1=> 创建并连接数据库 CREATE DATABASE template1=> CREATE DATABASE orcl_ppas; CREATE DATABASE template1=> \c orcl_ppas psql.bin (9.4.1.3, 服务器 9.3.5.14) 创建普通用户 CREATE ROLE orcl_ppas=> CREATE ROLE scott LOGIN PASSWORD 'scott123'; CREATE ROLE 创建用户的私有空间 CREATE SCHEMA orcl_ppas=> CREATE SCHEMA scott; CREATE SCHEMA orcl_ppas=> GRANT scott TO myuser; GRANT ROLE orcl_ppas=> ALTER SCHEMA scott OWNER TO scott; ALTER SCHEMA orcl_ppas=> REVOKE scott FROM myuser; REVOKE ROLE 说明 如果在进行 ALTER SCHEMA scott OWNER TO scott 之前没有将 scott 加入到 myuser 角色,将会出现如下权限问题。ERROR:must be member of role "scott" 从安全角度出发,在处理完 OWNER 的授权后,请将 scott 用户移出 myuser 角色以提高安全性。 连接到 orcl_ppas 数据库 说明 此步骤十分重要,以下所有操作都是在 scott 账号下进行的,否则所建立的数据表及各种数据库对象将不属于 scott用户,导致权限问题。 [root@localhost bin]# ./psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U scott -d orcl_ppas 用户 scott 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. orcl_ppas=> 创建数据表 CREATE TABLE CREATE TABLE dept ( deptno NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY, dname VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE, lock 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 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 OR REPLACE VIEW CREATE OR REPLACE VIEW salesemp AS SELECT empno, ename, hiredate, sal, comm FROM emp WHERE job = 'SALESMAN'; 创建序列 CREATE SEQUENCE CREATE SEQUENCE next_empno START WITH 8000 INCREMENT BY 1; 插入数据 INSERT INTO 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'); 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); 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'); 查询优化器数据分析 ANALYZE ANALYZE dept; ANALYZE emp; ANALYZE jobhist; 建立存储过程 CREATE PROCEDURE 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; -- -- Procedure that selects an employee row given the employee -- number and displays certain columns. -- CREATE OR REPLACE PROCEDURE select_emp ( p_empno IN NUMBER ) IS v_ename emp.ename%TYPE; v_hiredate emp.hiredate%TYPE; v_sal emp.sal%TYPE; v_comm emp.comm%TYPE; v_dname dept.dname%TYPE; v_disp_date VARCHAR2(10); BEGIN SELECT ename, hiredate, sal, NVL(comm, 0), dname INTO v_ename, v_hiredate, v_sal, v_comm, v_dname FROM emp e, dept d WHERE empno = p_empno AND e.deptno = d.deptno; v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY'); DBMS_OUTPUT.PUT_LINE('Number : ' || p_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm); DBMS_OUTPUT.PUT_LINE('Department: ' || v_dname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); END; -- -- Procedure that queries the 'emp' table based on -- department number and employee number or name. Returns -- employee number and name as IN OUT parameters and job, -- hire date, and salary as OUT parameters. -- 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; -- -- Procedure to call 'emp_query_caller' with IN and IN OUT -- parameters. Displays the results received from IN OUT and -- OUT parameters. -- CREATE OR REPLACE PROCEDURE emp_query_caller IS v_deptno NUMBER(2); v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_hiredate DATE; v_sal NUMBER; BEGIN v_deptno := 30; v_empno := 0; v_ename := 'Martin'; emp_query(v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal); DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_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); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('More than one employee was selected'); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No employees were selected'); END; 建立函数 CREATE FUNCTION 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; -- -- Function that gets the next number from sequence, 'next_empno', -- and ensures it is not already in use as an employee number. -- CREATE OR REPLACE FUNCTION new_empno RETURN NUMBER IS v_cnt INTEGER := 1; v_new_empno NUMBER; BEGIN WHILE v_cnt > 0 LOOP SELECT next_empno.nextval INTO v_new_empno FROM dual; SELECT COUNT(*) INTO v_cnt FROM emp WHERE empno = v_new_empno; END LOOP; RETURN v_new_empno; END; -- -- EDB-SPL function that adds a new clerk to table 'emp'. This function -- uses package 'emp_admin'. -- CREATE OR REPLACE FUNCTION hire_clerk ( p_ename VARCHAR2, p_deptno NUMBER ) RETURN NUMBER IS v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_mgr NUMBER(4); v_hiredate DATE; v_sal NUMBER(7,2); v_comm NUMBER(7,2); v_deptno NUMBER(2); BEGIN v_empno := new_empno; INSERT INTO emp VALUES (v_empno, p_ename, 'CLERK', 7782, TRUNC(SYSDATE), 950.00, NULL, p_deptno); SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno FROM emp WHERE empno = v_empno; DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Job : ' || v_job); DBMS_OUTPUT.PUT_LINE('Manager : ' || v_mgr); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission : ' || v_comm); RETURN v_empno; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- PostgreSQL PL/pgSQL function that adds a new salesman -- to table 'emp'. -- CREATE OR REPLACE FUNCTION hire_salesman ( p_ename VARCHAR, p_sal NUMERIC, p_comm NUMERIC ) RETURNS NUMERIC AS $$ DECLARE v_empno NUMERIC(4); v_ename VARCHAR(10); v_job VARCHAR(9); v_mgr NUMERIC(4); v_hiredate DATE; v_sal NUMERIC(7,2); v_comm NUMERIC(7,2); v_deptno NUMERIC(2); BEGIN v_empno := new_empno(); INSERT INTO emp VALUES (v_empno, p_ename, 'SALESMAN', 7698, CURRENT_DATE, p_sal, p_comm, 30); SELECT INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp WHERE empno = v_empno; RAISE INFO 'Department : %', v_deptno; RAISE INFO 'Employee No: %', v_empno; RAISE INFO 'Name : %', v_ename; RAISE INFO 'Job : %', v_job; RAISE INFO 'Manager : %', v_mgr; RAISE INFO 'Hire Date : %', v_hiredate; RAISE INFO 'Salary : %', v_sal; RAISE INFO 'Commission : %', v_comm; RETURN v_empno; EXCEPTION WHEN OTHERS THEN RAISE INFO 'The following is SQLERRM:'; RAISE INFO '%', SQLERRM; RAISE INFO 'The following is SQLSTATE:'; RAISE INFO '%', SQLSTATE; RETURN -1; END; 建立规则 CREATE RULE CREATE OR REPLACE RULE salesemp_i AS ON INSERT TO salesemp DO INSTEAD INSERT INTO emp VALUES (NEW.empno, NEW.ename, 'SALESMAN', 7698, NEW.hiredate, NEW.sal, NEW.comm, 30); CREATE OR REPLACE RULE salesemp_u AS ON UPDATE TO salesemp DO INSTEAD UPDATE emp SET empno = NEW.empno, ename = NEW.ename, hiredate = NEW.hiredate, sal = NEW.sal, comm = NEW.comm WHERE empno = OLD.empno; CREATE OR REPLACE RULE salesemp_d AS ON DELETE TO salesemp DO INSTEAD DELETE FROM emp WHERE empno = OLD.empno; 建立触发器 CREATE TRIGGER CREATE OR REPLACE TRIGGER user_audit_trig AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE v_action VARCHAR2(24); BEGIN IF INSERTING THEN v_action := ' added employee(s) on '; ELSIF UPDATING THEN v_action := ' updated employee(s) on '; ELSIF DELETING THEN v_action := ' deleted employee(s) on '; END IF; DBMS_OUTPUT.PUT_LINE('User ' || USER || v_action || TO_CHAR(SYSDATE,'YYYY-MM-DD')); END; CREATE OR REPLACE TRIGGER emp_sal_trig BEFORE DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW DECLARE sal_diff NUMBER; BEGIN IF INSERTING THEN DBMS_OUTPUT.PUT_LINE('Inserting employee ' || :NEW.empno); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); END IF; IF UPDATING THEN sal_diff := :NEW.sal - :OLD.sal; DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); DBMS_OUTPUT.PUT_LINE('..Raise : ' || sal_diff); END IF; IF DELETING THEN DBMS_OUTPUT.PUT_LINE('Deleting employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); END IF; END; 建立包 CREATE PACKAGE CREATE OR REPLACE PACKAGE emp_admin IS FUNCTION get_dept_name ( p_deptno NUMBER ) RETURN VARCHAR2; FUNCTION update_emp_sal ( p_empno NUMBER, p_raise NUMBER ) RETURN NUMBER; PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ); PROCEDURE fire_emp ( p_empno NUMBER ); END emp_admin; 建立包体 CREATE PACKAGE BODY -- -- Package body for the 'emp_admin' package. -- CREATE OR REPLACE PACKAGE BODY emp_admin IS -- -- Function that queries the 'dept' table based on the department -- number and returns the corresponding department name. -- FUNCTION get_dept_name ( p_deptno IN NUMBER ) RETURN VARCHAR2 IS v_dname VARCHAR2(14); BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno; RETURN v_dname; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Invalid department number ' || p_deptno); RETURN ''; END; -- -- Function that updates an employee's salary based on the -- employee number and salary increment/decrement passed -- as IN parameters. Upon successful completion the function -- returns the new updated salary. -- FUNCTION update_emp_sal ( p_empno IN NUMBER, p_raise IN NUMBER ) RETURN NUMBER IS v_sal NUMBER := 0; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_empno; v_sal := v_sal + p_raise; UPDATE emp SET sal = v_sal WHERE empno = p_empno; RETURN v_sal; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); RETURN -1; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- Procedure that inserts a new employee record into the 'emp' table. -- PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ) AS BEGIN INSERT INTO emp(empno, ename, job, sal, hiredate, comm, mgr, deptno) VALUES(p_empno, p_ename, p_job, p_sal, p_hiredate, p_comm, p_mgr, p_deptno); END; -- -- Procedure that deletes an employee record from the 'emp' table based -- on the employee number. -- PROCEDURE fire_emp ( p_empno NUMBER ) AS BEGIN DELETE FROM emp WHERE empno = p_empno; END; END;

2019-12-01 22:57:14 0 浏览量 回答数 0

回答

详细解答可以参考官方帮助文档 通过本文档中的示例,Oracle 用户可以快速了解 PPAS 数据库中的术语及概念,以便在迁移及开发过程中提高效率。 以下所有操作基于一个基础模型,通过此模型用户可以看到 RDS for PPAS 中最基本的创建数据库、创建数据表、管理用户等操作,基础数据模型如下: 同时,为了模拟 Oracle 上类似的环境,我们会建立一名字为 orcl_ppas 的数据库(database),在此数据库中建立名为 scott 的用户,并建立与这个用户同名的 schema 用户空间。 PPAS兼容性手册 关于PPAS兼容性说明的完整内容,请参见阿里云PPAS兼容性手册。 连接数据库 psql psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U myuser -d template1 用户 myuser 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. template1=> 创建并连接数据库 CREATE DATABASE template1=> CREATE DATABASE orcl_ppas; CREATE DATABASE template1=> \c orcl_ppas psql.bin (9.4.1.3, 服务器 9.3.5.14) 创建普通用户 CREATE ROLE orcl_ppas=> CREATE ROLE scott LOGIN PASSWORD 'scott123'; CREATE ROLE 创建用户的私有空间 CREATE SCHEMA orcl_ppas=> CREATE SCHEMA scott; CREATE SCHEMA orcl_ppas=> GRANT scott TO myuser; GRANT ROLE orcl_ppas=> ALTER SCHEMA scott OWNER TO scott; ALTER SCHEMA orcl_ppas=> REVOKE scott FROM myuser; REVOKE ROLE 说明 如果在进行 ALTER SCHEMA scott OWNER TO scott 之前没有将 scott 加入到 myuser 角色,将会出现如下权限问题。ERROR:must be member of role "scott" 从安全角度出发,在处理完 OWNER 的授权后,请将 scott 用户移出 myuser 角色以提高安全性。 连接到 orcl_ppas 数据库 说明 此步骤十分重要,以下所有操作都是在 scott 账号下进行的,否则所建立的数据表及各种数据库对象将不属于 scott用户,导致权限问题。 [root@localhost bin]# ./psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U scott -d orcl_ppas 用户 scott 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. orcl_ppas=> 创建数据表 CREATE TABLE CREATE TABLE dept ( deptno NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY, dname VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE, lock 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 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 OR REPLACE VIEW CREATE OR REPLACE VIEW salesemp AS SELECT empno, ename, hiredate, sal, comm FROM emp WHERE job = 'SALESMAN'; 创建序列 CREATE SEQUENCE CREATE SEQUENCE next_empno START WITH 8000 INCREMENT BY 1; 插入数据 INSERT INTO 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'); 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); 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'); 查询优化器数据分析 ANALYZE ANALYZE dept; ANALYZE emp; ANALYZE jobhist; 建立存储过程 CREATE PROCEDURE 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; -- -- Procedure that selects an employee row given the employee -- number and displays certain columns. -- CREATE OR REPLACE PROCEDURE select_emp ( p_empno IN NUMBER ) IS v_ename emp.ename%TYPE; v_hiredate emp.hiredate%TYPE; v_sal emp.sal%TYPE; v_comm emp.comm%TYPE; v_dname dept.dname%TYPE; v_disp_date VARCHAR2(10); BEGIN SELECT ename, hiredate, sal, NVL(comm, 0), dname INTO v_ename, v_hiredate, v_sal, v_comm, v_dname FROM emp e, dept d WHERE empno = p_empno AND e.deptno = d.deptno; v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY'); DBMS_OUTPUT.PUT_LINE('Number : ' || p_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm); DBMS_OUTPUT.PUT_LINE('Department: ' || v_dname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); END; -- -- Procedure that queries the 'emp' table based on -- department number and employee number or name. Returns -- employee number and name as IN OUT parameters and job, -- hire date, and salary as OUT parameters. -- 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; -- -- Procedure to call 'emp_query_caller' with IN and IN OUT -- parameters. Displays the results received from IN OUT and -- OUT parameters. -- CREATE OR REPLACE PROCEDURE emp_query_caller IS v_deptno NUMBER(2); v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_hiredate DATE; v_sal NUMBER; BEGIN v_deptno := 30; v_empno := 0; v_ename := 'Martin'; emp_query(v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal); DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_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); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('More than one employee was selected'); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No employees were selected'); END; 建立函数 CREATE FUNCTION 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; -- -- Function that gets the next number from sequence, 'next_empno', -- and ensures it is not already in use as an employee number. -- CREATE OR REPLACE FUNCTION new_empno RETURN NUMBER IS v_cnt INTEGER := 1; v_new_empno NUMBER; BEGIN WHILE v_cnt > 0 LOOP SELECT next_empno.nextval INTO v_new_empno FROM dual; SELECT COUNT(*) INTO v_cnt FROM emp WHERE empno = v_new_empno; END LOOP; RETURN v_new_empno; END; -- -- EDB-SPL function that adds a new clerk to table 'emp'. This function -- uses package 'emp_admin'. -- CREATE OR REPLACE FUNCTION hire_clerk ( p_ename VARCHAR2, p_deptno NUMBER ) RETURN NUMBER IS v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_mgr NUMBER(4); v_hiredate DATE; v_sal NUMBER(7,2); v_comm NUMBER(7,2); v_deptno NUMBER(2); BEGIN v_empno := new_empno; INSERT INTO emp VALUES (v_empno, p_ename, 'CLERK', 7782, TRUNC(SYSDATE), 950.00, NULL, p_deptno); SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno FROM emp WHERE empno = v_empno; DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Job : ' || v_job); DBMS_OUTPUT.PUT_LINE('Manager : ' || v_mgr); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission : ' || v_comm); RETURN v_empno; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- PostgreSQL PL/pgSQL function that adds a new salesman -- to table 'emp'. -- CREATE OR REPLACE FUNCTION hire_salesman ( p_ename VARCHAR, p_sal NUMERIC, p_comm NUMERIC ) RETURNS NUMERIC AS $$ DECLARE v_empno NUMERIC(4); v_ename VARCHAR(10); v_job VARCHAR(9); v_mgr NUMERIC(4); v_hiredate DATE; v_sal NUMERIC(7,2); v_comm NUMERIC(7,2); v_deptno NUMERIC(2); BEGIN v_empno := new_empno(); INSERT INTO emp VALUES (v_empno, p_ename, 'SALESMAN', 7698, CURRENT_DATE, p_sal, p_comm, 30); SELECT INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp WHERE empno = v_empno; RAISE INFO 'Department : %', v_deptno; RAISE INFO 'Employee No: %', v_empno; RAISE INFO 'Name : %', v_ename; RAISE INFO 'Job : %', v_job; RAISE INFO 'Manager : %', v_mgr; RAISE INFO 'Hire Date : %', v_hiredate; RAISE INFO 'Salary : %', v_sal; RAISE INFO 'Commission : %', v_comm; RETURN v_empno; EXCEPTION WHEN OTHERS THEN RAISE INFO 'The following is SQLERRM:'; RAISE INFO '%', SQLERRM; RAISE INFO 'The following is SQLSTATE:'; RAISE INFO '%', SQLSTATE; RETURN -1; END; 建立规则 CREATE RULE CREATE OR REPLACE RULE salesemp_i AS ON INSERT TO salesemp DO INSTEAD INSERT INTO emp VALUES (NEW.empno, NEW.ename, 'SALESMAN', 7698, NEW.hiredate, NEW.sal, NEW.comm, 30); CREATE OR REPLACE RULE salesemp_u AS ON UPDATE TO salesemp DO INSTEAD UPDATE emp SET empno = NEW.empno, ename = NEW.ename, hiredate = NEW.hiredate, sal = NEW.sal, comm = NEW.comm WHERE empno = OLD.empno; CREATE OR REPLACE RULE salesemp_d AS ON DELETE TO salesemp DO INSTEAD DELETE FROM emp WHERE empno = OLD.empno; 建立触发器 CREATE TRIGGER CREATE OR REPLACE TRIGGER user_audit_trig AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE v_action VARCHAR2(24); BEGIN IF INSERTING THEN v_action := ' added employee(s) on '; ELSIF UPDATING THEN v_action := ' updated employee(s) on '; ELSIF DELETING THEN v_action := ' deleted employee(s) on '; END IF; DBMS_OUTPUT.PUT_LINE('User ' || USER || v_action || TO_CHAR(SYSDATE,'YYYY-MM-DD')); END; CREATE OR REPLACE TRIGGER emp_sal_trig BEFORE DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW DECLARE sal_diff NUMBER; BEGIN IF INSERTING THEN DBMS_OUTPUT.PUT_LINE('Inserting employee ' || :NEW.empno); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); END IF; IF UPDATING THEN sal_diff := :NEW.sal - :OLD.sal; DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); DBMS_OUTPUT.PUT_LINE('..Raise : ' || sal_diff); END IF; IF DELETING THEN DBMS_OUTPUT.PUT_LINE('Deleting employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); END IF; END; 建立包 CREATE PACKAGE CREATE OR REPLACE PACKAGE emp_admin IS FUNCTION get_dept_name ( p_deptno NUMBER ) RETURN VARCHAR2; FUNCTION update_emp_sal ( p_empno NUMBER, p_raise NUMBER ) RETURN NUMBER; PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ); PROCEDURE fire_emp ( p_empno NUMBER ); END emp_admin; 建立包体 CREATE PACKAGE BODY -- -- Package body for the 'emp_admin' package. -- CREATE OR REPLACE PACKAGE BODY emp_admin IS -- -- Function that queries the 'dept' table based on the department -- number and returns the corresponding department name. -- FUNCTION get_dept_name ( p_deptno IN NUMBER ) RETURN VARCHAR2 IS v_dname VARCHAR2(14); BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno; RETURN v_dname; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Invalid department number ' || p_deptno); RETURN ''; END; -- -- Function that updates an employee's salary based on the -- employee number and salary increment/decrement passed -- as IN parameters. Upon successful completion the function -- returns the new updated salary. -- FUNCTION update_emp_sal ( p_empno IN NUMBER, p_raise IN NUMBER ) RETURN NUMBER IS v_sal NUMBER := 0; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_empno; v_sal := v_sal + p_raise; UPDATE emp SET sal = v_sal WHERE empno = p_empno; RETURN v_sal; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); RETURN -1; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- Procedure that inserts a new employee record into the 'emp' table. -- PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ) AS BEGIN INSERT INTO emp(empno, ename, job, sal, hiredate, comm, mgr, deptno) VALUES(p_empno, p_ename, p_job, p_sal, p_hiredate, p_comm, p_mgr, p_deptno); END; -- -- Procedure that deletes an employee record from the 'emp' table based -- on the employee number. -- PROCEDURE fire_emp ( p_empno NUMBER ) AS BEGIN DELETE FROM emp WHERE empno = p_empno; END; END;

2019-12-01 22:57:14 0 浏览量 回答数 0

回答

详细解答可以参考官方帮助文档 通过本文档中的示例,Oracle 用户可以快速了解 PPAS 数据库中的术语及概念,以便在迁移及开发过程中提高效率。 以下所有操作基于一个基础模型,通过此模型用户可以看到 RDS for PPAS 中最基本的创建数据库、创建数据表、管理用户等操作,基础数据模型如下: 同时,为了模拟 Oracle 上类似的环境,我们会建立一名字为 orcl_ppas 的数据库(database),在此数据库中建立名为 scott 的用户,并建立与这个用户同名的 schema 用户空间。 PPAS兼容性手册 关于PPAS兼容性说明的完整内容,请参见阿里云PPAS兼容性手册。 连接数据库 psql psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U myuser -d template1 用户 myuser 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. template1=> 创建并连接数据库 CREATE DATABASE template1=> CREATE DATABASE orcl_ppas; CREATE DATABASE template1=> \c orcl_ppas psql.bin (9.4.1.3, 服务器 9.3.5.14) 创建普通用户 CREATE ROLE orcl_ppas=> CREATE ROLE scott LOGIN PASSWORD 'scott123'; CREATE ROLE 创建用户的私有空间 CREATE SCHEMA orcl_ppas=> CREATE SCHEMA scott; CREATE SCHEMA orcl_ppas=> GRANT scott TO myuser; GRANT ROLE orcl_ppas=> ALTER SCHEMA scott OWNER TO scott; ALTER SCHEMA orcl_ppas=> REVOKE scott FROM myuser; REVOKE ROLE 说明 如果在进行 ALTER SCHEMA scott OWNER TO scott 之前没有将 scott 加入到 myuser 角色,将会出现如下权限问题。ERROR:must be member of role "scott" 从安全角度出发,在处理完 OWNER 的授权后,请将 scott 用户移出 myuser 角色以提高安全性。 连接到 orcl_ppas 数据库 说明 此步骤十分重要,以下所有操作都是在 scott 账号下进行的,否则所建立的数据表及各种数据库对象将不属于 scott用户,导致权限问题。 [root@localhost bin]# ./psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U scott -d orcl_ppas 用户 scott 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. orcl_ppas=> 创建数据表 CREATE TABLE CREATE TABLE dept ( deptno NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY, dname VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE, lock 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 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 OR REPLACE VIEW CREATE OR REPLACE VIEW salesemp AS SELECT empno, ename, hiredate, sal, comm FROM emp WHERE job = 'SALESMAN'; 创建序列 CREATE SEQUENCE CREATE SEQUENCE next_empno START WITH 8000 INCREMENT BY 1; 插入数据 INSERT INTO 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'); 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); 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'); 查询优化器数据分析 ANALYZE ANALYZE dept; ANALYZE emp; ANALYZE jobhist; 建立存储过程 CREATE PROCEDURE 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; -- -- Procedure that selects an employee row given the employee -- number and displays certain columns. -- CREATE OR REPLACE PROCEDURE select_emp ( p_empno IN NUMBER ) IS v_ename emp.ename%TYPE; v_hiredate emp.hiredate%TYPE; v_sal emp.sal%TYPE; v_comm emp.comm%TYPE; v_dname dept.dname%TYPE; v_disp_date VARCHAR2(10); BEGIN SELECT ename, hiredate, sal, NVL(comm, 0), dname INTO v_ename, v_hiredate, v_sal, v_comm, v_dname FROM emp e, dept d WHERE empno = p_empno AND e.deptno = d.deptno; v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY'); DBMS_OUTPUT.PUT_LINE('Number : ' || p_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm); DBMS_OUTPUT.PUT_LINE('Department: ' || v_dname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); END; -- -- Procedure that queries the 'emp' table based on -- department number and employee number or name. Returns -- employee number and name as IN OUT parameters and job, -- hire date, and salary as OUT parameters. -- 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; -- -- Procedure to call 'emp_query_caller' with IN and IN OUT -- parameters. Displays the results received from IN OUT and -- OUT parameters. -- CREATE OR REPLACE PROCEDURE emp_query_caller IS v_deptno NUMBER(2); v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_hiredate DATE; v_sal NUMBER; BEGIN v_deptno := 30; v_empno := 0; v_ename := 'Martin'; emp_query(v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal); DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_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); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('More than one employee was selected'); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No employees were selected'); END; 建立函数 CREATE FUNCTION 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; -- -- Function that gets the next number from sequence, 'next_empno', -- and ensures it is not already in use as an employee number. -- CREATE OR REPLACE FUNCTION new_empno RETURN NUMBER IS v_cnt INTEGER := 1; v_new_empno NUMBER; BEGIN WHILE v_cnt > 0 LOOP SELECT next_empno.nextval INTO v_new_empno FROM dual; SELECT COUNT(*) INTO v_cnt FROM emp WHERE empno = v_new_empno; END LOOP; RETURN v_new_empno; END; -- -- EDB-SPL function that adds a new clerk to table 'emp'. This function -- uses package 'emp_admin'. -- CREATE OR REPLACE FUNCTION hire_clerk ( p_ename VARCHAR2, p_deptno NUMBER ) RETURN NUMBER IS v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_mgr NUMBER(4); v_hiredate DATE; v_sal NUMBER(7,2); v_comm NUMBER(7,2); v_deptno NUMBER(2); BEGIN v_empno := new_empno; INSERT INTO emp VALUES (v_empno, p_ename, 'CLERK', 7782, TRUNC(SYSDATE), 950.00, NULL, p_deptno); SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno FROM emp WHERE empno = v_empno; DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Job : ' || v_job); DBMS_OUTPUT.PUT_LINE('Manager : ' || v_mgr); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission : ' || v_comm); RETURN v_empno; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- PostgreSQL PL/pgSQL function that adds a new salesman -- to table 'emp'. -- CREATE OR REPLACE FUNCTION hire_salesman ( p_ename VARCHAR, p_sal NUMERIC, p_comm NUMERIC ) RETURNS NUMERIC AS $$ DECLARE v_empno NUMERIC(4); v_ename VARCHAR(10); v_job VARCHAR(9); v_mgr NUMERIC(4); v_hiredate DATE; v_sal NUMERIC(7,2); v_comm NUMERIC(7,2); v_deptno NUMERIC(2); BEGIN v_empno := new_empno(); INSERT INTO emp VALUES (v_empno, p_ename, 'SALESMAN', 7698, CURRENT_DATE, p_sal, p_comm, 30); SELECT INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp WHERE empno = v_empno; RAISE INFO 'Department : %', v_deptno; RAISE INFO 'Employee No: %', v_empno; RAISE INFO 'Name : %', v_ename; RAISE INFO 'Job : %', v_job; RAISE INFO 'Manager : %', v_mgr; RAISE INFO 'Hire Date : %', v_hiredate; RAISE INFO 'Salary : %', v_sal; RAISE INFO 'Commission : %', v_comm; RETURN v_empno; EXCEPTION WHEN OTHERS THEN RAISE INFO 'The following is SQLERRM:'; RAISE INFO '%', SQLERRM; RAISE INFO 'The following is SQLSTATE:'; RAISE INFO '%', SQLSTATE; RETURN -1; END; 建立规则 CREATE RULE CREATE OR REPLACE RULE salesemp_i AS ON INSERT TO salesemp DO INSTEAD INSERT INTO emp VALUES (NEW.empno, NEW.ename, 'SALESMAN', 7698, NEW.hiredate, NEW.sal, NEW.comm, 30); CREATE OR REPLACE RULE salesemp_u AS ON UPDATE TO salesemp DO INSTEAD UPDATE emp SET empno = NEW.empno, ename = NEW.ename, hiredate = NEW.hiredate, sal = NEW.sal, comm = NEW.comm WHERE empno = OLD.empno; CREATE OR REPLACE RULE salesemp_d AS ON DELETE TO salesemp DO INSTEAD DELETE FROM emp WHERE empno = OLD.empno; 建立触发器 CREATE TRIGGER CREATE OR REPLACE TRIGGER user_audit_trig AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE v_action VARCHAR2(24); BEGIN IF INSERTING THEN v_action := ' added employee(s) on '; ELSIF UPDATING THEN v_action := ' updated employee(s) on '; ELSIF DELETING THEN v_action := ' deleted employee(s) on '; END IF; DBMS_OUTPUT.PUT_LINE('User ' || USER || v_action || TO_CHAR(SYSDATE,'YYYY-MM-DD')); END; CREATE OR REPLACE TRIGGER emp_sal_trig BEFORE DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW DECLARE sal_diff NUMBER; BEGIN IF INSERTING THEN DBMS_OUTPUT.PUT_LINE('Inserting employee ' || :NEW.empno); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); END IF; IF UPDATING THEN sal_diff := :NEW.sal - :OLD.sal; DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); DBMS_OUTPUT.PUT_LINE('..Raise : ' || sal_diff); END IF; IF DELETING THEN DBMS_OUTPUT.PUT_LINE('Deleting employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); END IF; END; 建立包 CREATE PACKAGE CREATE OR REPLACE PACKAGE emp_admin IS FUNCTION get_dept_name ( p_deptno NUMBER ) RETURN VARCHAR2; FUNCTION update_emp_sal ( p_empno NUMBER, p_raise NUMBER ) RETURN NUMBER; PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ); PROCEDURE fire_emp ( p_empno NUMBER ); END emp_admin; 建立包体 CREATE PACKAGE BODY -- -- Package body for the 'emp_admin' package. -- CREATE OR REPLACE PACKAGE BODY emp_admin IS -- -- Function that queries the 'dept' table based on the department -- number and returns the corresponding department name. -- FUNCTION get_dept_name ( p_deptno IN NUMBER ) RETURN VARCHAR2 IS v_dname VARCHAR2(14); BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno; RETURN v_dname; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Invalid department number ' || p_deptno); RETURN ''; END; -- -- Function that updates an employee's salary based on the -- employee number and salary increment/decrement passed -- as IN parameters. Upon successful completion the function -- returns the new updated salary. -- FUNCTION update_emp_sal ( p_empno IN NUMBER, p_raise IN NUMBER ) RETURN NUMBER IS v_sal NUMBER := 0; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_empno; v_sal := v_sal + p_raise; UPDATE emp SET sal = v_sal WHERE empno = p_empno; RETURN v_sal; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); RETURN -1; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- Procedure that inserts a new employee record into the 'emp' table. -- PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ) AS BEGIN INSERT INTO emp(empno, ename, job, sal, hiredate, comm, mgr, deptno) VALUES(p_empno, p_ename, p_job, p_sal, p_hiredate, p_comm, p_mgr, p_deptno); END; -- -- Procedure that deletes an employee record from the 'emp' table based -- on the employee number. -- PROCEDURE fire_emp ( p_empno NUMBER ) AS BEGIN DELETE FROM emp WHERE empno = p_empno; END; END;

2019-12-01 22:57:14 0 浏览量 回答数 0

回答

详细解答可以参考官方帮助文档 通过本文档中的示例,Oracle 用户可以快速了解 PPAS 数据库中的术语及概念,以便在迁移及开发过程中提高效率。 以下所有操作基于一个基础模型,通过此模型用户可以看到 RDS for PPAS 中最基本的创建数据库、创建数据表、管理用户等操作,基础数据模型如下: 同时,为了模拟 Oracle 上类似的环境,我们会建立一名字为 orcl_ppas 的数据库(database),在此数据库中建立名为 scott 的用户,并建立与这个用户同名的 schema 用户空间。 PPAS兼容性手册 关于PPAS兼容性说明的完整内容,请参见阿里云PPAS兼容性手册。 连接数据库 psql psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U myuser -d template1 用户 myuser 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. template1=> 创建并连接数据库 CREATE DATABASE template1=> CREATE DATABASE orcl_ppas; CREATE DATABASE template1=> \c orcl_ppas psql.bin (9.4.1.3, 服务器 9.3.5.14) 创建普通用户 CREATE ROLE orcl_ppas=> CREATE ROLE scott LOGIN PASSWORD 'scott123'; CREATE ROLE 创建用户的私有空间 CREATE SCHEMA orcl_ppas=> CREATE SCHEMA scott; CREATE SCHEMA orcl_ppas=> GRANT scott TO myuser; GRANT ROLE orcl_ppas=> ALTER SCHEMA scott OWNER TO scott; ALTER SCHEMA orcl_ppas=> REVOKE scott FROM myuser; REVOKE ROLE 说明 如果在进行 ALTER SCHEMA scott OWNER TO scott 之前没有将 scott 加入到 myuser 角色,将会出现如下权限问题。ERROR:must be member of role "scott" 从安全角度出发,在处理完 OWNER 的授权后,请将 scott 用户移出 myuser 角色以提高安全性。 连接到 orcl_ppas 数据库 说明 此步骤十分重要,以下所有操作都是在 scott 账号下进行的,否则所建立的数据表及各种数据库对象将不属于 scott用户,导致权限问题。 [root@localhost bin]# ./psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U scott -d orcl_ppas 用户 scott 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. orcl_ppas=> 创建数据表 CREATE TABLE CREATE TABLE dept ( deptno NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY, dname VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE, lock 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 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 OR REPLACE VIEW CREATE OR REPLACE VIEW salesemp AS SELECT empno, ename, hiredate, sal, comm FROM emp WHERE job = 'SALESMAN'; 创建序列 CREATE SEQUENCE CREATE SEQUENCE next_empno START WITH 8000 INCREMENT BY 1; 插入数据 INSERT INTO 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'); 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); 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'); 查询优化器数据分析 ANALYZE ANALYZE dept; ANALYZE emp; ANALYZE jobhist; 建立存储过程 CREATE PROCEDURE 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; -- -- Procedure that selects an employee row given the employee -- number and displays certain columns. -- CREATE OR REPLACE PROCEDURE select_emp ( p_empno IN NUMBER ) IS v_ename emp.ename%TYPE; v_hiredate emp.hiredate%TYPE; v_sal emp.sal%TYPE; v_comm emp.comm%TYPE; v_dname dept.dname%TYPE; v_disp_date VARCHAR2(10); BEGIN SELECT ename, hiredate, sal, NVL(comm, 0), dname INTO v_ename, v_hiredate, v_sal, v_comm, v_dname FROM emp e, dept d WHERE empno = p_empno AND e.deptno = d.deptno; v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY'); DBMS_OUTPUT.PUT_LINE('Number : ' || p_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm); DBMS_OUTPUT.PUT_LINE('Department: ' || v_dname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); END; -- -- Procedure that queries the 'emp' table based on -- department number and employee number or name. Returns -- employee number and name as IN OUT parameters and job, -- hire date, and salary as OUT parameters. -- 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; -- -- Procedure to call 'emp_query_caller' with IN and IN OUT -- parameters. Displays the results received from IN OUT and -- OUT parameters. -- CREATE OR REPLACE PROCEDURE emp_query_caller IS v_deptno NUMBER(2); v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_hiredate DATE; v_sal NUMBER; BEGIN v_deptno := 30; v_empno := 0; v_ename := 'Martin'; emp_query(v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal); DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_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); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('More than one employee was selected'); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No employees were selected'); END; 建立函数 CREATE FUNCTION 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; -- -- Function that gets the next number from sequence, 'next_empno', -- and ensures it is not already in use as an employee number. -- CREATE OR REPLACE FUNCTION new_empno RETURN NUMBER IS v_cnt INTEGER := 1; v_new_empno NUMBER; BEGIN WHILE v_cnt > 0 LOOP SELECT next_empno.nextval INTO v_new_empno FROM dual; SELECT COUNT(*) INTO v_cnt FROM emp WHERE empno = v_new_empno; END LOOP; RETURN v_new_empno; END; -- -- EDB-SPL function that adds a new clerk to table 'emp'. This function -- uses package 'emp_admin'. -- CREATE OR REPLACE FUNCTION hire_clerk ( p_ename VARCHAR2, p_deptno NUMBER ) RETURN NUMBER IS v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_mgr NUMBER(4); v_hiredate DATE; v_sal NUMBER(7,2); v_comm NUMBER(7,2); v_deptno NUMBER(2); BEGIN v_empno := new_empno; INSERT INTO emp VALUES (v_empno, p_ename, 'CLERK', 7782, TRUNC(SYSDATE), 950.00, NULL, p_deptno); SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno FROM emp WHERE empno = v_empno; DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Job : ' || v_job); DBMS_OUTPUT.PUT_LINE('Manager : ' || v_mgr); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission : ' || v_comm); RETURN v_empno; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- PostgreSQL PL/pgSQL function that adds a new salesman -- to table 'emp'. -- CREATE OR REPLACE FUNCTION hire_salesman ( p_ename VARCHAR, p_sal NUMERIC, p_comm NUMERIC ) RETURNS NUMERIC AS $$ DECLARE v_empno NUMERIC(4); v_ename VARCHAR(10); v_job VARCHAR(9); v_mgr NUMERIC(4); v_hiredate DATE; v_sal NUMERIC(7,2); v_comm NUMERIC(7,2); v_deptno NUMERIC(2); BEGIN v_empno := new_empno(); INSERT INTO emp VALUES (v_empno, p_ename, 'SALESMAN', 7698, CURRENT_DATE, p_sal, p_comm, 30); SELECT INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp WHERE empno = v_empno; RAISE INFO 'Department : %', v_deptno; RAISE INFO 'Employee No: %', v_empno; RAISE INFO 'Name : %', v_ename; RAISE INFO 'Job : %', v_job; RAISE INFO 'Manager : %', v_mgr; RAISE INFO 'Hire Date : %', v_hiredate; RAISE INFO 'Salary : %', v_sal; RAISE INFO 'Commission : %', v_comm; RETURN v_empno; EXCEPTION WHEN OTHERS THEN RAISE INFO 'The following is SQLERRM:'; RAISE INFO '%', SQLERRM; RAISE INFO 'The following is SQLSTATE:'; RAISE INFO '%', SQLSTATE; RETURN -1; END; 建立规则 CREATE RULE CREATE OR REPLACE RULE salesemp_i AS ON INSERT TO salesemp DO INSTEAD INSERT INTO emp VALUES (NEW.empno, NEW.ename, 'SALESMAN', 7698, NEW.hiredate, NEW.sal, NEW.comm, 30); CREATE OR REPLACE RULE salesemp_u AS ON UPDATE TO salesemp DO INSTEAD UPDATE emp SET empno = NEW.empno, ename = NEW.ename, hiredate = NEW.hiredate, sal = NEW.sal, comm = NEW.comm WHERE empno = OLD.empno; CREATE OR REPLACE RULE salesemp_d AS ON DELETE TO salesemp DO INSTEAD DELETE FROM emp WHERE empno = OLD.empno; 建立触发器 CREATE TRIGGER CREATE OR REPLACE TRIGGER user_audit_trig AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE v_action VARCHAR2(24); BEGIN IF INSERTING THEN v_action := ' added employee(s) on '; ELSIF UPDATING THEN v_action := ' updated employee(s) on '; ELSIF DELETING THEN v_action := ' deleted employee(s) on '; END IF; DBMS_OUTPUT.PUT_LINE('User ' || USER || v_action || TO_CHAR(SYSDATE,'YYYY-MM-DD')); END; CREATE OR REPLACE TRIGGER emp_sal_trig BEFORE DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW DECLARE sal_diff NUMBER; BEGIN IF INSERTING THEN DBMS_OUTPUT.PUT_LINE('Inserting employee ' || :NEW.empno); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); END IF; IF UPDATING THEN sal_diff := :NEW.sal - :OLD.sal; DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); DBMS_OUTPUT.PUT_LINE('..Raise : ' || sal_diff); END IF; IF DELETING THEN DBMS_OUTPUT.PUT_LINE('Deleting employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); END IF; END; 建立包 CREATE PACKAGE CREATE OR REPLACE PACKAGE emp_admin IS FUNCTION get_dept_name ( p_deptno NUMBER ) RETURN VARCHAR2; FUNCTION update_emp_sal ( p_empno NUMBER, p_raise NUMBER ) RETURN NUMBER; PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ); PROCEDURE fire_emp ( p_empno NUMBER ); END emp_admin; 建立包体 CREATE PACKAGE BODY -- -- Package body for the 'emp_admin' package. -- CREATE OR REPLACE PACKAGE BODY emp_admin IS -- -- Function that queries the 'dept' table based on the department -- number and returns the corresponding department name. -- FUNCTION get_dept_name ( p_deptno IN NUMBER ) RETURN VARCHAR2 IS v_dname VARCHAR2(14); BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno; RETURN v_dname; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Invalid department number ' || p_deptno); RETURN ''; END; -- -- Function that updates an employee's salary based on the -- employee number and salary increment/decrement passed -- as IN parameters. Upon successful completion the function -- returns the new updated salary. -- FUNCTION update_emp_sal ( p_empno IN NUMBER, p_raise IN NUMBER ) RETURN NUMBER IS v_sal NUMBER := 0; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_empno; v_sal := v_sal + p_raise; UPDATE emp SET sal = v_sal WHERE empno = p_empno; RETURN v_sal; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); RETURN -1; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- Procedure that inserts a new employee record into the 'emp' table. -- PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ) AS BEGIN INSERT INTO emp(empno, ename, job, sal, hiredate, comm, mgr, deptno) VALUES(p_empno, p_ename, p_job, p_sal, p_hiredate, p_comm, p_mgr, p_deptno); END; -- -- Procedure that deletes an employee record from the 'emp' table based -- on the employee number. -- PROCEDURE fire_emp ( p_empno NUMBER ) AS BEGIN DELETE FROM emp WHERE empno = p_empno; END; END;

2019-12-01 22:57:14 0 浏览量 回答数 0

回答

详细解答可以参考官方帮助文档 通过本文档中的示例,Oracle 用户可以快速了解 PPAS 数据库中的术语及概念,以便在迁移及开发过程中提高效率。 以下所有操作基于一个基础模型,通过此模型用户可以看到 RDS for PPAS 中最基本的创建数据库、创建数据表、管理用户等操作,基础数据模型如下: 同时,为了模拟 Oracle 上类似的环境,我们会建立一名字为 orcl_ppas 的数据库(database),在此数据库中建立名为 scott 的用户,并建立与这个用户同名的 schema 用户空间。 PPAS兼容性手册 关于PPAS兼容性说明的完整内容,请参见阿里云PPAS兼容性手册。 连接数据库 psql psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U myuser -d template1 用户 myuser 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. template1=> 创建并连接数据库 CREATE DATABASE template1=> CREATE DATABASE orcl_ppas; CREATE DATABASE template1=> \c orcl_ppas psql.bin (9.4.1.3, 服务器 9.3.5.14) 创建普通用户 CREATE ROLE orcl_ppas=> CREATE ROLE scott LOGIN PASSWORD 'scott123'; CREATE ROLE 创建用户的私有空间 CREATE SCHEMA orcl_ppas=> CREATE SCHEMA scott; CREATE SCHEMA orcl_ppas=> GRANT scott TO myuser; GRANT ROLE orcl_ppas=> ALTER SCHEMA scott OWNER TO scott; ALTER SCHEMA orcl_ppas=> REVOKE scott FROM myuser; REVOKE ROLE 说明 如果在进行 ALTER SCHEMA scott OWNER TO scott 之前没有将 scott 加入到 myuser 角色,将会出现如下权限问题。ERROR:must be member of role "scott" 从安全角度出发,在处理完 OWNER 的授权后,请将 scott 用户移出 myuser 角色以提高安全性。 连接到 orcl_ppas 数据库 说明 此步骤十分重要,以下所有操作都是在 scott 账号下进行的,否则所建立的数据表及各种数据库对象将不属于 scott用户,导致权限问题。 [root@localhost bin]# ./psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U scott -d orcl_ppas 用户 scott 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. orcl_ppas=> 创建数据表 CREATE TABLE CREATE TABLE dept ( deptno NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY, dname VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE, lock 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 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 OR REPLACE VIEW CREATE OR REPLACE VIEW salesemp AS SELECT empno, ename, hiredate, sal, comm FROM emp WHERE job = 'SALESMAN'; 创建序列 CREATE SEQUENCE CREATE SEQUENCE next_empno START WITH 8000 INCREMENT BY 1; 插入数据 INSERT INTO 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'); 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); 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'); 查询优化器数据分析 ANALYZE ANALYZE dept; ANALYZE emp; ANALYZE jobhist; 建立存储过程 CREATE PROCEDURE 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; -- -- Procedure that selects an employee row given the employee -- number and displays certain columns. -- CREATE OR REPLACE PROCEDURE select_emp ( p_empno IN NUMBER ) IS v_ename emp.ename%TYPE; v_hiredate emp.hiredate%TYPE; v_sal emp.sal%TYPE; v_comm emp.comm%TYPE; v_dname dept.dname%TYPE; v_disp_date VARCHAR2(10); BEGIN SELECT ename, hiredate, sal, NVL(comm, 0), dname INTO v_ename, v_hiredate, v_sal, v_comm, v_dname FROM emp e, dept d WHERE empno = p_empno AND e.deptno = d.deptno; v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY'); DBMS_OUTPUT.PUT_LINE('Number : ' || p_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm); DBMS_OUTPUT.PUT_LINE('Department: ' || v_dname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); END; -- -- Procedure that queries the 'emp' table based on -- department number and employee number or name. Returns -- employee number and name as IN OUT parameters and job, -- hire date, and salary as OUT parameters. -- 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; -- -- Procedure to call 'emp_query_caller' with IN and IN OUT -- parameters. Displays the results received from IN OUT and -- OUT parameters. -- CREATE OR REPLACE PROCEDURE emp_query_caller IS v_deptno NUMBER(2); v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_hiredate DATE; v_sal NUMBER; BEGIN v_deptno := 30; v_empno := 0; v_ename := 'Martin'; emp_query(v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal); DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_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); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('More than one employee was selected'); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No employees were selected'); END; 建立函数 CREATE FUNCTION 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; -- -- Function that gets the next number from sequence, 'next_empno', -- and ensures it is not already in use as an employee number. -- CREATE OR REPLACE FUNCTION new_empno RETURN NUMBER IS v_cnt INTEGER := 1; v_new_empno NUMBER; BEGIN WHILE v_cnt > 0 LOOP SELECT next_empno.nextval INTO v_new_empno FROM dual; SELECT COUNT(*) INTO v_cnt FROM emp WHERE empno = v_new_empno; END LOOP; RETURN v_new_empno; END; -- -- EDB-SPL function that adds a new clerk to table 'emp'. This function -- uses package 'emp_admin'. -- CREATE OR REPLACE FUNCTION hire_clerk ( p_ename VARCHAR2, p_deptno NUMBER ) RETURN NUMBER IS v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_mgr NUMBER(4); v_hiredate DATE; v_sal NUMBER(7,2); v_comm NUMBER(7,2); v_deptno NUMBER(2); BEGIN v_empno := new_empno; INSERT INTO emp VALUES (v_empno, p_ename, 'CLERK', 7782, TRUNC(SYSDATE), 950.00, NULL, p_deptno); SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno FROM emp WHERE empno = v_empno; DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Job : ' || v_job); DBMS_OUTPUT.PUT_LINE('Manager : ' || v_mgr); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission : ' || v_comm); RETURN v_empno; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- PostgreSQL PL/pgSQL function that adds a new salesman -- to table 'emp'. -- CREATE OR REPLACE FUNCTION hire_salesman ( p_ename VARCHAR, p_sal NUMERIC, p_comm NUMERIC ) RETURNS NUMERIC AS $$ DECLARE v_empno NUMERIC(4); v_ename VARCHAR(10); v_job VARCHAR(9); v_mgr NUMERIC(4); v_hiredate DATE; v_sal NUMERIC(7,2); v_comm NUMERIC(7,2); v_deptno NUMERIC(2); BEGIN v_empno := new_empno(); INSERT INTO emp VALUES (v_empno, p_ename, 'SALESMAN', 7698, CURRENT_DATE, p_sal, p_comm, 30); SELECT INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp WHERE empno = v_empno; RAISE INFO 'Department : %', v_deptno; RAISE INFO 'Employee No: %', v_empno; RAISE INFO 'Name : %', v_ename; RAISE INFO 'Job : %', v_job; RAISE INFO 'Manager : %', v_mgr; RAISE INFO 'Hire Date : %', v_hiredate; RAISE INFO 'Salary : %', v_sal; RAISE INFO 'Commission : %', v_comm; RETURN v_empno; EXCEPTION WHEN OTHERS THEN RAISE INFO 'The following is SQLERRM:'; RAISE INFO '%', SQLERRM; RAISE INFO 'The following is SQLSTATE:'; RAISE INFO '%', SQLSTATE; RETURN -1; END; 建立规则 CREATE RULE CREATE OR REPLACE RULE salesemp_i AS ON INSERT TO salesemp DO INSTEAD INSERT INTO emp VALUES (NEW.empno, NEW.ename, 'SALESMAN', 7698, NEW.hiredate, NEW.sal, NEW.comm, 30); CREATE OR REPLACE RULE salesemp_u AS ON UPDATE TO salesemp DO INSTEAD UPDATE emp SET empno = NEW.empno, ename = NEW.ename, hiredate = NEW.hiredate, sal = NEW.sal, comm = NEW.comm WHERE empno = OLD.empno; CREATE OR REPLACE RULE salesemp_d AS ON DELETE TO salesemp DO INSTEAD DELETE FROM emp WHERE empno = OLD.empno; 建立触发器 CREATE TRIGGER CREATE OR REPLACE TRIGGER user_audit_trig AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE v_action VARCHAR2(24); BEGIN IF INSERTING THEN v_action := ' added employee(s) on '; ELSIF UPDATING THEN v_action := ' updated employee(s) on '; ELSIF DELETING THEN v_action := ' deleted employee(s) on '; END IF; DBMS_OUTPUT.PUT_LINE('User ' || USER || v_action || TO_CHAR(SYSDATE,'YYYY-MM-DD')); END; CREATE OR REPLACE TRIGGER emp_sal_trig BEFORE DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW DECLARE sal_diff NUMBER; BEGIN IF INSERTING THEN DBMS_OUTPUT.PUT_LINE('Inserting employee ' || :NEW.empno); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); END IF; IF UPDATING THEN sal_diff := :NEW.sal - :OLD.sal; DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); DBMS_OUTPUT.PUT_LINE('..Raise : ' || sal_diff); END IF; IF DELETING THEN DBMS_OUTPUT.PUT_LINE('Deleting employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); END IF; END; 建立包 CREATE PACKAGE CREATE OR REPLACE PACKAGE emp_admin IS FUNCTION get_dept_name ( p_deptno NUMBER ) RETURN VARCHAR2; FUNCTION update_emp_sal ( p_empno NUMBER, p_raise NUMBER ) RETURN NUMBER; PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ); PROCEDURE fire_emp ( p_empno NUMBER ); END emp_admin; 建立包体 CREATE PACKAGE BODY -- -- Package body for the 'emp_admin' package. -- CREATE OR REPLACE PACKAGE BODY emp_admin IS -- -- Function that queries the 'dept' table based on the department -- number and returns the corresponding department name. -- FUNCTION get_dept_name ( p_deptno IN NUMBER ) RETURN VARCHAR2 IS v_dname VARCHAR2(14); BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno; RETURN v_dname; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Invalid department number ' || p_deptno); RETURN ''; END; -- -- Function that updates an employee's salary based on the -- employee number and salary increment/decrement passed -- as IN parameters. Upon successful completion the function -- returns the new updated salary. -- FUNCTION update_emp_sal ( p_empno IN NUMBER, p_raise IN NUMBER ) RETURN NUMBER IS v_sal NUMBER := 0; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_empno; v_sal := v_sal + p_raise; UPDATE emp SET sal = v_sal WHERE empno = p_empno; RETURN v_sal; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); RETURN -1; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- Procedure that inserts a new employee record into the 'emp' table. -- PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ) AS BEGIN INSERT INTO emp(empno, ename, job, sal, hiredate, comm, mgr, deptno) VALUES(p_empno, p_ename, p_job, p_sal, p_hiredate, p_comm, p_mgr, p_deptno); END; -- -- Procedure that deletes an employee record from the 'emp' table based -- on the employee number. -- PROCEDURE fire_emp ( p_empno NUMBER ) AS BEGIN DELETE FROM emp WHERE empno = p_empno; END; END;

2019-12-01 22:57:13 0 浏览量 回答数 0

回答

详细解答可以参考官方帮助文档 通过本文档中的示例,Oracle 用户可以快速了解 PPAS 数据库中的术语及概念,以便在迁移及开发过程中提高效率。 以下所有操作基于一个基础模型,通过此模型用户可以看到 RDS for PPAS 中最基本的创建数据库、创建数据表、管理用户等操作,基础数据模型如下: 同时,为了模拟 Oracle 上类似的环境,我们会建立一名字为 orcl_ppas 的数据库(database),在此数据库中建立名为 scott 的用户,并建立与这个用户同名的 schema 用户空间。 PPAS兼容性手册 关于PPAS兼容性说明的完整内容,请参见阿里云PPAS兼容性手册。 连接数据库 psql psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U myuser -d template1 用户 myuser 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. template1=> 创建并连接数据库 CREATE DATABASE template1=> CREATE DATABASE orcl_ppas; CREATE DATABASE template1=> \c orcl_ppas psql.bin (9.4.1.3, 服务器 9.3.5.14) 创建普通用户 CREATE ROLE orcl_ppas=> CREATE ROLE scott LOGIN PASSWORD 'scott123'; CREATE ROLE 创建用户的私有空间 CREATE SCHEMA orcl_ppas=> CREATE SCHEMA scott; CREATE SCHEMA orcl_ppas=> GRANT scott TO myuser; GRANT ROLE orcl_ppas=> ALTER SCHEMA scott OWNER TO scott; ALTER SCHEMA orcl_ppas=> REVOKE scott FROM myuser; REVOKE ROLE 说明 如果在进行 ALTER SCHEMA scott OWNER TO scott 之前没有将 scott 加入到 myuser 角色,将会出现如下权限问题。ERROR:must be member of role "scott" 从安全角度出发,在处理完 OWNER 的授权后,请将 scott 用户移出 myuser 角色以提高安全性。 连接到 orcl_ppas 数据库 说明 此步骤十分重要,以下所有操作都是在 scott 账号下进行的,否则所建立的数据表及各种数据库对象将不属于 scott用户,导致权限问题。 [root@localhost bin]# ./psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U scott -d orcl_ppas 用户 scott 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. orcl_ppas=> 创建数据表 CREATE TABLE CREATE TABLE dept ( deptno NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY, dname VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE, lock 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 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 OR REPLACE VIEW CREATE OR REPLACE VIEW salesemp AS SELECT empno, ename, hiredate, sal, comm FROM emp WHERE job = 'SALESMAN'; 创建序列 CREATE SEQUENCE CREATE SEQUENCE next_empno START WITH 8000 INCREMENT BY 1; 插入数据 INSERT INTO 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'); 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); 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'); 查询优化器数据分析 ANALYZE ANALYZE dept; ANALYZE emp; ANALYZE jobhist; 建立存储过程 CREATE PROCEDURE 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; -- -- Procedure that selects an employee row given the employee -- number and displays certain columns. -- CREATE OR REPLACE PROCEDURE select_emp ( p_empno IN NUMBER ) IS v_ename emp.ename%TYPE; v_hiredate emp.hiredate%TYPE; v_sal emp.sal%TYPE; v_comm emp.comm%TYPE; v_dname dept.dname%TYPE; v_disp_date VARCHAR2(10); BEGIN SELECT ename, hiredate, sal, NVL(comm, 0), dname INTO v_ename, v_hiredate, v_sal, v_comm, v_dname FROM emp e, dept d WHERE empno = p_empno AND e.deptno = d.deptno; v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY'); DBMS_OUTPUT.PUT_LINE('Number : ' || p_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm); DBMS_OUTPUT.PUT_LINE('Department: ' || v_dname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); END; -- -- Procedure that queries the 'emp' table based on -- department number and employee number or name. Returns -- employee number and name as IN OUT parameters and job, -- hire date, and salary as OUT parameters. -- 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; -- -- Procedure to call 'emp_query_caller' with IN and IN OUT -- parameters. Displays the results received from IN OUT and -- OUT parameters. -- CREATE OR REPLACE PROCEDURE emp_query_caller IS v_deptno NUMBER(2); v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_hiredate DATE; v_sal NUMBER; BEGIN v_deptno := 30; v_empno := 0; v_ename := 'Martin'; emp_query(v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal); DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_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); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('More than one employee was selected'); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No employees were selected'); END; 建立函数 CREATE FUNCTION 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; -- -- Function that gets the next number from sequence, 'next_empno', -- and ensures it is not already in use as an employee number. -- CREATE OR REPLACE FUNCTION new_empno RETURN NUMBER IS v_cnt INTEGER := 1; v_new_empno NUMBER; BEGIN WHILE v_cnt > 0 LOOP SELECT next_empno.nextval INTO v_new_empno FROM dual; SELECT COUNT(*) INTO v_cnt FROM emp WHERE empno = v_new_empno; END LOOP; RETURN v_new_empno; END; -- -- EDB-SPL function that adds a new clerk to table 'emp'. This function -- uses package 'emp_admin'. -- CREATE OR REPLACE FUNCTION hire_clerk ( p_ename VARCHAR2, p_deptno NUMBER ) RETURN NUMBER IS v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_mgr NUMBER(4); v_hiredate DATE; v_sal NUMBER(7,2); v_comm NUMBER(7,2); v_deptno NUMBER(2); BEGIN v_empno := new_empno; INSERT INTO emp VALUES (v_empno, p_ename, 'CLERK', 7782, TRUNC(SYSDATE), 950.00, NULL, p_deptno); SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno FROM emp WHERE empno = v_empno; DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Job : ' || v_job); DBMS_OUTPUT.PUT_LINE('Manager : ' || v_mgr); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission : ' || v_comm); RETURN v_empno; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- PostgreSQL PL/pgSQL function that adds a new salesman -- to table 'emp'. -- CREATE OR REPLACE FUNCTION hire_salesman ( p_ename VARCHAR, p_sal NUMERIC, p_comm NUMERIC ) RETURNS NUMERIC AS $$ DECLARE v_empno NUMERIC(4); v_ename VARCHAR(10); v_job VARCHAR(9); v_mgr NUMERIC(4); v_hiredate DATE; v_sal NUMERIC(7,2); v_comm NUMERIC(7,2); v_deptno NUMERIC(2); BEGIN v_empno := new_empno(); INSERT INTO emp VALUES (v_empno, p_ename, 'SALESMAN', 7698, CURRENT_DATE, p_sal, p_comm, 30); SELECT INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp WHERE empno = v_empno; RAISE INFO 'Department : %', v_deptno; RAISE INFO 'Employee No: %', v_empno; RAISE INFO 'Name : %', v_ename; RAISE INFO 'Job : %', v_job; RAISE INFO 'Manager : %', v_mgr; RAISE INFO 'Hire Date : %', v_hiredate; RAISE INFO 'Salary : %', v_sal; RAISE INFO 'Commission : %', v_comm; RETURN v_empno; EXCEPTION WHEN OTHERS THEN RAISE INFO 'The following is SQLERRM:'; RAISE INFO '%', SQLERRM; RAISE INFO 'The following is SQLSTATE:'; RAISE INFO '%', SQLSTATE; RETURN -1; END; 建立规则 CREATE RULE CREATE OR REPLACE RULE salesemp_i AS ON INSERT TO salesemp DO INSTEAD INSERT INTO emp VALUES (NEW.empno, NEW.ename, 'SALESMAN', 7698, NEW.hiredate, NEW.sal, NEW.comm, 30); CREATE OR REPLACE RULE salesemp_u AS ON UPDATE TO salesemp DO INSTEAD UPDATE emp SET empno = NEW.empno, ename = NEW.ename, hiredate = NEW.hiredate, sal = NEW.sal, comm = NEW.comm WHERE empno = OLD.empno; CREATE OR REPLACE RULE salesemp_d AS ON DELETE TO salesemp DO INSTEAD DELETE FROM emp WHERE empno = OLD.empno; 建立触发器 CREATE TRIGGER CREATE OR REPLACE TRIGGER user_audit_trig AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE v_action VARCHAR2(24); BEGIN IF INSERTING THEN v_action := ' added employee(s) on '; ELSIF UPDATING THEN v_action := ' updated employee(s) on '; ELSIF DELETING THEN v_action := ' deleted employee(s) on '; END IF; DBMS_OUTPUT.PUT_LINE('User ' || USER || v_action || TO_CHAR(SYSDATE,'YYYY-MM-DD')); END; CREATE OR REPLACE TRIGGER emp_sal_trig BEFORE DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW DECLARE sal_diff NUMBER; BEGIN IF INSERTING THEN DBMS_OUTPUT.PUT_LINE('Inserting employee ' || :NEW.empno); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); END IF; IF UPDATING THEN sal_diff := :NEW.sal - :OLD.sal; DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); DBMS_OUTPUT.PUT_LINE('..Raise : ' || sal_diff); END IF; IF DELETING THEN DBMS_OUTPUT.PUT_LINE('Deleting employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); END IF; END; 建立包 CREATE PACKAGE CREATE OR REPLACE PACKAGE emp_admin IS FUNCTION get_dept_name ( p_deptno NUMBER ) RETURN VARCHAR2; FUNCTION update_emp_sal ( p_empno NUMBER, p_raise NUMBER ) RETURN NUMBER; PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ); PROCEDURE fire_emp ( p_empno NUMBER ); END emp_admin; 建立包体 CREATE PACKAGE BODY -- -- Package body for the 'emp_admin' package. -- CREATE OR REPLACE PACKAGE BODY emp_admin IS -- -- Function that queries the 'dept' table based on the department -- number and returns the corresponding department name. -- FUNCTION get_dept_name ( p_deptno IN NUMBER ) RETURN VARCHAR2 IS v_dname VARCHAR2(14); BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno; RETURN v_dname; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Invalid department number ' || p_deptno); RETURN ''; END; -- -- Function that updates an employee's salary based on the -- employee number and salary increment/decrement passed -- as IN parameters. Upon successful completion the function -- returns the new updated salary. -- FUNCTION update_emp_sal ( p_empno IN NUMBER, p_raise IN NUMBER ) RETURN NUMBER IS v_sal NUMBER := 0; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_empno; v_sal := v_sal + p_raise; UPDATE emp SET sal = v_sal WHERE empno = p_empno; RETURN v_sal; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); RETURN -1; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- Procedure that inserts a new employee record into the 'emp' table. -- PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ) AS BEGIN INSERT INTO emp(empno, ename, job, sal, hiredate, comm, mgr, deptno) VALUES(p_empno, p_ename, p_job, p_sal, p_hiredate, p_comm, p_mgr, p_deptno); END; -- -- Procedure that deletes an employee record from the 'emp' table based -- on the employee number. -- PROCEDURE fire_emp ( p_empno NUMBER ) AS BEGIN DELETE FROM emp WHERE empno = p_empno; END; END;

2019-12-01 22:57:15 0 浏览量 回答数 0

回答

详细解答可以参考官方帮助文档 通过本文档中的示例,Oracle 用户可以快速了解 PPAS 数据库中的术语及概念,以便在迁移及开发过程中提高效率。 以下所有操作基于一个基础模型,通过此模型用户可以看到 RDS for PPAS 中最基本的创建数据库、创建数据表、管理用户等操作,基础数据模型如下: 同时,为了模拟 Oracle 上类似的环境,我们会建立一名字为 orcl_ppas 的数据库(database),在此数据库中建立名为 scott 的用户,并建立与这个用户同名的 schema 用户空间。 PPAS兼容性手册 关于PPAS兼容性说明的完整内容,请参见阿里云PPAS兼容性手册。 连接数据库 psql psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U myuser -d template1 用户 myuser 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. template1=> 创建并连接数据库 CREATE DATABASE template1=> CREATE DATABASE orcl_ppas; CREATE DATABASE template1=> \c orcl_ppas psql.bin (9.4.1.3, 服务器 9.3.5.14) 创建普通用户 CREATE ROLE orcl_ppas=> CREATE ROLE scott LOGIN PASSWORD 'scott123'; CREATE ROLE 创建用户的私有空间 CREATE SCHEMA orcl_ppas=> CREATE SCHEMA scott; CREATE SCHEMA orcl_ppas=> GRANT scott TO myuser; GRANT ROLE orcl_ppas=> ALTER SCHEMA scott OWNER TO scott; ALTER SCHEMA orcl_ppas=> REVOKE scott FROM myuser; REVOKE ROLE 说明 如果在进行 ALTER SCHEMA scott OWNER TO scott 之前没有将 scott 加入到 myuser 角色,将会出现如下权限问题。ERROR:must be member of role "scott" 从安全角度出发,在处理完 OWNER 的授权后,请将 scott 用户移出 myuser 角色以提高安全性。 连接到 orcl_ppas 数据库 说明 此步骤十分重要,以下所有操作都是在 scott 账号下进行的,否则所建立的数据表及各种数据库对象将不属于 scott用户,导致权限问题。 [root@localhost bin]# ./psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U scott -d orcl_ppas 用户 scott 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. orcl_ppas=> 创建数据表 CREATE TABLE CREATE TABLE dept ( deptno NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY, dname VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE, lock 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 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 OR REPLACE VIEW CREATE OR REPLACE VIEW salesemp AS SELECT empno, ename, hiredate, sal, comm FROM emp WHERE job = 'SALESMAN'; 创建序列 CREATE SEQUENCE CREATE SEQUENCE next_empno START WITH 8000 INCREMENT BY 1; 插入数据 INSERT INTO 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'); 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); 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'); 查询优化器数据分析 ANALYZE ANALYZE dept; ANALYZE emp; ANALYZE jobhist; 建立存储过程 CREATE PROCEDURE 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; -- -- Procedure that selects an employee row given the employee -- number and displays certain columns. -- CREATE OR REPLACE PROCEDURE select_emp ( p_empno IN NUMBER ) IS v_ename emp.ename%TYPE; v_hiredate emp.hiredate%TYPE; v_sal emp.sal%TYPE; v_comm emp.comm%TYPE; v_dname dept.dname%TYPE; v_disp_date VARCHAR2(10); BEGIN SELECT ename, hiredate, sal, NVL(comm, 0), dname INTO v_ename, v_hiredate, v_sal, v_comm, v_dname FROM emp e, dept d WHERE empno = p_empno AND e.deptno = d.deptno; v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY'); DBMS_OUTPUT.PUT_LINE('Number : ' || p_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm); DBMS_OUTPUT.PUT_LINE('Department: ' || v_dname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); END; -- -- Procedure that queries the 'emp' table based on -- department number and employee number or name. Returns -- employee number and name as IN OUT parameters and job, -- hire date, and salary as OUT parameters. -- 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; -- -- Procedure to call 'emp_query_caller' with IN and IN OUT -- parameters. Displays the results received from IN OUT and -- OUT parameters. -- CREATE OR REPLACE PROCEDURE emp_query_caller IS v_deptno NUMBER(2); v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_hiredate DATE; v_sal NUMBER; BEGIN v_deptno := 30; v_empno := 0; v_ename := 'Martin'; emp_query(v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal); DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_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); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('More than one employee was selected'); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No employees were selected'); END; 建立函数 CREATE FUNCTION 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; -- -- Function that gets the next number from sequence, 'next_empno', -- and ensures it is not already in use as an employee number. -- CREATE OR REPLACE FUNCTION new_empno RETURN NUMBER IS v_cnt INTEGER := 1; v_new_empno NUMBER; BEGIN WHILE v_cnt > 0 LOOP SELECT next_empno.nextval INTO v_new_empno FROM dual; SELECT COUNT(*) INTO v_cnt FROM emp WHERE empno = v_new_empno; END LOOP; RETURN v_new_empno; END; -- -- EDB-SPL function that adds a new clerk to table 'emp'. This function -- uses package 'emp_admin'. -- CREATE OR REPLACE FUNCTION hire_clerk ( p_ename VARCHAR2, p_deptno NUMBER ) RETURN NUMBER IS v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_mgr NUMBER(4); v_hiredate DATE; v_sal NUMBER(7,2); v_comm NUMBER(7,2); v_deptno NUMBER(2); BEGIN v_empno := new_empno; INSERT INTO emp VALUES (v_empno, p_ename, 'CLERK', 7782, TRUNC(SYSDATE), 950.00, NULL, p_deptno); SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno FROM emp WHERE empno = v_empno; DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Job : ' || v_job); DBMS_OUTPUT.PUT_LINE('Manager : ' || v_mgr); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission : ' || v_comm); RETURN v_empno; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- PostgreSQL PL/pgSQL function that adds a new salesman -- to table 'emp'. -- CREATE OR REPLACE FUNCTION hire_salesman ( p_ename VARCHAR, p_sal NUMERIC, p_comm NUMERIC ) RETURNS NUMERIC AS $$ DECLARE v_empno NUMERIC(4); v_ename VARCHAR(10); v_job VARCHAR(9); v_mgr NUMERIC(4); v_hiredate DATE; v_sal NUMERIC(7,2); v_comm NUMERIC(7,2); v_deptno NUMERIC(2); BEGIN v_empno := new_empno(); INSERT INTO emp VALUES (v_empno, p_ename, 'SALESMAN', 7698, CURRENT_DATE, p_sal, p_comm, 30); SELECT INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp WHERE empno = v_empno; RAISE INFO 'Department : %', v_deptno; RAISE INFO 'Employee No: %', v_empno; RAISE INFO 'Name : %', v_ename; RAISE INFO 'Job : %', v_job; RAISE INFO 'Manager : %', v_mgr; RAISE INFO 'Hire Date : %', v_hiredate; RAISE INFO 'Salary : %', v_sal; RAISE INFO 'Commission : %', v_comm; RETURN v_empno; EXCEPTION WHEN OTHERS THEN RAISE INFO 'The following is SQLERRM:'; RAISE INFO '%', SQLERRM; RAISE INFO 'The following is SQLSTATE:'; RAISE INFO '%', SQLSTATE; RETURN -1; END; 建立规则 CREATE RULE CREATE OR REPLACE RULE salesemp_i AS ON INSERT TO salesemp DO INSTEAD INSERT INTO emp VALUES (NEW.empno, NEW.ename, 'SALESMAN', 7698, NEW.hiredate, NEW.sal, NEW.comm, 30); CREATE OR REPLACE RULE salesemp_u AS ON UPDATE TO salesemp DO INSTEAD UPDATE emp SET empno = NEW.empno, ename = NEW.ename, hiredate = NEW.hiredate, sal = NEW.sal, comm = NEW.comm WHERE empno = OLD.empno; CREATE OR REPLACE RULE salesemp_d AS ON DELETE TO salesemp DO INSTEAD DELETE FROM emp WHERE empno = OLD.empno; 建立触发器 CREATE TRIGGER CREATE OR REPLACE TRIGGER user_audit_trig AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE v_action VARCHAR2(24); BEGIN IF INSERTING THEN v_action := ' added employee(s) on '; ELSIF UPDATING THEN v_action := ' updated employee(s) on '; ELSIF DELETING THEN v_action := ' deleted employee(s) on '; END IF; DBMS_OUTPUT.PUT_LINE('User ' || USER || v_action || TO_CHAR(SYSDATE,'YYYY-MM-DD')); END; CREATE OR REPLACE TRIGGER emp_sal_trig BEFORE DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW DECLARE sal_diff NUMBER; BEGIN IF INSERTING THEN DBMS_OUTPUT.PUT_LINE('Inserting employee ' || :NEW.empno); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); END IF; IF UPDATING THEN sal_diff := :NEW.sal - :OLD.sal; DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); DBMS_OUTPUT.PUT_LINE('..Raise : ' || sal_diff); END IF; IF DELETING THEN DBMS_OUTPUT.PUT_LINE('Deleting employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); END IF; END; 建立包 CREATE PACKAGE CREATE OR REPLACE PACKAGE emp_admin IS FUNCTION get_dept_name ( p_deptno NUMBER ) RETURN VARCHAR2; FUNCTION update_emp_sal ( p_empno NUMBER, p_raise NUMBER ) RETURN NUMBER; PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ); PROCEDURE fire_emp ( p_empno NUMBER ); END emp_admin; 建立包体 CREATE PACKAGE BODY -- -- Package body for the 'emp_admin' package. -- CREATE OR REPLACE PACKAGE BODY emp_admin IS -- -- Function that queries the 'dept' table based on the department -- number and returns the corresponding department name. -- FUNCTION get_dept_name ( p_deptno IN NUMBER ) RETURN VARCHAR2 IS v_dname VARCHAR2(14); BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno; RETURN v_dname; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Invalid department number ' || p_deptno); RETURN ''; END; -- -- Function that updates an employee's salary based on the -- employee number and salary increment/decrement passed -- as IN parameters. Upon successful completion the function -- returns the new updated salary. -- FUNCTION update_emp_sal ( p_empno IN NUMBER, p_raise IN NUMBER ) RETURN NUMBER IS v_sal NUMBER := 0; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_empno; v_sal := v_sal + p_raise; UPDATE emp SET sal = v_sal WHERE empno = p_empno; RETURN v_sal; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); RETURN -1; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- Procedure that inserts a new employee record into the 'emp' table. -- PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ) AS BEGIN INSERT INTO emp(empno, ename, job, sal, hiredate, comm, mgr, deptno) VALUES(p_empno, p_ename, p_job, p_sal, p_hiredate, p_comm, p_mgr, p_deptno); END; -- -- Procedure that deletes an employee record from the 'emp' table based -- on the employee number. -- PROCEDURE fire_emp ( p_empno NUMBER ) AS BEGIN DELETE FROM emp WHERE empno = p_empno; END; END;

2019-12-01 22:57:13 0 浏览量 回答数 0

回答

详细解答可以参考官方帮助文档 通过本文档中的示例,Oracle 用户可以快速了解 PPAS 数据库中的术语及概念,以便在迁移及开发过程中提高效率。 以下所有操作基于一个基础模型,通过此模型用户可以看到 RDS for PPAS 中最基本的创建数据库、创建数据表、管理用户等操作,基础数据模型如下: 同时,为了模拟 Oracle 上类似的环境,我们会建立一名字为 orcl_ppas 的数据库(database),在此数据库中建立名为 scott 的用户,并建立与这个用户同名的 schema 用户空间。 PPAS兼容性手册 关于PPAS兼容性说明的完整内容,请参见阿里云PPAS兼容性手册。 连接数据库 psql psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U myuser -d template1 用户 myuser 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. template1=> 创建并连接数据库 CREATE DATABASE template1=> CREATE DATABASE orcl_ppas; CREATE DATABASE template1=> \c orcl_ppas psql.bin (9.4.1.3, 服务器 9.3.5.14) 创建普通用户 CREATE ROLE orcl_ppas=> CREATE ROLE scott LOGIN PASSWORD 'scott123'; CREATE ROLE 创建用户的私有空间 CREATE SCHEMA orcl_ppas=> CREATE SCHEMA scott; CREATE SCHEMA orcl_ppas=> GRANT scott TO myuser; GRANT ROLE orcl_ppas=> ALTER SCHEMA scott OWNER TO scott; ALTER SCHEMA orcl_ppas=> REVOKE scott FROM myuser; REVOKE ROLE 说明 如果在进行 ALTER SCHEMA scott OWNER TO scott 之前没有将 scott 加入到 myuser 角色,将会出现如下权限问题。ERROR:must be member of role "scott" 从安全角度出发,在处理完 OWNER 的授权后,请将 scott 用户移出 myuser 角色以提高安全性。 连接到 orcl_ppas 数据库 说明 此步骤十分重要,以下所有操作都是在 scott 账号下进行的,否则所建立的数据表及各种数据库对象将不属于 scott用户,导致权限问题。 [root@localhost bin]# ./psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U scott -d orcl_ppas 用户 scott 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. orcl_ppas=> 创建数据表 CREATE TABLE CREATE TABLE dept ( deptno NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY, dname VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE, lock 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 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 OR REPLACE VIEW CREATE OR REPLACE VIEW salesemp AS SELECT empno, ename, hiredate, sal, comm FROM emp WHERE job = 'SALESMAN'; 创建序列 CREATE SEQUENCE CREATE SEQUENCE next_empno START WITH 8000 INCREMENT BY 1; 插入数据 INSERT INTO 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'); 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); 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'); 查询优化器数据分析 ANALYZE ANALYZE dept; ANALYZE emp; ANALYZE jobhist; 建立存储过程 CREATE PROCEDURE 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; -- -- Procedure that selects an employee row given the employee -- number and displays certain columns. -- CREATE OR REPLACE PROCEDURE select_emp ( p_empno IN NUMBER ) IS v_ename emp.ename%TYPE; v_hiredate emp.hiredate%TYPE; v_sal emp.sal%TYPE; v_comm emp.comm%TYPE; v_dname dept.dname%TYPE; v_disp_date VARCHAR2(10); BEGIN SELECT ename, hiredate, sal, NVL(comm, 0), dname INTO v_ename, v_hiredate, v_sal, v_comm, v_dname FROM emp e, dept d WHERE empno = p_empno AND e.deptno = d.deptno; v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY'); DBMS_OUTPUT.PUT_LINE('Number : ' || p_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm); DBMS_OUTPUT.PUT_LINE('Department: ' || v_dname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); END; -- -- Procedure that queries the 'emp' table based on -- department number and employee number or name. Returns -- employee number and name as IN OUT parameters and job, -- hire date, and salary as OUT parameters. -- 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; -- -- Procedure to call 'emp_query_caller' with IN and IN OUT -- parameters. Displays the results received from IN OUT and -- OUT parameters. -- CREATE OR REPLACE PROCEDURE emp_query_caller IS v_deptno NUMBER(2); v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_hiredate DATE; v_sal NUMBER; BEGIN v_deptno := 30; v_empno := 0; v_ename := 'Martin'; emp_query(v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal); DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_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); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('More than one employee was selected'); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No employees were selected'); END; 建立函数 CREATE FUNCTION 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; -- -- Function that gets the next number from sequence, 'next_empno', -- and ensures it is not already in use as an employee number. -- CREATE OR REPLACE FUNCTION new_empno RETURN NUMBER IS v_cnt INTEGER := 1; v_new_empno NUMBER; BEGIN WHILE v_cnt > 0 LOOP SELECT next_empno.nextval INTO v_new_empno FROM dual; SELECT COUNT(*) INTO v_cnt FROM emp WHERE empno = v_new_empno; END LOOP; RETURN v_new_empno; END; -- -- EDB-SPL function that adds a new clerk to table 'emp'. This function -- uses package 'emp_admin'. -- CREATE OR REPLACE FUNCTION hire_clerk ( p_ename VARCHAR2, p_deptno NUMBER ) RETURN NUMBER IS v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_mgr NUMBER(4); v_hiredate DATE; v_sal NUMBER(7,2); v_comm NUMBER(7,2); v_deptno NUMBER(2); BEGIN v_empno := new_empno; INSERT INTO emp VALUES (v_empno, p_ename, 'CLERK', 7782, TRUNC(SYSDATE), 950.00, NULL, p_deptno); SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno FROM emp WHERE empno = v_empno; DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Job : ' || v_job); DBMS_OUTPUT.PUT_LINE('Manager : ' || v_mgr); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission : ' || v_comm); RETURN v_empno; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- PostgreSQL PL/pgSQL function that adds a new salesman -- to table 'emp'. -- CREATE OR REPLACE FUNCTION hire_salesman ( p_ename VARCHAR, p_sal NUMERIC, p_comm NUMERIC ) RETURNS NUMERIC AS $$ DECLARE v_empno NUMERIC(4); v_ename VARCHAR(10); v_job VARCHAR(9); v_mgr NUMERIC(4); v_hiredate DATE; v_sal NUMERIC(7,2); v_comm NUMERIC(7,2); v_deptno NUMERIC(2); BEGIN v_empno := new_empno(); INSERT INTO emp VALUES (v_empno, p_ename, 'SALESMAN', 7698, CURRENT_DATE, p_sal, p_comm, 30); SELECT INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp WHERE empno = v_empno; RAISE INFO 'Department : %', v_deptno; RAISE INFO 'Employee No: %', v_empno; RAISE INFO 'Name : %', v_ename; RAISE INFO 'Job : %', v_job; RAISE INFO 'Manager : %', v_mgr; RAISE INFO 'Hire Date : %', v_hiredate; RAISE INFO 'Salary : %', v_sal; RAISE INFO 'Commission : %', v_comm; RETURN v_empno; EXCEPTION WHEN OTHERS THEN RAISE INFO 'The following is SQLERRM:'; RAISE INFO '%', SQLERRM; RAISE INFO 'The following is SQLSTATE:'; RAISE INFO '%', SQLSTATE; RETURN -1; END; 建立规则 CREATE RULE CREATE OR REPLACE RULE salesemp_i AS ON INSERT TO salesemp DO INSTEAD INSERT INTO emp VALUES (NEW.empno, NEW.ename, 'SALESMAN', 7698, NEW.hiredate, NEW.sal, NEW.comm, 30); CREATE OR REPLACE RULE salesemp_u AS ON UPDATE TO salesemp DO INSTEAD UPDATE emp SET empno = NEW.empno, ename = NEW.ename, hiredate = NEW.hiredate, sal = NEW.sal, comm = NEW.comm WHERE empno = OLD.empno; CREATE OR REPLACE RULE salesemp_d AS ON DELETE TO salesemp DO INSTEAD DELETE FROM emp WHERE empno = OLD.empno; 建立触发器 CREATE TRIGGER CREATE OR REPLACE TRIGGER user_audit_trig AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE v_action VARCHAR2(24); BEGIN IF INSERTING THEN v_action := ' added employee(s) on '; ELSIF UPDATING THEN v_action := ' updated employee(s) on '; ELSIF DELETING THEN v_action := ' deleted employee(s) on '; END IF; DBMS_OUTPUT.PUT_LINE('User ' || USER || v_action || TO_CHAR(SYSDATE,'YYYY-MM-DD')); END; CREATE OR REPLACE TRIGGER emp_sal_trig BEFORE DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW DECLARE sal_diff NUMBER; BEGIN IF INSERTING THEN DBMS_OUTPUT.PUT_LINE('Inserting employee ' || :NEW.empno); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); END IF; IF UPDATING THEN sal_diff := :NEW.sal - :OLD.sal; DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); DBMS_OUTPUT.PUT_LINE('..Raise : ' || sal_diff); END IF; IF DELETING THEN DBMS_OUTPUT.PUT_LINE('Deleting employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); END IF; END; 建立包 CREATE PACKAGE CREATE OR REPLACE PACKAGE emp_admin IS FUNCTION get_dept_name ( p_deptno NUMBER ) RETURN VARCHAR2; FUNCTION update_emp_sal ( p_empno NUMBER, p_raise NUMBER ) RETURN NUMBER; PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ); PROCEDURE fire_emp ( p_empno NUMBER ); END emp_admin; 建立包体 CREATE PACKAGE BODY -- -- Package body for the 'emp_admin' package. -- CREATE OR REPLACE PACKAGE BODY emp_admin IS -- -- Function that queries the 'dept' table based on the department -- number and returns the corresponding department name. -- FUNCTION get_dept_name ( p_deptno IN NUMBER ) RETURN VARCHAR2 IS v_dname VARCHAR2(14); BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno; RETURN v_dname; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Invalid department number ' || p_deptno); RETURN ''; END; -- -- Function that updates an employee's salary based on the -- employee number and salary increment/decrement passed -- as IN parameters. Upon successful completion the function -- returns the new updated salary. -- FUNCTION update_emp_sal ( p_empno IN NUMBER, p_raise IN NUMBER ) RETURN NUMBER IS v_sal NUMBER := 0; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_empno; v_sal := v_sal + p_raise; UPDATE emp SET sal = v_sal WHERE empno = p_empno; RETURN v_sal; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); RETURN -1; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- Procedure that inserts a new employee record into the 'emp' table. -- PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ) AS BEGIN INSERT INTO emp(empno, ename, job, sal, hiredate, comm, mgr, deptno) VALUES(p_empno, p_ename, p_job, p_sal, p_hiredate, p_comm, p_mgr, p_deptno); END; -- -- Procedure that deletes an employee record from the 'emp' table based -- on the employee number. -- PROCEDURE fire_emp ( p_empno NUMBER ) AS BEGIN DELETE FROM emp WHERE empno = p_empno; END; END;

2019-12-01 22:57:14 0 浏览量 回答数 0

回答

详细解答可以参考官方帮助文档 通过本文档中的示例,Oracle 用户可以快速了解 PPAS 数据库中的术语及概念,以便在迁移及开发过程中提高效率。 以下所有操作基于一个基础模型,通过此模型用户可以看到 RDS for PPAS 中最基本的创建数据库、创建数据表、管理用户等操作,基础数据模型如下: 同时,为了模拟 Oracle 上类似的环境,我们会建立一名字为 orcl_ppas 的数据库(database),在此数据库中建立名为 scott 的用户,并建立与这个用户同名的 schema 用户空间。 PPAS兼容性手册 关于PPAS兼容性说明的完整内容,请参见阿里云PPAS兼容性手册。 连接数据库 psql psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U myuser -d template1 用户 myuser 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. template1=> 创建并连接数据库 CREATE DATABASE template1=> CREATE DATABASE orcl_ppas; CREATE DATABASE template1=> \c orcl_ppas psql.bin (9.4.1.3, 服务器 9.3.5.14) 创建普通用户 CREATE ROLE orcl_ppas=> CREATE ROLE scott LOGIN PASSWORD 'scott123'; CREATE ROLE 创建用户的私有空间 CREATE SCHEMA orcl_ppas=> CREATE SCHEMA scott; CREATE SCHEMA orcl_ppas=> GRANT scott TO myuser; GRANT ROLE orcl_ppas=> ALTER SCHEMA scott OWNER TO scott; ALTER SCHEMA orcl_ppas=> REVOKE scott FROM myuser; REVOKE ROLE 说明 如果在进行 ALTER SCHEMA scott OWNER TO scott 之前没有将 scott 加入到 myuser 角色,将会出现如下权限问题。ERROR:must be member of role "scott" 从安全角度出发,在处理完 OWNER 的授权后,请将 scott 用户移出 myuser 角色以提高安全性。 连接到 orcl_ppas 数据库 说明 此步骤十分重要,以下所有操作都是在 scott 账号下进行的,否则所建立的数据表及各种数据库对象将不属于 scott用户,导致权限问题。 [root@localhost bin]# ./psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U scott -d orcl_ppas 用户 scott 的口令: psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 "help" 来获取帮助信息. orcl_ppas=> 创建数据表 CREATE TABLE CREATE TABLE dept ( deptno NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY, dname VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE, lock 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 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 OR REPLACE VIEW CREATE OR REPLACE VIEW salesemp AS SELECT empno, ename, hiredate, sal, comm FROM emp WHERE job = 'SALESMAN'; 创建序列 CREATE SEQUENCE CREATE SEQUENCE next_empno START WITH 8000 INCREMENT BY 1; 插入数据 INSERT INTO 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'); 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); 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'); 查询优化器数据分析 ANALYZE ANALYZE dept; ANALYZE emp; ANALYZE jobhist; 建立存储过程 CREATE PROCEDURE 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; -- -- Procedure that selects an employee row given the employee -- number and displays certain columns. -- CREATE OR REPLACE PROCEDURE select_emp ( p_empno IN NUMBER ) IS v_ename emp.ename%TYPE; v_hiredate emp.hiredate%TYPE; v_sal emp.sal%TYPE; v_comm emp.comm%TYPE; v_dname dept.dname%TYPE; v_disp_date VARCHAR2(10); BEGIN SELECT ename, hiredate, sal, NVL(comm, 0), dname INTO v_ename, v_hiredate, v_sal, v_comm, v_dname FROM emp e, dept d WHERE empno = p_empno AND e.deptno = d.deptno; v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY'); DBMS_OUTPUT.PUT_LINE('Number : ' || p_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm); DBMS_OUTPUT.PUT_LINE('Department: ' || v_dname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); END; -- -- Procedure that queries the 'emp' table based on -- department number and employee number or name. Returns -- employee number and name as IN OUT parameters and job, -- hire date, and salary as OUT parameters. -- 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; -- -- Procedure to call 'emp_query_caller' with IN and IN OUT -- parameters. Displays the results received from IN OUT and -- OUT parameters. -- CREATE OR REPLACE PROCEDURE emp_query_caller IS v_deptno NUMBER(2); v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_hiredate DATE; v_sal NUMBER; BEGIN v_deptno := 30; v_empno := 0; v_ename := 'Martin'; emp_query(v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal); DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_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); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('More than one employee was selected'); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No employees were selected'); END; 建立函数 CREATE FUNCTION 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; -- -- Function that gets the next number from sequence, 'next_empno', -- and ensures it is not already in use as an employee number. -- CREATE OR REPLACE FUNCTION new_empno RETURN NUMBER IS v_cnt INTEGER := 1; v_new_empno NUMBER; BEGIN WHILE v_cnt > 0 LOOP SELECT next_empno.nextval INTO v_new_empno FROM dual; SELECT COUNT(*) INTO v_cnt FROM emp WHERE empno = v_new_empno; END LOOP; RETURN v_new_empno; END; -- -- EDB-SPL function that adds a new clerk to table 'emp'. This function -- uses package 'emp_admin'. -- CREATE OR REPLACE FUNCTION hire_clerk ( p_ename VARCHAR2, p_deptno NUMBER ) RETURN NUMBER IS v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_mgr NUMBER(4); v_hiredate DATE; v_sal NUMBER(7,2); v_comm NUMBER(7,2); v_deptno NUMBER(2); BEGIN v_empno := new_empno; INSERT INTO emp VALUES (v_empno, p_ename, 'CLERK', 7782, TRUNC(SYSDATE), 950.00, NULL, p_deptno); SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno FROM emp WHERE empno = v_empno; DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Job : ' || v_job); DBMS_OUTPUT.PUT_LINE('Manager : ' || v_mgr); DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate); DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal); DBMS_OUTPUT.PUT_LINE('Commission : ' || v_comm); RETURN v_empno; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- PostgreSQL PL/pgSQL function that adds a new salesman -- to table 'emp'. -- CREATE OR REPLACE FUNCTION hire_salesman ( p_ename VARCHAR, p_sal NUMERIC, p_comm NUMERIC ) RETURNS NUMERIC AS $$ DECLARE v_empno NUMERIC(4); v_ename VARCHAR(10); v_job VARCHAR(9); v_mgr NUMERIC(4); v_hiredate DATE; v_sal NUMERIC(7,2); v_comm NUMERIC(7,2); v_deptno NUMERIC(2); BEGIN v_empno := new_empno(); INSERT INTO emp VALUES (v_empno, p_ename, 'SALESMAN', 7698, CURRENT_DATE, p_sal, p_comm, 30); SELECT INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp WHERE empno = v_empno; RAISE INFO 'Department : %', v_deptno; RAISE INFO 'Employee No: %', v_empno; RAISE INFO 'Name : %', v_ename; RAISE INFO 'Job : %', v_job; RAISE INFO 'Manager : %', v_mgr; RAISE INFO 'Hire Date : %', v_hiredate; RAISE INFO 'Salary : %', v_sal; RAISE INFO 'Commission : %', v_comm; RETURN v_empno; EXCEPTION WHEN OTHERS THEN RAISE INFO 'The following is SQLERRM:'; RAISE INFO '%', SQLERRM; RAISE INFO 'The following is SQLSTATE:'; RAISE INFO '%', SQLSTATE; RETURN -1; END; 建立规则 CREATE RULE CREATE OR REPLACE RULE salesemp_i AS ON INSERT TO salesemp DO INSTEAD INSERT INTO emp VALUES (NEW.empno, NEW.ename, 'SALESMAN', 7698, NEW.hiredate, NEW.sal, NEW.comm, 30); CREATE OR REPLACE RULE salesemp_u AS ON UPDATE TO salesemp DO INSTEAD UPDATE emp SET empno = NEW.empno, ename = NEW.ename, hiredate = NEW.hiredate, sal = NEW.sal, comm = NEW.comm WHERE empno = OLD.empno; CREATE OR REPLACE RULE salesemp_d AS ON DELETE TO salesemp DO INSTEAD DELETE FROM emp WHERE empno = OLD.empno; 建立触发器 CREATE TRIGGER CREATE OR REPLACE TRIGGER user_audit_trig AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE v_action VARCHAR2(24); BEGIN IF INSERTING THEN v_action := ' added employee(s) on '; ELSIF UPDATING THEN v_action := ' updated employee(s) on '; ELSIF DELETING THEN v_action := ' deleted employee(s) on '; END IF; DBMS_OUTPUT.PUT_LINE('User ' || USER || v_action || TO_CHAR(SYSDATE,'YYYY-MM-DD')); END; CREATE OR REPLACE TRIGGER emp_sal_trig BEFORE DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW DECLARE sal_diff NUMBER; BEGIN IF INSERTING THEN DBMS_OUTPUT.PUT_LINE('Inserting employee ' || :NEW.empno); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); END IF; IF UPDATING THEN sal_diff := :NEW.sal - :OLD.sal; DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal); DBMS_OUTPUT.PUT_LINE('..Raise : ' || sal_diff); END IF; IF DELETING THEN DBMS_OUTPUT.PUT_LINE('Deleting employee ' || :OLD.empno); DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal); END IF; END; 建立包 CREATE PACKAGE CREATE OR REPLACE PACKAGE emp_admin IS FUNCTION get_dept_name ( p_deptno NUMBER ) RETURN VARCHAR2; FUNCTION update_emp_sal ( p_empno NUMBER, p_raise NUMBER ) RETURN NUMBER; PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ); PROCEDURE fire_emp ( p_empno NUMBER ); END emp_admin; 建立包体 CREATE PACKAGE BODY -- -- Package body for the 'emp_admin' package. -- CREATE OR REPLACE PACKAGE BODY emp_admin IS -- -- Function that queries the 'dept' table based on the department -- number and returns the corresponding department name. -- FUNCTION get_dept_name ( p_deptno IN NUMBER ) RETURN VARCHAR2 IS v_dname VARCHAR2(14); BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno; RETURN v_dname; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Invalid department number ' || p_deptno); RETURN ''; END; -- -- Function that updates an employee's salary based on the -- employee number and salary increment/decrement passed -- as IN parameters. Upon successful completion the function -- returns the new updated salary. -- FUNCTION update_emp_sal ( p_empno IN NUMBER, p_raise IN NUMBER ) RETURN NUMBER IS v_sal NUMBER := 0; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_empno; v_sal := v_sal + p_raise; UPDATE emp SET sal = v_sal WHERE empno = p_empno; RETURN v_sal; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found'); RETURN -1; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:'); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:'); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; -- -- Procedure that inserts a new employee record into the 'emp' table. -- PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ) AS BEGIN INSERT INTO emp(empno, ename, job, sal, hiredate, comm, mgr, deptno) VALUES(p_empno, p_ename, p_job, p_sal, p_hiredate, p_comm, p_mgr, p_deptno); END; -- -- Procedure that deletes an employee record from the 'emp' table based -- on the employee number. -- PROCEDURE fire_emp ( p_empno NUMBER ) AS BEGIN DELETE FROM emp WHERE empno = p_empno; END; END;

2019-12-01 22:57:14 0 浏览量 回答数 0
阿里云大学 云服务器ECS com域名 网站域名whois查询 开发者平台 小程序定制 小程序开发 国内短信套餐包 开发者技术与产品 云数据库 图像识别 开发者问答 阿里云建站 阿里云备案 云市场 万网 阿里云帮助文档 免费套餐 开发者工具 企业信息查询 小程序开发制作 视频内容分析 企业网站制作 视频集锦 代理记账服务 企业建站模板