PL/SQL 面试题
--1、创建序列seq_employee,该序列每次取的时候自动增加，从1开始计数，不设最大值， 并且一直累加，不循环；
CREATE SEQUENCE seq_employee
START WITH 1 INCREMENT BY 1
NOMAXVALUE;
--也可以直接使用简单默认参数：
--CREATE SEQUENCE seq_employee;
SELECT seq_employee.NEXTVAL FROM dual;
SELECT seq_employee.CURRVAL FROM dual;
DROP SEQUENCE seq_employee;
--2、写一个PL/SQL块，插入表user.employee 中100条数据，
--插入该表中字段id 用序列seq_employee实现，薪水和姓名字段可以任写；
CREATE TABLE employee (
eid NUMBER(8),
ename VARCHAR2(30) NOT NULL,
sal NUMBER(8),
CONSTRAINT pk_empl_eid PRIMARY KEY(eid)
);
BEGIN
FOR i IN 1..100 LOOP
INSERT INTO employee VALUES(seq_employee.NEXTVAL,DBMS_RANDOM.string('a',3),dbms_random.value(1000,8000));
END LOOP;
COMMIT;
END;
SELECT * FROM employee;
--DESC employee;
--3、写一个语句块，在语句块中定义一个显式游标，按id升序排列，打印表employee中前10条数据；
DECLARE
CURSOR cur_empl_prt IS
SELECT * FROM (SELECT * FROM employee ORDER BY eid) WHERE ROWNUM <= 10;
BEGIN
dbms_output.put_line('eid' || ' ename' || ' sal');
FOR v_cur_rec IN cur_empl_prt LOOP
dbms_output.put_line(v_cur_rec.eid || ' ' || v_cur_rec.ename || ' ' ||
v_cur_rec.sal);
END LOOP;
END;
--4、创建存储过程p_employee,输入员工薪水范围，打印员工工号、姓名、薪水结果集，结果集按员工薪水升序排列。
CREATE PROCEDURE p_employee(minsal IN NUMBER,maxsal IN NUMBER)
IS
CURSOR cr_empl IS SELECT * FROM employee WHERE sal BETWEEN minsal AND maxsal ORDER BY sal;
BEGIN
FOR v_empl IN cr_empl LOOP
dbms_output.put_line(v_empl.eid||' '||v_empl.ename||' '||v_empl.sal);
END LOOP;
END;
DROP PROCEDURE p_employee;
BEGIN
p_employee(1000,3000);
END;
--5、创建函数f_employee实现更新员工薪水的功能，将薪水低于2000且姓wang的员工薪水加5%，其他不变，
--更新成功则返回0，否则返回1。（如果更新超过1条数据，返回0。如果没有更新的，返回1）
CREATE OR REPLACE FUNCTION upd_empl_sal(v_res NUMBER)
RETURN NUMBER
IS
v_num NUMBER;
BEGIN
UPDATE employee SET sal=sal*1.05 WHERE sal<2000 AND ename LIKE 'wang%';
COMMIT;
SELECT COUNT(*) INTO v_num FROM employee WHERE sal<2000 AND ename LIKE 'wang%';
IF v_num>0 THEN RETURN 0;
ELSE RETURN 1;
END IF;
END;
向表内添加姓名为wang的数据：
INSERT INTO employee VALUES (1,'wangGang',1000);
SELECT * FROM employee;
DELETE FROM employee WHERE eid<10;
--6、写一个匿名块，用于执行函数f_employee,并打印执行该函数的结果。
调用：
DECLARE
v_num NUMBER(8);
BEGIN
v_num:=upd_empl_sal(v_num);
dbms_output.put_line(v_num);
END;
测试：
SELECT * FROM employee;
--7、创建存储过程 p_drop_emp,用于判断表是否存在，如果存在则删除该表；
CREATE OR REPLACE PROCEDURE p_drop_emp(in_tb_name VARCHAR2)
IS
v_num NUMBER(8);
BEGIN
SELECT COUNT(*) INTO v_num FROM User_Tables WHERE table_name=UPPER(in_tb_name);
IF v_num >0 THEN
EXECUTE IMMEDIATE 'DROP TABLE '|| in_tb_name;--存储过程中不能直接写ddl语句，可以通过动态sql方式实现；
dbms_output.put_line('表'||in_tb_name||'已删除');
ELSE
dbms_output.put_line('该表不存在');
END IF;
END;
CALL p_drop_emp('employee');
DROP PROCEDURE p_drop_emp;
SELECT * FROM user_tables;
--8、写一个匿名语句块，用于执行存储过程p_drop_emp。
BEGIN
p_drop_emp('employee');
END;
