PLSQL的语法
常量、变量的定义
说明变量(char、varchar2、date、number、boolean、long)
引用变量
name emp.name%type;
引用型变量,即name的类型与emp表中的name列的类型一样
在sql中使用into来赋值
declare
name emp.name%type;
begin
select t.name into name from emp t where t.id=1029;
dbms_output.put_line(name);
end;
记录型变量
record1 emp%rowtype;
declare
recode1 emp%rowtype;
begin
select * into recode1 from emp where id=1029;
dbms_output.put_line(recode1.name||' was born on '||to_char(recode1.hdate,'yyyy-mm-dd')||' and he earns '||recode1.sal||'$ a month');
end;
if语句
范例一:如果从控制台输入1则输出" you have already enter number 1 . "。
declare
num1 number:=#
begin
if num1=1 then
dbms_output.put_line('you have already enter number 1 .');
end if;
end;
范例二:如果从控制台输入1则输出" you have already enter number 1 . “,否则输出” your enter is not 1,sorry. "。
declare
num1 number:=#
begin
if num1=1 then
dbms_output.put_line('you have already enter number 1 .');
else
dbms_output.put_line('your enter is not 1,sorry.');
end if;
end;
范例三:判断人的不同年龄段18岁以下是未成年人,18岁以上40以下是成年人,40以上是老年人
declare
myage number:=#
begin
if myage<18 then
dbms_output.put_line('you are a teenager');
elsif myage>=18 and myage <=48 then
dbms_output.put_line('you are an adult');
elsif myage>48 then
dbms_output.put_line('you are an elderly man');
end if;
end;
循环
从1输出到10:
declare
i number:=1;
begin
while i<=10 loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
plsql编程报错异常处理
常用类型:
NO_DATA_FOUND --ORA-01403-- 未找到行
TOO_MANY_ROWS --ORA-01422-- SELECT INTO 语句返回多行数据
VALUE_ERROR --ORA-06502-- 类型转换错误
ZERO_DIVIDE --ORA-01476-- 程序尝试除以 0
STORAGE_ERROR --ORA-06500-- PL/SQL 运行时内存溢出或内存不足