Location>code7788 >text

On the use of plsql operation oracle a little trick and a few common query statements

Popularity：243 ℃/2024-10-12 16:37:23

What is plsql:

This is it, a tool specialized in operating oracle, good and free.

Create a test table.

create table Student(
Id number not null,
Name varchar(20),
Age number,
Grade number,
Gender varchar(2)
)

Inside the varchar2 () is oracle's own specialized character type, with the line.

Move the cursor over the table, right-click and select Describe.

Right now none of these fields have descriptions and I don't know what they mean, add descriptions to all of them

comment on table Student is 'student chart';
comment on column  is 'ID';
comment on column  is 'name and surname';
comment on column  is '(a person's) age';
comment on column  is 'age';
comment on column  is 'distinguishing between the sexes';

Adding a Test Data

Add multiple pieces of data, but don't write insert

Type a for update after it, and the action bar above will show that there are transactions that can be committed, so ignore that for now, and then click on the lock below now

oracle generates a blank row and then precedes it with a ✳ Let's start by checking the row of data we added:.

Then copy it, and after copying it, select the next line and keep pasting it.

Then change the data, and finally click on that little green checkmark, then click on the green lock, and finally we'll go and click on the Commit Transaction button on the menu bar

Click Query after execution is complete:

If you only want to execute a certain piece of code, you can use the mouse to select the code you want to execute on the line, as shown in the figure, the back of the for update on the implementation of;.

If you want to update a field, you can also operate directly through the above steps, a bit like in the operation of excel;

If you want to delete, it's similar to the above, except that you click a different button;

After execution, Andy Lau will be deleted.

Export of data:

You can select rows, and hold down ctrl to select multiple rows.

The sql statement will be pasted in on the pasteboard:

Just delete the redundant ones and keep only the INSERT part.

How do you see the table building statement we started with:

Click on view

There is a view sql button in the bottom right corner, click on it

Click on it to see the table building statement, copy it out and save it.

That's all I can think of for now.

Here are some common query statements

select * from student t where instr(, 'surname Liu') > 0; --fuzzy query

select *
  from student t
 where ( = 'Andy Lau' and  = '50')
    or  = 'Tony Leung Chiu-wai (1965-), * actor'; --Queries with multiple conditions

select t.*,
       case
         when  = 'male' then
          'lady-killer'
         when  = 'women' then
          'beautiful woman'
         else
          'I don't know.'
       end p --Conditional judgment at the time of query
from student t;

select t.*, decode(, 'Andy Lau', 'My Favorite Star', 'celebrities') -- judgements
from student t;

select t.*, nvl(, 'non-mainstream (fashionable) (loanword)') from student t; --Determine if the name is empty

select wm_concat() from student t --Merge multiple rows of a particular piece of data,compatible withgroup by

QQ technical exchange group: 332035933;

 

<Previous