python: Oracle Stored Procedure query table

oracel sql script 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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 procSelectSchool; /    -- 查询所有 CREATE OR REPLACE PROCEDURE SelectSchoolAll(     p_cursor OUT SYS_REFCURSOR ) AS BEGIN     OPEN p_cursor FOR         SELECT *         FROM School; END SelectSchoolAll; /

　　

DAL:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

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:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142

# 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()

　　

 

Oracle查询存储过程需要游标，脚本代码写法有点区别，程序也有区别。postgreSQL查询存储过程是函数，程序代码区别只是名称一致就可以了。Sql server和MySQL程序代码调用差不多，脚本代码格式不一样而已。

命名规范和架构规范，找其规律，自写生成器，可以减少自己的工作量，个人初步想法。