Shulou(Shulou.com)05/31 Report--

This article will explain in detail what SQL is in Oracle, and the editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

100,-modify the table structure, add fields-

Alter table stu add (addr varchar2 (29))

101.-Delete fields--

Alter table stu drop (addr)

102.-modify the length of the table field-

Alter table stu modify (addr varchar2 (50));-- the changed length must be able to accommodate the original data

103.-Delete constraints-

Alter table stu drop constraint constraint name

104.-modify the table structure to add constraints-

Alter table stu add constraint stu_class_fk foreign key (class) references class (id)

105.-data dictionary table-

Desc dictionary

-- the data dictionary table has two fields table_name comments

-- table_name mainly stores the names of data dictionary tables

Comments is mainly a description of this data dictionary table.

-View all tables, views, and constraints under the current user-data dictionary table user_tables

Select table_name from user_tables

Select view_name from user_views

Select constraint_name from user-constraints

106.-Index-

Create index idx_stu_email on stu (email);-- create an index on the email field of the stu table: idx_stu_email

107,-Delete index-

Drop index index_stu_email

108.-View all indexes-

Select index_name from user_indexes

109.-create View-

Create view v$stu as selesct id,name,age from stu

The role of views: simplify queries to protect some of our private data, through the view can also be used to update data, but we generally do not use the disadvantage: to maintain the view

110,-create sequence-

Create sequence seq;-- creation sequence

Select seq.nextval from dual;-- looks at the next value of the seq sequence

Drop sequence seq;-- deletion sequence

111,-three paradigms of the database-

(1) it is necessary to have a primary key and the columns can not be separated.

(2) partial dependency cannot exist: when multiple fields are joined together as a primary key, a field that is not a primary key cannot be partially dependent on a field in the primary key.

(3) there can be no transitive dependency.

= PL/SQL==

112.-output helloworld-- on the client

Set serveroutput on;-- defaults to off, and sets it to on so that Oracle can output data on the client.

113 、 begin

Dbms_output.put_line ('helloworld')

End

/

Assignment and output of 114,114,114-pl/sql variables

Declare

V_name varchar2 (20);-- declare variable v_name the declaration of the variable begins with v _

Begin

V_name: = 'myname'

Dbms_output.put_line (v_name)

End

/

115,115,-pl/sql exception handling (divisor 0)-

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

/

116,-declaration of variables-

Binary_integer: integers, mainly used to count rather than represent field types, are more efficient than number

Number: numeric type

Char: fixed length string

Varchar2: variable length string

Date: date

Long: string, longest 2GB

Boolean: Boolean type. You can take a value of true,false,null--, preferably give an initial value.

Declaration of-variables, using the'% type' attribute

Declare

V_empno number (4)

V_empno2 emp.empno%type

V_empno3 v_empno2%type

Begin

Dbms_output.put_line ('Test')

End

/

-- using the% type attribute, you can automatically change the declaration of the variable according to the type of the table field, saving the trouble of maintenance, and the% type attribute can be used on the variable.

-Table variable type (table represents an array)-

Declare

Type type_table_emp_empno is table of emp.empno%type index by binary_integer

V_empnos type_table type_table_empno

Begin

V_empnos (0): = 7345

V_empnos (- 1): = 9999

Dbms_output.put_line (v_empnos (1))

End

119,119,-Record variable type

Declare

Type type_record_dept is record

(

Deptno dept.deptno%type

Dname dept.dname%type

Loc dept.loc%type

);

Begin

V_temp.deptno:=50

Vaaaaaaaaaaaaa'

Vandals temp.locore.locuper.Varititemp.

Dbms_output.put_line (v temp.deptno | |''| v temp.dname)

End

120.-declare record variables using% rowtype

Declare

V_temp dept%rowtype

Begin

V_temp.deptno:=50

Vaaaaaaaaaaaaa'

Vandals temp.locore.locuper.Varititemp.

Dbms_output.put_line (v temp.deptno | |''| v temp.dname)

End

121.-sql%count counts the number of records updated by a sql statement

122. the application of-sql sentence

Declare

V_ename emp.ename%type

V_sal emp.sal%type

Begin

Select ename,sal into viciename.vascal from emp where empno = 7369

Dbms_output.put_line (v_ename | |''| | v_sal)

End

Application of 123,- pl/sql statement

Declare

V_emp emp%rowtype

Begin

Select * into v_emp from emp where empno=7369

Dbms_output_line (v_emp.ename)

End

124. application of-pl/sql statement

Declare

V_deptno dept.deptno%type: = 50

V_dname dept.dname%type: = 'aaa'

V_loc dept.loc%type: = 'bj'

Begin

Insert into dept2 values (vascal deptnore.vedname.vandame.vantloc)

Commit

End

-ddl language, data definition language

Begin

Execute immediate 'create table T (nnn varchar (30) default' 'aura')'

End

126. application of-if else

Declare

V_sal emp.sal%type

Begin

Select sal into v_sal from emp where empno = 7369

If (v_sal

< 2000) then dbms_output.put_line('low'); elsif(v_sal >

2000) then

