PLSQL例子

--1、PL/SQL块
DECLARE
v_ename VARCHAR2(10);
BEGIN
SELECT ename
INTO v_ename
FROM emp
WHERE rownum=1;
DBMS_OUTPUT.PUT_LINE('职员姓名为'||v_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有职员');
RAISE;
END;

--2、%TYPE
DECLARE
v_ename emp.ename%type;
BEGIN
SELECT ename
INTO v_ename
FROM emp
WHERE rownum=1;
DBMS_OUTPUT.PUT_LINE('职员姓名为'||v_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有职员');
RAISE;
END;

--3、嵌套块和变量作用域
DECLARE
v_sal NUMBER(5):=100;
v_com NUMBER(5):=v_sal*0.2;
v_mes varchar2(100):='neusoft';
BEGIN
DECLARE
v_sal NUMBER(5):=200;
v_com NUMBER(5):=0;
BEGIN
v_mes:='shenyang'||v_mes;
v_com:=v_sal*0.3;
END;
v_mes:='china'||v_mes;
dbms_output.put_line(v_mes);
dbms_output.put_line(v_com);
END;

--4、NULL的处理
DECLARE
x NUMBER(2):=5;
y NUMBER(2):=NULL;
BEGIN
IF x!=y THEN
dbms_output.put_line(100);
ELSE
dbms_output.put_line(200);
END IF;
END;

DECLARE
x NUMBER(2):=5;
y NUMBER(2):=NULL;
BEGIN
IF nvl(x,0) != nvl(y,0) THEN
dbms_output.put_line(100);
ELSE
dbms_output.put_line(200);
END IF;
END;

--5、CASE语句
DECLARE
v_var varchar2(10);
v_deptno emp.deptno%type;
BEGIN
SELECT deptno INTO v_deptno
FROM emp
WHERE sal=(SELECT max(sal) FROM emp);

CASE
WHEN v_deptno = 10 THEN v_var:= '部门1';
WHEN v_deptno = 20 THEN v_var:= '部门2';
ELSE v_var:= '部门3';
END CASE;
dbms_output.put_line(v_var);
END;

--6、LOOP（无条件循环）
DECLARE
v_count number(2):=1;
v_empno emp.empno%type;
v_ename emp.ename%type:='ljs';
v_job emp.job%type:='manager';
BEGIN
SELECT max(empno) INTO v_empno
FROM emp;

LOOP
INSERT INTO TEST_TAB(empno,ename,job)
VALUES ((v_empno+v_count),v_ename,v_job);
v_count := v_count + 1;
EXIT WHEN v_count > 3;
END LOOP;
END;

--8、LOOP（有循环次数）
DECLARE
v_empno emp.empno%type;
v_ename emp.ename%type:='ljs';
v_job emp.job%type:='manager';
BEGIN
SELECT max(empno) INTO v_empno
FROM emp;

FOR v_count IN 1..3 LOOP
INSERT INTO TEST_TAB(empno,ename,job)
VALUES ((v_empno+v_count),v_ename,v_job);
END LOOP;
END;

--8、LOOP（WHILE LOOP）
DECLARE
v_count number(2):=1;
v_empno emp.empno%type;
v_ename emp.ename%type:='ljs';
v_job emp.job%type:='manager';
BEGIN
SELECT max(empno) INTO v_empno
FROM emp;

WHILE v_count <= 3 LOOP
INSERT INTO TEST_TAB(empno,ename,job)
VALUES ((v_empno+v_count),v_ename,v_job);
v_count := v_count + 1;
END LOOP;
END;

--9、记录
DECLAR

E
TYPE emp_record_type IS RECORD
(ename emp.ename%type,
sal emp.sal%type,
job varchar2(9));
emp_record emp_record_type;
BEGIN
SELECT ename,sal,job
INTO emp_record
FROM emp
WHERE empno = 7788;
dbms_output.put_line('雇员名='||emp_record.ename||'薪水='||emp_record.sal);
END;

--10、记录 %ROWTYPE
DECLARE
emp_rec emp%rowtype;
BEGIN
SELECT * into emp_rec
FROM emp
WHERE empno = &p_empno;

INSERT INTO TEST_TAB
VALUES (emp_rec.empno,emp_rec.ename,emp_rec.job,
emp_rec.mgr,emp_rec.hiredate,emp_rec.sal,
emp_https://www.wendangku.net/doc/ee14029097.html,m,emp_rec.deptno);
commit;
END;

--11、游标
DECLARE
v_empno emp.empno%type;
v_ename emp.ename%type;
CURSOR emp_cursor IS
SELECT empno,ename FROM emp;
BEGIN
OPEN emp_cursor;
FOR i IN 1..5 LOOP
FETCH emp_cursor INTO v_empno,v_ename;
dbms_output.put_line(v_empno||' '||v_ename);
END LOOP;
CLOSE emp_cursor;
END;


--12、游标
DECLARE
v_empno emp.empno%type;
v_ename emp.ename%type;
CURSOR emp_cursor IS
SELECT empno,ename FROM emp;
BEGIN
IF NOT emp_cursor%ISOPEN THEN
OPEN emp_cursor;
END IF;
LOOP
FETCH emp_cursor INTO v_empno,v_ename;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line(v_empno||' '||v_ename);
END LOOP;
CLOSE emp_cursor;
END;

--13、游标（带参数）
DECLARE
CURSOR emp_cursor
(p_deptno number, p_sal number)
IS
SELECT empno,ename FROM emp
WHERE deptno=p_deptno and sal > p_sal;
BEGIN
FOR emp_record IN emp_cursor(10,1000) LOOP
dbms_output.put_line(emp_record.empno||' '||emp_record.ename);
END LOOP;
END;