当前位置：首页 > article >正文

python: Oracle Stored Procedure query table

article 2025/1/3 1:54:01

oracel sql script

CREATE OR REPLACE PROCEDURE SelectSchool(
    paramSchoolId IN char,
    p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
        SELECT *
        FROM School
        WHERE SchoolId = paramSchoolId;
END SelectSchool;
/
 
-- 查询所有
CREATE OR REPLACE PROCEDURE SelectSchoolAll(
    p_cursor OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
        SELECT *
        FROM School;
END SelectSchoolAll;
/

DAL:

    def selectProc(cls)->list:
         """
         存储过程
         :return:
         """
         data = cls.myms.executeCursor("SelectSchoolAll")
         return data

    def selectIdProc(cls,SchoolId:str)->list:
         """
         存储过程
         :param SchoolId: 主键ID
         :return:
         """

         argsvalue= SchoolId
         args = 'paramSchoolId'
         row = cls.myms.executeParmCursor(args,argsvalue,'SelectSchool')
         return row

BLL


    def selectProc(cls) -> list[SchoolInfo]:
        """
        存储过程查询
        :return:
        """
        data=[]
        schools=[]
        data = cls.dal().selectProc()
        #print(data)
        if len(data) > 0:
            for SchoolId,SchoolName,SchoolTelNo in data:
                info = SchoolInfo()
                info.SchoolId = SchoolId
                info.SchoolName = SchoolName
                info.SchoolTelNo = SchoolTelNo
                schools.append(info)
        return schools

    def selectIdProc(cls,SchoolId:str) -> list[SchoolInfo]:
        """
        存储过程查询
        :param SchoolId:
        :return:
        """
        schools = []
        data = cls.dal().selectIdProc(SchoolId)
        if len(data) > 0:
            for SchoolId,SchoolName,SchoolTelNo in data:
                info = SchoolInfo()
                info.SchoolId = SchoolId
                info.SchoolName = SchoolName
                info.SchoolTelNo = SchoolTelNo
                schools.append(info)
        return schools

gui:

# encoding: utf-8
# 版权所有 2024 ©涂聚文有限公司
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 3.11
# OS        : windows 10
# database  : mysql 9.0 sql server 2019, poostgreSQL 17.0  oracle 11g
# Datetime  : 2024/12/24 16:58
# User      : geovindu
# Product   : PyCharm
# Project   : pyOracleDemo
# File      : main.py
# explain   : 学习
import bll
from bll.school import SchoolBll
from model.school import SchoolInfo
import ttkbootstrap as ttk
from ttkbootstrap.constants import *
from ttkbootstrap.tableview import Tableview
from gui.mainwindow.tabWindow import TabWindow

class MainWidnow(ttk.Window):
    """
    """

    def __init__(self):
        """
        """
        super().__init__(themename="cosmo", title="塗聚文學習進行中")
        # self.Window(themename="cosmo") #superhero
        self.maxsize = 300
        # self.geometry('{}x{}'.format(1350, 900))
        self.first_var = ttk.Variable()
        self.title = "main"
        self.themename = 'superhero'
        self.last_var = ttk.Variable()
        self.occupation_var = ttk.Variable()
        self.colors = self.style.colors
        self.coldata = [
            {"text": "编号", "stretch": False},
            "名称",
            {"text": "电话", "stretch": False},
        ]
        bl = bll.SchoolBll()
        infos =bl.selectProc()  # bl.selectSql()  # sql 语句
        self.rowdata = []
        for info in infos:
            row = []
            row.append(info.SchoolId)
            row.append(info.SchoolName)
            row.append(info.SchoolTelNo)
            self.rowdata.append(row)
        self.dt = Tableview(
            master=self,
            coldata=self.coldata,
            rowdata=self.rowdata,
            paginated=True,
            pagesize=15,
            searchable=True,
            bootstyle=PRIMARY,
            stripecolor=(self.colors.light, None),
        )
        self.dt.pack(fill=BOTH, expand=YES, padx=10, pady=10)
        # dt.hide_selected_column(cid=0)  #隱藏第一列

        self.dt.view.bind("<Double-1>", self.rowselected)
        # dt.view.bind("<<TreeviewSelect>>", rowselected)

        b1 = ttk.Button(self, text="Open", bootstyle="success")  # ,command=self.openwindows
        b1.pack(side=LEFT, padx=5, pady=10)
        # b1.bind("<Double-1>",openwindows)
        b1.bind("<Button-1>", self.openwindows)

        b2 = ttk.Button(self, text="New", bootstyle="info-outline")
        b2.pack(side=LEFT, padx=5, pady=10)
        b2.bind("<Button-1>", self.openlint)

    def rowselected(self, event) -> None:
        try:
            iid = self.dt.view.selection()[0]
            # print(iid)
            values = self.dt.view.item(iid, 'values')
            self.first_var.set(values[0])
            self.last_var.set(values[1])
            self.occupation_var.set(values[2])
            print(values[0], values[1], values[2])
            data = [values[0], values[1], values[2]]
            subwindow = ChildNewWindow(data)
        except IndexError as err:
            pass

    def openlint(self,event):
        """

        :param event:
        :return:
        """

        #self.destroy()
        print("link")
        suwindow=TabWindow()
        self.update()

    def openwindows(self, event):
        """
        """
        try:
            print('open windows')
            iid = self.dt.view.selection()[0]
            values = self.dt.view.item(iid, 'values')
            data = [values[0], values[1], values[2]]
            subwindow = ChildNewWindow(data)
            self.update()
        except IndexError as err:
            pass


class ChildNewWindow(ttk.Window):
    """
    彈出子窗口 ttk.Toplevel
    """

    def __init__(self, data):
        """
        :param master:
        """
        super().__init__(title='Child Window')
        self.geometry('{}x{}'.format(850, 900))
        self.title = 'Child Window'
        self.label = ttk.Label(self, text=data[0])
        self.label.pack()
        self.labe2 = ttk.Label(self, text=data[1])
        self.labe2.pack()
        self.labe3 = ttk.Label(self, text=data[2])
        self.labe3.pack()


if __name__ == '__main__':

    #print('PyCharm')
    mainwindow = MainWidnow()
    mainwindow.mainloop()

输出：