plsql例子

1.---------------------------------------------------------------
DECLARE
v_ename VARCHAR2(20):='Bill';
v_sal NUMBER(7,2):=1234.56;
v_deptno NUMBER(2):=10;
v_empno NUMBER(4):=8888;
BEGIN
INSERT INTO emp(empno,ename,JOB,sal,deptno,hiredate) VALUES (v_empno,v_ename,'Manager',v_sal,v_deptno,TO_DATE('1954.06.09','yyyy.mm.dd'));
COMMIT;
END;
2----------------------------------------------------------------
DECLARE
v_empno number(4): =8888;
BEGIN
DELETE FROM emp WHERE empno=v_empno;
COMMIT;
END;
3----------------------------------------------------------------
DECLARE
number1 NUMBER(2);
number2 number1%TYPE := 17;
text1 VARCHAR2(12) := 'Hello world';
text2 DATE := SYSDATE;
BEGIN
SELECT street_number
INTO number1
FROM address
WHERE name = 'INU';
END;
4----------------------------------------------------------------
数据插入例子
DECLARE
v_ename VARCHAR2(20) := 'Bill';
v_sal NUMBER(7,2) :=1234.56;
v_deptno NUMBER(2) := 10;
v_empno NUMBER(4) := 888;
BEGIN
INSERT INTO emp ( empno, ename, JOB, sal, deptno , hiredate )
VALUES ( v_empno, v_ename, ‘Manager’, v_sal, v_deptno,
TO_DATE(’1954.06.09’,’yyyy.mm.dd’) );
COMMIT;
END;
DECLARE
v_empno number(4) := 888;
BEGIN
DELETE FROM emp WHERE empno=v_empno;
COMMIT;
END;
5---------------------------------------------------------------
DECLARE
v_empno emp.empno%TYPE :=&no;
Type t_record is record (
v_name emp.ename%TYPE,
v_sal emp.sal%TYPE,
v_date emp.hiredate%TYPE);
Rec t_record;
BEGIN
SELECT ename, sal, hiredate INTO Rec FROM emp WHERE empno=v_empno;
DBMS_OUTPUT.PUT_LINE(Rec.v_name||'---'||Rec.v_sal||'--'||Rec.v_date); END;
6----------------------------------------------------------------
使用游?提取部?'008'?工的姓名和工?。

declare
cursor emp_cursor is select employeename,sal
from employee where departmentno = '008';
v_ename employee.employeename % type;
v_sal employee.sal % type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_ename,v_sal;
exit when emp_cursor % notfound;
dbms_output.put_line(v_ename||':'||v_sal);
end loop;
end;
7----------------------------------------------------------------
将工?低于2500的?工增加150元工?。

declare
cursor emp_cursor is select employeename,sal from employee
for update of sal;
v_ename employee.employeename % type;
v_oldsal employee.sal % type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_ename,v_oldsal;
exit when emp_cursor % notfound;
if v_oldsal<2500 then
update employee set sal =sal + 150 where current of emp_cursor;
end if;
end loop;
close emp_cursor;
end;
8----------------------------------------------------------------。