• 关于

    is_null

    的搜索结果

回答

with t_wait as ( select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted, a.objid,a.objsubid,a.pid,a.transactionid,a.mppsessionid,a.mppiswriter,a.gp_segment_id, b.procpid,b.sess_id,b.waiting_reason,b.current_query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_namefrom pg_locks a,pg_stat_activity b where a.mppsessionid=b.sess_id and not a.granted ), t_run as ( select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted, a.objid,a.objsubid,a.pid,a.transactionid,a.mppsessionid,a.mppiswriter,a.gp_segment_id, b.procpid,b.sess_id,b.waiting_reason,b.current_query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_namefrom pg_locks a,pg_stat_activity b where a.mppsessionid=b.sess_id and a.granted ), t_overlap as ( select r.* from t_wait w join t_run r on (r.locktype is not distinct from w.locktype and r.database is not distinct from w.database and r.relation is not distinct from w.relation and r.page is not distinct from w.page and r.tuple is not distinct from w.tuple and r.transactionid is not distinct from w.transactionid and r.classid is not distinct from w.classid and r.objid is not distinct from w.objid and r.objsubid is not distinct from w.objsubid and r.mppsessionid <> w.mppsessionid ) ), t_unionall as ( select r.* from t_overlap r union all select w.* from t_wait w ) select locktype,datname,relation::regclass,page,tuple,textin(xidout(transactionid)),classid::regclass,objid,objsubid, string_agg( 'Gp_Segment_Id: '||case when gp_segment_id is null then 'NULL' else gp_segment_id::text end||chr(10)|| 'MppIsWriter: '||case when mppiswriter is null then 'NULL' when mppiswriter is true then 'TRUE' else 'FALSE' end||chr(10)|| 'MppSessionId: '||case when mppsessionid is null then 'NULL' else mppsessionid::text end||chr(10)|| 'ProcPid: '||case when procpid is null then 'NULL' else procpid::text end||chr(10)|| 'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)|| 'Lock_Granted: '||case when granted is null then 'NULL' when granted is true then 'TRUE' else 'FALSE' end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , Waiting_Reason: '||case when waiting_reason is null then 'NULL' else waiting_reason::text end||chr(10)|| 'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)|| 'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)|| 'SQL (Current SQL in Transaction): '||chr(10)|| case when current_query is null then 'NULL' else current_query::text end, chr(10)||'--------'||chr(10) order by ( case modewhen 'INVALID' then 0 when 'AccessShareLock' then 1 when 'RowShareLock' then 2 when 'RowExclusiveLock' then 3 when 'ShareUpdateExclusiveLock' then 4 when 'ShareLock' then 5 when 'ShareRowExclusiveLock' then 6 when 'ExclusiveLock' then 7 when 'AccessExclusiveLock' then 8 else 0 end ) desc, (case when granted then 0 else 1 end) ) as lock_conflict from t_unionall group by locktype,datname,relation::regclass,page,tuple,textin(xidout(transactionid)),classid::regclass,objid,objsubid ;
卓刀 2019-12-02 00:46:33 0 浏览量 回答数 0

问题

超过了锁定等待超时;尝试重新启动事务-插入语句

