Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You have the whole <code>IN (select ....</code> part twice in your code. I think that's what the compilation error is about. </p> <p>I also think 2 <code>in</code>s will be faster, because Oracle may use the indexes better:</p> <pre><code>select e.e#, e.name from trkemployee e where e# IN (select e# from trkdriver) and e# in (select e# from trkmechanic); </code></pre> <p>Tip: If using cursors like this, you need to add a lot of exception handling to prevent cursors staying open in case of an error. The <code>for</code> loop makes looping through cursors easier and safer:</p> <pre><code>for r in C loop DBMS_OUTPUT.PUT_LINE(r.e#); end loop; </code></pre> <p>Also, I can imaging that <code>e#</code> is a field name that may cause trouble in PL/SQL. I'm not sure about that, but if you get compilation errors, that may be the case. I'd rather go for a more common name like 'EmployeeNr`.</p> <p>[edit]</p> <p>Your code has a couple of errors. First, there's a <code>;</code> after <code>cursor C1</code> that shouldn't be there. Also, there is no code inside your loop, which isn't allowed in PL/SQL. If you need to test it, but you haven't code code yet, you can type <code>null;</code> for 'a piece of code that does nothing. Like:</p> <pre><code>loop null; end loop; </code></pre> <p>Your whole code could look like this. This one actually compiles and should get you going to finish it with what else you may need.</p> <pre><code>CREATE OR REPLACE PROCEDURE verify_employee IS CURSOR C1 IS SELECT E.E#, E.name FROM trkemployee E WHERE E# IN (SELECT E# FROM trkdriver) AND E# IN (SELECT E# FROM trkmechanic); BEGIN -- R becomes an alias for each row. You can access the fields -- of the rows inside the loop FOR R IN C1 LOOP -- Output each Emplyee number. DBMS_OUTPUT.PUT_LINE(R.E# || ', ' || r.name); END LOOP; END; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSQL - Using Cursor & Implement statement
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USGolezTrol
    1. USGolezTrol
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHi thanks for the solution and help. but how do i integrate your code into my PL/SQL, sorry for asking. its seems like asking more when you already give me a clear answer, but i still very unfamiliar with pl/sql
      singulars
      1. PO
      1. USuser1777711
    2. CONo prob. Apparently you have a couple of syntax errors in your procedure. If you got a good tool (I use PL/SQL developer myself) you should be able to see where and what errors you've got. I've found a fews that I described in an addition to my answer. I've also added a procedure that compiles, which should get you going.
      singulars
      1. PO
      1. USGolezTrol
    3. COB.t.w. Using `E#` as a fieldname turns out not to be a problem at all, even in PL/SQL.
      singulars
      1. PO
      1. USGolezTrol
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload