drop table emomy1;
select * from emomy1;
begin
dbms_output.put_line('hello,world');
end;
--定义变量
declare
--name CONSTANT varchar2(20) :='Josn'; 赋予初始值,并且不能更改(CONSTANT);
name varchar2(20);
age number;
num number;
emomy varchar2(100);
--PLSQL语句
begin
name :='Mike';
age :=12;
num :=10/0;
dbms_output.put_line(name||age);
--异常处理
exception
when others then
emomy :='除数不能为零';
dbms_output.put_line(name||',你算错了');
insert into emomy1 values(emomy,sysdate);
end;
--赋值
declare
vsal scott.emp.sal%type;
begin
select sal into vsal from scott.emp where ename='KING';
dbms_output.put_line(vsal);
end;
--控制流程
begin
for i in 1..20 loop
if mod(i,2)=0 then
dbms_output.put_line(i);
else
dbms_output.put_line(i+1);
end if;
end loop;
end;
declare
vemp emp2%rowtype;
--定义游标
cursor c_emp (s number) is select * from emp2 where sal>s;
begin
for vemp in c_emp(&sal) loop
dbms_output.put_line(vemp.ename||'的工资是'||vemp.sal);
end loop;
end;
CREATE OR REPLACE PROCEDURE ptest1 (myinput in number)
is
-- 声明异常
myexception exception;
BEGIN
if myinput > 10 then
dbms_output.put_line('OK');
elsif myinput >= 0 then
-- 主动抛出异常
raise myexception;
else
-- 主动抛出异常, 是 raise 语句的封装
raise_application_error(-20002, '数字必须要大于0');
end if;
EXCEPTION
-- 捕获异常
when myexception then
dbms_output.put_line('数字太小');
END;
begin
ptest1(-1);
end;
declare
sqla varchar2(50);
begin
sqla :='&sql';
execute immediate sqla;
end;
select * from emp2;
--动态SQL语句
DECLARE
name varchar2(50);
sal number;
BEGIN
-- 字符串
name := upper('&ename');
-- 动态执行
execute immediate 'select sal+nvl(comm,0) from emp2 where ename =''' || name || '''' into sal;
dbms_output.put_line(name || '的工资是' || sal);
END;
--异常处理
DECLARE
myinput number;
-- 声明异常
myexception exception;
BEGIN
myinput := &mynum;
if myinput > 10 then
dbms_output.put_line('OK');
elsif myinput >= 0 then
-- 主动抛出异常
raise myexception;
else
-- 主动抛出异常, 是 raise 语句的封装
raise_application_error(-20002, '数字必须要大于0');
end if;
EXCEPTION
-- 捕获异常
when myexception then
dbms_output.put_line('数字太小');
END;
-- 存储过程的参数类型
create or replace procedure pxxx (name in varchar2, -- 不能指定长度
s out number, c out number)
is
begin
select sal, nvl(comm, 0) into s, c from emp2 where ename = name;
end;
-- 调用过程
declare
s number;
c number;
begin
pxxx('KING', s, c);
dbms_output.put_line(s || '----' || c);
end;