启动服务器
sqlplus /nolog;
连接服务器
connect sys as sysdba;
推出服务器
exit;
设置
#若命令行要输出DBMS_PUT.PUT_LINE 的内容。却没输出设置
set serveroutput on;
引用输出包极其过程进行输出
DBMS_OUTPUT.PUT_LINE(' The First Name of the
Employee is ' || f_name);
创建表
create table users (
id number(10) primary key,
username varchar2(20) not null,
passwords varchar2(20) not null
);
/
查询表信息
desc users;
创建且替换过程
# hello示例
create or replace procedure hello
as
begin
DBMS_OUTPUT.PUT_LINE('hello');
end;
/
# 选择指定单行
create or replace procedure selIdUser(
uid in number)
is
cur users%ROWTYPE;
begin
select id, username, passwords into cur
from users
where id = uid;
DBMS_OUTPUT.PUT_LINE(
'id = ' || cur.id ||
' username = ' || cur.username ||
' password = ' || cur.passwords);
end;
/
# 查询多行
create or replace procedure selAllUser
is
cursor users_cursor is select id, username, passwords from users;
begin
for cur in users_cursor
loop
DBMS_OUTPUT.PUT_LINE(
'id = ' || cur.id ||
' username = ' || cur.username ||
' password = ' || cur.passwords);
end loop;
end;
/
#查询总人数
create or replace procedure selAllUserCOUNT
is
cnt number;
begin
select count(id) into cnt from users;
DBMS_OUTPUT.PUT_LINE('总人数 = ' || cnt);
end;
/
删除过程
drop procedure users;