Dbms_output.put_line ('middle')

Else

Dbms_output.put_line ('height')

End if

End

127,-Loop = do while

Declare

I binary_integer: = 1

Begin

Loop

Dbms_output.put_line (I)

I: = I + 1

Exit when (I > = 11)

End loop

End

128,-while

Declare

J binary_integer: = 1

Begin

While j

< 11 loop dbms_output.put_line(j); j:=j+1; end loop; end; 129、---------------------for begin for k in 1..10 loop dbms_output.put_line(k); end loop; for k in reverse 1..10 loop dbms_output.put_line(k); end loop; end; 130、-----------------------异常(1) declare v_temp number(4); begin select empno into v_temp from emp where empno = 10; exception when too_many_rows then dbms_output.put_line('太多记录了'); when others then dbms_output.put_line('error'); end; 131、-----------------------异常(2) declare v_temp number(4); begin select empno into v_temp from emp where empno = 2222; exception when no_data_found then dbms_output.put_line('太多记录了'); end; 132、----------------------创建序列 create sequence seq_errorlog_id start with 1 increment by 1; 133、-----------------------错误处理(用表记录：将系统日志存到数据库便于以后查看) -- 创建日志表: create table errorlog ( id number primary key, errcode number, errmsg varchar2(1024), errdate date ); declare v_deptno dept.deptno%type := 10; v_errcode number; v_errmsg varchar2(1024); begin delete from dept where deptno = v_deptno; commit; exception when others then rollback; v_errcode := SQLCODE; v_errmsg := SQLERRM; insert into errorlog values (seq_errorlog_id.nextval, v_errcode,v_errmsg, sysdate); commit; end; 133---------------------PL/SQL中的重点cursor(游标)和指针的概念差不多 declare cursor c is select * from emp; --此处的语句不会立刻执行，而是当下面的open c的时候，才会真正执行 v_emp c%rowtype; begin open c; fetch c into v_emp; dbms_output.put_line(v_emp.ename); --这样会只输出一条数据 134将使用循环的方法输出每一条记录 close c; end; 134----------------------使用do while 循环遍历游标中的每一个数据 declare cursor c is select * from emp; v_emp c%rowtype; begin open c; loop fetch c into v_emp; (1) exit when (c%notfound); --notfound是oracle中的关键字，作用是判断是否还有下一条数据 (2) dbms_output.put_line(v_emp.ename); --(1)(2)的顺序不能颠倒，最后一条数据，不会出错，会把最后一条数据，再次的打印一遍 end loop; close c; end; 135------------------------while循环，遍历游标 declare cursor c is select * from emp; v_emp emp%rowtype; begin open c; fetch c into v_emp; while(c%found) loop dbms_output.put_line(v_emp.ename); fetch c into v_emp; end loop; close c; end; 136--------------------------for 循环，遍历游标 declare cursor c is select * from emp; begin for v_emp in c loop dbms_output.put_line(v_emp.ename); end loop; end; 137---------------------------带参数的游标 declare cursor c(v_deptno emp.deptno%type, v_job emp.job%type) is select ename, sal from emp where deptno=v_deptno and job=v_job; --v_temp c%rowtype;此处不用声明变量类型 begin for v_temp in c(30, 'click') loop dbms_output.put_line(v_temp.ename); end loop; end; 138-----------------------------可更新的游标 declare cursor c --有点小错误 is select * from emp2 for update; -v_temp c%rowtype; begin for v_temp in c loop if(v_temp.sal < 2000) then update emp2 set sal = sal * 2 where current of c; else if (v_temp.sal =5000) then delete from emp2 where current of c; end if; end loop; commit; end; 139-----------------------------------procedure存储过程(带有名字的程序块) create or replace procedure p is--这两句除了替代declare，下面的语句全部都一样 cursor c is select * from emp2 for update; begin for v_emp in c loop if(v_emp.deptno = 10) then update emp2 set sal = sal +10 where current of c; else if(v_emp.deptno =20) then update emp2 set sal = sal + 20 where current of c; else update emp2 set sal = sal + 50 where current of c; end if; end loop; commit; end; --执行存储过程的两种方法： （1）exec p;(p是存储过程的名称) （2） begin p; end; / 140-------------------------------带参数的存储过程 create or replace procedure p (v_a in number, v_b number, v_ret out number, v_temp in out number) is begin if(v_a >

Vladib) then

V_ret: = Ventra

Else

V_ret: = vsigb

End if

V_temp: = v_temp + 1

End

141mura-call 140

Declare

Vallea number: = 3

Veterb number: = 4

V_ret number

V_temp number: = 5

Begin

P (vyoga, vumbb, v_ret, v_temp)

Dbms_output.put_line (v_ret)

Dbms_output.put_line (v_temp)

End

142-Delete stored procedures

Drop procedure p

143Mutual-create a function to calculate personal income tax

Create or replace function sal_tax

(v_sal number)

Return number

Is

Begin

If (v_sal < 2000) then

Return 0.10

Elsif (v_sal

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.