--设置屏幕输出为on，即在屏幕上显示相应的输出。
SQL> set serveroutput on;
--dbms_output.put_line();相当于Java中的System.out.println();
SQL> begin
? 2?dbms_output.put_line('hello!');
? 3? end;
? 4? /
?
--PL/SQL 中声明变量用declare ，变量名在前，类型在后；约定俗成的变量命名以“V_”开头
SQL> declare
? 2? v_name varchar2(10);
? 3? begin
? 4? v_name := 'myname';
? 5?dbms_output.put_line(v_name);
? 6? end;
? 7? /
?
? declare
? v_num number := 0;
? begin
? v_num = 2/v_num;
? dbms_output.put_line(v_num);
? exception
? when others then
?dbms_output.put_line('error!');
? end;
? /
?--变量声明，使用%type属性
??declare
??v_empno number(4);
??v_empno2 emp.empno%type;
??v_empno3 v_empno2%type;
??
??
--Table变量类型 即相当于Java中的数组类型
declare
--声明类型type_table_emp_empno是一个table类型，里边存放的是emp表中的empno类型的数据，用binary_integer类型的索引；该类型的变量的下标可以是负数。
?type type_table_emp_empno is table ofemp.empno%type index by binary_integer;
?--定义变量v_empnos 是上面声明的类型
?v_empnos type_table_emp_empno;
?
--Record变量类型，相当于Java中的类
declare
?type type_record_dept is record--声明类型type_record_dept 是一个record
??{
???deptnodept.deptno%type,
???dnamedept.dname%type,
???locdept.loc%type
??};
?v_temp type_record_dept;?--定义变量v_temp是上面声明的type_record_dept类型的
?begin
??v_temp.deptno := 50;
??v_temp.dname :='aaa';
??v_temp.loc := 'BJ';
??dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
?end;
--使用%rowtype声明record变量
declare
?v_temp dept%rowtype; --声明变量v_temp为 dept整张表的行结构，当dept的表结构发生变化的时候该变量则跟着发生相应的变化
?begin
??v_temp.deptno := 50;
??v_temp.dname :='aaa';
??v_temp.loc := 'BJ';
??dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
?end;
?
--SQL语句的运用
declare
?v_ename emp.ename%type;
?v_sal emp.sal%type;
?begin
?--用select语句时必须要有返回值，并且返回值只能有一条
??select ename,sal intov_ename,v_sal from emp where empno = 7369;
??dbms_output.put_line(v_ename);
?end;
?
declare
?v_deptno dept.deptno%type :=50;
?v_name dept.dname%ytpe :='aaa';
?v_loc dept.loc%type := 'BJ';
?begin
??insert into dept2values(v_deptno ,v_dname,v_loc);
??commit;
?end;
--PL/SQL中执行DDL语句需要在前面加上execute immediate 语句
?begin
??executeimmediate? 'create table t (nnn varchar2(10)default ''aaa'' )'; --用两个单引号对DDL语句中的单引号进行转义
?end;
?
--if语句
--取出7369的薪水，如果<1200，则输出'low'，如果<200则输出'middle'，否则'high'
declare
?v_sal emp.sal%type;
?begin
??select sal into v_sal from empwhere empno = 7369;
??if(v_sal <1200)then
???dbms_output.put_line('low');
???elsif (v_sal< 2000) then
????dbms_output.put_line('middle');
????else
?????dbms_output.put_line('high');
??end if;
?end;
?
--PL/SQL中的循环
declare
?i binary_integer :=1;
?begin --相当于do..while循环
??loop
???dbms_output.put_line(i);
???i :=i+1;
???exit when(i>=11);
??end loop;
?end;
?
declare
?j binary_integer :=1;
?begin
??while j<11loop
???dbms_output.put_line(j);
???j :=j+1;
??end loop;
?end;
?
begin
?for k in 1..10 loop
??dbms_output.put_line(k);
?end loop;
?--从1到10倒序循环
?for k in reverse 1..10 loop
??dbms_output.put_line(k);
?end loop;
end;
--错误处理
declare
?v_empno emp.empno%type;
?begin
??select empno into v_empno fromemp where deptno=10;
??exception
???whentoo_many_rows then
????dbms_output.put_line('太对记录了');
???when othersthen
????dbms_output.put_line('error!');
?end;
??
--错误日志的记录
create table errlog
(
?id number primary key,
?errcode number,
?errmsg varchar2(1024),
?errdate date
)
create sequence seq_errlog_id start with 1 increment by 1;--创建errlog表的递增序列
declare
?v_deptno dept.deptno%type := 10;
?v_errcode number;
?v_errmsg errlog.errmsg%type;
?begin
??delete from dept where deptno =v_deptno;
??commit;
?exception
??when others then
???rollback;
???v_errcode :=SQLCODE;
???v_errmsg:=SQLERRM;
???insert intoerrlog values(seq_errlog_id.nextval,v_errcode,v_errmsg,sysdate);
???commit;
?end;
?
--PL/SQL中cursor（游标）的使用?游标有四个属性：isopen、notfound、found、recount
declare
?cursor c is
??select * from emp;
?v_emp c%rowtype;
?begin
??open c;
??fetch c into v_emp;
??dbms_output.put_line(v_emp.ename);
??close c;
?end;
?
--游标配合循环的运用
?declare
?cursor c is
??select * from emp;
?v_emp c%rowtype;
?begin
??open c;
??loop
???fetch c intov_emp;
???exit when(c%notfound);
???dbms_output.put_line(v_emp.ename);
??endloop;?
??close c;
?end;
--用while循环的实现
?declare
?cursor c is
??select * from emp;
?v_emp c%rowtype;
?begin
??open c;
??fetch c into v_emp;
??while(c%found) loop
???dbms_output.put_line(v_emp.ename);
???fetch c intov_emp;
??endloop;?
??close c;
?end;
??
--用for循环的实现
declare
?cursor c is
??select * from emp;
?begin
??for v_emp inc? loop
???dbms_output.put_line(v_emp.ename);
??end loop;
?end;
--带参数的游标的实例：?
declare
?cursor c(v_deptno emp.deptno%type,v_jobemp.job%type) is
??select ename,sal from emp wheredeptno = v_deptno and job = v_job;
?begin
??for v_emp inc(30,'CLERK')? loop
???dbms_output.put_line(v_emp.ename);
??end loop;
?end;
?
--可更新的游标
declare
?cursor c? is select * from emp2for update;
?begin
??for v_temp in c loop
???if(v_temp.sal< 2000) then
????updateemp2 set sal = sal *2 where current of c;
????elsif(v_temp.sal = 5000) then
?????deletefrom emp2 where current of c;
???end if;
??end loop;
??commit;
?end;
?
?
--存储过程
create or replace procedure p
?is
?cursor c? is select * from emp2for update;
?begin
??for v_temp in c loop
???if(v_temp.sal< 2000) then
????updateemp2 set sal = sal + 20 where current of c;
????elsif(v_temp.sal = 5000) then
?????updateemp2 set sal = sal + 50 where current of c;
?????else
??????updateemp2 set sal = sal +30 where current of c;
???end if;
??end loop;
??commit;
?end;
?
--运行过程的命令如下；
execute p;
或者
begin
?p;
end;
--创建带参数的存储过程
create or replace procedure p
?(v_a in number,v_b in number,v_c out number,v_din out number)
?is
?begin
??if(v_a > v_b)then
???v_c :=v_a;
???else
????v_c:= v_b;
??end if ;
??v_d := v_d +1;
?end;
--调用带参数的过程的实例：
declare
? v_a number := 3;
? v_b number := 4;
? v_c number;
? v_d number := 5;
? begin
???p(v_a,v_b,v_c,v_d);
???dbms_output.put_line(v_c);
???dbms_output.put_line(v_d);
??? end;
?
--函数
create or replace function sal_tax
?(v_sal number)
?return number
?is
?begin
??if(v_sal < 2000)then
???return0.1;
???elsif(v_sal<2750) then
????return0.15;
????else
?????return0.20;
??end if ;
?end;
--创建函数后调用同其他函数一样，例如：
select sal_tax(sal) , lower(ename) from emp;
--触发器
create table emp2_log
(
uname varchar2(20),
action varchar2(10),
atime date
);
--创建触发器
create or replace trigger trig
?after insert or delete or update on emp2 for eachrow
?begin
??if inserting then
???insert intoemp2_log values (USER,'insert',sysdate);
???elsifupdating then
????insertinto emp2_log values (USER ,'update' ,sysdate);
????elsifdeleting then
?????insertinto emp2_log values(USER,'delete',sysdate);
??end if;
?end;
?
--创建一触发器，解决当你执行update dept set deptno = 99 where deptno =10;时，会报emp表中已经有相应的引用的错?
create or replace trigger trig
?after update on dept? for eachrow
?begin
??update emp set deptno =:NEW.deptno where deptno =:OLD.deptno;
?end;
--当执行一条语句时既检查约束条件也触发相应的触发器，则相应的触发器先被触发！