2006年01月12日

最新推荐文章于 2022-12-31 17:50:11 发布

原创最新推荐文章于 2022-12-31 17:50:11 发布 · 826 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#delete #table #integer #insert #null #sql

Oralce培训笔记专栏收录该内容

12 篇文章

订阅专栏

本文通过多个示例展示了如何使用 Oracle PL/SQL 进行数据库操作，包括变量声明与赋值、条件判断、循环控制及异常处理等。同时介绍了如何进行数据的增删改查操作。

create table plantable as select * from scott.bonus;
create public synonym plan_table for plantable;
grant all on plantable to public;
create role plustrace;
grant plustrace to public;

drop public synonym plan_table;
drop table plantable;
drop role plustrace;
////////////////////////
PL/SQL

set serveroutput on;

declare
v_empno number(4);
begin
select empno into v_empno from emp where ename='SMITH';
dbms_output.put_line(v_empno);
end;

declare
v_job   VARCHAR2(9);
v_count  BINARY_INTEGER := 0;
v_total_sal  NUMBER(9,2) := 0;
v_orderdate  DATE := SYSDATE + 7;
c_tax_rate  CONSTANT NUMBER(3,2) := 8.25;
v_valid  BOOLEAN NOT NULL := TRUE;
v_ename  emp.ename%TYPE;
v_balance  NUMBER(7,2);
v_min_balance  v_balance%TYPE := 10;
begin
null;
end;

DECLARE
V_SAL  NUMBER(7,2) := 60000;
V_COMM  NUMBER(7,2) := V_SAL / 20;
V_MESSAGE VARCHAR2(255) := ' eligible for commission';
BEGIN
DECLARE
    V_SAL  NUMBER(7,2) := 50000;
    V_COMM   NUMBER(7,2) := 0;
    V_TOTAL_COMP NUMBER(7,2) := V_SAL + V_COMM;
BEGIN
    V_MESSAGE := 'CLERK not'||V_MESSAGE;
    dbms_output.put_line(V_MESSAGE);
END;
    V_MESSAGE := 'SALESMAN'||V_MESSAGE;
    dbms_output.put_line(V_MESSAGE);
END;

DECLARE
v_deptno NUMBER(2);
v_loc VARCHAR2(15);
BEGIN
SELECT deptno, loc
INTO v_deptno, v_loc
FROM dept
WHERE dname = 'SALES';
dbms_output.put_line(v_deptno||v_loc);
END;

DECLARE
v_orderdate   ord.orderdate%TYPE;
v_shipdate    ord.shipdate%TYPE;
BEGIN
SELECT   orderdate, shipdate
    INTO   v_orderdate, v_shipdate
FROM     ord
WHERE    id = 157;
END;

declare
v_empno emp.empno%type;
v_ename emp.ename%type;
begin
select empno,ename i
into v_empno,v_ename
from emp
where ename='MILLER';
dbms_output.put_line('Millers no is'||v_empno);
end;

show error;

select * from emp_t;

declare
v_emp emp_t%rowtype;
begin
v_emp.empno:=7893;
v_emp.ename:='lsh';
v_emp.job:='SALes';
insert into emp_t(empno,ename,job,deptno) values(v_emp.empno,v_emp.ename,v_emp.job,30);
commit;
end;

select * from emp_t;

declare
v_sal_inc emp_t.sal%type:=2000;
begin
update emp_t
set sal=sal+v_sal_inc
where job='ANALYST';
end;

select * from emp_t;

declare
v_deptno emp_t.deptno%type:=20;
begin
delete from emp_t
where deptno=v_deptno;
end;

select * from emp_t;

declare
v_emp emp_t%rowtype;
begin
select ename,sal
into v_emp.ename,v_emp.sal
from emp_t
where empno=7499;
v_emp.deptno:=20;
update emp_t set sal=sal*1.2
where deptno=v_emp.deptno;
dbms_output.put_line('update rows:'||sql%rowcount);
delete from emp_t
where deptno=v_emp.deptno;
dbms_output.put_line('delete rows:'||sql%rowcount);
rollback;
end;