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;