SQL: Oracle simple table, oraclesimple

SQL: Oracle simple table, oraclesimple

-- Oracle 9i data script address: $ oracle_home/rdbms/admin/utlsampl. sqlCREATE table dept (deptno number (2) CONSTRAINT PK_DEPT primary key, DNAME VARCHAR2 (14), LOC VARCHAR2 (13); create table emp (empno number (4) CONSTRAINT PK_EMP primary key, ENAME VARCHAR2 (10), JOB VARCHAR2 (9), mgr number (4), hiredate date, sal number (7, 2), comm number (7, 2 ), deptno number (2) CONSTRAINT FK_DEPTNO references dept); INSERT 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 ', 'cler ', 7902, to_date ('17-12-1980 ', 'dd-mm-yyyy'), 800, NULL, 20); insert into emp values (7499, 'allen ', 'salesman', 7698, to_date ('20-2-1981 ', 'dd-mm-yyyy'), 1600,300, 30); INSERT INTO EMP VALUES (7521, 'ward ', 'salesman', 7698, to_date ('22-2-1981 ', 'dd-mm-yyyy'), 1250,500, 30 ); insert into emp values (7566, 'Jones ', 'manager', 7839, to_date ('2-4-1981', 'dd-mm-yyyy '), 2975, NULL, 20); insert into emp values (7654, 'martin ', 'salesman', 7698, to_date ('28-9-1981', 'dd-mm-yyyy, 30); insert into emp values (7698, 'bucke', 'manager', 7839, to_date ('1-5-1981 ', 'dd-mm-yyyy'), 2850, NULL, 30); INSERT INTO EMP VALUE S (7782, 'clark', 'manager', 7839, to_date ('9-6-1981 ', 'dd-mm-yyyy'), 2450, NULL, 10 ); insert into emp values (7788, 'Scott ', 'analyst', 7566, to_date ('13-7-87', 'dd-mm-rr ')-, NULL, 20); insert into emp values (7839, 'King', 'President ', NULL, to_date ('17-11-1981', 'dd-mm-yyyy '), 5000, NULL, 10); insert into emp values (7844, 'turner ', 'salesman', 7698, to_date ('8-9-1981', 'dd-mm-yyyy '), 1500,0, 30); insert into emp values (7876, 'Adams', 'cler', 7788, to_date ('13-7-87 ', 'dd-mm-rr')-, 1100, NULL, 20 ); insert into emp values (7900, 'James ', 'cler', 7698, to_date ('3-12-1981', 'dd-mm-yyyy'), 950, NULL, 30); insert into emp values (7902, 'Ford ', 'analyst', 7566, to_date ('3-12-1981', 'dd-mm-yyyy '), 3000, NULL, 20); insert into emp values (7934, 'miller ', 'cler', 7782, to_date ('23-1-1982', 'dd-mm-yyyy '), 1300, NULL, 10); create table bonus (ENAME VARCHAR2 (10 ), JOB VARCHAR2 (9), sal number, comm number); create table salgrade (grade number, losal number, hisal number); insert into salgrade values (1,700,120 0 ); insert into salgrade values (1400, 2000); insert into salgrade values (3000, 9999); insert into salgrade values (,); insert into salgrade values ); COMMIT; EXITselect rowid, emp. * from emp; SELECT emp. empno, dept. deptno from em P, dept where emp. deptno = dept. deptno and dept. deptno> = 20; SELECT * from emp; DECLARE v_EmpName VARCHAR2 (50); begin select EName Into v_EmpName FROM Scott. emp where empNo = & EmpNo; DBMS_OUTPUT.PUT_LINE ('employee ID of the current query is: '| & EmpNo | 'employee name:' | v_EmpName); END; -- enable screen output display -- set serveroutput on; -- display the current date and time BEGIN DBMS_OUTPUT.PUT_LINE ('current Date and Time: '); -- display information without line feed DBMS_OUTPUT.PUT ('Today is: '); -- display information and line feed DBMS_OUTPUT.PUT_LINE (TO_CHAR (SYSDATE, 'day'); DBMS_OUTPUT.PUT ('current time is: '); DBMS_OUTPUT.PUT_LINE (TO_CHAR (SYSDATE, 'yyyy-MM-DD HH24: MI: ss'); END; -- branch structure example: DECLARE -- defines the salary increase ratio CONSTANT c_Manager constant number: = 0.15; c_SalesMan constant number: = 0.12; c_Clerk constant number: = 0.10; -- Define the job type variable v_Job VARCHAR (100); empNo1 NUMBER (4): = 7369; BEGIN -- query the employee information of the specified employee code SELECT job INTO v_Job FROM emp WHERE empno = empNo1; -- execute THE branch to determine IF v_Job = 'cler' N update emp set sal = SAL * (1 + c_Clerk) WHERE empno = empNo1; ELSIF v_Job = 'salesman' then update emp set sal = SAL * (1 + c_SalesMan) WHERE empno = empNo1; ELSIF v_Job = 'manager' then update emp set sal = SAL * (1 + c_Manager) WHERE empno = empNo1; end if; -- display completion information DBMS_OUTPUT.PUT_LINE ('employee already '| empNo1 |' salary increase successful! '); EXCEPTION -- handle PL/SQL pre-defined exceptions WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('employee data not found'); END; -- Example of a loop structure, demo cycle for all employees salary increase DECLARE -- Define salary increase ratio CONSTANT c_Manager constant number: = 0.15; c_SalesMan constant number: = 0.12; c_Clerk constant number: = 0.10; v_Job VARCHAR (100 ); -- define the position variable v_EmpNo VARCHAR (20); -- Define the employee ID variable v_Ename VARCHAR (60); -- Define the employee name variable CURSOR c_Emp is select job, empno, ename from emp for update; begin open c_Emp ;-- Open cursor LOOP -- cyclic cursor FETCH c_Emp INTO v_Job, v_EmpNo, v_Ename; -- extract cursor data exit when c_Emp % NOTFOUND; -- IF no data exists, extract and exit the cursor IF v_Job = 'cler' THEN -- IF the employee is working, raise the salary by 10% UPDATE emp set sal = SAL * (1 + c_Clerk) where current of c_Emp; ELSIF v_Job = 'salesman' THEN -- if the employee is a sales employee, the salary increase is 12% UPDATE emp set sal = SAL * (1 + c_SalesMan) where current of c_Emp; ELSIF v_Job = 'manager' THEN -- if you are a MANAGER, raise the salary by 15% UPDATE emp set sal = SAL * (1 + c_Manager) WHERE CURRENT OF c_Emp; end if; -- display completion information DBMS_OUTPUT.PUT_LINE ('employee already '| v_EmpNo |': '| v_Ename |' salary increase! '); End loop; CLOSE c_Emp; -- CLOSE the cursor exception when NO_DATA_FOUND THEN -- process the PL/SQL pre-defined EXCEPTION DBMS_OUTPUT.PUT_LINE ('employee data not found'); END; create or replace function GetAddSalaryRatio (p_Job VARCHAR2) return number as v_Result NUMBER (10%); begin if p_Job = 'cler' THEN -- IF it is a staff member, a raise of 0.10 v_Result: =; ELSIF p_Job = 'salesman' THEN -- if it is a sales employee, the salary increase is 12% v_Result: = 0.12; ELSIF p_Job = 'manager' THEN -- if it is a MANAGER, the salary increase is 15% v_Result: = 0.15; end if; RETURN v_Result; END; create or replace function GetAddSalaryRatioCASE (p_Job VARCHAR2) return number as v_Result NUMBER (7,2 ); begin case p_Job -- use the case when statement to determine the condition WHEN 'wheel' THEN -- employee v_Result: = 0.10; WHEN 'salesman' THEN -- sale v_Result: = 0.12; WHEN 'manager' THEN -- MANAGER v_Result: = 0.15; end case; RETURN v_Result; -- RETURN value END; BEGIN DBMS_OUTPUT.PUT_LINE (GetAddSalaryRatio ('bc'); END;