--声明一个变量,并且进行修改
declare
v_msg varchar(20) :='无忌';
begin
v_msg :='修改后的无忌';
dbms_output.put_line(v_msg);
end;
--声明一个常量
declare
c_msg varchar(20) :='456';
begin
dbms_output.put_line(c_msg);
end;
--传参数
declare
v_name varchar2(20) := &name1;
begin
dbms_output.put_line('你是;'||v_name);
end;
declare
v_name varchar2(50) := &name1; --&name仅仅是一个占位符而已
begin
dbms_output.put_line('你是:'|| v_name);
end;
--将查询到的结果放到变量中去
declare
v_name varchar2(50) :='';
v_salary number;
begin
select first_name||last_name,salary into v_name,v_salary from zx.employees where employee_id='100';
dbms_output.put_line('全名:'|| v_name);
dbms_output.put_line('薪水:'||v_salary);
end;
--声明类型就是我要查询的字段的类型
declare
v_name zx.employees.first_name%type;
v_salary zx.employees.salary%type;
begin
select first_name||last_name,salary into v_name,v_salary from zx.employees where employee_id='100';
dbms_output.put_line('全名:'|| v_name);
dbms_output.put_line('薪水:'||v_salary);
end;
--自定义一个empType类型,里面包含fullname与salary
declare
--声明一个type类型,类似Java中的类
type empType is record(
v_name zx.employees.first_name%type,
v_salary zx.employees.salary%type
);
empObj empType; --声明一个对象
begin
select first_name||last_name,salary into empObj from zx.employees where employee_id='100';
-- 这里查询的顺序必需和上面类型声明的顺序完全一致
dbms_output.put_line('姓名'||empObj.v_name);
dbms_output.put_line('工资'||empObj.v_salary);
end;
--使用*把行中所有数据放到emp中
declare
emp zx.employees%rowtype;
begin
select * into emp from zx.employees where employee_id='100';
dbms_output.put_line('姓名'||emp.first_name||emp.last_name);
dbms_output.put_line('工资'||emp.salary);
end;
--简单if
declare
v_name varchar2(20) :='无忌';
begin
if v_name='无忌' then
dbms_output.put_line(v_name);
end if;
end;
--IF-THEN-ELSE 语句
declare
v_num number(4):=&number01;
begin
if v_num<3 then
dbms_output.put_line( '你输入的是:'||v_num);
else
dbms_output.put_line('other');
end if;
v_num number := 1;
begin
while v_num<=5 loop
dbms_output.put_line(v_num);
v_num := v_num+1;
end loop;
end;
-- 第二种
begin
for i in 1..5 loop
dbms_output.put_line(i);
end loop;
end;
--从5打印到1
begin
for i in reverse 1..5 loop
dbms_output.put_line(i);
end loop;
end;
--使用游标拿到员工的first_name,salary
declare
first_name zx.employees.first_name%type;
salary zx.employees.salary%type;
--1.定义游标
Cursor my_cursor is select first_name,salary from zx.employees;
begin
--2.打开游标
open my_cursor;
--3.获取数据
fetch my_cursor into first_name,salary;
dbms_output.put_line(first_name||salary);
--4.关闭游标
close my_cursor;
end;
--使用循环拿到游标数据
declare
first_name zx.employees.first_name%type;
salary zx.employees.salary%type;
--1.定义游标
Cursor my_cursor is select first_name,salary from zx.employees;
begin
--2.打开游标
open my_cursor;
--3.获取数据
loop
fetch my_cursor into first_name,salary;
exit when my_cursor%notfound;
dbms_output.put_line(first_name||' '||salary);
end loop;
--4.关闭游标
close my_cursor;
end;
--异常的捕获的写法
declare
emp zx.employees%rowtype;
begin
select * into emp from zx.employees where employee_id = '100';
--异常开始捕获
exception
when TOO_MANY_ROWS then dbms_output.put_line('行数太多,装不下');
when NO_DATA_FOUND then dbms_output.put_line('没有数据');
when OTHERS then dbms_output.put_line('其他异常');
end;
--创建一个存储过程(拿到一个用户的名称)
create or replace procedure getName(empId in number,firstName in out varchar) is
begin
select first_name into firstName from zx.employees where employee_id=empId;
exception
when OTHERS then
dbms_output.put_line('你错了');
firstName := '无名';
end getName;
--创建一个函数,返回两个数相加的结果
create or replace function num5(num1 in number,num2 in number) return number is
Result number;
begin
return num1 + num2;
return(Result);
end num5;
declare
v_msg varchar(20) :='无忌';
begin
v_msg :='修改后的无忌';
dbms_output.put_line(v_msg);
end;
--声明一个常量
declare
c_msg varchar(20) :='456';
begin
dbms_output.put_line(c_msg);
end;
--传参数
declare
v_name varchar2(20) := &name1;
begin
dbms_output.put_line('你是;'||v_name);
end;
declare
v_name varchar2(50) := &name1; --&name仅仅是一个占位符而已
begin
dbms_output.put_line('你是:'|| v_name);
end;
--将查询到的结果放到变量中去
declare
v_name varchar2(50) :='';
v_salary number;
begin
select first_name||last_name,salary into v_name,v_salary from zx.employees where employee_id='100';
dbms_output.put_line('全名:'|| v_name);
dbms_output.put_line('薪水:'||v_salary);
end;
--声明类型就是我要查询的字段的类型
declare
v_name zx.employees.first_name%type;
v_salary zx.employees.salary%type;
begin
select first_name||last_name,salary into v_name,v_salary from zx.employees where employee_id='100';
dbms_output.put_line('全名:'|| v_name);
dbms_output.put_line('薪水:'||v_salary);
end;
--自定义一个empType类型,里面包含fullname与salary
declare
--声明一个type类型,类似Java中的类
type empType is record(
v_name zx.employees.first_name%type,
v_salary zx.employees.salary%type
);
empObj empType; --声明一个对象
begin
select first_name||last_name,salary into empObj from zx.employees where employee_id='100';
-- 这里查询的顺序必需和上面类型声明的顺序完全一致
dbms_output.put_line('姓名'||empObj.v_name);
dbms_output.put_line('工资'||empObj.v_salary);
end;
--使用*把行中所有数据放到emp中
declare
emp zx.employees%rowtype;
begin
select * into emp from zx.employees where employee_id='100';
dbms_output.put_line('姓名'||emp.first_name||emp.last_name);
dbms_output.put_line('工资'||emp.salary);
end;
--简单if
declare
v_name varchar2(20) :='无忌';
begin
if v_name='无忌' then
dbms_output.put_line(v_name);
end if;
end;
--IF-THEN-ELSE 语句
declare
v_num number(4):=&number01;
begin
if v_num<3 then
dbms_output.put_line( '你输入的是:'||v_num);
else
dbms_output.put_line('other');
end if;
end;
--输入一个数字,如果是1,显示一等奖,如果是2,显示二等奖,其它数字不中奖
declare
v_num number(4) := #
begin
if v_num = 1 then
dbms_output.put_line('恭喜你中了一等奖!!');
elsif v_num = 2 then
dbms_output.put_line('恭喜你中了二等奖!!');
else
dbms_output.put_line('没有中奖!!');
end if;
end;
--从1打印到100
declare
v_num number :=0;
begin
loop
v_num :=v_num+1;
dbms_output.put_line(v_num);
exit when v_num =100;
end loop;
end;
--从1打印到5
--第一种
declarev_num number := 1;
begin
while v_num<=5 loop
dbms_output.put_line(v_num);
v_num := v_num+1;
end loop;
end;
-- 第二种
begin
for i in 1..5 loop
dbms_output.put_line(i);
end loop;
end;
--从5打印到1
begin
for i in reverse 1..5 loop
dbms_output.put_line(i);
end loop;
end;
--使用游标拿到员工的first_name,salary
declare
first_name zx.employees.first_name%type;
salary zx.employees.salary%type;
--1.定义游标
Cursor my_cursor is select first_name,salary from zx.employees;
begin
--2.打开游标
open my_cursor;
--3.获取数据
fetch my_cursor into first_name,salary;
dbms_output.put_line(first_name||salary);
--4.关闭游标
close my_cursor;
end;
--使用循环拿到游标数据
declare
first_name zx.employees.first_name%type;
salary zx.employees.salary%type;
--1.定义游标
Cursor my_cursor is select first_name,salary from zx.employees;
begin
--2.打开游标
open my_cursor;
--3.获取数据
loop
fetch my_cursor into first_name,salary;
exit when my_cursor%notfound;
dbms_output.put_line(first_name||' '||salary);
end loop;
--4.关闭游标
close my_cursor;
end;
--异常的捕获的写法
declare
emp zx.employees%rowtype;
begin
select * into emp from zx.employees where employee_id = '100';
--异常开始捕获
exception
when TOO_MANY_ROWS then dbms_output.put_line('行数太多,装不下');
when NO_DATA_FOUND then dbms_output.put_line('没有数据');
when OTHERS then dbms_output.put_line('其他异常');
end;
--创建一个存储过程(拿到一个用户的名称)
create or replace procedure getName(empId in number,firstName in out varchar) is
begin
select first_name into firstName from zx.employees where employee_id=empId;
exception
when OTHERS then
dbms_output.put_line('你错了');
firstName := '无名';
end getName;
--创建一个函数,返回两个数相加的结果
create or replace function num5(num1 in number,num2 in number) return number is
Result number;
begin
return num1 + num2;
return(Result);
end num5;