2006年01月12日

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;