*/
update DEPT50 set DEPTNO=80 where EMPLOYEE='Matos'; /*
7.You need a sequence that can be used with the PRIMARY KEY column of the DEPT table. The sequence should start at 200 and have a maximum value of 1,000. Have your sequence increment by 10. Name the sequence DEPT_ID_SEQ. */
create sequence DEPT_ID_SEQ increment by 10 start with 200 maxvalue 1000; /*
8.To test your sequence, write a script to insert two rows in the DEPT table.
Name your script lab_11_08.sql. Be sure to use the sequence that you created for the ID column. Add two departments: Education and Administration. Confirm your additions. Run the commands in your script. */
insert into DEPT (id,name) values(DEPT_ID_SEQ.nextval,'Education'); insert into DEPT (id,name) values(DEPT_ID_SEQ.nextval,'Administration'); /*9.Create a nonunique index on the NAME column in the DEPT table.*/ create index on DEPT(name);
--查看索引
select * from user_indexes where index_name=upper('index_dept_name'); /*10.Create a synonym for your EMPLOYEES table. Call it EMP_synonym.*/ create synonym EMP_synonym for EMPLOYEES;
14.其他数据库对象
/*
1.The staff in the HR department wants to hide some of the data in the EMPLOYEES table. They want a view called EMPLOYEES_VU based on the employee numbers, employee names, and department numbers from the EMPLOYEES table.
They want the heading for the employee name to be EMPLOYEE(列名). */
create view EMPLOYEES_VU as
select employee_id,last_name as EMPLOYEE,department_id from employees ; /*
2.Confirm that the view works. Display the contents of the EMPLOYEES_VU view. */
select * from user_views
where view_name='EMPLOYEES_VU'; /*
3.Using your EMPLOYEES_VU view, write a query for the HR department to display all employee names and department numbers. */
select EMPLOYEE,department_id from EMPLOYEES_VU; /*
4.Department 50 needs access to its employee data.
Create a view named DEPT50 that contains the employee numbers, employee last names, and department numbers for all employees in department 50.
You have been asked to label the view columns EMPNO, EMPLOYEE, and DEPTNO.
For security purposes, do not allow an employee to be reassigned to another department through the view. */
create view DEPT50(EMPNO,EMPLOYEE,DEPTNO) as
select employee_id,last_name,department_id from employees
where department_id=50 with read only; /*
5.Display the structure and contents of the DEPT50 view. */
desc DEPT50;
select text from user_views where view_name='DEPT50'; /*
6.Test your view. Attempt to reassign Matos to department 80. */
update DEPT50 set DEPTNO=80 where EMPLOYEE='Matos'; /*
7.You need a sequence that can be used with the PRIMARY KEY column of the DEPT table. The sequence should start at 200 and have a maximum value of 1,000. Have your sequence increment by 10. Name the sequence DEPT_ID_SEQ. */
create sequence DEPT_ID_SEQ increment by 10 start with 200 maxvalue 1000; /*
8.To test your sequence, write a script to insert two rows in the DEPT table.
Name your script lab_11_08.sql. Be sure to use the sequence that you created for the ID column. Add two departments: Education and Administration.
Confirm your additions. Run the commands in your script. */
insert into DEPT (id,name) values(DEPT_ID_SEQ.nextval,'Education'); insert into DEPT (id,name) values(DEPT_ID_SEQ.nextval,'Administration'); /*9.Create a nonunique index on the NAME column in the DEPT table.*/ create index on DEPT(name); --查看索引
select * from user_indexes where index_name=upper('index_dept_name'); /*10.Create a synonym for your EMPLOYEES table. Call it EMP_synonym.*/ create synonym EMP_synonym for EMPLOYEES;
15.pl sql基础
/*
1.在声明部分声明各种标量变量,并尝试在程序体中使用select语句为某个标量变量赋值 */
declare
-- Local variables here
hire_date DATE;
first_name VARCHAR2(25):='zhangsan'; salary NUMBER(8,2); isLeader BOOLEAN; age pls_integer; blood_type CHAR DEFAULT 'O';
acct_id INTEGER(4) NOT NULL := 9999; begin
-- 直接赋值 isLeader:=true;
--常用方式,利用select对变量赋值 select sysdate into hire_date from dual;
select first_name into first_name from employees where employee_id=100; dbms_output.put_line(first_name);
dbms_output.put_line(to_char(hire_date)); end; /*
2.声明行级记录变量,并尝试在程序体中使用select语句为整个记录赋值,尝试为单个分量赋值 */
declare
emp_rec employees%ROWTYPE;--emp_rec的结构与emp表的结构一致
cursor c1 IS
SELECT dep.department_id,dep.department_name ,dep.location_id FROM departments dep;
dept_rec c1%ROWTYPE;--也可以用在游标上
Begin
select dep.department_id,dep.department_name ,dep.location_id into dept_rec
FROM departments dep
where dep.department_id=20;--这里不能返回多行 dbms_output.put_line(dept_rec.department_name);
emp_rec.first_name:='zhang'; emp_rec.salary:=3000;
end; /*
3.在声明部分定义游标,并尝试或获取游标中的值 */
declare
cursor c_emp is
select a.employee_id,a.first_name||' '||a.last_name as ename ,a.job_id ,a.salary from employees a
where a.department_id=20;
total_salary employees.salary%type :=0; begin
--依次提取游标中的行,赋值给c_emp_r，c_emp_r不用事先声明 for c_emp_r in c_emp loop
total_salary := total_salary+c_emp_r.salary; dbms_output.put_line(c_emp_r.ename); end loop;
dbms_output.put_line('工资sum:'||to_char(total_salary)); end; /*
4.根据指定id获得员工的工资,工资在1000-3000内的输出A,3000-5000之间的输出B,5000-8000之间的输出C,8000以上的输出D 使用if-else和case两种方式来完成 */
declare
-- Local variables here
id integer;
v_salary employees.salary%type; v_grade char(1); begin id:=:id;
select salary into v_salary from employees t where t.employee_id=id; if v_salary>=1000 and v_salary<3000 then v_grade:='A';
elsif v_salary>=3000 and v_salary<5000 then v_grade:='B';
elsif v_salary>=5000 and v_salary<8000 then v_grade:='C'; elsif v_salary>=8000 then v_grade:='D'; else null; end if;
dbms_output.put_line('工资级别：'||v_grade); end;
--5.使用FOR循环求1-100之间的素数 declare
-- Local variables here i integer;
v_flag boolean; begin
-- Test statements here for i in 2..100 loop v_flag:=true; for j in 2..i/2 loop if mod(i,j)=0 then begin
v_flag:=false; exit; end; end if; end loop; if v_flag then
dbms_output.put(rpad(to_char(i),8,' ')); end if; end loop;
dbms_output.new_line; end;