这是我的用户表方案: -- auto-generated definition CREATE TABLE Tb_Users ( user_id bigint UNSIGNED AUTO_INC...
几许相思几点泪 2019-12-29 19:45:01 0 浏览量 回答数 0

回答

`( start_time IS NULL and current_time< end_time ) or ( end_time IS NULL and and start_time is not null current_time> start_time ) or( start_time IS NULL and end_time IS NULL) or ( current_time> start_time and current_time< end_time )`这代表了四种情形:1.开始时间为NULL,则不限制开始时间,即 现在时间 在 结束时间 之前的数据都符合记录2.结束时间为NULL,则不限制结束时间,即 现在时间 在 开始时间 以后的数据都符合记录3.如果开始时间和结束时间都为NULL,那么查询所有的记录。4.如果开始时间和结束时间都为不为NULL,那么查询当前时间在此之间的数据
落地花开啦 2019-12-02 01:44:20 0 浏览量 回答数 0

问题

c++ 二叉树 access violation?链表不也是这样的吗?为什么以下代码会出错

#include using namespace std; class binary_tree_node { public: binary_tree_node(const int& init_data = int(),bin...
a123456678 2019-12-01 20:08:44 932 浏览量 回答数 1

问题

数据框将两个元素与功能进行比较

我有以下布尔函数,该函数比较两个值,但有一些后备功能: def __score_bool(a, b, default_element_value_if_null=None,...
is大龙 2020-03-23 20:24:16 0 浏览量 回答数 1

问题

什么是“a block is a sentinel for a buffer pool watch.”

如下代码为,mysql在做buffer pool load时,如果某一个page既在内存,同时也不符合“a block is a sentinel for a buffer pool watch.”,则跳过此page的加载。watch_pa...
sun_ashe 2019-12-01 19:30:53 878 浏览量 回答数 1

问题

C-SDK 之如何实现下载文件(二)?

分段下载文件 通过设置Range指定返回文件的传输范围,可以实现文件的分段下载。以下代码实现分段下载文件到内存中: aos_pool_t *p;oss_request_options_t *options...
青衫无名 2019-12-01 21:45:57 1209 浏览量 回答数 0

回答

SELECT D1.table_name, D1.column_name FROM Database1.information_schema.columns D1 LEFT OUTER JOIN Database2.information_schema.columns D2 ON D2.table_name = D1.table_name AND D2.column_name = D1.column_name LEFT OUTER JOIN Database3.information_schema.columns D3 ON D3.table_name = D1.table_name AND D3.column_name = D1.column_name LEFT OUTER JOIN Database4.information_schema.columns D4 ON D4.table_name = D1.table_name AND D4.column_name = D1.column_name WHERE D2.table_name IS NULL AND D3.table_name IS NULL AND D4.table_name IS NULL
保持可爱mmm 2019-12-02 03:16:54 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

回答

详细解答可以参考官方帮助文档 通过本文档中的示例,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: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

回答

您可以创建如下过程: CREATE PROCEDURE uid_update() BEGIN DECLARE Done_c INT; DECLARE v_min_id INT; declare number_plus int; declare v_cur int; DECLARE curs CURSOR FOR select ROW_NUMBER() OVER (order by unique_id) rn from testTable where uid is null; DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done_c = 1; SELECT max(uid) INTO number_plus FROM testTable; OPEN curs; SET Done_c = 0; REPEAT FETCH curs INTO v_cur; select min(unique_id) into v_min_id from testTable where uid is null; update testTable set uid = number_plus + v_cur where uid is null and unique_id = v_min_id ; commit; UNTIL Done_c END REPEAT; CLOSE curs; END 然后像这样调用该过程: call uid_update; 然后将根据您的要求更新值。
保持可爱mmm 2019-12-02 03:17:20 0 浏览量 回答数 0

回答

在SQL2005和2012上均未DBCC USEROPTIONS显示is_read_committed_snapshot_on: Set Option Value textsize 2147483647 language us_english dateformat mdy datefirst 7 lock_timeout -1 quoted_identifier SET arithabort SET ansi_null_dflt_on SET ansi_warnings SET ansi_padding SET ansi_nulls SET concat_null_yields_null SET isolation level read committed
心有灵_夕 2019-12-26 21:43:03 0 浏览量 回答数 0

回答

Re是官方C SDK的bug还是OSS服务器的bug???? C SDK没有问题,是我自己没有用对,我仔细看了sdk的源码实现,是我不该直接把?symlink放到对象名字后面作为文件名传入,应该把symlink作为请求参数传入,这样就不会对?编码了; 我把封装好的创建符号链接代码和使用示例代码传上来供需要的同学参考。 const char BUCKET_NAME[] = "xxxxxx";const char OSS_SYMLINK_TAEGET[] = "x-oss-symlink-target";const char OSS_REQ_PARAM_SYMLINK[] = "symlink";aos_status_t *oss_put_symlink(const oss_request_options_t *options,        const aos_string_t *bucket,        const aos_string_t *symlink_object,        const aos_string_t *target_object,        aos_table_t *headers,        aos_table_t **resp_headers){        aos_status_t *s = NULL;        aos_http_request_t *req = NULL;        aos_http_response_t *resp = NULL;        aos_table_t *query_params = NULL;        char* targname = NULL;        headers = aos_table_create_if_null(options, headers, 0);        query_params = aos_table_create_if_null(options, query_params, 0);        apr_table_add(query_params, OSS_REQ_PARAM_SYMLINK, ""); // symlink request key without value        targname = aos_pstrdup(options->pool, target_object);        apr_table_add(headers, OSS_SYMLINK_TAEGET, targname);        oss_init_object_request(options, bucket, symlink_object, HTTP_PUT,                &req, query_params, headers, NULL, 0, &resp);        s = oss_process_request(options, req, resp);        oss_fill_read_response_header(resp, resp_headers);        return s;}aos_status_t *oss_get_symlink(const oss_request_options_t *options,        const aos_string_t *bucket,        const aos_string_t *symlink_object,        aos_table_t *headers,        aos_table_t **resp_headers){        aos_status_t *s = NULL;        aos_http_request_t *req = NULL;        aos_http_response_t *resp = NULL;        aos_table_t *query_params = NULL;        headers = aos_table_create_if_null(options, headers, 0);        query_params = aos_table_create_if_null(options, query_params, 0);        apr_table_add(query_params, OSS_REQ_PARAM_SYMLINK, ""); // symlink request key without value        oss_init_object_request(options, bucket, symlink_object, HTTP_GET,                &req, query_params, headers, NULL, 0, &resp);        s = oss_process_request(options, req, resp);        oss_fill_read_response_header(resp, resp_headers);        return s;}//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////void put_object_symlink(){        aos_pool_t *p = NULL;        aos_string_t bucket;        aos_string_t symlink_object;        aos_string_t target_object;        int is_cname = 0;        aos_table_t *headers = NULL;        aos_table_t *resp_headers = NULL;        oss_request_options_t *options = NULL;        aos_list_t buffer;        aos_buf_t *content = NULL;        char *str = "";        aos_status_t *s = NULL;        char* pbuf = NULL;        aos_pool_create(&p, NULL);        options = oss_request_options_create(p);        init_sample_request_options(options, is_cname);        headers = aos_table_make(p, 1);        apr_table_set(headers, "x-oss-meta-author", "oss");        aos_str_set(&bucket, BUCKET_NAME);        aos_str_set(&symlink_object, "obj_link");// 符号链接名        aos_str_set(&target_object, "obj_name"); // 目标对象名        aos_list_init(&buffer);        content = aos_buf_pack(options->pool, str, strlen(str));        aos_list_add_tail(&content->node, &buffer);        s = oss_put_symlink(options, &bucket, &symlink_object, &target_object, headers, &resp_headers);        if (aos_status_is_ok(s)) {                printf("put object symlink succeeded\n");        }        else {                printf("put object symlink failed\n");        }        aos_pool_destroy(p);}void get_object_symlink(){        aos_pool_t *p = NULL;        aos_string_t bucket;        aos_string_t symlink_object;        int is_cname = 0;        aos_table_t *headers = NULL;        aos_table_t *resp_headers = NULL;        oss_request_options_t *options = NULL;        aos_list_t buffer;        aos_buf_t *content = NULL;        char *str = "";        aos_status_t *s = NULL;        char* pLink = NULL;        aos_pool_create(&p, NULL);        options = oss_request_options_create(p);        init_sample_request_options(options, is_cname);        headers = aos_table_make(p, 1);        apr_table_set(headers, "x-oss-meta-author", "oss");        aos_str_set(&bucket, BUCKET_NAME);        aos_str_set(&symlink_object, "obj_link");        aos_list_init(&buffer);        content = aos_buf_pack(options->pool, str, strlen(str));        aos_list_add_tail(&content->node, &buffer);        s = oss_get_symlink(options, &bucket, &symlink_object, headers, &resp_headers);        if (aos_status_is_ok(s))        {                printf("get object symlink succeeded\n");                pLink = apr_table_get(resp_headers, "x-oss-symlink-target");        }        else        {                printf("get object symlink failed\n");        }        aos_pool_destroy(p);}
伏神 2019-12-02 00:36:46 0 浏览量 回答数 0

问题

是官方C SDK的bug还是OSS服务器的bug????

目标任务:创建出对象obj_name的符号链接obj_link 开发环境:使用官方的OSS C SDK 结果:没有正确创建出符号链接obj_link,创建出的对象名为obj_link%...
伏神 2019-12-01 21:34:38 2293 浏览量 回答数 2

回答

您定义了两次主键。尝试: CREATE TABLE SHIPPING_GRID( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique ID for each row', shipping_vendor_no INT(6) NOT NULL COMMENT 'Foreign key to VENDOR.no for the shipping vendor (vendors_type must be 3)', start_vendor_no INT(6) NOT NULL COMMENT 'Foreign key to VENDOR.no for the vendor being shipped from', end_vendor_no INT(6) NOT NULL COMMENT 'Foreign key to the VENDOR.no for the vendor being shipped to', shipment_duration INT(1) DEFAULT 1 COMMENT 'Duration in whole days shipment will take', price FLOAT(5,5) NOT NULL COMMENT 'Price in US dollars per shipment lbs (down to 5 decimal places)', is_flat_rate TINYINT(1) DEFAULT 0 COMMENT '1 if is flat rate regardless of weight, 0 if price is by lbs', INDEX (shipping_vendor_no), INDEX (start_vendor_no), INDEX (end_vendor_no), FOREIGN KEY (shipping_vendor_no) REFERENCES VENDOR (no), FOREIGN KEY (start_vendor_no) REFERENCES VENDOR (no), FOREIGN KEY (end_vendor_no) REFERENCES VENDOR (no) ) TYPE = INNODB; VENDOR主键必须是INT(6),并且两个表都必须是InnoDB类型。
保持可爱mmm 2019-12-02 03:15:20 0 浏览量 回答数 0

问题

有没有其他方法可以在不使用临时表的情况下编写这些查询?

我有3个表(tb_cdcp_ATQchild,tb_cdcp_ATQInfant,tb_cdcp_ATQPreschool),看起来与下面的第一个表相似。根据Indivi...
LiuWH 2020-01-03 11:18:55 3 浏览量 回答数 1

问题

有没有其他方法可以在不使用临时表的情况下编写这些查询?

我有3个表(tb_cdcp_ATQchild,tb_cdcp_ATQInfant,tb_cdcp_ATQPreschool),看起来与下面的第一个表相似。根据Indivi...
LiuWH 2020-01-04 10:17:19 1 浏览量 回答数 1

回答

正如我在本文中所解释的,从8.0.16版本开始,MySQL添加了对CHECK约束的支持: ALTER TABLE topic ADD CONSTRAINT post_content_check CHECK ( CASE WHEN DTYPE = 'Post' THEN CASE WHEN content IS NOT NULL THEN 1 ELSE 0 END ELSE 1 END = 1 ); ALTER TABLE topic ADD CONSTRAINT announcement_validUntil_check CHECK ( CASE WHEN DTYPE = 'Announcement' THEN CASE WHEN validUntil IS NOT NULL THEN 1 ELSE 0 END ELSE 1 END = 1 ); 以前,仅在使用BEFORE INSERT和BEFORE UPDATE触发器时才可用: CREATE TRIGGER post_content_check BEFORE INSERT ON topic FOR EACH ROW BEGIN IF NEW.DTYPE = 'Post' THEN IF NEW.content IS NULL THEN signal sqlstate '45000' set message_text = 'Post content cannot be NULL'; END IF; END IF; END; CREATE TRIGGER post_content_update_check BEFORE UPDATE ON topic FOR EACH ROW BEGIN IF NEW.DTYPE = 'Post' THEN IF NEW.content IS NULL THEN signal sqlstate '45000' set message_text = 'Post content cannot be NULL'; END IF; END IF; END; CREATE TRIGGER announcement_validUntil_check BEFORE INSERT ON topic FOR EACH ROW BEGIN IF NEW.DTYPE = 'Announcement' THEN IF NEW.validUntil IS NULL THEN signal sqlstate '45000' set message_text = 'Announcement validUntil cannot be NULL'; END IF; END IF; END; CREATE TRIGGER announcement_validUntil_update_check BEFORE UPDATE ON topic FOR EACH ROW BEGIN IF NEW.DTYPE = 'Announcement' THEN IF NEW.validUntil IS NULL THEN signal sqlstate '45000' set message_text = 'Announcement validUntil cannot be NULL'; END IF; END IF; END; 有关使用8.0.16之前的MySQL版本的数据库触发器模拟CHECK约束的更多详细信息,请参阅本文。来源:stack overflow
保持可爱mmm 2020-05-17 10:35:59 0 浏览量 回答数 0

问题

本地数据导入后出错求解决

如图 MySQL Error: Access denied for user 'qdm154935684'@'%' to database 'qdm176236190_db' M...
说好打我脸 2019-12-01 21:56:20 7056 浏览量 回答数 3

回答

例子:长事务 30 min select datname,usename,query,xact_start,now()-xact_start xact_duration,query_start,now()-query_start query_duration,state from pg_stat_activity where state<> $$ idle $$ and (backend_xid is not null or backend_xmin is not null) and now()-xact_start > interval $$ 30 min $$ order by xact_start; 2PC长事务 30 min select name,statement,prepare_time,now()-prepare_time,parameter_types,from_sql from pg_prepared_statements where now()-prepare_time > interval $$ 30 min $$ order by prepare_time; 可以写成脚本https://github.com/digoal/pgsql_admin_script/blob/master/generate_report.sh echo "----->>>---->>> 长事务, 2PC: " psql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDATABASE --pset=pager=off -q -x -c 'select datname,usename,query,xact_start,now()-xact_start xact_duration,query_start,now()-query_start query_duration,state from pg_stat_activity where state<> $$ idle $$ and (backend_xid is not null or backend_xmin is not null) and now()-xact_start > interval $$ 30 min $$ order by xact_start' psql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGDATABASE --pset=pager=off -q -x -c 'select name,statement,prepare_time,now()-prepare_time,parameter_types,from_sql from pg_prepared_statements where now()-prepare_time > interval $$ 30 min $$ order by prepare_time' echo "建议: " echo " 长事务过程中产生的垃圾, 无法回收, 建议不要在数据库中运行LONG SQL, 或者错开DML高峰时间去运行LONG SQL. 2PC事务一定要记得尽快结束掉, 否则可能会导致数据库膨胀. " echo " 参考: http://blog.163.com/digoal@126/blog/static/1638770402015329115636287/ " echo -e "\n"
德哥 2019-12-02 01:28:50 0 浏览量 回答数 0

问题

NULL尽管存在值,但仍会产生值

将查询更改为新建议,并遇到另一个问题,因此以NEW POST的形式发布 我创建了以下查询,为表中不存在薪资操作类型PAY_PAYROLL_ACTIONS 或action_type为'Q&#...
保持可爱mmm 2019-12-01 21:59:10 11 浏览量 回答数 0

问题

SQL过滤条件中,a_flag!='1'查不出来a_flag is null,怎么办?

db2、mySQL 数据库中都有这个问题问题现象:SQL语句:select * from table where a_flag!='1';a_flag有3种情况,1、0、null。使用a_flag!='1'本意是想查出a_flag=‘0’以...
落地花开啦 2019-12-01 20:01:31 1147 浏览量 回答数 1

问题

#支付宝 KP.AE_ALIPASS_PARAME_IS_NULL?

调用【alipay.pass.instance.update】卡券实例更新接口报错“KP.AE_ALIPASS_PARAME_IS_NULL(缺失必要属性serial_number为空)”。...
保持可爱mmm 2020-05-07 13:32:10 0 浏览量 回答数 1

云产品推荐

上海奇点人才服务相关的云产品 小程序定制 上海微企信息技术相关的云产品 国内短信套餐包 ECS云服务器安全配置相关的云产品 开发者问答 阿里云建站 自然场景识别相关的云产品 万网 小程序开发制作 视频内容分析 视频集锦 代理记账服务 阿里云AIoT